Paginate a BigQuery result set
If you are trying to retrieve the results of a query to a BigQuery dataset that is larger than the Workflows memory limit, you can use a page token to paginate through the results. The page token represents the position in the result set, and is returned when additional results are available. This allows you to loop through a page of results at a time.
BigQuery hosts a number of public datasets that are available to the general public to query. In the following example, you query the USA Name Data public dataset to determine the most common names in the US between 1910 and 2013.
YAML
# Use a page token to loop through a page of results at a time when
# querying a BigQuery dataset larger than the Workflows memory limit
# This workflow queries a public dataset to determine the most common
# names in the US between 1910 and 2013
main:
params:[input]
steps:
-init:
assign:
-pageToken:null
-startQuery:
call:googleapis.bigquery.v2.jobs.insert
args:
projectId:${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
body:
configuration:
query:
useLegacySql:false
# Remove LIMIT from the query to iterate through all results
query:SELECT name, SUM(number) AS total FROM `bigquery-public-data.usa_names.usa_1910_2013` GROUP BY name ORDER BY total DESC LIMIT 50
result:query
-getPage:
call:googleapis.bigquery.v2.jobs.getQueryResults
args:
projectId:${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
jobId:${query.jobReference.jobId}
maxResults:10
pageToken:${pageToken}
result:page
-processPage:
for:
value:row
in:${page.rows}
steps:
-processRow:
call:sys.log
args:
data:${row}
-checkIfDone:
switch:
-condition:${"pageToken" in page and page.pageToken != ""}
assign:
-pageToken:${page.pageToken}
next:getPageJSON
{
"main":{
"params":[
"input"
],
"steps":[
{
"init":{
"assign":[
{
"pageToken":null
}
]
}
},
{
"startQuery":{
"call":"googleapis.bigquery.v2.jobs.insert",
"args":{
"projectId":"${sys.get_env(\"GOOGLE_CLOUD_PROJECT_ID\")}",
"body":{
"configuration":{
"query":{
"useLegacySql":false,
"query":"SELECT name, SUM(number) AS total FROM `bigquery-public-data.usa_names.usa_1910_2013` GROUP BY name ORDER BY total DESC LIMIT 50"
}
}
}
},
"result":"query"
}
},
{
"getPage":{
"call":"googleapis.bigquery.v2.jobs.getQueryResults",
"args":{
"projectId":"${sys.get_env(\"GOOGLE_CLOUD_PROJECT_ID\")}",
"jobId":"${query.jobReference.jobId}",
"maxResults":10,
"pageToken":"${pageToken}"
},
"result":"page"
}
},
{
"processPage":{
"for":{
"value":"row",
"in":"${page.rows}",
"steps":[
{
"processRow":{
"call":"sys.log",
"args":{
"data":"${row}"
}
}
}
]
}
}
},
{
"checkIfDone":{
"switch":[
{
"condition":"${\"pageToken\" in page and page.pageToken != \"\"}",
"assign":[
{
"pageToken":"${page.pageToken}"
}
],
"next":"getPage"
}
]
}
}
]
}
}