我编写了一个应用程序脚本,它将与 bigQuery API 交互并获取 bigQuery 结果。
API 被击中近 60-70 次。在此过程中,有时我会收到以下错误消息:
“JavaScript 运行时意外退出”
有人可以帮我吗?
如果您需要任何其他信息,请告诉我
function fetchTables() {
var timezone = "GMT+" + new Date().getTimezoneOffset()/60;
var date = Utilities.formatDate(new Date(), timezone, "yyyy-MM-dd HH:mm");
Logger.log(date);
var sheet = SpreadsheetApp.getActive().getSheetByName('Tables');
var value = sheet.getRange('A1').getValue();
var projectId = 'corpbi-dev';
var request = {
query: 'select table_id from [corpbi-dev:' + value + '.__TABLES__]'
};
var queryResults = BigQuery.Jobs.query(request, projectId);
var jobId = queryResults.jobReference.jobId;
var rows = queryResults.rows;
if (rows) {
// Append the results.
var data = new Array(rows.length);
for (var i = 0; i < rows.length; i++) {
var cols = rows[i].f;
data[i] = new Array(cols.length);
for (var j = 0; j < cols.length; j++) {
data[i][j] = cols[j].v;
}
}
}
var queries = [];
var totalbytes = [];
var timelapsed = [];
var rowcount = [];
var jobcode = [];
var finalSheet = SpreadsheetApp.getActive().getSheetByName('Final Metrics');
for(var i=1; i<=data.length; i++) {
var sql = "SELECT stage_loading_query FROM (SELECT stage_loading_query, job_start_time FROM [corpbi-dev:etl_log.job_details] WHERE target_table LIKE '%"+ value + "." + data[i-1]+"%' " +
"GROUP BY 1, 2 ORDER BY 2 desc) LIMIT 1"
var request1 = {
query: sql
};
var queryResults1 = BigQuery.Jobs.query(request1, projectId);
var sql1 = queryResults1.rows;
//Logger.log(sql1);
// break;
if (sql1) {
// Append the results.
var data1 = new Array(sql1.length);
for (var k = 0; k < sql1.length; k++) {
var cols = sql1[k].f;
data1[k] = new Array(cols.length);
for (var j = 0; j < cols.length; j++) {
data1[k][j] = cols[j].v;
}
}
}
queries.push(data1);
}
//Logger.log(queries.length);
finalSheet.getRange(2, 1, queries.length).setValues(queries);
// queries = finalSheet.getRange(2, 1, finalSheet.getLastRow()-1).getValues();
var endtime = (new Date()).getTime();
Logger.log(endtime);
for(var i=1; i<=queries.length; i++) {
var sqlstmt = queries[i-1];
try{
var request2 = {
query: sqlstmt,
useLegacySql: true
};
var sleepTimeMs = 50000;
var queryResults2 = BigQuery.Jobs.query(request2, projectId);
var jobId1 = queryResults2.jobReference.jobId;
while (!queryResults2.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults2 = BigQuery.Jobs.getQueryResults(projectId, jobId1);
//continue;
}
var queryDetails = BigQuery.Jobs.get(projectId, jobId1);
totalbytes.push(queryDetails.statistics.totalBytesProcessed);
timelapsed.push(queryDetails.statistics.totalSlotMs);
rowcount.push(queryResults2.totalRows);
}
catch(e){
try{
var request2 = {
query: sqlstmt,
useLegacySql: false
};
var sleepTimeMs = 50000;
var queryResults2 = BigQuery.Jobs.query(request2, projectId);
var jobId1 = queryResults2.jobReference.jobId;
while(!queryResults2.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults2 = BigQuery.Jobs.getQueryResults(projectId, jobId1);
// continue;
}
var queryDetails = BigQuery.Jobs.get(projectId, jobId1);
totalbytes.push(queryDetails.statistics.totalBytesProcessed);
timelapsed.push(queryDetails.statistics.totalSlotMs);
rowcount.push(queryResults2.totalRows);
}
catch(e){
Logger.log(e);
}
Logger.log(e);
}
finalSheet.getRange(i+1, 2,).setValue((totalbytes[i-1]/1048576).toFixed(2) + 'MB');
finalSheet.getRange(i+1, 3,).setValue((timelapsed[i-1]/1000).toFixed(2) + 's');
finalSheet.getRange(i+1, 4,).setValue((rowcount[i-1]));
}
}