CODEWITHSUNDEEP
google-bigquery
ewannic
ewannic

Reputation: 43

Window functions and allow large results

The window function documentation states that window functions cannot be used generate large query results:

https://developers.google.com/bigquery/query-reference#windowfunctions

This statement is repeated in the documentation for large query results:

https://developers.google.com/bigquery/querying-data#largequeryresults

I've created a query that uses window functions and creates a lot of results. The query can be found below for interest, it is run over the standard Google Analytics data extract into BigQuery.

When I run this query it returns a "Response too large to return" message. Specifying "Allow Large Results" seems to correct the problem. So I'm using both window functions and large results for this query.

This seems to be at odds with the statement that window functions can't be used to generate large query results. Can someone help me understand what this statement means?

SELECT 
CONCAT(fullVisitorId, STRING(visitId)) AS fullVisitID,
hits.hitNumber as Sequence,
hits.page.pagePath as PagePath,
LAG(Pagepath, 1) OVER 
  (PARTITION BY fullVisitID ORDER BY Sequence Asc) AS PrePage,
LEAD(Pagepath, 1) OVER 
  (PARTITION BY fullVisitID ORDER BY Sequence Asc) AS PostPage
FROM [<<TABLE NAME>>]
WHERE hits.type= 'PAGE'

Upvotes: 2

Views: 690

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

This is the product improving at a faster pace than documentation.

Initially window functions were not parallelizable, hence not compatible with "allow large results" (that works by paralleling the output). However BigQuery now is capable of parallelizing window function queries when they use the PARTITION keyword - hence that query now works.

Note that each partition can't be too big for this to work.

Upvotes: 1

Related Questions