BigQuery外部テーブルをネイティブテーブル化してYappli Analyticsを爆速にした話

こんにちは！データサイエンス室（以下、DS室）の山本です（@__Y4M4MOTO__）です。

先日、アプリ分析ツール「 Yappli Analytics 」の動作を大幅に改善しました。この記事では、どのように改善し、どれくらい速くなったのかを紹介します。

なぜやったのか
何をやったのか
どれくらい速くなったのか
結び

なぜやったのか

元々、Yappli Analyticsは「動作が遅い」という課題を抱えていました。そんな中、年明けから「最近、業務に支障が出るレベルで遅くなってきている」とカスタマーサクセスチームから要望をいただくようになりました。自分でも実際に触ってみると、確かに以前よりもレスポンスが悪くなっていることを実感したため、原因調査と対策を行うことにしました。

何をやったのか

1. 現状の可視化とモニタリング

まずは「どのくらい遅いのか」を定量的には把握するため、BigQueryの INFORMATION_SCHEMA.JOBS_BY_PROJECT を活用してモニタリングダッシュボードを構築しました。

具体的には日々実行されているジョブの数と所要時間の合計をプロットし、時間の経過とともにどのように変化しているかを可視化しました。

所要時間は JOBS_BY_PROJECT の start_time と end_time を用いて、以下のSQL式で秒単位に変換して算出しました。

EXTRACT(DAY FROM end_time　- start_time) * 86400 +     -- 1日 = 86400秒
EXTRACT(HOUR FROM end_time - start_time) * 3600 +     -- 1時間 = 3600秒
EXTRACT(MINUTE FROM end_time - start_time) * 60 +     -- 1分 = 60秒
EXTRACT(SECOND FROM end_time - start_time)            -- 秒

実際にプロットしたグラフがこちらです。ジョブ数はそれほど変わっていないにも関わらず、所要時間の合計が年明けからどんどん増加していっていることがわかります。このグラフを見て、急いで対策する必要があることを痛感しました。

2. 原因特定

原因はジョブの詰まり

続いて、何が原因で遅くなっているのかを調査しました。

遅くなってしまう原理については、下記であることが早期に分かりました。

Yappli Analyticsで使用しているBigQueryの計算リソースは容量料金（スロット時間単位）で契約している
特定のジョブで時間がかかってしまっていると、後続のジョブが詰まってしまう

ですので、調査の中心は「後続を詰まらせるジョブは何なのか」を特定することにありました。

詰まらせているジョブを特定するためのダッシュボード作成

詰まらせているジョブを特定するには、実行されたジョブを時系列順に並べて所要時間の推移を確認し、急に所要時間が長くなっている箇所を見つける必要があります。この特定作業を都度 JOBS_BY_PROJECT ビューをクエリして行うのは大変なので、次のような2つの図表を持つダッシュボードを作成しました。

1. 1分単位の平均所要時間の推移グラフ

creation_time で1分単位で区切ってジョブの所要時間の平均をプロットしたグラフです。これで、詰まっている時間帯を1分単位で特定できるようにしました。

2. ジョブの時系列表

先の推移グラフで特定した時間帯の詳細を確認するため、ジョブの実行順序を示す時系列表を作成しました。この表には、ジョブID、Yappli Analytics上のページ、参照しているテーブル、操作したユーザのメールアドレス、フィルタ設定（期間の開始日・終了日、期間の単位（日/週/月））などの情報を含めました。これにより、詰まらせているジョブとその特徴を分析しやすくしました。

参照しているテーブルは referenced_tables から取得しました。Yappli Analytics上のページは、参照しているテーブルをもとに CASE WHEN 句で分類して特定しました。

操作したユーザのメールアドレス、フィルタ設定は job_stages.steps.substeps 内のクエリからパースして取得しました。

補足：操作したユーザのメールアドレスについて
Yappli AnalyticsではLooker Studioを用いてマルチテナントなダッシュボードを実現するため、どのユーザがどのアプリのデータを見ることができるかを管理するための台帳（以下、YA権限台帳）をスプレッドシートで管理し、Looker Studioのカスタムクエリで閲覧者のメールアドレスパラメータと照合しています。詳細は次の記事をご覧ください。 tech.yappli.io

普通のフィルタであれば、フィルタ条件は query に入っているクエリから特定できます。しかし、パラメータを設定しているフィルタの場合、フィルタ条件は query には入っておらず、 job_stages.steps.substeps 内のクエリからパースする必要があります。クエリによってどのようなステップになるのかは異なるのですが、今回、クエリを発行するのはLooker Studioなので同じ図表であれば同じクエリが発行されます。したがって、パラメータの値が入っている substep も基本的に同じになるため、 substep 内のクエリからパラメータの値を抽出するというアプローチが十分有効となります。

期間のパラメータを設定している場合、 substep 内のクエリには1970/01/01からの経過日数で入っています。そのため、経過日数を抽出した上で DATE_ADD() することで元の日付を算出できます。

Looker Studioのカスタムクエリで期間のパラメータを設定している一例:

WHERE
  dt BETWEEN PARSE_DATE("%Y%m%d",@DS_START_DATE) AND PARSE_DATE("%Y%m%d",@DS_END_DATE)

substep 内のクエリの一例:

WHERE and(between($22, 20450, 20463)

上記クエリを DATE_ADD() した結果:

SELECT
  DATE_ADD(DATE("1970-01-01"), INTERVAL 20450 DAY), -- 2025-12-28
  DATE_ADD(DATE("1970-01-01"), INTERVAL 20463 DAY), -- 2026-01-10

実行クエリも確認できるようにしました。時系列表に入れるとスペースの関係で見づらくなってしまうため、 query を表示する1行1列の表を用意し、クロスフィルタリングを用いて、時系列表の行をクリックするとそのジョブのクエリが表示されるようにしました。

詰まらせているジョブの特徴を分析

詰まらせているジョブを特定した後は、そのジョブの job_id をもとにBigQueryのジョブエクスプローラーで検索をかけて、ジョブの詳細を分析しました。ジョブの各ステップの所要時間と処理内容を確認したところ、詰まらせているジョブはいずれもYA権限台帳のスプレッドシートを読み込むステップで特に所要時間がかかっていることがわかりました。

このことから、Yappli Analyticsが遅くなっている原因は、YA権限台帳のスプレッドシートを参照していることにあると考えました。

3. 対策の実施

方針

現状のYappli Analyticsの構成を下記に示します。先の調査から、YA権限台帳の参照に時間がかかっていることがわかりました。

graph LR subgraph LookerStudio viz[図表] source[ データソース（カスタムクエリ） ] end spreadsheet[ YA権限台帳（Google スプレッドシート） ] subgraph BigQuery external_table[ YA権限台帳（外部テーブル） ] native_table[ アプリデータ（ネイティブテーブル） ] end spreadsheet --> external_table --> source native_table --> source source　--> viz

そこで、YA権限台帳を外部テーブルとして参照するのではなく、何かしら同期機構を用意してネイティブテーブルとして参照することにしました。理由は、外部テーブルへの参照はネイティブテーブルへの参照と比べてパフォーマンスが低速だからです¹。

graph LR subgraph LookerStudio viz[図表] source[ データソース（カスタムクエリ） ] end spreadsheet[ YA権限台帳（Google スプレッドシート） ] subgraph BigQuery permission_table[ YA権限台帳（ネイティブテーブル） ] native_table[ アプリデータ（ネイティブテーブル） ] end sync_system([ 同期機構 ]) spreadsheet --> sync_system --> permission_table --> source native_table --> source source　--> viz

余談：スロット追加では解決しなかった
YA権限台帳のネイティブテーブル化以外の対策として、Yappli Analyticsで使用しているBigQueryのスロットの追加も試しました。しかし、改善するどころかむしろ状況は悪化してしまいました。理由は、スロットを追加したことで同時実行できるジョブの数が増え、YA権限台帳＝外部テーブルの制限事項に引っかかるようになってしまったためです。その時のエラーメッセージ:

Resource exceeded during query execution: Google Sheets service overloaded for spreadsheet id: [スプレッドシートID]

参考: 外部テーブルの概要 | BigQuery | Google Cloud Documentation

ネイティブテーブル化による速度検証

外部テーブル参照をネイティブテーブル参照に置き換えることでどれくらい速度が改善するのかを検証しました。サンプルデータを用意して、同じクエリを両方のテーブルに対して実行し、所要時間を比較しました。

サンプルデータは次のような10000行×2列のデータを用意しました。このデータをスプレッドシートに入れて作成された外部テーブルと、同じデータを入れたネイティブテーブルを用意しました。

col1        col2
--------------------------
row1        0.1009368403
row2        0.255385447
row3        0.7903592298
row4        0.7659615774
row5        0.1942172707
...
row10000    0.2006721497

クエリは下記のような、 col1 が row5000 の行を取得するクエリを用意しました。

外部テーブルに対して実行したクエリ:

SELECT * FROM `PROJECT_ID.DATASET_NAME.external_table`
WHERE
  col1 = 'row5000'

ネイティブテーブルに対して実行したクエリ:

SELECT * FROM `PROJECT_ID.DATASET_NAME.native_table`
WHERE
  col1 = 'row5000'

このクエリを、キャッシュを無効化した状態で5回ずつ実行した際の所要時間を以下に示します。

回数	外部テーブルの所要時間（秒）	ネイティブテーブルの所要時間（秒）
1回目	1.92	0.28
2回目	3.06	0.27
3回目	3.31	0.25
4回目	2.01	0.37
5回目	3.09	0.26

所要時間を見てみると、外部テーブルだと3秒ほどかかっていた処理がネイティブテーブルだと0.3秒程度で終わることがわかりました。これにより、外部テーブル参照をネイティブテーブル参照に置き換えることで大幅な速度改善が見込めることがわかりました。

同期機構の設計・構築

YA権限台帳（＝スプレッドシート）をどうやってネイティブテーブル化するか、その同期機構を設計・構築しました。YA権限台帳はカスタマーサクセスチームが日々更新するため、彼らの業務フローを極力変えないことが重要でした。そこで、GASとTROCCO APIを用いて以下の仕組みを構築しました。

GASでスプレッドシート上にカスタムメニューを実装し、「同期」ボタンを用意
「同期」ボタンが押されるとTROCCO APIが叩かれ、TROCCOのジョブを実行
TROCCOのジョブがスプレッドシートのデータをBigQuery上にあるネイティブテーブルへ同期

カスタムメニューと同期ボタンの作成は以下のようなコードで行いました。GASの onOpen() トリガーを用いて、スプレッドシートが開かれたときにカスタムメニューを作成するようにしています。

function onOpen() {
  SpreadsheetApp.getUi().createMenu('🔄同期')
    .addItem('同期する', 'syncPermissionList')
    .addToUi();
}

参考:

TROCCOでの同期処理は、CREATE OR REPLACE TABLEするデータマートジョブを用意して、それをデータマートジョブ実行API で実行する形で実装しました。

記事執筆時に、TROCCOでスプレッドシート→BigQueryへの転送ジョブを用意する方法もあることに気づきました。

この仕組みにより、既存フローに「ボタンを1つ押す」というステップが増えるだけで済むようにしました。とはいえ、押し忘れが起こる可能性も十分にあるため、次のような運用カバーも行いました。

日次での自動同期も実施（押し忘れても翌日には同期された状態になっている）
カスタマーサクセスチームへ告知・展開する際に、オフィスで実際に触ってもらったり改善後の大幅に速くなったYappli Analyticsを動画でSlackに投稿したりして、「一手間増えるけど、それ以上のリターンがある」ことを印象付け

これらの運用カバーが功を奏したのか、今のところ押し忘れによる問題は発生していません。

余談：「同期」ボタンの設置場所の検討
設計・構築の時点でカスタムメニューだと押し忘れが発生しやすいのではないかという懸念はあったため、もっと押し忘れにくい位置にボタンを配置できないか試行錯誤しました。試行錯誤した案は以下の通りですが、それぞれの理由で断念しました。

図形を設置してスクリプトを割り当てる案
- →スペースがなくて断念
サイドパネルを作成してそこにボタンを置く案
- →初めてボタンを押した際、スクリプトの権限確認ページへの遷移ができなくて断念

なお、この仕組みには下記の欠点があります。

TROCCOで実行しているジョブが完了したことを同期ボタンを押した人に通知することが困難
前の人の同期処理が完了する前に次の人が同期ボタンを押してしまうと、後から押した人の同期処理がキャンセルされてしまう

現状は一旦、下記の対策をとったり、様子見をしています。

「同期ボタン」を押したら「同期に30秒ほどかかるので、少し待って欲しい」旨をalertで表示
タイミングが被るほど頻繁に同期ボタンが押されることはないはず
- キャンセルされてしまうことは今のところ月に1~2回程度で、それによる大きな問題も発生していない

どれくらい速くなったのか

改善後の日々実行されているジョブの数と所要時間の合計の推移は以下のようになりました。所要時間の合計が劇的に減少していることがわかります（正直、ここまで爆速になるとは思ってませんでした…）。カスタマーサクセスチームからも「めっちゃ早くなった！」との嬉しい声をいただいています。

結び

この記事では、Yappli Analyticsの動作を大幅に改善した取り組みについて記しました。普段は見ない画面や触らないデータに触ったり、直面しない課題に引っかかったりして、興味深かったです。

ここまでお読みいただきありがとうございました！

外部テーブルの概要 | BigQuery | Google Cloud Documentation ↩

Yappli Tech Blog

株式会社ヤプリの開発メンバーによるブログです。最新の技術情報からチーム・働き方に関するテーマまで、日々の熱い想いを持って発信していきます。