Resources exceeded during query execution in BigQuery with window function

[stuart1]

Moderator note: Split from "Resources exceeded during query execution" when building derived table in BigQuery.

I have a situation where I’m receiving a similar error using the ROW_NUMBER() function with OVER. However in my case, I am using both a PARTITION BY and an ORDER BY. And… I’m not sure if ROW_NUMBER() with OVER() is causing the problem.
From your explanation, it sounds like ORDER BY forces BigQuery to a single node… However I’m wondering, if you use PARTITION BY along with ORDER BY, will this allow BigQuery to use more than one node?

I appreciate any insight you may have on this.
Thanks,
Stuart

[izzymiller]

Regardless of partitioning, I believe ORDER BY is going to cause single-node execution in bigquery. I wonder if you could do the query in steps, in an incremental PDT or something:

How to: Simple incremental PDTs using create_process SQL & Derived Tables

Something we’ve been wanting to achieve in Looker for a while is the ability to incrementally build PDTs rather than doing a DROP and CREATE nightly. I think I’ve found a reasonable solution, using create_process. I’m sharing my findings here as I’ve seen a lot of topics trying to solve this issue, so hopefully this can help some people. The table I’m trying to increment is quite simple, I’m pulling rows from one table (that fit a criteria), modifying them a bit, then inserting them into anoth…