最近在项目中有个需求就是使用sqoop把数据迁移到hive中,以下是我做的一个总结。
数据集描述
Oracle源表(SQOOP_BITCOIN_DS)
Hive目标表(ods_sqoop_bitcoin_ds)
1.创建目标表
手动创建Hive目标表
由于Sqoop导入时自动创建表会自行推断字段类型,可能导致字段类型不准确,所有推荐使用手动创建
Hive目标表的形式来进行目标表创建
create table ods_sqoop_bitcoin_ds(
id int, timestamp_col string, open string, high int, low string, close string, volume_btc string, volume_currency string, weighted_price string
) row format delimited fields terminated by ','
Sqoop导入时建表
执行前需要将Oracle的jdbc连接驱动放到 $SQOOP_HOME 的 lib 目录下
sqoop create-hive-table \
--connect jdbc:oracle:thin:@//192.168.45.144:1528/lzjf \
--username root \
--password 123456 \
--table SQOOP_BITCOIN_DS \
--fields-terminated-by "," \
--lines-terminated-by "\n" \
--hive-table default.ods_sqoop_bitcoin_ds
2.Sqoop常规导入
sqoop import \
--connect jdbc:oracle:thin:@//192.168.44.144:1521/lzjf \
--username analysisdev \
--password analysisdev \
--table SQOOP_BITCOIN_DS \
--fields-terminated-by "," --lines-terminated-by "\n" \
--null-string 'null' --null-non-string 'null' \
--hive-import \
--create-hive-table \
--hive-table default.ods_sqoop_bitcoin_ds \
--delete-target-dir \
--split-by "TIMESTAMP_COL" -m 1
使用文本字段进行拆分时需要加入”-Dorg.apache.sqoop.splitter.allow_text_splitter=true”,每个Map会在Hive对应 表目录下生成一个part数据文件
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:oracle:thin:@//192.168.45.144:1528/lzjf \
--username root --password 123456 \
--table SQOOP_BITCOIN_DS \
--fields-terminated-by "," --lines-terminated-by "\n" \
--hive-import \
--hive-overwrite \
--create-hive-table \
--hive-table default.ods_sqoop_bitcoin_ds \
--delete-target-dir \
--split-by "TIMESTAMP_COL" -m 3
3.Sqoop增量导入
常规增量导入方式
增量导入有append模式和lastmodified模式,通过–incremental属性指定
1.append:追加,比如对大于last-value指定的值之后的记录进行追加导入
2.lastmodified:最后的修改时间,追加last-value指定的日期之后的记录(包括last-value那一条数 据),通过–merge-key可以更新数据
sqoop import \
--connect jdbc:oracle:thin:@//192.168.45.144:1528/lzjf \
--username root \
--password 123456 \
--table SQOOP_BITCOIN_DS \
--fields-terminated-by "," --lines-terminated-by "\n" \
--null-string 'null' --null-non-string 'null' \
--hive-import \
--hive-table default.ods_sqoop_bitcoin_ds \
--check-column TIMESTAMP_COL \
--incremental append \
--last-value "2003-01-01 22:54:00" \
--split-by "TIMESTAMP_COL" -m 1
Sqoop Job方式增量导入
由于常规的增量导入方式每次都需要用户手动指定–last-value,不利于通过任务调度来定时执行迁移任 务
因此Sqoop提供将迁移任务存储为Job的形式,直接通过执行Job脚本提交任务,Job提交的方式Sqoop
在每次执行任务的时候都会在记录上一次导入的–last-value并自动更新,下次执行迁移任务的–last- value就是上次迁移任务的最后一条数据
常用指令:
sqoop job –list(查询Job列表)
sqoop job –delete test_job(删除Job) sqoop job –exec test_job(执行Job)
使用Job方式导入Hive时需要将执行Job的用户添加到hadoop用户组,如下:
usermod -a -G hadoop root # 将root用户添加到hadoop组
# 创建Job,注意-- import之间有空格sqoop job \
--create test_job \
-- import \
--connect jdbc:oracle:thin:@//192.168.45.144:1528/lzjf \
--username root \
--password 123456 \
--table SQOOP_BITCOIN_DS \
--fields-terminated-by "," --lines-terminated-by "\n" \
--null-string 'null' --null-non-string 'null' \
--hive-import \
--hive-table default.ods_sqoop_bitcoin_ds \
--check-column TIMESTAMP_COL \
--incremental append \
--last-value "2011-12-31 16:59:00" \
--split-by "TIMESTAMP_COL" -m 1
Sqoop Job方式增量导入(使用密钥文件)
上述的方式还有一点缺点是每次执行 sqoop job –exec xxx_job 的时候都需要手动输入数据库的访问密码,因此Sqoop也提供将密码保存为密钥文件的形式,在每次执行任务的时候访问密钥文件即可(密钥 文件存储在HDFS上)
通过这种方式,用户在定时调度Sqoop Job的时候只需要执行对应表的Job即可,可以很方便的通过脚本形式调度多张表(多个Job)的迁移任务
# 创建密钥文件
echo -n "123456" > oracle.pwd
# 上传密钥文件到HDFS
hadoop fs -put oracle.pwd /sqoop_pass
# 在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>
sqoop job \
--create test_job \
-- import \
--connect jdbc:oracle:thin:@//192.168.45.144:1528/lzjf \
--username root \
--password-file /sqoop_pass/oracle.pwd \
--table SQOOP_BITCOIN_DS \
--fields-terminated-by "," --lines-terminated-by "\n" \
--null-string 'null' --null-non-string 'null' \
--hive-import \
--hive-table default.ods_sqoop_bitcoin_ds \
--check-column TIMESTAMP_COL \
--incremental append \
--last-value "2011-12-31 07:52:00" \
--split-by "TIMESTAMP_COL" -m 3
4.定时调度
可以使用调度组件如Azkaban定时调度命令脚本调度Sqoop Job,也可以使用Linux自带的crontab进行任务调度
脚本可以参考如下示例: