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

by Takehiro Shiozaki

Slide 1

Slide 1 text

Slide 2

Slide 2 text

Slide 3

Slide 3 text

Slide 4

Slide 4 text

© ZOZO, Inc. データ基盤のインフラ & データの流れ紹介(データレイク)  4 オンプレ環境 Dedicated Interconnect Dataflow (Batch) Dataflow (Streaming) Fluentd on GKE Cloud Pub/Sub BigQuery (全量・日次) BigQuery (差分・リアルタイム) BigQuery (全量・リアルタイム) SQL Server ・・・ GCP

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

© ZOZO, Inc. タイムトラベルの方法  ● 日次テーブルの参照方法  ○ SELECT * FROM <データベース名>_daily.<テーブル名> ● リアルタイムテーブルの参照方法  ○ SELECT * FROM <データベース名>_realtime.<テーブル名> ● ⭐NEW⭐ タイムトラベルテーブルの参照方法  ○ SELECT * FROM <データベース名>_timetravel.<テーブル名>(<過去の日時>) ● データセット名の一部を変更し、テーブル名の後ろに(<過去の日時>)を指定  ● 約1分単位の精度で過去データにアクセス可能  7

Slide 8

Slide 8 text

© 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

Slide 9

Slide 9 text

© ZOZO, Inc. タイムマシンの材料    ● 日次スナップショット  ○ 日次バッチでBigQueryへ転送完了したタイミングで生成  ○ テーブル名の後ろに_yyyymmddを付与  ● Change Trackingデータ  ○ SQL ServerのChange Tracking機能を使って抜き出したデータ  ○ いわゆるCDC(Change Data Capture)的なデータ  ■ 注: 厳密には異なる  9

Slide 10

Slide 10 text

Slide 11

Slide 11 text

© 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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

Slide 14

Slide 14 text

© 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

Slide 15

Slide 15 text

© ZOZO, Inc. 2. タイムトラベルしたい日時までの  Change Trackingデータを取得   ● 目的日時の48時間前〜目的日時のChange Trackingデータを取得  ● なぜ「48時間前」なのか？  ○ 日次スナップショットテーブルの作成処理の遅延を考慮  ○ 当日に確実に日次スナップショット作成処理が終わるなら24時間で十分  ○ 念の為に24時間分のバッファを設けている  ● 注: ①と②のデータには重複する期間がある  15 ①のデータ  ②のデータ  目的日時  目的日時 - 48時間  目的日時の前日  ※ 時刻は不定 

Slide 16

Slide 16 text

© 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

Slide 17

Slide 17 text

© 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

Slide 18

Slide 18 text

© ZOZO, Inc. タイムトラベル機能の注意点  ● 未来にはタイムトラベルできません  ● テーブル同士の整合性は担保されません  ○ 外部キー制約が設定されているテーブルをJOINする時には要注意  ● タイムトラベル可能な最古の日時は以下の2つのうち新しい方  ○ テーブルスナップショットが作成され始めた翌日  ○ Change Trackingデータを保存し始めた48時間後      18

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Slide 21

Slide 21 text

© ZOZO, Inc. まとめ  ● ZOZOのデータ基盤にタイムトラベル機能を実装しました  ○ SELECT * FROM <データベース名>_timetravel.<テーブル名>(<過去の日時>) ● 日次スナップショットとChange Trackingデータを組み合わせて実現  21 BigQuery (全量・日次) BigQuery (差分・リアルタイム) BigQuery (全量・リアルタイム) BigQuery (全量・日次スナップショット ) BigQuery (全量・タイムトラベル ) ⭐NEW⭐

Slide 22

Slide 22 text

No content