|NO.Z.00053|——————————|^^ 实验 ^^|——|Hadoop&PB级数仓.V11|——PB数仓.v11|核心交易分析|DIM层建表|加载数据|
一、DIM层建表加载数据：DIM层建表加载数据概述
### --- 首先要确定哪些是事实表、哪些是维表。绿色的是事实表，灰色的维表
~~~ 用什么方式处理维表，每日快照、拉链表？
~~~ 小表使用每日快照：产品分类表、商家店铺表、商家地域组织表、支付方式表
~~~ 大表使用拉链表：产品信息表二、DIM层建表：商品分类表
### --- DIM层建表：商品分类表
~~~ 数据库中的数据是规范的（满足三范式），但是规范化的数据给查询带来不便。
~~~ 备注：这里对商品分类维度表做了逆规范化
~~~ 省略了无关信息，做成了宽表### --- 商品分类表
DROP TABLE IF EXISTS dim.dim_trade_product_cat;
create table if not exists dim.dim_trade_product_cat(
firstId int, -- 一级商品分类id
firstName string, -- 一级商品分类名称
secondId int, -- 二级商品分类Id
secondName string, -- 二级商品分类名称
thirdId int, -- 三级商品分类id
thirdName string -- 三级商品分类名称
)
partitioned by (dt string)
STORED AS PARQUET;### --- 实现：
hive (default)> select T1.catid, T1.catname, T2.catid, T2.catname, T3.catid, T3.catname
from (select catid, catname, parentid
from ods.ods_trade_product_category
where level=3 and dt='2020-07-01') T3 left join
(select catid, catname, parentid from ods.ods_trade_product_category
where level=2 and dt='2020-07-01') T2 on T3.parentid=T2.catid left join
(select catid, catname, parentid from ods.ods_trade_product_category
where level=1 and dt='2020-07-01')
T1 on T2.parentid=T1.catid;
~~~输出参数
t1.catid t1.catname t2.catid t2.catname t3.catid t3.catname
47 时蔬水果、网上菜场 61 进口水果 72 橙柚
47 时蔬水果、网上菜场 61 进口水果 73 苹果
48 厨卫清洁、纸制用品 74 纸制品 75 软包抽纸
47 时蔬水果、网上菜场 61 进口水果 76 凤梨
47 时蔬水果、网上菜场 61 进口水果 77 火龙果
56 虚拟服务、优惠票券 87 教育培训 88 早教幼教### --- 数据加载：
[root@hadoop02 ~]# vim /data/yanqidw/script/trade/dim_load_product_cat.sh#！/bin/bash
source /etc/profile
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
insert overwrite table dim.dim_trade_product_cat partition(dt='$do_date')
select
t1.catid, -- 一级分类id
t1.catname, -- 一级分类名称
t2.catid, -- 二级分类id
t2.catname, -- 二级分类名称
t3.catid, -- 三级分类id
t3.catname -- 三级分类名称
from
-- 商品三级分类数据
(select catid, catname, parentid from ods.ods_trade_product_category
where level=3 and dt='$do_date') t3 left join
-- 商品二级分类数据
(select catid, catname, parentid from ods.ods_trade_product_category
where level=2 and dt='$do_date') t2 on t3.parentid = t2.catid left join
-- 商品一级分类数据
(select catid, catname, parentid from ods.ods_trade_product_category
where level=1 and dt='$do_date') t1 on t2.parentid = t1.catid;
"
hive -e "$sql"~~~ # 加载数据到DIM商品分类表
[root@hadoop02 ~]# sh /data/yanqidw/script/trade/dim_load_product_cat.sh 2020-07-01~~~ # 查看DIM商品分类表是否加载到数据
hive (default)> show partitions dim.dim_trade_product_cat;
partition
dt=2020-07-01
hive (default)> select count(*) from dim.dim_trade_product_cat where dt='2020-07-01' limit 5;
415三、DIM层建表：商品地域组织表
~~~ DIM层建表：商品地域组织表
~~~ 商家店铺表、商家地域组织表 => 一张维表
~~~ 这里也是逆规范化的设计，将商家店铺表、商家地域组织表组织成一张表，并拉宽。
~~~ 在一行数据中体现：商家信息、城市信息、地域信息。信息中包括 id 和 name ；### --- 商品地域组织表
drop table if exists dim.dim_trade_shops_org;
create table dim.dim_trade_shops_org(
shopid int,
shopName string,
cityId int,
cityName string ,
regionId int ,
regionName string
)
partitioned by (dt string)
STORED AS PARQUET;### --- 实现
hive (default)> select T1.shopid, T1.shopname, T2.id cityid, T2.orgname cityname, T3.id regionid, T3.orgname regionname
from
(select shopid, shopname, areaid
from ods.ods_trade_shops
where dt='2020-07-01') T1
left join
(select id, parentid, orgname, orglevel
from ods.ods_trade_shop_admin_org
where orglevel=2 and dt='2020-07-01') T2
on T1.areaid=T2.id
left join
(select id, orgname, orglevel
from ods.ods_trade_shop_admin_org
where orglevel=1 and dt='2020-07-01') T3
on T2.parentid=T3.id
limit 10;
~~~输出参数
t1.shopid t1.shopname cityid cityname regionid regionname
100050 WSxxx营超市 100225 景德镇市分公司 100006 华北大区
100052 新鲜xxx旗舰店 100236 青岛市分公司 100006 华北大区
100053 华为xxx旗舰店 100011 石家庄市分公司 100006 华北大区
100054 小米xxx旗舰店 100159 石嘴山市分公司 100007 华西大区
100055 苹果xxx旗舰店 100211 六安市分公司 100006 华北大区
100056 OPxxx自营店 100050 阜新市分公司 100006 华北大区
100057 三只xxx鼠零食 100311 东莞市分公司 100008 华南大区
100058 良子xxx铺美食 100329 崇左市分公司 100008 华南大区
100059 乐居xxx日用品 100225 景德镇市分公司 100006 华北大区
100060 同仁xxx大健康 100211 六安市分公司 100006 华北大区### --- 数据加载
[root@hadoop02 ~]# vim /data/yanqidw/script/trade/dim_load_shop_org.sh#！/bin/bash
source /etc/profile
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
insert overwrite table dim.dim_trade_shops_org partition(dt='$do_date')
select t1.shopid,
t1.shopname,
t2.id as cityid,
t2.orgname as cityName,
t3.id as region_id,
t3.orgname as region_name from
(select shopId, shopName, areaId from ods.ods_trade_shops where dt='$do_date')
t1 left join
(select id, parentId, orgname, orglevel from ods.ods_trade_shop_admin_org where orglevel=2 and dt='$do_date')
t2 on t1.areaid = t2.id left join
(select id, parentId, orgname, orglevel from ods.ods_trade_shop_admin_org where orglevel=1 and dt='$do_date')
t3 on t2.parentid = t3.id;
"
hive -e "$sql"~~~ # 加载数据到DIM商品低于组织表
[root@hadoop02 ~]# sh /data/yanqidw/script/trade/dim_load_shop_org.sh 2020-07-01~~~ # 查看DIM商品分类表是否加载到数据
hive (default)> show partitions dim.dim_trade_shops_org;
partition
dt=2020-07-01
hive (default)> select * from dim.dim_trade_shops_org where dt='2020-07-01' limit 5;
dim_trade_shops_org.shopid dim_trade_shops_org.shopname dim_trade_shops_org.cityid dim_trade_shops_org.cityname dim_trade_shops_org.regionid dim_trade_shops_org.regionnamedim_trade_shops_org.dt
100050 WSxxx营超市 100225 景德镇市分公司 100006 华北大区 2020-07-01
100052 新鲜xxx旗舰店 100236 青岛市分公司 100006 华北大区 2020-07-01
100053 华为xxx旗舰店 100011 石家庄市分公司 100006 华北大区 2020-07-01
100054 小米xxx旗舰店 100159 石嘴山市分公司 100007 华西大区 2020-07-01
100055 苹果xxx旗舰店 100211 六安市分公司 100006 华北大区 2020-07-01
hive (default)> select count(*) from dim.dim_trade_shops_org where dt='2020-07-01' limit 5;
5266四、DIM层建表：支付方式表
~~~ DIM层建表：支付方式表
~~~ 对ODS中表的信息做了裁剪，只保留了必要的信息。### --- 支付方式表
drop table if exists dim.dim_trade_payment;
create table if not exists dim.dim_trade_payment(
paymentId string, -- 支付方式id
paymentName string -- 支付方式名称
)
partitioned by (dt string)
STORED AS PARQUET;### --- 数据加载
[root@hadoop02 ~]# vim /data/yanqidw/script/trade/dim_load_payment.sh#！/bin/bash
source /etc/profile
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
insert overwrite table dim.dim_trade_payment partition(dt='$do_date')
select id, payName from ods.ods_trade_payments
where dt='$do_date';
"
hive -e "$sql"~~~ # 加载数据到DIM支付方式表
[root@hadoop02 ~]# sh /data/yanqidw/script/trade/dim_load_payment.sh 2020-07-01~~~ # 查看DIM商品分类表是否加载到数据
hive (default)> show partitions dim.dim_trade_payment;
partition
dt=2020-07-01
hive (default)> select * from dim.dim_trade_payment where dt='2020-07-01' limit 10;
dim_trade_payment.paymentid dim_trade_payment.paymentname dim_trade_payment.dt
1 支付宝 2020-07-01
2 微信支付 2020-07-01
3 余额支付 2020-07-01
4 货到付款 2020-07-01
5 云闪付 2020-07-01
6 美团支付 2020-07-01
hive (default)> select count(*) from dim.dim_trade_payment where dt='2020-07-01' limit 5;
6五、DIM层建表：商品信息表
### --- 使用拉链表对商品信息进行处理。
~~~ 历史数据 => 初始化拉链表(开始日期：当日；结束日期：9999-12-31)【只执行一次】
~~~ 拉链表的每日处理【每次加载数据时处理】
~~~ 新增数据。每日新增数据(ODS) => 开始日期：当日；结束日期：9999-12-31
~~~ 历史数据。拉链表(DIM) 与 每日新增数据(ODS) 做左连接
~~~ 连接上数据。数据有变化，结束日期：当日；
~~~ 未连接上数据。数据无变化，结束日期保持不变；### --- 创建维表
~~~ 拉链表要增加两列，分别记录生效日期和失效日期
drop table if exists dim.dim_trade_product_info;
create table dim.dim_trade_product_info(
`productId` bigint,
`productName` string,
`shopId` string,
`price` decimal,
`isSale` tinyint,
`status` tinyint,
`categoryId` string,
`createTime` string,
`modifyTime` string,
`start_dt` string,
`end_dt` string
) COMMENT '产品表'
STORED AS PARQUET;### --- 初始数据加载（历史数据加载，只做一次）
hive (default)> insert overwrite table dim.dim_trade_product_info
select productId,
productName,
shopId,
price,
isSale,
status,
categoryId,
createTime,
modifyTime,
-- modifyTime非空取modifyTime，否则取createTime；substr取日期
case when modifyTime is not null
then substr(modifyTime, 0, 10)
else substr(createTime, 0, 10)
end as start_dt,
'9999-12-31' as end_dt
from ods.ods_trade_product_info
where dt = '2020-07-12';~~~ # 查看导入的数据总量
hive (default)> select count(*) from dim.dim_trade_product_info;
15807### --- 增量数据导入（重复执行，每次加载数据执行）
[root@hadoop02 ~]# vim /data/yanqidw/script/trade/dim_load_product_info.sh#！/bin/bash
source /etc/profile
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
insert overwrite table dim.dim_trade_product_info select
productId,
productName,
shopId,
price,
isSale,
status,
categoryId,
createTime,
modifyTime,
case when modifyTime is not null then substr(modifyTime,0,10) else substr(createTime,0,10)
end as start_dt,
'9999-12-31' as end_dt from ods.ods_trade_product_info where dt='$do_date' union all select
dim.productId,
dim.productName,
dim.shopId,
dim.price,
dim.isSale,
dim.status,
dim.categoryId,
dim.createTime,
dim.modifyTime,
dim.start_dt,
case when dim.end_dt >= '9999-12-31' and ods.productId is not null
then '$do_date'
else dim.end_dt
end as end_dt
from dim.dim_trade_product_info dim left join
(select *
from ods.ods_trade_product_info
where dt='$do_date' ) ods
on dim.productId = ods.productId
"
hive -e "$sql"~~~ # 加载数据到DIM增量数据导入
[root@hadoop02 ~]# sh /data/yanqidw/script/trade/dim_load_product_info.sh 2020-07-01~~~ # 查看DIM商品分类表是否加载到数据
~~~ 查看导入的历史数据总量
hive (default)> select count(*) from dim.dim_trade_product_info;
15807
hive (default)> select * from dim.dim_trade_product_info limit 5;
dim_trade_product_info.productid dim_trade_product_info.productname dim_trade_product_info.shopid dim_trade_product_info.price dim_trade_product_info.issale dim_trade_product_info.status dim_trade_product_info.categoryid dim_trade_product_info.createtime dim_trade_product_info.modifytime dim_trade_product_info.start_dt dim_trade_product_info.end_dt
100101 四川xxx/个 100056 37 1 0 72 2020-07-12 13:22:22 2020-07-12 13:22:22 2020-07-12 9999-12-31
100102 单果xxx单果 100061 59 1 1 72 2020-07-12 13:22:22 2020-07-12 13:22:22 2020-07-12 9999-12-31
100103 红颜xxx水果 100055 49 1 0 251 2020-07-12 13:22:22 2020-07-12 13:22:22 2020-07-12 9999-12-31
100104 智利xxx水果 100050 78 1 0 72 2020-07-12 13:22:22 2020-07-12 13:22:22 2020-07-12 9999-12-31
100105 Zexxx水果 100062 59 1 1 251 2020-07-12 13:22:22 2020-07-12 13:22:22 2020-07-12 9999-12-31Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart
——W.S.Landor
分类:
bdv014-PB离线数仓
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通