Sqoop组件详解
1、概述¶
Sqoop是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库(mysql、postgresql、oracle...)间进行数据的传递。其原理是导入和导出动作翻译成mapreduce程序来实现，在翻译出的mapreduce中主要是对inputformat和outputformat进行定制，从而提供并发特性和容错率。
2、sqoop工作流程图¶
下图分别是import和export的工作流程图。可以看到，通过将sqoop命令转换为一个个的Map任务，并行的对数据进行处理和转移，从而达到数据传输的目的。
3、导入数据¶
为方便演示，特创建三张表。一张hive未分区表cfg_siteinfo_tdlte1，一张hive分区表cfg_siteinfo_tdlte2，一张mysql表cfg_siteinfo_tdlte3（包含自增字段和时间字段）。
在Sqoop中，“导入”概念指：从非大数据集群（RDBMS）向大数据集群（HDFS，HIVE，HBASE）中传输数据，即使用import关键字。
create table hub_test.cfg_siteinfo_tdlte1 (
area string,
enbid int,
eci int)
row format delimited fields terminated by ',';
create table hub_test.cfg_siteinfo_tdlte2 (
area string,
enbid int,
eci int)
partitioned by(city string)
row format delimited fields terminated by ',';
create table hub_test.cfg_siteinfo_tdlte_3 (
area varchar(20),
enbid int,
eci int,
PRIMARY KEY (eci),
last_mod timestamp default current_timestamp on update current_timestamp)
3.1 Mysql导入到HDFS路径¶
sqoop import -D mapred.job.queue.name=toolbox \
-Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:mysql://134.58.98.26:3306/fast \
--username C##hub \
--password 123456 \
--query "select area,enbid,eci from cfg_siteinfo_tdlte where city='HEFEI' and \$CONDITIONS" \
--target-dir /apps/hive/warehouse/hub_test.db/cfg_siteinfo_tdlte1 \
--delete-target-dir \
--fields-terminated-by ',' \
--null-string '' \
--null-non-string '' \
--split-by eci \
--m 4
注意：query参数的引号问题，要么外层使用单引号，内层使用双引号，\$CONDITIONS的\$符号不用转义。若是外层使用双引号，那么内层使用单引号，然后\$CONDITIONS的\$符号需要转义: "\${query} AND \\$CONDITIONS"。
3.2 Mysql导入到Hive表¶
hive普通表导入：
sqoop import \
-Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:mysql://134.58.98.26:3306/fast \
--username C##hub \
--password 123456 \
--query "select area,enbid,eci from cfg_siteinfo_tdlte where city='HEFEI' and \$CONDITIONS" \
--target-dir /tmp/hive/warehouse/hub_test.db/cfg_siteinfo_tdlte1 \
--delete-target-dir \
--hive-import \
--hive-overwrite \
--hive-database hub_test \
--hive-table cfg_siteinfo_tdlte1 \
--fields-terminated-by ',' \
--lines-terminated-by '\n' \
--null-string '' \
--null-non-string '' \
--split-by eci \
--m 4
提示：该过程分为两步，第一步将数据导入到HDFS临时目录，第二步将临时目录的数据加载到Hive表的数据仓库位置。
hive分区表导入：
sqoop import \
-Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:mysql://134.58.98.26:3306/fast \
--username C##hub \
--password 123456 \
--query "select area,enbid,eci from cfg_siteinfo_tdlte where city='HEFEI' and \$CONDITIONS" \
--target-dir /tmp/hive/warehouse/hub_test.db/cfg_siteinfo_tdlte2 \
--delete-target-dir \
--hive-import \
--hive-overwrite \
--hive-database hub_test \
--hive-table cfg_siteinfo_tdlte2 \
--hive-partition-key city \
--hive-partition-value HEFEI \
--fields-terminated-by ',' \
--lines-terminated-by '\n' \
--null-string '' \
--null-non-string '' \
--split-by eci \
--m 4
提示:分区表和未分区表导入的区别，添加了hive-partition-key和hive-partition-value参数。
3.3 增量导入¶
增量导入是仅导入新添加的表中的行的技术，sqoop使用–incremental参数实现增量导入，有两种模式：append和lastmodified。
（1）append模式
sqoop import \
--connect jdbc:mysql://134.58.98.26:3306/fast \
--username username \
--password 123456 \
--table cfg_siteinfo_tdlte1 \
--num-mappers 2 \
--split-by eci \
--hive-import \
--hive-database hub_test \
--hive-table cfg_siteinfo_tdlte1 \
--check-column eci \
--incremental append \
--last-value 0 \
注意：APPEND 模式支持导入到HDFS和Ｈive。
（2）Lastmodified模式
lastmodified需要指定一个更新的时间列，会将大于指定时间列的值增量导入到目标数据源，被更新的数据行也会被导入,导入方式又分为append和merge-key俩种模式，需要注意的是，此种增量导入方式不支持直接导入到Hive
merge-key模式：
sqoop import \
--connect jdbc:mysql://134.58.98.26:3306/fast \
--username username \
--password 123456 \
--table cfg_siteinfo_tdlte3 \
--num-mappers 1 \
--target-dir /app/hive/warehouse/hub_test.db/cfg_siteinfo_tdlte3 \
--fields-terminated-by ',' \
--check-column last_mod \
--incremental lastmodified \
--last-value "2020-02-20 00:00:00" \
--merge-key eci
append模式：
sqoop import \
--connect jdbc:mysql://134.58.98.26:3306/fast \
--username username \
--password 123456 \
--table cfg_siteinfo_tdlte3 \
--num-mappers 1 \
--target-dir /app/hive/warehouse/hub_test.db/cfg_siteinfo_tdlte3 \
--fields-terminated-by ',' \
--check-column last_mod \
--incremental lastmodified \
--last-value "2020-02-20 00:00:00" \
--append
4、导出数据¶
4.1 HDFS文件导出到Mysql¶
在Sqoop中，“导出”概念指：从大数据集群（HDFS，HIVE，HBASE）向非大数据集群（RDBMS）中传输数据，即使用export关键字。
sqoop export \
-D mapred.job.queue.name=nokia \
--connect jdbc:mysql://134.58.98.26:3306/fast \
--username C##hub
--password 123456 \
--table cfg_siteinfo_tdlte1 \
--columns AREA,ENBID,ECI \
--export-dir /apps/hive/warehouse/lte_hub_wk1.db/cfg_siteinfo_tdlte1/ \
--input-fields-terminated-by ","
--num-mappers 1 \
--input-null-string '\\N'
--input-null-non-string '\\N'
提示：Mysql中如果表不存在，不会自动创建。
4.2 增量更新导出¶
-- update-key，更新标识，即根据某个字段进行更新，例如id，可以指定多个更新标识的字段，多个字段之间用逗号分隔。
-- updatemod，默认是updateonly，仅仅更新已存在的数据记录，不会插入新纪录。同时存在allowinsert模式，更新已存在的数据记录，同时插入新纪录。实质上是一个insert & update的操作。
sqoop export \
-D mapred.job.queue.name=nokia \
--connect jdbc:mysql://134.58.98.26:3306/fast \
--username C##hub
--password 123456 \
--table cfg_siteinfo_tdlte1 \
--columns AREA,ENBID,ECI \
--export-dir /apps/hive/warehouse/lte_hub_wk1.db/cfg_siteinfo_tdlte3/ \
--input-fields-terminated-by ","
--num-mappers 1 \
--input-null-string '\\N'
--input-null-non-string '\\N'
--update-key eci \
--update-mode allowinsert
5、sqopp job¶
当然，参数很多看着较为繁琐，可以将其封装在sqoop job中，同时也方便定时任务脚本的调用。
（1）创建作业
sqoop job --create my_job \
-- import --connect jdbc:mysql://134.58.98.26:3306/fast \
--username C##hub \
--password-file /user/hive/pwd/mysql.pwd \
--query "select area,enbid,eci from cfg_siteinfo_tdlte where city='HEFEI' and \$CONDITIONS" \
--target-dir /apps/hive/warehouse/hub_test.db/cfg_siteinfo_tdlte1 \
--delete-target-dir \
--fields-terminated-by ',' \
--null-string '' \
--null-non-string '' \
--split-by eci \
--m 4
（2）显示作业列表
sqoop job --list
（3）显示作业内容
sqoop job --show myjob
（4）执行作业内容
sqoop job --exec myjob
免密设置步骤：
sqoop在创建job时，使用–password-file参数，可以避免输入mysql密码，如果使用–password将出现警告，并且每次都要手动输入密码才能执行job，sqoop规定密码文件必须存放在HDFS上，并且权限必须是400。
（1）在mysql.pwd中追加MySQL密码
echo -n "123456" > mysql.pwd
（2）创建保存密码文件的目录
hadoop fs -mkdir -p /user/hive/pwd/
（3）把密码文件放入该目录中
hadoop fs -put mysql.pwd /user/hive/pwd/
（4）修改文件权限
hadoop fs -chmod 400 /user/hive/pwd/mysql.pwd
（5）检查sqoop的sqoop-site.xml是否存在如下配置：
<property>
<name>sqoop.metastore.client.record.password</name>
<value>true</value>
<description>If true, allow saved passwords in the metastore.
</description>
</property>
