一、常用表介绍
1. 全量表
每天的所有的最新状态的数据
2. 增量表
每天的新增数据
3. 拉链表
维护历史状态,以及最新状态数据
4. 流水表
对于表中的每一个修改都会记录,可以用于反映实际记录的变更
5. 拉链表 VS 流水表
拉链表:通常是对帐户信息的历史变动进行处理保留的结果;用于统计业务相关情况 流水表:每天的交易形成的历史;用于统计账户及客户的情况
二. 拉链表适用情况及优点
1. 适用情况
(1)数据量比较大 (2)表中的部分字段会被更新 (3)需要查看某一个时间点或者时间段的历史快照信息
查看某一个订单在历史某一个时间点的状态 某一个用户在过去某一段时间,下单次数
(4)更新的比例和频率不是很大 如果表中信息变化不是很大,每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费
2. 优点
三. 拉链表举例(订单域)
1. 订单表结构
order_id createtime modifiedtime status 001 2012-06-20 2012-06-20 创建 002 2012-06-20 2012-06-20 创建 003 2012-06-20 2012-06-20 支付完成
order_id createtime modifiedtime status 001 2012-06-20 2012-06-21 支付完成 002 2012-06-20 2012-06-20 创建 003 2012-06-20 2012-06-20 支付完成 004 2012-06-21 2012-06-21 创建 005 2012-06-21 2012-06-21 创建
order_id createtime modifiedtime status 001 2012-06-20 2012-06-21 支付完成 002 2012-06-20 2012-06-20 创建 003 2012-06-20 2012-06-22 已发货 004 2012-06-21 2012-06-21 创建 005 2012-06-21 2012-06-22 支付完成 006 2012-06-22 2012-06-22 创建
2. 为实现订单表存储,常用解决方案及存在问题
(1)快照表
只保留一份全量,此时快照表与6.22的记录一样 若要查看6.21订单001的状态,则无法满足
order_id createtime modifiedtime status 001 2012-06-20 2012-06-21 支付完成 002 2012-06-20 2012-06-20 创建 003 2012-06-20 2012-06-22 已发货 004 2012-06-21 2012-06-21 创建 005 2012-06-21 2012-06-22 支付完成 006 2012-06-22 2012-06-22 创建
(2)全量历史表
每天都保留一份全量,则数据库中的该表共有14条记录 存在重复保存的记录(订单002 004) 随着数据量的递增,会存在很大的存储浪费
key order_id createtime modifiedtime status 1 001 2012-06-20 2012-06-20 创建 2 001 2012-06-20 2012-06-21 支付完成 3 001 2012-06-20 2012-06-21 支付完成 4 002 2012-06-20 2012-06-20 创建 5 002 2012-06-20 2012-06-20 创建 6 002 2012-06-20 2012-06-20 创建 7 003 2012-06-20 2012-06-20 支付完成 8 003 2012-06-20 2012-06-20 支付完成 9 003 2012-06-20 2012-06-22 已发货 10 004 2012-06-21 2012-06-21 创建 11 004 2012-06-21 2012-06-21 创建 12 005 2012-06-21 2012-06-21 创建 13 005 2012-06-21 2012-06-22 支付完成 14 006 2012-06-22 2012-06-22 创建
(3)历史拉链表
key order_id createtime modifiedtime status start_time end_time 1 001 2012-06-20 2012-06-20 创建 2012-06-20 2012-06-20 2 001 2012-06-20 2012-06-21 支付完成 2012-06-21 9999-12-31 3 002 2012-06-20 2012-06-20 创建 2012-06-20 9999-12-31 4 003 2012-06-20 2012-06-20 支付完成 2012-06-20 2012-06-21 9 003 2012-06-20 2012-06-22 已发货 2012-06-22 9999-12-31 10 004 2012-06-21 2012-06-21 创建 2012-06-21 9999-12-31 11 005 2012-06-21 2012-06-21 创建 2012-06-21 2012-06-21 12 005 2012-06-22 2012-06-22 支付完成 2012-06-22 9999-12-31 13 006 2012-06-22 2012-06-22 支付完成 2012-06-20 9999-12-31
select * from order_his where end_time = '9999-12-31';
select * from order_his where start_time <= '2012-06-21' and end_time >= '2012-06-21';
order_id createtime modifiedtime status start_time end_time 001 2012-06-20 2012-06-21 支付完成 2012-06-21 9999-12-31 002 2012-06-20 2012-06-20 创建 2012-06-20 9999-12-31 003 2012-06-20 2012-06-21 支付完成 2012-06-20 2012-06-21 004 2012-06-21 2012-06-21 创建 2012-06-21 9999-12-31 005 2012-06-21 2012-06-21 创建 2012-06-21 9999-12-31
3. 拉链表更新方案
(1)假设
数仓中订单历史表刷新频率为一天,当天更新前一天的增量数据 若一个订单在一天内存在多个变化,只会记录最后一个状态的历史 订单状态:创建、支付、完成 创建时间、修改时间只取到天 若原系统无修改时间,需要有机制确保抽取到每天的增量数据,如binlog解析,或者sqoop同步有过修改的数据
(2)表结构及hivesql语句
CREATE TABLE orders (
orderid INT ,
createtime STRING,
modifiedtime STRING,
status STRING
) row format delimited fields terminated by '\t'
订单增量表【数仓ODS层,按天分区,存放每天的增量数据】
CREATE TABLE ods_orders_inc (
orderid INT ,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY ( day STRING)
row format delimited fields terminated by '\t'
历史数据拉链表【数仓DW层,存放订单的历史状态数据】
CREATE TABLE dw_orders_his (
orderid INT ,
createtime STRING,
modifiedtime STRING,
status STRING,
dw_start_date STRING,
dw_end_date STRING
) row format delimited fields terminated by '\t' ;
2015-08-21至2015-08-23,每天订单系统的数据如下,黄色表示当天发生变化的订单,即增量数据
order_id createtime modifiedtime status 1 2015-08-18 2015-08-18 创建 2 2015-08-18 2015-08-18 创建 3 2015-08-19 2015-08-21 支付 4 2015-08-19 2015-08-21 完成 5 2015-08-19 2015-08-20 支付 6 2015-08-20 2015-08-20 创建 7 2015-08-20 2015-08-21 支付 8 2015-08-21 2015-08-21 创建
order_id createtime modifiedtime status 1 2015-08-18 2015-08-22 支付 2 2015-08-18 2015-08-22 完成 3 2015-08-19 2015-08-21 支付 4 2015-08-19 2015-08-21 完成 5 2015-08-19 2015-08-20 支付 6 2015-08-20 2015-08-22 支付 7 2015-08-20 2015-08-21 支付 8 2015-08-21 2015-08-22 支付 9 2015-08-22 2015-08-22 创建 10 2015-08-22 2015-08-22 支付
order_id createtime modifiedtime status 1 2015-08-18 2015-08-23 完成 2 2015-08-18 2015-08-22 完成 3 2015-08-19 2015-08-23 完成 4 2015-08-19 2015-08-21 完成 5 2015-08-19 2015-08-23 完成 6 2015-08-20 2015-08-22 支付 7 2015-08-20 2015-08-21 支付 8 2015-08-21 2015-08-23 完成 9 2015-08-22 2015-08-22 创建 10 2015-08-22 2015-08-22 支付 11 2015-08-23 2015-08-23 创建 12 2015-08-23 2015-08-23 创建 13 2015-08-23 2015-08-23 支付
(3)更新步骤
1)全量初始化
在数据从源业务系统每天正常抽取和刷新到DW订单历史表之前,需要做一次全量的初始化,就是从源订单表中昨天以前的数据全部抽取到ODS,并刷新到DW。 以上面的数据为例,比如在2015-08-21这天做全量初始化,那么我需要将包括2015-08-20之前的所有的数据都抽取并刷新到DW
第一步:抽取全量数据到ODS
INSERT overwrite TABLE t_ods_orders_inc PARTITION ( day = ‘2015 - 08 - 20 ′)
SELECT orderid, createtime, modifiedtime, status
FROM orders
WHERE createtime <= ‘2015 - 08 - 20 ′;
order_id createtime modifiedtime status 1 2015-08-18 2015-08-18 创建 2 2015-08-18 2015-08-18 创建 3 2015-08-19 2015-08-21 支付 4 2015-08-19 2015-08-21 完成 5 2015-08-19 2015-08-20 支付 6 2015-08-20 2015-08-20 创建 7 2015-08-20 2015-08-21 支付
第二步:从ODS刷新到DW
INSERT overwrite TABLE t_dw_orders_his
SELECT orderid, createtime, modifiedtime, status ,
createtime AS dw_start_date,
‘9999 - 12 - 31 ′ AS dw_end_date
FROM t_ods_orders_inc
WHERE day = ‘2015 - 08 - 20 ′;
order_id createtime modifiedtime status dw_start_date dw_end_date 1 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 2 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 3 2015-08-19 2015-08-21 支付 2015-08-19 9999-12-31 4 2015-08-19 2015-08-21 完成 2015-08-19 9999-12-31 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31 6 2015-08-20 2015-08-20 创建 2015-08-20 9999-12-31 7 2015-08-20 2015-08-21 支付 2015-08-20 9999-12-31
2) 增量抽取
每天,从源系统订单表中,将前一天的增量数据抽取到ODS层的增量数据表 这里的增量需要通过订单表中的创建时间和修改时间来确定 注意:在ODS层按天分区的增量表,最好保留一段时间的数据,比如半年,为了防止某一天的数据有问题而回滚重做数据
INSERT overwrite TABLE t_ods_orders_inc PARTITION ( day = '${day}' )
SELECT orderid, createtime, modifiedtime, status
FROM orders
WHERE createtime = '${day}' OR modifiedtime = '${day}' ;
3) 增量刷新历史数据
从2015-08-22开始,需要每天正常刷新前一天(2015-08-21)的增量数据到历史表
第一步:通过增量抽取,将2015-08-21的数据抽取到ODS - 通过增量抽取,将2015-08-21的数据抽取到ODS:
INSERT overwrite TABLE t_ods_orders_inc PARTITION ( day = '2015-08-21' )
SELECT orderid, createtime, modifiedtime, status
FROM orders
WHERE createtime = '2012-08-21' OR modifiedtime = '2012-08-21' ;
order_id createtime modifiedtime status 3 2015-08-19 2015-08-21 支付 4 2015-08-19 2015-08-21 完成 7 2015-08-20 2015-08-21 支付 8 2015-08-21 2015-08-22 支付
第二步:通过DW历史数据(数据日期为2015-08-20),和ODS增量数据(2015-08-21),刷新历史表
先把数据放到一张临时表中,UNION ALL的两个结果集中,第一个是用历史表left outer join 日期为 ${yyy-MM-dd} 的增量,能关联上的,并且dw_end_date >
y
y
y
−
M
M
−
d
d
,
说
明
状
态
有
变
化
,
则
把
原
来
的
d
w
_
e
n
d
_
d
a
t
e
置
为
(
{yyy-MM-dd},说明状态有变化,则把原来的 dw\_ end\_ date 置为(
y y y − M M − d d , 说 明 状 态 有 变 化 , 则 把 原 来 的 d w _ e n d _ d a t e 置 为 ( {yyy-MM-dd} – 1);关联不上的,说明状态无变化,dw_end_date无变化。 第二个结果集是直接将增量数据插入历史表。
DROP TABLE IF EXISTS t_dw_orders_his_tmp;
CREATE TABLE t_dw_orders_his_tmp
AS
SELECT
orderid,
createtime,
modifiedtime,
status ,
dw_start_date,
dw_end_date
FROM
( SELECT
a. orderid,
a. createtime,
a. modifiedtime,
a. status ,
a. dw_start_date,
CASE WHEN b. orderid IS NOT NULL AND a. dw_end_date > '2012-06-21' THEN '2012-06-20' ELSE a. dw_end_date END AS dw_end_date
FROM t_dw_orders_his a
left outer join ( SELECT * FROM t_ods_orders_inc WHERE day = '2012-06-21' ) b
ON ( a. orderid = b. orderid)
UNION ALL
SELECT
orderid,
createtime,
modifiedtime,
status ,
modifiedtime AS dw_start_date,
'9999-12-31' AS dw_end_date
FROM t_ods_orders_inc
WHERE day = '2012-06-21'
) x
ORDER BY orderid, dw_start_date;
INSERT overwrite TABLE t_dw_orders_his
SELECT * FROM t_dw_orders_his_tmp;
order_id createtime modifiedtime status dw_start_date dw_end_date 1 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 2 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20 3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31 6 2015-08-20 2015-08-20 创建 2015-08-20 9999-12-31 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31 8 2015-08-21 2015-08-21 创建 2015-08-21 9999-12-31
由于在2015-08-21做了8月20日以前的数据全量初始化,而订单3、4、7在2015-08-21的增量数据中也存在,因此都有两条记录,但不影响后面的查询。
第三步:通过增量抽取,将2015-08-22的数据抽取到ODS - 通过增量抽取,将2015-08-22的数据抽取到ODS:
INSERT overwrite TABLE t_ods_orders_inc PARTITION ( day = '2015-08-21' )
SELECT orderid, createtime, modifiedtime, status
FROM orders
WHERE createtime = '2012-08-21' OR modifiedtime = '2012-08-22' ;
order_id createtime modifiedtime status 1 2015-08-18 2015-08-22 支付 2 2015-08-18 2015-08-22 完成 6 2015-08-20 2015-08-22 支付 8 2015-08-21 2015-08-22 支付 9 2015-08-22 2015-08-22 创建 10 2015-08-22 2015-08-22 支付
INSERT overwrite TABLE t_ods_orders_inc PARTITION ( day = '2015-08-22' )
SELECT orderid, createtime, modifiedtime, status
FROM orders
WHERE createtime = '2015-08-22' OR modifiedtime = '2015-08-22' ;
DROP TABLE IF EXISTS t_dw_orders_his_tmp;
CREATE TABLE t_dw_orders_his_tmp AS
SELECT orderid,
createtime,
modifiedtime,
status ,
dw_start_date,
dw_end_date
FROM (
SELECT a. orderid,
a. createtime,
a. modifiedtime,
a. status ,
a. dw_start_date,
CASE WHEN b. orderid IS NOT NULL AND a. dw_end_date > '2015-08-22' THEN '2015-08-21' ELSE a. dw_end_date END AS dw_end_date
FROM t_dw_orders_his a
left outer join ( SELECT * FROM t_ods_orders_inc WHERE day = '2015-08-22' ) b
ON ( a. orderid = b. orderid)
UNION ALL
SELECT orderid,
createtime,
modifiedtime,
status ,
modifiedtime AS dw_start_date,
'9999-12-31' AS dw_end_date
FROM t_ods_orders_inc
WHERE day = '2015-08-22'
) x
ORDER BY orderid, dw_start_date;
INSERT overwrite TABLE t_dw_orders_his
SELECT * FROM t_dw_orders_his_tmp;
order_id createtime modifiedtime status dw_start_date dw_end_date 1 2015-08-18 2015-08-18 创建 2015-08-18 2015-08-21 1 2015-08-18 2015-08-18 支付 2015-08-22 9999-12-31 2 2015-08-18 2015-08-18 创建 2015-08-18 2015-08-21 2 2015-08-18 2015-08-18 完成 2015-08-22 9999-12-31 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20 3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31 6 2015-08-20 2015-08-20 创建 2015-08-20 2015-08-21 6 2015-08-20 2015-08-20 支付 2015-08-22 9999-12-31 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31 8 2015-08-21 2015-08-21 创建 2015-08-21 2015-08-21 8 2015-08-21 2015-08-21 支付 2015-08-22 9999-12-31 9 2015-08-22 2015-08-22 创建 2015-08-22 9999-12-31 10 2015-08-22 2015-08-22 支付 2015-08-22 9999-12-31
参考博文1 参考博文2 [参考博文3]