Snowflake存储过程适配CSV列调整及‘O型数据类型’报错排查

当你用SELECT * FROM @stage LIMIT 1创建临时表时，Snowflake如果无法从单行CSV数据中明确推断列类型（比如空值、特殊格式值），会将列标记为OTHER（缩写为'O'）。这个类型仅适用于临时查询场景，不能用于创建持久化表，因此触发"Unsupported data type 'O'"错误。

以下两种方案均可解决问题，优先推荐方案一：

方案一：强制临时表所有列类型为STRING（推荐） #

在创建临时表的FILE_FORMAT参数中添加FORCE_CHARACTER_STRING = TRUE，强制Snowflake将所有CSV列识别为STRING类型，从根源避免'O'类型的产生。

修改后的完整存储过程代码：

CREATE OR REPLACE PROCEDURE create_table_from_csv(file_name STRING, target_table STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    // Step 1: 创建临时表，强制所有列类型为STRING
    var createTempStmt = snowflake.createStatement({
        sqlText: `CREATE OR REPLACE TEMPORARY TABLE temp_table AS
                   SELECT * FROM @csv_file_stage/(:file_name)
                   FILE_FORMAT = (FORMAT_NAME = 'csv_format', FORCE_CHARACTER_STRING = TRUE)
                   LIMIT 1;`,
        binds: { file_name: file_name }
    });
    createTempStmt.execute();

    // Step 2: 基于临时表结构生成正式表创建语句
    var createTableSQL = snowflake.createStatement({
        sqlText: `SELECT 'CREATE OR REPLACE TABLE ' || :target_table || ' (' || 
                         LISTAGG(column_definition, ', ') || 
                         ');'
                  FROM (
                      SELECT COLUMN_NAME || ' ' || DATA_TYPE AS column_definition
                      FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE TABLE_NAME = 'TEMP_TABLE'
                      ORDER BY ORDINAL_POSITION
                  )`,
        binds: { target_table: target_table }
    }).getSingleRow()[0];

    // 执行正式表创建语句
    snowflake.execute(createTableSQL);

    // Step 3: 加载CSV数据到目标表
    var copyStmt = snowflake.createStatement({
        sqlText: `COPY INTO :target_table
                   FROM @csv_file_stage/(:file_name)
                   FILE_FORMAT = (FORMAT_NAME = 'csv_format');`,
        binds: { target_table: target_table, file_name: file_name }
    });
    copyStmt.execute();

    return 'Table ' + target_table + ' created and data loaded successfully from ' + file_name;
$$;

CALL create_table_from_csv('your_file.csv','your_target_table');

方案二：自动替换无效的'O'类型为STRING #

如果无法修改FILE_FORMAT配置，可在生成正式表创建语句时，将DATA_TYPE中的'O'替换为'STRING'，确保SQL语句合法。

仅需修改Step2的查询语句：

var createTableSQL = snowflake.createStatement({
    sqlText: `SELECT 'CREATE OR REPLACE TABLE ' || :target_table || ' (' || 
                     LISTAGG(column_definition, ', ') || 
                     ');'
              FROM (
                  SELECT COLUMN_NAME || ' ' || REPLACE(DATA_TYPE, 'O', 'STRING') AS column_definition
                  FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_NAME = 'TEMP_TABLE'
                  ORDER BY ORDINAL_POSITION
              )`,
    binds: { target_table: target_table }
}).getSingleRow()[0];

上述代码已优化了SQL注入风险：原本直接拼接file_name和target_table的写法存在注入隐患，现在改用Snowflake绑定变量（:变量名）的方式处理参数，提升安全性。

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