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

Slide 1

Slide 1 text

Slide 2

Slide 2 text

Slide 3

Slide 3 text

Slide 4

Slide 4 text

© ZOZO Technologies, Inc. https://wear.jp/  ● 日本最大級のファッションコーディネートアプリ  ● 1,400万ダウンロード突破、コーディネート投稿総数は900万件以上（ともに2019年12月末時点）  ● 全世界（App Store / Google Playが利用可能な全ての国）でダウンロードが可能  ● 等身大の着こなしが支持を集め、10万人以上のフォロワーを持ち WEARISTAに認定された一般ユーザーも誕生  4

Slide 5

Slide 5 text

© ZOZO Technologies, Inc. https://zozo.jp/multisize/    ● 身長と体重を選択するだけで理想のサイズの商品が見つかる新しい洋服の買い方  ● 2019年秋冬アイテムから、人気ブランドのマルチサイズアイテムを販売開始  【参加企業】  　株式会社アーバンリサーチ、株式会社ストライプインターナショナル、  　株式会社デイトナ・インターナショナル、株式会社パル、株式会社ビームス、  　株式会社ベイクルーズ、MARK STYLER株式会社、リーバイ・ストラウスジャパン株式会社　　など  5

Slide 6

Slide 6 text

Slide 7

Slide 7 text

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

© 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をなくせそう

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

© ZOZO Technologies, Inc. オンプレDHW vs クラウドDWH  20 ● 圧倒的にクラウドの運用が楽  ○ クラウドだと一瞬で終わることに時間を取られる  ■ スイッチの不調とか  ■ バックアップ戦略とか  ○ クラウドの容量が無限のストレージは本当に便利    ● 一回買ったら数年間使うのは辛い  ○ 念の為に大きめのDWHを買う  ○ 壊れたら直すが基本    →クラウドDWHでは気にしなくても良いコストが多すぎる 

Slide 21

Slide 21 text

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

Slide 24

Slide 24 text

© ZOZO Technologies, Inc. Looker導入  24 SQLでデータ集計処理を記述(数百ファイル)していたことの問題点  ● 車輪(クエリ)の再発明の量産  ○ SQLはWHERE句のみを切り出して再利用することができない  ○ いまならUDFを作れるので、ある程度は解決できるかも  ● KPIの定義が役職によって異なる  ○ 同じ「取扱高」という言葉でも人によって定義が違う  ○ 月跨ぎの返品・予約商品をどうするか？  ● 〇〇_idのハードコード  ○ 現金払いはpay_type_id=10, クレカ払いはpay_type_id=98    → データガバナンスツールとしての  　　Lookerを導入   

Slide 25

Slide 25 text

© 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: "テストアカウント" } } }

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Slide 32

Slide 32 text

© ZOZO Technologies, Inc. 懺悔  32 ● データレイクだと思っていたものがデータレイクではなかった  ○ データレイク = 元のデータを「無加工で」コピーしたもの    ● 元のDBと比較した時に欠損や不整合が発生  ○ 一部の特殊文字が化ける or 消える  ○ 一部のタイムスタンプが9時間ずれる or 8時間ずれる  ○ 一部のカラムが消えてる    ● 「自称」データレイクを信じてはいけない  ○ 最上流の信頼できるDBからデータを取得するべき  ❓❓謎の変換処理❓❓

Slide 33

Slide 33 text

Slide 34

Slide 34 text

Slide 35

Slide 35 text

© ZOZO Technologies, Inc. ここが辛いよBigQuery  35 ● 使い勝手が良すぎていつの間にか・・・  ○ 噂を聞きつけて色々な人が使い出す  ■ DWHの扱いに慣れたプロ  ■ OLTPなクエリは書けるけど分析クエリは初心者  ■ エクセル(=表計算ソフト)大好き人間  ○ 気軽なSELECT *を実行して一瞬で数千円が溶ける  ● 対策  ○ auditログから無駄遣いしてる人を見つけて「BQ違反者講習」  ○ 初心者向け資料を社内コンフルに  ○ Service Usage APIでスキャン量が多すぎるクエリをエラーにする   

Slide 36

Slide 36 text

© ZOZO Technologies, Inc. ここが辛いよBigQuery  36 ● 使い勝手が良すぎていつの間にか・・・  ○ 噂を聞きつけて色々な人が使い出す  ■ DWHの扱いに慣れたプロ  ■ OLTPなクエリは書けるけど分析クエリは初心者  ■ エクセル(=表計算ソフト)大好き人間  ○ 気軽なSELECT *を実行して一瞬で数千円が溶ける  ● 対策  ○ auditログから無駄遣いしてる人を見つけて「BQ違反者講習」  ○ 初心者向け資料を社内コンフルに  ○ Service Usage APIでスキャン量が多すぎるクエリをエラーにする     

Slide 37

Slide 37 text

© ZOZO Technologies, Inc. ここが辛いよBigQuery  37 ● 使い勝手が良すぎていつの間にか・・・  ○ 噂を聞きつけて色々な人が使い出す  ■ DWHの扱いに慣れたプロ  ■ OLTPなクエリは書けるけど分析クエリは初心者  ■ エクセル(=表計算ソフト)大好き人間  ○ 気軽なSELECT *を実行して一瞬で数千円が溶ける  ● 対策  ○ auditログから無駄遣いしてる人を見つけて「BQ違反者講習」  ○ 初心者向け資料を社内コンフルに  ○ Service Usage APIでスキャン量が多すぎるクエリをエラーにする     

Slide 38

Slide 38 text

© 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   

Slide 39

Slide 39 text

© 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   

Slide 40

Slide 40 text

© ZOZO Technologies, Inc. ここが辛いよBigQuery  40 ● コストの予測が・・・  ○ 使った分だけ課金 = いくらになるか事前に予測しづらい  ● 対策  ○ Flat-rate pricing  ○ コンピューティングコスト(5USD/TB)が固定になる  ■ 10,000USD/month〜  ○ 注: バースト性能が欲しいときはFlex Slot(今月にGA)と併用     

Slide 41

Slide 41 text

Slide 42

Slide 42 text

© ZOZO Technologies, Inc. リアルタイム系データ基盤紹介(構築中)  42 ● データ連携の頻度=1日1回  ○ 昔はそれでも問題なかった(日次レポートがメイン)  ○ AI活用が盛んになったらニアリアルタイムの基盤が欲しくなった  ● 更新タイムスタンプが格納された列を使った差分更新を検討  ○ https://speakerdeck.com/kyontan/number-dpct-20190416  ● 以下が多いため断念  ○ 更新タイムスタンプ列が存在しないテーブル  ○ 存在しても更新されていないテーブル  ○ 物理削除されているテーブル       

Slide 43

Slide 43 text

© 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 

Slide 44

Slide 44 text

Slide 45

Slide 45 text

© ZOZO Technologies, Inc. リアルタイム系データ基盤紹介(構築中)  45 ● Change TrackingデータをPubSubにPublish ○ SELECT * FROM テーブル WHERE 主キー IN (変更のあった主ー) ● HA構成にするためにfluentdサーバーを2台設置 ○ Active Active構成 ○ 2台のfluentd間での協調動作はしない ● PubSubには重複したデータが入る

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

© 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と同等のデータを再現

Slide 48

Slide 48 text

Slide 49

Slide 49 text

Slide 50

Slide 50 text

© ZOZO Technologies, Inc. まとめ  50 ● データ基盤にBigQueryを使っています  ● オンプレDWHよりもクラウドDWHの方が圧倒的に楽  ● BigQueryはクラウドDWHの中でも特に抽象度が高い  ○ 運用で気にするべきポイントが変わるので注意  ■ 従来: CPU・ディスク使用率  ■ BigQuery: クエリのレスポンスタイム・費用  ○ 当初は弱かったセキュリティ機能も最近はまともになってきてる  ● リアルタイム系の基盤も作ってます   

Slide 51

Slide 51 text

No content