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

问题描述

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

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

$ b

  SELECT id，COUNT（DISTINCT field）
 FROM [my.dataset] 
 GROUP BY ID 
 LIMIT 10 
  

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

  SELECT id，COUNT（field）
 FROM [my.dataset] 
 GROUP BY ID 
 LIMIT 10 
  

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

解决方案

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

注意：COUNT DISTINCT在1000个值之后返回统计近似值 - 您可以通过选择特定值来改变近似值DISTINCT将返回一个近似值的限制，例如： COUNT（DISTINCT your_field，500）

请参阅： https://developers.google.com/bigquery/docs/query-reference#这种行为是由于BigQuery的设计造成的，它使得它非常快速：数据通过单独的节点查询，结果在混合器中汇总。 COUNT会统计结果的总数并合并答案，但COUNT DISTINCT需要跟踪潜在的数百万个单独的总和，然后再合并这些值。因此，COUNT DISTINCT可以创建大量数据，并可能超过每个节点的内部最大值。

还要注意，目前BigQuery LIMIT子句在整个结果集已确定。

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

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

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?

解决方案

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.

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)

See: https://developers.google.com/bigquery/docs/query-reference#aggfunctions

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.

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

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