sqoop导入mysql定时_sqoop定时增量导入mysql数据到hdfs(hive)

需求

有2张大的mysql表，量级分别是1亿和4.5亿(太大了，DBA的同学正在考虑分表)，而且数据是增量的，需要写spark任务做处理，直接读取mysql有点吃力，想通过sqoop定时增量直接导入hive，然后spark sql再与hive交互，能避免mysql的很多瓶颈，研究好几天sqoop定时任务，使用的是sqoop1，版本是sqoop-1.4.6-cdh5.7.0。

1. 创建并执行sqoop job：

sqoop job -delete torderincrementjob //先删除之前的job

sqoop job --create torderincrementjob -- import \

--connect jdbc:mysql://172.16.*.*:3306/*?useCursorFetch=true \

--username *\

--password-file /input/sqoop/pwd/109mysql.pwd \

--target-dir /mysqldb/t_order \

--table t_order \

--fields-terminated-by "\t" \

--lines-terminated-by "\n" \

--null-string '\\N' \

--null-non-string '\\N' \

--incremental append \

--check-column id \

--last-value 1281 \

-m 1

其中：

–password-file指定hdfs上存放的密码

–fields-terminated-by “\t” \ 指定列分隔符，即制表符

–lines-terminated-by “\n” \ 指定行分隔符，及换行符

–split-by id \ 指定分割的字段

–null-string ‘\N’ \ 指定string类型到hive里的值为NULL

–null-non-string ‘\N’ \ 指定非string类型到hive里的值为NULL

–incremental append

–check-column id

–last-value 1281

以上3个参数组合使用做增量

创建完成后，执行这个job：sqoop job -exec torderincrementjob会看到在日志里有如下select语句：

SELECT MIN(id), MAX(id) FROM t_order WHERE ( id >= ‘1281’ AND id < ‘100701508’ )，也就是last-value指定的id，sqoop会自己维护记录，下次再执行这个任务，起始id就是100701508，每次都是从上次执行的id到当前id的区间增量，这样就能通过crontab做定时任务，定时增量导入到hdfs

每次执行sqoop都会更新last-value的值，下次从新的值开始，以下是我从3次打印的日志里截取的：

Lower bound value: 1281

Upper bound value: 100701508

Lower bound value: 100701508

Upper bound value: 100703035

Lower bound value: 100703035

Upper bound value: 100704475

第一次执行完job后hdfs就有数据了，可以在命令行或者通过50070查看

2. 在hive中创建外部表关联HDFS上的数据：

CREATE external TABLE `t_order`(

`id` bigint,

`serial` string,

`product_id` int,

`product_type` tinyint,

`product_name` string,

`quantity` double,

`buyer_id` bigint,

`payer_id` bigint,

`price` double,

`vip_price` double,

`settle_price` double,

`currency` string,

`payer_level` tinyint,

`status` tinyint,

`pay_mode` tinyint,

`payment_serial` string,

`client_type` string,

`app_type` tinyint,

`seller_id` string,

`partner_id` int,

`reference` string,

`channel_source` string,

`note` string,

`expiration_time` string,

`operator` string,

`create_time` string,

`pay_time` string,

`update_time` string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\n'

LOCATION

'hdfs://golive-master:8020/mysqldb/t_order'

这时候就可以通过hive查询hdfs上的数据了

select * from golivecms20.t_order limit 10;

3. crontab定时任务

创建如下3个文件:

timermysqltohdfs.cron //定时任务

timermysqltohdfs.sh //脚本文件

timermysqltohdfs.log //日志文件

timermysqltohdfs.sh：

#!/bin/sh

current_time=$(date +%Y%m%d%H%M%S)

echo $current_time >> /data/bigdata/app/sqoopjob/timermysqltohdfs.log

echo ............................>> /data/bigdata/app/sqoopjob/timermysqltohdfs.log

#t_order表同步

/data/bigdata/sqoop-1.4.6-cdh5.7.0/bin/sqoop job -exec torderincrementjob

#t_userlogout表同步

/data/bigdata/sqoop-1.4.6-cdh5.7.0/bin/sqoop job -exec tuserlogoutincrementjob

timermysqltohdfs.cron(每天1点、7点、13点、19点定时执行)：

00 1,7,13,19 * * * /bin/bash /data/bigdata/app/sqoopjob/timermysqltohdfs.sh >> /data/bigdata/app/sqoopjob/timermysqltohdfs.log 2>&1

另一个表t_userlogout也是一样，相关命令如下：

创建job:

sqoop job --create tuserlogoutincrementjob -- import \

--connect jdbc:mysql://172.16.*.*:3306/*?useCursorFetch=true \

--username *\

--password-file /input/sqoop/pwd/68mysql.pwd \

--target-dir /mysqldb/t_userlogout \

--table t_userlogout \

--fields-terminated-by "\t" \

--lines-terminated-by "\n" \

--null-string '\\N' \

--null-non-string '\\N' \

--incremental append \

--check-column ID \

--last-value 1 \

-m 1

首次执行：sqoop job -exec tuserlogoutincrementjob

在hive创建外部表：

CREATE external TABLE `t_userlogout`(

`ID` bigint,

`GoliveId` string,

`InstalmentCode` string,

`ManufacturerCode` string,

`MacAddress` string,

`AreaCode` string,

`IpAddress` string,

`LoginTime` string,

`LogoutTime` string,

`DeviceID` string,

`VersionType` string,

`Version` string,

`Platform` string,

`PartnerID` int,

`BranchType` int,

`LicenseProviderCode` string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\n'

LOCATION

'hdfs://golive-master:8020/mysqldb/t_userlogout'

后边就是定时任务，增量导入了

附录：

除了指定–table导入表的全部字段，也可以通过–query指定sql:

–query “select ID,GoliveId,InstalmentCode,ManufacturerCode,MacAddress,IpAddress,LoginTime,VersionType,Version,PartnerID from t_userlogout where $CONDITIONS” \

–query “select id,serial,product_id,product_type,product_name,buyer_id,price,vip_price,settle_price,status,pay_mode,client_type,seller_id,partner_id,channel_source,expiration_time,create_time,pay_time,update_time from t_order where $CONDITIONS” \