BigQuery can’t find job when using pagination

I’ve been using this AppScript function that I took from here with slight modifications and it seemed to work fine, it just takes in a query and returns a 2D array. However, if the query is big and comes back with more totalRows than rows and therefore requires pagination, the job doesn’t seem to be persistent and therefore I get the following error after the while (queryResults.pageToken):

API call to bigquery.jobs.getQueryResults failed with error: Not found: Job cellular-nuance-292711:job_-i4Dk9W7JVKF2-W_5

The job never seems to show up in the job history or using the bq command line tool. Essentially, it’s returning data on the first call but it’s impossible to query that same job more than once as it just disappears.

Here’s my function:

function runQuery(query) {
  var request = {
    query: query,
    useLegacySql: false
  };
  var queryResults = BigQuery.Jobs.query(request, bigQuerySettings.projectId);
  var jobId = queryResults.jobReference.jobId;
  var projectId = bigQuerySettings.projectId; // This is just a mock declaration, it's actually declared elsewhere in the actual code

  // Check on status of the Query Job.
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }
}

I’ve been adding tons of logs and can confirm that the job is definitely coming back correctly after the first attempt, and that there’s even data in the rows – the issue is simply when there’s a pagination token because totalRows is bigger than rows.

Answer

Turns out I just needed to add location to the last part:

while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken,
      location: bigQuerySettings.location
    });
    rows = rows.concat(queryResults.rows);
}

Advertisement

Answer