图解HIVE累积型快照事实表

概述

• 什么是事实表？
  每行数据代表一个业务事件，通常有很多外键（地区、用户…）
  业务事件可以是：下单、支付、退款、评价…
  业务事件有数字度量，如：数量、金额、次数…
  行数较多，列数较少
  每天很多新增

• 事实表的分类

• 本文以订单状态表为例

行转多列

1、按订单ID分组，聚合订单状态和时间，转为MAP

SELECT
    order_id,
    STR_TO_MAP(CONCAT_WS(',',COLLECT_SET(CONCAT(order_status,'=',operation_time))),',','=') m
FROM
    ods_order_status
GROUP BY
    order_id
1
2
3
4
5
6
7

打印结果

+--------+----------------------------------------------------------+
|order_id|m                                                         |
+--------+----------------------------------------------------------+
|P2      |[end -> 2020-01-01 23:45:00, start -> 2020-01-01 22:45:00]|
|P3      |[start -> 2020-01-01 23:30:00]                            |
|P1      |[start -> 2020-01-01 08:00:00, end -> 2020-01-01 08:01:00]|
+--------+----------------------------------------------------------+
1
2
3
4
5
6
7

2、按Key获取MAP值

WITH
    t1 AS (
        SELECT
            order_id,
            STR_TO_MAP(CONCAT_WS(',',COLLECT_SET(CONCAT(order_status,'=',operation_time))),',','=') m
        FROM
            ods_order_status
        GROUP BY
            order_id
    )
SELECT
    order_id,
    m['start'] start_time,
    m['end'] end_time
FROM
    t1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

打印结果

+--------+-------------------+-------------------+
|order_id|start_time         |end_time           |
+--------+-------------------+-------------------+
|P2      |2020-01-01 22:45:00|2020-01-01 23:45:00|
|P3      |2020-01-01 23:30:00|null               |
|P1      |2020-01-01 08:00:00|2020-01-01 08:01:00|
+--------+-------------------+-------------------+
1
2
3
4
5
6
7

数仓详细

数据路径

代码

1、数据准备

-- 建库：e-commerce
DROP DATABASE IF EXISTS ec CASCADE;
CREATE DATABASE ec LOCATION '/ec';
USE ec;
-- 建表：原始层，订单状态表
DROP TABLE IF EXISTS ec.ods_order_status;
CREATE TABLE ec.ods_order_status (
    order_id STRING,
    order_status STRING,
    operation_time STRING)
PARTITIONED BY (ymd STRING)
LOCATION '/ec/ods_order_status';
-- 建表：明细层，订单(累积型快照事实)表
DROP TABLE IF EXISTS ec.dwd_order;
CREATE TABLE ec.dwd_order (
    order_id STRING,
    start_time STRING,
    end_time STRING)
PARTITIONED BY (ymd STRING)
LOCATION '/ec/dwd_order';
-- 造数据，写到原始层
INSERT INTO TABLE ec.ods_order_status PARTITION(ymd='2020-01-01') VALUES
("P1","start","2020-01-01 08:00:00"),
("P1","end","2020-01-01 08:01:00"),
("P2","start","2020-01-01 22:45:00"),
("P3","start","2020-01-01 23:30:00");
INSERT INTO TABLE ec.ods_order_status PARTITION(ymd='2020-01-02') VALUES
("P3","end","2020-01-02 00:15:00"),
("P4","start","2020-01-02 06:30:00");
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

2、设置动态分区

-- 开启动态分区功能
SET hive.exec.dynamic.partition=true;
-- 设置动态分区为非严格模式
SET hive.exec.dynamic.partition.mode=nonstrict;
1
2
3
4

3、第一天数据写入

数据查询

WITH
    t1 AS(
        SELECT
            order_id,
            STR_TO_MAP(CONCAT_WS(',',COLLECT_SET(CONCAT(order_status,'=',operation_time))),',','=') m
        FROM ec.ods_order_status
        WHERE ymd='2020-01-01'
        GROUP BY order_id
    )
SELECT
    order_id,
    m['start'] start_time,
    m['end'] end_time,
    CASE
        WHEN m['end'] IS NOT NULL THEN '2020-01-01'
        ELSE '9999-12-31'
    END ymd
FROM t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

查询结果

数据写入

注意：语法要求WITH写在INSERT前面

WITH
    t1 AS(
        SELECT
            order_id,
            STR_TO_MAP(CONCAT_WS(',',COLLECT_SET(CONCAT(order_status,'=',operation_time))),',','=') m
        FROM ec.ods_order_status
        WHERE ymd='2020-01-01'
        GROUP BY order_id
    )
INSERT OVERWRITE TABLE ec.dwd_order PARTITION(ymd)
SELECT
    order_id,
    m['start'] start_time,
    m['end'] end_time,
    CASE
        WHEN m['end'] IS NOT NULL THEN '2020-01-01'
        ELSE '9999-12-31'
    END ymd
FROM t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

写入后结果

4、第二天数据写入

数据查询

WITH
    t1 AS(
        SELECT
            order_id,
            STR_TO_MAP(CONCAT_WS(',',COLLECT_SET(CONCAT(order_status,'=',operation_time))),',','=')m
        FROM ec.ods_order_status
        WHERE ymd='2020-01-02'
        GROUP BY order_id
    ),
    new AS(
    SELECT
        order_id,
        m['start'] start_time,
        m['end'] end_time,
        CASE
            WHEN m['end'] IS NOT NULL THEN '2020-01-02'
            ELSE '9999-12-31'
        END ymd
    FROM
        t1
    ),
    old AS (SELECT * FROM ec.dwd_order WHERE ymd='9999-12-31')
SELECT
    NVL(new.order_id,old.order_id) order_id,
    NVL(new.start_time,old.start_time) start_time,
    NVL(new.end_time,old.end_time) end_time,
    NVL(new.ymd,old.ymd) ymd
FROM new
FULL OUTER JOIN old
ON new.order_id=old.order_id;
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

查询结果

数据写入

注意：语法要求WITH写在INSERT前面

WITH
    t1 AS(
        SELECT
            order_id,
            STR_TO_MAP(CONCAT_WS(',',COLLECT_SET(CONCAT(order_status,'=',operation_time))),',','=')m
        FROM ec.ods_order_status
        WHERE ymd='2020-01-02'
        GROUP BY order_id
    ),
    new AS(
    SELECT
        order_id,
        m['start'] start_time,
        m['end'] end_time,
        CASE
            WHEN m['end'] IS NOT NULL THEN '2020-01-02'
            ELSE '9999-12-31'
        END ymd
    FROM
        t1
    ),
    old AS (SELECT * FROM ec.dwd_order WHERE ymd='9999-12-31')
INSERT OVERWRITE TABLE ec.dwd_order PARTITION(ymd)
SELECT
    NVL(new.order_id,old.order_id) order_id,
    NVL(new.start_time,old.start_time) start_time,
    NVL(new.end_time,old.end_time) end_time,
    NVL(new.ymd,old.ymd) ymd
FROM new
FULL OUTER JOIN old
ON new.order_id=old.order_id;
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

写入后结果

补充

上面的订单状态比较简单，这个全一点，SQL的思路是一样的

WITH
    t1 AS(
        SELECT
            order_id,
            STR_TO_MAP(CONCAT_WS(',',COLLECT_SET(CONCAT(order_status,'=',operation_time))),',','=') m
        FROM ec.ods_order_status
        WHERE ymd='2020-01-01'
        GROUP BY order_id
    )
SELECT
    order_id,
    m['已支付'] pay_time,
    m['已取消'] cancel_time,
    m['确认收货'] confirm_time,
    m['退款中'] refund_time,
    m['退款完成'] refund_finish_time,
    m['支付过期'] overdue_time,
    m['结束'] end_time,
    CASE
        WHEN m['结束'] IS NOT NULL THEN '昨天'
        ELSE '9999-12-31'
    END ymd
FROM t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

另外，订单状态表（ods_order_status）要和订单表（ods_order）连接，本文就不JOIN了