Sqoop的基本使用

MySQL导入到Hive或者HDFS

#!/bin/bash
dt=$1
year=$2
sqoop import \
--connect jdbc:mysql://192.168.4.230:3306/database?tinyInt1isBit=false \
--username 123 \
--password 123 \
--columns xxx,xxx \
--query "select dt,order_id,order_status,createtimestamp from t_order_${year} where 1=1 and createtimestamp between unix_timestamp('${dt}') and unix_timestamp('${dt}')+86399 and \$CONDITIONS" \
--m 4 \
--split-by createtimestamp \
--target-dir /user/sqoop/xxx/ \
--delete-target-dir \
--direct \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-import \
--hive-database tmp \
--hive-overwrite \
--hive-partition-key dt \
--hive-partition-value 20181117 \
--hive-table tmp_order \
--fields-terminated-by '\001' 

hive -e"
insert overwrite table dim.order partition (dt='${dt}')
select * from tmp.tmp_order;
"

 

Hive导出到MySQL

#!/bin/bash
gn=$1
ds=$2

hive -e "
INSERT overwrite directory '/xxx/xxx/xxx'
select * from tmp_table where dt = '${dt}' ;
"
sqoop export \
--connect jdbc:mysql://ip:3306/bi?tinyInt1isBit=false \
--username xxx \
--password xxx \
--columns dt,xxx,xxx \
--m 1 \
--export-dir /xxx/xxx/xx \
--table export_table \
--input-null-string '\\N' \
--input-null-non-string '\\N' \
--update-key dt,id \
--update-mode allowinsert \
--fields-terminated-by '\001'