Netezza系统如何自动合并指定前缀分月表并添加来源列

场景背景 #

• 库内现有65张分月表，命名遵循Mon_Tues_Wed_yyyymm规则，时间跨度为201701至202205，后续会按相同规则持续新增
• 单表数据量在100万-200万行，原SAS网格方案采用跨系统导数、加列、合并再回写的流程，总耗时达6小时，效率极低
• 核心要求：
  • 所有符合命名规则的表通过UNION合并为统一结果表
  • 流程全自动化，执行脚本时无需人工修改，可自动纳入后续新增的合规表
  • 合并时为每行数据新增Source列，值为数据对应的来源表表名

实现逻辑 #

通过Netezza内置系统视图_V_TABLE自动扫描匹配命名规则的表，用存储过程动态拼接合并SQL，全程在Netezza库内完成计算，无跨系统数据传输开销。默认使用UNION ALL拼接，避免UNION自带的全局去重排序开销，性能远高于原SAS方案。

可直接运行的代码 #

先创建自动合并的存储过程，使用前仅需替换代码中标记的schema名和目标结果表名即可：

-- 替换为分月表实际所属的schema名称
SET SCHEMA <YOUR_TABLE_SCHEMA>;

CREATE OR REPLACE PROCEDURE SP_AUTO_MERGE_MONTH_TABLES()
RETURNS INTEGER
LANGUAGE NZPLSQL
AS
BEGIN_PROC
DECLARE
    v_table_name VARCHAR(100);
    v_merge_sql TEXT;
    v_is_first_table INTEGER DEFAULT 1;
    -- 替换为合并后结果存储的目标表名
    v_target_table VARCHAR(100) DEFAULT '<YOUR_MERGED_RESULT_TABLE>';
BEGIN
    -- 先清理已存在的旧目标表
    EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS ' || v_target_table || ';';
    v_merge_sql := '';

    -- 遍历所有符合命名规则的表：前缀固定为Mon_Tues_Wed_，后缀为6位年月数字
    -- 正则规则保证不会误匹配临时表、备份表等非目标表
    FOR v_table_name IN 
        SELECT TABLENAME 
        FROM _V_TABLE 
        WHERE SCHEMA = CURRENT_SCHEMA
          AND TABLENAME ~ '^Mon_Tues_Wed_[0-9]{6}$'
        ORDER BY TABLENAME
    LOOP
        IF v_is_first_table = 1 THEN
            -- 第一张表直接作为建表基础，同时新增Source列
            v_merge_sql := 'CREATE TABLE ' || v_target_table || ' AS 
                SELECT *, ''' || v_table_name || ''' AS Source 
                FROM ' || v_table_name;
            v_is_first_table = 0;
        ELSE
            -- 后续表通过UNION ALL拼接，如业务要求全局去重可替换为UNION
            v_merge_sql := v_merge_sql || ' 
                UNION ALL 
                SELECT *, ''' || v_table_name || ''' AS Source 
                FROM ' || v_table_name;
        END IF;
    END LOOP;

    -- 执行拼接完成的合并语句
    EXECUTE IMMEDIATE v_merge_sql;

    -- 可选：为Source列创建索引，提升后续按来源表过滤的查询效率
    EXECUTE IMMEDIATE 'CREATE INDEX IDX_' || REPLACE(v_target_table, '.', '_') || '_SOURCE ON ' || v_target_table || '(SOURCE);';

    RAISE NOTICE '分月表合并完成，结果已写入表%', v_target_table;
    RETURN 0;
END;
END_PROC;

执行方式 #

存储过程创建完成后，每次需要更新全量合并数据时，直接调用即可，无需修改任何代码，会自动识别所有符合规则的存量、新增表：

CALL SP_AUTO_MERGE_MONTH_TABLES();

注意事项 #

• 所有待合并分月表的列数、列顺序、对应列的数据类型必须保持一致，否则会报列匹配错误
• 如果分月表分布在多个schema，可修改FOR循环中的查询条件，调整schema过滤规则即可
• 若业务不需要每次全量重建结果表，可修改存储过程逻辑，仅识别未合并过的新表做INSERT追加，进一步缩短执行时间
• 非必要不要将UNION ALL改为UNION，全局去重的排序开销会让执行时间提升数倍

性能参考 #

全流程在Netezza内部MPP架构下并行执行，无跨系统数据传输开销，65张表总计约1亿行数据的合并耗时通常在5-10分钟区间，相比原SAS流程提升30倍以上。

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