Azure Synapse无服务器SQL外部表按年月日分区创建指导

错误分析 #

你之前的创建语句存在两个核心问题：

• LOCATION路径不应包含year=*/month=*/day=*这类分区占位符，Synapse会自动识别路径中的分区键值对，只需指定分区根目录即可
• 分区列year、month、day不需要包含在表的字段定义中，它们属于分区元数据，而非数据文件内的字段

正确创建语句 #

假设你已提前创建好对应的数据源[datasource]和Parquet文件格式[fileformat]，正确的创建语句如下：

CREATE EXTERNAL TABLE [dbo].[my_partitioned_table]
(
    [UTCDate] DATE,
    [UTCHour] INT,
    [LocalDate] DATE,
    [LocalHour] INT,
    [value] FLOAT
)
WITH (
    LOCATION = '/path/', -- 仅指向分区根目录，不要带分区占位符
    DATA_SOURCE = [datasource],
    FILE_FORMAT = [fileformat]
)
PARTITION BY (year INT, month INT, day INT); -- 分区列单独声明，无需在表字段中定义

加载分区元数据 #

创建表后，Synapse不会自动识别已存在的分区，需执行以下命令刷新分区：

MSCK REPAIR TABLE [dbo].[my_partitioned_table];

这条命令会扫描根目录下所有符合year=*/month=*/day=*结构的路径，将分区信息同步到表的元数据中。

验证查询 #

现在可以使用你提供的示例查询语句，Synapse会自动根据分区过滤，只扫描符合条件的路径下的Parquet文件，避免全量扫描：

SELECT * FROM my_partitioned_table
WHERE year BETWEEN 2023 AND 2024
AND month BETWEEN 1 AND 2
AND day BETWEEN 5 AND 6

注意事项 #

• 确保数据源[datasource]配置正确，指向存储账户中包含分区数据的容器
• 文件格式[fileformat]需配置为PARQUET类型，且与你的数据文件格式匹配
• 后续新增分区后，需再次执行MSCK REPAIR TABLE命令同步元数据

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