按周/月//季度/年进行分区以超出分区限制? [英] Partition by week/month//quarter/year to get over the partition limit?

问题描述

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

I have 32 years of data that I want to put into a partitioned table. However BigQuery says that I'm going over the limit (4000 partitions).

对于类似这样的查询:

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

我遇到类似以下错误:

查询产生的分区太多，允许2000个，查询至少产生11384个分区

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

我如何超过此限制?

推荐答案

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

Instead of partitioning by day, you could partition by week/month/year.

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

In my case each year of data contains around ~3GB of data, so I'll get the most benefits from clustering if I partition by year.

为此，我将创建一个year日期列，并对其进行分区:

For this, I'll create a year date column, and partition by it:

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.

Note that I created the extra column DATE_TRUNC(FlightDate, YEAR) AS FlightDate_year in the process.

表格统计信息:

由于表是群集的，因此我将获得好处即使我不使用分区列(年份)作为过滤器也是如此

Since the table is clustered, I'll get the benefits of partitioning even if I don't use the partitioning column (year) as a filter:

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

这篇关于按周/月//季度/年进行分区以超出分区限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！