如何基于Snowflake中Flatten函数的输出转置至另一表？

核心思路 #

Snowflake原生PIVOT语法需要明确枚举透视列，但针对DynamoDB这类结构多变的导出数据，可通过动态SQL+键值聚合实现无硬编码的动态转置，具体步骤如下：

1. 提取所有唯一属性键 #

假设你flatten后的数据集结构如下（基于DynamoDB典型的键值对格式）：

WITH flattened_data AS (
    SELECT 
        record_id, -- 用于分组的唯一标识，比如DynamoDB主键
        f.key AS attr_key,
        f.value::variant AS attr_value
    FROM dynamodb_export_table,
         LATERAL FLATTEN(input => parse_json(item)) f -- item是DynamoDB导出的JSON字段
)
SELECT ARRAY_AGG(DISTINCT attr_key) AS all_keys FROM flattened_data;

这段查询会返回所有需要转置的属性键数组，例如['user_id', 'order_date', 'total_amount']。

2. 用动态SQL生成透视语句 #

通过Snowflake存储过程或脚本拼接动态透视逻辑，避免手动枚举所有键：

示例存储过程

CREATE OR REPLACE PROCEDURE dynamic_pivot_dynamodb_data()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    // 1. 获取所有唯一属性键
    const getKeysStmt = `
        WITH flattened_data AS (
            SELECT 
                record_id,
                f.key AS attr_key,
                f.value::variant AS attr_value
            FROM dynamodb_export_table,
                 LATERAL FLATTEN(input => parse_json(item)) f
        )
        SELECT ARRAY_AGG(DISTINCT attr_key) AS all_keys FROM flattened_data
    `;
    const keysResult = snowflake.execute({sqlText: getKeysStmt});
    keysResult.next();
    const allKeys = keysResult.getColumnValue(1);
    
    // 2. 拼接动态PIVOT SQL
    const pivotColumns = allKeys.map(key => `'${key}' AS "${key}"`).join(', ');
    const pivotSql = `
        WITH flattened_data AS (
            SELECT 
                record_id,
                f.key AS attr_key,
                f.value::variant AS attr_value
            FROM dynamodb_export_table,
                 LATERAL FLATTEN(input => parse_json(item)) f
        )
        SELECT *
        FROM flattened_data
        PIVOT (
            MAX(attr_value) FOR attr_key IN (${pivotColumns})
        ) AS p
        ORDER BY record_id;
    `;
    
    // 3. 执行动态SQL
    snowflake.execute({sqlText: pivotSql});
    return '动态转置完成，执行的SQL语句：\n' + pivotSql;
$$;

执行存储过程

CALL dynamic_pivot_dynamodb_data();

关键注意事项 #

• 数据类型兼容：DynamoDB属性值类型可能不一致，用variant类型存储转置后的列可避免类型冲突；若需特定类型，可在MAX(attr_value)后添加转换（如MAX(attr_value::number)），但需确保同键值的类型统一。
• 性能优化：数据量较大时，建议先将flatten结果物化（如创建临时表），再执行动态透视，减少重复计算。
• 空值处理：PIVOT默认会把不存在的键值显示为NULL，可通过NVL函数替换为业务所需的默认值。

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