从S3读取Avro数据至Snowflake时字段解析报错的解决咨询

问题场景 #

将S3中按YYYY/MM/DD文件夹结构存储的Avro格式数据读取到Snowflake，执行以下查询语句时触发报错：

select 
  $1 as data,
  substr(METADATA$FILENAME,31,10) as rpt_date,
  METADATA$FILENAME FILENAME,
  METADATA$FILE_LAST_MODIFIED,
  METADATA$START_SCAN_TIME,
  $1:"App Selected Coverage"::varchar as APP_SELECTED_COVERAGE,
  $1:"Prior_Term_POLICY_NUMBER"::varchar as PRIOR_TERM_POLICY_NUMBER
FROM @finance_analytics_stage  
limit 10

报错信息 #

100084 (22P02): Error parsing AVRO: bad record field: "App Selected Coverage" contains a character which is not alphanumeric or _

原因分析 #

Snowflake默认解析Avro字段时，要求字段名只能包含字母、数字和下划线。字段"App Selected Coverage"包含空格，不符合默认的字段名规则，导致解析失败。

解决方案 #

有两种方式可以解决这个问题：

方式一：修改查询语句的字段引用语法 #

对含特殊字符的字段，使用$1:["字段名"]的语法来引用，替换原有的$1:"字段名"格式：

select 
  $1 as data,
  substr(METADATA$FILENAME,31,10) as rpt_date,
  METADATA$FILENAME as FILENAME,
  METADATA$FILE_LAST_MODIFIED,
  METADATA$START_SCAN_TIME,
  $1:["App Selected Coverage"]::varchar as APP_SELECTED_COVERAGE,
  $1:"Prior_Term_POLICY_NUMBER"::varchar as PRIOR_TERM_POLICY_NUMBER
FROM @finance_analytics_stage  
limit 10

方式二：修改外部阶段的文件格式配置 #

创建或修改外部阶段时，指定AVRO_FIELD_NAME_POLICY = 'IGNORE_CASE_OR_SPECIAL_CHARS'，Snowflake会自动将字段名中的特殊字符（如空格）转换为下划线，后续查询可直接使用转换后的字段名：

修改阶段的SQL语句

CREATE OR REPLACE STAGE finance_analytics_stage
URL = 's3://your-bucket/your-path/'
FILE_FORMAT = (TYPE = AVRO AVRO_FIELD_NAME_POLICY = 'IGNORE_CASE_OR_SPECIAL_CHARS');

调整后的查询语句

select 
  $1 as data,
  substr(METADATA$FILENAME,31,10) as rpt_date,
  METADATA$FILENAME as FILENAME,
  METADATA$FILE_LAST_MODIFIED,
  METADATA$START_SCAN_TIME,
  $1:APP_SELECTED_COVERAGE::varchar as APP_SELECTED_COVERAGE,
  $1:PRIOR_TERM_POLICY_NUMBER::varchar as PRIOR_TERM_POLICY_NUMBER
FROM @finance_analytics_stage  
limit 10

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