Snowflake中根据配置表指定列从父表提取数据创建视图/表咨询

前置说明 #

你现有配置表my_table的字段规则如下：

• parent_table：指定要读取的父表名称
• res_table_cols：需要从父表提取的列，支持两种存储格式：逗号分隔字符串（如name,age）、数组格式（如['name','age']）

注意：如果res_table_cols存储的列名在对应父表中不存在，执行时会报列不存在错误，建议提前做列名校验。

方案1：单条配置场景（my_table仅存1条配置规则） #

如果配置表只有一条规则，比如示例中仅存储了stage_table的提取规则，可按以下步骤实现：

临时使用方案（无需存储过程） #

先执行以下查询拿到合法的父表名和列名字符串，手动拼接SELECT语句即可：

-- 兼容数组和逗号分隔字符串两种格式的列名提取
SELECT 
    parent_table,
    CASE
        WHEN ARRAY_SIZE(TRY_PARSE_JSON(res_table_cols)) IS NOT NULL THEN ARRAY_TO_STRING(TRY_PARSE_JSON(res_table_cols), ',')
        ELSE res_table_cols
    END AS final_cols
FROM my_table
LIMIT 1;

拿到结果后直接写查询语句，比如示例返回parent_table=stage_table、final_cols=name,age，直接执行SELECT name,age FROM stage_table即可。

复用方案（用存储过程自动生成视图/表） #

如果需要频繁跟随配置更新结果，可创建存储过程自动生成目标对象：

CREATE OR REPLACE PROCEDURE generate_target_table()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    v_parent_table VARCHAR;
    v_final_cols VARCHAR;
    v_create_sql VARCHAR;
BEGIN
    -- 读取配置表的规则
    SELECT 
        parent_table,
        CASE
            WHEN ARRAY_SIZE(TRY_PARSE_JSON(res_table_cols)) IS NOT NULL THEN ARRAY_TO_STRING(TRY_PARSE_JSON(res_table_cols), ',')
            ELSE res_table_cols
        END AS final_cols
    INTO :v_parent_table, :v_final_cols
    FROM my_table
    LIMIT 1;

    -- 拼接创建视图的SQL，要生成物理表的话把CREATE VIEW换成CREATE TABLE即可
    v_create_sql := 'CREATE OR REPLACE VIEW target_result AS SELECT ' || v_final_cols || ' FROM ' || v_parent_table || ';';
    
    -- 执行SQL
    EXECUTE IMMEDIATE :v_create_sql;
    
    RETURN '目标视图target_result创建成功，已提取列：' || v_final_cols;
END;
$$;

-- 调用存储过程生成结果
CALL generate_target_table();

-- 查看最终结果
SELECT * FROM target_result;

方案2：多条配置规则场景（my_table存多组父表+对应列规则） #

如果配置表有多条规则，需要把多个父表的指定列合并输出，可在存储过程中循环处理所有配置，用UNION ALL拼接结果（需保证不同父表提取的列数量和类型一致）：

CREATE OR REPLACE PROCEDURE generate_multi_source_target()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    c_config CURSOR FOR 
        SELECT 
            parent_table,
            CASE
                WHEN ARRAY_SIZE(TRY_PARSE_JSON(res_table_cols)) IS NOT NULL THEN ARRAY_TO_STRING(TRY_PARSE_JSON(res_table_cols), ',')
                ELSE res_table_cols
            END AS final_cols
        FROM my_table;
    v_union_sql VARCHAR := '';
    v_first_row BOOLEAN := TRUE;
BEGIN
    FOR rec IN c_config DO
        IF NOT v_first_row THEN
            v_union_sql := v_union_sql || ' UNION ALL ';
        END IF;
        v_union_sql := v_union_sql || 'SELECT ' || rec.final_cols || ' FROM ' || rec.parent_table;
        v_first_row := FALSE;
    END FOR;

    -- 拼接最终建表语句
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW multi_source_result AS ' || v_union_sql || ';';
    
    RETURN '多源合并视图创建成功';
END;
$$;

CALL generate_multi_source_target();

效果验证 #

按照你给出的示例：

• 父表stage_table有name、age、sex、occupation4列共3条用户数据
• my_table中parent_table='stage_table'，res_table_cols指定列为name、age
  调用上述存储过程后，生成的target_result视图只会保留name、age两列的全部3条父表数据，和预期结果一致。

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