API调用bigquery.jobs.getQueryResults失败，错误为:找不到:作业

Question

我正试图通过应用程序脚本从BigQuery中提取地理数据，但我的脚本失败，并显示上述错误消息。

function runQuery() {
// Replace this value with the project ID listed in the Google
// Cloud Platform project.
var projectId = '111111';

var request = {
useLegacySql: false,
query: 'select geom, POA_CODE16, POA_NAME16, AREASQKM16 from `my-bigquery-project.my-datasource.my-table` WHERE POA_CODE16 >= 2000 AND POA_CODE16 < 3000;'
};

var queryResults = BigQuery.Jobs.query(request, projectId);
var jobId = queryResults.jobReference.jobId;

Logger.log('Initial jobId: %s', jobId);

// Check on status of the Query Job.
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
Logger.log('Not complete jobId: %s', jobId);
try {
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
} catch(e) {
Logger.log(e);
}
}

// Get all the rows of results.
var rows = queryResults.rows;
while (queryResults.pageToken) {

Logger.log('Page token jobId: %s', jobId);

queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}

Logger.log(rows.length);
} 

脚本在此失败：

queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken
}); 

错误

GoogleJsonResponseException: API call to bigquery.jobs.getQueryResults failed with error: Not found: Job my-bigquery-project:job_##RJYm1kUsbMif3N6Dpa7t-mkC## (line 34, file "tests")

日志显示：

Initial jobId: job_##RJYm1kUsbMif3N6Dpa7t-mkC##
Page token jobId: job_##RJYm1kUsbMif3N6Dpa7t-mkC##

我觉得这可能是返回的行的大小-多边形相当大-这就是问题所在，但错误消息没有给出任何线索，我不确定如何/在哪里调试它。

查询从BigQuery运行良好。

Answer 1

我也遇到了同样的错误，用pageToken添加location解决了问题。

function runQuery(projectId, query) {
let request = {
query: query,
location: "dataset-location",
useLegacySql: false
};
let queryResults = BigQuery.Jobs.query(request, projectId);
let jobId = queryResults.jobReference.jobId;
// Check on status of the Query Job.
let sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
// Get all the rows of results.
let rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken,
location: request.location

});
rows = rows.concat(queryResults.rows);
}
return { rows: rows, queryResults: queryResults }
}

Answer 2

您可能需要为timeoutMs设置一个比默认值10000更高的值。