概述
-
什么是事实表?
每行数据代表一个业务事件,通常有很多外键(地区、用户…)
业务事件可以是:下单、支付、退款、评价…
业务事件有数字度量,如:数量、金额、次数…
行数较多,列数较少
每天很多新增 -
事实表的分类
分类 | 说明 | 特点 | 场景 |
---|---|---|---|
事务型事实表 | 以每个事务为单位 | 数据只追加不修改 | 一个订单支付 一笔订单退款 |
周期型快照事实表 | 保留固定时间间隔的数据 | 不会保留所有数据 | 点赞数 |
累积型快照事实表 | 跟踪业务事实的变化 | 数据可修改 | 订单状态 |
- 本文以订单状态表为例
行转多列
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|
+--------+-------------------+-------------------+
数仓详细
数据路径
表名 | 表名 | 路径 | 策略 | 备注 |
---|---|---|---|---|
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的思路是一样的
状态 | 时间字段 | 说明 | 备注 |
---|---|---|---|
待支付 | 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了