Snowflake创建S3 Parquet外部表报分区列表达式无效问题

• 分区列表达式报错的核心原因：PARTITION_TYPE = USER_SPECIFIED模式下，分区列的计算逻辑不能引用代表Parquet文件内容的value对象。原DDL中写的value:"partition_0"是试图从Parquet文件内部读取partition_0字段，但该字段是路径层级的虚拟占位，并不存在于Parquet文件内容中，因此会触发表达式无效报错。
• 仅用partition_key作为分区列时查询为空的核心原因：USER_SPECIFIED分区模式不会自动识别、注册存储路径下的分区，即使开启auto_refresh = true也不会自动同步分区元数据，必须手动执行分区注册语句才能让Snowflake识别对应路径下的文件。未注册分区的文件不会被外部表扫描，因此返回空结果。

优先使用Snowflake自动分区模式，适配按小时动态新增的分层级Parquet数据集，无需手动维护分区，auto_refresh配置可正常生效，正确DDL如下：

CREATE OR REPLACE EXTERNAL TABLE abc (
    "year" INT AS (split_part(METADATA$EXTERNAL_TABLE_PARTITION, '/', 1)::INT),
    "month" INT AS (split_part(METADATA$EXTERNAL_TABLE_PARTITION, '/', 2)::INT),
    "day" INT AS (split_part(METADATA$EXTERNAL_TABLE_PARTITION, '/', 3)::INT),
    "hour" INT AS (split_part(METADATA$EXTERNAL_TABLE_PARTITION, '/', 4)::INT)
    -- 其余Parquet文件内的业务字段可按如下格式追加定义：
    -- "字段名" 字段类型 AS (value:"parquet中对应字段名"::字段类型)
)
PARTITION BY ("year", "month", "day", "hour")
-- 替换为Snowflake自动管理分区模式
PARTITION_TYPE = SNOWFLAKE_FULL
WITH LOCATION = @abc
AUTO_REFRESH = TRUE
FILE_FORMAT = (TYPE = PARQUET);

说明：如果你的S3存储路径是Hive风格的键值对格式（即路径形如year=2024/month=05/day=01/hour=00/），可以直接将分区列定义为"year" INT AS (value:"year"::INT)格式，无需使用split_part拆分路径。

如果因为特殊场景必须使用USER_SPECIFIED分区模式，除了参照上面的逻辑修正分区列的取值表达式（不要从value中取路径字段，改用METADATA$EXTERNAL_TABLE_PARTITION拆分），建表完成后还需要手动注册每一个存在的分区，示例语句如下：

ALTER EXTERNAL TABLE abc 
ADD PARTITION ("year"=2024, "month"=5, "day"=1, "hour"=0) 
LOCATION '2024/05/01/00/';

该方式需要持续手动同步新增分区，不适用于按小时滚动产出的时间分区数据，无特殊需求不推荐使用。

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