大数据（3f）HIVE拉链表_hive中拉链怎么实现更新,修改,删除

什么是拉链表

• 数仓设计中的一种表，记录数据的历史状态和最新状态
  zip table亦可译作压缩表
• 优点：和全量表相比，可大大节省存储空间
• 应用场景：数据量大但变化量不大，且有查看历史快照的需求

拉链表制作

从拉链表取出快照

HIVE实现拉链表

1、建表

1、建库

DROP DATABASE IF EXISTS b0 CASCADE;
CREATE DATABASE b0;
USE b0;
1
2
3

2、新增和变化表（按天分区）

DROP TABLE IF EXISTS ods_user;
CREATE TABLE ods_user(i STRING,balance INT) PARTITIONED BY (ymd DATE);
1
2

3、拉链表

DROP TABLE IF EXISTS dwd_user;
CREATE TABLE dwd_user(i STRING,balance INT,start_date DATE,end_date DATE);
1
2

2、插入数据到 每天新增和变化表

此处为一次性插入，实际场景是每天插入

INSERT INTO ods_user (i,balance,ymd) VALUES
('a1',100,'2020-01-01'),
('a2',0,'2020-01-01'),
('a1',300,'2020-01-02'),
('a3',200,'2020-01-02'),
('a1',400,'2020-01-03'),
('a3',500,'2020-01-03');
SELECT * FROM ods_user;
1
2
3
4
5
6
7
8

3、更新拉链表

3.1、首次导入

SET ymd='2020-01-01';

INSERT OVERWRITE TABLE dwd_user
SELECT
    i,
    balance,
    ${hiveconf:ymd} start_date,
    '9999-12-31' end_date
FROM ods_user WHERE ymd=${hiveconf:ymd};
1
2
3
4
5
6
7
8
9

SELECT * FROM dwd_user;
1

查看数据

3.2、第一次更新

SET ymd=TO_DATE('2020-01-02');

INSERT OVERWRITE TABLE dwd_user
SELECT * FROM
(
    SELECT
        i,
        balance,
        ${hiveconf:ymd} start_date,
        TO_DATE('9999-12-31') end_date
    FROM ods_user WHERE ymd=${hiveconf:ymd}

    UNION ALL

    SELECT
        a.i,
        a.balance ,
        a.start_date,
        IF(b.i IS NULL,a.end_date,b.ymd)end_date
    FROM dwd_user a LEFT JOIN(
        SELECT * FROM ods_user WHERE ymd=${hiveconf:ymd}
    )b ON CONCAT(a.i,a.end_date)=CONCAT(b.i,'9999-12-31')
)c;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 全表
SELECT * FROM dwd_user;
-- 最新数据
SELECT * FROM dwd_user WHERE end_date='9999-12-31';
-- 快照2020-01-01
SELECT * FROM dwd_user WHERE start_date<='2020-01-01' AND end_date>'2020-01-01';
1
2
3
4
5
6

查看数据

3.3、第二次更新

SET ymd=TO_DATE('2020-01-03');
INSERT OVERWRITE TABLE dwd_user
SELECT * FROM
(
    SELECT
        i,
        balance,
        ${hiveconf:ymd} start_date,
        TO_DATE('9999-12-31') end_date
    FROM ods_user WHERE ymd=${hiveconf:ymd}
    UNION ALL
    SELECT
        a.i,
        a.balance ,
        a.start_date,
        IF(b.i IS NULL,a.end_date,b.ymd)end_date
    FROM dwd_user a LEFT JOIN(
        SELECT * FROM ods_user WHERE ymd=${hiveconf:ymd}
    )b ON CONCAT(a.i,a.end_date)=CONCAT(b.i,'9999-12-31')
)c;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- 全表
SELECT * FROM dwd_user;
-- 最新数据
SELECT * FROM dwd_user WHERE end_date='9999-12-31';
-- 快照2020-01-02
SELECT * FROM dwd_user WHERE start_date<='2020-01-02' AND end_date>'2020-01-02';
-- 快照2020-01-01
SELECT * FROM dwd_user WHERE start_date<='2020-01-01' AND end_date>'2020-01-01';
1
2
3
4
5
6
7
8

查看数据

---

补充

• 使用UNION ALL时，两个表的数据类型要一致
• 日期不一定用DATE，用STRING也行
• 更新拉链表时，两个表JOIN ON多字段，也可单字段
• 更新拉链表时，可以先写到一个中间表，再覆盖拉链表
• 上面SQL为简化的SQL，实战中HIVE表要分区（并且要使用 动态分区）
  2021-02-02最新数据写到9999-12-31分区，过期数据写到-1的2021-02-01分区

HIVE动态分区

静态分区的写法

INSERT OVERWRITE TABLE xxx_t1 PARTITION(dt='2021-06-15')
SELECT
    xxx
FROM xxx_t0
WHERE dt='2021-06-15';
1
2
3
4
5

INSERT OVERWRITE TABLE xxx_t1 PARTITION(dt='2021-06-16')
SELECT
    xxx
FROM xxx_t0
WHERE dt='2021-06-16';
1
2
3
4
5

动态分区写法（被SELECT的dt要在最后一行）

INSERT OVERWRITE TABLE xxx_t1 PARTITION(dt)
SELECT
    xxx,
    dt
FROM xxx_t0
WHERE dt='2021-06-15' OR dt='2021-06-16';
1
2
3
4
5
6

使用动态分区，需要提前设置参数

SET hive.exec.dynamici.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
1
2