【登壇資料】QuickSight から L ke b a ase へ BI 基盤移行の設計判断と実測値

2026/10/02　JEDAI Meetup! 秋のDatabricks技術トーク

Transcript

1. QuickSight から Lakebase へ BI 基盤移行の設計判断と実測値 2026-10-02 JEDAI Meetup! 秋の Databricks 技術トーク 株式会社エブリー 江﨑 郁磨
2. 自己紹介 江﨑 郁磨 （いくまる） @190_eng 株式会社エブリー Software Engineer（新卒 2 年目） 「デリッシュリサーチ」を担当 フロント / バック / インフラ / データを一通り Databricks は ETL 処理をメインに使用 バイク乗り 2 / 24
4. アジェンダ 01 02 03 04 05 06 07 題材：デリッシュリサーチと、今までの仕組み 何が課題だったか：データ側と画面側 Lakebase とは Q1 何を格納するか Q2 どう速くするか Q3 数値をどう確かめるか 切替前に準備できていることと、まだ分からないこと・まとめ 4 / 24
5. 今日の題材：デリッシュリサーチ デリッシュキッチンの検索データから「食のトレンド」を見る、食 品関連企業向けの分析ツール 誰が使うか：調味料メーカー・小売など食品関連企業のマーケティン グ・事業企画部門 何ができるか：検索トレンド分析、組み合わせ検索ランキング、レシピ ランキング、ユーザー属性別の分析。メニュー提案や消費者分析の材料 に データ：デリッシュキッチンのレシピ検索ログ（2019 年 1 月〜最短前 日まで）。「POS では分からない、作りたい・食べたいという興味関心」 が見える 検索トレンド分析（QuickSight 埋め込み）。「せいろ」が 2024 年 秋から急伸 5 / 24
6. 今までの仕組み 移行前 Databricks の ETL 検索ログを集計し QuickSight 用の表を作る グラフ・表は全部 QuickSight → Athena その表を SQL で読む SPICE 取り込みの入口 全画面がダッシュボード 1 つの中のシート。検索語や期間の入力欄 は自社アプリで描き、入力値を QuickSight に渡す → QuickSight（SPICE） 表を取り込んで ダッシュボードに → 自社アプリ（Next.js） iframe で埋め込み 入力欄・メニューは自前 データは毎日 10 時に SPICE へ取り込む Databricks が作った表を Athena 経由で読み込み、画面はそれを 絞って集計して見せる。この構成で運用してきた ※ Amazon QuickSight は 2025 年 10 月に Amazon Quick Suite、2026 年に Amazon Quick へ改称。BI 機能は「Amazon Quick Sight」として継続。本資料では 移行当時の名称 QuickSight で統一する 6 / 24
7. データ側の課題：答えを先に行として作るしかなく、15.96 億行に膨れた QuickSight は SPICE に取り込んだ表を絞って集計する仕組み。入力を受けて行を作ったり、表を結合し直したりはできない 答えを先に行として作るしかなかった例 検索が 0 の日：グラフを途切れさせない 元データに無い 0 の行を、全単語 × 全日付で先に作る 単語 キャベツ キャベツ キャベツ キャベツ 表記揺れ：「きゃべつ」でも「キャベツ」の結果を出す 日付 回数 1/1 120 1/2 0 1/3 0 1/4 85 同じ値の行を表記ごとに複製した表を先に作る 表記 キャベツ きゃべつ きやべつ 日付 回数 1/1 120 1/1 120 1/1 120 複製していない表では効かない。レシピランキング（レシピ名の部分一致） は表記揺れに未対応。同じ表を SQL で引く社内ツールは、辞書を引いて対 応できている 属性別の表は、単語 × 月 × 性別 × 年代 × 地方の全組み合わせで 0 の行を 作る。組み合わせの数だけ行が増え、元の 6 倍になった 先に作った行。検索回数の表 約 4.9 億行 → 0 の行を先に作った表 15.96 億行（約 3 倍。属性別の表では 6 倍） 移行先ではこの表を作らず、必要なときに SQL で計算できるデータベースへ 7 / 24
8. 画面側の課題：iframe の中は、変更もレビューもしにくい 見た目は iframe の中。色を 1 つ変えるのも QuickSight 側の作業 設定は GUI 操作。計算式・フィルタ・色の定義が画面の中にあり、変 更履歴が残らず、担当者に属人化していた コードと同じようにレビューできない。SQL の写しが git にあっても、 本番と一致している保証がない 画面を自分たちで変更し、レビューできる形にしたい 自社アプリ（変更できる） iframe の境界 QuickSight の画面 見た目・計算式・色は GUI の設定 変更もレビューもしにくい 8 / 24
9. 今日の話：Lakebase への BI 移行 2 つの課題を解くために、格納先を SPICE から Lakebase（Postgres）へ、描くのを QuickSight から自作画面へ 移行後 Databricks の ETL そのまま 集計処理は触らない Q1 → 転送処理 集計結果を Postgres へ COPY Q2 → Lakebase（Postgres） 画面用のデータベース SQL でその場で計算 Q3 → 自社アプリ（Next.js） グラフ・表も自前で描く iframe なし Lakebase へ移行するにあたり、 何を格納するか、どう速くするか、数値をどう確かめるかを、 本番データを元に決めた 本番切替前の設計判断と実測値の共有 Q1 Q2 Q3 格納するもの・しないもの、更新の方式 本番コピーでインデックスの効果を測る 旧画面と新 API の数字を自動で照合する 何を格納するか どう速くするか 数値をどう確かめるか 9 / 24
10. Lakebase とは Databricks が提供するマネージド PostgreSQL 2026-01 GA 2026-06 東京リージョン ストレージと compute（クエリを処理する計算資源）を分けた構成。compute は使わない時間に停止でき、データはストレージに残る compute の大きさは CU（Compute Unit、1 CU ≈ メモリ 2 GB）で指定する 集計データは Unity Catalog にあり、転送処理も Databricks のジョブとして動く。同じ基盤の中に Postgres を持てるので選んだ Databricks の ETL 検索ログを集計 大きな処理を 1 日 1 回 → Lakebase（Postgres） 集計結果を保持し 画面の操作ごとに即座に返す（目安 1 秒） → BI 画面 操作ごとに小さな問い合わせ 短時間で何度も SQL ウェアハウスは分析クエリ向けで、 この「小さく速く何度も」には性質が合わない この発表で出てくる Lakebase の機能：Databricks の表を自動同期する synced table（Q1）／本番データのコピーを作るブラ ンチ（Q2）／用途別に分けて停止できる compute（Q2） 10 / 24
11. Q1 何を格納するか 格納するもの・しないもの、更新の方式
12. Q1 何を格納するか — Databricks の集計結果を格納し、QuickSight 向けの事前計算は SQL で計算する 変えない Databricks の集計処理 格納しない QuickSight 用の事前計算 書き直す 画面表示時の SQL 単語ごとの検索回数と、 表記揺れの辞書などを作る 0 の行を先に作った 15.96 億行の表と、 前年の同じ日の値の列は転送しない 0 の日の穴埋め、表記揺れの解決、 前年との比較を Postgres の SQL で Lakebase に格納するのはこの集計結果 検索回数の表・辞書・順位などの事前集計 QuickSight が動く間は必要なので、 作る ETL は残し、転送対象から外すだけ 移行で新しく書くのはここ → Q3 で照合 12 / 24
13. 転送は自動同期（synced table）ではなく自前の COPY にした。決め手は全期間を書き換える月次更新 synced table とは Unity Catalog の表を Lakebase へ自動でコピーし続ける機能。 表ごとに「毎回全量コピー」か「変更された行だけ」のどちらか 1 つを選ぶ 今回は合わなかった 更新は日次の追加と、辞書更新に合わせた月次の全期間書き換 えの 2 種類。方式を 1 つしか選べない 変更行だけ → 月次は値が同じ行も書き直されるため 1.2 億行 すべてが対象。1 行ずつ適用するので遅く、この表 1 本だけで 試算 3.5 時間 全量コピー → 日次でも毎回 1.2 億行を全部コピーし直す。全 表の同期で毎朝 4.5 時間 採った方式 日次：昨日以降だけを 1 トランザクションで更新 月次 別名の表へ 全量 COPY → インデックス を作る → 表名を入れ替え 本番へ切替 入れ替えるまで本番の表には触れない 日次は差分、月次は全量と、方式を分けられる 本番実測：初回 COPY は全表の合計 7.03 億行を 117 分（1.2 億行の表を含む）。元データとの行数一致も確認 13 / 24
14. Q2 どう速くするか 本番コピーでインデックスの効果を測る
15. Q2 どう速くするか — 単語で絞るクエリは速く、全単語を横断するランキングだけが遅かった 単語を指定する画面 検索トレンド分析など、大半の画面 全単語を横断するランキング 検索ワードランキングなど SELECT date, searches FROM daily_word_searches WHERE word = ' ' AND date BETWEEN '2025-11-01' AND '2026-07-31' SELECT word, SUM(searches) FROM daily_word_searches WHERE date BETWEEN '2025-07-01' AND '2025-07-31' AND popularity >= 0.01 -- popularity = GROUP BY word キャベツ 単語で絞れるので (word, date) のインデックスで該当行だけ読む Index Only Scan：0.8 ms（9 か月分） 検索頻度スコア 単語で絞れず、日付が先頭のインデックスも無い → 1.2 億行を全件読 み、条件で 9 割を捨てる。前年同期比・年間分も要るので 3 回 Seq Scan × 3：747 秒（期間 1 か月・初回） SQL は要点だけ抜粋。表名・列名は説明用に置き換えている（実際は分母の表や表記揺れの辞書とも結合する） 遅いのは集計ではなく読み取り。必要なのは popularity ≥ 0.01 の約 1 割で、残り 9 割は読んだ後に捨てている 15 / 24
16. ブランチなら、本番を変えずに同じデータで試せる Lakebase のブランチとは 本番用の Lakebase を作成時点で切り出す独立したコピー 本番とストレージを共有し、変更した差分だけを保存する （Copy-on-Write） 数秒で作成。有効期限を付けられる（今回は 7 日で自動削 除） 作成後の本番の更新は流れてこない。試験中の状態が固定 される 本番用 Lakebase ブランチ 作成時点で 切り出す → 107 GB 数秒 本番と共有 作成後の本番の更新はブランチに流れない 差分 今回：本番用の Lakebase からブランチを作り、インデックスを 追加して本番と同じ SQL を実測。本番には触れていない 16 / 24
17. 対象行を 1 割に絞ったインデックスで、747 秒を 0.7 秒に短縮 足したインデックス：条件に合う 1 割の行だけを対象にす 結果：同じ SQL・同じデータで る（部分インデックス） 期間 インデックスなし あり・初回 CREATE INDEX ON daily_word_searches (date) INCLUDE (word, searches) WHERE popularity >= 0.01 日付順に並べ、必要な 2 列を含め、条件に合う行だけ 590 MB（既存インデックスの 1/12）、作成 100 秒 表本体は読まず、インデックスだけで答える 1 か月 1年 3年 秒 計測せず 32.3 秒 計測せず 62.5 秒 747 秒 16.1 あり・2 回目以降 秒 2.0 秒 3.2 秒 0.7 初回はストレージから読む。2 回目以降はキャッシュに載った状態（2 CU で計測） 初回の遅さを本番で避ける方法はこの後で 事前に集計した表は作らずに済んだ 17 / 24
18. 同じデータに用途別の compute を分け、本番用だけ常時稼働させる 初回（キャッシュ無し）の遅さを、本番で避けるために 1 つのブランチ（本番データを持つ production ブランチ）に compute を用途別に複数立てられ、それぞれ CU と停止の設定を持つ。 READ_ONLY = 読み取り専用（read replica） production ブランチ 開発用 ↓ READ_ONLY 使わない時間は 0 まで落ちる CU は本番用と分離 本番データ 本番アプリ用 ↓ READ_ONLY 切替時に常時稼働へ（停止しない） キャッシュを維持し、初回の遅さを月次入れ 替え直後と compute 再起動時に限定 転送用 書き込み ↓ 転送処理だけが使う 月次入れ替え直後の初回（16〜62 秒）は残る。入れ替え直後によく使う期間を先に問い合わせ、キャッシュに載せる（ウォームアップ、未実装） 開発をブランチには分けない。本番側が書き換わると、共有していたページがブランチ固有の差分になり課金される。月次の全量書 き換えでは毎月 100 GB 超が差分になる試算。ブランチは実験用、日常の分離は compute で 18 / 24
19. Q3 数値をどう確かめるか 旧画面と新 API の数字を自動で照合する
20. Q3 数値をどう確かめるか — 旧画面の出力を固定し、新 API と同じ条件で自動照合する QuickSight の画面 → CSV 基準として固定 新 API → JSON 同じ検索語・期間・粒度で呼ぶ → 基準データと実装を、同じ PR では変更できない 「不一致になったので基準を書き換える」を CI で防ぐ 同じ丸め・同じ並びに整え 行ごとに自動で比較 → 一致 不一致 基準は旧画面の出力。ズレが出たら旧画面に合わせる QuickSight 固有の挙動も含め、指標の定義は変えない 実装済みの画面は、用意した照合条件をすべて通してから切り替える 照合条件 = 検索語・期間・粒度・属性の組み合わせ。代表値、表記揺れ、該当なし、期間の端、属性 1 値などを選ぶ 20 / 24
21. 照合で見つかったズレ：判定の解釈が 1 つ違うだけで、100 行中 98 行がずれた ランキングの対象は「popularity が閾値以上の語」だけ。その判定のしかたが、旧画面と 1 つ違っていた 生チョコトリュフ ↓ 最後に検索があった月（冬）の popularity は閾値以上 ／ 7 月は検索 0 回 最初の実装 7 月に検索があるか？ いいえ → 対象外 ↓ 旧画面 最後に検索があった月の popularity が閾値以上か？ はい → 対象 季節指数の表で 100 行中 98 行がずれた。SQL を読んでも気づけず、照合して初めて分かった。判定を旧画面に合わせ、3 表・ 300 個の数字がすべて一致 21 / 24
22. 切替前に準備できていることと、まだ分からないこと 切替済みの画面はまだ無い 準備できていること：画面ごとに切り替え、1 行で戻 せる 切替は画面単位。アプリの対応表に 1 行足すと自作画面、 消すと QuickSight の表示に戻る 戻せるのは表示の向き先だけ。QuickSight 側の表作りと 取り込みは撤去まで動かし続ける まだ分からないこと 本番トラフィックでの応答性能 継続運用（月次の入れ替え、compute の常時稼働） 障害時の対応 22 / 24
23. まとめ：Lakebase への BI 移行 Lakebase へ移行するにあたり、 何を格納するか、どう速くするか、数値をどう確かめるかを、 本番データを元に決めた Q1 何を格納するか Q2 どう速くするか Q3 数値をどう確かめるか Databricks の集計結果を格納し、 ブランチ上の同じデータで対象行を 1 割に 旧画面の出力を固定し、新 API と同じ条件 QuickSight 向けの事前計算は画面用 SQL 絞ったインデックスを試し、全単語を横断す で自動照合。ズレを解消してから切り替える で計算。転送は自前の COPY るランキングを 747 秒 → 0.7 秒 に短縮 ※ 本番切替前。ここで示したのは本番運用の成果ではなく、切替前の検証結果 Databricks に集計データがあるなら、Lakebase は BI 用 DB の選択肢になる 23 / 24