Snowflake逆透视查询遇2024年12月日期触发无效标识符错误

我有一张包含User Name、User Email、Access Groups及多个日期列的表，想要将其转换为包含User Name、User Email、Access Groups、Date、Day of Week、Time的格式。但在Snowflake中执行UNPIVOT查询时，只要包含2024年12月的日期列（如"12/1/2024"）就会触发无效标识符错误，查询代码如下：

SELECT 
    "User Name", 
    "User Email", 
    "Access Groups", 
    TO_DATE(column_name, 'MM/DD/YYYY') AS "Date",
    DAYNAME(TO_DATE(column_name, 'MM/DD/YYYY')) AS "Day of Week",
    value AS "Time"
FROM sandbox.hr_analytics.boston_genea_attendance
UNPIVOT (
    value FOR column_name IN (
        "6/1/2024" .........
-- 无效标识符错误触发位置
        "12/1/2024", "12/2/2024", "12/3/2024", "12/4/2024", "12/5/2024", "12/6/2024", 
        "12/7/2024", "12/8/2024", "12/9/2024", "12/10/2024", "12/11/2024", "12/12/2024", "12/13/2024", 
        "12/14/2024", "12/15/2024", "12/16/2024", "12/17/2024", "12/18/2024", "12/19/2024", "12/20/2024", 
        "12/21/2024", "12/22/2024", "12/23/2024", "12/24/2024", "12/25/2024", "12/26/2024", "12/27/2024", 
        "12/28/2024", "12/29/2024", "12/30/2024", "12/31/2024"
    )
);

问题原因 #

报错的核心是你写的日期列名和表中实际存在的列名不匹配，常见情况包括：

• 日期格式不一致：表中实际列名是带前导零的"12/01/2024"，但你写的是"12/1/2024"
• 列名被自动修改：导入数据时，特殊字符（如/）可能被替换成下划线或其他符号
• 标识符规则不符：Snowflake对带双引号的标识符区分大小写，必须和表中列名完全一致

解决办法 #

1. 先确认真实的列名 #

执行以下查询获取表中所有12月日期列的准确名称：

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'HR_ANALYTICS'
  AND table_name = 'BOSTON_GENEA_ATTENDANCE'
  AND column_name LIKE '12/%/2024';

将查询返回的列名直接复制到UNPIVOT的IN子句中，确保完全匹配。

2. 用动态SQL自动生成UNPIVOT列 #

如果日期列数量多，手动输入容易出错，用动态SQL自动获取所有日期列并生成查询：

DECLARE
    date_cols STRING;
BEGIN
    SELECT LISTAGG('"' || column_name || '"', ', ')
    INTO date_cols
    FROM information_schema.columns
    WHERE table_schema = 'HR_ANALYTICS'
      AND table_name = 'BOSTON_GENEA_ATTENDANCE'
      AND column_name NOT IN ('User Name', 'User Email', 'Access Groups')
      AND TRY_TO_DATE(column_name, 'MM/DD/YYYY') IS NOT NULL;

    EXECUTE IMMEDIATE '
        SELECT 
            "User Name", 
            "User Email", 
            "Access Groups", 
            TO_DATE(column_name, ''MM/DD/YYYY'') AS "Date",
            DAYNAME(TO_DATE(column_name, ''MM/DD/YYYY'')) AS "Day of Week",
            value AS "Time"
        FROM sandbox.hr_analytics.boston_genea_attendance
        UNPIVOT (
            value FOR column_name IN (' || date_cols || ')
        )';
END;

这个脚本会自动筛选所有可转换为日期的列，避免手动输入的错误。

3. 检查列名的特殊字符处理 #

如果是数据导入时列名被修改（比如/变成了_），可以先查询所有列名排查：

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'HR_ANALYTICS'
  AND table_name = 'BOSTON_GENEA_ATTENDANCE';

确认后修改UNPIVOT中的列名即可。

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