使用Flatten加载JSON至Snowflake时遇COPY/INSERT报错求解决方案

问题场景 #

要从Azure Stage加载如下嵌套JSON数据：

{
  "location": {
    "city": "Lexington",
    "zip": "40503"
  },
  "price": "75836",
  "sale_date": "4-25-16",
  "sq__ft": "1000"
}

已执行建表语句：

create or replace table property_sales(city varchar, zip string, price number, sale_date timestamp_ntz);

第一次尝试（COPY INTO） #

执行语句：

copy into property_sales(city, zip, price, sale_date, sqt_ft) 
from (select vm.value:city::string, vm.value:zip::number, $1:price::number, to_date($1.sale_date::text,'MM-DD-YY', $1.sq__ft::number) 
      from @json_stage, lateral flatten(input => $1:location) vm);

报错：

002098 (0A000): SQL compilation error:
COPY statement only supports simple SELECT from stage statements for import

第二次尝试（INSERT INTO） #

执行语句：

insert into property_sales(city, zip, price, sale_date, sqt_ft) 
select vm.value:city::string, vm.value:zip::number, $1:price::number, to_date($1.sale_date::text,'MM-DD-YY', $1.sq__ft::number) 
from @json_stage, lateral flatten(input => $1:location) vm;

报错：

ambiguous column name '$1'

解决方案 #

错误原因分析 #

1. COPY INTO报错原因：Snowflake的COPY INTO不支持包含LATERAL FLATTEN这类复杂转换的子查询，仅支持从stage直接读取的简单SELECT语句。
2. INSERT INTO报错原因：使用LATERAL FLATTEN后，$1会产生歧义——它既指代stage中的原始JSON数据，又可能被FLATTEN的结果集引用，导致Snowflake无法识别具体指向。

修正步骤 #

1. 修正表结构

原表property_sales缺少sqt_ft字段，先补充：

create or replace table property_sales(
    city varchar, 
    zip string, 
    price number, 
    sale_date timestamp_ntz,
    sqt_ft number
);

2. 使用别名消除$1歧义

给stage的源数据指定别名，明确引用原始JSON字段，同时修正to_date函数的参数错误（原语句多传入了sq__ft参数）：

insert into property_sales(city, zip, price, sale_date, sqt_ft)
select 
    t.$1:location.city::string,
    t.$1:location.zip::string,
    t.$1:price::number,
    to_date(t.$1:sale_date::text, 'MM-DD-YY'),
    t.$1:sq__ft::number
from @json_stage t;

（可选）如果必须使用FLATTEN的场景

如果你的JSON中location是数组类型（当前示例是对象，无需FLATTEN），可以用以下方式避免歧义：

insert into property_sales(city, zip, price, sale_date, sqt_ft)
select 
    vm.value:city::string,
    vm.value:zip::string,
    t.$1:price::number,
    to_date(t.$1:sale_date::text, 'MM-DD-YY'),
    t.$1:sq__ft::number
from @json_stage t,
lateral flatten(input => t.$1:location) vm;

说明 #

当前示例中的location是单个JSON对象，而非数组，因此无需使用FLATTEN，直接通过路径$1:location.city即可提取字段，这样既简化语句又避免了歧义问题。

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