【电商数仓】数仓搭建之原始数据（Operation Data Store -- ODS）层（用户行为数据、业务数据）

零 ODS层

• 保持数据原貌不做任何修改，起到备份数据的作用。
• 数据采用Gzip压缩，减少磁盘存储空间。
• 创建分区表，防止后续的全表扫描，在企业开发中大量使用分区表。
• 创建外部表。在企业开发中，除了自己用的临时表，创建内部表外，绝大多数场景都是创建外部表。

27张业务数据表加1张用户行为数据表（log），共需要创建28张表，在mysql中有什么列，在这里就有什么列，直接复制过来。

log中的json数据直接存放为一行，所有的JSON体都作为line的一部分。

一 ODS层（用户行为数据）

1 创建日志表ods_log

（1）创建分区表

# 开启hive
hive
# 使用idea连接hive

drop table if exists ods_log;
CREATE EXTERNAL TABLE ods_log (`line` string)
PARTITIONED BY (`dt` string) -- 按照时间创建分区
LOCATION '/warehouse/gmall/ods/ods_log';  -- 指定数据在hdfs上的存储位置

dt：每一天都需要采集数据，每一天采集的数据存储到对应的分区中，如2020-06-14采集到的数据dt字段就是2020-06-14

（2）加载数据

注意导入之后，原始数据就没了，从 /origin_data/gmall/log/topic_log 导入到 /warehousr/gmall/ods/ods_log

load data inpath '/origin_data/gmall/log/topic_log/2020-06-14' into table ods_log partition(dt='2020-06-14');

在idea中能够看到一行一行的日志信息，说明第一张表的导入没有问题。

在hadoop压缩中，能够自动识别压缩编码，不需要进行处理，这里采用得到是文本压缩格式，文本表格压缩，hadoop能够直接处理，但orc列存格式，就需要进行处理。

注意：时间格式都配置成YYYY-MM-DD格式，这是Hive默认支持的时间格式

2 Shell中单引号和双引号区别

（1）在/home/hzy/bin创建一个test.sh文件

vim test.sh
# 在文件中添加如下内容
#!/bin/bash
do_date=$1echo '$do_date'
echo "$do_date"
echo "'$do_date'"
echo '"$do_date"'
echo `date`

（2）查看执行结果

test.sh 2020-06-14
$do_date
2020-06-14
'2020-06-14'
"$do_date"
2020年 06月 18日 星期四 21:02:08 CST

（3）总结

• 单引号不取变量值
• 双引号取变量值
• 反引号`，执行引号中命令
• 双引号内部嵌套单引号，取出变量值
• 单引号内部嵌套双引号，不取出变量值

3 ODS层日志表加载数据脚本

（1）在/home/hzy/bin创建脚本

vim hdfs_to_ods_log.sh
# 在脚本中编写如下内容
#!/bin/bash# 定义变量方便修改
APP=gmall# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;thendo_date=$1
else do_date=`date -d "-1 day" +%F`
fi echo ================== 日志日期为 $do_date ==================
sql="
load data inpath '/origin_data/$APP/log/topic_log/$do_date' into table ${APP}.ods_log partition(dt='$do_date');
"hive -e "$sql"

说明1

[ -n 变量值 ] 判断变量的值，是否为空-- 变量的值，非空，返回true-- 变量的值，为空，返回false注意：[ -n 变量值 ]不会解析数据，使用[ -n 变量值 ]时，需要对变量加上双引号(" ")

说明2

查看date命令的使用，date --help

（2）执行脚本

hdfs_to_ods_log.sh 2020-06-14
# 如果没有传参数，默认导入前一天的数据

查看导入数据

二 ODS层（业务数据）

1 27张表建表语句

-- 活动信息表
DROP TABLE IF EXISTS ods_activity_info;
CREATE EXTERNAL TABLE ods_activity_info(`id` STRING COMMENT '编号',`activity_name` STRING  COMMENT '活动名称',`activity_type` STRING  COMMENT '活动类型',`start_time` STRING  COMMENT '开始时间',`end_time` STRING  COMMENT '结束时间',`create_time` STRING  COMMENT '创建时间'
) COMMENT '活动信息表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_activity_info/';-- 活动规则表
DROP TABLE IF EXISTS ods_activity_rule;
CREATE EXTERNAL TABLE ods_activity_rule(`id` STRING COMMENT '编号',`activity_id` STRING  COMMENT '活动ID',`activity_type` STRING COMMENT '活动类型',`condition_amount` DECIMAL(16,2) COMMENT '满减金额',`condition_num` BIGINT COMMENT '满减件数',`benefit_amount` DECIMAL(16,2) COMMENT '优惠金额',`benefit_discount` DECIMAL(16,2) COMMENT '优惠折扣',`benefit_level` STRING COMMENT '优惠级别'
) COMMENT '活动规则表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_activity_rule/';-- 一级品类表
DROP TABLE IF EXISTS ods_base_category1;
CREATE EXTERNAL TABLE ods_base_category1(`id` STRING COMMENT 'id',`name` STRING COMMENT '名称'
) COMMENT '商品一级分类表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_base_category1/';-- 二级品类表
DROP TABLE IF EXISTS ods_base_category2;
CREATE EXTERNAL TABLE ods_base_category2(`id` STRING COMMENT ' id',`name` STRING COMMENT '名称',`category1_id` STRING COMMENT '一级品类id'
) COMMENT '商品二级分类表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_base_category2/';
-- 三级品类表
DROP TABLE IF EXISTS ods_base_category3;
CREATE EXTERNAL TABLE ods_base_category3(`id` STRING COMMENT ' id',`name` STRING COMMENT '名称',`category2_id` STRING COMMENT '二级品类id'
) COMMENT '商品三级分类表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_base_category3/';-- 编码字典表
DROP TABLE IF EXISTS ods_base_dic;
CREATE EXTERNAL TABLE ods_base_dic(`dic_code` STRING COMMENT '编号',`dic_name` STRING COMMENT '编码名称',`parent_code` STRING COMMENT '父编码',`create_time` STRING COMMENT '创建日期',`operate_time` STRING COMMENT '操作日期'
) COMMENT '编码字典表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_base_dic/';-- 省份表
DROP TABLE IF EXISTS ods_base_province;
CREATE EXTERNAL TABLE ods_base_province (`id` STRING COMMENT '编号',`name` STRING COMMENT '省份名称',`region_id` STRING COMMENT '地区ID',`area_code` STRING COMMENT '地区编码',`iso_code` STRING COMMENT 'ISO-3166编码，供可视化使用',`iso_3166_2` STRING COMMENT 'IOS-3166-2编码，供可视化使用'
)  COMMENT '省份表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_base_province/';-- 地区表
DROP TABLE IF EXISTS ods_base_region;
CREATE EXTERNAL TABLE ods_base_region (`id` STRING COMMENT '编号',`region_name` STRING COMMENT '地区名称'
)  COMMENT '地区表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_base_region/';-- 品牌表
DROP TABLE IF EXISTS ods_base_trademark;
CREATE EXTERNAL TABLE ods_base_trademark (`id` STRING COMMENT '编号',`tm_name` STRING COMMENT '品牌名称'
)  COMMENT '品牌表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_base_trademark/';-- 购物车表
DROP TABLE IF EXISTS ods_cart_info;
CREATE EXTERNAL TABLE ods_cart_info(`id` STRING COMMENT '编号',`user_id` STRING COMMENT '用户id',`sku_id` STRING COMMENT 'skuid',`cart_price` DECIMAL(16,2)  COMMENT '放入购物车时价格',`sku_num` BIGINT COMMENT '数量',`sku_name` STRING COMMENT 'sku名称 (冗余)',`create_time` STRING COMMENT '创建时间',`operate_time` STRING COMMENT '修改时间',`is_ordered` STRING COMMENT '是否已经下单',`order_time` STRING COMMENT '下单时间',`source_type` STRING COMMENT '来源类型',`source_id` STRING COMMENT '来源编号'
) COMMENT '加购表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_cart_info/';-- 评论表
DROP TABLE IF EXISTS ods_comment_info;
CREATE EXTERNAL TABLE ods_comment_info(`id` STRING COMMENT '编号',`user_id` STRING COMMENT '用户ID',`sku_id` STRING COMMENT '商品sku',`spu_id` STRING COMMENT '商品spu',`order_id` STRING COMMENT '订单ID',`appraise` STRING COMMENT '评价',`create_time` STRING COMMENT '评价时间'
) COMMENT '商品评论表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_comment_info/';-- 优惠券信息表
DROP TABLE IF EXISTS ods_coupon_info;
CREATE EXTERNAL TABLE ods_coupon_info(`id` STRING COMMENT '购物券编号',`coupon_name` STRING COMMENT '购物券名称',`coupon_type` STRING COMMENT '购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券',`condition_amount` DECIMAL(16,2) COMMENT '满额数',`condition_num` BIGINT COMMENT '满件数',`activity_id` STRING COMMENT '活动编号',`benefit_amount` DECIMAL(16,2) COMMENT '减金额',`benefit_discount` DECIMAL(16,2) COMMENT '折扣',`create_time` STRING COMMENT '创建时间',`range_type` STRING COMMENT '范围类型 1、商品 2、品类 3、品牌',`limit_num` BIGINT COMMENT '最多领用次数',`taken_count` BIGINT COMMENT '已领用次数',`start_time` STRING COMMENT '开始领取时间',`end_time` STRING COMMENT '结束领取时间',`operate_time` STRING COMMENT '修改时间',`expire_time` STRING COMMENT '过期时间'
) COMMENT '优惠券表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_coupon_info/';-- 优惠券领用表
DROP TABLE IF EXISTS ods_coupon_use;
CREATE EXTERNAL TABLE ods_coupon_use(`id` STRING COMMENT '编号',`coupon_id` STRING  COMMENT '优惠券ID',`user_id` STRING  COMMENT 'skuid',`order_id` STRING  COMMENT 'spuid',`coupon_status` STRING  COMMENT '优惠券状态',`get_time` STRING  COMMENT '领取时间',`using_time` STRING  COMMENT '使用时间(下单)',`used_time` STRING  COMMENT '使用时间(支付)',`expire_time` STRING COMMENT '过期时间'
) COMMENT '优惠券领用表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_coupon_use/';-- 收藏表
DROP TABLE IF EXISTS ods_favor_info;
CREATE EXTERNAL TABLE ods_favor_info(`id` STRING COMMENT '编号',`user_id` STRING COMMENT '用户id',`sku_id` STRING COMMENT 'skuid',`spu_id` STRING COMMENT 'spuid',`is_cancel` STRING COMMENT '是否取消',`create_time` STRING COMMENT '收藏时间',`cancel_time` STRING COMMENT '取消时间'
) COMMENT '商品收藏表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_favor_info/';-- 订单明细表
DROP TABLE IF EXISTS ods_order_detail;
CREATE EXTERNAL TABLE ods_order_detail(`id` STRING COMMENT '编号',`order_id` STRING  COMMENT '订单号',`sku_id` STRING COMMENT '商品id',`sku_name` STRING COMMENT '商品名称',`order_price` DECIMAL(16,2) COMMENT '商品价格',`sku_num` BIGINT COMMENT '商品数量',`create_time` STRING COMMENT '创建时间',`source_type` STRING COMMENT '来源类型',`source_id` STRING COMMENT '来源编号',`split_final_amount` DECIMAL(16,2) COMMENT '分摊最终金额',`split_activity_amount` DECIMAL(16,2) COMMENT '分摊活动优惠',`split_coupon_amount` DECIMAL(16,2) COMMENT '分摊优惠券优惠'
) COMMENT '订单详情表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_order_detail/';-- 订单明细活动关联表
DROP TABLE IF EXISTS ods_order_detail_activity;
CREATE EXTERNAL TABLE ods_order_detail_activity(`id` STRING COMMENT '编号',`order_id` STRING  COMMENT '订单号',`order_detail_id` STRING COMMENT '订单明细id',`activity_id` STRING COMMENT '活动id',`activity_rule_id` STRING COMMENT '活动规则id',`sku_id` BIGINT COMMENT '商品id',`create_time` STRING COMMENT '创建时间'
) COMMENT '订单详情活动关联表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_order_detail_activity/';-- 订单明细优惠券关联表
DROP TABLE IF EXISTS ods_order_detail_coupon;
CREATE EXTERNAL TABLE ods_order_detail_coupon(`id` STRING COMMENT '编号',`order_id` STRING  COMMENT '订单号',`order_detail_id` STRING COMMENT '订单明细id',`coupon_id` STRING COMMENT '优惠券id',`coupon_use_id` STRING COMMENT '优惠券领用记录id',`sku_id` STRING COMMENT '商品id',`create_time` STRING COMMENT '创建时间'
) COMMENT '订单详情活动关联表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_order_detail_coupon/';-- 订单表
DROP TABLE IF EXISTS ods_order_info;
CREATE EXTERNAL TABLE ods_order_info (`id` STRING COMMENT '订单号',`final_amount` DECIMAL(16,2) COMMENT '订单最终金额',`order_status` STRING COMMENT '订单状态',`user_id` STRING COMMENT '用户id',`payment_way` STRING COMMENT '支付方式',`delivery_address` STRING COMMENT '送货地址',`out_trade_no` STRING COMMENT '支付流水号',`create_time` STRING COMMENT '创建时间',`operate_time` STRING COMMENT '操作时间',`expire_time` STRING COMMENT '过期时间',`tracking_no` STRING COMMENT '物流单编号',`province_id` STRING COMMENT '省份ID',`activity_reduce_amount` DECIMAL(16,2) COMMENT '活动减免金额',`coupon_reduce_amount` DECIMAL(16,2) COMMENT '优惠券减免金额',`original_amount` DECIMAL(16,2)  COMMENT '订单原价金额',`feight_fee` DECIMAL(16,2)  COMMENT '运费',`feight_fee_reduce` DECIMAL(16,2)  COMMENT '运费减免'
) COMMENT '订单表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_order_info/';-- 退单表
DROP TABLE IF EXISTS ods_order_refund_info;
CREATE EXTERNAL TABLE ods_order_refund_info(`id` STRING COMMENT '编号',`user_id` STRING COMMENT '用户ID',`order_id` STRING COMMENT '订单ID',`sku_id` STRING COMMENT '商品ID',`refund_type` STRING COMMENT '退单类型',`refund_num` BIGINT COMMENT '退单件数',`refund_amount` DECIMAL(16,2) COMMENT '退单金额',`refund_reason_type` STRING COMMENT '退单原因类型',`refund_status` STRING COMMENT '退单状态',--退单状态应包含买家申请、卖家审核、卖家收货、退款完成等状态。此处未涉及到，故该表按增量处理`create_time` STRING COMMENT '退单时间'
) COMMENT '退单表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_order_refund_info/';-- 订单状态日志表
DROP TABLE IF EXISTS ods_order_status_log;
CREATE EXTERNAL TABLE ods_order_status_log (`id` STRING COMMENT '编号',`order_id` STRING COMMENT '订单ID',`order_status` STRING COMMENT '订单状态',`operate_time` STRING COMMENT '修改时间'
)  COMMENT '订单状态表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_order_status_log/';-- 支付表
DROP TABLE IF EXISTS ods_payment_info;
CREATE EXTERNAL TABLE ods_payment_info(`id` STRING COMMENT '编号',`out_trade_no` STRING COMMENT '对外业务编号',`order_id` STRING COMMENT '订单编号',`user_id` STRING COMMENT '用户编号',`payment_type` STRING COMMENT '支付类型',`trade_no` STRING COMMENT '交易编号',`payment_amount` DECIMAL(16,2) COMMENT '支付金额',`subject` STRING COMMENT '交易内容',`payment_status` STRING COMMENT '支付状态',`create_time` STRING COMMENT '创建时间',`callback_time` STRING COMMENT '回调时间'
)  COMMENT '支付流水表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_payment_info/';-- 退款表
DROP TABLE IF EXISTS ods_refund_payment;
CREATE EXTERNAL TABLE ods_refund_payment(`id` STRING COMMENT '编号',`out_trade_no` STRING COMMENT '对外业务编号',`order_id` STRING COMMENT '订单编号',`sku_id` STRING COMMENT 'SKU编号',`payment_type` STRING COMMENT '支付类型',`trade_no` STRING COMMENT '交易编号',`refund_amount` DECIMAL(16,2) COMMENT '支付金额',`subject` STRING COMMENT '交易内容',`refund_status` STRING COMMENT '支付状态',`create_time` STRING COMMENT '创建时间',`callback_time` STRING COMMENT '回调时间'
)  COMMENT '支付流水表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_refund_payment/';-- 商品平台属性表
DROP TABLE IF EXISTS ods_sku_attr_value;
CREATE EXTERNAL TABLE ods_sku_attr_value(`id` STRING COMMENT '编号',`attr_id` STRING COMMENT '平台属性ID',`value_id` STRING COMMENT '平台属性值ID',`sku_id` STRING COMMENT '商品ID',`attr_name` STRING COMMENT '平台属性名称',`value_name` STRING COMMENT '平台属性值名称'
) COMMENT 'sku平台属性表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_sku_attr_value/';-- 商品（SKU）表
DROP TABLE IF EXISTS ods_sku_info;
CREATE EXTERNAL TABLE ods_sku_info(`id` STRING COMMENT 'skuId',`spu_id` STRING COMMENT 'spuid',`price` DECIMAL(16,2) COMMENT '价格',`sku_name` STRING COMMENT '商品名称',`sku_desc` STRING COMMENT '商品描述',`weight` DECIMAL(16,2) COMMENT '重量',`tm_id` STRING COMMENT '品牌id',`category3_id` STRING COMMENT '品类id',`is_sale` STRING COMMENT '是否在售',`create_time` STRING COMMENT '创建时间'
) COMMENT 'SKU商品表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_sku_info/';-- 商品销售属性表
DROP TABLE IF EXISTS ods_sku_sale_attr_value;
CREATE EXTERNAL TABLE ods_sku_sale_attr_value(`id` STRING COMMENT '编号',`sku_id` STRING COMMENT 'sku_id',`spu_id` STRING COMMENT 'spu_id',`sale_attr_value_id` STRING COMMENT '销售属性值id',`sale_attr_id` STRING COMMENT '销售属性id',`sale_attr_name` STRING COMMENT '销售属性名称',`sale_attr_value_name` STRING COMMENT '销售属性值名称'
) COMMENT 'sku销售属性名称'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_sku_sale_attr_value/';-- 商品（SPU）表
DROP TABLE IF EXISTS ods_spu_info;
CREATE EXTERNAL TABLE ods_spu_info(`id` STRING COMMENT 'spuid',`spu_name` STRING COMMENT 'spu名称',`category3_id` STRING COMMENT '品类id',`tm_id` STRING COMMENT '品牌id'
) COMMENT 'SPU商品表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_spu_info/';-- 用户表
DROP TABLE IF EXISTS ods_user_info;
CREATE EXTERNAL TABLE ods_user_info(`id` STRING COMMENT '用户id',`login_name` STRING COMMENT '用户名称',`nick_name` STRING COMMENT '用户昵称',`name` STRING COMMENT '用户姓名',`phone_num` STRING COMMENT '手机号码',`email` STRING COMMENT '邮箱',`user_level` STRING COMMENT '用户等级',`birthday` STRING COMMENT '生日',`gender` STRING COMMENT '性别',`create_time` STRING COMMENT '创建时间',`operate_time` STRING COMMENT '操作时间'
) COMMENT '用户表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ods/ods_user_info/';

# hdfs上数据还在，但元数据没有了，修复元数据
msck repair table 表名；

2 ODS层业务表首日数据装载脚本

# 在/home/hzy/bin目录下创建脚本hdfs_to_ods_db_init.sh
vim hdfs_to_ods_db_init.sh
# 在脚本中填写如下内容

#!/bin/bashAPP=gmall# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;thendo_date=$2
else echo "请传入日期参数"exit
fi ods_order_info=" 
load data inpath '/origin_data/$APP/db/order_info/$do_date' OVERWRITE into table ${APP}.ods_order_info partition(dt='$do_date');"ods_order_detail="
load data inpath '/origin_data/$APP/db/order_detail/$do_date' OVERWRITE into table ${APP}.ods_order_detail partition(dt='$do_date');"ods_sku_info="
load data inpath '/origin_data/$APP/db/sku_info/$do_date' OVERWRITE into table ${APP}.ods_sku_info partition(dt='$do_date');"ods_user_info="
load data inpath '/origin_data/$APP/db/user_info/$do_date' OVERWRITE into table ${APP}.ods_user_info partition(dt='$do_date');"ods_payment_info="
load data inpath '/origin_data/$APP/db/payment_info/$do_date' OVERWRITE into table ${APP}.ods_payment_info partition(dt='$do_date');"ods_base_category1="
load data inpath '/origin_data/$APP/db/base_category1/$do_date' OVERWRITE into table ${APP}.ods_base_category1 partition(dt='$do_date');"ods_base_category2="
load data inpath '/origin_data/$APP/db/base_category2/$do_date' OVERWRITE into table ${APP}.ods_base_category2 partition(dt='$do_date');"ods_base_category3="
load data inpath '/origin_data/$APP/db/base_category3/$do_date' OVERWRITE into table ${APP}.ods_base_category3 partition(dt='$do_date'); "ods_base_trademark="
load data inpath '/origin_data/$APP/db/base_trademark/$do_date' OVERWRITE into table ${APP}.ods_base_trademark partition(dt='$do_date'); "ods_activity_info="
load data inpath '/origin_data/$APP/db/activity_info/$do_date' OVERWRITE into table ${APP}.ods_activity_info partition(dt='$do_date'); "ods_cart_info="
load data inpath '/origin_data/$APP/db/cart_info/$do_date' OVERWRITE into table ${APP}.ods_cart_info partition(dt='$do_date'); "ods_comment_info="
load data inpath '/origin_data/$APP/db/comment_info/$do_date' OVERWRITE into table ${APP}.ods_comment_info partition(dt='$do_date'); "ods_coupon_info="
load data inpath '/origin_data/$APP/db/coupon_info/$do_date' OVERWRITE into table ${APP}.ods_coupon_info partition(dt='$do_date'); "ods_coupon_use="
load data inpath '/origin_data/$APP/db/coupon_use/$do_date' OVERWRITE into table ${APP}.ods_coupon_use partition(dt='$do_date'); "ods_favor_info="
load data inpath '/origin_data/$APP/db/favor_info/$do_date' OVERWRITE into table ${APP}.ods_favor_info partition(dt='$do_date'); "ods_order_refund_info="
load data inpath '/origin_data/$APP/db/order_refund_info/$do_date' OVERWRITE into table ${APP}.ods_order_refund_info partition(dt='$do_date'); "ods_order_status_log="
load data inpath '/origin_data/$APP/db/order_status_log/$do_date' OVERWRITE into table ${APP}.ods_order_status_log partition(dt='$do_date'); "ods_spu_info="
load data inpath '/origin_data/$APP/db/spu_info/$do_date' OVERWRITE into table ${APP}.ods_spu_info partition(dt='$do_date'); "ods_activity_rule="
load data inpath '/origin_data/$APP/db/activity_rule/$do_date' OVERWRITE into table ${APP}.ods_activity_rule partition(dt='$do_date');" ods_base_dic="
load data inpath '/origin_data/$APP/db/base_dic/$do_date' OVERWRITE into table ${APP}.ods_base_dic partition(dt='$do_date'); "ods_order_detail_activity="
load data inpath '/origin_data/$APP/db/order_detail_activity/$do_date' OVERWRITE into table ${APP}.ods_order_detail_activity partition(dt='$do_date'); "ods_order_detail_coupon="
load data inpath '/origin_data/$APP/db/order_detail_coupon/$do_date' OVERWRITE into table ${APP}.ods_order_detail_coupon partition(dt='$do_date'); "ods_refund_payment="
load data inpath '/origin_data/$APP/db/refund_payment/$do_date' OVERWRITE into table ${APP}.ods_refund_payment partition(dt='$do_date'); "ods_sku_attr_value="
load data inpath '/origin_data/$APP/db/sku_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_attr_value partition(dt='$do_date'); "ods_sku_sale_attr_value="
load data inpath '/origin_data/$APP/db/sku_sale_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_sale_attr_value partition(dt='$do_date'); "ods_base_province=" 
load data inpath '/origin_data/$APP/db/base_province/$do_date' OVERWRITE into table ${APP}.ods_base_province;"ods_base_region="
load data inpath '/origin_data/$APP/db/base_region/$do_date' OVERWRITE into table ${APP}.ods_base_region;"case $1 in"ods_order_info"){hive -e "$ods_order_info"};;"ods_order_detail"){hive -e "$ods_order_detail"};;"ods_sku_info"){hive -e "$ods_sku_info"};;"ods_user_info"){hive -e "$ods_user_info"};;"ods_payment_info"){hive -e "$ods_payment_info"};;"ods_base_category1"){hive -e "$ods_base_category1"};;"ods_base_category2"){hive -e "$ods_base_category2"};;"ods_base_category3"){hive -e "$ods_base_category3"};;"ods_base_trademark"){hive -e "$ods_base_trademark"};;"ods_activity_info"){hive -e "$ods_activity_info"};;"ods_cart_info"){hive -e "$ods_cart_info"};;"ods_comment_info"){hive -e "$ods_comment_info"};;"ods_coupon_info"){hive -e "$ods_coupon_info"};;"ods_coupon_use"){hive -e "$ods_coupon_use"};;"ods_favor_info"){hive -e "$ods_favor_info"};;"ods_order_refund_info"){hive -e "$ods_order_refund_info"};;"ods_order_status_log"){hive -e "$ods_order_status_log"};;"ods_spu_info"){hive -e "$ods_spu_info"};;"ods_activity_rule"){hive -e "$ods_activity_rule"};;"ods_base_dic"){hive -e "$ods_base_dic"};;"ods_order_detail_activity"){hive -e "$ods_order_detail_activity"};;"ods_order_detail_coupon"){hive -e "$ods_order_detail_coupon"};;"ods_refund_payment"){hive -e "$ods_refund_payment"};;"ods_sku_attr_value"){hive -e "$ods_sku_attr_value"};;"ods_sku_sale_attr_value"){hive -e "$ods_sku_sale_attr_value"};;"ods_base_province"){hive -e "$ods_base_province"};;"ods_base_region"){hive -e "$ods_base_region"};;"all"){hive -e "$ods_order_info$ods_order_detail$ods_sku_info$ods_user_info$ods_payment_info$ods_base_category1$ods_base_category2$ods_base_category3$ods_base_trademark$ods_activity_info$ods_cart_info$ods_comment_info$ods_coupon_info$ods_coupon_use$ods_favor_info$ods_order_refund_info$ods_order_status_log$ods_spu_info$ods_activity_rule$ods_base_dic$ods_order_detail_activity$ods_order_detail_coupon$ods_refund_payment$ods_sku_attr_value$ods_sku_sale_attr_value$ods_base_province$ods_base_region"};;
esac

chmod +x hdfs_to_ods_db_init.sh
# 执行脚本
hdfs_to_ods_db_init.sh all 2020-06-14

3 ODS层业务表每日数据装载脚本

vim hdfs_to_ods_db.sh

#!/bin/bashAPP=gmall# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;thendo_date=$2
else do_date=`date -d "-1 day" +%F`
fiods_order_info=" 
load data inpath '/origin_data/$APP/db/order_info/$do_date' OVERWRITE into table ${APP}.ods_order_info partition(dt='$do_date');"ods_order_detail="
load data inpath '/origin_data/$APP/db/order_detail/$do_date' OVERWRITE into table ${APP}.ods_order_detail partition(dt='$do_date');"ods_sku_info="
load data inpath '/origin_data/$APP/db/sku_info/$do_date' OVERWRITE into table ${APP}.ods_sku_info partition(dt='$do_date');"ods_user_info="
load data inpath '/origin_data/$APP/db/user_info/$do_date' OVERWRITE into table ${APP}.ods_user_info partition(dt='$do_date');"ods_payment_info="
load data inpath '/origin_data/$APP/db/payment_info/$do_date' OVERWRITE into table ${APP}.ods_payment_info partition(dt='$do_date');"ods_base_category1="
load data inpath '/origin_data/$APP/db/base_category1/$do_date' OVERWRITE into table ${APP}.ods_base_category1 partition(dt='$do_date');"ods_base_category2="
load data inpath '/origin_data/$APP/db/base_category2/$do_date' OVERWRITE into table ${APP}.ods_base_category2 partition(dt='$do_date');"ods_base_category3="
load data inpath '/origin_data/$APP/db/base_category3/$do_date' OVERWRITE into table ${APP}.ods_base_category3 partition(dt='$do_date'); "ods_base_trademark="
load data inpath '/origin_data/$APP/db/base_trademark/$do_date' OVERWRITE into table ${APP}.ods_base_trademark partition(dt='$do_date'); "ods_activity_info="
load data inpath '/origin_data/$APP/db/activity_info/$do_date' OVERWRITE into table ${APP}.ods_activity_info partition(dt='$do_date'); "ods_cart_info="
load data inpath '/origin_data/$APP/db/cart_info/$do_date' OVERWRITE into table ${APP}.ods_cart_info partition(dt='$do_date'); "ods_comment_info="
load data inpath '/origin_data/$APP/db/comment_info/$do_date' OVERWRITE into table ${APP}.ods_comment_info partition(dt='$do_date'); "ods_coupon_info="
load data inpath '/origin_data/$APP/db/coupon_info/$do_date' OVERWRITE into table ${APP}.ods_coupon_info partition(dt='$do_date'); "ods_coupon_use="
load data inpath '/origin_data/$APP/db/coupon_use/$do_date' OVERWRITE into table ${APP}.ods_coupon_use partition(dt='$do_date'); "ods_favor_info="
load data inpath '/origin_data/$APP/db/favor_info/$do_date' OVERWRITE into table ${APP}.ods_favor_info partition(dt='$do_date'); "ods_order_refund_info="
load data inpath '/origin_data/$APP/db/order_refund_info/$do_date' OVERWRITE into table ${APP}.ods_order_refund_info partition(dt='$do_date'); "ods_order_status_log="
load data inpath '/origin_data/$APP/db/order_status_log/$do_date' OVERWRITE into table ${APP}.ods_order_status_log partition(dt='$do_date'); "ods_spu_info="
load data inpath '/origin_data/$APP/db/spu_info/$do_date' OVERWRITE into table ${APP}.ods_spu_info partition(dt='$do_date'); "ods_activity_rule="
load data inpath '/origin_data/$APP/db/activity_rule/$do_date' OVERWRITE into table ${APP}.ods_activity_rule partition(dt='$do_date');" ods_base_dic="
load data inpath '/origin_data/$APP/db/base_dic/$do_date' OVERWRITE into table ${APP}.ods_base_dic partition(dt='$do_date'); "ods_order_detail_activity="
load data inpath '/origin_data/$APP/db/order_detail_activity/$do_date' OVERWRITE into table ${APP}.ods_order_detail_activity partition(dt='$do_date'); "ods_order_detail_coupon="
load data inpath '/origin_data/$APP/db/order_detail_coupon/$do_date' OVERWRITE into table ${APP}.ods_order_detail_coupon partition(dt='$do_date'); "ods_refund_payment="
load data inpath '/origin_data/$APP/db/refund_payment/$do_date' OVERWRITE into table ${APP}.ods_refund_payment partition(dt='$do_date'); "ods_sku_attr_value="
load data inpath '/origin_data/$APP/db/sku_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_attr_value partition(dt='$do_date'); "ods_sku_sale_attr_value="
load data inpath '/origin_data/$APP/db/sku_sale_attr_value/$do_date' OVERWRITE into table ${APP}.ods_sku_sale_attr_value partition(dt='$do_date'); "ods_base_province=" 
load data inpath '/origin_data/$APP/db/base_province/$do_date' OVERWRITE into table ${APP}.ods_base_province;"ods_base_region="
load data inpath '/origin_data/$APP/db/base_region/$do_date' OVERWRITE into table ${APP}.ods_base_region;"case $1 in"ods_order_info"){hive -e "$ods_order_info"};;"ods_order_detail"){hive -e "$ods_order_detail"};;"ods_sku_info"){hive -e "$ods_sku_info"};;"ods_user_info"){hive -e "$ods_user_info"};;"ods_payment_info"){hive -e "$ods_payment_info"};;"ods_base_category1"){hive -e "$ods_base_category1"};;"ods_base_category2"){hive -e "$ods_base_category2"};;"ods_base_category3"){hive -e "$ods_base_category3"};;"ods_base_trademark"){hive -e "$ods_base_trademark"};;"ods_activity_info"){hive -e "$ods_activity_info"};;"ods_cart_info"){hive -e "$ods_cart_info"};;"ods_comment_info"){hive -e "$ods_comment_info"};;"ods_coupon_info"){hive -e "$ods_coupon_info"};;"ods_coupon_use"){hive -e "$ods_coupon_use"};;"ods_favor_info"){hive -e "$ods_favor_info"};;"ods_order_refund_info"){hive -e "$ods_order_refund_info"};;"ods_order_status_log"){hive -e "$ods_order_status_log"};;"ods_spu_info"){hive -e "$ods_spu_info"};;"ods_activity_rule"){hive -e "$ods_activity_rule"};;"ods_base_dic"){hive -e "$ods_base_dic"};;"ods_order_detail_activity"){hive -e "$ods_order_detail_activity"};;"ods_order_detail_coupon"){hive -e "$ods_order_detail_coupon"};;"ods_refund_payment"){hive -e "$ods_refund_payment"};;"ods_sku_attr_value"){hive -e "$ods_sku_attr_value"};;"ods_sku_sale_attr_value"){hive -e "$ods_sku_sale_attr_value"};;"all"){hive -e "$ods_order_info$ods_order_detail$ods_sku_info$ods_user_info$ods_payment_info$ods_base_category1$ods_base_category2$ods_base_category3$ods_base_trademark$ods_activity_info$ods_cart_info$ods_comment_info$ods_coupon_info$ods_coupon_use$ods_favor_info$ods_order_refund_info$ods_order_status_log$ods_spu_info$ods_activity_rule$ods_base_dic$ods_order_detail_activity$ods_order_detail_coupon$ods_refund_payment$ods_sku_attr_value$ods_sku_sale_attr_value"};;
esac

# 修改权限
chmod +x hdfs_to_ods_db.sh
# 执行脚本
hdfs_to_ods_db.sh all 2020-06-14
# 查看数据是否导入成功
# origin_data/gmall/db/表名 中的数据没有了
# 从idea中查看数据