タイムトラベルはじめました 〜時をかける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
   

   View Slide

2. © ZOZO, Inc.
   株式会社ZOZO

   技術本部 ML・データ部 データ基盤ブロック

   

   

   塩崎 健弘

   数年前にデータ基盤をRedshiftからBigQueryに移行したことが
   きっかけで、データ基盤の開発運用をすることに。

   「ごはんですよ！」のオブジェは自作です。

   

   2
   

   View Slide

3. © ZOZO, Inc.
   目次

   ● データ基盤のインフラ紹介

   ● タイムトラベル機能

   ○ タイムトラベルの方法

   ○ タイムマシンの材料

   ○ タイムマシンの組み立てかた

   ● We’re hiring!!

   ● まとめ

   3
   

   View Slide

4. © ZOZO, Inc.
   データ基盤のインフラ & データの流れ紹介(データレイク)

   4
   オンプレ環境
   Dedicated
   Interconnect
   Dataflow
   (Batch)
   Dataflow
   (Streaming)
   Fluentd on GKE
   Cloud Pub/Sub
   BigQuery
   (全量・日次)
   BigQuery
   (差分・リアルタイム)
   BigQuery
   (全量・リアルタイム)
   SQL Server
   ・・・
   GCP
   

   View Slide

5. © ZOZO, Inc.
   現在情報が重要 vs 過去情報も重要

   5
   「現在」の情報が重要
   エンドユーザー データ分析者
   「過去」の情報も重要
   板挟み😥
   データベース
   

   View Slide

6. © ZOZO, Inc.
   というわけで、BigQuery上にタイムマシン作りました！！

   6
   

   View Slide

7. © ZOZO, Inc.
   タイムトラベルの方法

   ● 日次テーブルの参照方法

   ○ SELECT * FROM <データベース名>_daily.<テーブル名>
   ● リアルタイムテーブルの参照方法

   ○ SELECT * FROM <データベース名>_realtime.<テーブル名>
   ● ⭐NEW⭐ タイムトラベルテーブルの参照方法

   ○ SELECT * FROM <データベース名>_timetravel.<テーブル名>(<過去の日時>)
   ● データセット名の一部を変更し、テーブル名の後ろに(<過去の日時>)を指定

   ● 約1分単位の精度で過去データにアクセス可能

   7
   

   View Slide

8. © ZOZO, Inc.
   BigQueryネイティブのタイムマシン機能は未使用

   

   

   ● ネイティブ機能の記法

   ○ SELECT * FROM <テーブル名> FOR SYSTEM TIME AS OF <日時>
   ○ SELECT * FROM <テーブル名>@
   ● ネイティブ機能の問題点

   ○ 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
   

   View Slide

9. © ZOZO, Inc.
   タイムマシンの材料

   

   ● 日次スナップショット

   ○ 日次バッチでBigQueryへ転送完了したタイミングで生成

   ○ テーブル名の後ろに_yyyymmddを付与

   ● Change Trackingデータ

   ○ SQL ServerのChange Tracking機能を使って抜き出したデータ

   ○ いわゆるCDC(Change Data Capture)的なデータ

   ■ 注: 厳密には異なる

   9
   

   View Slide

10. © ZOZO, Inc.
    材料1: 日次スナップショット

    ● 日次バッチの完了タイミングで生成

    ● 基本的に全てのテーブルに対して生成

    ● テーブル名の末尾に_yyyymmddを付けてコピー

    10
    SQL Server BigQuery
    (全量・日次)
    ① Dataflowで転送
    BigQuery
    (全量・日次スナップショット )
    ② テーブルコピー
    

    View Slide

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
    

    View Slide

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で転送
    

    View Slide

13. © ZOZO, Inc.
    材料を組み立て

    1. タイムトラベルしたい日付の前日のテーブルを取得

    2. タイムトラベルしたい日時までのChange Trackingデータを取
    得

    3. ①と②を「いい感じ」にマージ

    13
    

    View Slide

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
    

    View Slide

15. © ZOZO, Inc.
    2. タイムトラベルしたい日時までの

    Change Trackingデータを取得

    
● 目的日時の48時間前〜目的日時のChange Trackingデータを取得

    ● なぜ「48時間前」なのか？

    ○ 日次スナップショットテーブルの作成処理の遅延を考慮

    ○ 当日に確実に日次スナップショット作成処理が終わるなら24時間で十分

    ○ 念の為に24時間分のバッファを設けている

    ● 注: ①と②のデータには重複する期間がある

    15
    ①のデータ

    ②のデータ

    目的日時

    目的日時 - 48時間
 目的日時の前日

    ※ 時刻は不定

    

    View Slide

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
    

    View Slide

17. © ZOZO, Inc.
    最後の仕上げ

    ● ここまでの処理(+エラーハンドリング)をまとめると200行くらいになる

    ● タイムトラベルするたびに長いSELECT文を書くのは煩雑

    ● Table Valued Functionを使って使い易いインターフェースに

    ○ CREATE TABLE FUNCTION <データベース名>_timetravel.<テーブル名>(past_time TIMESTAMP) AS (
    
    )
    ● 参考

    ○ Table functions

    https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions

    17
    

    View Slide

18. © ZOZO, Inc.
    タイムトラベル機能の注意点

    ● 未来にはタイムトラベルできません

    ● テーブル同士の整合性は担保されません

    ○ 外部キー制約が設定されているテーブルをJOINする時には要注意

    ● タイムトラベル可能な最古の日時は以下の2つのうち新しい方

    ○ テーブルスナップショットが作成され始めた翌日

    ○ Change Trackingデータを保存し始めた48時間後

    

    

    18
    

    View Slide

19. © ZOZO, Inc.
    ZOZO TECH BLOGも併せて御覧ください

    ● 完全版のクエリをTECH BLOGで近日公開予定

    ○ エラーハンドリング処理などが追加

    ● https://techblog.zozo.com/

    19
    

    View Slide

20. © ZOZO, Inc.
    We’re Hiring!!

    ● データ基盤を一緒に開発する仲間募集中

    ○ データパイプラインの構築が好きな人

    ○ データガバナンスに興味がある人

    ● https://hrmos.co/pages/zozotech/jobs/0000156

    20
    

    View Slide

21. © ZOZO, Inc.
    まとめ

    ● ZOZOのデータ基盤にタイムトラベル機能を実装しました

    ○ SELECT * FROM <データベース名>_timetravel.<テーブル名>(<過去の日時>)
    ● 日次スナップショットとChange Trackingデータを組み合わせて実現

    21
    BigQuery
    (全量・日次)
    BigQuery
    (差分・リアルタイム)
    BigQuery
    (全量・リアルタイム)
    BigQuery
    (全量・日次スナップショット )
    BigQuery
    (全量・タイムトラベル )
    ⭐NEW⭐
    

    View Slide

22. View Slide