带有“Order Each by"子句的 Google BigQuery 大表(105M 记录)产生“Resources Exceeds Query Execution"；错误 [英] Google BigQuery large table (105M records) with 'Order Each by' clause produce "Resources Exceeds Query Execution" error

问题描述

当 Google Big Query 大表(105M 记录)带有Order Each by"子句时，我遇到了严重问题Resources Exceeds Query Execution".

I am running into Serious issue "Resources Exceeds Query Execution" when Google Big Query large table (105M records) with 'Order Each by' clause.

这是示例查询(使用公共数据集:维基百科):

Here is the sample query (which using public data set: Wikipedia):

SELECT Id,Title,Count(*) FROM [publicdata:samples.wikipedia] Group EACH by Id, title Order by Id, Title Desc

如何在不添加限制关键字的情况下解决此问题.

How to solve this without adding Limit keyword.

推荐答案

在大数据数据库上使用 order by 不是一个普通的操作，在某种程度上它超出了大数据资源的属性.您应该考虑在导出的数据中对查询进行分片或按顺序运行.

Using order by on big data databases is not an ordinary operation and at some point it exceeds the attributes of big data resources. You should consider sharding your query or run the order by in your exported data.

正如我今天在 您的其他问题，添加 allowLargeResults 将允许您返回大响应，但您不能指定顶级 ORDER BY、TOP 或 LIMIT 子句.这样做会抵消使用 allowLargeResults 的好处，因为查询输出不能再并行计算.

As I explained to you today in your other question, adding allowLargeResults will allow you to return large response, but you can't specify a top-level ORDER BY, TOP or LIMIT clause. Doing so negates the benefit of using allowLargeResults, because the query output can no longer be computed in parallel.

您可以尝试的一个选项是分片您的查询.

One option here that you may try is sharding your query.

where ABS(HASH(Id) % 4) = 0

您可以多次使用上述参数来实现更小的结果集，然后再进行组合.

You can play with the above parameters a lot to achieve smaller resultsets and then combining.

另请阅读第 9 章 - 了解查询执行 它解释了内部分片的工作原理.

Also read Chapter 9 - Understanding Query Execution it explaines how internally sharding works.

您还应该阅读BigQuery 的启动清单

这篇关于带有“Order Each by"子句的 Google BigQuery 大表(105M 记录)产生“Resources Exceeds Query Execution"；错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！