背景
发现最近写博客的频率越来越低了,是太忙了?或者是不想写?呵呵!革命尚未成功,同志还需努力。今天利用下午一点空余时间整理如何基于hive上实现数据仓库中的历史拉链算法。
笔者使用hive1.1进行测试。
历史存储方式
下面先来了解一下历史数据存储的几种方法。
数据历史的存储方式
1、切片存储:通过数据日期记录数据的入库时点
2、拉链存储:通过开始日期与结束日期记录每一个主键记录的变化过程
两种存储方式没有好坏之分,都可以完整的保留数据历史,只是各自适合于不同的数据情况。
拉链存储方式–新增:
拉链存储方式–删除:
拉链存储方式–修改:
拉链存储的优点
1、节省存储空间
2、记录数据变化
3、便于获取数据
为什么我们要大规模使用拉链表
1、数据平台的建设原则是保留数据历史
2、遵循实际的数据规律,搭建标准、灵活、可用行强的数据平台
存储方式的选择
1、记录对象:除特殊情况,一般使用拉链存储
2、记录时间:一般默认使用切片存储,实际选择时,根据实际数据情况(是否存在属性变化,是否存在数据物理删除)进行选择
拉链存储的事件表:
拉链算法实现
创建测试表1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47-- 技术缓冲层
DROP TABLE
t_account;
CREATE TABLE
t_account
(
id VARCHAR(30),
name VARCHAR(60),
balance DECIMAL(14,4)
)
row format delimited fields terminated BY '\177';
-- 近源模型层 临时表
DROP TABLE
n_account;
CREATE TABLE
n_account
(
id VARCHAR(30),
name VARCHAR(60),
balance DECIMAL(14,4),
START_DT VARCHAR(8),
END_DT VARCHAR(8)
)
STORED AS parquet TBLPROPERTIES
(
'COMPRESSION_CODEC'='snappy'
);
-- 近源模型层 数据表
DROP TABLE
o_account;
CREATE TABLE
o_account
(
id VARCHAR(30),
name VARCHAR(60),
balance DECIMAL(14,4),
START_DT VARCHAR(8)
)
PARTITIONED BY
(
END_DT VARCHAR(8)
)
STORED AS parquet TBLPROPERTIES
(
'COMPRESSION_CODEC'='snappy'
);
本次使用账户表进行测试,技术缓冲层t_account使用默认的txt格式存储,有便于将格式符合的原始文件能直接加载到hive中。近源模型层使用parquet格式存储,parquet格式号称为计算而生。
拉链步骤
从技术缓冲层向近源模型层加载状态类无删除拉链算法(LEFT JOIN方式)
步骤1,清空临时表:
1
2TRUNCATE TABLE
default.n_account;
步骤2,取出增量数据(新增|修改)到临时表:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23INSERT
INTO
TABLE default.n_account
SELECT DISTINCT
T.id,
T.name,
T.balance,
'${data_date}' AS START_DT,
'30001231' AS END_DT
FROM
default.t_account T
LEFT JOIN
default.o_account O
ON
O.id=COALESCE(RTRIM(T.id),'')
AND O.START_DT
AND O.END_DT>='${data_date}'
WHERE
(
O.id IS NULL)
OR O.id<>COALESCE(RTRIM(T.id),'')
OR O.name<>COALESCE(RTRIM(T.name),'')
OR O.balance<>COALESCE(T.balance,'') ;
步骤3,取当前有效数据到临时表,并对其中部分数据进行闭链:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22INSERT
INTO
TABLE default.n_account
SELECT DISTINCT
O.id ,
O.name ,
O.balance ,
O.START_DT ,
CASE
WHEN N.id IS NOT NULL
THEN N.START_DT
ELSE '30001231'
END AS END_DT
FROM
default.o_account O
LEFT JOIN
default.n_account N
ON
O.id=N.id
WHERE
O.START_DT
AND O.END_DT>='${data_date}';
步骤4,取当前有效数据到临时表,并对其中部分数据进行闭链:
1
2ALTER TABLE
default.o_account DROP PARTITION(END_DT>='${data_date}');
步骤5,将临时表数据全部插入目标表:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16// 开启动态分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
INSERT
OVERWRITE TABLE default.o_account PARTITION
(
END_DT
)
SELECT
id,
name,
balance,
START_DT,
END_DT
FROM
default.n_account;
拉链测试
将数据加载到技术缓冲层(次步骤可以由pig、mapreduce、spark、sqoop等工具来替代):
1
2
3
4
5
6
7
8INSERT
INTO
TABLE default.t_account VALUES
(
'001',
'zhangsan',
2000
);
根据拉链步骤一一执行,并将sql中的${data_date}变量替换成测试日期如(20140101)
模拟下一次数据:清空技术缓冲层表数据(如需备份可以采用分区的形式来保存),并插入测试数据:
1
2
3
4
5
6
7
8
9
10TRUNCATE TABLE
default.t_account ;
INSERT
INTO
TABLE default.t_account VALUES
(
'001',
'zhangsan',
4000
);
根据拉链步骤一一执行,并将sql中的${data_date}变量替换成测试日期如(20140102)
查看测试结果:
此时可以看到,zhangsan余额为2000时的数据已经闭链。
APPEND算法
直接将当日增量数据覆盖到目标表:
1
2
3
4
5
6
7
8
9
10
11
12
13// 开启动态分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE TABLE default.o_account PARTITION(ETL_DT)
SELECT
id,
name,
balance,
END_DT
FROM
default.n_account T
WHERE T.ETL_DT='${data_date}';
至此拉链算法实现测试完成。