Snowflake使用INFER_SCHEMA建外部表如何新增列并配置PARTITION BY

你当前写法的核心错误有两个：

• 错误在INFER_SCHEMA()函数内部直接写partition by (DATE_PART)，这个位置的partition参数仅用于识别路径中已经存在的Hive风格分区，不能引用尚未定义的计算列别名。
• USING TEMPLATE子查询必须返回Snowflake要求的固定schema字段：COLUMN_NAME、TYPE、EXPRESSION、FILENAMES、ORDER_ID，仅查询expression、filenames无法正确生成表结构。

通过UNION ALL把INFER_SCHEMA识别到的源文件原生列，和你自定义的date分区列元数据拼接，作为TEMPLATE的输入即可，完整代码如下：

create or replace external table people_test1
  -- 外部表层面指定分区键
  partition by (date_part)
  using template (
    -- 第一部分：取源Parquet文件的所有原生列元数据
    select 
      column_name,
      type,
      expression,
      filenames,
      order_id
    from table(
      infer_schema(
        location=>'@ITCHBOD.COMPANY_STAGE/cre/invets/',
        file_format=>'CRUPARQUET'
      )
    )
    union all
    -- 第二部分：追加自定义的date_part分区列定义
    select
      'DATE_PART' as column_name,
      'DATE' as type,
      -- 这里写分区列的计算逻辑，直接调用外部表内置的METADATA$FILENAME字段截取
      'substr(metadata$filename, 24, 10)::date' as expression,
      null as filenames,
      -- 分区列排序在所有原生列之后，取原生列最大order_id+1避免顺序冲突
      (select max(order_id)+1 from table(infer_schema(
          location=>'@ITCHBOD.COMPANY_STAGE/cre/invets/',
          file_format=>'CRUPARQUET'
      ))) as order_id
  )

• 如果你的S3存储路径本身是Hive分区风格（即路径格式为.../date_part=2024-01-01/xxx.parquet），不需要手动拼接列，只需要在INFER_SCHEMA中增加参数PARTITION_BY => 'DATE_PART'即可自动识别分区列。
• 如果分区值是从文件名非固定路径段截取的，就使用上述UNION拼接自定义列的方式实现。

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