Bigquery API: Running query with destinationTable

Question

I'm trying to use Google Apps Script to automate a daily aggregation process. The idea is to run on a few tables from one dataset, and save their results to a table on a second dataset. My user has permissions on both relevant datasets.

My request looks as follows

        var request = {
        "maxResults": 10000,
        "useQueryCache": false,
        "destinationTable": {
              "projectId": "project_name_obfuscated",
              "datasetId": "project_114151_shared",
              "tableId": "test123"
            },
        "writeDisposition": "WRITE_TRUNCATE",
        "createDisposition": "CREATE_IF_NEEDED",
        "allowLargeResults": true,
        "query": "query syntax here"
      };

  var queryResults = BigQuery.Jobs.query(request, "project_name_obfuscated");

(I've obfuscated the name of the projects and the query itself for confidentiality reasons).

The query actually runs fine, however, it's not being saved into the designated project/dataset/table which I've defined.

In the BQ UI I'm seeing something like:

Job ID: project_name_obfuscated:job_NhkQpi110p3i5yoOO7nzXp3tTKY
Start Time: 5:47pm, 20 Oct 2014
End Time: 5:47pm, 20 Oct 2014
Bytes Processed: 0 B
Destination Table: project_name_obfuscated:_138c3340e691065e8db0b55781b5a41c6b4bcd49.anonev_SOiiRC10lfetj000kcj4rmXNc5E

Any ideas what I'm doing wrong?

Answer 1

You're using arguments which are only available via the Jobs.insert in the Jobs.query method. Jobs.query is intended for quick, synchronous queries, and doesn't support the full range of options available in Jobs.insert, which is the asynchronous version.

Try something like:

var configuration = {
  "query": {
    "useQueryCache": false,
    "destinationTable": {
          "projectId": "project_name_obfuscated",
          "datasetId": "project_114151_shared",
          "tableId": "test123"
        },
    "writeDisposition": "WRITE_TRUNCATE",
    "createDisposition": "CREATE_IF_NEEDED",
    "allowLargeResults": true,
    "query": "query syntax here"
  }
};
var job = {
    "configuration": configuration
};

var jobResult = BigQuery.Jobs.insert(job, "project_name_obfuscated");

var jobId = jobResult.jobReference.jobId;

// The job might not actually be done; wait until it is marked
// complete.
var sleepTimeMs = 500;
while (true) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      "maxResults": 10000);
    if (!queryResults.jobComplete) {
      break;
    }
}