ZOZOTOWNの事業を支えるBigQueryの話 / BigQuery behind ZOZOTOWN

Transcript

1. ZOZOTOWNの事業を支えるBigQuery
   の話

   株式会社ZOZOテクノロジーズ

   SRE部

   塩崎健弘

   Copyright © ZOZO Technologies, Inc.
   

   View Slide

2. © ZOZO Technologies, Inc.
   株式会社ZOZOテクノロジーズ

   SRE部

   塩崎 健弘

   2年前にRedshiftをBigQueryにマイグレーションしたことが
   きっかけで、データ基盤を整備する人になった。

   リモートワーク中に服を着ているかどうかは五分五分。

   

   2
   

   View Slide

3. © ZOZO Technologies, Inc.
   https://zozo.jp/

   ● 日本最大級のファッション通販サイト

   ● 1,300以上のショップ、7,400以上のブランドの取り扱い（ともに2019年12
   月末時点）

   ● 常時73万点以上の商品アイテム数と毎日平均3,200点以上の新着 商
   品を掲載

   ● 即日配送サービス

   ● ギフトラッピングサービス

   ● ツケ払い など

   3
   

   View Slide

4. © ZOZO Technologies, Inc.
   https://wear.jp/

   ● 日本最大級のファッションコーディネートアプリ

   ● 1,400万ダウンロード突破、コーディネート投稿総数は900万件以上（と
   もに2019年12月末時点）

   ● 全世界（App Store / Google Playが利用可能な全ての国）でダウンロー
   ドが可能

   ● 等身大の着こなしが支持を集め、10万人以上のフォロワーを持ち
   WEARISTAに認定された一般ユーザーも誕生

   4
   

   View Slide

5. © ZOZO Technologies, Inc.
   https://zozo.jp/multisize/

   

   ● 身長と体重を選択するだけで理想のサイズの商品が見つかる新しい洋
   服の買い方

   ● 2019年秋冬アイテムから、人気ブランドのマルチサイズアイテムを販売
   開始

   【参加企業】

   　株式会社アーバンリサーチ、株式会社ストライプインターナショナル、

   　株式会社デイトナ・インターナショナル、株式会社パル、株式会社ビームス、

   　株式会社ベイクルーズ、MARK STYLER株式会社、リーバイ・ストラウス ジャパン株式会社　　など

   5
   

   View Slide

6. © ZOZO Technologies, Inc.
   https://zozo.jp/zozomat/

   

   ● お客様の足を3Dで計測するために開発された計測用マット

   ● 計測情報をもとに、靴の推奨サイズを提案

   ● 2020年春よりNIKEやCONVERSEなどの約100アイテムに対応（対象商
   品は順次拡充予定）

   

   6
   

   View Slide

7. © ZOZO Technologies, Inc.
   目次

   1. データ基盤紹介

   2. 事例紹介

   2.1. Amazon Redshift→BigQuery

   2.2. オンプレDWH vs クラウドDWH

   2.3. BIツールの使い分け

   2.4. Looker導入

   2.5. 懺悔

   3. ここが辛いよBigQuery

   4. 構築中のリアルタイム系データ基盤紹介

   5. まとめ

   7
   

   View Slide

8. © ZOZO Technologies, Inc.
   目次

   1. データ基盤紹介

   2. 事例紹介

   2.1. Amazon Redshift→BigQuery

   2.2. オンプレDWH vs クラウドDWH

   2.3. BIツールの使い分け

   2.4. Looker導入

   2.5. 懺悔

   3. ここが辛いよBigQuery

   4. 構築中のリアルタイム系データ基盤紹介

   5. まとめ

   8
   

   View Slide

9. © ZOZO Technologies, Inc.
   データ基盤紹介

   9
   ・・・
   基幹DBs
   中間DB BigQuery
   Power BI
   AI
   

   View Slide

10. © ZOZO Technologies, Inc.
    データ基盤紹介

    10
    ・・・
    基幹DBs
    中間DB BigQuery
    Power BI
    AI
    ETLツール: bcp
    ● SQL Server専用のバルクコピーツール
    ● バイナリ形式でインプット/アウトプットできるのでとにかく速い
    ● 昔はWindows版しかなかったけど、最近はUbuntu版もある
    ● Linuxコンテナ上で実行
    ワークフローエンジン: Digdag
    ● TreasureData製のOSS
    ● 他のワークフローエンジンと比較したメリットはDAGをYAMLで書くシンプルさ
    ● 実はZOZOテクノロジーズにはコントリビューターが7人もいる
    ● (ちなみに)最近まではWindowsタスクスケジューラー + VBScriptで頑張ってた
    インフラ
    ● EC2上にDigdagクラスタを組んで運用
    ● 一部のtaskはFargateに投げている
    ● Fargate 1.4でストレージ系に大きな機能拡張が入ったので、EC2をなくせそう
    

    View Slide

11. © ZOZO Technologies, Inc.
    データ基盤紹介

    11
    ・・・
    基幹DBs
    中間DB BigQuery
    Power BI
    AI
    ETLツール: Embulk
    ● Treasure Data製のOSS
    ● プラグイン構造で多種多様なデータソースに対応できる
    ● 転送設定をYAMLで書く
    ● 秘密情報(個人情報など)はここでNULL置換 or ハッシュ化
    ワークフローエンジン: Digdag
    (略)
    インフラ
    ● EC2上にオートスケーリングするDigdagクラスターを組んで運用
    ● 昔Redshiftを使っていた時の名残でawsに置いてある
    

    View Slide

12. © ZOZO Technologies, Inc.
    データ基盤紹介

    12
    ・・・
    基幹DBs
    中間DB BigQuery
    Power BI
    AI
    Power BI
    ● オフィシャルに導入されているBIツール
    ● BI専門チームがダッシュボードを作成
    ● 読み出し専用アカウントは全社員が持っている
    ● 全社的なKPI(売上など)を共有
    Looker
    ● 他社ではBIツールとして利用されることが多い
    ● ZOZOではデータガバナンスツールとして利用
    ● 後で詳しく説明します
    

    View Slide

13. © ZOZO Technologies, Inc.
    データ基盤紹介

    13
    ・・・
    基幹DBs
    中間DB BigQuery
    Power BI
    AI
    検索結果パーソナライズ
    ● ユーザー毎に以下を計算
    ○ 新着商品に対する感度
    ○ セール商品に対する感度
    ○ etc…
    ● 上記の特徴量と商品情報を組み合わせることで、
    ユーザー毎のおすすめ順を生成
    

    View Slide

14. © ZOZO Technologies, Inc.
    データ基盤紹介

    14
    ・・・
    基幹DBs
    中間DB BigQuery
    Power BI
    AI
    類似アイテム検索
    ● 以下のワークフローをCloud Composer + GKEで実行
    ● 物体検出→特徴量空間にマッピング→最近傍探索
    プレスリリース
    ZOZOTOWN、AIを活用し、閲覧商品と似ている商品を検索できる「類似アイテム検索機
    能」を本日より導入
    内部の仕組み
    近似最近傍探索Indexを作るワークフロー
    

    View Slide

15. © ZOZO Technologies, Inc.
    目次

    1. データ基盤紹介

    2. 事例紹介

    2.1. Amazon Redshift→BigQuery

    2.2. オンプレDWH vs クラウドDWH

    2.3. BIツールの使い分け

    2.4. Looker導入

    2.5. 懺悔

    3. ここが辛いよBigQuery

    4. 構築中のリアルタイム系データ基盤紹介

    5. まとめ

    15
    

    View Slide

16. © ZOZO Technologies, Inc.
    Amazon Redshift→Google BigQuery

    16
    ● 昔はRedshiftをDWHとして使っていた

    ○ 当時のワークロードは定期レポートがほとんど

    ■ 日次レポート・月次レポート

    ● ZOZO研究所発足のタイミングでBigQueryに移行

    ○ 研究開発: TRY&ERRORのサイクルが早い

    ○ 事前にSORTKEYやDISTKEYを決められない

    ○ INDEXがなくても力技で高速に計算できるDWHに乗り換え

    ○ あと、当時Cloud TPUとかが流行ってた

    

    View Slide

17. © ZOZO Technologies, Inc.
    Amazon Redshift→Google BigQuery

    17
    詳しくはこちら(去年のデブサミで発表しました)

    https://speakerdeck.com/shiozaki/moving-zozotown-dwh-from-redshift-to-bigquery
    

    View Slide

18. © ZOZO Technologies, Inc.
    目次

    1. データ基盤紹介

    2. 事例紹介

    2.1. Amazon Redshift→BigQuery

    2.2. オンプレDWH vs クラウドDWH

    2.3. BIツールの使い分け

    2.4. Looker導入

    2.5. 懺悔

    3. ここが辛いよBigQuery

    4. 構築中のリアルタイム系データ基盤紹介

    5. まとめ

    18
    

    View Slide

19. © ZOZO Technologies, Inc.
    オンプレDHW vs クラウドDWH

    19
    ・・・
    基幹DBs
    中間DB BigQuery
    オンプレ
    DWH
    ● 実はオンプレにもDWHがいる
    ● 初期導入時期
    ○ Redshiftよりもさらに前
    ● 用途
    ○ メルマガやPUSH配信のためのデータマート
    ● 昔は有名だったアレの後続機の後続機
    N
    

    View Slide

20. © ZOZO Technologies, Inc.
    オンプレDHW vs クラウドDWH

    20
    ● 圧倒的にクラウドの運用が楽

    ○ クラウドだと一瞬で終わることに時間を取られる

    ■ スイッチの不調とか

    ■ バックアップ戦略とか

    ○ クラウドの容量が無限のストレージは本当に便利

    

    ● 一回買ったら数年間使うのは辛い

    ○ 念の為に大きめのDWHを買う

    ○ 壊れたら直すが基本

    

    →クラウドDWHでは気にしなくても良いコストが多すぎる

    

    View Slide

21. © ZOZO Technologies, Inc.
    目次

    1. データ基盤紹介

    2. 事例紹介

    2.1. Amazon Redshift→BigQuery

    2.2. オンプレDWH vs クラウドDWH

    2.3. BIツールの使い分け

    2.4. Looker導入

    2.5. 懺悔

    3. ここが辛いよBigQuery

    4. 構築中のリアルタイム系データ基盤紹介

    5. まとめ

    21
    

    View Slide

22. © ZOZO Technologies, Inc.
    BIツールの使い分け

    22
    ● Power BI

    ○ オフィシャルに導入されているBI

    ○ 高度なダッシュボードや全社に共有するダッシュボード

    ● Looker

    ○ BIツールというよりは、データガバナンスツール(この後、詳細に)

    ● Redash

    ○ SQLを書けるエンジニアチームが自力でホスティングしている x N

    ○ バージョンアップに追従するのが大変

    ○ グラフ化はしたいけど、BIチームに頼むと時間が掛かる時の「つなぎ」

    ● Google SpreadSheet

    ○ やっぱりExcel系は根強い人気がある

    ○ 簡単なGoogle App Scriptで定期更新可能

    ○ SpreadSheetの他データとVLOOKUPしたい時に

    

    View Slide

23. © ZOZO Technologies, Inc.
    目次

    1. データ基盤紹介

    2. 事例紹介

    2.1. Amazon Redshift→BigQuery

    2.2. オンプレDWH vs クラウドDWH

    2.3. BIツールの使い分け

    2.4. Looker導入

    2.5. 懺悔

    3. ここが辛いよBigQuery

    4. 構築中のリアルタイム系データ基盤紹介

    5. まとめ

    23
    

    View Slide

24. © ZOZO Technologies, Inc.
    Looker導入

    24
    SQLでデータ集計処理を記述(数百ファイル)していたことの問題点

    ● 車輪(クエリ)の再発明の量産

    ○ SQLはWHERE句のみを切り出して再利用することができない

    ○ いまならUDFを作れるので、ある程度は解決できるかも

    ● KPIの定義が役職によって異なる

    ○ 同じ「取扱高」という言葉でも人によって定義が違う

    ○ 月跨ぎの返品・予約商品をどうするか？

    ● 〇〇_idのハードコード

    ○ 現金払いはpay_type_id=10, クレカ払いはpay_type_id=98

    

    → データガバナンスツールとしての

    　　Lookerを導入

    

    

    View Slide

25. © ZOZO Technologies, Inc.
    Looker導入

    25
    ● LookMLによる集計定義の記述

    ○ WHERE句のコピペ防止

    ○ 無機質な〇〇_id=10に名前をつける

    ○ LookMLをGitでバージョン管理

    ● ETLバッチとLookerの連携

    ○ ETLの完了タイミングでPubSubにPublish

    ○ Looker基盤側のワークフローエンジンからLookerのAPIを呼び出す

    ○ データマートの更新処理をLookerが実行

    dimension: test_account_tag {
    case: {
    when: {
    sql: ${member_id} IN ( 12345,
    23456,
    34567,
    45678,
    56789 ) ;;
    label: "テストアカウント"
    }
    }
    }
    

    View Slide

26. © ZOZO Technologies, Inc.
    Looker導入

    26
    詳しくはこちらに

    

    https://techblog.zozo.com/entry/

    looker-data-processing-improvement

    

    View Slide

27. © ZOZO Technologies, Inc.
    目次

    1. データ基盤紹介

    2. 事例紹介

    2.1. Amazon Redshift→BigQuery

    2.2. オンプレDWH vs クラウドDWH

    2.3. BIツールの使い分け

    2.4. Looker導入

    2.5. 懺悔

    3. ここが辛いよBigQuery

    4. 構築中のリアルタイム系データ基盤紹介

    5. まとめ

    27
    

    View Slide

28. © ZOZO Technologies, Inc.
    懺悔

    28
    ・・・
    基幹DBs
    中間DB Redshift
    S3
    Redshift時代(2年以上昔)

    

    View Slide

29. © ZOZO Technologies, Inc.
    懺悔

    29
    ・・・
    基幹DBs
    中間DB Redshift
    S3
    Redshift時代(2年以上昔)

    そこそこ分かる
    存在は知っている
    ❓❓
    ❓
    

    View Slide

30. © ZOZO Technologies, Inc.
    懺悔

    30
    ・・・
    基幹DBs
    中間DB Redshift
    S3
    Redshift時代(2年以上昔)

    そこそこ分かる
    存在は知っている
    秘境

    

    View Slide

31. © ZOZO Technologies, Inc.
    懺悔

    31
    ・・・
    基幹DBs
    中間DB Redshift
    S3
    私が犯した罪: 取りやすいところからとる

    そこそこ分かる
    存在は知っている
    秘
    境

    BigQuery
    

    View Slide

32. © ZOZO Technologies, Inc.
    懺悔

    32
    ● データレイクだと思っていたものがデータレイクではなかった

    ○ データレイク = 元のデータを「無加工で」コピーしたもの

    

    ● 元のDBと比較した時に欠損や不整合が発生

    ○ 一部の特殊文字が化ける or 消える

    ○ 一部のタイムスタンプが9時間ずれる or 8時間ずれる

    ○ 一部のカラムが消えてる

    

    ● 「自称」データレイクを信じてはいけない

    ○ 最上流の信頼できるDBからデータを取得するべき

    ❓❓謎の変換処理❓❓
    

    View Slide

33. © ZOZO Technologies, Inc.
    懺悔

    33
    参考: ゆずたそさんのブログ

    https://yuzutas0.hatenablog.com/entry/2018/12/04/190000

    

    View Slide

34. © ZOZO Technologies, Inc.
    目次

    1. データ基盤紹介

    2. 事例紹介

    2.1. Amazon Redshift→BigQuery

    2.2. オンプレDWH vs クラウドDWH

    2.3. BIツールの使い分け

    2.4. Looker導入

    2.5. 懺悔

    3. ここが辛いよBigQuery

    4. 構築中のリアルタイム系データ基盤紹介

    5. まとめ

    34
    

    View Slide

35. © ZOZO Technologies, Inc.
    ここが辛いよBigQuery

    35
    ● 使い勝手が良すぎていつの間にか・・・

    ○ 噂を聞きつけて色々な人が使い出す

    ■ DWHの扱いに慣れたプロ

    ■ OLTPなクエリは書けるけど分析クエリは初心者

    ■ エクセル(=表計算ソフト)大好き人間

    ○ 気軽なSELECT *を実行して一瞬で数千円が溶ける

    ● 対策

    ○ auditログから無駄遣いしてる人を見つけて「BQ違反者講習」

    ○ 初心者向け資料を社内コンフルに

    ○ Service Usage APIでスキャン量が多すぎるクエリをエラーにする

    

    

    View Slide

36. © ZOZO Technologies, Inc.
    ここが辛いよBigQuery

    36
    ● 使い勝手が良すぎていつの間にか・・・

    ○ 噂を聞きつけて色々な人が使い出す

    ■ DWHの扱いに慣れたプロ

    ■ OLTPなクエリは書けるけど分析クエリは初心者

    ■ エクセル(=表計算ソフト)大好き人間

    ○ 気軽なSELECT *を実行して一瞬で数千円が溶ける

    ● 対策

    ○ auditログから無駄遣いしてる人を見つけて「BQ違反者講習」

    ○ 初心者向け資料を社内コンフルに

    ○ Service Usage APIでスキャン量が多すぎるクエリをエラーにする

    

    

    

    View Slide

37. © ZOZO Technologies, Inc.
    ここが辛いよBigQuery

    37
    ● 使い勝手が良すぎていつの間にか・・・

    ○ 噂を聞きつけて色々な人が使い出す

    ■ DWHの扱いに慣れたプロ

    ■ OLTPなクエリは書けるけど分析クエリは初心者

    ■ エクセル(=表計算ソフト)大好き人間

    ○ 気軽なSELECT *を実行して一瞬で数千円が溶ける

    ● 対策

    ○ auditログから無駄遣いしてる人を見つけて「BQ違反者講習」

    ○ 初心者向け資料を社内コンフルに

    ○ Service Usage APIでスキャン量が多すぎるクエリをエラーにする

    

    

    

    View Slide

38. © ZOZO Technologies, Inc.
    ここが辛いよBigQuery

    38
    ● セキュリティが・・・

    ○ IAMで設定できる最小粒度はデータセット ※GAになっている機能限定

    ○ BigQuery単体ではIPアドレス制限が出来ない

    

    ● 対策

    ○ 権限境界とGCPのプロジェクト境界の一致

    ○ Table ACL(Beta)でテーブル単位での権限付与

    ○ 承認済みビュー or Column Level Secutiry(Beta)を使って列単位での権限付与

    ○ VPC Service Controlsを使ってIPアドレス制限

    ■ https://medium.com/google-cloud-jp/gcp-secure-protection-with-vpc-service-controls-part-1-85643fbfb674

    

    

    View Slide

39. © ZOZO Technologies, Inc.
    ここが辛いよBigQuery

    39
    ● セキュリティが・・・

    ○ IAMで設定できる最小粒度はデータセット ※GAになっている機能限定

    ○ BigQuery単体ではIPアドレス制限が出来ない

    

    ● 対策

    ○ 権限境界とGCPのプロジェクト境界の一致

    ○ Table ACL(Beta)でテーブル単位での権限付与

    ○ 承認済みビュー or Column Level Secutiry(Beta)を使って列単位での権限付与

    ○ VPC Service Controlsを使ってIPアドレス制限

    ■ https://medium.com/google-cloud-jp/gcp-secure-protection-with-vpc-service-controls-part-1-85643fbfb674

    

    

    View Slide

40. © ZOZO Technologies, Inc.
    ここが辛いよBigQuery

    40
    ● コストの予測が・・・

    ○ 使った分だけ課金 = いくらになるか事前に予測しづらい

    ● 対策

    ○ Flat-rate pricing

    ○ コンピューティングコスト(5USD/TB)が固定になる

    ■ 10,000USD/month〜

    ○ 注: バースト性能が欲しいときはFlex Slot(今月にGA)と併用

    

    

    

    View Slide

41. © ZOZO Technologies, Inc.
    目次

    1. データ基盤紹介

    2. 事例紹介

    2.1. Amazon Redshift→BigQuery

    2.2. オンプレDWH vs クラウドDWH

    2.3. BIツールの使い分け

    2.4. Looker導入

    2.5. 懺悔

    3. ここが辛いよBigQuery

    4. 構築中のリアルタイム系データ基盤紹介

    5. まとめ

    41
    

    View Slide

42. © ZOZO Technologies, Inc.
    リアルタイム系データ基盤紹介(構築中)

    42
    ● データ連携の頻度=1日1回

    ○ 昔はそれでも問題なかった(日次レポートがメイン)

    ○ AI活用が盛んになったらニアリアルタイムの基盤が欲しくなった

    ● 更新タイムスタンプが格納された列を使った差分更新を検討

    ○ https://speakerdeck.com/kyontan/number-dpct-20190416

    ● 以下が多いため断念

    ○ 更新タイムスタンプ列が存在しないテーブル

    ○ 存在しても更新されていないテーブル

    ○ 物理削除されているテーブル

    

    

    

    

    View Slide

43. © ZOZO Technologies, Inc.
    リアルタイム系データ基盤紹介(構築中)

    43
    ● SQL ServerのChange Tracking機能を利用

    ● Change Tracking機能 is CDC「的な」機能

    ○ SQLを使ってPULL型で差分データを取得

    ○ 変更のあった行の主キーのみしか取得できない

    ■ PULLする間隔よりも高頻度で更新が実行されると、最新の情報しか取得できない

    ○ 差分情報管理テーブルには同期的に書きこまれる

    ■ COMMITされたトランザクションは確実にChange Trackingで読み出せる

    ■ CDCはeventual consistentで読み出せる

    ■ 更新負荷がやや上がる(INDEXを1つ追加するより少し低いくらいの負荷)

    

    https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server

    

    View Slide

44. © ZOZO Technologies, Inc.
    リアルタイム系データ基盤紹介(構築中)

    44
    

    View Slide

45. © ZOZO Technologies, Inc.
    リアルタイム系データ基盤紹介(構築中)

    45
    ● Change TrackingデータをPubSubにPublish
    ○ SELECT * FROM テーブル
    WHERE 主キー IN (変更のあった主ー)
    ● HA構成にするためにfluentdサーバーを2台設置
    ○ Active Active構成
    ○ 2台のfluentd間での協調動作はしない
    ● PubSubには重複したデータが入る
    

    View Slide

46. © ZOZO Technologies, Inc.
    リアルタイム系データ基盤紹介(構築中)

    46
    ● 重複排除をDataflowで行う
    ○ withIdAttributeで処理するのでウインドウによる遅延なし
    ○ PubsubIO.readMessagesWithAttributes()
    .withIdAttribute("id")
    .fromSubscription(サブスクリプション名)
    ● fluentdで行の値からハッシュ値を生成
    ○ id = hash(col1 + col2 + ... + colN)
    ● 結局下流のPubSubがat least onceじゃない？
    ○ 流石に全データ重複しているとその後の処理が辛い
    

    View Slide

47. © ZOZO Technologies, Inc.
    リアルタイム系データ基盤紹介(構築中)

    47
    ● 差分データをBigQueryにStreaming Insert
    ● Dynamic Destination機能を活用
    ○ 1つのDataflowインスタンスで複数テーブルへ出力
    ○ Google提供のテンプレートを独自でカスタマイズ
    ○ https://www.case-k.jp/entry/2020/06/25/150527
    ● 日次連携のテーブルとマージするVIEWを用意
    ○ SQL Serverと同等のデータを再現
    

    View Slide

48. © ZOZO Technologies, Inc.
    リアルタイム系データ基盤紹介(構築中)

    48
    ● 提供する読み出し口1
    ○ PubSubのSubscription
    ○ 差分情報のみ
    ○ 生の差分データ(JSON形式)
    ● 提供する読み出し口2
    ○ BigQueryのテーブル
    ○ 差分情報 + 全量情報
    ○ SQL文で取得
    ① ②
    

    View Slide

49. © ZOZO Technologies, Inc.
    目次

    1. データ基盤紹介

    2. 事例紹介

    2.1. Amazon Redshift→BigQuery

    2.2. オンプレDWH vs クラウドDWH

    2.3. BIツールの使い分け

    2.4. Looker導入

    2.5. 懺悔

    3. ここが辛いよBigQuery

    4. 構築中のリアルタイム系データ基盤紹介

    5. まとめ

    49
    

    View Slide

50. © ZOZO Technologies, Inc.
    まとめ

    50
    ● データ基盤にBigQueryを使っています

    ● オンプレDWHよりもクラウドDWHの方が圧倒的に楽

    ● BigQueryはクラウドDWHの中でも特に抽象度が高い

    ○ 運用で気にするべきポイントが変わるので注意

    ■ 従来: CPU・ディスク使用率

    ■ BigQuery: クエリのレスポンスタイム・費用

    ○ 当初は弱かったセキュリティ機能も最近はまともになってきてる

    ● リアルタイム系の基盤も作ってます

    

    

    View Slide

51. View Slide