Google Apps Script でBigQuery に1分以上5分未満かかるSQLを投げる方法


背景

Google Apps Script (GAS) で BigQuery (BQ)にSQLをなげる関数 BigQuery.Jobs.query が 1分以上結果が返るまでかかる場合、エラーが返ってくる。

上記のJobはBQで計算され続けてる&GAS自体は540秒まで実行してくれるのでもったいない

https://cloud.google.com/functions/quotas#time_limits


解決策

var resource = {

"query" : sql, //クエリ文字列
"timeoutMs": 1000000 // ここをいくら長くしてもBigQuery側のJob実行時間がかわるだけでGASはまってくれない
};

//エラーが返っても止まらないようにtry~finallyで囲む
try{
query_results = BigQuery.Jobs.query(resource, projectNumber);
}
finally{

Utilities.sleep(300000);//GASにはSleepがある!最長で5分まで指定可能

var joblist = BigQuery.Jobs.list(projectNumber);//BQのjobidをひっぱってくる
var jobid = joblist.jobs[0].jobReference.jobId;//先頭のものが最新。仮にBQjobを並列実行したり、sleepしている間にもうひとつ実行したりしたら、うまく動かない。もっと賢い方法がないものか
var out = BigQuery.Jobs.getQueryResults(projectNumber,jobid);
}

だいぶかっこわるいが動く

特に joblist.jobs[0] の箇所は他のBQ job が投げられなくなるので、もっとスマートな方法はないものか

(はやくCollaborarotyがバックグラウンドでイベント実行できるようにならないかな)


参考

https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/list

https://developers.google.com/apps-script/reference/utilities/utilities#sleep(Integer)