按周/月/季度/年分区以超过分区限制? [英] 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
我怎样才能突破这个限制?
How can I get over this limit?
推荐答案
你可以按周/月/年分区,而不是按天分区.
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屋!