构建派生表时在查询执行期间超出了资源 [英] Resources exceeded during a query execution when building a derived table

问题描述

表结构:InvoiceLineKey(string)，
收入(浮动)，
COGS(浮动)，
Cost1(浮动)，
Cost2(浮动)，
Cost3(浮动)，
Cost4(浮动)，
Cost5(浮动)，
Proft(float)，
QSPEC(浮点数).

Table structure:. InvoiceLineKey(string),
Revenue(float),
COGS(float),
Cost1(float),
Cost2(float),
Cost3(float),
Cost4(float),
Cost5(float),
Proft(float),
QSPEC(float).

行数:60,000,000条记录

Number of rows : 60,000,000 records

选择
InvoiceLineKey，
收入，
齿轮，
费用1，
Cost2，
Cost3，
Cost4，
Cost5，
教授，
" AS QSPEC，
排名超过(按收入DESC排序)的AS排名，
累计(收入)超过(按收入DESC排序)的累计收入
来自
[pi-training:training.training_fact]
在哪里
收入> 0
订购
收入DESC
查询失败，并显示以下错误:

SELECT
InvoiceLineKey,
Revenue,
COGS,
Cost1,
Cost2,
Cost3,
Cost4,
Cost5,
Proft,
"" AS QSPEC,
RANK() OVER(ORDER BY Revenue DESC) AS Ranking,
SUM(Revenue) OVER(ORDER BY Revenue DESC) AS CumulativeRevenue
FROM
[pi-training:training.training_fact]
WHERE
Revenue > 0
ORDER BY
Revenue DESC
The query failed failed with the error:

查询执行期间超出了资源:无法查询在分配的内存中执行.ORDER BY运算子使用过多记忆..

Resources exceeded during query execution: The query could not be executed in the allotted memory. ORDER BY operator used too much memory..

推荐答案

假设 InvoiceLineKey 的大小约为8个字节(与浮点列相同)，6000万行表示结果大小约为4.8 GB.有关BigQuery管理查询输出"的文档解释说，在大型数据集上使用 ORDER BY 时，应包括 LIMIT 或过滤器.

Supposing that InvoiceLineKey is about 8 bytes in size (the same size as the floating point columns), 60 million rows means that the result size is about 4.8 gigabytes. The documentation on "Managing Query Outputs" for BigQuery explains that when using ORDER BY over a large dataset, you should include a LIMIT or a filter.

在您的情况下，假设大约十分之一的 Revenue 值大于10000，而这些都是您要返回的值，则可以使用 WHERE Revenue> = 10000以将要排序的行数限制为较小的数据子集.

In your case, supposing that about a tenth of Revenue values are greater than 10000, and these are the ones that you want to return, you could use WHERE Revenue >= 10000 to restrict the number of rows to sort to a smaller subset of the data.

这篇关于构建派生表时在查询执行期间超出了资源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！