使用来自联接表的引用查询BigQuery中的分区表

本文介绍了使用来自联接表的引用查询BigQuery中的分区表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想运行一个查询，该查询使用表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 脚本(现在是Beta版)，有一种方法可以修剪分区.

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)