タイムトラベルはじめました 〜時をかけるBigQuery〜 / Now serving Time Machine 〜BigQuery Which Leapt Through Time〜

2022/04/20 Data Engineering Meetup 【ZOZO × GMOペパボ】での発表内容です。
https://pepabo.connpass.com/event/242688/

Transcript

1. タイムトラベルはじめました
 〜時をかけるBigQuery〜
 2022/04/20 Data Engineering Meetup 【ZOZO × GMOペパボ】
 
 株式会社ZOZO
 技術本部 ML・データ部 データ基盤ブロック
 
 塩崎健弘 Copyright © ZOZO, Inc. 1
2. © ZOZO, Inc. 株式会社ZOZO
 技術本部 ML・データ部 データ基盤ブロック
 
 
 塩崎 健弘
 数年前にデータ基盤をRedshiftからBigQueryに移行したことが きっかけで、データ基盤の開発運用をすることに。
 「ごはんですよ！」のオブジェは自作です。
 
 2
3. © ZOZO, Inc. 目次
 • データ基盤のインフラ紹介
 • タイムトラベル機能
 ◦ タイムトラベルの方法
 ◦ タイムマシンの材料
 ◦ タイムマシンの組み立てかた
 • We’re hiring!!
 • まとめ
 3
4. © ZOZO, Inc. データ基盤のインフラ & データの流れ紹介(データレイク)
 4 オンプレ環境 Dedicated Interconnect Dataflow (Batch) Dataflow (Streaming) Fluentd on GKE Cloud Pub/Sub BigQuery (全量・日次) BigQuery (差分・リアルタイム) BigQuery (全量・リアルタイム) SQL Server ・・・ GCP
5. © ZOZO, Inc. 現在情報が重要 vs 過去情報も重要
 5 「現在」の情報が重要 エンドユーザー データ分析者 「過去」の情報も重要 板挟み😥 データベース
6. © ZOZO, Inc. というわけで、BigQuery上にタイムマシン作りました！！
 6
7. © ZOZO, Inc. タイムトラベルの方法
 • 日次テーブルの参照方法
 ◦ SELECT * FROM <データベース名>_daily.<テーブル名> • リアルタイムテーブルの参照方法
 ◦ SELECT * FROM <データベース名>_realtime.<テーブル名> • ⭐NEW⭐ タイムトラベルテーブルの参照方法
 ◦ SELECT * FROM <データベース名>_timetravel.<テーブル名>(<過去の日時>) • データセット名の一部を変更し、テーブル名の後ろに(<過去の日時>)を指定
 • 約1分単位の精度で過去データにアクセス可能
 7
8. © ZOZO, Inc. BigQueryネイティブのタイムマシン機能は未使用
 
 
 • ネイティブ機能の記法
 ◦ SELECT * FROM <テーブル名> FOR SYSTEM TIME AS OF <日時> ◦ SELECT * FROM <テーブル名>@<UNIX epoch time> • ネイティブ機能の問題点
 ◦ 7日以上過去にタイムトラベルできない
 • 最近GAになったスナップショットは？
 ◦ 7日以上過去にもタイムトラベルできる
 ◦ ただし、事前にスナップショットを取らないといけないので使い勝手が悪い
 ▪ 分単位でタイムトラベルしたい場合は、1分毎にスナップショットを取る必要あり
 • 参考
 ◦ Access historical data using time travel
 https://cloud.google.com/bigquery/docs/time-travel
 ◦ Introduction to table snapshots
 https://cloud.google.com/bigquery/docs/table-snapshots-intro
 8
9. © ZOZO, Inc. タイムマシンの材料
 
 • 日次スナップショット
 ◦ 日次バッチでBigQueryへ転送完了したタイミングで生成
 ◦ テーブル名の後ろに_yyyymmddを付与
 • Change Trackingデータ
 ◦ SQL ServerのChange Tracking機能を使って抜き出したデータ
 ◦ いわゆるCDC(Change Data Capture)的なデータ
 ▪ 注: 厳密には異なる
 9
10. © ZOZO, Inc. 材料1: 日次スナップショット
 • 日次バッチの完了タイミングで生成
 • 基本的に全てのテーブルに対して生成
 • テーブル名の末尾に_yyyymmddを付けてコピー
 10 SQL Server BigQuery (全量・日次) ① Dataflowで転送 BigQuery (全量・日次スナップショット ) ② テーブルコピー
11. © ZOZO, Inc. 材料2: Change Trackingデータ
 11 id price stock_num 1 10000 3 2 15000 10 商品マスタ id: 2のpriceをUPDATE id price stock_num 1 10000 3 2 10000 10 id: 3をINSERT id price stock_num 1 10000 3 2 10000 10 3 5000 2 id: 1をDELETE id price stock_num 2 10000 10 3 5000 2 Change ver. = 1 Change ver. = 2 Change ver. = 3 Change ver. = 4 • TransactionがCOMMITされるごとに
 Change versionが増加
 • 以下でChange ver. = 1から現在までの差分を取得
 ◦ SELECT * FROM CHANGETABLE(商品マスタ, 1) • 取得結果に含まれるもの
 ◦ ⭕ 更新された行の主キー
 ◦ ⭕ 変更タイプ(I, U, D)
 ◦ ⭕ 更新されたカラム
 ◦ ❌ 更新前後の値 (CDCはこれも取得可能)
 • 右図の更新処理をした時のCHANGETABLEの結果
 id operation columns 2 U price 3 I 1 D
12. © ZOZO, Inc. 材料2: Change Trackingデータ
 • 変更があった行の「主キー」と「変更タイプ」等が取得できる
 ◦ この主キーに対して、元テーブルをJOINして全カラムのデータを取得
 • リアルタイムにBigQueryにStreaming Insert
 • リアルタイム連携のために取得するシステムは構築済
 • 参考
 ◦ About Change Tracking (SQL Server)
 https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server?view=sql-server-ver15 
 ◦ ZOZOTOWNを支えるリアルタイムデータ連携基盤
 https://techblog.zozo.com/entry/real-time-data-linkage-infrastructure 
 
 12 SQL Server BigQuery (差分・リアルタイム) Fluentdで転送
13. © ZOZO, Inc. 材料を組み立て
 1. タイムトラベルしたい日付の前日のテーブルを取得
 2. タイムトラベルしたい日時までのChange Trackingデータを取 得
 3. ①と②を「いい感じ」にマージ
 13
14. © ZOZO, Inc. 1. タイムトラベルしたい日付の前日の
 スナップショットテーブルを取得
 
• なぜ「前日」なのか
 ◦ 日次スナップショットが作成されるのが00:00とは限らない
 ◦ 当日のスナップショットでは必要なデータが欠けている可能性あり
 • _TABLE_SUFFIX疑似列を利用
 ◦ `daily_snapshot.<テーブル名>_*` WHERE _TABLE_SUFFIX = <前日> • 参考 ◦ Query multiple tables using a wildcard table https://cloud.google.com/bigquery/docs/querying-wildcard-tables 14
15. © ZOZO, Inc. 2. タイムトラベルしたい日時までの
 Change Trackingデータを取得
 
• 目的日時の48時間前〜目的日時のChange Trackingデータを取得
 • なぜ「48時間前」なのか？
 ◦ 日次スナップショットテーブルの作成処理の遅延を考慮
 ◦ 当日に確実に日次スナップショット作成処理が終わるなら24時間で十分
 ◦ 念の為に24時間分のバッファを設けている
 • 注: ①と②のデータには重複する期間がある
 15 ①のデータ
 ②のデータ
 目的日時
 目的日時 - 48時間
 目的日時の前日
 ※ 時刻は不定

16. © ZOZO, Inc. 3. ①と②を「いい感じ」にマージ
 
 • ①と②には重複している期間があるため「いい感じ」にマージする必要
 • 最初に②に含まれる主キーに該当する行を①から削除
 ◦ ①と②の両方に同じ主キーのデータが含まれている場合は②を優先
 • 上記のデータと②からtype=D(DELETE)を除外したデータをUNION
 ◦ 同一主キーの行が複数存在する場合は最新版を優先
 16 id price stock_num 1 10000 3 2 15000 10 id operation price stock_num 2 U 10000 10 3 I 5000 2 1 D ① 日次スナップショット
 ② Change Tracking
 id price stock_num 2 10000 10 3 5000 2
17. © ZOZO, Inc. 最後の仕上げ
 • ここまでの処理(+エラーハンドリング)をまとめると200行くらいになる
 • タイムトラベルするたびに長いSELECT文を書くのは煩雑
 • Table Valued Functionを使って使い易いインターフェースに
 ◦ CREATE TABLE FUNCTION <データベース名>_timetravel.<テーブル名>(past_time TIMESTAMP) AS ( <past_timeにタイムトラベルするためのもの凄く長いSELECT文> ) • 参考
 ◦ Table functions
 https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions
 17
18. © ZOZO, Inc. タイムトラベル機能の注意点
 • 未来にはタイムトラベルできません
 • テーブル同士の整合性は担保されません
 ◦ 外部キー制約が設定されているテーブルをJOINする時には要注意
 • タイムトラベル可能な最古の日時は以下の2つのうち新しい方
 ◦ テーブルスナップショットが作成され始めた翌日
 ◦ Change Trackingデータを保存し始めた48時間後
 
 
 18
19. © ZOZO, Inc. ZOZO TECH BLOGも併せて御覧ください
 • 完全版のクエリをTECH BLOGで近日公開予定
 ◦ エラーハンドリング処理などが追加
 • https://techblog.zozo.com/
 19
20. © ZOZO, Inc. We’re Hiring!!
 • データ基盤を一緒に開発する仲間募集中
 ◦ データパイプラインの構築が好きな人
 ◦ データガバナンスに興味がある人
 • https://hrmos.co/pages/zozotech/jobs/0000156
 20
21. © ZOZO, Inc. まとめ
 • ZOZOのデータ基盤にタイムトラベル機能を実装しました
 ◦ SELECT * FROM <データベース名>_timetravel.<テーブル名>(<過去の日時>) • 日次スナップショットとChange Trackingデータを組み合わせて実現
 21 BigQuery (全量・日次) BigQuery (差分・リアルタイム) BigQuery (全量・リアルタイム) BigQuery (全量・日次スナップショット ) BigQuery (全量・タイムトラベル ) ⭐NEW⭐