1、 hive分区 导数据到mysql
#!/bin/bash
MYSQL_BIN=/usr/local/mysql/bin
#EOF
#入口参数,获取脚本运行时间参数,默认参数为今天,与当前调度中的参数一致
echo $#
if [ $# = 0 ]; then
p_partition_d=`date -d "-1 days" +%Y%m%d`
fi
if [ $# = 1 ]; then
p_partition_d=`date -d "$1" +%Y%m%d`
fi
echo "p_partition_d:${p_partition_d}"
# 从hive分区表导出到mysql
sqoop export \
--connect "jdbc:mysql://***:3306/cc_output" \
--username ***\
--password ***\
--table txn_order_s \
--export-dir /user/hive/warehouse/cc_idl.db/txn_order_s/pt_log_d=$p_partition_d \
--input-fields-terminated-by '\001' \
--input-null-string '\\N' \
--input-null-non-string '\\N'
# 5.错误检测
if [ $? != 0 ];then
exit -1
fi
2、 hive 导数据到mysql
#!/bin/bash
MYSQL_BIN=/usr/local/mysql/bin
# 4.从hive分区表导出到mysql
sqoop export \
--connect "jdbc:mysql://****:3306/page_target" \
--username ***\
--password *** \
--table page_reg_members \
--export-dir /user/hive/warehouse/cc_flw.db/page_reg_members \
--input-fields-terminated-by '\001' \
--input-null-string '\\N' \
--input-null-non-string '\\N'
# 5.错误检测
if [ $? != 0 ];then
exit -1
fi
3、mysql 导数据到hive
#!/bin/bash
##/*********************************************************************
#--------------------------------------------------公共区 BEGIN--------------------------
脱敏文件内容
CONNECTURL=jdbc:mysql://***:3306
USERNAME=***
PASSWORD=***
---------------------#
source /etc/profile
source /home/jflm/app/dfm/sh/param/sq_shell.config #脱敏文件 url 账号密码
#随机数以时间戳纳秒用于防止目录冲突
randnum=`date +%s%N`
#mysql_database
database=machine
#mysql table
mysql_table=tb_loan_member_info
#hive_table
hive_table=time.tb_loan_member_info
##1-入口参数,获取脚本运行时间参数,默认参数为今天,与当前调度中的参数一致
echo $#
if [ $# = 0 ]; then
p_partition_d=`date -d "-1 days" +%Y%m%d`
date=`date -d -0days +%Y%m%d`
fi
if [ $# = 1 ]; then
p_partition_d=`date -d "$1" +%Y%m%d`
date=`date -d "+1 days $1" +%Y%m%d`
fi
echo "p_partition_d:${p_partition_d}"
echo "date:${date}"
#--------------------------------------------------公共区 END-------------------------------------------------#
#-----------------------------------------------------执行区START-----------------------------------------------------------------#
sqoop import \
--connect $CONNECTURL/$database \
--username $USERNAME \
--password $PASSWORD \
--query 'select eid,user_id,mobile_no,register_date,real_name
from '$mysql_table' where 1=1 and $CONDITIONS' \
--target-dir /user/jflm/odl/$database/$mysql_table/$randnum \
--num-mappers 1 \
--hive-import \
--hive-table $hive_table \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-drop-import-delims \
--fields-terminated-by '\001' \
--lines-terminated-by '\n' \
--delete-target-dir \
--hive-overwrite \
--hive-partition-key pt_log_d \
--hive-partition-value $p_partition_d
。