使用 sqoop从MySQL增量导出数据到hive

本文主要解释incremental参数下append模式和lastmodified模式的区别，想要了解具体其他参数请参考官方文档：
http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_importing_data_into_hive

- 首先测试append模式

/usr/bin/sqoop import --connect 'jdbc:mysql://172.17.1.103:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull' --username root --password 123456 -table incr_test -hive-database test -hive-table test_incr_test_20171130 -hive-import -m -1 --incremental append --check-column time_update --last-value "2017-12-01"

MySQL原始数据如下图所示：

执行上述命令后，查看hive数据如下：

只把‘2017-12-01’之后的数据导过来了，然后在MySQL添加数据之后再次导入。

MySQL数据如图：

再次执行上述命令结果如图：

把id为1的数据时间改为2017-12-01，模拟数据被更新，之后再导入hive（注：2017-11-01那条记录是之前就导入进来的）

append模式

结果表明append模式每次导入数据都会把last-value之后的数据全部导过来，不管是否超过当前时间。   

-再来测试一下lastmodified模式

/usr/bin/sqoop import --connect 'jdbc:mysql://172.17.1.103:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull' --username root --password 123456 -table incr_test -hive-database test -hive-table test_incr_test_20171130 -hive-import -m -1 --incremental lastmodified --check-column time_update --last-value "2017-12-01" 

原始MySQL数据如下：

执行命令后查看hive数据如下：

修改原始MySQL数据：

再次执行命令：

把id为1的数据时间改为2017-12-01，模拟数据被更新，之后再导入hive注：2017-11-01那条记录是之前就导入进来的）
lastmodified模式

测试结果表明lastmodified模式只会导入当前时间之前，大于当前时间不会导入，其他的与append模式无差异。
从sqoop执行日志里也可以看出来
lastmodified模式

append模式

做这个测试的原因就是因为看不太懂官方网站对这两个模式的解释，但是从测试结果来看好像区别不大，如果有错误还请小伙伴指出来，共同学习。

--------------------------------------------------------------------------------------------------------------------------------------------------------

sqoop的增量导入（increment import）

1、import增量导入的官方说明

2、测试sqoop的increment import

　　增量导入在企业当中，一般都是需要经常执行的，如隔一个星期就执行一次增量导入，故增量导入的方式需要多次执行，而每次执行时，又去写相应的执行命令的话，比较麻烦。而sqoop提供了一个很好的工具save job的方式。

　　测试的方式是通过--incremental来执行 lastmodified 模式， --check-column来设置 LASTMODIFIED检查的字段，意思就是当该字段发生更新或者添加操作，则才会执行导入。--last-value来设置初始值 '2014/8/27 13:00:00'，该值是用来作为第一次导入的下界，从第二次开始，sqoop会自动更新该值为上一次导入的上界。

　　测试开始：sqoop创建一个job的方式来实现日常的增量导入，首先在关系型的数据库中oracle穿件一个测试表oracletablename，添加两条数据：

　　　　select  *  from oracletablename；

　　　　id 　　name　　　　lastmodified

　　　　1　　　张三　　　　2015-10-10 17：52：20.0

　　　　2　　　李四　　　　2015-10-10 17：52：20.0

 

（1）创建sqoop job

sqoop job --create jobname -- import --connect jdbc:oracle:thin:@192.168.27.235:1521/orcl  --username DATACENTER --password clear --table oracletablename --hive-import    --hive-table hivetablename --incremental lastmodified --check-column LASTMODIFIED --last-value '2014/8/27 13:00:00'

说明：

　　1）在上面的job当中，不能指定-m ，因为指定了-m的话，对应的导入会在hdfs上差生相应的中间结果，当你下一次再次执行job时，则会因为output directory is exist 报错。

　　2）上面的hivetablename必须是已存在的。在第一次导入的时候，为了使得表存在，可以通过将oracletablename的表结构导入到hive中，执行的命令如下：　　　

            sqoop create-hive-table --connect jdbc:oracle:thin:@//192.168.27.235:1521/ORCL --username DATACENTER --password clear --table tablename

　　　　执行完后，会在hive中创建一个具有相同名字和相同表结构的表。 

（2）查看并执行job

上面已经创建了job后，可以通过下面的命令来查看是否已经创建job成功：

　　sqoop job --list　　　　　　　　　   列出所有的job

　　sqoop job --show jobname　　　　显示jobname的信息

　　sqoop job --delete jobname 　　　删除jobname

　　sqoop job --exec  jobname  　　　执行jobname

 

（3）执行完job后，查看hive中的表是否有数据。当然不出意外肯定是有数据的

　　并且在 执行的过程中，我们可以看到对应的执行日志如下：

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
15/10/12 15:59:37 INFO manager.OracleManager: Time zone has been set to GMT
15/10/12 15:59:37 INFO manager.SqlManager: Executing SQL statement: SELECT t.* F    ROM TEMP2 t WHERE 1=0
15/10/12 15:59:37 INFO tool.ImportTool: Incremental import based on column LASTM    ODIFIED
15/10/12 15:59:37 INFO tool.ImportTool: Lower bound value: TO_TIMESTAMP('2014/8/    27 13:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')
15/10/12 15:59:37 INFO tool.ImportTool: Upper bound value: TO_TIMESTAMP('2015-10    -12 15:59:35.0', 'YYYY-MM-DD HH24:MI:SS.FF')
15/10/12 15:59:37 WARN manager.OracleManager: The table TEMP2 contains a multi-c    olumn primary key. Sqoop will default to the column ID only for this job.
15/10/12 15:59:37 INFO manager.OracleManager: Time zone has been set to GMT
15/10/12 15:59:37 WARN manager.OracleManager: The table TEMP2 contains a multi-c    olumn primary key. Sqoop will default to the column ID only for this job.
15/10/12 15:59:37 INFO mapreduce.ImportJobBase: Beginning import of TEMP2
15/10/12 15:59:37 INFO Configuration.deprecation: mapred.jar is deprecated. Inst    ead, use mapreduce.job.jar
15/10/12 15:59:37 INFO manager.OracleManager: Time zone has been set to GMT
15/10/12 15:59:37 INFO Configuration.deprecation: mapred.map.tasks is deprecated    . Instead, use mapreduce.job.maps
15/10/12 15:59:37 INFO client.RMProxy: Connecting to ResourceManager at hadoop3/    192.168.27.233:8032
15/10/12 15:59:42 INFO db.DBInputFormat: Using read commited transaction isolati    on
15/10/12 15:59:42 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN    (ID), MAX(ID) FROM TEMP2 WHERE ( LASTMODIFIED >= TO_TIMESTAMP('2014/8/27 13:00:0    0', 'YYYY-MM-DD HH24:MI:SS.FF') AND LASTMODIFIED < TO_TIMESTAMP('2015-10-12 15:59:35.0', 'YYYY-MM-DD HH24:MI:SS.FF') )
15/10/12 15:59:42 INFO mapreduce.JobSubmitter: number of splits:4

说明：从上面的红色部分我们很清楚的知道，sqoop在导入的时候是怎么导入。我们可以知道设置的--last-value的值就是对应的下界。

( LASTMODIFIED >= TO_TIMESTAMP('2014/8/27 13:00:0    0', 'YYYY-MM-DD HH24:MI:SS.FF') AND LASTMODIFIED < TO_TIMESTAMP('2015-10-12 15:59:35.0', 'YYYY-MM-DD HH24:MI:SS.FF') )

这两个时间：

最小时间2014/8/27 13:00:0是在命令行中输入的时间：--check-column LASTMODIFIED --last-value '2014/8/27 13:00:00'

最大时间2015-10-12 15:59:35.0是当前系统时间，也就是执行命令的时候的系统当前时间。

（4）在关系数据库oracle中对oracletablename添加一行

　　　　id 　　name　　　　lastmodified

　　　　1　　　张三　　　　2015-10-10 17：52：20.0

　　　　2　　　李四　　　　2015-10-10 17：52：20.0

　　　　3　　　李四　　　　2015-10-12 16：01：23.0

（5）此时进行增量导入

　　　　即再一次执行job：sqoop job --exec  jobname

       再次查看日志的内容如下：

　　SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
15/10/12 16:02:17 INFO manager.OracleManager: Time zone has been set to GMT
15/10/12 16:02:17 INFO manager.SqlManager: Executing SQL statement: SELECT t.* F    ROM TEMP2 t WHERE 1=0
15/10/12 16:02:17 INFO tool.ImportTool: Incremental import based on column LASTM    ODIFIED
15/10/12 16:02:17 INFO tool.ImportTool: Lower bound value: TO_TIMESTAMP('2015-10    -12 15:59:35.0', 'YYYY-MM-DD HH24:MI:SS.FF')
15/10/12 16:02:17 INFO tool.ImportTool: Upper bound value: TO_TIMESTAMP('2015-10    -12 16:02:15.0', 'YYYY-MM-DD HH24:MI:SS.FF')
15/10/12 16:02:17 WARN manager.OracleManager: The table TEMP2 contains a multi-c    olumn primary key. Sqoop will default to the column ID only for this job.
15/10/12 16:02:17 INFO manager.OracleManager: Time zone has been set to GMT
15/10/12 16:02:17 WARN manager.OracleManager: The table TEMP2 contains a multi-c    olumn primary key. Sqoop will default to the column ID only for this job.
15/10/12 16:02:17 INFO mapreduce.ImportJobBase: Beginning import of TEMP2
15/10/12 16:02:17 INFO Configuration.deprecation: mapred.jar is deprecated. Inst    ead, use mapreduce.job.jar
15/10/12 16:02:17 INFO manager.OracleManager: Time zone has been set to GMT
15/10/12 16:02:17 INFO Configuration.deprecation: mapred.map.tasks is deprecated    . Instead, use mapreduce.job.maps
15/10/12 16:02:17 INFO client.RMProxy: Connecting to ResourceManager at hadoop3/    192.168.27.233:8032
15/10/12 16:02:23 INFO db.DBInputFormat: Using read commited transaction isolati    on
15/10/12 16:02:23 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN    (ID), MAX(ID) FROM TEMP2 WHERE ( LASTMODIFIED >= TO_TIMESTAMP('2015-10-12 15:59:35.0', 'YYYY-MM-DD HH24:MI:SS.FF') AND LASTMODIFIED < TO_TIMESTAMP('2015-10-12 1    6:02:15.0', 'YYYY-MM-DD HH24:MI:SS.FF') )

15/10/12 16:02:23 WARN db.BigDecimalSplitter: Set BigDecimal splitSize to MIN_IN    CREMENT
15/10/12 16:02:23 INFO mapreduce.JobSubmitter: number of splits:1

说明：我们可以从执行的日志中看出，--last-value的值会自动更新为上一次的上界的值，注意看一下上次的上界即可。

第一次执行命令中时间上下界为( LASTMODIFIED >= TO_TIMESTAMP('2014/8/27 13:00:0    0', 'YYYY-MM-DD HH24:MI:SS.FF') AND LASTMODIFIED < TO_TIMESTAMP('2015-10-12 15:59:35.0', 'YYYY-MM-DD HH24:MI:SS.FF') )

当第二次再执行的时候（第二次执行的时候不需要重新再写一遍命令，只需要sqoop job --exec  jobname即可，sqoop可以自动计算第二次、每一次执行时候的时间上下界）。第一次执行命令的时间最大值是2015-10-12 15:59:35.0，大于（不是大于等于）2015-10-12 15:59:35.0，第二次执行命令的时间最小值也是2015-10-12 15:59:35.0，小于（不是小于等于）2015-10-12 15:59:35.0。

这样完美的包含了所有数据。

------------------------------------------------------------------------------------------------

在实际的生产环境下,我们常常是要继续数据增量的导入

核心参数

• –check-column
  用来指定一些列，这些列在增量导入时用来检查这些数据是否作为增量数据进行导入，和关系型数据库中的自增字段及时间戳类似.
  注意:这些被指定的列的类型不能使任意字符类型，如char、varchar等类型都是不可以的，同时–check-column可以去指定多个列
• –incremental
  用来指定增量导入的模式，两种模式分别为Append和Lastmodified
• –last-value
  指定上一次导入中检查列指定字段最大值

Append模式实战增量导入

执行以下指令先将我们之前的数据导入

sqoop import \
--connect jdbc:mysql://master:3306/test \
--username hive \
--password 123456 \
--table customer \
-m 1

使用hdfs dfs -cat查看生成的数据文件，发现数据已经导入.然后我们在mysql的customer中插入2条数据

insert into customer values(6,'james');
insert into customer values(7,'luna');

执行如下的指令，实现增量的导入

sqoop import \
--connect jdbc:mysql://master:3306/test \
--username hive \ 
--password 123456 \
--table customer \
--check-column id \
--incremental append \
--last-value 5

在数据库的表字段中常常会设置一个自增的字段来作为数据表的主键，我们这里以id字段来作为判断数据行是否为增量数据的依据.last-value设置上次导入的id的最大值，因此sqoop就只会将id值在5~7之间的数据进行导入，实现了增量的导入
注意:如果不指定last-value值，将会将表的所有数据进行导入,便发生了数据的冗余

Lastmodified导入实战

首先我们要创建一个customer表，指定一个时间戳字段

create table customertest(id int,name varchar(20),last_mod timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

• 
  

我们再次插入如下记录:

insert into customertest(id,name) values(1,'neil');
insert into customertest(id,name) values(2,'jack');
insert into customertest(id,name) values(3,'martin');
insert into customertest(id,name) values(4,'tony');
insert into customertest(id,name) values(5,'eric');

此处的时间戳设置为在数据的产生和更新时都会发生改变.
我们此时执行sqoop指令将数据导入hdfs，

sqoop import \
--connect jdbc:mysql://master:3306/test \
--username hive \
--password 123456 \
--table customertest 
-m 1

我们再次插入一条数据进入customertest表

insert into customertest(id,name) values(6,'james')

• 
  

我们使用incremental的方式进行增量的导入

sqoop import \
--connect jdbc:mysql://master:3306/test \
--username hive \
--password 123456 \
--table customertest \
--check-column last_mod \
--incremental lastmodified \
--last-value "2016-12-15 15:47:29" \
-m 1 \
--append 

此处已经会导入我们最后插入的一条记录,但是我们却发现此处插入了2条数据，这是为什么呢？
这是因为采用lastmodified模式去处理增量时，会将大于等于last-value值的数据当做增量插入.
注意:
使用lastmodified模式进行增量处理要指定增量数据是以append模式(附加)还是merge-key(合并)模式添加

我们演示使用merge-by的模式进行增量更新,我们去update id为1的name字段

update customertest set name = 'Neil' where id = 1;

• 
  

更新之后，这条数据的时间戳会更新为我们更新数据时的系统时间，我们执行如下指令，把id字段作为merge-key

sqoop import \
--connect jdbc:mysql://master:3306/test \
--username hive \
--password 123456 \
--table customertest \
--check-column last_mod \
--incremental lastmodified \
--last-value "2016-12-15 15:47:30" \
-m 1 \
--merge-key id 

由于merge-key这种模式是进行了一次完整的maoreduce操作，因此最终我们在customertest文件夹下可以看到生成的为part-r-00000这样的文件，会发现id=1的name已经得到修改，同时新增了id=6的数据