Snowflake未分区文件夹下，能否基于文件列创建外部表分区？

在存储文件夹未分区的场景下，能否不依赖文件路径（metadata$filename），而是基于文件自身列在Snowflake中创建外部表分区？

我尝试创建如下外部表：

create or replace external table "db"."schema".EXT_TABLE (
    "year" NUMBER(38,0) AS YEAR(TO_TIMESTAMP((PARSE_JSON(VALUE:"c3"):"time":"$date"::varchar)))
    )
    partition by ("year")
    partition_type = user_specified 
    location=@stage
    file_format=file_format;

执行后返回错误：Function GET is not supported in an external table partition column expression.；我还尝试按照文档使用parse_json(metadata$external_table_partition)，但该字段始终返回空值，请问有可行的实现方法吗？

Snowflake的外部表无法直接基于文件内容字段定义用户指定分区，因为分区列的表达式仅允许引用元数据字段（如metadata$filename、metadata$file_row_number等）或常量，不能解析文件内容生成分区键。

针对你的场景，有两种可行的替代方案：

• 方案1：先映射外部表数据，再加载到分区内部表

  1. 创建无分区的外部表，仅用于映射文件内容：

  create or replace external table "db"."schema".EXT_TABLE_STAGING (
      "year" NUMBER(38,0) AS YEAR(TO_TIMESTAMP((PARSE_JSON(VALUE:"c3"):"time":"$date"::varchar))),
      "raw_data" variant as VALUE
  )
  location=@stage
  file_format=file_format;
  

  2. 创建带分区的内部表，从外部表加载数据：

  create or replace table "db"."schema".PARTITIONED_TABLE (
      "year" NUMBER(38,0),
      "raw_data" variant
  )
  partition by ("year");
  
  insert into "db"."schema".PARTITIONED_TABLE
  select "year", "raw_data" from "db"."schema".EXT_TABLE_STAGING;
  

  该方案适合数据更新频率较低的场景，能实现基于文件内容字段的分区存储。

• 方案2：Snowpipe+流+任务实现近实时分区加载
  如果需要近实时处理新增文件：

  1. 创建无分区的暂存内部表，通过Snowpipe自动加载外部存储的文件；
  2. 创建流监控暂存表的新增数据；
  3. 编写定时任务，将流中的数据按year字段合并到分区目标表中。

关于metadata$external_table_partition的说明 #

这个字段仅在外部表使用**内置分区（partition_type = native）**且存储路径符合分区格式（如year=2023/month=01/）时才会填充值。你的存储文件夹未分区，所以该字段为空是正常现象。

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