BigQuery 错误“响应太大而无法返回"使用 COUNT(DISTINCT ...) 时 [英] BigQuery error "Response too large to return" when using COUNT(DISTINCT ...)

问题描述

我有一个大约有 20M 行的数据集，我正在观察以下行为.

I have a dataset with ~20M rows and I'm observing the following behavior.

下面的查询返回错误响应太大而无法返回".'id' 字段在多个记录之间共享，并且 'field' 字段对每条记录都有一些任意值.我希望结果集应该只包含 10 行，远低于查询响应限制.

The query below returns the error "Response too large to return". The 'id' field is shared among multiple records and the 'field' field has some arbitrary value for each record. I would expect that the result set should only contain 10 rows, well below the query response limit.

SELECT id, COUNT(DISTINCT field)
FROM [my.dataset]
GROUP BY id
LIMIT 10

但是，当从 COUNT 聚合函数中删除 DISTINCT 关键字时，BigQuery 会按预期返回 10 个结果.

However, when the DISTINCT keyword is removed from the COUNT aggregation function, BigQuery returns 10 results as expected.

SELECT id, COUNT(field)
FROM [my.dataset]
GROUP BY id
LIMIT 10

我不明白为什么第一个查询返回错误而第二个查询成功完成.两个查询不应该返回相同数量的行吗?

I don't understand why the first query returns an error and the second completes successfully. Shouldn't both queries return the same number of rows?

推荐答案

导致此响应的不是结果大小，而是由 COUNT DISTINCT 查询生成的数据的中间大小.

It's not the result size that is causing this response, it's the intermediate size of the data generated by your COUNT DISTINCT query.

注意:COUNT DISTINCT 在 1000 个值后返回统计近似值 - 您可以通过为 DISTINCT 将返回近似值的限制选择特定值来更改近似值.. 例如:COUNT(DISTINCT your_field, 500)

Note: COUNT DISTINCT returns a statistical approximation after 1000 values - you can alter the approximation by choosing a particular value for the limit in which DISTINCT will return an approximation.. such as: COUNT(DISTINCT your_field, 500)

请参阅:https://developers.google.com/bigquery/docs/查询参考#aggfunctions

这种行为是由于 BigQuery 的设计，使其如此快速:通过单独的节点查询数据，结果在混合器中聚合.COUNT 将计算结果的总数并合并答案，但 COUNT DISTINCT 需要跟踪潜在的数百万个单独的总和，然后再合并这些值.因此，一个 COUNT DISTINCT 可以创建大量数据，并且可能会超过单个节点的内部最大值.

This behavior is due to the design of BigQuery, which makes it so quick: data is queried via separate nodes, and results are aggregated at mixers. A COUNT will tally the total number of results and combine the answer, but COUNT DISTINCT needs to keep track of potentially millions of separate sums, and then combine those values later. Therefore a COUNT DISTINCT can create a lot of data, and could potentially be over internal maximum for individual nodes.

另请注意，目前，BigQuery LIMIT 子句是在确定整个结果集后应用的.

Note also that currently, BigQuery LIMIT clauses are applied after the entire result set is determined.

这篇关于BigQuery 错误“响应太大而无法返回"使用 COUNT(DISTINCT ...) 时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！