BigQuery API：使用destinationTable运行查询不会保存结果 [英] Bigquery API: Running query with destinationTable does not save results

问题描述

我尝试使用Google Apps脚本来自动执行每日聚合流程。这个想法是在一个数据集的几张表格上运行，并将结果保存到另一个数据集的表格中。我的用户拥有相关数据集的权限。

我的请求如下所示：

  $ bmaxResults：10000，$ b $useQueryCache：false，
destinationTable：{
projectId：project_name_obfuscated，
datasetId：project_114151_shared，
tableId：test123
}，
writeDisposition：WRITE_TRUNCATE，
createDisposition：CREATE_IF_NEEDED ，
allowLargeResults：true，
query：查询语法here
}; 
 
 var queryResults = BigQuery.Jobs.query（request，project_name_obfuscated）; 
  

（我出于保密原因混淆了项目名称和查询本身） p>

查询实际上运行正常，但是它并没有被保存到我定义的指定项目/数据集/表中。

在BQ UI中，我看到类似以下内容：

 作业ID：project_name_obfuscated：作业_NhkQpi110p3i5yoOO7nzXp3tTKY 
开始时间：2014年10月20日17点47分
结束时间：2014年10月20日下午5:47 
处理的字节数：0 B 
目的地表：project_name_obfuscated：_138c3340e691065e8db0b55781b5a41c6b4bcd49.anonev_SOiiRC10lfetj000kcj4rmXNc5E 
  

任何想法我做错了什么？


解决方案

您使用的参数只能通过Jobs.query方法中的Jobs.insert获得。 Jobs.query用于快速同步查询，并且不支持Jobs.insert中的全部选项，这是异步版本。

尝试一下例如：

  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; 
 
 //工作实际上可能不会完成;等到它被标记为
 //完成。 
 var sleepTimeMs = 500; 
 while（true）{
 Utilities.sleep（sleepTimeMs）; 
 sleepTimeMs * = 2; 
 queryResults = BigQuery.Jobs.getQueryResults（projectId，jobId，{
maxResults：10000）; 
 if（！queryResults.jobComplete）{
 break; 
} 
} 
  

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?

解决方案

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;
    }
}

这篇关于BigQuery API：使用destinationTable运行查询不会保存结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！