使用来自联接表的引用查询BigQuery中的分区表 [英] Querying a Partitioned table in BigQuery using a reference from a joined table

问题描述

我想运行一个查询，该查询使用表B中的值对表A进行分区.例如:

I would like to run a query that partitions table A using a value from table B. For example:

#standard SQL
select A.user_id
from my_project.xxx A
inner join my_project.yyy B
on A._partitiontime = timestamp(B.date)
where B.date = '2018-01-01'

此查询将扫描表A中的所有分区，并且不会考虑我在where子句中指定的日期(出于分区目的).我曾尝试以几种不同的方式运行此查询，但都产生了相同的结果-扫描表A中的所有分区.有什么办法解决吗?

This query will scan all the partitions in table A and will not take into consideration the date I specified in the where clause (for partitioning purposes). I have tried running this query in several different ways but all produced the same result - scanning all partitions in table A. Is there any way around it?

谢谢.

推荐答案

使用BigQuery

With BigQuery scripting (Beta now), there is a way to prune the partitions.

基本上，定义了脚本变量来捕获子查询的动态部分.然后在随后的查询中，脚本变量用作过滤器以修剪要扫描的分区.

Basically, a scripting variable is defined to capture the dynamic part of a subquery. Then in subsequent query, scripting variable is used as a filter to prune the partitions to be scanned.

DECLARE date_filter ARRAY<DATETIME> 
  DEFAULT (SELECT ARRAY_AGG(date) FROM B WHERE ...);

select A.user_id
from my_project.xxx A
inner join my_project.yyy B
on A._partitiontime = timestamp(B.date)
where A._partitiontime IN UNNEST(date_filter)

这篇关于使用来自联接表的引用查询BigQuery中的分区表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！