如何在Azure Synapse无服务器池中创建类似Hive的分区外部表

Synapse无服务器SQL池完全支持和Hive逻辑一致的分区外部表能力，根据你的存储路径格式可以选择两种实现方式：

方式一：自动分区发现（适配Hive风格路径） #

如果你的存储目录命名符合Hive分区路径规范（格式为/父路径/分区键=分区值/），可以通过自动扫描实现批量分区加载：

1. 建分区外部表

CREATE EXTERNAL TABLE [testdb].[test1]
(
    [STUDYID] varchar(2000),
    [SITEID] varchar(2000)
)
WITH
(
    LOCATION = '/<abc_location>/csv/archive/',
    DATA_SOURCE = [datalake],
    FILE_FORMAT = [csv_comma_values],
    PARTITIONED_BY = N'[{"name": "dept", "type": "varchar(100)"}]'
)

2. 执行分区刷新，自动识别所有符合规范的子目录为分区

EXEC sys.sp_refresh_external_table_partitions N'[testdb].[test1]';

方式二：手动添加分区（适配任意分散路径） #

如果你的文件夹没有遵循Hive命名规范，或者文件分散在完全不同的存储路径下，可以使用和Hive逻辑完全一致的手动添加分区语法：

1. 先按上述方式完成带分区字段的外部表创建
2. 逐个添加分区，支持绑定任意存储路径

ALTER TABLE [testdb].[test1] ADD PARTITION (dept='dept1') LOCATION '/<abc_location>/csv/archive/dept1/';
ALTER TABLE [testdb].[test1] ADD PARTITION (dept='dept2') LOCATION '/<abc_location>/csv/otherpath/dept2/';
ALTER TABLE [testdb].[test1] ADD PARTITION (dept='dept3') LOCATION '/<其他自定义存储路径/dept3/';

补充说明 #

• 分区字段不需要定义在表的基础字段列表中，逻辑和Hive完全一致
• 查询时添加分区条件会自动跳过无关路径扫描，大幅提升查询性能，示例查询语句：

  SELECT * FROM [testdb].[test1] WHERE dept = 'dept1'
  

• 如需删除无效分区可执行：

  ALTER TABLE [testdb].[test1] DROP PARTITION (dept='dept1')
  

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