1

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

Number of rows : 60,000,000 records

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:

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

Rabih Maatouk
  • 21
  • 1
  • 6
  • Can you include the exact error messages output? Or is "Resources exceeded during query execution" all you get? – Mark Stewart Nov 10 '17 at 17:22
  • Query Failed Error: Resources exceeded during query execution: The query could not be executed in the allotted memory. ORDER BY operator used too much memory.. – Rabih Maatouk Nov 10 '17 at 17:32
  • How many rows are in the table? Do you get the same error when using standard SQL? (I'm assuming you will, but it's worth checking). – Elliott Brossard Nov 10 '17 at 17:50
  • Welcome to Stack Overflow! I marked your code as code sample and included the error. But your question still needs to be more specific e.g. describe data you are processing. – IvanH Nov 10 '17 at 19:59
  • Yes same error with standard SQL . Table :60,000,000 records – Rabih Maatouk Nov 10 '17 at 22:06
  • Table structure , InvoiceLineKey(string), Revenue(float), COGS(float), Cost1(float), Cost2(float), Cost3(float), Cost4(float), Cost5(float), Proft(float), QSPEC(string) – Rabih Maatouk Nov 10 '17 at 22:08
  • It is much better to edit the question than add details to comments. Please try to imagine somebody trying to answer your question. The person needs to get all information quickly otherwise goes away. – IvanH Nov 10 '17 at 22:24
  • Added successfully, thank you – Rabih Maatouk Nov 10 '17 at 22:36

1 Answers1

3

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.

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.

Elliott Brossard
  • 32,095
  • 2
  • 67
  • 99