Read data with BigQuery API using pagination
This document describes how to read table data and query results with the BigQuery API using pagination.
Page through results using the API
All *collection*.list
methods return paginated results
under certain circumstances. The maxResults
property limits the number of results per page.
Method | Pagination criteria | Default maxResults value |
Maximum maxResults value |
Maximum maxFieldValues value |
---|---|---|---|---|
tabledata.list |
Returns paginated results if the response size is more than
10 MB1 of data or more than maxResults
rows. |
Unlimited | Unlimited | Unlimited |
All other *collection*.list methods |
Returns paginated results if the response is more than
maxResults rows and also less than the maximum limits. |
10,000 | Unlimited | 300,000 |
If the result is larger than the byte or field limit, the result is
trimmed to fit the limit. If one row is greater than the byte or field limit,
tabledata.list
can return up to 100 MB of data1,
which is consistent with the maximum row size limit for query results.
There is no minimum size per page, and some pages might return more rows than others.
1The row size is approximate, as the size is based on the internal representation of row data. The maximum row size limit is enforced during certain stages of query job execution.
jobs.getQueryResults
can return 20 MB of data unless explicitly
requested more through support.
A page is a subset of the total number of rows. If your results are more
than one page of data, the result data has a pageToken
property. To retrieve the next page of results, make another list
call and include the token value as a URL parameter named pageToken
.
The tabledata.list
method, which is used to page through table data, uses a row offset value or a
page token. See Browsing table data
for information.
Iterate through client libraries results
The cloud client libraries handle the low-level details of API pagination and provide a more iterator-like experience that simplifies interaction with the individual elements in the page responses.
The following samples demonstrate paging through BigQuery table data.
C#
Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery C# API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Go
Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
The Cloud Client Libraries for Go automatically paginates by default, so you do not need to implement pagination yourself, for example:
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
The Cloud Client Libraries for Node.js automatically paginates by default, so you do not need to implement pagination yourself, for example:
PHP
Before trying this sample, follow the PHP setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery PHP API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Pagination happens automatically in the Cloud Client Libraries for PHP
using the generator function rows
, which fetches the next page of
results during iteration.
Python
Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
The Cloud Client Libraries for Python automatically paginates by default, so you do not need to implement pagination yourself, for example:
Ruby
Before trying this sample, follow the Ruby setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Ruby API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Pagination happens automatically in the Cloud Client Libraries for Ruby
using Table#data
and Data#next
.
Request arbitrary pages and avoid redundant list calls
When you page backwards or jump to arbitrary pages using cached
pageToken
values, it is possible that the data in your pages might
have changed since it was last viewed but there is no clear indication that
the data might have changed. To mitigate this, you can use the etag
property.
Every collection.list
method (except for Tabledata) returns an
etag
property in the result. This property is a hash of the page
results that can be used to verify whether the page has changed since the last
request. When you make a request to BigQuery with an ETag value,
BigQuery compares the ETag value to the ETag value returned by
the API and responds based on whether the ETag values match. You can use ETags
to avoid redundant list calls as follows:
To return list values if the values have changed.
If you only want to return a page of list values if the values have changed, you can make a list call with a previously-stored ETag using the HTTP "if-none-match" header. If the ETag you provide doesn't match the ETag on the server, BigQuery returns a page of new list values. If the ETags do match, BigQuery returns an
HTTP 304 Not Modified
status code and no values. An example of this might be a web page where users might periodically fill in information that is stored in BigQuery. If there are no changes to your data, you can avoid making redundant list calls to BigQuery by using the if-none-match header with ETags.To return list values if the values have not changed.
If you only want to return a page of list values if the list values have not changed, you can use the HTTP "if-match" header. BigQuery matches the ETag values and returns the page of results if the results have not changed or returns a 412 "Precondition Failed" result if the page has changed.
Page through query results
Each query writes to a destination table. If no destination table is provided, the BigQuery API automatically populates the destination table property with a reference to a temporary anonymous table.
API
Read the
jobs.config.query.destinationTable
field to determine the table that query results have been written to.
Call the tabledata.list
to read the query results.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
To set the number of rows returned on each page, use a
GetQueryResults
job and set the
pageSize
option
of the QueryResultsOption
object that you pass in, as shown in the
following example:
TableResult result = job.getQueryResults();
QueryResultsOption queryResultsOption = QueryResultsOption.pageSize(20);
TableResult result = job.getQueryResults(queryResultsOption);
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Python
The
QueryJob.result
method returns an iterable of the query results. Alternatively,
- Read the
QueryJob.destination
property. If this property is not configured, it is set by the API to a reference to a temporary anonymous table. - Get the table schema with the
Client.get_table
method. - Create an iterable over all rows in the destination table with the
Client.list_rows
method.
Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.