Redshift大表拆分优化：按日/年滚动/月末快照拆分及视图合并求助

1. 创建核心表结构 #

假设源表包含id、data_col1、data_col2、biz_date（数据所属日期）字段，可根据实际业务调整字段定义：

-- daily_table：每日存储前一天数据，每日截断重加载
CREATE TABLE IF NOT EXISTS daily_table (
    id INT,
    data_col1 VARCHAR(255),
    data_col2 NUMERIC(18,2),
    load_date DATE,
    PRIMARY KEY (id, load_date)
) DISTSTYLE KEY DISTKEY (id) SORTKEY (load_date);

-- 1Year_rolling：滚动保留一年数据，每日接收daily_table迁移数据，每月将最早一个月数据移至快照表
CREATE TABLE IF NOT EXISTS 1Year_rolling (
    id INT,
    data_col1 VARCHAR(255),
    data_col2 NUMERIC(18,2),
    load_date DATE,
    PRIMARY KEY (id, load_date)
) DISTSTYLE KEY DISTKEY (id) SORTKEY (load_date);

-- monthend_snapshot：存储2019年起的月末快照数据
CREATE TABLE IF NOT EXISTS monthend_snapshot (
    id INT,
    data_col1 VARCHAR(255),
    data_col2 NUMERIC(18,2),
    load_date DATE,
    snapshot_month DATE, -- 标记数据所属快照月份，便于管理
    PRIMARY KEY (id, snapshot_month)
) DISTSTYLE KEY DISTKEY (id) SORTKEY (snapshot_month);

2. 每日调度执行流程 #

严格按顺序执行以下步骤：

步骤1：迁移daily_table数据到1Year_rolling #

-- 避免重复插入同id同日期的数据
INSERT INTO 1Year_rolling (id, data_col1, data_col2, load_date)
SELECT id, data_col1, data_col2, load_date
FROM daily_table
WHERE NOT EXISTS (
    SELECT 1 FROM 1Year_rolling r
    WHERE r.id = daily_table.id AND r.load_date = daily_table.load_date
);

步骤2：截断daily_table并加载前一天源数据 #

TRUNCATE TABLE daily_table;

-- 加载源表中前一天的数据，假设源表为source_table
INSERT INTO daily_table (id, data_col1, data_col2, load_date)
SELECT id, data_col1, data_col2, biz_date
FROM source_table
WHERE biz_date = CURRENT_DATE - INTERVAL '1 day';

步骤3：清理1Year_rolling中超期数据 #

清理掉超过1年的历史数据，维持滚动窗口：

DELETE FROM 1Year_rolling
WHERE load_date < CURRENT_DATE - INTERVAL '1 year';

3. 每月1日调度执行快照迁移流程 #

步骤1：将1Year_rolling中最早月份数据迁移到monthend_snapshot #

WITH target_month AS (
    SELECT DATE_TRUNC('month', MIN(load_date)) AS month
    FROM 1Year_rolling
    WHERE DATE_TRUNC('month', load_date) <= CURRENT_DATE - INTERVAL '1 year'
)
INSERT INTO monthend_snapshot (id, data_col1, data_col2, load_date, snapshot_month)
SELECT id, data_col1, data_col2, load_date, (SELECT month FROM target_month)
FROM 1Year_rolling
WHERE DATE_TRUNC('month', load_date) = (SELECT month FROM target_month)
AND NOT EXISTS (
    SELECT 1 FROM monthend_snapshot s
    WHERE s.id = 1Year_rolling.id AND s.snapshot_month = (SELECT month FROM target_month)
);

步骤2：从1Year_rolling中删除已迁移的月份数据 #

WITH target_month AS (
    SELECT DATE_TRUNC('month', MIN(load_date)) AS month
    FROM 1Year_rolling
    WHERE DATE_TRUNC('month', load_date) <= CURRENT_DATE - INTERVAL '1 year'
)
DELETE FROM 1Year_rolling
WHERE DATE_TRUNC('month', load_date) = (SELECT month FROM target_month);

4. 创建无重复合并视图 #

通过优先级排序确保同id同日期的数据唯一，优先取最新的表数据：

CREATE OR REPLACE VIEW combined_data AS
WITH ranked_data AS (
    SELECT id, data_col1, data_col2, load_date,
           ROW_NUMBER() OVER (PARTITION BY id, load_date ORDER BY priority DESC) AS rn
    FROM (
        -- daily_table优先级最高（最新数据）
        SELECT id, data_col1, data_col2, load_date, 1 AS priority FROM daily_table
        UNION ALL
        -- 1Year_rolling次之
        SELECT id, data_col1, data_col2, load_date, 2 AS priority FROM 1Year_rolling
        UNION ALL
        -- monthend_snapshot优先级最低（归档数据）
        SELECT id, data_col1, data_col2, load_date, 3 AS priority FROM monthend_snapshot
    ) all_data
)
SELECT id, data_col1, data_col2, load_date
FROM ranked_data
WHERE rn = 1;

关键注意事项 #

• 调度顺序：每月任务需在当日每日任务之前执行，避免刚加载的前一天数据被误迁移。
• 性能优化：Redshift中DELETE操作会产生碎片，建议定期对1Year_rolling执行VACUUM和ANALYZE；也可将该表按load_date月份分区，迁移时直接DROP分区提升效率。
• 源数据去重：加载到daily_table前需对源数据去重，避免后续表出现重复数据。

内容的提问来源于stack exchange，提问作者Aniket