since June 2nd we are having issues with analytic functions. when the query (not the partitions) passes a certain size the query fails with the following error:
Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 125% of limit. Top memory consumer(s): analytic OVER() clauses: 97% other/unattributed: 3% . at [....]
has anyone encountered the same problem?
BigQuery chooses several parallel workers for OVER() clauses based on the size of the tables being run upon. We can see resources exceeded error, when too much data are being processed by workers that BigQuery assigned to your query.
I assume that this issue could come from the OVER() clause and the amount of data used. You'll need to try to tune a bit on your query script (specially on the OVER() clauses) as it is said in the error message.
To read more about the error, take a look to the official documentation.
That's what would help is Slots - unit of computational capacity required to execute SQL queries:
When you enroll in a flat-rate pricing plan, you purchase a dedicated number of slots to use for query processing. You can specify the allocation of slots by location for all projects attached to that billing account. Contact your sales representative if you are interested in flat-rate pricing.
I hope you find the above pieces of information useful.
We were able ot overcome this limitation by splitting the original data into several shards and applying the analytical function on each shard.
In essence (for 8 shards):
WITH
t AS (
SELECT
RAND () AS __aux,
*
FROM <original table>
)
SELECT
* EXCEPT (__aux),
F () OVER (...) AS ...
FROM t
MOD (CAST (__aux*POW(10,9) AS INT64), 8) = 0
UNION ALL
....
SELECT
* EXCEPT (__aux),
F () OVER (...) AS ...
FROM t
MOD (CAST (__aux*POW(10,9) AS INT64), 8) = 7
