代码之家 › 专栏 › 技术社区 › Felipe Hoffa

在BigQuery中决定何时对表进行分区有什么好的平衡?

database-cluster database-partitioning google-bigquery

0

Felipe Hoffa · 技术社区 · 6 年前

SELECT  sum(score) 
FROM `fh-bigquery.stackoverflow_archive.201906_posts_questions` 
WHERE creation_date > "2019-01-01"

相同,对于分区:

SELECT  sum(score) 
FROM `temp.questions_partitioned` 
WHERE creation_date > "2019-01-01"

耗时2秒,处理量为14.3 MB。

因此,我们看到MBs处理的好处,但是查询速度较慢。

什么是决定何时分区的好策略?

(来自我今天收到的一封电子邮件)

1 回复 | 直到 6 年前

1

18

Felipe Hoffa 6 年前

在对表进行分区时,需要考虑为每个分区提供足够的数据。把每个分区想象成一个不同的文件-打开365个文件可能比拥有一个大的文件慢。

在这种情况下,用于基准的表中有1.6GB的2019年数据(本表中截至6月)。即每个每日分区的1.6GB/180=9mb的数据。

对于如此低的数据量,将其安排在每日分区中不会带来太多好处。考虑按年对数据进行分区。请参阅以下问题了解如何操作:

Partition by week/month//quarter/year to get over the partition limit?

另一种选择是根本不分区表,而是使用集群按日期对数据进行排序。然后BigQuery可以选择每个块的理想大小。

如果您想运行自己的基准测试,请执行以下操作:

CREATE TABLE `temp.questions_partitioned`
PARTITION BY DATE(creation_date)
AS
SELECT *
FROM `fh-bigquery.stackoverflow_archive.201906_posts_questions`

CREATE TABLE `temp.questions_clustered`
PARTITION BY fake_date
CLUSTER BY creation_date
AS

SELECT *, DATE('2000-01-01') fake_date  
FROM `fh-bigquery.stackoverflow_archive.201906_posts_questions`

那么我对集群表的查询将是:

SELECT sum(score) 
FROM `temp.questions_clustered`
WHERE creation_date > "2019-01-01"

它花了0.5秒,处理了17MB。

分区:2秒,14.3 MB
群集:0.5秒,17 MB

查看这些表上每个查询的执行详细信息也很有趣:

占用的时隙时间

原始表:10.683秒
群集:0.718秒

如您所见,raw表上的查询使用了大量的槽(并行)来在1秒内得到结果。在这个例子中,50名工人用多年的数据处理了整个表,读取了1770万行数据。对分区表的查询必须使用大量的槽,但这是因为每个槽都被分配了较小的每日分区,这一读取占用了153个并行工作线程,超过了0.9M行。相反,集群查询能够使用非常少的插槽。数据组织良好,可供57名平行工作者阅读,读取112万行数据。