批量更新Bigquery结果到google表格(大约50k个结果)

Question

我试图使用脚本编辑器(应用程序脚本)流BigQuery结果到谷歌表。我在看这些[文件][1]。

我的问题是，数据没有完全加载，它挂起了。我有大量的行(超过12行)。我认为我需要优化"追加结果"。部分，并进行某种批处理更新。现在这是一个循环，我猜效率不是很高。我想不通。我尝试使用".next()"但是得到一个错误，函数不存在。我用这些[文档][2]。

如何优化追加结果部分?下面是整个代码(稍后我只包含了我想修改的部分):

var ui = SpreadsheetApp.getUi();
ui.createMenu("Update")
.addItem('Update','update')
.addToUi();
}

function update() {

run1(""Filter1"",""FilterA"","Sheet1);
run1(""Filter2"",""FilterB"","Sheet2");

};

function run1(filter1,filter2,output) {

var projectId = 'xxx';
var request = {
useLegacySql: false,
useQueryCache: false,
query: 'select * from table ' +
'where a1.col1 = ' + filter1 + ' and a1.col2  in ( ' + filter2 + ' ); ' 

};

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


// Check on status of the Query Job.
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId,jobId);
}
// Get all the rows of results.
var rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(projectId,jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
if (rows) {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName(output);
sheet.clearContents();

// Append the headers.
var headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
sheet.appendRow(headers);
spreadsheet.getSheetByName(output).getRange("C1").setValue("Modified_col_name");
// 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;
}
}


sheet.getRange(2, 1, rows.length, headers.length).setValues(data);
Logger.log("Results spreadsheet created: %s",
spreadsheet.getUrl());
} else {
Logger.log("No rows returned.");
}
};

特别是这部分代码:

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

Answer 1

我相信你的目标是这样的。

• 你想减少脚本的处理成本。

在这种情况下，下面的修改如何?

修改脚本:

在使用此脚本之前，请在Advanced Google services中启用Sheets API。

:

if (rows) {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName(output);
sheet.clearContents();

// Append the headers.
var headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
sheet.appendRow(headers);
spreadsheet.getSheetByName(output).getRange("C1").setValue("Modified_col_name");
// 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;
}
}


sheet.getRange(2, 1, rows.length, headers.length).setValues(data);
Logger.log("Results spreadsheet created: %s",
spreadsheet.getUrl());
} else {
Logger.log("No rows returned.");
}

:

if (rows) {
var headers = queryResults.schema.fields.map(function (field) {
return field.name;
});
var data = [headers, ...rows.map(({ f }) => f.map(({ v }) => v || ""))];
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName(output);
sheet.clearContents();
SpreadsheetApp.flush();
Sheets.Spreadsheets.Values.update({ values: data }, spreadsheet.getId(), output, { valueInputOption: "USER_ENTERED" });
// spreadsheet.getSheetByName(output).getRange("C1").setValue("Modified_col_name"); // I'm not sure about this line.
Logger.log("Results spreadsheet created: %s", spreadsheet.getUrl());
} else {
Logger.log("No rows returned.");
}

参考:

• 方法:spreadsheets.values.update

Answer 2

您需要在查询中使用更多的过滤器或指定更少的列数。因为你可能犯的错误之一是文件大小大于10MB。因为行的限制是50,000行在这种情况下你会得到12,000或15,000行之间的错误。你可以把数据分成几个google表格。

在这里你可以看到一些解决方案，你也可以在这个链接中看到更多的文档。

您的查询结果可能太大。如果:

查询将失败数据透视表有超过50K个结果。要减少查询结果，您可以可以:

• 使用过滤器限制结果
• 限制每次出线的行数
• 在添加行、列、值和过滤器时关闭"显示总数">
• 结果的大小超过10MB。若要减小大小，请返回更少的行或列。