sqoop基本操作

1.导出（mysql->hive）

sqoop export 
--connect "jdbc:mysql://ip:port/condata?useUnicode=true&characterEncoding=utf-8"  \
--username root \
--password Root@1234 \
--table mysql_table \
--export-dir "/user/hive/warehouse/app.db/hive_table" \
--input-fields-terminated-by "," \
-m 1

2.增量导出或更新(mysql->hive)

sqoop export 
--connect "jdbc:mysql://ip:port/condata?useUnicode=true&characterEncoding=utf-8" \ --username root \
--password Root@1234 \
--table mysql_table \
--export-dir "/user/hive/warehouse/app.db/hive_table/strpartitiondate=2018-04" \
--input-fields-terminated-by "," \
--update-key "monthkey,partner_id" \
--update-mode allowinsert

3.数据导入(hive->mysql)

sqoop import  
--connect jdbc:mysql://ip:port/easy_loan \
--table mysql_table \
--username slave_data \
--password ps \
--hive-import \
--hive-database dw \
--hive-table hive_table \
--fields-terminated-by "\t" \
--lines-terminated-by "\n" \
-m 1 \
--driver com.mysql.jdbc.Driver 

4.使用colums来指定导出到mysql的列

sqoop export 
--connect "jdbc:mysql://ip:port/xdata?useUnicode=true&characterEncoding=utf-8" \ 
--username "root" \
--password "Root@1234" \
--table "mysql_table" \
--export-dir "/data/user/hive/warehouse/app.db/hive_table/strpartitiondate=2018-06-11" \
--input-fields-terminated-by "\001" \
--update-key "datekey" \
--update-mode "allowinsert"  \
--columns="datekey,mall_out_amount,mall_in_amount,fenqi_out_amount,fenqi_in_amount,invest_out_amount,invest_in_amount" \
-m 1

5.导入

没有hive表可以create-hive-table
sqoop create-hive-table \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://10.16.3.13:3306/datacube_athena \
--table CF_MALL_ACTIVITY_THEME \
--username "prodmaster" \
--password "oa87(*FSD" \
--hive-database dw \
--hive-table cf_mall_activity_theme \
--fields-terminated-by "\t"
mysql存储为null，但是hive中存储null是\N，可以替换掉：
direct直连较快 overwrite直接覆盖hive表
sqoop import  \
--direct \
--hive-overwrite \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://192.168.254.220:3307/cs_business?zeroDateTimeBehavior=convertToNull  \
--table tboverduebill \
--username "slave_data" \
--password "ungsten@dafy098QWE123" \
--delete-target-dir \
--target-dir hdfs://bigdataservice/data/user/hive/warehouse/dw.db/dw_tboverduebill \
--hive-database dw \
--hive-table dw_tboverduebill \
--fields-terminated-by "\t" \
--null-string '\\N' \
--null-non-string '\\N' \
-m 1

转载于:https://my.oschina.net/lzhaoqiang/blog/3064526