google-apps-script - 对 bigquery.jobs.getQueryResults 的 API 调用失败并出现错误:未找到:作业
问题描述
我正在尝试通过 Apps 脚本从 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 运行良好。
解决方案
我遇到了同样的错误,添加location
withpageToken
解决了这个问题。
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 }
}
推荐阅读
- makefile - 在make all中,先组成哪个目标
- ionic-framework - 用离子中的观察位置更新谷歌标记
- c# - Globalhotkey - 从文本文件中读取想要的热键
- python - django服务器启动时如何启动后台线程?
- selenium - 使用 Selenium 从数据库填充的多个下拉列表中的处理选项
- android - 将信用卡添加到 Google Pay
- scala - 类中表达式的用例是什么而不将其存储在值/变量中?
- python - pyqt4 QTabWidget addtab不成功
- c++ - 是否可以在 for 循环中添加两个浮点数?
- ios - Cordova iOS 13 拾取视频空文件内容