官方文档Sqoop User Guide (v1.4.7) (apache.org)
一、数据导入(import)
从关系型数据库(RDBMS:mysql/oracle/Postgres等)导入到大数据集群;导入的目标系统为hdfs/hive/hbase
1.导入到hdfs
1.1 全部导入
bin/sqoop import --connect jdbc:mysql://node1:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL --username root --password 1234 --table student --append --target-dir /user/hive/warehouse/test.db/student1 --num-mappers 1 --fields-terminated-by "\t" --null-non-string '\\N' --null-string '\\N'
源数据库连接-源数据库用户名-密码-源表-目标表位置(hive的目标表位置分隔符等信息可通过sql>show create table student查询)-mappers数-分隔符-mysql中的null值,导入到hive之后变为null字符串
1.2 查询导入
bin/sqoop import --connect jdbc:mysql://node1:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL --username root --password 1234 --query 'select id,name from student where id>=1 and $CONDITIONS;' --target-dir /user/hive/warehouse/test.db/student2 --num-mappers 1 --fields-terminated-by "\t"
源数据库连接-源数据库用户名-密码-源表-目标表位置(hive的目标表位置分隔符等信息可通过sql>show create table student查询)-mappers数-分隔符-查询语句
注意:where语句后必须要包含and $CONDITIONS;
如果query后使用的是双引号,则$CONDITIONS前必须加转义符“\”,防止shell识别为自己的变量
1.3 导入指定的列
bin/sqoop import --connect jdbc:mysql://node1:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL --username root --password 1234 --columns name --table student --target-dir /user/hive/warehouse/test.db/student3 --num-mappers 1 --fields-terminated-by "\t"
源数据库连接-源数据库用户名-密码-指定列-源表-目标表位置(hive的目标表位置分隔符等信息可通过sql>show create table student查询)-mappers数-分隔符
1.4 使用sqoop关键字筛选查询导入数据
bin/sqoop import --connect jdbc:mysql://node1:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL --username root --password 1234 --table student --where "id=1001" --target-dir /user/hive/warehouse/test.db/student4 --num-mappers 1 --fields-terminated-by "\t"
源数据库连接-源数据库用户名-密码-源表-筛选条件-目标表位置(hive的目标表位置分隔符等信息可通过sql>show create table student查询)-mappers数-分隔符
2. 导入到hive
hive数据存储在hdfs上,可用1中方法导入;1中直接指定hive表所在的hdfs的位置可以导入到hive)
bin/sqoop import --connect jdbc:mysql://node1:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL --username root --password 1234 --table student --num-mappers 1 --hive-import --fields-terminated-by "\t" --hive-overwrite --hive-table student
源数据库连接-源数据库用户名-密码-源表--mappers数-指定hive-import-分隔符-hive导入方式(覆盖)-hive表(没有会自建)
个人想法:这种方法分两步,先在hdfs上创建临时文件接收源表数据,再导入到hive表;经过的步骤多,导入到hive优选第一种方法,但是如果hive表存在表分区,不建议使用,因为通过方法1导入后需要修复分区
3.导入到hbase
bin/sqoop import --connect jdbc:mysql://node1:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL --username root --password 1234 --table student --columns "id,name" --column-family "info" --hbase-create-table --hbase-row-key "id" --hbase-table "hbase_student" --num-mappers 1 --split-by id
源数据库连接-源数据库用户名-密码-源表-指定列-hbase列族-指定row-key-hbase表-mapper数-切分的列
hbase手动建表
hbase>create 'hbase_company','info';
hbase>scan 'hbase_company'
二、数据导出(export)
从大数据集群导出到关系型数据库(RDBMS);导出的目标系统为:mysql/oracle/Postgres等
bin/sqoop export --connect jdbc:mysql://node1:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL --username root --password 1234 --table student2 --num-mappers 1 --export-dir /user/hive/warehouse/test.db/student1 --fields-terminated-by "\t"
目标数据库连接-目标数据库用户名-密码-目标表-mapper数-源文件目录(hdfs上的文件)-分隔符
三、sqoop作业
1.修改配置文件
vim $SQOOP_HOME/conf/sqoop-site.xml(复制sqoop-site-template.xml重命名可得)
<property>
<name>sqoop.metastore.client.record.password</name>
<value>true</value>
</property>
<property>
<name>sqoop.metastore.server.location</name>
<value>/tmp/sqoop-metastore/shared.db</value>
</property>
<property>
<name>sqoop.metastore.server.port</name>
<value>16000</value>
</property>
2.开启sqoop源数据服务
bin/sqoop metastore
3.创建job
sqoop job --create testjob --meta-connect jdbc:hsqldb:hsql://node1:16000/sqoop -- import --connect jdbc:mysql://node1:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL --username root --password 1234 --table student --target-dir /user/hive/warehouse/test.db/student2 --num-mappers 1 --fields-terminated-by "\t" --null-non-string '\\N' \--null-string '\\N'
注意 -- 与import间要有空格
4.查看job
sqoop job --meta-connect jdbc:hsqldb:hsql://node1:16000/sqoop --list
5.显示job详情
sqoop job --meta-connect jdbc:hsqldb:hsql://node1:16000/sqoop --show myjob
6.执行job
sqoop job --meta-connect jdbc:hsqldb:hsql://node1:16000/sqoop --exec mydjob
7.删除job
sqoop job --meta-connect jdbc:hsqldb:hsql://node1:16000/sqoop --delete testjob
四、问题与解决
1.java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver
原因:$SQOOP_HOM]/lib/下缺少mysql驱动包
2.Caused by: java.lang.RuntimeException: Can’t parse input data: '800 1 620025;java.io.IOException: Can’t export data, please check failed map task logs
原因:此问题出现在hive导出到关系数据库,原因为hive表的存储格式问题。
sqoop无法导出parquet文件或者ORC格式到关系型数据库,否则会报错,修改方案:
方式1:原表改为普通的textfile存储类型的表。
方式2:把要导出的数据存到一个临时表,然后再把数据导出。
3.sqoop从mysql导入到hive的时候,mysql中的null值,导入到hive之后变为null字符串
sqoop命令上加入参数
--null-non-string '\\N' \--null-string '\\N'
4.sqoop 导入过程中报错
ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: The connection property ‘zeroDateTimeBehavior’ acceptable values are: ‘CONVERT_TO_NULL’, ‘EXCEPTION’ or ‘ROUND’. The value ‘convertToNull’ is not acceptable.
原因:
上面的报错信息可以得出zeroDateTimeBehavior可以接受的值是CONVERT_TO_NULL’, ‘EXCEPTION’ or ‘ROUND’
解决办法:在connect参数中指定一下zeroDateTimeBehavior值类型
--connect jdbc:mysql://node1:3306/test?zeroDateTimeBehavior=CONVERT_TO_NULL
5.sqoop从mysql导入hive数据,时间数据全都多了13个小时
问题原因:mysql时区问题
排查:mysql>show variables like ‘%time_zone%’;
用于查看mysql中的时区 可以看到time_zone | SYSTEM,
mysql中的时区是随系统时区,这时如果你的系统时区显示CST时区,就有可能会出现这种情况,因为CST时区可以同时代表四个时区
两种解决方法:
a、mysql>set global time_zone='+8:00';
直接设置mysql全局的时区为东八区,这种方法是要对全局影响的
b、jdbc:mysql://{0}:3306/{1}?zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone='Asia/Shanghai'
在sqoop通过jdbc连接数据库的时候加上时区限制,这种方法只对当前会话生效
6.ERROR sqoop.Sqoop: Got exception running Sqoop: org.kitesdk.data.DatasetNotFoundException: Descriptor location does not exist: hdfs://XXX/.metadata
原因:此报错多为从hive导出到关系型数据库导致。原因为hive表为parquet格式。需要
添加--hcatalog参数
--hcatalog-database hive库名 \
--hcatalog-table hive表名 \
7. 使用sqoop连接数据库时报错:Exception in thread "main" java.lang.NoClassDefFoundError: org/json/JSONObject
原因:这是因为sqoop缺少java-json.jar包.,下载jar包导入sqoop目录下的lib目录下
wget http://www.java2s.com/Code/JarDownload/java-json/java-json.jar.zip
8.数据通过sqoop作业从mysql导入到hives时,报错:ERROR org.apache.sqoop.tool.JobTool - I/O error performing
job operation: java.io.IOException: Cannot restore missing job
原因:因为在其他node的本地metastore里面没有相关job的信息,job的信息只有namenode才有,job任务需要分配到其它node执行。
1)修改sqoop配置文件
vim sqoop-site.xml
添加内容(去掉对应位置原文注释)
<property>
<name>sqoop.metastore.client.record.password</name>
<value>true</value>
</property>
<property>
<name>sqoop.metastore.server.location</name>
<value>/tmp/sqoop-metastore/shared.db</value>
</property>
<property>
<name>sqoop.metastore.server.port</name>
<value>16000</value>
</property>
2)开启sqoop元数据服务
bin/sqoop metastore
3)创建job时参数添加元数据连接地址
sqoop job --create myjob --meta-connect jdbc:hsqldb:hsql://node1:16000/sqoop ……
点赞收藏加关注,问题解决不迷路!!!