大数据基础设施搭建 - 数据装载

一、概述

入仓时机：业务数据、日志数据都同步到HDFS后。即Sqoop同步业务全量表完成后。
业务数据进入HDFS时机：全量数据每天凌晨将昨天的数据同步到HDFS昨天目录。增量数据实时同步到HDFS到binlog对应的日期目录。
日志数据进入HDFS时机：实时同步到日志行为发生的日期，JSON串中的ts字段解析出来的日期。

二、数据装载（HDFS -> Hive）

2.1 创建Hive表

共28张表：16张业务全量表、11张业务增量表、1张流量增量表。
注意1：Hive表默认是TextFile格式。
注意2：省份表和地区表为非分区表。

2.1.1 业务全量表建表语句

DROP TABLE IF EXISTS ods_sku_info_full;
CREATE EXTERNAL TABLE ods_sku_info_full(`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/dw_ods.db/ods_sku_info_full/';DROP TABLE IF EXISTS ods_base_category1_full;
CREATE EXTERNAL TABLE ods_base_category1_full(`id` STRING COMMENT 'id',`name` STRING COMMENT '名称'
) COMMENT '商品一级分类表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/dw_ods.db/ods_base_category1_full/';DROP TABLE IF EXISTS ods_base_category2_full;
CREATE EXTERNAL TABLE ods_base_category2_full(`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/dw_ods.db/ods_base_category2_full/';DROP TABLE IF EXISTS ods_base_category3_full;
CREATE EXTERNAL TABLE ods_base_category3_full(`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/dw_ods.db/ods_base_category3_full/';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/dw_ods.db/ods_base_province/';DROP TABLE IF EXISTS ods_base_trademark_full;
CREATE EXTERNAL TABLE ods_base_trademark_full (`id` STRING COMMENT '编号',`tm_name` STRING COMMENT '品牌名称'
)  COMMENT '品牌表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/dw_ods.db/ods_base_trademark_full/';DROP TABLE IF EXISTS ods_spu_info_full;
CREATE EXTERNAL TABLE ods_spu_info_full(`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/dw_ods.db/ods_spu_info_full/';DROP TABLE IF EXISTS ods_favor_info_full;
CREATE EXTERNAL TABLE ods_favor_info_full(`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/dw_ods.db/ods_favor_info_full/';DROP TABLE IF EXISTS ods_cart_info_full;
CREATE EXTERNAL TABLE ods_cart_info_full(`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/dw_ods.db/ods_cart_info_full/';DROP TABLE IF EXISTS ods_coupon_info_full;
CREATE EXTERNAL TABLE ods_coupon_info_full(`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/dw_ods.db/ods_coupon_info_full/';DROP TABLE IF EXISTS ods_activity_info_full;
CREATE EXTERNAL TABLE ods_activity_info_full(`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/dw_ods.db/ods_activity_info_full/';DROP TABLE IF EXISTS ods_activity_rule_full;
CREATE EXTERNAL TABLE ods_activity_rule_full(`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/dw_ods.db/ods_activity_rule_full/';DROP TABLE IF EXISTS ods_base_dic_full;
CREATE EXTERNAL TABLE ods_base_dic_full(`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/dw_ods.db/ods_base_dic_full/';DROP TABLE IF EXISTS ods_sku_attr_value_full;
CREATE EXTERNAL TABLE ods_sku_attr_value_full(`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/dw_ods.db/ods_sku_attr_value_full/';DROP TABLE IF EXISTS ods_sku_sale_attr_value_full;
CREATE EXTERNAL TABLE ods_sku_sale_attr_value_full(`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/dw_ods.db/ods_sku_sale_attr_value_full/';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/dw_ods.db/ods_base_region/';

2.1.2 业务增量表建表语句

DROP TABLE IF EXISTS ods_order_info_inc;
CREATE EXTERNAL TABLE ods_order_info_inc (`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/dw_ods.db/ods_order_info_inc/';DROP TABLE IF EXISTS ods_coupon_use_inc;
CREATE EXTERNAL TABLE ods_coupon_use_inc(`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/dw_ods.db/ods_coupon_use_inc/';DROP TABLE IF EXISTS ods_order_status_log_inc;
CREATE EXTERNAL TABLE ods_order_status_log_inc (`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/dw_ods.db/ods_order_status_log_inc/';DROP TABLE IF EXISTS ods_user_info_inc;
CREATE EXTERNAL TABLE ods_user_info_inc(`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/dw_ods.db/ods_user_info_inc/';DROP TABLE IF EXISTS ods_order_detail_inc;
CREATE EXTERNAL TABLE ods_order_detail_inc(`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/dw_ods.db/ods_order_detail_inc/';DROP TABLE IF EXISTS ods_payment_info_inc;
CREATE EXTERNAL TABLE ods_payment_info_inc(`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/dw_ods.db/ods_payment_info_inc/';DROP TABLE IF EXISTS ods_comment_info_inc;
CREATE EXTERNAL TABLE ods_comment_info_inc(`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/dw_ods.db/ods_comment_info_inc/';DROP TABLE IF EXISTS ods_order_refund_info_inc;
CREATE EXTERNAL TABLE ods_order_refund_info_inc(`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/dw_ods.db/ods_order_refund_info_inc/';DROP TABLE IF EXISTS ods_order_detail_activity_inc;
CREATE EXTERNAL TABLE ods_order_detail_activity_inc(`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/dw_ods.db/ods_order_detail_activity_inc/';DROP TABLE IF EXISTS ods_order_detail_coupon_inc;
CREATE EXTERNAL TABLE ods_order_detail_coupon_inc(`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/dw_ods.db/ods_order_detail_coupon_inc/';DROP TABLE IF EXISTS ods_refund_payment_inc;
CREATE EXTERNAL TABLE ods_refund_payment_inc(`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/dw_ods.db/ods_refund_payment_inc/';

2.1.3 流量增量表建表语句

DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc (`line` string)
PARTITIONED BY (`dt` string)
LOCATION '/warehouse/dw_ods.db/ods_log_inc/';

2.2 数据装载

省份表和城市表只需首次导入一次，其他业务表需要每天都装载。

2.2.1 初始化装载省份和地区表

[hadoop@hadoop102 ~]$ hive -e "load data inpath '/warehouse/db/gmall/base_province_full/2023-12-02' OVERWRITE into table dw_ods.ods_base_province;"
[hadoop@hadoop102 ~]$ hive -e "load data inpath '/warehouse/db/gmall/base_region_full/2023-12-02' OVERWRITE into table dw_ods.ods_base_region;"

2.2.2 业务数据装载

注意1：要在Sqoop数据同步成功之后运行。
注意2：crontab无法直接处理任务依赖关系！
TODO：如何判定Sqoop同步成功？？？？ 如何在其成功后运行？？？

（1） 开发脚本

[hadoop@hadoop102 ~]$ cd /home/hadoop/bin/
[hadoop@hadoop102 bin]$ vim hdfs_to_ods_db.sh

脚本内容：

#!/bin/bashAPP=dw_ods# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;thendo_date=$2
else do_date=`date -d "-1 day" +%F`
fisku_info=" 
load data inpath '/warehouse/db/gmall/sku_info_full/$do_date' OVERWRITE into table ${APP}.ods_sku_info_full partition(dt='$do_date');"base_category1="
load data inpath '/warehouse/db/gmall/base_category1_full/$do_date' OVERWRITE into table ${APP}.ods_base_category1_full partition(dt='$do_date');"base_category2="
load data inpath '/warehouse/db/gmall/base_category2_full/$do_date' OVERWRITE into table ${APP}.ods_base_category2_full partition(dt='$do_date');"base_category3="
load data inpath '/warehouse/db/gmall/base_category3_full/$do_date' OVERWRITE into table ${APP}.ods_base_category3_full partition(dt='$do_date');"base_trademark="
load data inpath '/warehouse/db/gmall/base_trademark_full/$do_date' OVERWRITE into table ${APP}.ods_base_trademark_full partition(dt='$do_date');"spu_info="
load data inpath '/warehouse/db/gmall/spu_info_full/$do_date' OVERWRITE into table ${APP}.ods_spu_info_full partition(dt='$do_date');"favor_info="
load data inpath '/warehouse/db/gmall/favor_info_full/$do_date' OVERWRITE into table ${APP}.ods_favor_info_full partition(dt='$do_date'); "cart_info="
load data inpath '/warehouse/db/gmall/cart_info_full/$do_date' OVERWRITE into table ${APP}.ods_cart_info_full partition(dt='$do_date'); "coupon_info="
load data inpath '/warehouse/db/gmall/coupon_info_full/$do_date' OVERWRITE into table ${APP}.ods_coupon_info_full partition(dt='$do_date'); "activity_info="
load data inpath '/warehouse/db/gmall/activity_info_full/$do_date' OVERWRITE into table ${APP}.ods_activity_info_full partition(dt='$do_date'); "activity_rule="
load data inpath '/warehouse/db/gmall/activity_rule_full/$do_date' OVERWRITE into table ${APP}.ods_activity_rule_full partition(dt='$do_date'); "base_dic="
load data inpath '/warehouse/db/gmall/base_dic_full/$do_date' OVERWRITE into table ${APP}.ods_base_dic_full partition(dt='$do_date'); "sku_attr_value="
load data inpath '/warehouse/db/gmall/sku_attr_value_full/$do_date' OVERWRITE into table ${APP}.ods_sku_attr_value_full partition(dt='$do_date'); "sku_sale_attr_value="
load data inpath '/warehouse/db/gmall/sku_sale_attr_value_full/$do_date' OVERWRITE into table ${APP}.ods_sku_sale_attr_value_full partition(dt='$do_date'); "order_info="
load data inpath '/warehouse/db/gmall/order_info_inc/$do_date' OVERWRITE into table ${APP}.ods_order_info_inc partition(dt='$do_date'); "coupon_use="
load data inpath '/warehouse/db/gmall/coupon_use_inc/$do_date' OVERWRITE into table ${APP}.ods_coupon_use_inc partition(dt='$do_date'); "order_status_log="
load data inpath '/warehouse/db/gmall/order_status_log_inc/$do_date' OVERWRITE into table ${APP}.ods_order_status_log_inc partition(dt='$do_date');" user_info="
load data inpath '/warehouse/db/gmall/user_info_inc/$do_date' OVERWRITE into table ${APP}.ods_user_info_inc partition(dt='$do_date'); "order_detail="
load data inpath '/warehouse/db/gmall/order_detail_inc/$do_date' OVERWRITE into table ${APP}.ods_order_detail_inc partition(dt='$do_date'); "payment_info="
load data inpath '/warehouse/db/gmall/payment_info_inc/$do_date' OVERWRITE into table ${APP}.ods_payment_info_inc partition(dt='$do_date'); "comment_info="
load data inpath '/warehouse/db/gmall/comment_info_inc/$do_date' OVERWRITE into table ${APP}.ods_comment_info_inc partition(dt='$do_date'); "order_refund_info="
load data inpath '/warehouse/db/gmall/order_refund_info_inc/$do_date' OVERWRITE into table ${APP}.ods_order_refund_info_inc partition(dt='$do_date'); "order_detail_activity="
load data inpath '/warehouse/db/gmall/order_detail_activity_inc/$do_date' OVERWRITE into table ${APP}.ods_order_detail_activity_inc partition(dt='$do_date'); "order_detail_coupon=" 
load data inpath '/warehouse/db/gmall/order_detail_coupon_inc/$do_date' OVERWRITE into table ${APP}.ods_order_detail_coupon_inc partition(dt='$do_date');"refund_payment="
load data inpath '/warehouse/db/gmall/refund_payment_inc/$do_date' OVERWRITE into table ${APP}.ods_refund_payment_inc partition(dt='$do_date');"case $1 in"sku_info"){hive -e "$sku_info"};;"base_category1"){hive -e "$base_category1"};;"base_category2"){hive -e "$base_category2"};;"base_category3"){hive -e "$base_category3"};;"base_trademark"){hive -e "$base_trademark"};;"spu_info"){hive -e "$spu_info"};;"favor_info"){hive -e "$favor_info"};;"cart_info"){hive -e "$cart_info"};;"coupon_info"){hive -e "$coupon_info"};;"activity_info"){hive -e "$activity_info"};;"activity_rule"){hive -e "$activity_rule"};;"base_dic"){hive -e "$base_dic"};;"sku_attr_value"){hive -e "$sku_attr_value"};;"sku_sale_attr_value"){hive -e "$sku_sale_attr_value"};;"order_info"){hive -e "$order_info"};;"coupon_use"){hive -e "$coupon_use"};;"order_status_log"){hive -e "$order_status_log"};;"user_info"){hive -e "$user_info"};;"order_detail"){hive -e "$order_detail"};;"payment_info"){hive -e "$payment_info"};;"comment_info"){hive -e "$comment_info"};;"order_refund_info"){hive -e "$order_refund_info"};;"order_detail_activity"){hive -e "$order_detail_activity"};;"order_detail_coupon"){hive -e "$order_detail_coupon"};;"refund_payment"){hive -e "$refund_payment"};;"all"){hive -e "$sku_info$base_category1$base_category2$base_category3$base_trademark$spu_info$favor_info$cart_info$coupon_info$activity_info$activity_rule$base_dic$sku_attr_value$sku_sale_attr_value$order_info$coupon_use$order_status_log$user_info$order_detail$payment_info$comment_info$order_refund_info$order_detail_activity$order_detail_coupon$refund_payment"};;
esac

（2） 授予脚本执行权限

[hadoop@hadoop102 bin]$ chmod +x hdfs_to_ods_db.sh

（3） 定时调度

每天凌晨3点装载业务数据

[hadoop@hadoop102 ~]$ crontab -e

新增内容：

# 每天凌晨3点装载业务数据
0 3 * * * hdfs_to_ods_db.sh all

2.2.3 日志数据装载

（1） 开发脚本

[hadoop@hadoop102 ~]$ cd /home/hadoop/bin/
[hadoop@hadoop102 bin]$ vim hdfs_to_ods_log.sh

脚本内容：

#!/bin/bash# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;thendo_date=$1
else do_date=`date -d "-1 day" +%F`
fi echo ================== 日志日期为 $do_date ==================
sql="
load data inpath '/warehouse/applog/gmall/tracking_log/$do_date' into table dw_ods.ods_log_inc partition(dt='$do_date');
"hive -e "$sql"

（2） 授予脚本执行权限

[hadoop@hadoop102 bin]$ chmod +x hdfs_to_ods_log.sh

（3） 定时调度

凌晨3点执行，要等昨天的日志数据同步完成。
关键是保证白天时数仓能查到昨天分区的数据。

[hadoop@hadoop102 ~]$ crontab -e

新增内容：

# 每天凌晨3点装载用户日志数据增量表
0 3 * * * hdfs_to_ods_log.sh