hive 事务表自动合并 hive事实表

转载

mob64ca13fb1f2e 2024-03-12 17:42:35

文章标签 hive 事务表自动合并 hive hadoop 累积型快照事实表订单状态表 文章分类 Hive 大数据

文章目录

概述
行转多列
数仓详细

数据路径
代码

1、数据准备
2、设置动态分区
3、第一天数据写入

数据查询
数据写入

4、第二天数据写入

数据查询
数据写入

补充

概述

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

分类	说明	特点	场景
事务型事实表	以每个事务为单位	数据只追加不修改	一个订单支付一笔订单退款
周期型快照事实表	保留固定时间间隔的数据	不会保留所有数据	点赞数
累积型快照事实表	跟踪业务事实的变化	数据可修改	订单状态

本文以订单状态表为例

行转多列

hive 事务表自动合并 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

打印结果

+--------+----------------------------------------------------------+
|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]|
+--------+----------------------------------------------------------+

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

打印结果

+--------+-------------------+-------------------+
|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|
+--------+-------------------+-------------------+

数仓详细

hive 事务表自动合并 hive事实表_hive_02

数据路径

Sqoop

HIVE

MySQL

HDFS

ODS

DWD

表名	表名	路径	策略	备注
ods_order	订单表	sqoop > hdfs > ods	增量变化同步按`create_time`和`operate_time`	此处省略
ods_order_status	订单状态流水表	sqoop > hdfs > ods	增量同步按`operate_time`
dwd_order	订单表	ods > dwd	未结束订单写到`9999-12-31`分区结束订单按结束日期写到日期分区

代码

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");

2、设置动态分区

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

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;

查询结果

数据写入

注意：语法要求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;

写入后结果

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;

查询结果

数据写入

注意：语法要求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;

写入后结果

补充

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

下单

支付

主动或被动确认

申请退款

主动取消

1小时内没付款

7天内没申请退款

即

开始

待支付

已支付

确认收货

退款中

退款完成

已取消

支付过期

结束

状态	时间字段	说明	备注
待支付	create_time	创建时间
已支付	pay_time	支付时间
确认收货	confirm_time	确认时间	到货后7天内，买家可主动确认收货或退款；7天后没有操作将会自动确认收货
已取消	cancel_time	取消时间	下单后支付前，主动取消订单
支付过期	overdue_time	过期时间	下单后1小时内没有支付
退款中	refund_time	退款申请时间
退款完成	refund_finish_time	退款完成时间
结束	end_time	结束时间

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;

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。