此脚本可输入参数,单独运行某个表,也可不输入参数,导入全部的表
sh sqoop_tp_member.sh 运行全部
sh sqoop_tp_member.sh ods_t_mc_member_df 单独运行 ods_t_mc_member_df
#!/bin/bash
#参数列表--需要进行业务进行修改的
#获取前一天日期
do_date=`date --date="-1 day" +%Y%m%d`
#如果输入某个表名,将单独导入某个表,不输入导入全部
if [ -n "$1" ] ;then
table=$1
else
table=all
fi
if [ -n "$2" ] ;then
do_date=$2
else
do_date=`date --date="-1 day" +%Y%m%d`
fi
#mysql参数
db_connect='jdbc:mysql://rm-2ze710u0463081j33lo.mysql.rds.aliyuncs.com:3306/tp_member?serverTimezone=GMT%2B8'
db_user='trt_reader'
db_password='Pg2019@)!('
database=shopbase
#导入数据的sqoop脚本
import_data() {
sqoop import \
--connect $db_connect \
--username $db_user \
--password $db_password \
--target-dir /datatemp/shopbase/member/$1/$do_date \
-m 1 \
--compression-codec org.apache.hadoop.io.compress.SnappyCodec \
--fields-terminated-by "\001" \
--query "$2"' and $CONDITIONS'
}
#会员基本信息表
import_ods_t_mc_member_df(){
import_data "ods_t_mc_member_df" "select
*
from
tp_member.t_mc_member
where deleted = '0'
"
}
#会员账户信息表
import_ods_t_mc_account_df(){
import_data "ods_t_mc_account_df" "select
*
from tp_member.t_mc_account
where deleted = '0'
"
}
#会员等级维表
import_ods_t_mc_level_rule_df(){
import_data "ods_t_mc_level_rule_df" "select
*
from tp_member.t_mc_level_rule
where deleted = '0'
"
}
#支付单表
import_ods_t_mc_pay_order_di(){
import_data "ods_t_mc_pay_order_di" "select
*
from tp_member.t_mc_pay_order
where (DATE_FORMAT(create_time,'%Y%m%d')=$do_date and DATE_FORMAT(update_time,'%Y%m%d') is null) or DATE_FORMAT(update_time,'%Y%m%d')=$do_date
"
}
#支付单明细表
import_ods_t_mc_pay_order_detail_di(){
import_data "ods_t_mc_pay_order_detail_di" "select
*
from tp_member.t_mc_pay_order_detail
where (DATE_FORMAT(create_time,'%Y%m%d')=$do_date and DATE_FORMAT(update_time,'%Y%m%d') is null) or DATE_FORMAT(update_time,'%Y%m%d')=$do_date
"
}
#充值记录
import_ods_t_mc_charge_record_di(){
import_data "ods_t_mc_charge_record_di" "select
*
from tp_member.t_mc_charge_record
where (DATE_FORMAT(create_time,'%Y%m%d')=$do_date and DATE_FORMAT(update_time,'%Y%m%d') is null) or DATE_FORMAT(update_time,'%Y%m%d')=$do_date
"
}
#退款记录
import_ods_t_mc_refund_order_di(){
import_data "ods_t_mc_refund_order_di" "select
*
from tp_member.t_mc_refund_order
where (DATE_FORMAT(create_time,'%Y%m%d')=$do_date and DATE_FORMAT(update_time,'%Y%m%d') is null) or DATE_FORMAT(update_time,'%Y%m%d')=$do_date
"
}
#将数据加载到表中
load_ods_method(){
#加载数据到目标表
sql="load data inpath '/datatemp/shopbase/member/$1/$do_date/part-m-00000.snappy' OVERWRITE into table $database.$1 partition(pt='$do_date');
"
hive -e "$sql"
}
#删除创建的临时目录
remove_ods_method(){
hadoop fs -rm -r /datatemp/shopbase/member/$1
}
#程序执行:
case $table in
"ods_t_mc_member_df")
#会员基本信息表
import_ods_t_mc_member_df
load_ods_method "ods_t_mc_member_df"
remove_ods_method "ods_t_mc_member_df"
;;
"ods_t_mc_account_df")
#会员账户信息表
import_ods_t_mc_account_df
load_ods_method "ods_t_mc_account_df"
remove_ods_method "ods_t_mc_account_df"
;;
"ods_t_mc_pay_order_di")
#支付单表
import_ods_t_mc_pay_order_di
load_ods_method "ods_t_mc_pay_order_di"
remove_ods_method "ods_t_mc_pay_order_di"
;;
"ods_t_mc_pay_order_detail_di")
#支付单明细表
import_ods_t_mc_pay_order_detail_di
load_ods_method "ods_t_mc_pay_order_detail_di"
remove_ods_method "ods_t_mc_pay_order_detail_di"
;;
"ods_t_mc_charge_record_di")
#充值记录
import_ods_t_mc_charge_record_di
load_ods_method "ods_t_mc_charge_record_di"
remove_ods_method "ods_t_mc_charge_record_di"
;;
"ods_t_mc_refund_order_di")
#退款记录
import_ods_t_mc_refund_order_di
load_ods_method "ods_t_mc_refund_order_di"
remove_ods_method "ods_t_mc_refund_order_di"
;;
"ods_t_mc_level_rule_df")
#会员等级维表
import_ods_t_mc_level_rule_df
load_ods_method "ods_t_mc_level_rule_df"
remove_ods_method "ods_t_mc_level_rule_df"
;;
"all")
#会员基本信息表
import_ods_t_mc_member_df
load_ods_method "ods_t_mc_member_df"
remove_ods_method "ods_t_mc_member_df"
#会员账户信息表
import_ods_t_mc_account_df
load_ods_method "ods_t_mc_account_df"
remove_ods_method "ods_t_mc_account_df"
#支付单表
import_ods_t_mc_pay_order_di
load_ods_method "ods_t_mc_pay_order_di"
remove_ods_method "ods_t_mc_pay_order_di"
#支付单明细表
import_ods_t_mc_pay_order_detail_di
load_ods_method "ods_t_mc_pay_order_detail_di"
remove_ods_method "ods_t_mc_pay_order_detail_di"
#充值记录
import_ods_t_mc_charge_record_di
load_ods_method "ods_t_mc_charge_record_di"
remove_ods_method "ods_t_mc_charge_record_di"
#退款记录
import_ods_t_mc_refund_order_di
load_ods_method "ods_t_mc_refund_order_di"
remove_ods_method "ods_t_mc_refund_order_di"
#会员等级维表
import_ods_t_mc_level_rule_df
load_ods_method "ods_t_mc_level_rule_df"
remove_ods_method "ods_t_mc_level_rule_df"
;;
esac