CODEWITHSUNDEEP
google-apps-scriptgoogle-bigquery
Ron Bailey
Ron Bailey

Reputation: 56

Error: Call to bigquery.jobs.get fails with error: Not found:

I'm executing the following code in the AppsScript of a Google sheet that is uploading its data to BigQuery...

var tableId = tableName
var job = {
  configuration: {
    load: {
      destinationTable: {
        projectId: projectId,
        datasetId: datasetId,
        tableId: tableId
      },
      skipLeadingRows: 1,
     allowQuotedNewlines:true,
    quote: "'"
    }
  }
};

csv_blob = Utilities.newBlob(csv,"application/octet-stream");

job = BigQuery.Jobs.insert(job, projectId, csv_blob );

var jobId=job.jobReference.jobId;

var queryResults = BigQuery.Jobs.get(projectId, jobId);

var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
  Utilities.sleep(sleepTimeMs);
  sleepTimeMs *= 2;
  queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}

The upload is successful, but the BigQuery.Jobs.get call always fails with the error "API call to bigquery.jobs.get failed with error: Not found" If I look up the job in the BigQuery UI, the jobId I'm getting back from the job.jobReference.jobId call is correct. If I try the API call on its own at cloud.google.com/bigquery/docs/reference/rest/v2/jobs/get, passing in the projictId, jobId, and location (us-west3), It finds the job and returns it. Any ideas why the call in my code is failing? There is no parameter for location.

Upvotes: 0

Views: 729

Answers (1)

Ron Bailey
Ron Bailey

Reputation: 56

So, it turns out that BigQuery.Jobs.get needs the location in addition to the projectId and jobId, even though there is no location parameter. You can shoehorn it in though by doing the following...

var job = BigQuery.Jobs.get(projectId, jobId, {location: jobLocation});

and you can get the location from the job you inserted...

var jobLocation = job.jobReference.location;

So here's my fixed code...

var tableId = tableName

var job = {
  configuration: {
    load: {
      destinationTable: {
        projectId: projectId,
        datasetId: datasetId,
        tableId: tableId
      },
    skipLeadingRows: 1,
    allowQuotedNewlines:true,
    quote: "'"
    }
  }
};

csv_blob = Utilities.newBlob(csv,"application/octet-stream");

//Copy the csv to BigQuery
job = BigQuery.Jobs.insert(job, projectId, csv_blob );

//Get the job details
var jobId=job.jobReference.jobId;
var jobLocation = job.jobReference.location;

//Give it a half second to complete
var sleepTimeMs = 500;
Utilities.sleep(sleepTimeMs);

//Get the job status (refresh the job var to get the latest)
var job = BigQuery.Jobs.get(projectId, jobId, {location: jobLocation});  

//Loop until its done
var jobstatusstate = job.status.state;
while(jobstatusstate!="DONE") {
  Utilities.sleep(sleepTimeMs);
  sleepTimeMs *= 2;
  job = BigQuery.Jobs.get(projectId, jobId, {location: jobLocation});  
  jobstatusstate = job.status.state;
}

Upvotes: 2

Related Questions