How to implement Pagination in BigQuery Resultsets

Quite often we want to convert the Bigquery resultsets to multiple pages and return page by page. Here are some options:

Method #1 Using ORDER BY and OFFSET

This method works well for small resultsets. You can simply provide the start row number (offset) and bigquery will only display from that row

Example:

To show from the 6 row onwards from the results:

SELECT * FROM `project.Dataset.Table` ORDER BY MY_DATE LIMIT 10 OFFSET 5

Method #2 Using ROW_NUMBER to create Pagination

Use ROW_NUMBER to assign a incremental number to each row and then filter on that column to create pages

SELECT MY_DATE,ROW_NUMBER() OVER (ORDER BY MY_DATE) AS RN 
FROM `project.dataset.table` 

NOTE: ROW_NUMBER is also resource intensive operation and requires all the rows to be processed in one slot (compute node), so you may encounter "Resource Exceeded" error for large datasets

dan-irving

posted on 16 Apr 19