CODEWITHSUNDEEP
google-apps-scriptgoogle-bigquery
Aaron Harris
Aaron Harris

Reputation: 415

Apps Script to automate Bigquery runs but doesn't do anything

I'm posting this as a new question as it's slightly different to my original questions on this matter.

I have an apps script to run a query in BigQuery. The script is running and communicating with BigQuery but the job isn't working as expected. I currently get the following error in BigQuery:

Already Exists: Table fiery-cabinet-111****:***.Test4 (error code: duplicate)

Here is the script:

function saveQueryToTable() {
  var projectId = 'fiery-cabinet-*****';
  var datasetId = '11****101';
  var tableId = 'Test4';
  var job = {
    configuration: {
      query: {
        query: 'SELECT * ' +
               'FROM [fiery-cabinet-****:*****.Test2];',
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        }
      }
    }
  };

var queryResults = BigQuery.Jobs.insert(job, projectId);
  Logger.log(queryResults.status);
}

Upvotes: 0

Views: 1081

Answers (2)

Graham Polley
Graham Polley

Reputation: 14791

The table you are writing the query results to already exists. You need to set the WRITE_DISPOSITION to either:

  1. WRITE_TRUNCATE: If the table already exists, BigQuery overwrites the table data.
  2. WRITE_APPEND: If the table already exists, BigQuery appends the data to the table.
  3. WRITE_EMPTY: If the table already exists and contains data, a 'duplicate' error is returned in the job result.

The default value is WRITE_EMPTY.

https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs#configuration.query

configuration.query.writeDisposition

Upvotes: 1

Daryl Wenman-Bateson
Daryl Wenman-Bateson

Reputation: 3939

It looks like you are missing the dataset name from your select query. Change the query to include the dataset name 

query: 'SELECT *' +
       'FROM 11****101.Test2;',

and verify syntax by viewing the execution transcript and logs

Be aware that providing you have access and the syntax (i.e. project, table and dataset name) correct, your code will just log a table insert job with BigQuery and then finish.

The script completing wont be the same as the BigQuery job completing, for that you'd have to poll the job status. You could for example be trying to insert to a table that already exists, which would post the job successfully, but fail at execution time (depending on parameters set).

Try the following to obtain the completed query status 

var queryResults = BigQuery.Jobs.insert(job, projectId);

  var jobId = queryResults.jobReference.jobId;
  var response = 'Running'

  var sleepTimeMs = 500;
  while (response != 'DONE') {
    Utilities.sleep(sleepTimeMs);
    response = BigQuery.Jobs.get(projectId, jobId).status.state;
  }

  Logger.log('Job status : ' + queryResults.status);

Upvotes: 0

Related Questions