Snowflake外部表异常：除Value列外其余列均显示NULL

问题描述 #

创建了CSV文件格式及外部表用于从Azure Blob Storage加载数据，查询EXT_DIM_TESTTABLE时，除Value列外所有列均显示NULL。Value列内的字段名为c1-c6（而非定义的COL1-COL6），但值正确。

提供的代码 #

文件格式定义 #

COMPRESSION = 'NONE'
FIELD_DELIMITER = ','
RECORD_DELIMITER = '\n'
SKIP_HEADER = 0
FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE'
EMPTY_FIELD_AS_NULL = FALSE
TRIM_SPACE = FALSE 
ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE 
ESCAPE = 'NONE'
ESCAPE_UNENCLOSED_FIELD = '\134' 
DATE_FORMAT = 'AUTO'
TIMESTAMP_FORMAT = 'AUTO'
NULL_IF = ('NULL');

外部表定义 #

CREATE OR REPLACE EXTERNAL TABLE EXT_DIM_TESTTABLE
(
  COL1                  VARCHAR (1000)          AS (value:"COL1"::string),
  COL2              VARCHAR (1000)          AS (value:"COL2"::string),
  COL3          VARCHAR (1000)          AS (value:"COL3"::string),
  COL4          VARCHAR (1000)          AS (value:"COL4"::string),
  COL5          VARCHAR (1000)          AS (value:"COL5"::string),
  COL6          VARCHAR (1000)          AS (value:"COL6"::string)
)
WITH 
LOCATION=@TESTSTAGE
AUTO_REFRESH = true
FILE_FORMAT = 'FILE_TESTFORMAT_CSV'
PATTERN='.*TEST_DATA.csv';

Value列内容示例 #

{
"c1": "TESTING",
"c2": "TESTING",
"c3": "TESTING",
"c4": "TESTING",
"c5": "TESTING",
"c6": "TESTING"
}

问题根源 #

1. 列映射不匹配：外部表定义中尝试映射value:"COL1"，但实际CSV解析后生成的字段名是c1-c6，找不到对应字段导致返回NULL。
2. 表头处理错误（可选）：如果CSV文件第一行是COL1-COL6的表头，当前SKIP_HEADER = 0会把表头当作数据行解析，Snowflake自动生成c1-c6作为默认字段名，而非识别表头为字段名。

解决方案 #

方案1：修正列映射字段名 #

直接将外部表的列映射改为实际的c1-c6：

CREATE OR REPLACE EXTERNAL TABLE EXT_DIM_TESTTABLE
(
  COL1 VARCHAR (1000) AS (value:"c1"::string),
  COL2 VARCHAR (1000) AS (value:"c2"::string),
  COL3 VARCHAR (1000) AS (value:"c3"::string),
  COL4 VARCHAR (1000) AS (value:"c4"::string),
  COL5 VARCHAR (1000) AS (value:"c5"::string),
  COL6 VARCHAR (1000) AS (value:"c6"::string)
)
WITH 
LOCATION=@TESTSTAGE
AUTO_REFRESH = true
FILE_FORMAT = 'FILE_TESTFORMAT_CSV'
PATTERN='.*TEST_DATA.csv';

方案2：正确处理CSV表头（如果文件带表头） #

如果CSV第一行是COL1-COL6的表头，修改文件格式的SKIP_HEADER参数为1，让Snowflake识别表头作为字段名：

-- 修改文件格式
ALTER FILE FORMAT FILE_TESTFORMAT_CSV SET SKIP_HEADER = 1;

-- 重新创建外部表（原定义可保持不变，此时字段名会自动识别为COL1-COL6）
CREATE OR REPLACE EXTERNAL TABLE EXT_DIM_TESTTABLE
(
  COL1 VARCHAR (1000) AS (value:"COL1"::string),
  COL2 VARCHAR (1000) AS (value:"COL2"::string),
  COL3 VARCHAR (1000) AS (value:"COL3"::string),
  COL4 VARCHAR (1000) AS (value:"COL4"::string),
  COL5 VARCHAR (1000) AS (value:"COL5"::string),
  COL6 VARCHAR (1000) AS (value:"COL6"::string)
)
WITH 
LOCATION=@TESTSTAGE
AUTO_REFRESH = true
FILE_FORMAT = 'FILE_TESTFORMAT_CSV'
PATTERN='.*TEST_DATA.csv';

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