Sqoop对hive进行导出到mysql的shell脚本
1.表准备
CREATE TABLE src_sc(
`sid` int ,
`cid` int ,
`gd` int
) COMMENT = 'src_sc'
;
CREATE TABLE src_sc_d(
`sid` int ,
`cid` int ,
`gd` int
) COMMENT = 'src_sc'
;
CREATE TABLE src.src_sc (
`sid` int ,
`cid` int ,
`gd` int
) COMMENT 'sc'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS textfile;
drop table src.src_sc_d;
CREATE TABLE src.src_sc_d (
`sid` int ,
`cid` int ,
`gd` int
) COMMENT 'sc'
PARTITIONED BY (etl_date STRING COMMENT 'etl_日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
lines terminated by '\n'
STORED AS textfile;
2.shell脚本导出
2.1.非分区表导出脚本
sqoop_home=/opt/links/sqoop/bin/sqoop
connect=jdbc:mysql://hlm03:8021/bfd
username=root
password=root
table=src_sc
fields_terminated_by="\001"
export_dir=/user/hive/warehouse/src.db/src_sc
export_data() {
$sqoop_home export \
--connect $connect \
--username $username \
--password $password \
--table $table \
--fields-terminated-by "$fields_terminated_by" \
--num-mappers 1 \
--columns sid,cid,gd \
--export-dir $export_dir \
--input-null-string '\\N' \
--input-null-non-string '\\N'
}
export_data
2.2.分区表接入脚本
etl_date=$(date -d"1 day ago" +%Y-%m-%d)
sqoop_home=/opt/links/sqoop/bin/sqoop
connect=jdbc:mysql://hlm03:8021/bfd
username=root
password=root
table=src_sc_d
fields_terminated_by="\001"
hive_partition_key=etl_date
hive_partition_value=$etl_date
export_dir=/user/hive/warehouse/src.db/src_sc_d/$hive_partition_key=$hive_partition_value
export_data() {
$sqoop_home export \
--connect $connect \
--username $username \
--password $password \
--table $table \
--fields-terminated-by "$fields_terminated_by" \
--num-mappers 1 \
--columns sid,cid,gd \
--export-dir $export_dir \
--input-null-string '\\N' \
--input-null-non-string '\\N'
}
export_data