Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

Takehiro Shiozaki

July 15, 2020
Tweet

More Decks by Takehiro Shiozaki

Other Decks in Technology

Transcript

  1. © ZOZO Technologies, Inc. https://zozo.jp/
 • 日本最大級のファッション通販サイト
 • 1,300以上のショップ、7,400以上のブランドの取り扱い(ともに2019年12 月末時点)


    • 常時73万点以上の商品アイテム数と毎日平均3,200点以上の新着 商 品を掲載
 • 即日配送サービス
 • ギフトラッピングサービス
 • ツケ払い など
 3
  2. © ZOZO Technologies, Inc. https://wear.jp/
 • 日本最大級のファッションコーディネートアプリ
 • 1,400万ダウンロード突破、コーディネート投稿総数は900万件以上(と もに2019年12月末時点)


    • 全世界(App Store / Google Playが利用可能な全ての国)でダウンロー ドが可能
 • 等身大の着こなしが支持を集め、10万人以上のフォロワーを持ち WEARISTAに認定された一般ユーザーも誕生
 4
  3. © ZOZO Technologies, Inc. https://zozo.jp/multisize/
 
 • 身長と体重を選択するだけで理想のサイズの商品が見つかる新しい洋 服の買い方
 •

    2019年秋冬アイテムから、人気ブランドのマルチサイズアイテムを販売 開始
 【参加企業】
  株式会社アーバンリサーチ、株式会社ストライプインターナショナル、
  株式会社デイトナ・インターナショナル、株式会社パル、株式会社ビームス、
  株式会社ベイクルーズ、MARK STYLER株式会社、リーバイ・ストラウス ジャパン株式会社  など
 5
  4. © 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
  5. © 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
  6. © 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をなくせそう
  7. © ZOZO Technologies, Inc. データ基盤紹介
 11 ・・・ 基幹DBs 中間DB BigQuery

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

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

    Power BI AI 検索結果パーソナライズ • ユーザー毎に以下を計算 ◦ 新着商品に対する感度 ◦ セール商品に対する感度 ◦ etc… • 上記の特徴量と商品情報を組み合わせることで、 ユーザー毎のおすすめ順を生成
  10. © ZOZO Technologies, Inc. データ基盤紹介
 14 ・・・ 基幹DBs 中間DB BigQuery

    Power BI AI 類似アイテム検索 • 以下のワークフローをCloud Composer + GKEで実行 • 物体検出→特徴量空間にマッピング→最近傍探索 プレスリリース ZOZOTOWN、AIを活用し、閲覧商品と似ている商品を検索できる「類似アイテム検索機 能」を本日より導入 内部の仕組み 近似最近傍探索Indexを作るワークフロー
  11. © 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
  12. © ZOZO Technologies, Inc. Amazon Redshift→Google BigQuery
 16 • 昔はRedshiftをDWHとして使っていた


    ◦ 当時のワークロードは定期レポートがほとんど
 ▪ 日次レポート・月次レポート
 • ZOZO研究所発足のタイミングでBigQueryに移行
 ◦ 研究開発: TRY&ERRORのサイクルが早い
 ◦ 事前にSORTKEYやDISTKEYを決められない
 ◦ INDEXがなくても力技で高速に計算できるDWHに乗り換え
 ◦ あと、当時Cloud TPUとかが流行ってた

  13. © 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
  14. © ZOZO Technologies, Inc. オンプレDHW vs クラウドDWH
 19 ・・・ 基幹DBs

    中間DB BigQuery オンプレ DWH • 実はオンプレにもDWHがいる • 初期導入時期 ◦ Redshiftよりもさらに前 • 用途 ◦ メルマガやPUSH配信のためのデータマート • 昔は有名だったアレの後続機の後続機 N
  15. © ZOZO Technologies, Inc. オンプレDHW vs クラウドDWH
 20 • 圧倒的にクラウドの運用が楽


    ◦ クラウドだと一瞬で終わることに時間を取られる
 ▪ スイッチの不調とか
 ▪ バックアップ戦略とか
 ◦ クラウドの容量が無限のストレージは本当に便利
 
 • 一回買ったら数年間使うのは辛い
 ◦ 念の為に大きめのDWHを買う
 ◦ 壊れたら直すが基本
 
 →クラウドDWHでは気にしなくても良いコストが多すぎる

  16. © 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
  17. © ZOZO Technologies, Inc. BIツールの使い分け
 22 • Power BI
 ◦

    オフィシャルに導入されているBI
 ◦ 高度なダッシュボードや全社に共有するダッシュボード
 • Looker
 ◦ BIツールというよりは、データガバナンスツール(この後、詳細に)
 • Redash
 ◦ SQLを書けるエンジニアチームが自力でホスティングしている x N
 ◦ バージョンアップに追従するのが大変
 ◦ グラフ化はしたいけど、BIチームに頼むと時間が掛かる時の「つなぎ」
 • Google SpreadSheet
 ◦ やっぱりExcel系は根強い人気がある
 ◦ 簡単なGoogle App Scriptで定期更新可能
 ◦ SpreadSheetの他データとVLOOKUPしたい時に

  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. まとめ
 23
  19. © ZOZO Technologies, Inc. Looker導入
 24 SQLでデータ集計処理を記述(数百ファイル)していたことの問題点
 • 車輪(クエリ)の再発明の量産
 ◦

    SQLはWHERE句のみを切り出して再利用することができない
 ◦ いまならUDFを作れるので、ある程度は解決できるかも
 • KPIの定義が役職によって異なる
 ◦ 同じ「取扱高」という言葉でも人によって定義が違う
 ◦ 月跨ぎの返品・予約商品をどうするか?
 • 〇〇_idのハードコード
 ◦ 現金払いはpay_type_id=10, クレカ払いはpay_type_id=98
 
 → データガバナンスツールとしての
   Lookerを導入
 

  20. © 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: "テストアカウント" } } }
  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. まとめ
 27
  22. © ZOZO Technologies, Inc. 懺悔
 29 ・・・ 基幹DBs 中間DB Redshift

    S3 Redshift時代(2年以上昔)
 そこそこ分かる 存在は知っている ❓❓ ❓
  23. © ZOZO Technologies, Inc. 懺悔
 30 ・・・ 基幹DBs 中間DB Redshift

    S3 Redshift時代(2年以上昔)
 そこそこ分かる 存在は知っている 秘境

  24. © ZOZO Technologies, Inc. 懺悔
 31 ・・・ 基幹DBs 中間DB Redshift

    S3 私が犯した罪: 取りやすいところからとる
 そこそこ分かる 存在は知っている 秘 境
 BigQuery
  25. © ZOZO Technologies, Inc. 懺悔
 32 • データレイクだと思っていたものがデータレイクではなかった
 ◦ データレイク

    = 元のデータを「無加工で」コピーしたもの
 
 • 元のDBと比較した時に欠損や不整合が発生
 ◦ 一部の特殊文字が化ける or 消える
 ◦ 一部のタイムスタンプが9時間ずれる or 8時間ずれる
 ◦ 一部のカラムが消えてる
 
 • 「自称」データレイクを信じてはいけない
 ◦ 最上流の信頼できるDBからデータを取得するべき
 ❓❓謎の変換処理❓❓
  26. © 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
  27. © ZOZO Technologies, Inc. ここが辛いよBigQuery
 35 • 使い勝手が良すぎていつの間にか・・・
 ◦ 噂を聞きつけて色々な人が使い出す


    ▪ DWHの扱いに慣れたプロ
 ▪ OLTPなクエリは書けるけど分析クエリは初心者
 ▪ エクセル(=表計算ソフト)大好き人間
 ◦ 気軽なSELECT *を実行して一瞬で数千円が溶ける
 • 対策
 ◦ auditログから無駄遣いしてる人を見つけて「BQ違反者講習」
 ◦ 初心者向け資料を社内コンフルに
 ◦ Service Usage APIでスキャン量が多すぎるクエリをエラーにする
 

  28. © ZOZO Technologies, Inc. ここが辛いよBigQuery
 36 • 使い勝手が良すぎていつの間にか・・・
 ◦ 噂を聞きつけて色々な人が使い出す


    ▪ DWHの扱いに慣れたプロ
 ▪ OLTPなクエリは書けるけど分析クエリは初心者
 ▪ エクセル(=表計算ソフト)大好き人間
 ◦ 気軽なSELECT *を実行して一瞬で数千円が溶ける
 • 対策
 ◦ auditログから無駄遣いしてる人を見つけて「BQ違反者講習」
 ◦ 初心者向け資料を社内コンフルに
 ◦ Service Usage APIでスキャン量が多すぎるクエリをエラーにする
 
 

  29. © ZOZO Technologies, Inc. ここが辛いよBigQuery
 37 • 使い勝手が良すぎていつの間にか・・・
 ◦ 噂を聞きつけて色々な人が使い出す


    ▪ DWHの扱いに慣れたプロ
 ▪ OLTPなクエリは書けるけど分析クエリは初心者
 ▪ エクセル(=表計算ソフト)大好き人間
 ◦ 気軽なSELECT *を実行して一瞬で数千円が溶ける
 • 対策
 ◦ auditログから無駄遣いしてる人を見つけて「BQ違反者講習」
 ◦ 初心者向け資料を社内コンフルに
 ◦ Service Usage APIでスキャン量が多すぎるクエリをエラーにする
 
 

  30. © 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
 

  31. © 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
 

  32. © ZOZO Technologies, Inc. ここが辛いよBigQuery
 40 • コストの予測が・・・
 ◦ 使った分だけ課金

    = いくらになるか事前に予測しづらい
 • 対策
 ◦ Flat-rate pricing
 ◦ コンピューティングコスト(5USD/TB)が固定になる
 ▪ 10,000USD/month〜
 ◦ 注: バースト性能が欲しいときはFlex Slot(今月にGA)と併用
 
 

  33. © 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
  34. © ZOZO Technologies, Inc. リアルタイム系データ基盤紹介(構築中)
 42 • データ連携の頻度=1日1回
 ◦ 昔はそれでも問題なかった(日次レポートがメイン)


    ◦ AI活用が盛んになったらニアリアルタイムの基盤が欲しくなった
 • 更新タイムスタンプが格納された列を使った差分更新を検討
 ◦ https://speakerdeck.com/kyontan/number-dpct-20190416
 • 以下が多いため断念
 ◦ 更新タイムスタンプ列が存在しないテーブル
 ◦ 存在しても更新されていないテーブル
 ◦ 物理削除されているテーブル
 
 
 

  35. © 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

  36. © ZOZO Technologies, Inc. リアルタイム系データ基盤紹介(構築中)
 45 • Change TrackingデータをPubSubにPublish ◦

    SELECT * FROM テーブル WHERE 主キー IN (変更のあった主ー) • HA構成にするためにfluentdサーバーを2台設置 ◦ Active Active構成 ◦ 2台のfluentd間での協調動作はしない • PubSubには重複したデータが入る
  37. © ZOZO Technologies, Inc. リアルタイム系データ基盤紹介(構築中)
 46 • 重複排除をDataflowで行う ◦ withIdAttributeで処理するのでウインドウによる遅延なし

    ◦ PubsubIO.readMessagesWithAttributes() .withIdAttribute("id") .fromSubscription(サブスクリプション名) • fluentdで行の値からハッシュ値を生成 ◦ id = hash(col1 + col2 + ... + colN) • 結局下流のPubSubがat least onceじゃない? ◦ 流石に全データ重複しているとその後の処理が辛い
  38. © 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と同等のデータを再現
  39. © ZOZO Technologies, Inc. リアルタイム系データ基盤紹介(構築中)
 48 • 提供する読み出し口1 ◦ PubSubのSubscription

    ◦ 差分情報のみ ◦ 生の差分データ(JSON形式) • 提供する読み出し口2 ◦ BigQueryのテーブル ◦ 差分情報 + 全量情報 ◦ SQL文で取得 ① ②
  40. © 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
  41. © ZOZO Technologies, Inc. まとめ
 50 • データ基盤にBigQueryを使っています
 • オンプレDWHよりもクラウドDWHの方が圧倒的に楽


    • BigQueryはクラウドDWHの中でも特に抽象度が高い
 ◦ 運用で気にするべきポイントが変わるので注意
 ▪ 従来: CPU・ディスク使用率
 ▪ BigQuery: クエリのレスポンスタイム・費用
 ◦ 当初は弱かったセキュリティ機能も最近はまともになってきてる
 • リアルタイム系の基盤も作ってます