如何将分号分隔的列名与值字符串转换为Snowflake中的结构化新表

该需求完全可以通过Snowflake原生功能实现，无需额外工具，具体操作如下：

实现思路 #

• 第一步：将每行分号分隔的列名、值列表按顺序拆分为一对一的键值对
• 第二步：提取所有出现过的唯一列名，动态生成透视SQL
• 第三步：执行动态SQL生成目标宽表，缺失值默认填充为NULL，可按需转换为NaN

具体实现代码 #

1. 测试样例准备（已有源表可直接跳过） #

-- 建测试源表
CREATE OR REPLACE TABLE RAW_LOGS (
    LOG_ID NUMBER,
    COLUMN_NAMES STRING,
    LOG_ENTRIES STRING
);

-- 插入示例数据
INSERT INTO RAW_LOGS VALUES
(1, 'TIMESTAMP;Sensor A;Sensor B', '2020-02-11 09:08:19;99.24;12.25'),
(2, 'TIMESTAMP;Sensor B;Sensor C', '2020-02-11 09:10:44;13.32;0.947');

2. 动态生成目标宽表脚本 #

DECLARE
    -- 存储所有唯一列的拼接字符串
    pivot_cols STRING;
    -- 存储最终执行的SQL
    final_sql STRING;
BEGIN
    -- 第一步：获取所有唯一列名，拼接为PIVOT所需的 "列名1","列名2",... 格式
    SELECT LISTAGG(DISTINCT '"' || TRIM(value) || '"', ',') 
    INTO pivot_cols
    FROM RAW_LOGS, LATERAL SPLIT_TO_TABLE(COLUMN_NAMES, ';');

    -- 第二步：拼接完整的透视建表SQL
    final_sql := '
    CREATE OR REPLACE TABLE PROCESSED_LOGS AS
    SELECT 
        LOG_ID,
        ' || pivot_cols || '
    FROM (
        SELECT
            l.LOG_ID,
            TRIM(cn.value) AS COLUMN_NAME,
            TRIM(le.value) AS COLUMN_VALUE
        FROM RAW_LOGS l
        ,LATERAL SPLIT_TO_TABLE(l.COLUMN_NAMES, '';'') WITH ORDINALITY cn
        ,LATERAL SPLIT_TO_TABLE(l.LOG_ENTRIES, '';'') WITH ORDINALITY le
        WHERE cn.INDEX = le.INDEX
    )
    PIVOT (
        MAX(COLUMN_VALUE)
        FOR COLUMN_NAME IN (' || pivot_cols || ')
    )
    ORDER BY LOG_ID;
    ';

    -- 执行SQL
    EXECUTE IMMEDIATE :final_sql;
END;
/

3. 缺失值转NaN处理 #

如果需要将默认的NULL替换为字符串NaN，可在查询时用IFNULL转换，示例：

SELECT 
    LOG_ID,
    IFNULL("TIMESTAMP", 'NaN') AS "TIMESTAMP",
    IFNULL("Sensor A", 'NaN') AS "Sensor A",
    IFNULL("Sensor B", 'NaN') AS "Sensor B",
    IFNULL("Sensor C", 'NaN') AS "Sensor C"
FROM PROCESSED_LOGS;

注意事项 #

• 同一LOG_ID下不会出现重复列名，因此PIVOT聚合函数用MAX/MIN均可，不会影响结果准确性
• 若需要数值类型的传感器值，可在拆分值时用TRY_CAST(TRIM(le.value) AS FLOAT)转换，转换失败的位置会自动为NULL
• 后续新增日志类型包含新列时，重新执行上述脚本即可自动更新目标表的列结构

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