网站建设资料准备标准,建设部网站证件查询,滨州网站建设招聘,长沙seo公司网站优化离线数仓-8-数据仓库开发DWD层设计要点-交易域相关事实表离线数仓-8-数据仓库开发DWD层设计要点-交易域相关事实表一、DWD层设计要点二、交易域相关事实表1.交易域加购事务事实表1.加购事务事实表 前期梳理2.加购事务事实表 DDL表设计分析3.加购事务事实表 加载数据分析1.首日全…
离线数仓-8-数据仓库开发DWD层设计要点-交易域相关事实表离线数仓-8-数据仓库开发DWD层设计要点-交易域相关事实表一、DWD层设计要点二、交易域相关事实表1.交易域加购事务事实表1.加购事务事实表 前期梳理2.加购事务事实表 DDL表设计分析3.加购事务事实表 加载数据分析1.首日全量加购的数据加载2.每日增量加购的数据加载2.交易域下单事务事实表1.下单事务事实表 前期梳理2.下单事务事实表 DDL表设计分析3.下单事务事实表 加载数据分析1.首日全量下单的数据加载2.每日增量量下单的数据加载3.交易域取消订单事务事实表1.取消订单事务事实表 前期梳理2.取消订单事务事实表 DDL表设计分析3.取消订单事务事实表 加载数据分析1.首日全量取消订单的数据加载2.每日增量取消订单的数据加载7.交易域购物车周期快照事实表1.购物车周期快照事实表 前期梳理2.购物车周期快照事实表 DDL表设计分析3.购物车周期快照事实表 加载数据分析4.交易域支付成功事务事实表1.支付成功事务事实表 前期梳理2.支付成功事务事实表 DDL表设计分析3.支付成功事务事实表 加载数据分析5.交易域退单事务事实表1.退单事务事实表 前期梳理2.退单事务事实表 DDL表设计分析3.退单事务事实表 加载数据分析6.交易域退款成功事务事实表1.退款成功事务事实表 前期梳理2.退款成功事务事实表 DDL表设计分析3.退款成功事务事实表 加载数据分析离线数仓-8-数据仓库开发DWD层设计要点-交易域相关事实表
一、DWD层设计要点
DWD层设计要点 1DWD层的设计依据是维度建模理论该层存储维度模型的事实表。 事实表维度建模理论参考之前整理资料https://blog.csdn.net/weixin_38136584/article/details/129137583?spm1001.2014.3001.5501 2DWD层的数据存储格式为orc列式存储snappy压缩。3DWD层表名的命名规范为dwd_数据域_表名(体现业务过程)_单分区增量全量标识inc/full
二、交易域相关事实表
事实事务表设计流程大概分为4步选择业务过程 -- 声明粒度 -- 确认维度– 确认事实
1.交易域加购事务事实表
1.加购事务事实表 前期梳理
加购事务事实表 设计流程跟事务事实表流程一致分为四步进行。查看之前梳理的业务矩阵基于业务矩阵来进行设计流程4步骤分析 1.选择业务过程加购物车2.声明粒度业务过程确定后需要为每个业务过程声明粒度。即精确定义每张事务型事实表的每行数据表示什么应该尽可能选择最细粒度以此来应各种细节程度的需求。xx人在xx时间将xx商品加入到购物车3.确认维度寻找符合业务逻辑的并与此业务过程关联的维度如果前期选择少了几个维度后期可以更新表格再添加即可。4.确认事实每个业务过程的度量值商品件数
2.加购事务事实表 DDL表设计分析
业务数据库对应的表格加购物车cart_info中存在source_id字段此字段对应的是加购物车这个操作对应的数据来源所以需要加来源相关的信息添加到维度表中此处做了维度弱化直接将数据整合到加购事务事实表中了。
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 \tSTORED AS ORCLOCATION /warehouse/gmall/dwd/dwd_trade_cart_add_inc/TBLPROPERTIES (orc.compress snappy);3.加购事务事实表 加载数据分析
1.加购事务事实表来自于业务数据库中哪些表格对应同步到ods层事务事实表使用的是inc结尾的增量数据full结尾的全量数据对应到周期快照事实表使用。 cart_info2.加购物车这一业务过程是怎样实现的有哪些限制条件。 一个用户将一个原来不存在的商品加入到购物车insert操作一个用户将原来购物车有的数据再加一件到购物车update操作并且数据1 3.数据最终落地那个分区下面需要明确 首日全量加购记录 首日默认全部加购物车按照创建时间写入到对应时间分区里面每日增量加购记录 过滤满足条件的数据直接写入对应的当日时间分区。 4.加购事务事实表的数据流向如下图
1.首日全量加购的数据加载
针对业务系统中历史的数据进行处理就是首日装载的意义。sql的思路 1.相关表格已同步到ods层为增量inc表格购物车信息表和加购操作数据来源类型表2.两张表格进行关联获取到加购事务事实表所有字段3.处理数据使用hive动态分区将不同数据写入到不同分区 hive中sql注意date_format(create_time,‘yyyy-MM-dd’)跟mysql中语法不一致。
set hive.exec.dynamic.partition.modenonstrict;
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-14and type bootstrap-insert
)ci
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt2020-06-14and parent_code24
)dic
on ci.source_typedic.dic_code;2.每日增量加购的数据加载 sql思路 一个用户将一个原来不存在的商品加入到购物车insert操作一个用户将原来购物车有的数据再加一件到购物车update操作并且数据变大使用maxwell同步过来的json外部的ts时间作为加入购物车时间而不使用json内部的create_time作为加购时间这样设计比较合理。对加购数量进行判断 如果是insert类型直接使用sku_num的值即可如果是update操作需要将maxwell过来的json数据中 新值-老值得到的结果存入. hive中函数的使用 map_keys(map集合)将此map集合中所有的key取出作为一个数组。array_contains(数组元素) 该数组中是否包含此元素 返回布尔类型的值cast(数据 as int )将该数据强制转化为int类型 hive中时间戳到时间字符串的转换 经常用到,官网https://cwiki.apache.org/confluence/display/Hive/LanguageManualUDF#LanguageManualUDF-DateFunctions 时间戳自零时区以来1970-01-01以来的经历的秒数10位或者毫秒数13位以秒为单位的时间戳转为时分秒 from_unixtime(bigint unixtime[, string format]) 这个转换时间戳函数没有分区概念所以之间转为了零时区的时间。from_utc_timestamp({any primitive type} ts(必选是毫秒数), string timezone) 使用from_utc_timestamp(ts*1000, “GMT8”)使用时间格式化工具将上面处理完的数据转为想要的格式date_format(from_utc_timestamp(ts*1000, “GMT8”),“yyyy-MM-dd HH:mm:ss”) 2020-06-15的增量加购数据处理
insert overwrite table dwd_trade_cart_add_inc partition(dt2020-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,GMT8),yyyy-MM-dd) date_id,date_format(from_utc_timestamp(ts*1000,GMT8),yyyy-MM-dd HH:mm:ss) create_time,data.source_id,data.source_type source_type_code,if(typeinsert,data.sku_num,data.sku_num-old[sku_num]) sku_numfrom ods_cart_info_incwhere dt2020-06-15and (typeinsertor(typeupdate and old[sku_num] is not null and data.sku_numcast(old[sku_num] as int)))
)cart
left join
(selectdic_code,dic_name source_type_namefrom ods_base_dic_fullwhere dt2020-06-15and parent_code24
)dic
on cart.source_type_codedic.dic_code;linux查看进程对应在服务器的配置
1. 首先jps查看进程号
2. cd /proc/84912(某进程对应的进程号)
3. limits 文件里面有对应的限制信息
4. exe 是对应的启动二进制进程
5. fd 文件描述符对应该进程所打开的文件聚合查看一下打开多少文件即可2.交易域下单事务事实表
设计流程大概分为4步选择业务过程 -- 声明粒度 -- 确认维度– 确认事实
1.下单事务事实表 前期梳理
之前梳理的业务矩阵如下对应下单过程如下
1.选择业务过程下单 业务过程2.声明粒度xx订单是在xx时间xx用户在xx地区完成下单操作这对应的是下单事务表每行表示的含义。3.确认维度时间、用户、商品、地区、活动、优惠券等声明维度灵活性较高是由前面梳理的业务数据库中业务过程决定的业务过程关联哪些表格也就是对应的环境信息此处就添加多少维度信息。4.确认事实下单件数、下单原始金额、下单最终金额、活动优惠金额、优惠券优惠金额
2.下单事务事实表 DDL表设计分析
之前创建的dim层维度表以外其他的维度都退化到对应的事实表中没有退化的事实表直接在本表中体现某些维度表的id即可退化的维度直接写入对应数据即可。
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 \tSTORED AS ORCLOCATION /warehouse/gmall/dwd/dwd_trade_order_detail_inc/TBLPROPERTIES (orc.compress snappy);3.下单事务事实表 加载数据分析
下单会对哪些业务表格产生影响如下图
1.首日全量下单的数据加载
业务数据库中 下单明细表中每行数据就能代表一条下单记录直接将数据同步到ods层然后同步到dwd层即可。下单事务事实表 跟 下单明细表字段对比观察哪些字段能获取到哪些字段获取不到获取不到的直接对照数据库表格关联图书写sql获取对应关系如下图没注释掉的就能获取到注释掉的通过sql关联或者处理字段方式获取。订单明细表不能获取到的字段通过关联关系进行子查询配置子查询配置完成后进行sql关联关联完毕后通过hive创建动态分区实现收入不同时间下单数据进入到不同的分区。最终整合完的sql如下
set hive.exec.dynamic.partition.modenonstrict;
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-14and type bootstrap-insert
) od
left join
(selectdata.id,data.user_id,data.province_idfrom ods_order_info_incwhere dt 2020-06-14and 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-14and 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-14and type bootstrap-insert
) cou
on od.id cou.order_detail_id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt2020-06-14and parent_code24
)dic
on od.source_typedic.dic_code;2.每日增量量下单的数据加载
2020-06-15 增量下单明细数据加载-最终sql maxwell同步过来的数据过滤出来insert类型数据即可。
insert overwrite table dwd_trade_order_detail_inc partition (dt2020-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-15and type insert
) od
left join
(selectdata.id,data.user_id,data.province_idfrom ods_order_info_incwhere dt 2020-06-15and 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-15and 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-15and type insert
) cou
on od.id cou.order_detail_id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt2020-06-15and parent_code24
)dic
on od.source_typedic.dic_code;3.交易域取消订单事务事实表
1.取消订单事务事实表 前期梳理
之前梳理的业务矩阵如下对应下单过程如下
1.选择业务过程取消订单 业务过程2.声明粒度xx订单是在xx时间xx用户在xx地区完成取消订单操作这对应的是取消订单事务表每行表示的含义。3.确认维度时间、用户、商品、地区、活动、优惠券等声明维度灵活性较高是由前面梳理的业务数据库中业务过程决定的业务过程关联哪些表格也就是对应的环境信息此处就添加多少维度信息。4.确认事实取消订单件数、取消订单原始金额、取消订单最终金额、活动优惠金额、优惠券优惠金额
2.取消订单事务事实表 DDL表设计分析
取消订单事务事实表中一行代表一次用户取消订单操作。
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 \tSTORED AS ORCLOCATION /warehouse/gmall/dwd/dwd_trade_cancel_detail_inc/TBLPROPERTIES (orc.compress snappy);3.取消订单事务事实表 加载数据分析
数据流程 数据来源相关订单表 中 取消的订单 关联 取消订单表 中 订单详情即可获取全量字段
1.首日全量取消订单的数据加载
set hive.exec.dynamic.partition.modenonstrict;
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-14and 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-14and type bootstrap-insertand data.order_status1003
) 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-14and 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-14and type bootstrap-insert
) cou
on od.id cou.order_detail_id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt2020-06-14and parent_code24
)dic
on od.source_typedic.dic_code;2.每日增量取消订单的数据加载
maxwell同步过来的数据update过来的数据并且order_status的状态变为了取消状态。15号取消的订单可能是之前下单的订单所以获取订单明细数据的时候需要关联订单明细表的前几天的数据需要跟时间维度进行关联获取当天或者前一天的数据。
insert overwrite table dwd_trade_cancel_detail_inc partition (dt2020-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 (dt2020-06-15 or dtdate_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-15and type updateand data.order_status1003and 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 (dt2020-06-15 or dtdate_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 (dt2020-06-15 or dtdate_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 dt2020-06-15and parent_code24
)dic
on od.source_typedic.dic_code;7.交易域购物车周期快照事实表
1.购物车周期快照事实表 前期梳理 周期快照事实表实际上类似于Hive中按天做分区然后全量拉取mysql中数据这样就会形成mysql的快照每日全量快照表。 周期快照事实表解决的主要问题对于商品库存、账户余额这些存量型指标业务系统中通常就会计算并保存最新结果所以定期同步一份全量数据到数据仓库构建周期型快照事实表就能轻松应对此类统计需求而无需再对事务型事实表中大量的历史记录进行聚合了。 周期快照表的创建完全是基于需求来的是服务于需求的此处创建购物车周期快照事实表是服务于需求各分类商品购物车存量Top10 将购物车存量数据创建购物车周期快照事实表直接基于此表按照sku_id分组求和sku_num就可简单实现上面的需求。 周期快照表和业务过程对照关系没有必要进行讨论可能对应一个业务过程也可能对应两个业务过程。
2.购物车周期快照事实表 DDL表设计分析
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 \tSTORED AS ORCLOCATION /warehouse/gmall/dwd/dwd_trade_cart_full/TBLPROPERTIES (orc.compress snappy);3.购物车周期快照事实表 加载数据分析
insert overwrite table dwd_trade_cart_full partition(dt2020-06-14)
selectid,user_id,sku_id,sku_name,sku_num
from ods_cart_info_full
where dt2020-06-14
and is_ordered0;4.交易域支付成功事务事实表
1.支付成功事务事实表 前期梳理
2.支付成功事务事实表 DDL表设计分析
3.支付成功事务事实表 加载数据分析
5.交易域退单事务事实表
1.退单事务事实表 前期梳理
2.退单事务事实表 DDL表设计分析
3.退单事务事实表 加载数据分析
6.交易域退款成功事务事实表
1.退款成功事务事实表 前期梳理
2.退款成功事务事实表 DDL表设计分析
3.退款成功事务事实表 加载数据分析
