离线数仓DWD层

离线数仓DWD层

  • DWD层设计要点：
  • 9.1 交易域加购事务事实表
  • 9.2 交易域下单事务事实表
  • 9.3 交易域取消订单事务事实表
  • 9.4 交易域支付成功事务事实表
  • 9.5 交易域退单事务事实表
  • 9.6 交易域退款成功事务事实表
  • 9.7 交易域购物车周期快照事实表
  • 9.8 工具域优惠券领取事务事实表
  • 9.9 工具域优惠券使用(下单)事务事实表
  • 9.10 工具域优惠券使用(支付)事务事实表
  • 9.11 互动域收藏商品事务事实表
  • 9.12 互动域评价事务事实表
  • 9.13 流量域页面浏览事务事实表
  • 9.14 流量域启动事务事实表
  • 9.15 流量域动作事务事实表
  • 9.16 流量域曝光事务事实表
  • 9.17 流量域错误事务事实表
  • 9.18 用户域用户注册事务事实表
  • 9.19 用户域用户登录事务事实表
  • 9.20 数据装载脚本
    • 9.20.1 首日装载脚本
    • 9.20.2 每日装载脚本

DWD层设计要点：

（1）DWD层的设计依据是维度建模理论，该层存储维度模型的事实表。
（2）DWD层的数据存储格式为orc列式存储+snappy压缩。
（3）DWD层表名的命名规范为dwd_数据域_表名_单分区增量全量标识（inc/full）

9.1 交易域加购事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_trade_cart_add_inc;
CREATE EXTERNAL TABLE dwd_trade_cart_add_inc
(`id`               STRING COMMENT '编号',`user_id`          STRING COMMENT '用户id',`sku_id`           STRING COMMENT '商品id',`date_id`          STRING COMMENT '时间id',`create_time`      STRING COMMENT '加购时间',`source_id`        STRING COMMENT '来源类型ID',`source_type_code` STRING COMMENT '来源类型编码',`source_type_name` STRING COMMENT '来源类型名称',`sku_num`          BIGINT COMMENT '加购物车件数'
) COMMENT '交易域加购物车事务事实表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_trade_cart_add_inc/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）分区规划
在这里插入图片描述
3）数据装载
（1）数据流向
在这里插入图片描述

（2）首日装载

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_cart_add_inc partition (dt)
selectid,user_id,sku_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,source_id,source_type,dic.dic_name,sku_num,date_format(create_time, 'yyyy-MM-dd')
from
(selectdata.id,data.user_id,data.sku_id,data.create_time,data.source_id,data.source_type,data.sku_numfrom ods_cart_info_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
)ci
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-14'and parent_code='24'
)dic
on ci.source_type=dic.dic_code;

（3）每日装载

insert overwrite table dwd_trade_cart_add_inc partition(dt='2020-06-15')
selectid,user_id,sku_id,date_id,create_time,source_id,source_type_code,source_type_name,sku_num
from
(selectdata.id,data.user_id,data.sku_id,date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd HH:mm:ss') create_time,data.source_id,data.source_type source_type_code,if(type='insert',data.sku_num,data.sku_num-old['sku_num']) sku_numfrom ods_cart_info_incwhere dt='2020-06-15'and (type='insert'or(type='update' and old['sku_num'] is not null and data.sku_num>cast(old['sku_num'] as int)))
)cart
left join
(selectdic_code,dic_name source_type_namefrom ods_base_dic_fullwhere dt='2020-06-15'and parent_code='24'
)dic
on cart.source_type_code=dic.dic_code;

9.2 交易域下单事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_trade_order_detail_inc;
CREATE EXTERNAL TABLE dwd_trade_order_detail_inc
(`id`                    STRING COMMENT '编号',`order_id`              STRING COMMENT '订单id',`user_id`               STRING COMMENT '用户id',`sku_id`                STRING COMMENT '商品id',`province_id`           STRING COMMENT '省份id',`activity_id`           STRING COMMENT '参与活动规则id',`activity_rule_id`      STRING COMMENT '参与活动规则id',`coupon_id`             STRING COMMENT '使用优惠券id',`date_id`               STRING COMMENT '下单日期id',`create_time`           STRING COMMENT '下单时间',`source_id`             STRING COMMENT '来源编号',`source_type_code`      STRING COMMENT '来源类型编码',`source_type_name`      STRING COMMENT '来源类型名称',`sku_num`               BIGINT COMMENT '商品数量',`split_original_amount` DECIMAL(16, 2) COMMENT '原始价格',`split_activity_amount` DECIMAL(16, 2) COMMENT '活动优惠分摊',`split_coupon_amount`   DECIMAL(16, 2) COMMENT '优惠券优惠分摊',`split_total_amount`    DECIMAL(16, 2) COMMENT '最终价格分摊'
) COMMENT '交易域下单明细事务事实表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_trade_order_detail_inc/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载
（1）首日装载

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_order_detail_inc partition (dt)
selectod.id,order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,date_format(create_time, 'yyyy-MM-dd') date_id,create_time,source_id,source_type,dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount,date_format(create_time,'yyyy-MM-dd')
from
(selectdata.id,data.order_id,data.sku_id,data.create_time,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ods_order_detail_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
) od
left join
(selectdata.id,data.user_id,data.province_idfrom ods_order_info_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
) oi
on od.order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ods_order_detail_activity_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ods_order_detail_coupon_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-14'and parent_code='24'
)dic
on od.source_type=dic.dic_code;

（2）每日装载

insert overwrite table dwd_trade_order_detail_inc partition (dt='2020-06-15')
selectod.id,order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,date_id,create_time,source_id,source_type,dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount
from
(selectdata.id,data.order_id,data.sku_id,date_format(data.create_time, 'yyyy-MM-dd') date_id,data.create_time,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ods_order_detail_incwhere dt = '2020-06-15'and type = 'insert'
) od
left join
(selectdata.id,data.user_id,data.province_idfrom ods_order_info_incwhere dt = '2020-06-15'and type = 'insert'
) oi
on od.order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ods_order_detail_activity_incwhere dt = '2020-06-15'and type = 'insert'
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ods_order_detail_coupon_incwhere dt = '2020-06-15'and type = 'insert'
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-15'and parent_code='24'
)dic
on od.source_type=dic.dic_code;

9.3 交易域取消订单事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_trade_cancel_detail_inc;
CREATE EXTERNAL TABLE dwd_trade_cancel_detail_inc
(`id`                    STRING COMMENT '编号',`order_id`              STRING COMMENT '订单id',`user_id`               STRING COMMENT '用户id',`sku_id`                STRING COMMENT '商品id',`province_id`           STRING COMMENT '省份id',`activity_id`           STRING COMMENT '参与活动规则id',`activity_rule_id`      STRING COMMENT '参与活动规则id',`coupon_id`             STRING COMMENT '使用优惠券id',`date_id`               STRING COMMENT '取消订单日期id',`cancel_time`           STRING COMMENT '取消订单时间',`source_id`             STRING COMMENT '来源编号',`source_type_code`      STRING COMMENT '来源类型编码',`source_type_name`      STRING COMMENT '来源类型名称',`sku_num`               BIGINT COMMENT '商品数量',`split_original_amount` DECIMAL(16, 2) COMMENT '原始价格',`split_activity_amount` DECIMAL(16, 2) COMMENT '活动优惠分摊',`split_coupon_amount`   DECIMAL(16, 2) COMMENT '优惠券优惠分摊',`split_total_amount`    DECIMAL(16, 2) COMMENT '最终价格分摊'
) COMMENT '交易域取消订单明细事务事实表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_trade_cancel_detail_inc/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载
（1）首日装载

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_cancel_detail_inc partition (dt)
selectod.id,order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,date_format(canel_time,'yyyy-MM-dd') date_id,canel_time,source_id,source_type,dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount,date_format(canel_time,'yyyy-MM-dd')
from
(selectdata.id,data.order_id,data.sku_id,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ods_order_detail_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
) od
join
(selectdata.id,data.user_id,data.province_id,data.operate_time canel_timefrom ods_order_info_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'and data.order_status='1003'
) oi
on od.order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ods_order_detail_activity_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ods_order_detail_coupon_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-14'and parent_code='24'
)dic
on od.source_type=dic.dic_code;

（2）每日装载

insert overwrite table dwd_trade_cancel_detail_inc partition (dt='2020-06-15')
selectod.id,order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,date_format(canel_time,'yyyy-MM-dd') date_id,canel_time,source_id,source_type,dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount
from
(selectdata.id,data.order_id,data.sku_id,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ods_order_detail_incwhere (dt='2020-06-15' or dt=date_add('2020-06-15',-1))and (type = 'insert' or type= 'bootstrap-insert')
) od
join
(selectdata.id,data.user_id,data.province_id,data.operate_time canel_timefrom ods_order_info_incwhere dt = '2020-06-15'and type = 'update'and data.order_status='1003'and array_contains(map_keys(old),'order_status')
) oi
on order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ods_order_detail_activity_incwhere (dt='2020-06-15' or dt=date_add('2020-06-15',-1))and (type = 'insert' or type= 'bootstrap-insert')
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ods_order_detail_coupon_incwhere (dt='2020-06-15' or dt=date_add('2020-06-15',-1))and (type = 'insert' or type= 'bootstrap-insert')
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-15'and parent_code='24'
)dic
on od.source_type=dic.dic_code;

9.4 交易域支付成功事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_trade_pay_detail_suc_inc;
CREATE EXTERNAL TABLE dwd_trade_pay_detail_suc_inc
(`id`                    STRING COMMENT '编号',`order_id`              STRING COMMENT '订单id',`user_id`               STRING COMMENT '用户id',`sku_id`                STRING COMMENT '商品id',`province_id`           STRING COMMENT '省份id',`activity_id`           STRING COMMENT '参与活动规则id',`activity_rule_id`      STRING COMMENT '参与活动规则id',`coupon_id`             STRING COMMENT '使用优惠券id',`payment_type_code`     STRING COMMENT '支付类型编码',`payment_type_name`     STRING COMMENT '支付类型名称',`date_id`               STRING COMMENT '支付日期id',`callback_time`         STRING COMMENT '支付成功时间',`source_id`             STRING COMMENT '来源编号',`source_type_code`      STRING COMMENT '来源类型编码',`source_type_name`      STRING COMMENT '来源类型名称',`sku_num`               BIGINT COMMENT '商品数量',`split_original_amount` DECIMAL(16, 2) COMMENT '应支付原始金额',`split_activity_amount` DECIMAL(16, 2) COMMENT '支付活动优惠分摊',`split_coupon_amount`   DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',`split_payment_amount`  DECIMAL(16, 2) COMMENT '支付金额'
) COMMENT '交易域成功支付事务事实表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_trade_pay_detail_suc_inc/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载
（1）首日装载

insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt)
selectod.id,od.order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,payment_type,pay_dic.dic_name,date_format(callback_time,'yyyy-MM-dd') date_id,callback_time,source_id,source_type,src_dic.dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount,date_format(callback_time,'yyyy-MM-dd')
from
(selectdata.id,data.order_id,data.sku_id,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ods_order_detail_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
) od
join
(selectdata.user_id,data.order_id,data.payment_type,data.callback_timefrom ods_payment_info_incwhere dt='2020-06-14'and type='bootstrap-insert'and data.payment_status='1602'
) pi
on od.order_id=pi.order_id
left join
(selectdata.id,data.province_idfrom ods_order_info_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
) oi
on od.order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ods_order_detail_activity_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ods_order_detail_coupon_incwhere dt = '2020-06-14'and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-14'and parent_code='11'
) pay_dic
on pi.payment_type=pay_dic.dic_code
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-14'and parent_code='24'
)src_dic
on od.source_type=src_dic.dic_code;

（2）每日装载

insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt='2020-06-15')
selectod.id,od.order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,payment_type,pay_dic.dic_name,date_format(callback_time,'yyyy-MM-dd') date_id,callback_time,source_id,source_type,src_dic.dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount
from
(selectdata.id,data.order_id,data.sku_id,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ods_order_detail_incwhere (dt = '2020-06-15' or dt = date_add('2020-06-15',-1))and (type = 'insert' or type = 'bootstrap-insert')
) od
join
(selectdata.user_id,data.order_id,data.payment_type,data.callback_timefrom ods_payment_info_incwhere dt='2020-06-15'and type='update'and array_contains(map_keys(old),'payment_status')and data.payment_status='1602'
) pi
on od.order_id=pi.order_id
left join
(selectdata.id,data.province_idfrom ods_order_info_incwhere (dt = '2020-06-15' or dt = date_add('2020-06-15',-1))and (type = 'insert' or type = 'bootstrap-insert')
) oi
on od.order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ods_order_detail_activity_incwhere (dt = '2020-06-15' or dt = date_add('2020-06-15',-1))and (type = 'insert' or type = 'bootstrap-insert')
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ods_order_detail_coupon_incwhere (dt = '2020-06-15' or dt = date_add('2020-06-15',-1))and (type = 'insert' or type = 'bootstrap-insert')
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-15'and parent_code='11'
) pay_dic
on pi.payment_type=pay_dic.dic_code
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-15'and parent_code='24'
)src_dic
on od.source_type=src_dic.dic_code;

9.5 交易域退单事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_trade_order_refund_inc;
CREATE EXTERNAL TABLE dwd_trade_order_refund_inc
(`id`                      STRING COMMENT '编号',`user_id`                 STRING COMMENT '用户ID',`order_id`                STRING COMMENT '订单ID',`sku_id`                  STRING COMMENT '商品ID',`province_id`             STRING COMMENT '地区ID',`date_id`                 STRING COMMENT '日期ID',`create_time`             STRING COMMENT '退单时间',`refund_type_code`        STRING COMMENT '退单类型编码',`refund_type_name`        STRING COMMENT '退单类型名称',`refund_reason_type_code` STRING COMMENT '退单原因类型编码',`refund_reason_type_name` STRING COMMENT '退单原因类型名称',`refund_reason_txt`       STRING COMMENT '退单原因描述',`refund_num`              BIGINT COMMENT '退单件数',`refund_amount`           DECIMAL(16, 2) COMMENT '退单金额'
) COMMENT '交易域退单事务事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_trade_order_refund_inc/'TBLPROPERTIES ("orc.compress" = "snappy");

2）数据装载
（1）首日装载

insert overwrite table dwd_trade_order_refund_inc partition(dt)
selectri.id,user_id,order_id,sku_id,province_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,refund_type,type_dic.dic_name,refund_reason_type,reason_dic.dic_name,refund_reason_txt,refund_num,refund_amount,date_format(create_time,'yyyy-MM-dd')
from
(selectdata.id,data.user_id,data.order_id,data.sku_id,data.refund_type,data.refund_num,data.refund_amount,data.refund_reason_type,data.refund_reason_txt,data.create_timefrom ods_order_refund_info_incwhere dt='2020-06-14'and type='bootstrap-insert'
)ri
left join
(selectdata.id,data.province_idfrom ods_order_info_incwhere dt='2020-06-14'and type='bootstrap-insert'
)oi
on ri.order_id=oi.id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-14'and parent_code = '15'
)type_dic
on ri.refund_type=type_dic.dic_code
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-14'and parent_code = '13'
)reason_dic
on ri.refund_reason_type=reason_dic.dic_code;
（2）每日装载
insert overwrite table dwd_trade_order_refund_inc partition(dt='2020-06-15')
selectri.id,user_id,order_id,sku_id,province_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,refund_type,type_dic.dic_name,refund_reason_type,reason_dic.dic_name,refund_reason_txt,refund_num,refund_amount
from
(selectdata.id,data.user_id,data.order_id,data.sku_id,data.refund_type,data.refund_num,data.refund_amount,data.refund_reason_type,data.refund_reason_txt,data.create_timefrom ods_order_refund_info_incwhere dt='2020-06-15'and type='insert'
)ri
left join
(selectdata.id,data.province_idfrom ods_order_info_incwhere dt='2020-06-15'and type='update'and data.order_status='1005'and array_contains(map_keys(old),'order_status')
)oi
on ri.order_id=oi.id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-15'and parent_code = '15'
)type_dic
on ri.refund_type=type_dic.dic_code
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-15'and parent_code = '13'
)reason_dic
on ri.refund_reason_type=reason_dic.dic_code;

9.6 交易域退款成功事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_trade_refund_pay_suc_inc;
CREATE EXTERNAL TABLE dwd_trade_refund_pay_suc_inc
(`id`                STRING COMMENT '编号',`user_id`           STRING COMMENT '用户ID',`order_id`          STRING COMMENT '订单编号',`sku_id`            STRING COMMENT 'SKU编号',`province_id`       STRING COMMENT '地区ID',`payment_type_code` STRING COMMENT '支付类型编码',`payment_type_name` STRING COMMENT '支付类型名称',`date_id`           STRING COMMENT '日期ID',`callback_time`     STRING COMMENT '支付成功时间',`refund_num`        DECIMAL(16, 2) COMMENT '退款件数',`refund_amount`     DECIMAL(16, 2) COMMENT '退款金额'
) COMMENT '交易域提交退款成功事务事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_trade_refund_pay_suc_inc/'TBLPROPERTIES ("orc.compress" = "snappy");

2）数据装载
（1）首日装载

insert overwrite table dwd_trade_refund_pay_suc_inc partition(dt)
selectrp.id,user_id,rp.order_id,rp.sku_id,province_id,payment_type,dic_name,date_format(callback_time,'yyyy-MM-dd') date_id,callback_time,refund_num,total_amount,date_format(callback_time,'yyyy-MM-dd')
from
(selectdata.id,data.order_id,data.sku_id,data.payment_type,data.callback_time,data.total_amountfrom ods_refund_payment_incwhere dt='2020-06-14'and type = 'bootstrap-insert'and data.refund_status='1602'
)rp
left join
(selectdata.id,data.user_id,data.province_idfrom ods_order_info_incwhere dt='2020-06-14'and type='bootstrap-insert'
)oi
on rp.order_id=oi.id
left join
(selectdata.order_id,data.sku_id,data.refund_numfrom ods_order_refund_info_incwhere dt='2020-06-14'and type='bootstrap-insert'
)ri
on rp.order_id=ri.order_id
and rp.sku_id=ri.sku_id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-14'and parent_code='11'
)dic
on rp.payment_type=dic.dic_code;

（2）每日装载

insert overwrite table dwd_trade_refund_pay_suc_inc partition(dt='2020-06-15')
selectrp.id,user_id,rp.order_id,rp.sku_id,province_id,payment_type,dic_name,date_format(callback_time,'yyyy-MM-dd') date_id,callback_time,refund_num,total_amount
from
(selectdata.id,data.order_id,data.sku_id,data.payment_type,data.callback_time,data.total_amountfrom ods_refund_payment_incwhere dt='2020-06-15'and type = 'update'and array_contains(map_keys(old),'refund_status')and data.refund_status='1602'
)rp
left join
(selectdata.id,data.user_id,data.province_idfrom ods_order_info_incwhere dt='2020-06-15'and type='update'and data.order_status='1006'and array_contains(map_keys(old),'order_status')
)oi
on rp.order_id=oi.id
left join
(selectdata.order_id,data.sku_id,data.refund_numfrom ods_order_refund_info_incwhere dt='2020-06-15'and type='update'and data.refund_status='0705'and array_contains(map_keys(old),'refund_status')
)ri
on rp.order_id=ri.order_id
and rp.sku_id=ri.sku_id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-15'and parent_code='11'
)dic
on rp.payment_type=dic.dic_code;

9.7 交易域购物车周期快照事实表

1）建表语句

DROP TABLE IF EXISTS dwd_trade_cart_full;
CREATE EXTERNAL TABLE dwd_trade_cart_full
(`id`       STRING COMMENT '编号',`user_id`  STRING COMMENT '用户id',`sku_id`   STRING COMMENT '商品id',`sku_name` STRING COMMENT '商品名称',`sku_num`  BIGINT COMMENT '加购物车件数'
) COMMENT '交易域购物车周期快照事实表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_trade_cart_full/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dwd_trade_cart_full partition(dt='2020-06-14')
selectid,user_id,sku_id,sku_name,sku_num
from ods_cart_info_full
where dt='2020-06-14'
and is_ordered='0';

9.8 工具域优惠券领取事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_tool_coupon_get_inc;
CREATE EXTERNAL TABLE dwd_tool_coupon_get_inc
(`id`        STRING COMMENT '编号',`coupon_id` STRING COMMENT '优惠券ID',`user_id`   STRING COMMENT 'userid',`date_id`   STRING COMMENT '日期ID',`get_time`  STRING COMMENT '领取时间'
) COMMENT '优惠券领取事务事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_tool_coupon_get_inc/'TBLPROPERTIES ("orc.compress" = "snappy");

2）数据装载
（1）首日装载

insert overwrite table dwd_tool_coupon_get_inc partition(dt)
selectdata.id,data.coupon_id,data.user_id,date_format(data.get_time,'yyyy-MM-dd') date_id,data.get_time,date_format(data.get_time,'yyyy-MM-dd')
from ods_coupon_use_inc
where dt='2020-06-14'
and type='bootstrap-insert';

（2）每日装载

insert overwrite table dwd_tool_coupon_get_inc partition (dt='2020-06-15')
selectdata.id,data.coupon_id,data.user_id,date_format(data.get_time,'yyyy-MM-dd') date_id,data.get_time
from ods_coupon_use_inc
where dt='2020-06-15'
and type='insert';

9.9 工具域优惠券使用(下单)事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_tool_coupon_order_inc;
CREATE EXTERNAL TABLE dwd_tool_coupon_order_inc
(`id`         STRING COMMENT '编号',`coupon_id`  STRING COMMENT '优惠券ID',`user_id`    STRING COMMENT 'user_id',`order_id`   STRING COMMENT 'order_id',`date_id`    STRING COMMENT '日期ID',`order_time` STRING COMMENT '使用下单时间'
) COMMENT '优惠券使用下单事务事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_tool_coupon_order_inc/'TBLPROPERTIES ("orc.compress" = "snappy");

2）数据装载
（1）首日装载

insert overwrite table dwd_tool_coupon_order_inc partition(dt)
selectdata.id,data.coupon_id,data.user_id,data.order_id,date_format(data.using_time,'yyyy-MM-dd') date_id,data.using_time,date_format(data.using_time,'yyyy-MM-dd')
from ods_coupon_use_inc
where dt='2020-06-14'
and type='bootstrap-insert'
and data.using_time is not null;

（2）每日装载

insert overwrite table dwd_tool_coupon_order_inc partition(dt='2020-06-15')
selectdata.id,data.coupon_id,data.user_id,data.order_id,date_format(data.using_time,'yyyy-MM-dd') date_id,data.using_time
from ods_coupon_use_inc
where dt='2020-06-15'
and type='update'
and array_contains(map_keys(old),'using_time');

9.10 工具域优惠券使用(支付)事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_tool_coupon_pay_inc;
CREATE EXTERNAL TABLE dwd_tool_coupon_pay_inc
(`id`           STRING COMMENT '编号',`coupon_id`    STRING COMMENT '优惠券ID',`user_id`      STRING COMMENT 'user_id',`order_id`     STRING COMMENT 'order_id',`date_id`      STRING COMMENT '日期ID',`payment_time` STRING COMMENT '使用下单时间'
) COMMENT '优惠券使用支付事务事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_tool_coupon_pay_inc/'TBLPROPERTIES ("orc.compress" = "snappy");

2）数据装载
（1）首日装载

insert overwrite table dwd_tool_coupon_pay_inc partition(dt)
selectdata.id,data.coupon_id,data.user_id,data.order_id,date_format(data.used_time,'yyyy-MM-dd') date_id,data.used_time,date_format(data.used_time,'yyyy-MM-dd')
from ods_coupon_use_inc
where dt='2020-06-14'
and type='bootstrap-insert'
and data.used_time is not null;

（2）每日装载

insert overwrite table dwd_tool_coupon_pay_inc partition(dt='2020-06-15')
selectdata.id,data.coupon_id,data.user_id,data.order_id,date_format(data.used_time,'yyyy-MM-dd') date_id,data.used_time
from ods_coupon_use_inc
where dt='2020-06-15'
and type='update'
and array_contains(map_keys(old),'used_time');

9.11 互动域收藏商品事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_interaction_favor_add_inc;
CREATE EXTERNAL TABLE dwd_interaction_favor_add_inc
(`id`          STRING COMMENT '编号',`user_id`     STRING COMMENT '用户id',`sku_id`      STRING COMMENT 'sku_id',`date_id`     STRING COMMENT '日期id',`create_time` STRING COMMENT '收藏时间'
) COMMENT '收藏事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_interaction_favor_add_inc/'TBLPROPERTIES ("orc.compress" = "snappy");

2）数据装载
（1）首日装载

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_interaction_favor_add_inc partition(dt)
selectdata.id,data.user_id,data.sku_id,date_format(data.create_time,'yyyy-MM-dd') date_id,data.create_time,date_format(data.create_time,'yyyy-MM-dd')
from ods_favor_info_inc
where dt='2020-06-14'
and type = 'bootstrap-insert';

（2）每日装载

insert overwrite table dwd_interaction_favor_add_inc partition(dt='2020-06-15')
selectdata.id,data.user_id,data.sku_id,date_format(data.create_time,'yyyy-MM-dd') date_id,data.create_time
from ods_favor_info_inc
where dt='2020-06-15'
and type = 'insert';

9.12 互动域评价事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_interaction_comment_inc;
CREATE EXTERNAL TABLE dwd_interaction_comment_inc
(`id`            STRING COMMENT '编号',`user_id`       STRING COMMENT '用户ID',`sku_id`        STRING COMMENT 'sku_id',`order_id`      STRING COMMENT '订单ID',`date_id`       STRING COMMENT '日期ID',`create_time`   STRING COMMENT '评价时间',`appraise_code` STRING COMMENT '评价编码',`appraise_name` STRING COMMENT '评价名称'
) COMMENT '评价事务事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_interaction_comment_inc/'TBLPROPERTIES ("orc.compress" = "snappy");

2）数据装载
（1）首日装载

insert overwrite table dwd_interaction_comment_inc partition(dt)
selectid,user_id,sku_id,order_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,appraise,dic_name,date_format(create_time,'yyyy-MM-dd')
from
(selectdata.id,data.user_id,data.sku_id,data.order_id,data.create_time,data.appraisefrom ods_comment_info_incwhere dt='2020-06-14'and type='bootstrap-insert'
)ci
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-14'and parent_code='12'
)dic
on ci.appraise=dic.dic_code;

（2）每日装载

insert overwrite table dwd_interaction_comment_inc partition(dt='2020-06-15')
selectid,user_id,sku_id,order_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,appraise,dic_name
from
(selectdata.id,data.user_id,data.sku_id,data.order_id,data.create_time,data.appraisefrom ods_comment_info_incwhere dt='2020-06-15'and type='insert'
)ci
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-15'and parent_code='12'
)dic
on ci.appraise=dic.dic_code;

9.13 流量域页面浏览事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_traffic_page_view_inc;
CREATE EXTERNAL TABLE dwd_traffic_page_view_inc
(`province_id`    STRING COMMENT '省份id',`brand`          STRING COMMENT '手机品牌',`channel`        STRING COMMENT '渠道',`is_new`         STRING COMMENT '是否首次启动',`model`          STRING COMMENT '手机型号',`mid_id`         STRING COMMENT '设备id',`operate_system` STRING COMMENT '操作系统',`user_id`        STRING COMMENT '会员id',`version_code`   STRING COMMENT 'app版本号',`page_item`      STRING COMMENT '目标id ',`page_item_type` STRING COMMENT '目标类型',`last_page_id`   STRING COMMENT '上页类型',`page_id`        STRING COMMENT '页面ID ',`source_type`    STRING COMMENT '来源类型',`date_id`        STRING COMMENT '日期id',`view_time`      STRING COMMENT '跳入时间',`session_id`     STRING COMMENT '所属会话id',`during_time`    BIGINT COMMENT '持续时间毫秒'
) COMMENT '页面日志表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_traffic_page_view_inc'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

set hive.cbo.enable=false;
insert overwrite table dwd_traffic_page_view_inc partition (dt='2020-06-14')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,page_item,page_item_type,last_page_id,page_id,source_type,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,during_time
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,ts,if(page.last_page_id is null,ts,null) session_start_pointfrom ods_log_incwhere dt='2020-06-14'and page is not null
)log
left join
(selectid province_id,area_codefrom ods_base_province_fullwhere dt='2020-06-14'
)bp
on log.area_code=bp.area_code;

9.14 流量域启动事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_traffic_start_inc;
CREATE EXTERNAL TABLE dwd_traffic_start_inc
(`province_id`     STRING COMMENT '省份id',`brand`           STRING COMMENT '手机品牌',`channel`         STRING COMMENT '渠道',`is_new`          STRING COMMENT '是否首次启动',`model`           STRING COMMENT '手机型号',`mid_id`          STRING COMMENT '设备id',`operate_system`  STRING COMMENT '操作系统',`user_id`         STRING COMMENT '会员id',`version_code`    STRING COMMENT 'app版本号',`entry`           STRING COMMENT 'icon手机图标 notice 通知',`open_ad_id`      STRING COMMENT '广告页ID ',`date_id`         STRING COMMENT '日期id',`start_time`      STRING COMMENT '启动时间',`loading_time_ms` BIGINT COMMENT '启动加载时间',`open_ad_ms`      BIGINT COMMENT '广告总共播放时间',`open_ad_skip_ms` BIGINT COMMENT '用户跳过广告时点'
) COMMENT '启动日志表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_traffic_start_inc'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

set hive.cbo.enable=false;
insert overwrite table dwd_traffic_start_inc partition(dt='2020-06-14')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,entry,open_ad_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time,loading_time,open_ad_ms,open_ad_skip_ms
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,`start`.entry,`start`.loading_time,`start`.open_ad_id,`start`.open_ad_ms,`start`.open_ad_skip_ms,tsfrom ods_log_incwhere dt='2020-06-14'and `start` is not null
)log
left join
(selectid province_id,area_codefrom ods_base_province_fullwhere dt='2020-06-14'
)bp
on log.area_code=bp.area_code;

9.15 流量域动作事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_traffic_action_inc;
CREATE EXTERNAL TABLE dwd_traffic_action_inc
(`province_id`      STRING COMMENT '省份id',`brand`            STRING COMMENT '手机品牌',`channel`          STRING COMMENT '渠道',`is_new`           STRING COMMENT '是否首次启动',`model`            STRING COMMENT '手机型号',`mid_id`           STRING COMMENT '设备id',`operate_system`   STRING COMMENT '操作系统',`user_id`          STRING COMMENT '会员id',`version_code`     STRING COMMENT 'app版本号',`during_time`      BIGINT COMMENT '持续时间毫秒',`page_item`        STRING COMMENT '目标id ',`page_item_type`   STRING COMMENT '目标类型',`last_page_id`     STRING COMMENT '上页类型',`page_id`          STRING COMMENT '页面id ',`source_type`      STRING COMMENT '来源类型',`action_id`        STRING COMMENT '动作id',`action_item`      STRING COMMENT '目标id ',`action_item_type` STRING COMMENT '目标类型',`date_id`          STRING COMMENT '日期id',`action_time`      STRING COMMENT '动作发生时间'
) COMMENT '动作日志表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_traffic_action_inc'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

set hive.cbo.enable=false;
insert overwrite table dwd_traffic_action_inc partition(dt='2020-06-14')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,during_time,page_item,page_item_type,last_page_id,page_id,source_type,action_id,action_item,action_item_type,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,action.action_id,action.item action_item,action.item_type action_item_type,action.tsfrom ods_log_inc lateral view explode(actions) tmp as actionwhere dt='2020-06-14'and actions is not null
)log
left join
(selectid province_id,area_codefrom ods_base_province_fullwhere dt='2020-06-14'
)bp
on log.area_code=bp.area_code;

9.16 流量域曝光事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_traffic_display_inc;
CREATE EXTERNAL TABLE dwd_traffic_display_inc
(`province_id`       STRING COMMENT '省份id',`brand`             STRING COMMENT '手机品牌',`channel`           STRING COMMENT '渠道',`is_new`            STRING COMMENT '是否首次启动',`model`             STRING COMMENT '手机型号',`mid_id`            STRING COMMENT '设备id',`operate_system`    STRING COMMENT '操作系统',`user_id`           STRING COMMENT '会员id',`version_code`      STRING COMMENT 'app版本号',`during_time`       BIGINT COMMENT 'app版本号',`page_item`         STRING COMMENT '目标id ',`page_item_type`    STRING COMMENT '目标类型',`last_page_id`      STRING COMMENT '上页类型',`page_id`           STRING COMMENT '页面ID ',`source_type`       STRING COMMENT '来源类型',`date_id`           STRING COMMENT '日期id',`display_time`      STRING COMMENT '曝光时间',`display_type`      STRING COMMENT '曝光类型',`display_item`      STRING COMMENT '曝光对象id ',`display_item_type` STRING COMMENT 'app版本号',`display_order`     BIGINT COMMENT '曝光顺序',`display_pos_id`    BIGINT COMMENT '曝光位置'
) COMMENT '曝光日志表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_traffic_display_inc'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

set hive.cbo.enable=false;
insert overwrite table dwd_traffic_display_inc partition(dt='2020-06-14')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,during_time,page_item,page_item_type,last_page_id,page_id,source_type,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') display_time,display_type,display_item,display_item_type,display_order,display_pos_id
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,display.display_type,display.item display_item,display.item_type display_item_type,display.`order` display_order,display.pos_id display_pos_id,tsfrom ods_log_inc lateral view explode(displays) tmp as displaywhere dt='2020-06-14'and displays is not null
)log
left join
(selectid province_id,area_codefrom ods_base_province_fullwhere dt='2020-06-14'
)bp
on log.area_code=bp.area_code;

9.17 流量域错误事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_traffic_error_inc;
CREATE EXTERNAL TABLE dwd_traffic_error_inc
(`province_id`     STRING COMMENT '地区编码',`brand`           STRING COMMENT '手机品牌',`channel`         STRING COMMENT '渠道',`is_new`          STRING COMMENT '是否首次启动',`model`           STRING COMMENT '手机型号',`mid_id`          STRING COMMENT '设备id',`operate_system`  STRING COMMENT '操作系统',`user_id`         STRING COMMENT '会员id',`version_code`    STRING COMMENT 'app版本号',`page_item`       STRING COMMENT '目标id ',`page_item_type`  STRING COMMENT '目标类型',`last_page_id`    STRING COMMENT '上页类型',`page_id`         STRING COMMENT '页面ID ',`source_type`     STRING COMMENT '来源类型',`entry`           STRING COMMENT 'icon手机图标  notice 通知',`loading_time`    STRING COMMENT '启动加载时间',`open_ad_id`      STRING COMMENT '广告页ID ',`open_ad_ms`      STRING COMMENT '广告总共播放时间',`open_ad_skip_ms` STRING COMMENT '用户跳过广告时点',`actions`         ARRAY<STRUCT<action_id:STRING,item:STRING,item_type:STRING,ts:BIGINT>> COMMENT '动作信息',`displays`        ARRAY<STRUCT<display_type :STRING,item :STRING,item_type :STRING,`order` :STRING,pos_id:STRING>> COMMENT '曝光信息',`date_id`         STRING COMMENT '日期id',`error_time`      STRING COMMENT '错误时间',`error_code`      STRING COMMENT '错误码',`error_msg`       STRING COMMENT '错误信息'
) COMMENT '错误日志表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_traffic_error_inc'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

set hive.cbo.enable=false;
set hive.execution.engine=mr;
insert overwrite table dwd_traffic_error_inc partition(dt='2020-06-14')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,page_item,page_item_type,last_page_id,page_id,source_type,entry,loading_time,open_ad_id,open_ad_ms,open_ad_skip_ms,actions,displays,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') error_time,error_code,error_msg
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,`start`.entry,`start`.loading_time,`start`.open_ad_id,`start`.open_ad_ms,`start`.open_ad_skip_ms,actions,displays,err.error_code,err.msg error_msg,tsfrom ods_log_incwhere dt='2020-06-14'and err is not null
)log
join
(selectid province_id,area_codefrom ods_base_province_fullwhere dt='2020-06-14'
)bp
on log.area_code=bp.area_code;

9.18 用户域用户注册事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_user_register_inc;
CREATE EXTERNAL TABLE dwd_user_register_inc
(`user_id`        STRING COMMENT '用户ID',`date_id`        STRING COMMENT '日期ID',`create_time`    STRING COMMENT '注册时间',`channel`        STRING COMMENT '应用下载渠道',`province_id`    STRING COMMENT '省份id',`version_code`   STRING COMMENT '应用版本',`mid_id`         STRING COMMENT '设备id',`brand`          STRING COMMENT '设备品牌',`model`          STRING COMMENT '设备型号',`operate_system` STRING COMMENT '设备操作系统'
) COMMENT '用户域用户注册事务事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_user_register_inc/'TBLPROPERTIES ("orc.compress" = "snappy");

2）数据装载
（1）首日装载

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_user_register_inc partition(dt)
selectui.user_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,channel,province_id,version_code,mid_id,brand,model,operate_system,date_format(create_time,'yyyy-MM-dd')
from
(selectdata.id user_id,data.create_timefrom ods_user_info_incwhere dt='2020-06-14'and type='bootstrap-insert'
)ui
left join
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_codefrom ods_log_incwhere dt='2020-06-14'and page.page_id='register'and common.uid is not null
)log
on ui.user_id=log.user_id
left join
(selectid province_id,area_codefrom ods_base_province_fullwhere dt='2020-06-14'
)bp
on log.area_code=bp.area_code;

（2）每日装载

insert overwrite table dwd_user_register_inc partition(dt='2020-06-15')
selectui.user_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,channel,province_id,version_code,mid_id,brand,model,operate_system
from
(selectdata.id user_id,data.create_timefrom ods_user_info_incwhere dt='2020-06-15'and type='insert'
)ui
left join
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_codefrom ods_log_incwhere dt='2020-06-15'and page.page_id='register'and common.uid is not null
)log
on ui.user_id=log.user_id
left join
(selectid province_id,area_codefrom ods_base_province_fullwhere dt='2020-06-15'
)bp
on log.area_code=bp.area_code;

9.19 用户域用户登录事务事实表

1）建表语句

DROP TABLE IF EXISTS dwd_user_login_inc;
CREATE EXTERNAL TABLE dwd_user_login_inc
(`user_id`        STRING COMMENT '用户ID',`date_id`        STRING COMMENT '日期ID',`login_time`     STRING COMMENT '登录时间',`channel`        STRING COMMENT '应用下载渠道',`province_id`    STRING COMMENT '省份id',`version_code`   STRING COMMENT '应用版本',`mid_id`         STRING COMMENT '设备id',`brand`          STRING COMMENT '设备品牌',`model`          STRING COMMENT '设备型号',`operate_system` STRING COMMENT '设备操作系统'
) COMMENT '用户域用户登录事务事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dwd/dwd_user_login_inc/'TBLPROPERTIES ("orc.compress" = "snappy");

2）数据装载

insert overwrite table dwd_user_login_inc partition(dt='2020-06-14')
selectuser_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,channel,province_id,version_code,mid_id,brand,model,operate_system
from
(selectuser_id,channel,area_code,version_code,mid_id,brand,model,operate_system,tsfrom(selectuser_id,channel,area_code,version_code,mid_id,brand,model,operate_system,ts,row_number() over (partition by session_id order by ts) rnfrom(selectuser_id,channel,area_code,version_code,mid_id,brand,model,operate_system,ts,concat(mid_id,'-',last_value(session_start_point,true) over(partition by mid_id order by ts)) session_idfrom(selectcommon.uid user_id,common.ch channel,common.ar area_code,common.vc version_code,common.mid mid_id,common.ba brand,common.md model,common.os operate_system,ts,if(page.last_page_id is null,ts,null) session_start_pointfrom ods_log_incwhere dt='2020-06-14'and page is not null)t1)t2where user_id is not null)t3where rn=1
)t4
left join
(selectid province_id,area_codefrom ods_base_province_fullwhere dt='2020-06-14'
)bp
on t4.area_code=bp.area_code;

9.20 数据装载脚本

9.20.1 首日装载脚本

（1）在hadoop102的/home/atguigu/bin目录下创建ods_to_dwd_init.sh
[atguigu@hadoop102 bin]$ vim ods_to_dwd_init.sh
（2）编写如下内容

#!/bin/bash
APP=gmallif [ -n "$2" ] ;thendo_date=$2
else echo "请传入日期参数"exit
fidwd_interaction_comment_inc="
insert overwrite table ${APP}.dwd_interaction_comment_inc partition(dt)
selectid,user_id,sku_id,order_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,appraise,dic_name,date_format(create_time,'yyyy-MM-dd')
from
(selectdata.id,data.user_id,data.sku_id,data.order_id,data.create_time,data.appraisefrom ${APP}.ods_comment_info_incwhere dt='$do_date'and type='bootstrap-insert'
)ci
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='12'
)dic
on ci.appraise=dic.dic_code;
"
dwd_interaction_favor_add_inc="
insert overwrite table ${APP}.dwd_interaction_favor_add_inc partition(dt)
selectdata.id,data.user_id,data.sku_id,date_format(data.create_time,'yyyy-MM-dd') date_id,data.create_time,date_format(data.create_time,'yyyy-MM-dd')
from ${APP}.ods_favor_info_inc
where dt='$do_date'
and type = 'bootstrap-insert';
"dwd_tool_coupon_get_inc="
insert overwrite table ${APP}.dwd_tool_coupon_get_inc partition(dt)
selectdata.id,data.coupon_id,data.user_id,date_format(data.get_time,'yyyy-MM-dd') date_id,data.get_time,date_format(data.get_time,'yyyy-MM-dd')
from ${APP}.ods_coupon_use_inc
where dt='$do_date'
and type='bootstrap-insert';
"
dwd_tool_coupon_order_inc="
insert overwrite table ${APP}.dwd_tool_coupon_order_inc partition(dt)
selectdata.id,data.coupon_id,data.user_id,data.order_id,date_format(data.using_time,'yyyy-MM-dd') date_id,data.using_time,date_format(data.using_time,'yyyy-MM-dd')
from ${APP}.ods_coupon_use_inc
where dt='$do_date'
and type='bootstrap-insert'
and data.using_time is not null;
"
dwd_tool_coupon_pay_inc="
insert overwrite table ${APP}.dwd_tool_coupon_pay_inc partition(dt)
selectdata.id,data.coupon_id,data.user_id,data.order_id,date_format(data.used_time,'yyyy-MM-dd') date_id,data.used_time,date_format(data.used_time,'yyyy-MM-dd')
from ${APP}.ods_coupon_use_inc
where dt='$do_date'
and type='bootstrap-insert'
and data.used_time is not null;
"
dwd_trade_cancel_detail_inc="
insert overwrite table ${APP}.dwd_trade_cancel_detail_inc partition (dt)
selectod.id,order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,date_format(canel_time,'yyyy-MM-dd') date_id,canel_time,source_id,source_type,dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount,date_format(canel_time,'yyyy-MM-dd')
from
(selectdata.id,data.order_id,data.sku_id,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ${APP}.ods_order_detail_incwhere dt = '$do_date'and type = 'bootstrap-insert'
) od
join
(selectdata.id,data.user_id,data.province_id,data.operate_time canel_timefrom ${APP}.ods_order_info_incwhere dt = '$do_date'and type = 'bootstrap-insert'and data.order_status='1003'
) oi
on od.order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ${APP}.ods_order_detail_activity_incwhere dt = '$do_date'and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ${APP}.ods_order_detail_coupon_incwhere dt = '$do_date'and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='24'
)dic
on od.source_type=dic.dic_code;
"
dwd_trade_cart_add_inc="
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition (dt)
selectid,user_id,sku_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,source_id,source_type,dic.dic_name,sku_num,date_format(create_time, 'yyyy-MM-dd')
from
(selectdata.id,data.user_id,data.sku_id,data.create_time,data.source_id,data.source_type,data.sku_numfrom ${APP}.ods_cart_info_incwhere dt = '$do_date'and type = 'bootstrap-insert'
)ci
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='24'
)dic
on ci.source_type=dic.dic_code;
"
dwd_trade_cart_full="
insert overwrite table ${APP}.dwd_trade_cart_full partition(dt='$do_date')
selectid,user_id,sku_id,sku_name,sku_num
from ${APP}.ods_cart_info_full
where dt='$do_date'
and is_ordered='0';
"
dwd_trade_order_detail_inc="
insert overwrite table ${APP}.dwd_trade_order_detail_inc partition (dt)
selectod.id,order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,date_format(create_time, 'yyyy-MM-dd') date_id,create_time,source_id,source_type,dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount,date_format(create_time,'yyyy-MM-dd')
from
(selectdata.id,data.order_id,data.sku_id,data.create_time,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ${APP}.ods_order_detail_incwhere dt = '$do_date'and type = 'bootstrap-insert'
) od
left join
(selectdata.id,data.user_id,data.province_idfrom ${APP}.ods_order_info_incwhere dt = '$do_date'and type = 'bootstrap-insert'
) oi
on od.order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ${APP}.ods_order_detail_activity_incwhere dt = '$do_date'and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ${APP}.ods_order_detail_coupon_incwhere dt = '$do_date'and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='24'
)dic
on od.source_type=dic.dic_code;
"
dwd_trade_order_refund_inc="
insert overwrite table ${APP}.dwd_trade_order_refund_inc partition(dt)
selectri.id,user_id,order_id,sku_id,province_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,refund_type,type_dic.dic_name,refund_reason_type,reason_dic.dic_name,refund_reason_txt,refund_num,refund_amount,date_format(create_time,'yyyy-MM-dd')
from
(selectdata.id,data.user_id,data.order_id,data.sku_id,data.refund_type,data.refund_num,data.refund_amount,data.refund_reason_type,data.refund_reason_txt,data.create_timefrom ${APP}.ods_order_refund_info_incwhere dt='$do_date'and type='bootstrap-insert'
)ri
left join
(selectdata.id,data.province_idfrom ${APP}.ods_order_info_incwhere dt='$do_date'and type='bootstrap-insert'
)oi
on ri.order_id=oi.id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code = '15'
)type_dic
on ri.refund_type=type_dic.dic_code
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code = '13'
)reason_dic
on ri.refund_reason_type=reason_dic.dic_code;
"dwd_trade_pay_detail_suc_inc="
insert overwrite table ${APP}.dwd_trade_pay_detail_suc_inc partition (dt)
selectod.id,od.order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,payment_type,pay_dic.dic_name,date_format(callback_time,'yyyy-MM-dd') date_id,callback_time,source_id,source_type,src_dic.dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount,date_format(callback_time,'yyyy-MM-dd')
from
(selectdata.id,data.order_id,data.sku_id,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ${APP}.ods_order_detail_incwhere dt = '$do_date'and type = 'bootstrap-insert'
) od
join
(selectdata.user_id,data.order_id,data.payment_type,data.callback_timefrom ${APP}.ods_payment_info_incwhere dt='$do_date'and type='bootstrap-insert'and data.payment_status='1602'
) pi
on od.order_id=pi.order_id
left join
(selectdata.id,data.province_idfrom ${APP}.ods_order_info_incwhere dt = '$do_date'and type = 'bootstrap-insert'
) oi
on od.order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ${APP}.ods_order_detail_activity_incwhere dt = '$do_date'and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ${APP}.ods_order_detail_coupon_incwhere dt = '$do_date'and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='11'
) pay_dic
on pi.payment_type=pay_dic.dic_code
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='24'
)src_dic
on od.source_type=src_dic.dic_code;
"
dwd_trade_refund_pay_suc_inc="
insert overwrite table ${APP}.dwd_trade_refund_pay_suc_inc partition(dt)
selectrp.id,user_id,rp.order_id,rp.sku_id,province_id,payment_type,dic_name,date_format(callback_time,'yyyy-MM-dd') date_id,callback_time,refund_num,total_amount,date_format(callback_time,'yyyy-MM-dd')
from
(selectdata.id,data.order_id,data.sku_id,data.payment_type,data.callback_time,data.total_amountfrom ${APP}.ods_refund_payment_incwhere dt='$do_date'and type = 'bootstrap-insert'and data.refund_status='1602'
)rp
left join
(selectdata.id,data.user_id,data.province_idfrom ${APP}.ods_order_info_incwhere dt='$do_date'and type='bootstrap-insert'
)oi
on rp.order_id=oi.id
left join
(selectdata.order_id,data.sku_id,data.refund_numfrom ${APP}.ods_order_refund_info_incwhere dt='$do_date'and type='bootstrap-insert'
)ri
on rp.order_id=ri.order_id
and rp.sku_id=ri.sku_id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='11'
)dic
on rp.payment_type=dic.dic_code;
"
dwd_traffic_action_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_action_inc partition(dt='$do_date')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,during_time,page_item,page_item_type,last_page_id,page_id,source_type,action_id,action_item,action_item_type,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,action.action_id,action.item action_item,action.item_type action_item_type,action.tsfrom ${APP}.ods_log_inc lateral view explode(actions) tmp as actionwhere dt='$do_date'and actions is not null
)log
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
"
dwd_traffic_display_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_display_inc partition(dt='$do_date')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,during_time,page_item,page_item_type,last_page_id,page_id,source_type,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') display_time,display_type,display_item,display_item_type,display_order,display_pos_id
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,display.display_type,display.item display_item,display.item_type display_item_type,display.\`order\` display_order,display.pos_id display_pos_id,tsfrom ${APP}.ods_log_inc lateral view explode(displays) tmp as displaywhere dt='$do_date'and displays is not null
)log
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
"
dwd_traffic_error_inc="
set hive.cbo.enable=false;
set hive.execution.engine=mr;
insert overwrite table ${APP}.dwd_traffic_error_inc partition(dt='$do_date')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,page_item,page_item_type,last_page_id,page_id,source_type,entry,loading_time,open_ad_id,open_ad_ms,open_ad_skip_ms,actions,displays,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') error_time,error_code,error_msg
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,\`start\`.entry,\`start\`.loading_time,\`start\`.open_ad_id,\`start\`.open_ad_ms,\`start\`.open_ad_skip_ms,actions,displays,err.error_code,err.msg error_msg,tsfrom ${APP}.ods_log_incwhere dt='$do_date'and err is not null
)log
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
set hive.execution.engine=spark;
"
dwd_traffic_page_view_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition (dt='$do_date')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,page_item,page_item_type,last_page_id,page_id,source_type,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,during_time
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,ts,if(page.last_page_id is null,ts,null) session_start_pointfrom ${APP}.ods_log_incwhere dt='$do_date'and page is not null
)log
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
"
dwd_traffic_start_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_start_inc partition(dt='$do_date')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,entry,open_ad_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time,loading_time,open_ad_ms,open_ad_skip_ms
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,\`start\`.entry,\`start\`.loading_time,\`start\`.open_ad_id,\`start\`.open_ad_ms,\`start\`.open_ad_skip_ms,tsfrom ${APP}.ods_log_incwhere dt='$do_date'and \`start\` is not null
)log
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
"
dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition(dt='$do_date')
selectuser_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,channel,province_id,version_code,mid_id,brand,model,operate_system
from
(selectuser_id,channel,area_code,version_code,mid_id,brand,model,operate_system,tsfrom(selectuser_id,channel,area_code,version_code,mid_id,brand,model,operate_system,ts,row_number() over (partition by session_id order by ts) rnfrom(selectuser_id,channel,area_code,version_code,mid_id,brand,model,operate_system,ts,concat(mid_id,'-',last_value(session_start_point,true) over(partition by mid_id order by ts)) session_idfrom(selectcommon.uid user_id,common.ch channel,common.ar area_code,common.vc version_code,common.mid mid_id,common.ba brand,common.md model,common.os operate_system,ts,if(page.last_page_id is null,ts,null) session_start_pointfrom ${APP}.ods_log_incwhere dt='$do_date'and page is not null)t1)t2where user_id is not null)t3where rn=1
)t4
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on t4.area_code=bp.area_code;
"
dwd_user_register_inc="
insert overwrite table ${APP}.dwd_user_register_inc partition(dt)
selectui.user_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,channel,province_id,version_code,mid_id,brand,model,operate_system,date_format(create_time,'yyyy-MM-dd')
from
(selectdata.id user_id,data.create_timefrom ${APP}.ods_user_info_incwhere dt='$do_date'and type='bootstrap-insert'
)ui
left join
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_codefrom ${APP}.ods_log_incwhere dt='$do_date'and page.page_id='register'and common.uid is not null
)log
on ui.user_id=log.user_id
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
"case $1 in"dwd_interaction_comment_inc" )hive -e "$dwd_interaction_comment_inc";;"dwd_interaction_favor_add_inc" )hive -e "$dwd_interaction_favor_add_inc";;"dwd_tool_coupon_get_inc" )hive -e "$dwd_tool_coupon_get_inc";;"dwd_tool_coupon_order_inc" )hive -e "$dwd_tool_coupon_order_inc";;"dwd_tool_coupon_pay_inc" )hive -e "$dwd_tool_coupon_pay_inc";;"dwd_trade_cancel_detail_inc" )hive -e "$dwd_trade_cancel_detail_inc";;"dwd_trade_cart_add_inc" )hive -e "$dwd_trade_cart_add_inc";;"dwd_trade_cart_full" )hive -e "$dwd_trade_cart_full";;"dwd_trade_order_detail_inc" )hive -e "$dwd_trade_order_detail_inc";;"dwd_trade_order_refund_inc" )hive -e "$dwd_trade_order_refund_inc";;"dwd_trade_pay_detail_suc_inc" )hive -e "$dwd_trade_pay_detail_suc_inc";;"dwd_trade_refund_pay_suc_inc" )hive -e "$dwd_trade_refund_pay_suc_inc";;"dwd_traffic_action_inc" )hive -e "$dwd_traffic_action_inc";;"dwd_traffic_display_inc" )hive -e "$dwd_traffic_display_inc";;"dwd_traffic_error_inc" )hive -e "$dwd_traffic_error_inc";;"dwd_traffic_page_view_inc" )hive -e "$dwd_traffic_page_view_inc";;"dwd_traffic_start_inc" )hive -e "$dwd_traffic_start_inc";;"dwd_user_login_inc" )hive -e "$dwd_user_login_inc";;"dwd_user_register_inc" )hive -e "$dwd_user_register_inc";;"all" )hive -e "$dwd_interaction_comment_inc$dwd_interaction_favor_add_inc$dwd_tool_coupon_get_inc$dwd_tool_coupon_order_inc$dwd_tool_coupon_pay_inc$dwd_trade_cancel_detail_inc$dwd_trade_cart_add_inc$dwd_trade_cart_full$dwd_trade_order_detail_inc$dwd_trade_order_refund_inc$dwd_trade_pay_detail_suc_inc$dwd_trade_refund_pay_suc_inc$dwd_traffic_action_inc$dwd_traffic_display_inc$dwd_traffic_error_inc$dwd_traffic_page_view_inc$dwd_traffic_start_inc$dwd_user_login_inc$dwd_user_register_inc"
esac

（3）增加脚本执行权限
[atguigu@hadoop102 bin]$ chmod +x ods_to_dwd_init.sh
（4）脚本用法
[atguigu@hadoop102 bin]$ ods_to_dwd_init.sh all 2020-06-14

9.20.2 每日装载脚本

（1）在hadoop102的/home/atguigu/bin目录下创建ods_to_dwd.sh
[atguigu@hadoop102 bin]$ vim ods_to_dwd.sh
（2）编写如下内容

#!/bin/bashAPP=gmall
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;thendo_date=$2
else do_date=`date -d "-1 day" +%F`
fidwd_interaction_comment_inc="
insert overwrite table ${APP}.dwd_interaction_comment_inc partition(dt='$do_date')
selectid,user_id,sku_id,order_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,appraise,dic_name
from
(selectdata.id,data.user_id,data.sku_id,data.order_id,data.create_time,data.appraisefrom ${APP}.ods_comment_info_incwhere dt='$do_date'and type='insert'
)ci
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='12'
)dic
on ci.appraise=dic.dic_code;
"
dwd_interaction_favor_add_inc="
insert overwrite table ${APP}.dwd_interaction_favor_add_inc partition(dt='$do_date')
selectdata.id,data.user_id,data.sku_id,date_format(data.create_time,'yyyy-MM-dd') date_id,data.create_time
from ${APP}.ods_favor_info_inc
where dt='$do_date'
and type = 'insert';
"dwd_tool_coupon_get_inc="
insert overwrite table ${APP}.dwd_tool_coupon_get_inc partition (dt='$do_date')
selectdata.id,data.coupon_id,data.user_id,date_format(data.get_time,'yyyy-MM-dd') date_id,data.get_time
from ${APP}.ods_coupon_use_inc
where dt='$do_date'
and type='insert';
"
dwd_tool_coupon_order_inc="
insert overwrite table ${APP}.dwd_tool_coupon_order_inc partition(dt='$do_date')
selectdata.id,data.coupon_id,data.user_id,data.order_id,date_format(data.using_time,'yyyy-MM-dd') date_id,data.using_time
from ${APP}.ods_coupon_use_inc
where dt='$do_date'
and type='update'
and array_contains(map_keys(old),'using_time');
"
dwd_tool_coupon_pay_inc="
insert overwrite table ${APP}.dwd_tool_coupon_pay_inc partition(dt='$do_date')
selectdata.id,data.coupon_id,data.user_id,data.order_id,date_format(data.used_time,'yyyy-MM-dd') date_id,data.used_time
from ${APP}.ods_coupon_use_inc
where dt='$do_date'
and type='update'
and array_contains(map_keys(old),'used_time');
"
dwd_trade_cancel_detail_inc="
insert overwrite table ${APP}.dwd_trade_cancel_detail_inc partition (dt='$do_date')
selectod.id,order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,date_format(canel_time,'yyyy-MM-dd') date_id,canel_time,source_id,source_type,dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount
from
(selectdata.id,data.order_id,data.sku_id,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ${APP}.ods_order_detail_incwhere (dt='$do_date' or dt=date_add('$do_date',-1))and (type = 'insert' or type= 'bootstrap-insert')
) od
join
(selectdata.id,data.user_id,data.province_id,data.operate_time canel_timefrom ${APP}.ods_order_info_incwhere dt = '$do_date'and type = 'update'and data.order_status='1003'and array_contains(map_keys(old),'order_status')
) oi
on order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ${APP}.ods_order_detail_activity_incwhere (dt='$do_date' or dt=date_add('$do_date',-1))and (type = 'insert' or type= 'bootstrap-insert')
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ${APP}.ods_order_detail_coupon_incwhere (dt='$do_date' or dt=date_add('$do_date',-1))and (type = 'insert' or type= 'bootstrap-insert')
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='24'
)dic
on od.source_type=dic.dic_code;
"dwd_trade_cart_add_inc="
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition(dt='$do_date')
selectid,user_id,sku_id,date_id,create_time,source_id,source_type_code,source_type_name,sku_num
from
(selectdata.id,data.user_id,data.sku_id,date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd HH:mm:ss') create_time,data.source_id,data.source_type source_type_code,if(type='insert',data.sku_num,data.sku_num-old['sku_num']) sku_numfrom ${APP}.ods_cart_info_incwhere dt='$do_date'and (type='insert'or(type='update' and old['sku_num'] is not null and data.sku_num>cast(old['sku_num'] as int)))
)cart
left join
(selectdic_code,dic_name source_type_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='24'
)dic
on cart.source_type_code=dic.dic_code;
"
dwd_trade_cart_full="
insert overwrite table ${APP}.dwd_trade_cart_full partition(dt='$do_date')
selectid,user_id,sku_id,sku_name,sku_num
from ${APP}.ods_cart_info_full
where dt='$do_date'
and is_ordered='0';
"
dwd_trade_order_detail_inc="
insert overwrite table ${APP}.dwd_trade_order_detail_inc partition (dt='$do_date')
selectod.id,order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,date_id,create_time,source_id,source_type,dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount
from
(selectdata.id,data.order_id,data.sku_id,date_format(data.create_time, 'yyyy-MM-dd') date_id,data.create_time,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ${APP}.ods_order_detail_incwhere dt = '$do_date'and type = 'insert'
) od
left join
(selectdata.id,data.user_id,data.province_idfrom ${APP}.ods_order_info_incwhere dt = '$do_date'and type = 'insert'
) oi
on od.order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ${APP}.ods_order_detail_activity_incwhere dt = '$do_date'and type = 'insert'
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ${APP}.ods_order_detail_coupon_incwhere dt = '$do_date'and type = 'insert'
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='24'
)dic
on od.source_type=dic.dic_code;
"
dwd_trade_order_refund_inc="
insert overwrite table ${APP}.dwd_trade_order_refund_inc partition(dt='$do_date')
selectri.id,user_id,order_id,sku_id,province_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,refund_type,type_dic.dic_name,refund_reason_type,reason_dic.dic_name,refund_reason_txt,refund_num,refund_amount
from
(selectdata.id,data.user_id,data.order_id,data.sku_id,data.refund_type,data.refund_num,data.refund_amount,data.refund_reason_type,data.refund_reason_txt,data.create_timefrom ${APP}.ods_order_refund_info_incwhere dt='$do_date'and type='insert'
)ri
left join
(selectdata.id,data.province_idfrom ${APP}.ods_order_info_incwhere dt='$do_date'and type='update'and data.order_status='1005'and array_contains(map_keys(old),'order_status')
)oi
on ri.order_id=oi.id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code = '15'
)type_dic
on ri.refund_type=type_dic.dic_code
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code = '13'
)reason_dic
on ri.refund_reason_type=reason_dic.dic_code;
"dwd_trade_pay_detail_suc_inc="
insert overwrite table ${APP}.dwd_trade_pay_detail_suc_inc partition (dt='$do_date')
selectod.id,od.order_id,user_id,sku_id,province_id,activity_id,activity_rule_id,coupon_id,payment_type,pay_dic.dic_name,date_format(callback_time,'yyyy-MM-dd') date_id,callback_time,source_id,source_type,src_dic.dic_name,sku_num,split_original_amount,split_activity_amount,split_coupon_amount,split_total_amount
from
(selectdata.id,data.order_id,data.sku_id,data.source_id,data.source_type,data.sku_num,data.sku_num * data.order_price split_original_amount,data.split_total_amount,data.split_activity_amount,data.split_coupon_amountfrom ${APP}.ods_order_detail_incwhere (dt = '$do_date' or dt = date_add('$do_date',-1))and (type = 'insert' or type = 'bootstrap-insert')
) od
join
(selectdata.user_id,data.order_id,data.payment_type,data.callback_timefrom ${APP}.ods_payment_info_incwhere dt='$do_date'and type='update'and array_contains(map_keys(old),'payment_status')and data.payment_status='1602'
) pi
on od.order_id=pi.order_id
left join
(selectdata.id,data.province_idfrom ${APP}.ods_order_info_incwhere (dt = '$do_date' or dt = date_add('$do_date',-1))and (type = 'insert' or type = 'bootstrap-insert')
) oi
on od.order_id = oi.id
left join
(selectdata.order_detail_id,data.activity_id,data.activity_rule_idfrom ${APP}.ods_order_detail_activity_incwhere (dt = '$do_date' or dt = date_add('$do_date',-1))and (type = 'insert' or type = 'bootstrap-insert')
) act
on od.id = act.order_detail_id
left join
(selectdata.order_detail_id,data.coupon_idfrom ${APP}.ods_order_detail_coupon_incwhere (dt = '$do_date' or dt = date_add('$do_date',-1))and (type = 'insert' or type = 'bootstrap-insert')
) cou
on od.id = cou.order_detail_id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='11'
) pay_dic
on pi.payment_type=pay_dic.dic_code
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='24'
)src_dic
on od.source_type=src_dic.dic_code;
"
dwd_trade_refund_pay_suc_inc="
insert overwrite table ${APP}.dwd_trade_refund_pay_suc_inc partition(dt='$do_date')
selectrp.id,user_id,rp.order_id,rp.sku_id,province_id,payment_type,dic_name,date_format(callback_time,'yyyy-MM-dd') date_id,callback_time,refund_num,total_amount
from
(selectdata.id,data.order_id,data.sku_id,data.payment_type,data.callback_time,data.total_amountfrom ${APP}.ods_refund_payment_incwhere dt='$do_date'and type = 'update'and array_contains(map_keys(old),'refund_status')and data.refund_status='1602'
)rp
left join
(selectdata.id,data.user_id,data.province_idfrom ${APP}.ods_order_info_incwhere dt='$do_date'and type='update'and data.order_status='1006'and array_contains(map_keys(old),'order_status')
)oi
on rp.order_id=oi.id
left join
(selectdata.order_id,data.sku_id,data.refund_numfrom ${APP}.ods_order_refund_info_incwhere dt='$do_date'and type='update'and data.refund_status='0705'and array_contains(map_keys(old),'refund_status')
)ri
on rp.order_id=ri.order_id
and rp.sku_id=ri.sku_id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='11'
)dic
on rp.payment_type=dic.dic_code;
"
dwd_traffic_action_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_action_inc partition(dt='$do_date')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,during_time,page_item,page_item_type,last_page_id,page_id,source_type,action_id,action_item,action_item_type,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,action.action_id,action.item action_item,action.item_type action_item_type,action.tsfrom ${APP}.ods_log_inc lateral view explode(actions) tmp as actionwhere dt='$do_date'and actions is not null
)log
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
"
dwd_traffic_display_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_display_inc partition(dt='$do_date')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,during_time,page_item,page_item_type,last_page_id,page_id,source_type,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') display_time,display_type,display_item,display_item_type,display_order,display_pos_id
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,display.display_type,display.item display_item,display.item_type display_item_type,display.\`order\` display_order,display.pos_id display_pos_id,tsfrom ${APP}.ods_log_inc lateral view explode(displays) tmp as displaywhere dt='$do_date'and displays is not null
)log
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
"
dwd_traffic_error_inc="
set hive.cbo.enable=false;
set hive.execution.engine=mr;
insert overwrite table ${APP}.dwd_traffic_error_inc partition(dt='$do_date')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,page_item,page_item_type,last_page_id,page_id,source_type,entry,loading_time,open_ad_id,open_ad_ms,open_ad_skip_ms,actions,displays,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') error_time,error_code,error_msg
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,\`start\`.entry,\`start\`.loading_time,\`start\`.open_ad_id,\`start\`.open_ad_ms,\`start\`.open_ad_skip_ms,actions,displays,err.error_code,err.msg error_msg,tsfrom ${APP}.ods_log_incwhere dt='$do_date'and err is not null
)log
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
set hive.execution.engine=spark;
"
dwd_traffic_page_view_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition (dt='$do_date')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,page_item,page_item_type,last_page_id,page_id,source_type,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,during_time
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,page.during_time,page.item page_item,page.item_type page_item_type,page.last_page_id,page.page_id,page.source_type,ts,if(page.last_page_id is null,ts,null) session_start_pointfrom ${APP}.ods_log_incwhere dt='$do_date'and page is not null
)log
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
"
dwd_traffic_start_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_start_inc partition(dt='$do_date')
selectprovince_id,brand,channel,is_new,model,mid_id,operate_system,user_id,version_code,entry,open_ad_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time,loading_time,open_ad_ms,open_ad_skip_ms
from
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.is_new,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_code,\`start\`.entry,\`start\`.loading_time,\`start\`.open_ad_id,\`start\`.open_ad_ms,\`start\`.open_ad_skip_ms,tsfrom ${APP}.ods_log_incwhere dt='$do_date'and \`start\` is not null
)log
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
"
dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition(dt='$do_date')
selectuser_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,channel,province_id,version_code,mid_id,brand,model,operate_system
from
(selectuser_id,channel,area_code,version_code,mid_id,brand,model,operate_system,tsfrom(selectuser_id,channel,area_code,version_code,mid_id,brand,model,operate_system,ts,row_number() over (partition by session_id order by ts) rnfrom(selectuser_id,channel,area_code,version_code,mid_id,brand,model,operate_system,ts,concat(mid_id,'-',last_value(session_start_point,true) over(partition by mid_id order by ts)) session_idfrom(selectcommon.uid user_id,common.ch channel,common.ar area_code,common.vc version_code,common.mid mid_id,common.ba brand,common.md model,common.os operate_system,ts,if(page.last_page_id is null,ts,null) session_start_pointfrom ${APP}.ods_log_incwhere dt='$do_date'and page is not null)t1)t2where user_id is not null)t3where rn=1
)t4
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on t4.area_code=bp.area_code;
"
dwd_user_register_inc="
insert overwrite table ${APP}.dwd_user_register_inc partition(dt='$do_date')
selectui.user_id,date_format(create_time,'yyyy-MM-dd') date_id,create_time,channel,province_id,version_code,mid_id,brand,model,operate_system
from
(selectdata.id user_id,data.create_timefrom ${APP}.ods_user_info_incwhere dt='$do_date'and type='insert'
)ui
left join
(selectcommon.ar area_code,common.ba brand,common.ch channel,common.md model,common.mid mid_id,common.os operate_system,common.uid user_id,common.vc version_codefrom ${APP}.ods_log_incwhere dt='$do_date'and page.page_id='register'and common.uid is not null
)log
on ui.user_id=log.user_id
left join
(selectid province_id,area_codefrom ${APP}.ods_base_province_fullwhere dt='$do_date'
)bp
on log.area_code=bp.area_code;
"
case $1 in"dwd_interaction_comment_inc" )hive -e "$dwd_interaction_comment_inc";;"dwd_interaction_favor_add_inc" )hive -e "$dwd_interaction_favor_add_inc";;"dwd_tool_coupon_get_inc" )hive -e "$dwd_tool_coupon_get_inc";;"dwd_tool_coupon_order_inc" )hive -e "$dwd_tool_coupon_order_inc";;"dwd_tool_coupon_pay_inc" )hive -e "$dwd_tool_coupon_pay_inc";;"dwd_trade_cancel_detail_inc" )hive -e "$dwd_trade_cancel_detail_inc";;"dwd_trade_cart_add_inc" )hive -e "$dwd_trade_cart_add_inc";;"dwd_trade_cart_full" )hive -e "$dwd_trade_cart_full";;"dwd_trade_order_detail_inc" )hive -e "$dwd_trade_order_detail_inc";;"dwd_trade_order_refund_inc" )hive -e "$dwd_trade_order_refund_inc";;"dwd_trade_pay_detail_suc_inc" )hive -e "$dwd_trade_pay_detail_suc_inc";;"dwd_trade_refund_pay_suc_inc" )hive -e "$dwd_trade_refund_pay_suc_inc";;"dwd_traffic_action_inc" )hive -e "$dwd_traffic_action_inc";;"dwd_traffic_display_inc" )hive -e "$dwd_traffic_display_inc";;"dwd_traffic_error_inc" )hive -e "$dwd_traffic_error_inc";;"dwd_traffic_page_view_inc" )hive -e "$dwd_traffic_page_view_inc";;"dwd_traffic_start_inc" )hive -e "$dwd_traffic_start_inc";;"dwd_user_login_inc" )hive -e "$dwd_user_login_inc";;"dwd_user_register_inc" )hive -e "$dwd_user_register_inc";;"all" )hive -e "$dwd_interaction_comment_inc$dwd_interaction_favor_add_inc$dwd_tool_coupon_get_inc$dwd_tool_coupon_order_inc$dwd_tool_coupon_pay_inc$dwd_trade_cancel_detail_inc$dwd_trade_cart_add_inc$dwd_trade_cart_full$dwd_trade_order_detail_inc$dwd_trade_order_refund_inc$dwd_trade_pay_detail_suc_inc$dwd_trade_refund_pay_suc_inc$dwd_traffic_action_inc$dwd_traffic_display_inc$dwd_traffic_error_inc$dwd_traffic_page_view_inc$dwd_traffic_start_inc$dwd_user_login_inc$dwd_user_register_inc"
esac

（3）增加脚本执行权限
[atguigu@hadoop102 bin]$ chmod +x ods_to_dwd.sh
（4）脚本用法
[atguigu@hadoop102 bin]$ ods_to_dwd.sh all 2020-06-14

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/424666.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

springboot修改组件扫描包位置

springboot修改组件扫描包位置

步骤很详细&#xff0c;直接上教程 问题分析 默认情况下组件扫描包范围为启动类所在包及其子包 解决方法 我们只需要在启动类上面加个注解配置扫描范围 效果演示 温馨提示 非必要不建议修改&#xff0c;按规范创建项目结构一般不会出现这个问题
阅读更多...
uniapp 发布苹果IOS详细流程，包括苹果开发者公司账号申请、IOS证书、.p12证书文件等

uniapp 发布苹果IOS详细流程，包括苹果开发者公司账号申请、IOS证书、.p12证书文件等

记录一下uniapp发布苹果IOS的流程。 一、苹果开发者公司账号申请 1、邓白氏编码申请&#xff08;先申请公司邓白氏编码&#xff0c;这一步需要1-2周&#xff0c;没有这个编码苹果开发者没法申请&#xff0c;已有编码的跳过此步骤&#xff09;&#xff1a; 1&#xff09;联系苹…
阅读更多...
什么是职场？职场的本质又是什么呢？

什么是职场？职场的本质又是什么呢？

最近&#xff0c;经常看到很多职场相关的&#xff0c;比如职场必备技能、职场人际关系、职场晋升等等&#xff0c;这些都是职场的一些方面&#xff0c;但是却少有人来深入剖析什么是职场&#xff0c;职场的本质又是什么&#xff0c;今天我们就来一起来聊一聊&#xff0c;到底职…
阅读更多...
【MySQL】MySQL索引与事务的透析——（超详解）

【MySQL】MySQL索引与事务的透析——（超详解）

前言 &#x1f31f;&#x1f31f;本期讲解关于MySQL索引事务&#xff0c;希望能帮到屏幕前的你。 &#x1f308;上期博客在这里&#xff1a;【MySQL】MySQL表的增删改查&#xff08;进阶篇&#xff09;——之查询操作&#xff08;超级详解&#xff09;-CSDN博客 &#x1f308;感…
阅读更多...
【计算机网络 - 基础问题】每日 3 题（一）

【计算机网络 - 基础问题】每日 3 题（一）

✍个人博客&#xff1a;Pandaconda-CSDN博客 &#x1f4e3;专栏地址&#xff1a;http://t.csdnimg.cn/fYaBd &#x1f4da;专栏简介&#xff1a;在这个专栏中&#xff0c;我将会分享 C 面试中常见的面试题给大家~ ❤️如果有收获的话&#xff0c;欢迎点赞&#x1f44d;收藏&…
阅读更多...
HTML贪吃蛇游戏

HTML贪吃蛇游戏

文章目录 贪吃蛇游戏 运行效果代码 贪吃蛇游戏 贪吃蛇是一款经典的休闲益智游戏。本文将通过HTML5和JavaScript详细解析如何实现一个简易版的贪吃蛇游戏。游戏的主要逻辑包括蛇的移动、碰撞检测、食物生成等功能。以下是游戏的完整代码及注释解析。&#xff08;纯属好玩&#…
阅读更多...
【python】OpenCV—Mask RCNN for Object Detection and Instance Segmentation

【python】OpenCV—Mask RCNN for Object Detection and Instance Segmentation

文章目录 1、任务描述2、MASR RCNN 网络结构3、方法实现4、结果展示5、涉及到的库getPerfProfile 6、参考 1、任务描述 利用 mask rcnn 网络&#xff0c;进行图片和视频的目标检测和实例分割 2、MASR RCNN 网络结构 3、方法实现 # Copyright (C) 2018-2019, BigVision LLC (L…
阅读更多...
SQL进阶技巧：火车票相邻座位预定一起可能情况查询算法 ？

SQL进阶技巧：火车票相邻座位预定一起可能情况查询算法 ？

目录 0 场景描述 1 数据准备 2 问题分析 2.1 分析函数法 2.2 自关联求解 3 小结 如果觉得本文对你有帮助&#xff0c;那么不妨也可以选择去看看我的数字化建设通关指南博客专栏 &#xff0c;或许对你更有用。专栏原价99&#xff0c;现在活动价29.9&#xff0c;按照阶梯…
阅读更多...
什么是上拉，下拉？

什么是上拉，下拉？

上拉就是将引脚通过一个电阻连接到电源&#xff0c;作用&#xff1a;1.使IO口的不确定电平稳定在高点平&#xff0c;2、为了增加IO口拉电流的能力。 下拉就是将引脚通过一个电阻与GND相连&#xff0c;作用&#xff1a;1.从器件输出电流 2.当IO口为输入状态时&#xff0c;引脚的…
阅读更多...
GitHub Star 数量前 13 的自托管项目清单

GitHub Star 数量前 13 的自托管项目清单

一个多月前&#xff0c;我们撰写并发布了这篇文章《终极自托管解决方案指南》。在那篇文章里我们深入探讨了云端服务与自托管方案的对比、自托管的潜在挑战、如何选择适合自托管解决方案&#xff0c;并深入介绍了五款涵盖不同场景的优秀自托管产品。 关于自托管的优势&#xf…
阅读更多...
快速了解高并发解决方案

快速了解高并发解决方案

对《高并发的哲学原理》的个人总结&#xff0c;原书地址如下 https://pphc.lvwenhan.com/ 本书的核心思想就是拆分&#xff0c;服务细化拆分多资源并行。 通用设计方法 例子&#xff1a;每秒100万次http请求 通过架构解决性能问题&#xff0c;在面对并发需求时&#xff…
阅读更多...
Tuxera NTFS for Mac 2023绿色版

Tuxera NTFS for Mac 2023绿色版

​ 在数字化时代&#xff0c;数据的存储和传输变得至关重要。Mac用户经常需要在Windows NTFS格式的移动硬盘上进行读写操作&#xff0c;然而&#xff0c;由于MacOS系统默认不支持NTFS的写操作&#xff0c;这就需要我们寻找一款高效的读写软件。Tuxera NTFS for Mac 2023便是其中…
阅读更多...
idea激活页面怎么打开

idea激活页面怎么打开

打开Help------选择Register 然后就可以选择激活方式了
阅读更多...
Java 入门指南：JVM（Java虚拟机）——类的生命周期与加载过程

Java 入门指南：JVM（Java虚拟机）——类的生命周期与加载过程

文章目录 类的生命周期类加载过程1&#xff09;载入&#xff08;Loading&#xff09;2&#xff09;验证&#xff08;Verification&#xff09;文件格式验证符号引用验证 3&#xff09;准备&#xff08;Preparation&#xff09;4&#xff09;解析&#xff08;Resolution&#xf…
阅读更多...
【渗透测试】——DVWA靶场搭建

【渗透测试】——DVWA靶场搭建

&#x1f4d6; 前言&#xff1a;DVWA&#xff08;Damn Vulnerable Web Application&#xff09;是一个用于安全漏洞测试的 PHP/MySQL 网络应用&#xff0c;旨在为安全专业人士提供一个合法的环境&#xff0c;以测试他们的技能和工具&#xff0c;同时帮助 Web 开发者更好地理解 …
阅读更多...
探索自动化的魔法：Python中的pyautogui库

探索自动化的魔法：Python中的pyautogui库

文章目录 探索自动化的魔法&#xff1a;Python中的 pyautogui 库背景&#xff1a;为什么选择pyautogui&#xff1f;pyautogui是什么&#xff1f;如何安装pyautogui&#xff1f;五个简单的库函数使用方法场景应用常见Bug及解决方案总结 探索自动化的魔法&#xff1a;Python中的 …
阅读更多...
Cyber Weekly #24

Cyber Weekly #24

赛博新闻 1、OpenAI发布最强模型o1 本周四&#xff08;9月12日&#xff09;&#xff0c;OpenAI宣布推出OpenAIo1系列模型&#xff0c;标志着AI推理能力的新高度。o1系列包括性能强大的o1以及经济高效的o1-mini&#xff0c;适用于不同复杂度的推理任务。新模型在科学、编码、数…
阅读更多...
用nginx-rtmp-win32-master及ffmpeg模拟rtmp视频流

用nginx-rtmp-win32-master及ffmpeg模拟rtmp视频流

效果 使用nginx-rtmp-win32-master搭建RTMP服务 双击exe就可以了。切记整个目录不能有中文 README.md ，启用后本地的RTM路径: rtmp://192.168.1.186/live/xxx ffmpeg将地本地视频推RMTP F:\rtsp\ffmpeg-7.0.2-essentials_build\bin>ffmpeg -re -i F:\rtsp\123.mp4 -c c…
阅读更多...
红黑树前语

红黑树前语

目录 概念 性质 红黑树与AVL树的比较 过两天更新红黑树的模拟实现,中秋快乐各位 概念 1. 概念&#xff1a; 是一种搜索二叉树&#xff0c; 但在每个结点上增加一个存储位表示节点的颜色&#xff0c;可以是Red 或 Black。通过对任何一条从根到叶子的路径上各个节点着色方式的…
阅读更多...
C++_20_多态

C++_20_多态

多继承会造成 菱形继承** 使用虚继承来解决 不是给爷爷类加 也不是给子类加 是给父类加 虚基指针和虚基表 多态 概念&#xff1a; 概念&#xff1a; 一个事物的多种形态&#xff0c;简称多态 如&#xff1a; 对象的多态 ​ 张三 ​ 在对象面前 怂 ​ 在朋友面前 谄媚 ​ 在父…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023