google-bigquery - BigQuery 整数分区 - 我可以使用另一个查询的结果来获取要访问的分区列表吗？

Question

我有一个使用整数分区（~1TB）的大表。我需要定期制作这张表的几个小子集。这花费了很多，但使用整数分区我可以将成本降低 95%。它看起来像这样。

tbl_a：partition_index IN (1, 2, 5, 6, 7, 10, 11, 15, 104, 106, 111)

tbl_b：partition_index IN (3, 4, 5, 20, 21, 25, 16, 84, 201, 301, 302, 303)

依此类推，不同的子表使用不同的索引子集。它丑得要命，但它确实有效。我担心如果我需要创建一个新的子表，这将难以维护，并且潜在的排列发生了变化，我必须编辑所有 .sql 文件以获取新的索引值集。我有一个小表，其中包含我想要的标准的所有不同排列，以及相关的索引值。使用实际子表选择标准对该索引查找表进行 5Kb 查询会产生一个索引值列表，如果将其复制并粘贴到 .sql 文件中，则可以保持一切正常运行。

但是，出于架构原因，我无法从子查询中提取索引值并将它们作为字符串插入到 .sql 文件中，然后再执行。我的意思是，我可以，而且会奏效。但它的hacky和糟糕且不合理的解决方案。但是，我找不到正确使用查找表上的小查询结果的方法。它总是导致全表扫描。这里有什么想法吗？

我想一个等效的问题是，如果我有一个在 customerID 上分区的大数据表，但我只有客户名称。BQ 似乎希望我查询名称查找表以获取 ID，然后使用 customerID 作为字符串文字提交第二个查询。我希望能够在单个查询中执行此操作。但我很难过。

标签： google-bigquery

Answer 1

让我重现你的问题。

SELECT MAX(views) max_views
FROM `fh-bigquery.wikipedia_v3.pageviews_2019` 
WHERE DATE(datehour) IN ('2019-03-27', '2019-04-10', '2019-05-10', '2019-10-10')
AND wiki='en'
AND title = 'Barbapapa'

已处理 1.4GB。

但是现在您有一张包含这些日期的表格：

CREATE TABLE temp.some_dates AS (
  SELECT * 
  FROM UNNEST([DATE('2019-03-27'), '2019-04-10', '2019-05-10', '2019-10-10']) date
);

现在我们将运行一个查询，从该表中取出值：

SELECT MAX(views) max_views
FROM `fh-bigquery.wikipedia_v3.pageviews_2019` 
WHERE DATE(datehour) IN (SELECT * FROM temp.some_dates)
AND wiki='en'
AND title = 'Barbapapa'

已处理 1.4 GB。

这里没问题：处理了相同数量的数据！为什么？这个表是聚集的，聚集你的表。

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

但是让我们看看那个表的 v2，如果事情没有聚集：

SELECT MAX(views) max_views
FROM `fh-bigquery.wikipedia_v2.pageviews_2019` 
WHERE DATE(datehour) IN ('2019-03-27', '2019-04-10', '2019-05-10', '2019-10-10')
AND wiki='en'
AND title = 'Barbapapa'

已处理 26.5 GB。这远远超过 1.4GB。如果我只对这张表进行聚类。

如果我们从不同的表中获取日期？

SELECT MAX(views) max_views
FROM `fh-bigquery.wikipedia_v2.pageviews_2019` 
WHERE DATE(datehour) IN (SELECT * FROM `temp.some_dates`)
AND wiki='en'
AND title = 'Barbapapa'

2.3 TB。

哇，那是一个非常大的表扫描。我应该聚集我的桌子。

但我能以某种方式解决这个问题吗？

是的：

DECLARE some_dates ARRAY<DATE> DEFAULT (SELECT ARRAY_AGG(date) FROM `temp.some_dates`);


SELECT MAX(views) max_views
FROM `fh-bigquery.wikipedia_v2.pageviews_2019` 
WHERE DATE(datehour) IN UNNEST(some_dates)
AND wiki='en'
AND title = 'Barbapapa'

已处理 26.46 GB。

不如聚簇表好，但至少我们使用了分区，这要归功于 BigQuery 中运行的脚本：首先声明一个变量，然后使用它的内容。

不过，我最好的建议是：将您的表聚集在一起。

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