6

I'm trying to pull geo data from BigQuery via Apps Script but my script is failing with the above error message.

function runQuery() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  var projectId = '111111';
  
  var request = {
    useLegacySql: false,
    query: 'select geom, POA_CODE16, POA_NAME16, AREASQKM16 from `my-bigquery-project.my-datasource.my-table` WHERE POA_CODE16 >= 2000 AND POA_CODE16 < 3000;'
  };
  
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;
  
  Logger.log('Initial jobId: %s', jobId);
    
  // Check on status of the Query Job.
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    Logger.log('Not complete jobId: %s', jobId);
    try {
      queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
    } catch(e) {
      Logger.log(e);
    }
  }
  
  // Get all the rows of results.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    
    Logger.log('Page token jobId: %s', jobId);
    
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);

  }
  
  Logger.log(rows.length);
}

The script is failing here:

queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });

with the error

GoogleJsonResponseException: API call to bigquery.jobs.getQueryResults failed with error: Not found: Job my-bigquery-project:job_##RJYm1kUsbMif3N6Dpa7t-mkC## (line 34, file "tests")

The logs show:

Initial jobId: job_##RJYm1kUsbMif3N6Dpa7t-mkC##
Page token jobId: job_##RJYm1kUsbMif3N6Dpa7t-mkC##

I feel like it might be the size of the rows being returned - the polygons are quite big - that is the problem but the error message doesn't give any clues and I'm not sure how/where else to debug it.

The query runs fine from BigQuery.

Alasdair
  • 101
  • 1
  • 5

2 Answers2

3

I was facing the same error, adding the location with pageToken solved the problem.

function runQuery(projectId, query) {
        let request = {
            query: query,
            location: "dataset-location",
            useLegacySql: false
        };

        let queryResults = BigQuery.Jobs.query(request, projectId);
        let jobId = queryResults.jobReference.jobId;

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

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

        return { rows: rows, queryResults: queryResults }

    }
Darsh Shukla
  • 289
  • 4
  • 5
0

You might need to set a higher value for timeoutMs than the default of 10000.

Ted Romer
  • 11
  • 1