google-bigquery - 决定何时在 Bi​​gQuery 中对表进行分区时，有什么好的平衡点？

Question

我们正在使用公共(public)数据集对 BigQuery 进行基准测试。我们使用同一张表并按天对它进行分区，但不清楚我们是否获得了很多好处。什么是好的平衡？

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

耗时 1 秒，处理 270.7MB。

同样，分区:

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

需要 2 秒并处理 14.3 MB。

因此，我们看到了处理 MB 的好处，但查询速度较慢。

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

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

Answer 1

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

在这种情况下，用于基准测试的表有 1.6 GB 的 2019 年数据(截至 6 月)。每个每日分区有 1.6GB/180 = 9 MB 的数据。

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

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

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

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

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 秒，处理了 17 MB。

比较的:

原始表:1 秒，270.7MB

分区:2 秒，14.3 MB

集群:0.5 秒，17 MB

我们有赢家!与严格按天进行分区相比，集群将每日数据(对于该表来说并不多)组织成更有效的 block 。

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

槽时间消耗

原始表:10.683 秒

分区:7.308 秒

集群:0.718 秒

如您所见，对原始表的查询使用了很多槽(并行性)以在 1 秒内获得结果。在这种情况下，50 名工作人员处理了包含多年数据的整个表，读取了 1770 万行。对分区表的查询必须使用很多槽 - 但这是因为每个槽都分配了较小的每日分区，读取使用了 0.9M 行的 153 个并行工作人员。相反，集群查询能够使用非常少量的插槽。数据组织良好，可被 57 个并行工作人员读取，读取 112 万行。







也可以看看:

https://medium.com/google-cloud/bigquery-optimized-cluster-your-tables-65e2f684594b

How can I improve the amount of data queried with a partitioned+clustered table?

how clustering works in BigQuery