离线数仓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.xdnf.cn/news/141454.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

2024/9/15 408“回头看”之应用层小总结（下）

2024/9/15 408“回头看”之应用层小总结（下）

域名系统DNS: 本地域名服务器 本地域名服务器起着代理的作用&#xff0c;会将报文转发到根域名服务器、顶级域名服务器、权限域名服务器。 递归查询&#xff1a; 迭代查询&#xff1a; 文件传送协议FTP: FTP客户和FTP服务器之间使用的是tcp连接。 控制连接使用21端口&…
阅读更多...
树莓派5上手

树莓派5上手

1 安装系统 Raspberry Pi OS 是基于 Debian 的免费操作系统&#xff0c;针对 Raspberry Pi 硬件进行了优化。Raspberry Pi OS 支持超过 35,000 个 Debian 软件包。树莓派 5 可以安装各种系统&#xff0c;但是如果对于系统没有特殊的要求&#xff0c;还是安装 Raspberry Pi OS …
阅读更多...
基于SSM的二手车管理系统的设计与实现 （含源码+sql+视频导入教程）

基于SSM的二手车管理系统的设计与实现 （含源码+sql+视频导入教程）

&#x1f449;文末查看项目功能视频演示获取源码sql脚本视频导入教程视频 1 、功能描述 基于SSM的二手车管理系统4拥有三种角色 管理员&#xff1a;订单管理、在售车辆管理、下架车辆管理、品牌管理、分类管理、推荐管理、统计等 商家&#xff1a;登录注册、添加/下架/删除车辆…
阅读更多...
各类元器件调试记录-E+H

各类元器件调试记录-E+H

一、EH压力传感器 适用型号为&#xff1a; Cerabar S PMC71, PMP71/75 Deltabar S FMD76/77/78, PMD70/75 Deltapilot S FMB70 调试过程&#xff1a;(后续补上图片) 一、湿标(湿调) 1、前提条件&#xff1a;罐体可以灌满和实际水箱水位高度 2、调试步骤&#xff1a; A、调节语…
阅读更多...
网络安全有救了，37所高校新增网络安全空间安全专业

网络安全有救了，37所高校新增网络安全空间安全专业

《网安面试指南》http://mp.weixin.qq.com/s?__bizMzkwNjY1Mzc0Nw&mid2247484339&idx1&sn356300f169de74e7a778b04bfbbbd0ab&chksmc0e47aeff793f3f9a5f7abcfa57695e8944e52bca2de2c7a3eb1aecb3c1e6b9cb6abe509d51f&scene21#wechat_redirect 《Java代码审…
阅读更多...
凸优化学习（3）——对偶方法、KKT条件、ADMM

凸优化学习（3）——对偶方法、KKT条件、ADMM

&#x1f345; 写在前面 &#x1f468;‍&#x1f393; 博主介绍&#xff1a;大家好&#xff0c;这里是hyk写算法了吗&#xff0c;一枚致力于学习算法和人工智能领域的小菜鸟。 &#x1f50e;个人主页&#xff1a;主页链接&#xff08;欢迎各位大佬光临指导&#xff09; ⭐️近…
阅读更多...
鸿蒙交互事件开发07——手势竞争问题

鸿蒙交互事件开发07——手势竞争问题

如果你也对鸿蒙开发感兴趣&#xff0c;加入“Harmony自习室”吧&#xff01;扫描下方名片&#xff0c;关注公众号&#xff0c;公众号更新更快&#xff0c;同时也有更多学习资料和技术讨论群。 1、背景 在文章鸿蒙交互事件开发05——常用的6种手势类型中&#xff0c;有朋友留言…
阅读更多...
C语言自定义类型-联合与枚举

C语言自定义类型-联合与枚举

在之前的文章中&#xff0c;我们学到了结构体类型&#xff0c;而结构体其实归属于一个大类——自定义类型。那么今天我们就继续讲解关于自定义类型的知识~ 一、类型命名关键字-typedef typedef的作用其实就是标题的意思——为一种类型赋予新的名字。 ① typedef在变量中的应…
阅读更多...
Java【集合】

Java【集合】

一、集合的概述 集合建立在数组基础上&#xff0c;主要位于java.util包中&#xff0c;用来存储Java类对象&#xff0c;并且可以实现各种数据结构。 集合大小可以改变&#xff0c;可以存放不同数据类型数据。集合不能存放基本类型数据&#xff0c;只能存放引用数据类型数据。集…
阅读更多...
力扣题解2848

力扣题解2848

大家好&#xff0c;欢迎来到无限大的频道。 今日继续给大家带来力扣题解。 题目描述&#xff08;简单&#xff09;&#xff1a; 与车相交的点 给你一个下标从 0 开始的二维整数数组 nums 表示汽车停放在数轴上的坐标。对于任意下标 i&#xff0c;nums[i] [starti, endi] &…
阅读更多...
中考全国45套（全国教育发达地区中考试卷）

中考全国45套（全国教育发达地区中考试卷）

文章目录 获取方式 为什么选择这份资源&#xff1f; 权威性与全面性&#xff1a;我们精心搜集了全国教育发达地区的最新中考试卷&#xff0c;确保每一套试卷都代表了该地区的教学水平和考试趋势。这不仅涵盖了丰富的知识点&#xff0c;还融入了各地独特的命题风格&#xff0c;让…
阅读更多...
2020ICPC上海 D - Walker M - Gitignore

2020ICPC上海 D - Walker M - Gitignore

D: 首先显然要二分,判断当前二分的mid时间下是否能满足走满0~n 枚举所有情况,这里按照左,右起点p1,p2分别讨论 p1向左 p2向左(以下向左和向右都代表向左或者向右到墙,而不代表初速度方向)&#xff0c;只需要计算p1或者p2反弹之后还能走距离n就是合法 p1向左 p2向右&#xff…
阅读更多...
3.4.2 __ipipe_init_early之fixup_percpu_data()

3.4.2 __ipipe_init_early之fixup_percpu_data()

点击查看系列文章 》 Interrupt Pipeline系列文章大纲-CSDN博客 3.4.2 __ipipe_init_early之fixup_percpu_data() 这个函数只有在CPU是SMP对称多core的情况下&#xff0c;才会真正运作&#xff0c;否则就是个空函数。 #ifdef CONFIG_SMPstatic inline void fixup_percpu_data…
阅读更多...
H5 three.js 实现六年级观察物体

H5 three.js 实现六年级观察物体

o(&#xffe3;▽&#xffe3;)ブ 我又带着新的demo来啦~ 预览 功能点 立方体的阴影 立方体的添加 位置记录 最大限制 三视图展示 立方体的移除 答题模式 随机出题 题库出题 源码 注释算是比较全了&#xff0c;可能部分会有点绕&#xff0c;还能够再优化一下~ <!DOCTYPE …
阅读更多...
【代码随想录训练营第42期 续Day58打卡 - 图论Part8 - Dijkstra算法

【代码随想录训练营第42期 续Day58打卡 - 图论Part8 - Dijkstra算法

目录 一、Dijkstra算法 实现方式 1、使用优先队列&#xff08;最小堆&#xff09; 2、朴素法&#xff08;简单数组&#xff09; 二、经典例题 题目&#xff1a;卡码网 47. 参加科学大会 题目链接 题解&#xff1a;朴素Dijkstra 三、小结 一、Dijkstra算法 刚入门Dijks…
阅读更多...
AI论文写作测评！类似茅茅虫论文写作助手网站

AI论文写作测评！类似茅茅虫论文写作助手网站

在当前的学术研究和写作环境中&#xff0c;AI论文写作助手成为了许多学者和学生的重要工具。其中&#xff0c;千笔-AIPassPaper和茅茅虫论文写作助手是两款备受关注的平台。本文将对这两款工具进行详细测评&#xff0c;并推荐适合不同需求的用户使用。 千笔-AIPassPaper AI论文…
阅读更多...
实现领域驱动设计（DDD）系列详解：限界上下文

实现领域驱动设计（DDD）系列详解：限界上下文

随着微服务的兴起&#xff0c;限界上下文更是被拔高到战略设计的核心地位&#xff0c;也成了连接问题空间与解空间的重要桥梁&#xff0c;但不可否认&#xff0c;一方面&#xff0c;领域驱动设计社区纷纷发声强调它的重要性&#xff1b;另一方面&#xff0c;还有很多人依旧弄不…
阅读更多...
游戏算法专题之PRD算法:听说你想凭运气抽中荣耀水晶?

游戏算法专题之PRD算法:听说你想凭运气抽中荣耀水晶?

PRD算法全称Pseudo-Random Distribution。是概率分布中的一种常见算法&#xff0c;在游戏开发领域中很常用。 PRD用于控制随机事件的触发概率&#xff0c;使其表现得更加符合预期&#xff0c;相比于传统得随机数生成&#xff0c;PRD算法可以平滑得控制随机事件的触发次数&…
阅读更多...
计算机毕业设计 二手闲置交易系统的设计与实现 Java实战项目 附源码+文档+视频讲解

计算机毕业设计 二手闲置交易系统的设计与实现 Java实战项目 附源码+文档+视频讲解

博主介绍&#xff1a;✌从事软件开发10年之余&#xff0c;专注于Java技术领域、Python人工智能及数据挖掘、小程序项目开发和Android项目开发等。CSDN、掘金、华为云、InfoQ、阿里云等平台优质作者✌ &#x1f345;文末获取源码联系&#x1f345; &#x1f447;&#x1f3fb; 精…
阅读更多...
什么是快充协议，最常见的快充协议有哪些

什么是快充协议，最常见的快充协议有哪些

什么是快充协议 随着手机快充的出现大家都知道快充技术但很多人确不知道快充协议&#xff0c;在快充技术里快充协议是必不可少的&#xff0c;那么今天我们就来探讨一下什么是快充协议&#xff1f; 快充协议是一种通过提高充电效率来缩短设备充电时间的电池充电技术。它通过在充…
阅读更多...
最新文章

Copyright @ 2022~2023