1.目标库MySQL相关参数
1)连接配置
--connect jdbc:oracle:thin:@//10.xx.xx.xxx:1521/orcl
--username bdas
--password xxx
2)表的配置
--table xxx
2.hive参数配置
1)基本配置
--export-dir /user/hive/warehouse/dm.db/t_prc_video_join_count # hive的表在hdfs位置
--columns xxx
2)覆盖更新插入(可选)
--update-key 主键列表
--update-mode allowinsert
3.输入源分隔符与空串解析配置
--input-fields-terminated-by '\001' \
--input-lines-terminated-by '\n' \
--input-null-string "" \
--input-null-non-string "";
4.测试
将上次的users表导入到MySQL的mktest库下的users表中
首先在MySQL中把表建好:
create table mktest.users(name varchar(20),host varchar(20));
首先直接导入看看:
sqoop export \
--connect jdbc:mysql://mycat01:3306/mktest \
--username root \
-password miku \
--table users \
--export-dir /user/hadoop/hive/warehouse/mktest.db/users \
--m 3;
看下数据:
mysql> select * from mktest.users;
+----------------+------+
| name | host |
+----------------+------+
| rootmycat01 | NULL |
| root% | NULL |
| rootlocalhost | NULL |
+----------------+------+
分隔符问题,设置看看(导出默认字段分隔符是\001)
sqoop export \
--connect jdbc:mysql://mycat01:3306/mktest \
--username root \
-password miku \
--table users \
--export-dir /user/hadoop/hive/warehouse/mktest.db/users \
--input-fields-terminated-by '\001' \
--m 3;
OK,现在再来看看结果:
mysql> select * from mktest.users;
+------+-----------+
| name | host |
+------+-----------+
| root | mycat01 |
| root | % |
| root | localhost |
+------+-----------+
完整sqoop shell:
sqoop export \
--connect jdbc:oracle:thin:@//10.xx.xx.xxx:1521/orcl \
--username xxx \
--password xx \
--table T_PRC_VIDEO_JOIN_COUNT \
--export-dir /user/hive/warehouse/dm.db/t_prc_video_join_count \
--columns operate_date,channel_no,channel_name,department_no,department_name,video_join_num,video_refuse_num,video_reject_num,video_pass_num,count_type,create_date,update_date \
--update-key operate_date,channel_no,department_no \
--update-mode allowinsert \
--input-fields-terminated-by '\001' \
--input-lines-terminated-by '\n' \
--input-null-string "" \
--input-null-non-string "";