大家好,又见面了,我是你们的朋友全栈君。
DWD层设计要点:
数据域 | 业务过程 | 粒度 | 维度 | 度量 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
时间 | 用户 | 商品 | 地区 | 活动(具体规则) | 优惠券 | 支付方式 | 退单类型 | 退单原因类型 | 渠道 | 设备 | ||||
交易域 | 加购物车 | 一次加购物车的操作 | √ | √ | √ | 商品件数 | ||||||||
下单 | 一个订单中一个商品项 | √ | √ | √ | √ | √ | √ | 下单件数/下单原始金额/下单最终金额/活动优惠金额/优惠券优惠金额 | ||||||
取消订单 | 一次取消订单操作 | √ | √ | √ | √ | √ | √ | 下单件数/下单原始金额/下单最终金额/活动优惠金额/优惠券优惠金额 | ||||||
支付成功 | 一个订单中的一个商品项的支付成功操作 | √ | √ | √ | √ | √ | √ | √ | 支付件数/支付原始金额/支付最终金额/活动优惠金额/优惠券优惠金额 | |||||
退单 | 一次退单操作 | √ | √ | √ | √ | √ | √ | 退单件数/退单金额 | ||||||
退款成功 | 一次退款成功操作 | √ | √ | √ | √ | √ | 退款件数/退款金额 |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
user_id | 用户id | varchar(200) |
sku_id | skuid | bigint(20) |
cart_price | 放入购物车时价格 | decimal(10,2) |
sku_num | 数量 | int(11) |
img_url | 图片文件 | varchar(200) |
sku_name | sku名称 (冗余) | varchar(200) |
is_checked | int(1) | |
create_time | 创建时间 | datetime |
operate_time | 修改时间 | datetime |
is_ordered | 是否已经下单 | bigint(20) |
order_time | 下单时间 | datetime |
source_type | 来源类型 | varchar(20) |
source_id | 来源编号 | bigint(20) |
-- 加购事务事实表
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 ORC
LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_add_inc/'
TBLPROPERTIES ('orc.compress' = 'snappy');
-- 首日装载
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_cart_add_inc partition (dt)
select id,
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') dt
from (
select data.id,
data.user_id,
data.sku_id,
data.create_time,
data.source_id,
data.source_type,
data.sku_num
from ods_cart_info_inc
where dt = '2020-06-14'
and type = 'bootstrap-insert'
) ci
left join
(
select dic_code,
dic_name
from ods_base_dic_full
where dt = '2020-06-14'
and parent_code = '24'
) dic
on
ci.source_type = dic.dic_code;
-- 每日装载
insert overwrite table dwd_trade_cart_add_inc partition(dt = '2020-06-15')
select
id,
user_id,
sku_id,
date_id,
create_time,
source_id,
source_type_code,
dic.dic_name source_type_name,
sku_num
from (
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,
data.source_id,
data.source_type source_type_code,
if(type = 'insert', data.sku_num, data.sku_num - old['sku_num']) sku_num
from ods_cart_info_inc
where 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 (
select dic_code,
dic_name
from ods_base_dic_full
where dt = '2020-06-14'
and parent_code = '24'
) dic
on cart.source_type_code = dic.dic_code;
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
order_id | 订单编号 | bigint(20) |
sku_id | sku_id | bigint(20) |
sku_name | sku名称(冗余) | varchar(200) |
img_url | 图片名称(冗余) | varchar(200) |
order_price | 购买价格(下单时sku价格) | decimal(10,2) |
sku_num | 购买个数 | bigint(20) |
create_time | 创建时间 | datetime |
source_type | 来源类型 | varchar(20) |
source_id | 来源编号 | bigint(20) |
split_total_amount | 分摊总金额 | decimal(16,2) |
split_activity_amount | 分摊活动减免金额 | decimal(16,2) |
split_coupon_amount | 分摊优惠券减免金额 | decimal(16,2) |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
consignee | 收货人 | varchar(100) |
consignee_tel | 收件人电话 | varchar(20) |
total_amount | 总金额 | |
order_status | 订单状态 | varchar(20) |
user_id | 用户id | bigint(20) |
payment_way | 付款方式 | varchar(20) |
delivery_address | 送货地址 | varchar(1000) |
order_comment | 订单备注 | varchar(200) |
out_trade_no | 订单交易编号(第三方支付用) | varchar(50) |
trade_body | 订单描述(第三方支付用) | varchar(200) |
create_time | 创建时间 | datetime |
operate_time | 操作时间 | datetime |
expire_time | 失效时间 | datetime |
process_status | 进度状态 | varchar(20) |
tracking_no | 物流单编号 | varchar(100) |
parent_order_id | 父订单编号 | bigint(20) |
img_url | 图片路径 | varchar(200) |
province_id | 地区 | int(20) |
activity_reduce_amount | 促销金额 | decimal(16,2) |
coupon_reduce_amount | 优惠券 | decimal(16,2) |
original_total_amount | 原价金额 | decimal(16,2) |
freight_fee | 运费 | decimal(16,2) |
freight_fee_reduce | 运费减免 | decimal(16,2) |
refundable_time | 可退款日期(签收后30天) | datetime |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
order_id | 订单id | bigint(20) |
order_detail_id | 订单明细id | bigint(20) |
activity_id | 活动ID | bigint(20) |
activity_rule_id | 活动规则 | bigint(20) |
sku_id | skuID | bigint(20) |
create_time | 获取时间 | datetime |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
order_id | 订单id | bigint(20) |
order_detail_id | 订单明细id | bigint(20) |
coupon_id | 购物券ID | bigint(20) |
coupon_use_id | 购物券领用id | bigint(20) |
sku_id | skuID | bigint(20) |
create_time | 获取时间 | datetime |
字段名 | 字段说明 | 类型 |
---|---|---|
dic_code | 编号 | varchar(10) |
dic_name | 编码名称 | varchar(100) |
parent_code | 父编号 | varchar(10) |
create_time | 创建日期 | datetime(0) |
operate_time | 修改日期 | datetime(0) |
-- 交易域下单事务事实表
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 ORC
LOCATION '/warehouse/gmall/dwd/dwd_trade_order_detail_inc/'
TBLPROPERTIES ('orc.compress' = 'snappy');
-- 首日装载 交易域下单事务事实表
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table 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,
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 (
select data.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_amount
from ods_order_detail_inc
where dt = '2020-06-14'
and type = 'bootstrap-insert'
) od
left join (
select data.id,
data.user_id,
data.province_id
from ods_order_info_inc
where dt = '2020-06-14'
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 ods_order_detail_activity_inc
where dt = '2020-06-14'
and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join (
select data.order_detail_id,
data.coupon_id
from ods_order_detail_coupon_inc
where dt = '2020-06-14'
and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id
left join (
select dic_name,
dic_code
from ods_base_dic_full
where dt = '2020-06-14'
and parent_code = '24'
) dic
on od.source_type = dic.dic_code;
-- 每日装载 交易域下单事务事实表
insert overwrite table dwd_trade_order_detail_inc
partition (dt = '2020-06-15')
select od.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 (
select data.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_amount
from ods_order_detail_inc
where dt = '2020-06-15'
and type = 'insert'
) od
left join (
select data.id,
data.user_id,
data.province_id
from ods_order_info_inc
where dt = '2020-06-15'
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 ods_order_detail_activity_inc
where dt = '2020-06-15'
and type = 'insert'
) act
on od.id = act.order_detail_id
left join (
select data.order_detail_id,
data.coupon_id
from ods_order_detail_coupon_inc
where dt = '2020-06-15'
and type = 'insert'
) cou
on od.id = cou.order_detail_id
left join (
select dic_name,
dic_code
from ods_base_dic_full
where dt = '2020-06-15'
and parent_code = '24'
) dic
on od.source_type = dic.dic_code;
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
order_id | 订单编号 | bigint(20) |
sku_id | sku_id | bigint(20) |
sku_name | sku名称(冗余) | varchar(200) |
img_url | 图片名称(冗余) | varchar(200) |
order_price | 购买价格(下单时sku价格) | decimal(10,2) |
sku_num | 购买个数 | bigint(20) |
create_time | 创建时间 | datetime |
source_type | 来源类型 | varchar(20) |
source_id | 来源编号 | bigint(20) |
split_total_amount | 分摊总金额 | decimal(16,2) |
split_activity_amount | 分摊活动减免金额 | decimal(16,2) |
split_coupon_amount | 分摊优惠券减免金额 | decimal(16,2) |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
consignee | 收货人 | varchar(100) |
consignee_tel | 收件人电话 | varchar(20) |
total_amount | 总金额 | |
order_status | 订单状态 | varchar(20) |
user_id | 用户id | bigint(20) |
payment_way | 付款方式 | varchar(20) |
delivery_address | 送货地址 | varchar(1000) |
order_comment | 订单备注 | varchar(200) |
out_trade_no | 订单交易编号(第三方支付用) | varchar(50) |
trade_body | 订单描述(第三方支付用) | varchar(200) |
create_time | 创建时间 | datetime |
operate_time | 操作时间 | datetime |
expire_time | 失效时间 | datetime |
process_status | 进度状态 | varchar(20) |
tracking_no | 物流单编号 | varchar(100) |
parent_order_id | 父订单编号 | bigint(20) |
img_url | 图片路径 | varchar(200) |
province_id | 地区 | int(20) |
activity_reduce_amount | 促销金额 | decimal(16,2) |
coupon_reduce_amount | 优惠券 | decimal(16,2) |
original_total_amount | 原价金额 | decimal(16,2) |
freight_fee | 运费 | decimal(16,2) |
freight_fee_reduce | 运费减免 | decimal(16,2) |
refundable_time | 可退款日期(签收后30天) | datetime |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
order_id | 订单id | bigint(20) |
order_detail_id | 订单明细id | bigint(20) |
activity_id | 活动ID | bigint(20) |
activity_rule_id | 活动规则 | bigint(20) |
sku_id | skuID | bigint(20) |
create_time | 获取时间 | datetime |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
order_id | 订单id | bigint(20) |
order_detail_id | 订单明细id | bigint(20) |
coupon_id | 购物券ID | bigint(20) |
coupon_use_id | 购物券领用id | bigint(20) |
sku_id | skuID | bigint(20) |
create_time | 获取时间 | datetime |
字段名 | 字段说明 | 类型 |
---|---|---|
dic_code | 编号 | varchar(10) |
dic_name | 编码名称 | varchar(100) |
parent_code | 父编号 | varchar(10) |
create_time | 创建日期 | datetime(0) |
operate_time | 修改日期 | datetime(0) |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | int(11) |
out_trade_no | 对外业务编号 | varchar(50) |
order_id | 订单编号 | bigint(50) |
user_id | 用户id | bigint(20) |
payment_type | 支付类型(微信 支付宝) | varchar(20) |
trade_no | 交易编号 | varchar(50) |
total_amount | 支付金额 | decimal(10,2) |
subject | 交易内容 | varchar(200) |
payment_status | 支付状态 | varchar(20) |
create_time | 创建时间 | datetime |
callback_time | 回调时间 | datetime |
callback_content | 回调信息 | text |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
order_id | 订单编号 | bigint(20) |
sku_id | sku_id | bigint(20) |
sku_name | sku名称(冗余) | varchar(200) |
img_url | 图片名称(冗余) | varchar(200) |
order_price | 购买价格(下单时sku价格) | decimal(10,2) |
sku_num | 购买个数 | bigint(20) |
create_time | 创建时间 | datetime |
source_type | 来源类型 | varchar(20) |
source_id | 来源编号 | bigint(20) |
split_total_amount | 分摊总金额 | decimal(16,2) |
split_activity_amount | 分摊活动减免金额 | decimal(16,2) |
split_coupon_amount | 分摊优惠券减免金额 | decimal(16,2) |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
consignee | 收货人 | varchar(100) |
consignee_tel | 收件人电话 | varchar(20) |
total_amount | 总金额 | |
order_status | 订单状态 | varchar(20) |
user_id | 用户id | bigint(20) |
payment_way | 付款方式 | varchar(20) |
delivery_address | 送货地址 | varchar(1000) |
order_comment | 订单备注 | varchar(200) |
out_trade_no | 订单交易编号(第三方支付用) | varchar(50) |
trade_body | 订单描述(第三方支付用) | varchar(200) |
create_time | 创建时间 | datetime |
operate_time | 操作时间 | datetime |
expire_time | 失效时间 | datetime |
process_status | 进度状态 | varchar(20) |
tracking_no | 物流单编号 | varchar(100) |
parent_order_id | 父订单编号 | bigint(20) |
img_url | 图片路径 | varchar(200) |
province_id | 地区 | int(20) |
activity_reduce_amount | 促销金额 | decimal(16,2) |
coupon_reduce_amount | 优惠券 | decimal(16,2) |
original_total_amount | 原价金额 | decimal(16,2) |
freight_fee | 运费 | decimal(16,2) |
freight_fee_reduce | 运费减免 | decimal(16,2) |
refundable_time | 可退款日期(签收后30天) | datetime |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
order_id | 订单id | bigint(20) |
order_detail_id | 订单明细id | bigint(20) |
activity_id | 活动ID | bigint(20) |
activity_rule_id | 活动规则 | bigint(20) |
sku_id | skuID | bigint(20) |
create_time | 获取时间 | datetime |
字段名 | 字段说明 | 类型 |
---|---|---|
id | 编号 | bigint(20) |
order_id | 订单id | bigint(20) |
order_detail_id | 订单明细id | bigint(20) |
coupon_id | 购物券ID | bigint(20) |
coupon_use_id | 购物券领用id | bigint(20) |
sku_id | skuID | bigint(20) |
create_time | 获取时间 | datetime |
字段名 | 字段说明 | 类型 |
---|---|---|
dic_code | 编号 | varchar(10) |
dic_name | 编码名称 | varchar(100) |
parent_code | 父编号 | varchar(10) |
create_time | 创建日期 | datetime(0) |
operate_time | 修改日期 | datetime(0) |
数据域 | 业务过程 | 粒度 | 维度 | 度量 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
时间 | 用户 | 商品 | 地区 | 活动(具体规则) | 优惠券 | 支付方式 | 退单类型 | 退单原因类型 | 渠道 | 设备 | ||||
工具域 | 领取优惠券 | 一次优惠券领取操作 | √ | √ | √ | 无事实(次数1) | ||||||||
使用优惠券(下单) | 一次优惠券使用(下单)操作 | √ | √ | √ | 无事实(次数1) | |||||||||
使用优惠券(支付) | 一次优惠券使用(支付)操作 | √ | √ | √ | 无事实(次数1) |
数据域 | 业务过程 | 粒度 | 维度 | 度量 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
时间 | 用户 | 商品 | 地区 | 活动(具体规则) | 优惠券 | 支付方式 | 退单类型 | 退单原因类型 | 渠道 | 设备 | ||||
互动域 | 收藏商品 | 一次收藏商品操作 | √ | √ | √ | 无事实(次数1) | ||||||||
评价 | 一次取消收藏商品操作 | √ | √ | √ | 无事实(次数1) |
数据域 | 业务过程 | 粒度 | 维度 | 度量 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
时间 | 用户 | 商品 | 地区 | 活动(具体规则) | 优惠券 | 支付方式 | 退单类型 | 退单原因类型 | 渠道 | 设备 | ||||
流量域 | 页面浏览 | 一次页面浏览记录 | √ | √ | √ | √ | √ | 浏览时长 | ||||||
动作 | 一次动作记录 | √ | √ | √ | √ | √ | √ | √ | 无事实(次数1) | |||||
曝光 | 一次曝光记录 | √ | √ | √ | √ | √ | √ | √ | 无事实(次数1) | |||||
启动应用 | 一次启动记录 | √ | √ | √ | √ | √ | 无事实(次数1) | |||||||
错误 | 一次错误记录 | √ | √ | √ | √ | 无事实(次数1) |
数据域 | 业务过程 | 粒度 | 维度 | 度量 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
时间 | 用户 | 商品 | 地区 | 活动(具体规则) | 优惠券 | 支付方式 | 退单类型 | 退单原因类型 | 渠道 | 设备 | ||||
用户域 | 注册 | 一次注册操作 | √ | √ | 无事实(次数1) | |||||||||
登录 | 一次登录操作 | √ | √ | √ | √ | √ | 无事实(次数1) |
创建 ods_to_dwd_init.sh
vim ods_to_dwd_init.sh
脚本执行权限
chmod 777 ods_to_dwd_init.sh
创建 ods_to_dwd.sh
vim ods_to_dwd.sh
脚本执行权限
chmod 777 ods_to_dwd.sh
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/153233.html原文链接:https://javaforall.cn