介绍
DWD 层是对MySQL业务行为数据（从insert和update体现出来）进行统计的一个层，即维度层存放事实表
主要作用是从各个维度对数据进行统计
事实表概述
事实表作为数据仓库维度建模的核心，紧紧围绕着业务过程来设计。其包含与该业务过程有关的维度引用（维度表外键）以及该业务过程的度量（通常是可累加的数字类型字段）
事务型事实表
事务型事实表用来记录各业务过程，它保存的是各业务过程的原子操作事件，即最细粒度的操作事件。粒度是指事实表中一行数据所表达的业务细节程度，事务型事实表可用于分析与各业务过程相关的各项统计指标，由于其保存了最细粒度的记录，可以提供最大限度的灵活性，可以支持无法预期的各种细节层次的统计需求
周期型快照事实表
周期快照事实表以具有规律性的、可预见的时间间隔来记录事实，主要用于分析一些存量型（例如商品库存，账户余额）或者状态型（空气温度，行驶速度）指标。
对于商品库存、账户余额这些存量型指标，业务系统中通常就会计算并保存最新结果，所以定期同步一份全量数据到数据仓库，构建周期型快照事实表，就能轻松应对此类统计需求，而无需再对事务型事实表中大量的历史记录进行聚合了。
对于空气温度、行驶速度这些状态型指标，由于它们的值往往是连续的，我们无法捕获其变动的原子事务操作，所以无法使用事务型事实表统计此类需求。而只能定期对其进行采样，构建周期型快照事实表。
累积型快照事实表
累计快照事实表是基于一个业务流程中的多个关键业务过程联合处理而构建的事实表，如交易流程中的下单、支付、发货、确认收货业务过程。
建表
DWD层的设计依据是维度建模理论，该层存储维度模型的事实表。
DWD层的数据存储格式为orc列式存储 snappy压缩。
DWD层表名的命名规范为dwd_数据域_表名_单分区增量全量标识（inc/full）
建表脚本
代码语言：shell复制vim CreateHiveDWDTable.hqlCREATE DATABASE IF NOT EXISTS gmall;
DROP TABLE IF EXISTS gmall.dwd_trade_cart_add_inc;
CREATE EXTERNAL TABLE gmall.dwd_trade_cart_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 '加购时间',
`sku_num` BIGINT COMMENT '加购物车件数'
) COMMENT '交易域加购事务事实表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_add_inc/'
TBLPROPERTIES ('orc.compress' = 'snappy');
DROP TABLE IF EXISTS gmall.dwd_trade_order_detail_inc;
CREATE EXTERNAL TABLE gmall.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 '下单时间',
`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)
STORED AS ORC
LOCATION '/warehouse/gmall/dwd/dwd_trade_order_detail_inc/'
TBLPROPERTIES ('orc.compress' = 'snappy');
DROP TABLE IF EXISTS gmall.dwd_trade_pay_detail_suc_inc;
CREATE EXTERNAL TABLE gmall.dwd_trade_pay_detail_suc_inc
(
`id` STRING COMMENT '编号',
`order_id` STRING COMMENT '订单ID',
`user_id` STRING COMMENT '用户ID',
`sku_id` STRING COMMENT 'SKU_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 '支付成功时间',
`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)
STORED AS ORC
LOCATION '/warehouse/gmall/dwd/dwd_trade_pay_detail_suc_inc/'
TBLPROPERTIES ('orc.compress' = 'snappy');
DROP TABLE IF EXISTS gmall.dwd_trade_cart_full;
CREATE EXTERNAL TABLE gmall.dwd_trade_cart_full
(
`id` STRING COMMENT '编号',
`user_id` STRING COMMENT '用户ID',
`sku_id` STRING COMMENT 'SKU_ID',
`sku_name` STRING COMMENT '商品名称',
`sku_num` BIGINT COMMENT '现存商品件数'
) COMMENT '交易域购物车周期快照事实表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_full/'
TBLPROPERTIES ('orc.compress' = 'snappy');
DROP TABLE IF EXISTS gmall.dwd_trade_trade_flow_acc;
CREATE EXTERNAL TABLE gmall.dwd_trade_trade_flow_acc
(
`order_id` STRING COMMENT '订单ID',
`user_id` STRING COMMENT '用户ID',
`province_id` STRING COMMENT '省份ID',
`order_date_id` STRING COMMENT '下单日期ID',
`order_time` STRING COMMENT '下单时间',
`payment_date_id` STRING COMMENT '支付日期ID',
`payment_time` STRING COMMENT '支付时间',
`finish_date_id` STRING COMMENT '确认收货日期ID',
`finish_time` STRING COMMENT '确认收货时间',
`order_original_amount` DECIMAL(16, 2) COMMENT '下单原始价格',
`order_activity_amount` DECIMAL(16, 2) COMMENT '下单活动优惠分摊',
`order_coupon_amount` DECIMAL(16, 2) COMMENT '下单优惠券优惠分摊',
`order_total_amount` DECIMAL(16, 2) COMMENT '下单最终价格分摊',
`payment_amount` DECIMAL(16, 2) COMMENT '支付金额'
) COMMENT '交易域交易流程累积快照事实表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dwd/dwd_trade_trade_flow_acc/'
TBLPROPERTIES ('orc.compress' = 'snappy');
DROP TABLE IF EXISTS gmall.dwd_tool_coupon_used_inc;
CREATE EXTERNAL TABLE gmall.dwd_tool_coupon_used_inc
(
`id` STRING COMMENT '编号',
`coupon_id` STRING COMMENT '优惠券ID',
`user_id` STRING COMMENT '用户ID',
`order_id` STRING COMMENT '订单ID',
`date_id` STRING COMMENT '日期ID',
`payment_time` STRING COMMENT '使用(支付)时间'
) COMMENT '优惠券使用（支付）事务事实表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dwd/dwd_tool_coupon_used_inc/'
TBLPROPERTIES ("orc.compress" = "snappy");
DROP TABLE IF EXISTS gmall.dwd_interaction_favor_add_inc;
CREATE EXTERNAL TABLE gmall.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 ORC
LOCATION '/warehouse/gmall/dwd/dwd_interaction_favor_add_inc/'
TBLPROPERTIES ("orc.compress" = "snappy");
DROP TABLE IF EXISTS gmall.dwd_traffic_page_view_inc;
CREATE EXTERNAL TABLE gmall.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 '上页ID',
`page_id` STRING COMMENT '页面ID ',
`from_pos_id` STRING COMMENT '点击坑位ID',
`from_pos_seq` STRING COMMENT '点击坑位位置',
`refer_id` STRING COMMENT '营销渠道ID',
`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 ORC
LOCATION '/warehouse/gmall/dwd/dwd_traffic_page_view_inc'
TBLPROPERTIES ('orc.compress' = 'snappy');
DROP TABLE IF EXISTS gmall.dwd_user_register_inc;
CREATE EXTERNAL TABLE gmall.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 ORC
LOCATION '/warehouse/gmall/dwd/dwd_user_register_inc/'
TBLPROPERTIES ("orc.compress" = "snappy");
DROP TABLE IF EXISTS gmall.dwd_user_login_inc;
CREATE EXTERNAL TABLE gmall.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 ORC
LOCATION '/warehouse/gmall/dwd/dwd_user_login_inc/'
TBLPROPERTIES ("orc.compress" = "snappy");数据装载脚本
首日数据装载
代码语言：shell复制vim ods_to_dwd_init.sh
chmod 777 ./ods_to_dwd_init.sh #!/bin/bash
APP=gmall
if [ -n "$2" ] ;then
do_date=$2
else
echo "请传入日期参数"
exit
fi
dwd_trade_cart_add_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition (dt)
select data.id,
data.user_id,
data.sku_id,
date_format(data.create_time, 'yyyy-MM-dd') date_id,
data.create_time,
data.sku_num,
date_format(data.create_time, 'yyyy-MM-dd')
from ${APP}.ods_cart_info_inc
where dt = '$do_date'
and type = 'bootstrap-insert';
"
dwd_trade_order_detail_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_trade_order_detail_inc partition (dt)
select
od.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,
sku_num,
split_original_amount,
nvl(split_activity_amount,0.0),
nvl(split_coupon_amount,0.0),
split_total_amount,
date_format(create_time,'yyyy-MM-dd')
from
(
select
data.id,
data.order_id,
data.sku_id,
data.create_time,
data.sku_num,
data.sku_num * data.order_price split_original_amount,
data.split_total_amount,
data.split_activity_amount,
data.split_coupon_amount
from ${APP}.ods_order_detail_inc
where dt = '$do_date'
and type = 'bootstrap-insert'
) od
left join
(
select
data.id,
data.user_id,
data.province_id
from ${APP}.ods_order_info_inc
where dt = '$do_date'
and type = 'bootstrap-insert'
) oi
on od.order_id = oi.id
left join
(
select
data.order_detail_id,
data.activity_id,
data.activity_rule_id
from ${APP}.ods_order_detail_activity_inc
where dt = '$do_date'
and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join
(
select
data.order_detail_id,
data.coupon_id
from ${APP}.ods_order_detail_coupon_inc
where dt = '$do_date'
and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id;
"
dwd_trade_pay_detail_suc_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_trade_pay_detail_suc_inc partition (dt)
select
od.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,
sku_num,
split_original_amount,
nvl(split_activity_amount,0.0),
nvl(split_coupon_amount,0.0),
split_total_amount,
date_format(callback_time,'yyyy-MM-dd')
from
(
select
data.id,
data.order_id,
data.sku_id,
data.sku_num,
data.sku_num * data.order_price split_original_amount,
data.split_total_amount,
data.split_activity_amount,
data.split_coupon_amount
from ${APP}.ods_order_detail_inc
where dt = '$do_date'
and type = 'bootstrap-insert'
) od
join
(
select
data.user_id,
data.order_id,
data.payment_type,
data.callback_time
from ${APP}.ods_payment_info_inc
where dt='$do_date'
and type='bootstrap-insert'
and data.payment_status='1602'
) pi
on od.order_id=pi.order_id
left join
(
select
data.id,
data.province_id
from ${APP}.ods_order_info_inc
where dt = '$do_date'
and type = 'bootstrap-insert'
) oi
on od.order_id = oi.id
left join
(
select
data.order_detail_id,
data.activity_id,
data.activity_rule_id
from ${APP}.ods_order_detail_activity_inc
where dt = '$do_date'
and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join
(
select
data.order_detail_id,
data.coupon_id
from ${APP}.ods_order_detail_coupon_inc
where dt = '$do_date'
and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id
left join
(
select
dic_code,
dic_name
from ${APP}.ods_base_dic_full
where dt='$do_date'
and parent_code='11'
) pay_dic
on pi.payment_type=pay_dic.dic_code;
"
dwd_trade_cart_full="
insert overwrite table ${APP}.dwd_trade_cart_full partition(dt='$do_date')
select
id,
user_id,
sku_id,
sku_name,
sku_num
from ${APP}.ods_cart_info_full
where dt='$do_date'
and is_ordered='0';
"
dwd_trade_trade_flow_acc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_trade_trade_flow_acc partition(dt)
select
oi.id,
user_id,
province_id,
date_format(create_time,'yyyy-MM-dd'),
create_time,
date_format(callback_time,'yyyy-MM-dd'),
callback_time,
date_format(finish_time,'yyyy-MM-dd'),
finish_time,
original_total_amount,
activity_reduce_amount,
coupon_reduce_amount,
total_amount,
nvl(payment_amount,0.0),
nvl(date_format(finish_time,'yyyy-MM-dd'),'9999-12-31')
from
(
select
data.id,
data.user_id,
data.province_id,
data.create_time,
data.original_total_amount,
data.activity_reduce_amount,
data.coupon_reduce_amount,
data.total_amount
from ${APP}.ods_order_info_inc
where dt='$do_date'
and type='bootstrap-insert'
)oi
left join
(
select
data.order_id,
data.callback_time,
data.total_amount payment_amount
from ${APP}.ods_payment_info_inc
where dt='$do_date'
and type='bootstrap-insert'
and data.payment_status='1602'
)pi
on oi.id=pi.order_id
left join
(
select
data.order_id,
data.create_time finish_time
from ${APP}.ods_order_status_log_inc
where dt='$do_date'
and type='bootstrap-insert'
and data.order_status='1004'
)log
on oi.id=log.order_id;
"
dwd_tool_coupon_used_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_tool_coupon_used_inc partition(dt)
select
data.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_interaction_favor_add_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_interaction_favor_add_inc partition(dt)
select
data.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_traffic_page_view_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition (dt='$do_date')
select
common.ar province_id,
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.item page_item,
page.item_type page_item_type,
page.last_page_id,
page.page_id,
page.from_pos_id,
page.from_pos_seq,
page.refer_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') view_time,
common.sid session_id,
page.during_time
from ${APP}.ods_log_inc
where dt='$do_date'
and page is not null;
set hive.cbo.enable=true;
"
dwd_user_register_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_user_register_inc partition(dt)
select
ui.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
(
select
data.id user_id,
data.create_time
from ${APP}.ods_user_info_inc
where dt='$do_date'
and type='bootstrap-insert'
)ui
left join
(
select
common.ar province_id,
common.ba brand,
common.ch channel,
common.md model,
common.mid mid_id,
common.os operate_system,
common.uid user_id,
common.vc version_code
from ${APP}.ods_log_inc
where dt='$do_date'
and page.page_id='register'
and common.uid is not null
)log
on ui.user_id=log.user_id;
"
dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition (dt = '$do_date')
select user_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 (
select user_id,
channel,
province_id,
version_code,
mid_id,
brand,
model,
operate_system,
ts
from (select common.uid user_id,
common.ch channel,
common.ar province_id,
common.vc version_code,
common.mid mid_id,
common.ba brand,
common.md model,
common.os operate_system,
ts,
row_number() over (partition by common.sid order by ts) rn
from ${APP}.ods_log_inc
where dt = '$do_date'
and page is not null
and common.uid is not null) t1
where rn = 1
) t2;
"
case $1 in
"dwd_trade_cart_add_inc" )
hive -e "$dwd_trade_cart_add_inc"
;;
"dwd_trade_order_detail_inc" )
hive -e "$dwd_trade_order_detail_inc"
;;
"dwd_trade_pay_detail_suc_inc" )
hive -e "$dwd_trade_pay_detail_suc_inc"
;;
"dwd_trade_cart_full" )
hive -e "$dwd_trade_cart_full"
;;
"dwd_trade_trade_flow_acc" )
hive -e "$dwd_trade_trade_flow_acc"
;;
"dwd_tool_coupon_used_inc" )
hive -e "$dwd_tool_coupon_used_inc"
;;
"dwd_interaction_favor_add_inc" )
hive -e "$dwd_interaction_favor_add_inc"
;;
"dwd_traffic_page_view_inc" )
hive -e "$dwd_traffic_page_view_inc"
;;
"dwd_user_register_inc" )
hive -e "$dwd_user_register_inc"
;;
"dwd_user_login_inc" )
hive -e "$dwd_user_login_inc"
;;
"all" )
hive -e "$dwd_trade_cart_add_inc$dwd_trade_order_detail_inc$dwd_trade_pay_detail_suc_inc$dwd_trade_cart_full$dwd_trade_trade_flow_acc$dwd_tool_coupon_used_inc$dwd_interaction_favor_add_inc$dwd_traffic_page_view_inc$dwd_user_register_inc$dwd_user_login_inc"
;;
esac每日数据装载
代码语言：shell复制vim ods_to_dwd.sh
chmod 777 ./ods_to_dwd.sh #!/bin/bash
APP=gmall
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
do_date=$2
else
do_date=`date -d "-1 day" %F`
fi
dwd_interaction_favor_add_inc="
insert overwrite table ${APP}.dwd_interaction_favor_add_inc partition(dt='$do_date')
select
data.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_used_inc="
insert overwrite table ${APP}.dwd_tool_coupon_used_inc partition(dt='$do_date')
select
data.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_cart_add_inc="
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition (dt = '$do_date')
select data.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,
if(type = 'insert', data.sku_num, cast(data.sku_num as int) - cast(old['sku_num'] as int)) sku_num
from ${APP}.ods_cart_info_inc
where dt = '$do_date'
and (type = 'insert'
or (type = 'update' and old['sku_num'] is not null and cast(data.sku_num as int) > cast(old['sku_num'] as int)));
"
dwd_trade_cart_full="
insert overwrite table ${APP}.dwd_trade_cart_full partition(dt='$do_date')
select
id,
user_id,
sku_id,
sku_name,
sku_num
from ${APP}.ods_cart_info_full
where dt='$do_date'
and is_ordered='0';
"
dwd_trade_trade_flow_acc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_trade_trade_flow_acc partition(dt)
select
oi.order_id,
user_id,
province_id,
order_date_id,
order_time,
nvl(oi.payment_date_id,pi.payment_date_id),
nvl(oi.payment_time,pi.payment_time),
nvl(oi.finish_date_id,log.finish_date_id),
nvl(oi.finish_time,log.finish_time),
order_original_amount,
order_activity_amount,
order_coupon_amount,
order_total_amount,
nvl(oi.payment_amount,pi.payment_amount),
nvl(nvl(oi.finish_time,log.finish_time),'9999-12-31')
from
(
select
order_id,
user_id,
province_id,
order_date_id,
order_time,
payment_date_id,
payment_time,
finish_date_id,
finish_time,
order_original_amount,
order_activity_amount,
order_coupon_amount,
order_total_amount,
payment_amount
from ${APP}.dwd_trade_trade_flow_acc
where dt='9999-12-31'
union all
select
data.id,
data.user_id,
data.province_id,
date_format(data.create_time,'yyyy-MM-dd') order_date_id,
data.create_time,
null payment_date_id,
null payment_time,
null finish_date_id,
null finish_time,
data.original_total_amount,
data.activity_reduce_amount,
data.coupon_reduce_amount,
data.total_amount,
null payment_amount
from ${APP}.ods_order_info_inc
where dt='$do_date'
and type='insert'
)oi
left join
(
select
data.order_id,
date_format(data.callback_time,'yyyy-MM-dd') payment_date_id,
data.callback_time payment_time,
data.total_amount payment_amount
from ${APP}.ods_payment_info_inc
where dt='$do_date'
and type='update'
and array_contains(map_keys(old),'payment_status')
and data.payment_status='1602'
)pi
on oi.order_id=pi.order_id
left join
(
select
data.order_id,
date_format(data.create_time,'yyyy-MM-dd') finish_date_id,
data.create_time finish_time
from ${APP}.ods_order_status_log_inc
where dt='$do_date'
and type='insert'
and data.order_status='1004'
)log
on oi.order_id=log.order_id;
"
dwd_trade_order_detail_inc="
insert overwrite table ${APP}.dwd_trade_order_detail_inc partition (dt='2022-06-09')
select
od.id,
order_id,
user_id,
sku_id,
province_id,
activity_id,
activity_rule_id,
coupon_id,
date_id,
create_time,
sku_num,
split_original_amount,
nvl(split_activity_amount,0.0),
nvl(split_coupon_amount,0.0),
split_total_amount
from
(
select
data.id,
data.order_id,
data.sku_id,
date_format(data.create_time, 'yyyy-MM-dd') date_id,
data.create_time,
data.sku_num,
data.sku_num * data.order_price split_original_amount,
data.split_total_amount,
data.split_activity_amount,
data.split_coupon_amount
from ${APP}.ods_order_detail_inc
where dt = '$do_date'
and type = 'insert'
) od
left join
(
select
data.id,
data.user_id,
data.province_id
from ${APP}.ods_order_info_inc
where dt = '$do_date'
and type = 'insert'
) oi
on od.order_id = oi.id
left join
(
select
data.order_detail_id,
data.activity_id,
data.activity_rule_id
from ${APP}.ods_order_detail_activity_inc
where dt = '$do_date'
and type = 'insert'
) act
on od.id = act.order_detail_id
left join
(
select
data.order_detail_id,
data.coupon_id
from ${APP}.ods_order_detail_coupon_inc
where dt = '$do_date'
and type = 'insert'
) cou
on od.id = cou.order_detail_id;
"
dwd_trade_pay_detail_suc_inc="
insert overwrite table ${APP}.dwd_trade_pay_detail_suc_inc partition (dt='$do_date')
select
od.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,
sku_num,
split_original_amount,
nvl(split_activity_amount,0.0),
nvl(split_coupon_amount,0.0),
split_total_amount
from
(
select
data.id,
data.order_id,
data.sku_id,
data.sku_num,
data.sku_num * data.order_price split_original_amount,
data.split_total_amount,
data.split_activity_amount,
data.split_coupon_amount
from ${APP}.ods_order_detail_inc
where (dt = '$do_date' or dt = date_add('$do_date',-1))
and (type = 'insert' or type = 'bootstrap-insert')
) od
join
(
select
data.user_id,
data.order_id,
data.payment_type,
data.callback_time
from ${APP}.ods_payment_info_inc
where 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
(
select
data.id,
data.province_id
from ${APP}.ods_order_info_inc
where (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
(
select
data.order_detail_id,
data.activity_id,
data.activity_rule_id
from ${APP}.ods_order_detail_activity_inc
where (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
(
select
data.order_detail_id,
data.coupon_id
from ${APP}.ods_order_detail_coupon_inc
where (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
(
select
dic_code,
dic_name
from ${APP}.ods_base_dic_full
where dt='$do_date'
and parent_code='11'
) pay_dic
on pi.payment_type=pay_dic.dic_code;
"
dwd_traffic_page_view_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition (dt='$do_date')
select
common.ar province_id,
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.item page_item,
page.item_type page_item_type,
page.last_page_id,
page.page_id,
page.from_pos_id,
page.from_pos_seq,
page.refer_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') view_time,
common.sid session_id,
page.during_time
from ${APP}.ods_log_inc
where dt='$do_date'
and page is not null;
set hive.cbo.enable=true;
"
dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition (dt = '$do_date')
select user_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 (
select user_id,
channel,
province_id,
version_code,
mid_id,
brand,
model,
operate_system,
ts
from (select common.uid user_id,
common.ch channel,
common.ar province_id,
common.vc version_code,
common.mid mid_id,
common.ba brand,
common.md model,
common.os operate_system,
ts,
row_number() over (partition by common.sid order by ts) rn
from ${APP}.ods_log_inc
where dt = '$do_date'
and page is not null
and common.uid is not null) t1
where rn = 1
) t2;
"
dwd_user_register_inc="
insert overwrite table ${APP}.dwd_user_register_inc partition(dt='$do_date')
select
ui.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
(
select
data.id user_id,
data.create_time
from ${APP}.ods_user_info_inc
where dt='$do_date'
and type='insert'
)ui
left join
(
select
common.ar province_id,
common.ba brand,
common.ch channel,
common.md model,
common.mid mid_id,
common.os operate_system,
common.uid user_id,
common.vc version_code
from ${APP}.ods_log_inc
where dt='$do_date'
and page.page_id='register'
and common.uid is not null
)log
on ui.user_id=log.user_id;
"
case $1 in
"dwd_trade_cart_add_inc" )
hive -e "$dwd_trade_cart_add_inc"
;;
"dwd_trade_order_detail_inc" )
hive -e "$dwd_trade_order_detail_inc"
;;
"dwd_trade_pay_detail_suc_inc" )
hive -e "$dwd_trade_pay_detail_suc_inc"
;;
"dwd_trade_cart_full" )
hive -e "$dwd_trade_cart_full"
;;
"dwd_trade_trade_flow_acc" )
hive -e "$dwd_trade_trade_flow_acc"
;;
"dwd_tool_coupon_used_inc" )
hive -e "$dwd_tool_coupon_used_inc"
;;
"dwd_interaction_favor_add_inc" )
hive -e "$dwd_interaction_favor_add_inc"
;;
"dwd_traffic_page_view_inc" )
hive -e "$dwd_traffic_page_view_inc"
;;
"dwd_user_register_inc" )
hive -e "$dwd_user_register_inc"
;;
"dwd_user_login_inc" )
hive -e "$dwd_user_login_inc"
;;
"all" )
hive -e "$dwd_trade_cart_add_inc$dwd_trade_order_detail_inc$dwd_trade_pay_detail_suc_inc$dwd_trade_cart_full$dwd_trade_trade_flow_acc$dwd_tool_coupon_used_inc$dwd_interaction_favor_add_inc$dwd_traffic_page_view_inc$dwd_user_register_inc$dwd_user_login_inc"
;;
esac
