如何通过Snowflake查询生成数据字典？示例值获取异常求助

问题根源 #

原查询的example_values CTE依赖INFORMATION_SCHEMA.COLUMNS视图，这个视图仅存储表结构的元数据信息，不包含表的实际业务数据。你在子查询中调用COLUMN_NAME::VARCHAR，本质是把列名本身转换成字符串，而非读取该列在表中的实际值。

修正方案 #

要获取表中列的实际示例值，需要动态查询WORKDAY_SANDBOX schema下的每张表，提取对应列的样本数据。以下是可执行的分步方案：

步骤1：生成动态查询语句

执行以下SQL，生成用于提取所有列示例值的动态SQL：

SELECT LISTAGG(
    'SELECT ''' || TABLE_SCHEMA || ''' AS TABLE_SCHEMA, ''' || TABLE_NAME || ''' AS TABLE_NAME, ''' || COLUMN_NAME || ''' AS COLUMN_NAME, ' ||
    CASE 
        WHEN DATA_TYPE IN ('VARCHAR', 'TEXT', 'CHAR') THEN 'MAX(CAST(' || COLUMN_NAME || ' AS STRING)) AS Example_Value'
        WHEN DATA_TYPE IN ('NUMBER', 'FLOAT', 'DOUBLE', 'INT', 'BIGINT') THEN 'MAX(CAST(' || COLUMN_NAME || ' AS VARCHAR)) AS Example_Value'
        WHEN DATA_TYPE IN ('DATE', 'TIMESTAMP', 'TIMESTAMP_NTZ') THEN 'MAX(CAST(' || COLUMN_NAME || ' AS VARCHAR)) AS Example_Value'
        ELSE 'NULL AS Example_Value'
    END || ' FROM ' || TABLE_SCHEMA || '.' || TABLE_NAME || ' LIMIT 1'
, ' UNION ALL ') AS dynamic_sql
FROM DEV_RAW.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'WORKDAY_SANDBOX'
GROUP BY TABLE_SCHEMA, TABLE_NAME;

步骤2：执行动态SQL

复制步骤1输出的dynamic_sql内容，单独执行该SQL。执行完成后，记录返回结果的query_id（可通过Snowflake的查询历史或结果页面获取）。

步骤3：生成完整数据字典

将步骤2获取的query_id替换到以下SQL的<替换为第二步的query_id>位置，执行后即可得到包含实际示例值的数据字典：

WITH column_metadata AS (
    SELECT 
        TABLE_SCHEMA,
        TABLE_NAME,
        COLUMN_NAME,
        DATA_TYPE,
        IS_NULLABLE
    FROM DEV_RAW.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'WORKDAY_SANDBOX'
    ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
),
example_values AS (
    SELECT * FROM TABLE(RESULT_SCAN('<替换为第二步的query_id>'))
)
SELECT 
    cm.TABLE_SCHEMA,
    cm.TABLE_NAME,
    cm.COLUMN_NAME,
    cm.DATA_TYPE,
    cm.IS_NULLABLE,
    ev.Example_Value
FROM column_metadata cm
LEFT JOIN example_values ev
    ON cm.TABLE_SCHEMA = ev.TABLE_SCHEMA
    AND cm.TABLE_NAME = ev.TABLE_NAME
    AND cm.COLUMN_NAME = ev.COLUMN_NAME
ORDER BY cm.TABLE_SCHEMA, cm.TABLE_NAME, cm.COLUMN_NAME;

补充说明 #

• 使用MAX()聚合函数是为了过滤掉列中的NULL值，确保返回有效的示例值；如果需要随机样本，也可以替换为ANY_VALUE()或直接取TOP 1
• 针对DATE、TIMESTAMP等类型，已在CASE中补充了转换逻辑，可根据实际数据类型扩展
• 若表数据量极大，LIMIT 1可以大幅减少查询的计算资源消耗

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