shell操作sqoop从mysql导入到hive

此脚本可输入参数,单独运行某个表,也可不输入参数,导入全部的表
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