はじめに
この記事は、LIFULL Advent Calendar 2024の4日目の記事です。
普段はLIFULLのWebマーケティングに関連のバッチの運用とかレポート用のクエリの管理システムの運用とかやっています。（開発もねっ）
今回はそんな運用を改善するためにGASを書いていた時の一コマを描きます。
こんな人に向けた記事です(AND条件)
- BigQueryで数百万件のデータを扱っている
- スクラッチでプログラムを書くことがよくある
- RubyやPythonだとBigQuery接続のためにライブラリ読み込ませるのめんどい
- 時代はGASだ！
What happened?
あれは2024年11月の終わり頃。とあるシステムで実行されているSQL群を別のシステムに移行しようとしていました。SQLの接続先はシステムが変わってもBigQueryで変わりません。
移行自体は難しくないのですが、テストが大変です。新旧2つの1000行以上からなる巨大クエリを実行して新旧で結果が変わらないことを検証せねばならないのです。
最初、愚直にクエリの結果行同士の数字項目の差分を出して比較してみるつもりでした。しかし、クエリによっては結果が100万行を超えたり列が50以上あったりするので目視による比較は無理だと判断。
GROUP BYとSUMを駆使すればわかりやすくまとめられなくはないけれど、移行するクエリの数が40くらいあるのでいちいちテストの検算用に40個もSQL書いてられない。
「ぢゃあ」と後輩がBigQueryからGASで新旧のクエリ実行結果データを取ってきてGAS上で比較するツールを書いてくれたのですが、結果が数万行のクエリで普通に実行するとGASがエラーで落ちました。
どうやらGASに割当てられているメモリが足らなくなったっぽい。
keelaiに聞いてみた
LIFULLには、内製AIにコミットしてチームの業務を効率化しよう！でも触れられている keelai というAIがあります。とりあえずそいつに聞いてみました。
語尾の「ぴょん」はkeelaiのカスタマイズ機能で某バスケ漫画の山王工業のキャプテンっぽくしゃべらせているからです。
Q. GASが "The JavaScript runtime exited unexpectedly" でおちるのなぜ？
A. Google Apps Script (GAS) が "The JavaScript runtime exited unexpectedly" で落ちる理由はいくつか考えられます。以下が一般的な原因ですぴょん：
- メモリ制限の超過: スクリプトが利用可能なメモリ量を超えてしまうと実行が中断されることがあります。データ操作が多い場合は特に注意が必要ですぴょん
うん、やっぱり原因はメモリ不足っぽいなあ。
Q. GASが途中でメモリ不足で落ちないようにJSプログラム上で工夫することは何？
A. Google Apps Script (GAS) を使っている際にメモリ不足を避けるための工夫として考えられる方法は以下の通りぴょん：
- 処理の分割: 長時間実行されるタスクや大量のデータを処理する場合は、複数の小さなスクリプトに分割して実行するぴょん。例えば、バッチ処理を使って少しずつデータを処理することで、メモリ消費を抑えることができるぴょん。
- 不要なデータの削減: 使用しないデータや変数は、メモリから開放するために明示的に null を代入してクリアするぴょん。大きな配列やオブジェクトを使い終わったら、それをクリアすることでメモリの負荷を減らせるぴょん。
- 選定的なデータ取得: 必要なデータだけを取得するようにし、可能であればサーバーサイドでフィルターして取得する量を減らすぴょん。たとえば、スプレッドシート全体を読み込まず、必要な範囲だけを指定してデータを取得する方法があるぴょん。
実装時に気を付けていたこととしては
- 関数化して処理を分割する
- メモリ解放を意識してconstやletによる定数・変数の宣言（ブロックを抜けたら宣言した領域が解放されるはず）
なので、まあ大体方針は合ってる。
あと実装してないことは 3 の選定的なデータ取得だな。
こんな感じでkeelaiは自分の方針の振り返りや足らない点の補足などに活躍しています。
BigQueryの実行結果を分割して取得する
LIMIT句で行数制限
普通にパッと思いつくのは
SELECT
hugeColumn AS huge
FROM
`myproject.dsHoge.tableHuge*`
WHERE
_TABLE_SUFFIX BETWEEN beginYmd AND endYmd
LIMIT 1, 10000
みたいなSQLを発行し、GASでループさせてLIMIT句の数値を増やしていくというものでした。
ループ内で新旧のクエリにLIMIT句くっつけて発行すればとれるはずです。
だがちょっと待ってほしい。
BigQueryの場合、LIMIT句で制限してもクエリ自体は全データを取得するので料金は全部実行したのと同じだけかかります。100万行だったら上記のクエリを100回実行するので、参照するテーブルのサイズによってはテストのためにものすごい金額が掛かることになってしまいます。
却下。
ページネーション
その前にkeelaiに聞いていた質問がありました。
GASでBigQueryの20万件ほどのテーブルのデータを取得しようとしているのに、デフォルトでは25,000件しかとれなかったのです。
Q. GASで BigQuery.Jobs.getQueryResults を利用してクエリの実行結果を取得すると、20万件ほど取得するはずのクエリでも25000件しかデータが取得できません。
どうすればいいですか？
A. Google Apps ScriptでBigQueryの
BigQuery.Jobs.getQueryResultsを使う際、デフォルトでは取得できる行数が制限されています。そのため、20万件のデータを取得するには以下の手順を検討してくださいぴょん
- ページネーションの実装：
- getQueryResultsメソッドには、ページネーション用の
pageTokenが存在します。最初の25,000件を取得した後、pageTokenを活用して次のデータセットを継続的に取得しますぴょん。
これだぁああああ。実装コードもkeelaiに聞いてしまおう。
Q. ページネーションの実装は実際にはどうなりますか？
A. Google Apps ScriptでBigQueryのデータをページネーションで取得するには、以下の手順で実装できますぴょん。これにより、複数ページに渡るデータを順次取得できますぴょん。
function getBigQueryResults() { var projectId = 'your-project-id'; // プロジェクトID var datasetId = 'your-dataset-id'; // データセットID var query = 'SELECT * FROM your_table'; // 実行するクエリ // クエリを実行してジョブを作成 var request = { query: query, useLegacySql: false // 標準SQLを使用する設定 }; var queryResults = BigQuery.Jobs.query(request, projectId); var jobId = queryResults.jobReference.jobId; var pageToken = null; var allRows = []; // すべての行を格納する配列 do { var results = BigQuery.Jobs.getQueryResults(projectId, jobId, { pageToken: pageToken, maxResults: 25000 // 1回の取得での最大行数 }); // 現在のページの行を追加 var rows = results.rows; if (rows) { allRows = allRows.concat(rows); } // 次のページがあるかを取得 pageToken = results.pageToken; } while (pageToken); // すべてのデータがallRowsに格納されます Logger.log('Fetched ' + allRows.length + ' rows.'); }
このやり方なら、クエリは最初の１回のみの発行でpageTokenとjobIdから同じ一時的な結果テーブルのデータを取得しにいきます。BigQuery API呼び出しに若干のオーバーヘッドはありますが、LIMIT句に比べればずっとパフォーマンスがいいです。
ということで、適宜varをconstやletに書き換えたりは自分でしつつ、上記のコードを組み込んでなんとかテストツールを作成しました。
微調整
うーん、まだ同じエラーで落ちるなあ。
仕方がない、maxResultsの値を徐々に下げて試そう。
結果、何回か試して10000で落ち着きました。
なお、maxResultsの値を下げすぎると、BigQuery API呼び出しの数が増えるのでパフォーマンスは悪化します。
色々な条件によって変わってくると思われるので、ちょうど良いところを各自で探してみてください。