Snowflake中无需指定EntityName的动态透视视图创建求助

问题背景 #

现有如下Snowflake表结构及示例数据：

-- Sample data setup
CREATE OR REPLACE TEMP TABLE your_table 
(
    ID VARCHAR(50),
    EntityName VARCHAR(50),
    Value VARCHAR(50)
);

INSERT INTO your_table (ID, EntityName, Value)
VALUES
    ('IL15342506861', 'AAP_DENOM', '02107'),
    ('IL15342506861', 'AAP_DENOM_PYCM', '098'),
    ('IL15342506861', 'AAP_DENOM_PYEM', '2154'),
    ('IL15342506861', 'AAP_NUM', '3241'),
    ('IL15342506861', 'AAP_NUM_PYEM', '215'),
    ('IL15342506861', 'AGE', '23'),
    ('IL15342506213', 'AAP_DENOM_PYEM', '134'),
    ('IL15342506213', 'AGE', '12');

传统静态透视视图需要手动枚举所有EntityName，当EntityName新增或变更时，必须手动修改视图定义：

CREATE OR REPLACE VIEW pivoted_view AS
SELECT
    ID,
    MAX(CASE WHEN EntityName = 'AAP_DENOM' THEN Value END) AS AAP_DENOM,
    MAX(CASE WHEN EntityName = 'AAP_DENOM_PYCM' THEN Value END) AS AAP_DENOM_PYCM,
    MAX(CASE WHEN EntityName = 'AAP_DENOM_PYEM' THEN Value END) AS AAP_DENOM_PYEM,
    MAX(CASE WHEN EntityName = 'AAP_NUM' THEN Value END) AS AAP_NUM,
    MAX(CASE WHEN EntityName = 'AAP_NUM_PYEM' THEN Value END) AS AAP_NUM_PYEM,
    MAX(CASE WHEN EntityName = 'AGE' THEN Value END) AS AGE
FROM your_table
GROUP BY ID

我们可以通过Snowflake的JavaScript存储过程实现动态生成透视视图，无需手动指定EntityName。

解决方案：JavaScript存储过程实现动态透视 #

创建一个存储过程，自动读取源表中所有唯一的EntityName，动态生成并执行创建透视视图的SQL语句：

CREATE OR REPLACE PROCEDURE CREATE_PIVOTED_VIEW(source_table VARCHAR, target_view VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    // 1. 查询获取所有唯一的EntityName
    var entityNamesStmt = snowflake.execute({
        sqlText: `SELECT DISTINCT EntityName FROM ${SOURCE_TABLE}`
    });
    
    // 2. 拼接CASE语句部分
    var caseClauses = [];
    while (entityNamesStmt.next()) {
        var name = entityNamesStmt.getColumnValue(1);
        caseClauses.push(`MAX(CASE WHEN EntityName = '${name}' THEN Value END) AS ${name}`);
    }
    
    // 3. 拼接完整的CREATE VIEW语句
    var createViewSql = `
        CREATE OR REPLACE VIEW ${TARGET_VIEW} AS
        SELECT
            ID,
            ${caseClauses.join(',\n            ')}
        FROM ${SOURCE_TABLE}
        GROUP BY ID
    `;
    
    // 4. 执行SQL语句创建视图
    snowflake.execute({sqlText: createViewSql});
    
    return `透视视图 ${TARGET_VIEW} 已成功创建/更新，包含 ${caseClauses.length} 个EntityName字段`;
$$;

使用方法 #

1. 调用存储过程，传入源表名和目标视图名：

CALL CREATE_PIVOTED_VIEW('your_table', 'pivoted_view');

2. 查询生成的透视视图：

SELECT * FROM pivoted_view;

执行后会得到如下结果：

注意事项 #

• 当源表中的EntityName新增、删除或变更时，只需重新调用该存储过程即可更新透视视图
• 执行存储过程的用户需要具备：源表的SELECT权限、目标视图所在 schema 的CREATE VIEW权限
• 如果EntityName包含特殊字符（如空格、引号），需要在存储过程中添加转义逻辑，避免SQL注入风险

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