Azure 数据资源管理器外部表分区有什么用处？

发布于 2025-01-10 04:53:56 字数 1122 浏览 2 评论 0原文

将权限添加到外部表定义对分区查询没有帮助。

Blob 路径示例

/data/1234/2021/12/02/9483D.parquet
/data/1235/2021/12/02/12345.parquet

分区（伪语法不是真正的语法）： '/data/'uniqueid'/yyyy /MM/dd/'

因此存储路径中只有两个 uniqueid 值。路径中不同日期的文件总数约为 100 万个

所以我定义了 2 个分区作为虚拟列：

uniqueid
datetime

对 uniqueid 执行查询，如下所示： <代码>表|通过uniqueid总结由于某种原因会检查 blob 存储中的所有文件。

由于 uniqueid 是一个分区并且作为虚拟列，因此查询不应该超级快吗，因为我们在它的路径中只有 2 个值？我完全错过了分区的要点吗？

编辑添加样本：

.create external table ['sensordata'] (['timestamp']:long,['value']:real)
    kind = adl
partition by (['uniqueid']:string ,['datecreated']:datetime )
pathformat = (['uniqueid']  '/' datetime_pattern("yyyy/MM/dd", ['daterecorded']))
    dataformat = parquet
    (
        h@'abfss://[email protected]/histdata;impersonate'
    )
    with (FileExtension='.parquet')

查询样本：

sensordata
| summarize by uniqueid

原文

Adding pertition to the external table definition does not help with a query on the partition.

Blob path example

/data/1234/2021/12/02/9483D.parquet
/data/1235/2021/12/02/12345.parquet

Partition (pseudo syntax not the real one) : '/data/'uniqueid'/yyyy/MM/dd/'

So only two uniqueids values are in the storage path. Total files count ~ 1 million for different dates in the path

So I defined 2 partitions as virtual columns:

uniqueid
datetime

Executing a query on the uniqueid like:
table | summarize by uniqueid
goes over all files in the blob storage for some reason.

As the uniqueid is a partition and as virtual column, shouldn't the query be super fast as we have only 2 values in the path for it?
Am I totally missing the point of partitioning?

EDIT add smaple:

.create external table ['sensordata'] (['timestamp']:long,['value']:real)
    kind = adl
partition by (['uniqueid']:string ,['datecreated']:datetime )
pathformat = (['uniqueid']  '/' datetime_pattern("yyyy/MM/dd", ['daterecorded']))
    dataformat = parquet
    (
        h@'abfss://[email protected]/histdata;impersonate'
    )
    with (FileExtension='.parquet')

Query sample: