昨天说了一下 ONCALL,没看过的可以看昨天的文章。
今天说一下重构。重构看起来挺简单,不过也是很多坑的。今天举个例子。
比如我有一张 Hive 表叫做 dws_live_order_amt_di,就称它为每日直播带货汇总表吧,记录了每个直播的每日下单的用户中,对各个渠道的用户下单金额做统计,比如有的人从主页进入直播间,有的人刷到直播进入直播间,有的人刷到短视频从头像进入直播间。假设表结构如下:
字段 | 含义 |
---|---|
live_id | 直播 id |
entrance1_amt | 渠道1下单总金额 |
entrance2_amt | 渠道2下单总金额 |
entrance3_amt | 渠道3下单总金额 |
有一张 Hive 表叫做 dwd_live_user_entrance_order_di,记录了这一天每个人在直播间的下单:
字段 | 含义 |
---|---|
live_id | 直播 id |
user_id | 用户 id |
entrance | 入口,1,2,3 |
pay_amt | 支付金额 |
那么从 dwd → dws,以前我都生产代码是:
insert overwrite dws_live_order_amt_di partition(date='${date}')
select
live_id,
sum(if(entrance=1,pay_amt,0)) as entrance1_amt,
sum(if(entrance=2,pay_amt,0)) as entrance2_amt,
sum(if(entrance=3,pay_amt,0)) as entrance3_amt
from
dwd_live_user_entrance_order_di
where
date='${date}'
group by live_id;
直到有一天,dwd_live_user_entrance_order_di 这张表要停用了。变成了两张表,一张是 dwd_live_user_order_di,记录的字段如下:
字段 | 含义 |
---|---|
live_id | 直播 id |
user_id | 用户 id |
pay_amt | 支付金额 |
… | (其他字段) |
一张表是 dwd_live_user_entrance_di,记录的字段如下:
字段 | 含义 |
---|---|
live_id | 直播 id |
user_id | 用户 id |
entrance | 入口,1,2,3 |
… | (其他字段) |
那么,我就要重构 dws_live_order_amt_di 的生产代码,这里先写入测试表格:
insert overwrite test_dws_live_order_amt_di partition(date='${date}')
select
a.live_id,
sum(if(entrance=1,pay_amt,0)) as entrance1_amt,
sum(if(entrance=2,pay_amt,0)) as entrance2_amt,
sum(if(entrance=3,pay_amt,0)) as entrance3_amt
from
(
select
live_id,
user_id,
pay_amt
from dwd_live_user_order_di
where date = '${date}'
) a
join
(
select
live_id,
user_id,
entrance
from dwd_live_user_entrance_di
where date = '${date}'
) b
on a.live_id=b.live_id and a.user_id=b.user_id
group by live_id;
做到这里并不是结束。烦人的步骤才刚刚开始,因为你要保证改过之后,两个表完全一致。所以还得验证 test_dws_live_order_amt_di 和 dws_live_order_amt_di 是否完全一样:
1.先验证行数是否相等
select count(*) as x
from dws_live_order_amt_di
where date = '${date}'
UNION ALL
select count(*) as y
from test_dws_live_order_amt_di
where date = '${date}'
不相等时需要找到不相等的 live_id:
select
*
from(
select
live_id
from dws_live_order_amt_di
where date = '${date}'
) a
left join
(
select
live_id
from test_dws_live_order_amt_di
where date = '${date}'
) b
on a.live_id = b.live_id
where b.live_id is null;
拿结果中的 live_id 和 user_id 到上有表查原因。这里又很复杂了,可能上有表行数很多,逻辑也很复杂。
2.验证指标是否相等
行数相等之后验证具体指标:
select
*
from(
select
live_id,
entrance1_amt,
entrance2_amt,
entrance2_amt,
from dws_live_order_amt_di
where date = '${date}'
) a
left join
(
select
live_id,
entrance1_amt,
entrance2_amt,
entrance2_amt,
from test_dws_live_order_amt_di
where date = '${date}'
) b
on a.live_id = b.live_id
where a.entrance1_amt != b.entrance1_amt
or a.entrance2_amt != b.entrance2_amt
or a.entrance3_amt != b.entrance3_amt;
不相等就拿到结果的 live_id 到上游查原因。
附
额外讲两点。
1.为什么要重构
统一指标,让表格更合理,更方便管理。另外缩短运行时间。比如上面的例子:
- ods → 90 分钟 → dwd_live_user_entrance_order_di
- dwd_live_user_entrance_order_di → 30 分钟 → dws_live_order_amt_di
- 最终耗时:90 + 30 = 120 分钟
改用新表之后:
- ods → 30 分钟 → dwd_live_user_order_di
- ods → 40 分钟 → dwd_live_user_entrance_di
- 2 个 新 dwd 表 → 50 分钟 → test_dws_live_order_amt_di
- 最终耗时 max(30, 40) + 50 = 90 分钟
2.先子查询再 join
对于上面的 sql:
select
*
from(
select
live_id
from dws_live_order_amt_di
where date = '${date}'
) a
left join
(
select
live_id
from test_dws_live_order_amt_di
where date = '${date}'
) b
on a.live_id = b.live_id
where b.live_id is null;
在 MySQL 中我们一般这样写:
select
a.live_id,
b.live_id
from dws_live_order_amt_di a
left join test_dws_live_order_amt_di
on a.live_id = b.live_id
where a.date = '${date}'
and b.date = '${date}'
and b.live_id is null;
但是 hive 中不要,这样 join 数据量太大。因为大数据采用列式存储,先裁剪,再 join 数据量比较小。
欢迎关注。