如何在Snowflake中解析分隔消息并生成对应列的新表？

核心思路 #

先把逗号分隔的消息拆分成键值对行，再通过动态透视将行转成列，自动包含所有出现过的列头。

具体步骤 #

1. 拆分消息为键值对行 #

假设原表名为TRANSACTIONS，包含TRANSACTION_ID（交易主键）和Message列，先拆分每个交易的消息项：

SELECT 
    TRANSACTION_ID,
    STRTOK(VALUE, '=', 1) AS COLUMN_NAME,
    STRTOK(VALUE, '=', 2) AS COLUMN_VALUE
FROM TRANSACTIONS,
LATERAL STRTOK_SPLIT_TO_TABLE(Message, ',')

• 这里用STRTOK_SPLIT_TO_TABLE把逗号分隔的消息拆成多行，再用STRTOK按=拆分列名和值（如果你的键值分隔符不是=，替换成实际符号即可）。

2. 动态生成全量列的透视表 #

因为列头是动态的，需要先收集所有唯一列名，再执行动态透视：

第一步：收集所有列名

SET ALL_COLUMNS = (
    SELECT LISTAGG(DISTINCT '"' || COLUMN_NAME || '"', ',') WITHIN GROUP (ORDER BY COLUMN_NAME)
    FROM (
        SELECT STRTOK(VALUE, '=', 1) AS COLUMN_NAME
        FROM TRANSACTIONS,
        LATERAL STRTOK_SPLIT_TO_TABLE(Message, ',')
    )
);

• 用LISTAGG拼接所有列名，加上双引号是为了兼容特殊列名（比如带数字开头的列名）。

第二步：执行动态透视

EXECUTE IMMEDIATE $$
CREATE OR REPLACE TABLE NEW_TRANSACTIONS AS
SELECT *
FROM (
    SELECT 
        TRANSACTION_ID,
        STRTOK(VALUE, '=', 1) AS COLUMN_NAME,
        STRTOK(VALUE, '=', 2) AS COLUMN_VALUE
    FROM TRANSACTIONS,
    LATERAL STRTOK_SPLIT_TO_TABLE(Message, ',')
)
PIVOT (
    MAX(COLUMN_VALUE) FOR COLUMN_NAME IN ($ALL_COLUMNS)
)
$$;

• 用MAX聚合是因为每个交易对应单个列名只有一个值，不会影响结果；没有对应值的交易列会显示为NULL。
• 这段代码直接生成新表NEW_TRANSACTIONS，包含所有交易的动态列。

额外说明 #

• 如果消息格式不是列名=值，比如是列名:值，修改STRTOK的分隔符参数即可。
• 若列名本身包含逗号或等号，需要先对消息做预处理（比如用其他特殊字符临时替换），避免拆分错误。

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