未记录事实核验 · 状态：维护中

记录仅说明核验时间与维护状态，不保证适用于所有环境；使用前请留意正文中的版本、适用条件与风险提示。

Greenplum数仓建模实践 - 从维度建模到PB级落地

维度建模在Greenplum上的最佳实践

事实表设计：分布键与外键对齐

事实表是数仓的核心，通常是最大的表。分布键的选择直接决定与维度表JOIN时是否需要跨节点传输。

-- 电商数仓：订单事实表
CREATE TABLE fact_orders (
    order_id        BIGINT,
    order_date_key  INT,          -- 日期维度外键
    user_key        INT,          -- 用户维度外键
    product_key     INT,          -- 商品维度外键
    city_key        INT,          -- 城市维度外键
    amount          DECIMAL(10,2),
    quantity        INT,
    discount        DECIMAL(4,2),
    created_at      TIMESTAMP
)
WITH (appendoptimized=true, orientation=column, compresstype=zstd, compresslevel=1)
DISTRIBUTED BY (user_key)         -- 分布键选择最常JOIN的维度键
PARTITION BY RANGE (order_date_key) (
    START (20230101) END (20270101) EVERY (100),   -- 整数步长100，不是日历月或日分区；生产使用须另行定义日历边界
    DEFAULT PARTITION other
);

-- 关键决策说明：
-- 1. DISTRIBUTED BY (user_key)：用户维度是最常用的过滤/聚合维度
-- 2. 列存储：只有几列参与每次查询，列存大幅减少I/O
-- 3. 按日期键分区：几乎所有查询都有日期过滤，分区裁剪效果显著
-- 4. zstd压缩：高压缩比（通常3-5倍），减少磁盘I/O和存储成本

维度表设计：REPLICATED vs DISTRIBUTED

-- 小维度表（<100万行）：使用REPLICATED，每个Segment都有完整副本
-- 优势：与事实表JOIN时完全避免Motion（数据已在本地）
CREATE TABLE dim_date (
    date_key    INT PRIMARY KEY,
    date_value  DATE,
    year        SMALLINT,
    quarter     SMALLINT,
    month       SMALLINT,
    week        SMALLINT,
    day_of_week SMALLINT,
    is_holiday  BOOLEAN
)
DISTRIBUTED REPLICATED;  -- 日期维度通常只有几千行，REPLICATED最优

-- 中等维度表（百万到千万行）：与事实表分布键对齐
CREATE TABLE dim_user (
    user_key    INT,
    user_id     BIGINT,
    name        VARCHAR(50),
    city        VARCHAR(50),
    level       SMALLINT,
    register_date DATE,
    start_date  DATE,          -- 拉链表有效期开始
    end_date    DATE           -- 9999-12-31 表示当前有效
)
WITH (appendoptimized=true, compresstype=zstd)
DISTRIBUTED BY (user_key);  -- 与fact_orders的user_key分布键相同，是实现Collocated Join的条件之一

-- 大维度表（>千万行）：与最常关联的事实表分布键一致
CREATE TABLE dim_product (
    product_key   INT,
    product_id    BIGINT,
    product_name  VARCHAR(200),
    category_l1   VARCHAR(50),
    category_l2   VARCHAR(50),
    brand         VARCHAR(100),
    price         DECIMAL(10,2)
)
DISTRIBUTED BY (product_key);

Collocated Join验证

-- 验证fact_orders与dim_user的JOIN是否触发Motion
EXPLAIN SELECT u.city, SUM(o.amount)
FROM fact_orders o
JOIN dim_user u ON o.user_key = u.user_key
WHERE o.order_date_key >= 20240101
  AND u.end_date = '9999-12-31'   -- 当前有效的用户
GROUP BY u.city;

-- 检查JOIN输入是否发生Redistribute/Broadcast Motion，以判断JOIN是否在本地完成；GROUP BY和结果汇集仍可能产生Motion
-- 如果JOIN输入出现Redistribute Motion：检查分布键、类型、分布策略及连接条件；隐式转换可能影响本地JOIN，须以实际执行计划确认

宽表 vs 范式化：Greenplum的选型指南

Greenplum的特殊考量

传统OLTP数据库遵循范式化（3NF）减少数据冗余。Greenplum作为OLAP数据库，在以下方面与OLTP数据库不同：

支持宽表的因素：

• 列存储下，宽表的冗余列不参与查询时无额外I/O代价
• 减少JOIN可减少部分数据移动，但聚合、排序及结果汇集仍可能产生Motion，性能收益须实测
• 更简单的SQL，开发效率更高

支持范式化（星型/雪花模型）的因素：

• 维度属性变化时只需更新维度表（而非全量更新宽表）
• 维度共享（同一维度被多个事实表使用）
• 存储空间（冗余字段确实占空间）

实践建议

Greenplum数仓的最佳模式：星型模型（Star Schema）

- 事实表：窄（只含度量和外键），列存，按日期分区
- 维度表：宽（包含所有属性，减少JOIN），堆表或REPLICATED
- 不推荐：雪花模型（过多维度表JOIN在Greenplum上代价高）
- 不推荐：大宽表（超过200列，维护困难，且统计信息质量下降）

-- 避免：雪花模型的多层维度JOIN
-- 低效：product → category → category_parent → department
SELECT d.department_name, SUM(o.amount)
FROM fact_orders o
JOIN dim_product p      ON o.product_key = p.product_key
JOIN dim_category c     ON p.category_key = c.category_key
JOIN dim_category_p cp  ON c.parent_key = cp.category_key
JOIN dim_department d   ON cp.dept_key = d.dept_key
GROUP BY d.department_name;

-- 推荐：将层级属性内嵌到商品维度表（维度反范式化）
-- category_l1和category_l2已在前文dim_product中定义，无需重复添加
ALTER TABLE dim_product ADD COLUMN department VARCHAR(50);
-- 查询前须由ETL填充并维护这些层级属性
-- 查询简化为单表JOIN
SELECT p.department, SUM(o.amount)
FROM fact_orders o
JOIN dim_product p ON o.product_key = p.product_key
GROUP BY p.department;

ETL入库策略：性能对比

三种主要入库方式

方式1：COPY（最快，适合批量导入）

-- COPY从文件系统导入（Master端）
COPY fact_orders FROM '/tmp/orders_2024.csv' WITH (FORMAT CSV, DELIMITER ',', HEADER);

-- gpfdist外部表并行加载（性能最佳，多个Segment并行读取）
-- Step1：在ETL服务器上启动gpfdist服务
-- gpfdist -d /data/load_files -p 8080 &

-- Step2：创建外部表
CREATE EXTERNAL TABLE ext_orders_load (
    order_id BIGINT, user_id INT, amount DECIMAL, order_date DATE
)
LOCATION ('gpfdist://etl-server:8080/orders_2024.csv')
FORMAT 'CSV' (DELIMITER ',' HEADER);

-- Step3：INSERT INTO SELECT加载（所有Segment并行从gpfdist读取）
INSERT INTO fact_orders SELECT * FROM ext_orders_load;
-- 原稿未核实教学示例：32个Segment的集群，100GB文件约需3-5分钟；未提供版本、硬件、文件格式和并发条件，不作为性能承诺

-- gpload（封装了gpfdist，配置更方便）
-- 详见06-数据导入导出

方式2：INSERT INTO SELECT（中等，常用于ETL转换）

-- 从ODS层加工后写入DWD层（可以包含复杂的JOIN和计算）
INSERT INTO fact_orders
SELECT
    o.order_id,
    d.date_key AS order_date_key,
    u.user_key,
    p.product_key,
    c.city_key,
    oi.amount,
    oi.quantity,
    oi.discount,
    o.created_at
FROM ods_orders o
JOIN ods_order_items oi ON o.order_id = oi.order_id
JOIN dim_date d ON DATE(o.created_at) = d.date_value
JOIN dim_user u ON o.user_id = u.user_id AND u.end_date = '9999-12-31'
JOIN dim_product p ON oi.product_id = p.product_id
JOIN dim_city c ON o.city_code = c.city_code
WHERE o.status = 1
  AND DATE(o.created_at) = '2024-06-15';

方式3：直接UPDATE（最慢，尽量避免）

-- Greenplum的UPDATE会导致旧行被标记为删除+插入新行（类似PostgreSQL的MVCC）
-- 大量UPDATE后应评估死元组、空间膨胀及常规VACUUM需求；VACUUM FULL涉及重写和锁，不应作为必选步骤

-- 可评估DELETE + INSERT，但它同样产生删除与插入开销，并不天然优于UPDATE
-- 对覆盖完整分区的批量更新，可评估分区交换（EXCHANGE PARTITION），先验证边界和表结构兼容性

-- 避免：
UPDATE fact_orders SET amount = amount * 1.1 WHERE order_date_key = 20240615;  -- 百万行UPDATE极慢

-- 推荐：对于大范围更新，用临时表+分区交换
CREATE TABLE tmp_orders_20240615 AS
SELECT order_id, order_date_key, user_key, product_key, city_key,
       (amount * 1.1)::DECIMAL(10,2) AS amount,
       quantity, discount, created_at
FROM fact_orders
WHERE order_date_key = 20240615
DISTRIBUTED BY (user_key);
-- 仅适用于已单独建立20240615日分区的设计；交换前还须核对存储属性、约束与分区完整性
ALTER TABLE fact_orders EXCHANGE PARTITION FOR (20240615) WITH TABLE tmp_orders_20240615;

批量加载的最佳实践

-- 1. 先加载数据，后建索引（Greenplum索引很少用，但如果有的话）
-- 2. Greenplum不强制外键约束，须在ETL中验证维度键完整性；不要依赖数据库外键检查
-- 3. 加载完后立即ANALYZE
-- 4. 使用gpfdist多文件并行加载

-- 典型的日增量ETL流程
-- Step1：清理目标分区的旧数据（如果有）
-- 以下仅适用于事先按整数日期键建立的独立日分区，不可直接套用前文EVERY(100)定义
ALTER TABLE fact_orders DROP PARTITION FOR (20240615);
ALTER TABLE fact_orders ADD PARTITION d20240615
    START (20240615) END (20240616);

-- Step2：并行加载新数据
INSERT INTO fact_orders SELECT ... FROM ext_orders_load;

-- Step3：更新统计信息
ANALYZE fact_orders;

-- Step4：验证行数
SELECT COUNT(*) FROM fact_orders WHERE order_date_key = 20240615;

数据生命周期管理

分区删除（最高效的历史数据清理）

-- 按留存策略清理历史分区；通常比逐行DELETE高效，但耗时与锁等待取决于环境，不保证毫秒级完成
ALTER TABLE fact_orders DROP PARTITION FOR (20210101);  -- 删除2021年1月1日分区

-- 批量清理（清理2020年之前所有分区）
DO $$
DECLARE
    yr INT;
    mo INT;
BEGIN
    FOR yr IN 2017..2019 LOOP
        FOR mo IN 1..12 LOOP
            BEGIN
                EXECUTE format('ALTER TABLE fact_orders DROP PARTITION FOR (%s%s01)',
                               yr, LPAD(mo::TEXT, 2, '0'));
            EXCEPTION WHEN OTHERS THEN
                NULL;  -- 此处会忽略所有异常，不只分区不存在；生产任务须记录并区分错误，避免静默漏清理
            END;
        END LOOP;
    END LOOP;
END;
$$;

归档到OSS（冷热数据分离）

-- Step1：将旧分区数据导出到OSS（S3/阿里OSS）
-- 使用gpfdist写外部表，或直接用PXF写S3

CREATE WRITABLE EXTERNAL TABLE ext_archive_2020 (LIKE fact_orders)
LOCATION ('pxf://my-bucket/archive/fact_orders/year=2020/?PROFILE=s3:parquet&SERVER=s3')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');

INSERT INTO ext_archive_2020
SELECT * FROM fact_orders
WHERE order_date_key >= 20200101 AND order_date_key < 20210101;

-- Step2：验证归档数据完整性
-- Step3：删除Greenplum中的历史分区
ALTER TABLE fact_orders DROP PARTITION FOR (20200101);  -- 依此类推

-- Step4：如需查询历史数据，通过外部表按需访问（不常规查询，无需常驻内存）
CREATE EXTERNAL TABLE ext_orders_2020 (LIKE fact_orders)
LOCATION ('pxf://my-bucket/archive/fact_orders/year=2020/?PROFILE=s3:parquet&SERVER=s3')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

Greenplum与BI工具对接

连接协议

Greenplum兼容PostgreSQL协议，支持PostgreSQL的BI工具通常可尝试连接；仍须按Greenplum、BI工具及驱动版本验证认证、类型映射、元数据查询和网关要求。

BI工具	连接方式	注意事项
Tableau	PostgreSQL连接器	核对当前版本支持的Greenplum连接器和驱动，性能须实测
Apache Superset	SQLAlchemy PostgreSQL	直连，性能好
Metabase	PostgreSQL连接	直连即可
DataGrip/DBeaver	PostgreSQL驱动	日常开发调试
Power BI	PostgreSQL连接器及适用的数据网关	是否需要本地网关取决于部署、网络可达性和刷新方式

对接优化建议

-- 1. 为BI查询创建专用资源队列，防止影响ETL
CREATE RESOURCE QUEUE bi_queue
    WITH (ACTIVE_STATEMENTS = 10, PRIORITY = MEDIUM);
ALTER ROLE bi_user RESOURCE QUEUE bi_queue;

-- 2. 为高频BI查询创建物化视图或预聚合表（加速仪表板加载）
CREATE TABLE agg_daily_gmv AS
SELECT o.order_date_key, u.city, SUM(o.amount) AS daily_gmv, COUNT(*) AS order_count
FROM fact_orders o
JOIN dim_user u ON o.user_key = u.user_key AND u.end_date = '9999-12-31'
GROUP BY o.order_date_key, u.city
DISTRIBUTED BY (city);
-- 此处按当前用户城市聚合；若要统计下单时城市，须改用相应历史维度口径

-- 每日ETL后刷新
TRUNCATE TABLE agg_daily_gmv;
INSERT INTO agg_daily_gmv SELECT ...;
ANALYZE agg_daily_gmv;

-- 3. 创建分析视图，隐藏复杂的维度JOIN；只读访问须另行通过角色与SELECT权限控制
CREATE VIEW v_order_analysis AS
SELECT
    o.order_id,
    d.year, d.month,
    u.city AS user_city,
    p.category_l1 AS product_category,
    o.amount
FROM fact_orders o
JOIN dim_date d   ON o.order_date_key = d.date_key
JOIN dim_user u   ON o.user_key = u.user_key AND u.end_date = '9999-12-31'
JOIN dim_product p ON o.product_key = p.product_key;

案例：某电信公司PB级数仓在Greenplum上的实践

教学假设说明：本节未提供企业身份、来源或测试记录，以下企业背景、规模、容量与性能数字均按原稿未核实教学示例保留，不代表真实企业成果，也不构成PB级落地验证。

背景

• 数据规模：日增量约50亿条通话/上网记录，历史数据约3PB
• 集群规模：80个Segment节点，每节点24C/192GB/10TB SSD
• 主要用例：运营分析报表、用户画像、网络质量分析

关键设计决策

事实表设计：

-- 通话记录事实表（日增50亿条）
CREATE TABLE fact_cdr (
    call_id         BIGINT,
    call_date_key   INT,
    caller_user_key INT,
    callee_user_key INT,
    base_station_key INT,
    duration_sec    INT,
    call_type       SMALLINT,    -- 1=语音, 2=视频, 3=短信
    fee_amount      DECIMAL(8,2)
)
WITH (appendoptimized=true, orientation=column, compresstype=zstd, compresslevel=3)
-- 更高压缩级别：数据量极大，I/O是主要瓶颈
DISTRIBUTED BY (caller_user_key)
PARTITION BY RANGE (call_date_key) (
    START (20200101) END (20280101) EVERY (100),  -- 整数步长100，不是按天分区；须按实际日期定义分区边界
    DEFAULT PARTITION other
);
-- 原稿未核实教学示例：列存+zstd压缩后，每天50亿条记录约占150GB（原始CSV约1.2TB）
-- 示例压缩比约8倍；无实测记录，不能据此估算实际存储需求

查询优化实践：

-- 用户月通话时长统计（高频查询，已预聚合）
-- 原始查询（原稿未核实示例耗时：数十秒）：直接查fact_cdr
SELECT caller_user_key, SUM(duration_sec)
FROM fact_cdr
WHERE call_date_key >= 20240601 AND call_date_key < 20240701
GROUP BY caller_user_key;

-- 优化：每日预聚合到月汇总表（原稿未核实示例：毫秒级响应，实际耗时须测试）
CREATE TABLE agg_user_monthly_cdr (
    year_month      INT,          -- YYYYMM格式
    user_key        INT,
    total_duration  BIGINT,
    call_count      INT
)
WITH (appendoptimized=true, compresstype=zstd)
DISTRIBUTED BY (user_key);

-- 每日ETL追加当日汇总：同一月份、同一用户会有多行，月结果需再按year_month和user_key聚合
-- 本示例未实现重跑幂等控制，重复执行会重复累计，生产任务须另行设计去重或分区重建
INSERT INTO agg_user_monthly_cdr
SELECT
    EXTRACT(YEAR FROM d.date_value) * 100 + EXTRACT(MONTH FROM d.date_value) AS year_month,
    caller_user_key AS user_key,
    SUM(duration_sec) AS total_duration,
    COUNT(*) AS call_count
FROM fact_cdr f
JOIN dim_date d ON f.call_date_key = d.date_key
WHERE f.call_date_key = 20240615  -- 每日增量
GROUP BY 1, 2;

数据生命周期：

在线（Greenplum）：保留最近2年数据，约2TB
近线（Greenplum冷存）：2-5年数据，部分压缩，约10TB
离线（OSS归档）：5年以上，Parquet格式，通过PXF按需访问
以上为原稿未核实示例数字；与前述每天150GB及历史3PB缺少一致的容量口径，不能用于容量规划。

关联知识

• 09-架构原理与核心特性深度解析 - 分布键与Motion原理
• 07-表设计与分区 - 分区表基础
• 06-数据导入导出 - gpload/gpfdist详细用法
• 10-性能调优实战（原引用“10-性能调优实战”未能确定有效目标，暂不可跳转） - 建模后的性能优化
• 数据仓库分层架构（原引用“数据仓库分层架构”未能确定有效目标，暂不可跳转） - ODS/DWD/DWS/ADS分层设计
• 维度建模方法论（原引用“维度建模方法论”未能确定有效目标，暂不可跳转） - Kimball维度建模理论基础