Paginating BigQuery

Question

I am trying to create something similar to the Google BigQuery toolbar, except for the predefined queries / views. The problem I am facing is pagination of data.

tabledata endpoint supports pagination in which you can specify the starting index or use the page token, which allows me to do something like this:

query_reply = table_data_job.list(projectId=settings.PROJECT_ID, datasetId=settings.DATASET_ID, tableId=table, startIndex=offset, maxResults=page_size).execute() 

The problem is that I would like to run certain queries (or at least order the results of a table).

 query_data = {'query': 'SELECT * FROM my_dataset.foo_table LIMIT %s' % page_size} query_reply = job_collection.query(projectId=settings.PROJECT_ID, body=query_data).execute() 

As far as I know, there is no way to do the offset with the above code. Is it just something that BigQuery is not suitable? I assume that an alternative would be to do pagination in memory and work with smaller result sets?

Answer 1

BigQuery query results are tables. Thus, you can run the query and get the destination table from the result, and then execute the page through the results using the tabledata.list () api. Alternatively, you can get the job id from the response and use jobs.getQueryResults () , which has pagination support.

Answer 2

You can achieve pagination with SQL only with ROW_NUMBER ()

Here is a general template

 SELECT t.* FROM ( SELECT 1 AS one, [field], ROW_NUMBER() OVER(PARTITION BY one) AS rownum FROM [table] ) t WHERE rownum BETWEEN X AND Y