google-bigquery - 在 bigquery 中对 _PARTITIONTIME 使用子查询不会限制成本

Question

当我使用标准 SQL 在 BQ 上运行以下查询时，它说它将在运行时处理 76.6TB

SELECT 
event_time, user_id, activity_id,dbm_insertion_order_id, dbm_total_media_cost_usd 
FROM `raw.5295.activity_*`
WHERE _PARTITIONTIME >(SELECT * FROM `analytics-dwh.autobidding.activity_list` )
AND timestamp_micros(event_time)  > (SELECT timestamp_micros(MAX(event_time)) from `essence-analytics-dwh.ml_for_autobidding.nest_na_4q18_activity_updated_daily`)
AND _TABLE_SUFFIX IN ('25','20')

表 analytics-dwh.autobidding.activity_list 只有一列包含唯一的整数列表

如果我从上表中删除子查询，查询在运行时使用不到 500GB

SELECT 
event_time, user_id, activity_id,dbm_insertion_order_id, dbm_total_media_cost_usd 
FROM `raw.5295.activity_*`
WHERE _PARTITIONTIME >TIMESTAMP('2018-12-20')
AND timestamp_micros(event_time)  > (SELECT timestamp_micros(MAX(event_time)) from `essence-analytics-dwh.ml_for_autobidding.nest_na_4q18_activity_updated_daily`)
AND _TABLE_SUFFIX IN ('25','20')

为什么在我使用子查询时会发生这种情况？有解决方法吗？

Answer 1

Why does this happen with use of subquery?

一般来说，如果可以在查询开始时评估过滤器而不需要任何子查询评估或数据扫描，分区修剪将降低查询成本。

你可以看到更多关于Limiting partitions queried using pseudo columns的信息

因此，在您的第一个查询中(您使用子查询的地方)- 修剪没有发生(它不会根据涉及子查询的条件限制分区的使用)

在第二个查询中，您使用 _PARTITIONTIME >TIMESTAMP('2018-12-20') 因此分区是有限的

底线:_PARTITIONTIME 上包含子查询的过滤器不能用于限制为分区表扫描的分区数。

Is there a workaround?

您应该将任务分为两个步骤:使用您需要的任何逻辑从表 analytics-dwh.autobidding.activity_list 计算 _PARTITIONTIME 的过滤器，然后使用它(而不是子查询)- 使用任何client您的选择