google-bigquery - 按周/月/季度/年分区以超过分区限制？

Question

我有 32 年的数据要放入分区表中。但是 BigQuery 说我超过了限制(4000 个分区)。

对于像这样的查询:

CREATE TABLE `deleting.day_partition`
PARTITION BY FlightDate 
AS 
SELECT *
FROM `flights.original` 

我收到如下错误:

Too many partitions produced by query, allowed 2000, query produces at least 11384 partitions

我怎样才能克服这个限制？

Answer 1

您可以按周/月/年进行分区，而不是按天分区。

就我而言，每年的数据都包含大约 3GB 的数据，因此如果按年进行分区，我将从聚类中获得最大的好处。

为此，我将创建一个 year日期列，并按它分区:

CREATE TABLE `fh-bigquery.flights.ontime_201903`
PARTITION BY FlightDate_year
CLUSTER BY Origin, Dest 
AS
SELECT *, DATE_TRUNC(FlightDate, YEAR) FlightDate_year
FROM `fh-bigquery.flights.raw_load_fixed`

请注意，我创建了额外的列 DATE_TRUNC(FlightDate, YEAR) AS FlightDate_year正在进行中。

表统计:



Since the table is clustered, I'll get the benefits of partitioning即使我不使用分区列(年份)作为过滤器:

SELECT *
FROM `fh-bigquery.flights.ontime_201903`
WHERE FlightDate BETWEEN '2008-01-01' AND '2008-01-10'

Predicted cost: 83.4 GB
Actual cost: 3.2 GB