BigQuery日期分区视图 [英] BigQuery Date-Partitioned Views

问题描述

BigQuery允许您创建日期分区表：
https ：//cloud.google.com/bigquery/docs/creating-partitioned-tables

我希望能够在顶部创建视图日期分区表，理想情况下仍然可以获得性能优势。我读过的所有东西都表明这是不可能的？

有没有人有过其他方面的运气？

解决方案

定义您的视图以公开分区伪列，如下所示：

  SELECT *，EXTRACT （DATE FROM _PARTITIONTIME）AS日期
 FROM日期分区表; 
  

现在，如果您使用 date ，它会限制读取的分区。

BigQuery allows you to create date-partitioned tables: https://cloud.google.com/bigquery/docs/creating-partitioned-tables

I'd like to be able to create views on top of date-partitioned tables and ideally still reap the performance benefits. Everything I've read suggests that this isn't possible?

Has anyone had any luck otherwise?

解决方案

Define your view to expose the partitioning pseudocolumn, like this:

SELECT *, EXTRACT(DATE FROM _PARTITIONTIME) AS date
FROM Date partitioned table;

Now if you query the view using a filter on date, it will restrict the partitions that are read.

这篇关于BigQuery日期分区视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！