CODEWITHSUNDEEP
google-apps-scriptgoogle-bigquery
TryingToCatchUp
TryingToCatchUp

Reputation: 13

How do I use Apps Script to run a BigQuery job that doesn't write the results to a table?

I'm trying to write a GAS that simply runs a saved BigQuery query I have. The query itself is very straightforward - it's just an UPDATE query:

UPDATE `project.dataset.table`
SET field1= '72142',
    field2= 'Yes'
WHERE field3 like '%72142%'
AND field1 IS NULL

I found this previous question How to use App script to run a google big query and save it as a new table which can we reused?

That was quite helpful, but it includes creating a table after the query is run, and I don't want to do that. I tried to adapt the script to ignore the destination table and writeDisposition values.

But it's not working.

Update: this is the script

function runQuery() {
  var projectId = 'project';
  var datasetId = 'dataset';
  var tableId = 'table';
  var job = {
    configuration: {
      query: {
        query: 'UPDATE `project.dataset.table`  SET field1 = '72142',    field2 = 'Yes' WHERE field3 like '%72142%'AND field1 IS NULL,
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        }
      }
    }
  };

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

And the error message is "Syntax error.(line 21, file "xxx")

Upvotes: 1

Views: 591

Answers (1)

Graham Polley
Graham Polley

Reputation: 14781

You need to remove all references to any table write flags, and you also have some syntax problems in your JSON:

function runQuery() {
  var configuration = {
    "query": {
    "useQueryCache": false,
    "useLegacySql": false,
    "query": "UPDATE `<your_projectId>.<your_dataset>.<your_table>` SET field1 = '72142',field2 = 'Yes' WHERE field3 like '%72142%' AND field1 IS NULL"
    }
  };

  var job = {
    "configuration": configuration
  };

  var jobResult = BigQuery.Jobs.insert(job, "<your_projectId>");
  Logger.log(jobResult);
}

Upvotes: 1

Related Questions