Data Engineering Study #16「データカタログ入門」 にて登壇した内容
イベントリンク:https://forkwell.connpass.com/event/255166/ アーカイブ:https://www.youtube.com/watch?v=1tBNmP7UY8w&list=PL-zOB4NIHubaG_W63xg0dJCfm3C6GJEIz&index=15&t=48s
ZOZOのデータカタログ内製事例と、 データカタログを整え続けるための仕組みづくりについて 2022年10月19日 Data Engineering Study #16「データカタログ特集回」 株式会社ZOZO ZOZOTOWN開発本部 ZOZOTOWNWEB部 バックエンド1ブロック 山本 健太Copyright © ZOZO, Inc.1
View Slide
© ZOZO, Inc.株式会社ZOZO ZOZOTOWN開発本部 ZOZOTOWNWEB部 バックエンド1ブロック山本 健太 2020年4月に新卒で入社 ZOZOTOWNのWEBバックエンド、マイクロサービスAPIなどの保守運用・開発をするチームに所属 最近はウェブサイトのリプレイスなどを担当しています 2
© ZOZO, Inc.https://zozo.jp/ 3● ファッションEC ● 1,500以上のショップ、8,500以上のブランドの取り扱い ● 常時90万点以上の商品アイテム数と毎日平均2,600点以上の新着 商品を掲載(2022年6月末時点) ● ブランド古着のファッションゾーン「ZOZOUSED」や コスメ専門モール「ZOZOCOSME」、靴の専門モール 「ZOZOSHOES」、ラグジュアリー&デザイナーズゾーン 「ZOZOVILLA」を展開 ● 即日配送サービス ● ギフトラッピングサービス ● ツケ払い など
© ZOZO, Inc.https://wear.jp/ 4● ファッションコーディネートアプリ ● 1,600万ダウンロード突破、コーディネート投稿総数は1,300万件以上(2022年6月末時点) ● ピックアップタグから最新のトレンドをチェック ● コーディネート着用アイテムを公式サイトで購入可能 ● WEAR公認の人気ユーザーをWEARISTAと認定。モデル・タレント・デザイナー・インフルエンサーといった各界著名人も参加
© ZOZO, Inc.5https://zozo.jp/zozoglass/ ● 自宅で簡単・高精度にご自身の顔の肌の色を計測できるフェイスカラー計測ツール ● ECにおけるコスメ購入時の課題であった「色選び」に関する不安や悩みを解消 ● 肌の色を構成する成分、ヘモグロビン量とメラニン量を画像から推定 ● コスメ専門モール「ZOZOCOSME」で取り扱うベースメイクの一部に対応 ● 計測者数110万人を突破(2022年1月末時点)
© ZOZO, Inc.6今日の話は、 ZOZOで導入(内製)したデータカタログについてです!
© ZOZO, Inc.7プロジェクトの概要 1. 前提 2. 背景と、既存ツールを利用せずに内製した理由 3. システムとプロジェクトの概要 システム説明 1. DEMO 2. ロジック紹介 3. どう活用されているか 作ったあとの話 1. データカタログを整え続けるための仕組み作り 目次
© ZOZO, Inc.8プロジェクトの概要 1. 前提 2. 背景と、既存ツールを利用せずに内製した理由 3. システムとプロジェクトの概要 システム説明 1. DEMO 2. ロジック紹介 3. どう活用されているか 作ったあとの話 1. データカタログを整え続けるための仕組み作り 目次
© ZOZO, Inc.前提 9今回作成したデータカタログは、Microsoft SQL Server 用のものです SQL Server特有の話が出た場合は、注釈で補足します
© ZOZO, Inc.10プロジェクトの概要 1. 前提 2. 背景と、既存ツールを利用せずに内製した理由 3. システムとプロジェクトの概要 システム説明 1. DEMO 2. ロジック紹介 3. どう活用されているか 作ったあとの話 1. データカタログを整え続けるための仕組み作り 目次
© ZOZO, Inc.背景 11データカタログ導入以前 テーブル定義書、ER図を手動更新で管理していた 発生していた問題 更新が放置され、信頼度の低い資料となっていた 手動で入力できる情報量にも限界があるので、資料化できていない項目が多かった
© ZOZO, Inc.背景 12データカタログ導入以前 テーブル定義書、ER図を手動更新で管理していた 発生していた問題 更新が放置され、信頼度の低い資料となっていた 手動で入力できる情報量にも限界があるので、資料化できていない項目が多かった 情報の更新を自動化し、問題を解決しよう!(よくある導入動機かと思います)
© ZOZO, Inc.解決すべき課題 13● テーブル定義書・ER図の情報が古い ● テーブル同士の繋がりが複雑で不明瞭 ● テーブル数が膨大なためER図の出力形式を工夫したい
© ZOZO, Inc.解決すべき課題 14● テーブル定義書・ER図の情報が古い ● テーブル同士の繋がりが複雑で不明瞭 ● テーブル数が膨大なためER図の出力形式を工夫したい 既存ツールで解決可能
© ZOZO, Inc.解決すべき課題 15● テーブル定義書・ER図の情報が古い ● テーブル同士の繋がりが複雑で不明瞭 ● テーブル数が膨大なためER図の出力形式を工夫したい 外部キー制約が貼られていれば既存ツールで解決可能
© ZOZO, Inc.解決すべき課題 16● テーブル定義書・ER図の情報が古い ● テーブル同士の繋がりが複雑で不明瞭 ● テーブル数が膨大なためER図の出力形式を工夫したい ZOZOのDBには、外部キー制約は貼られていないがリレーションを持っているものとして運用されているテーブルが大量にあった→この関係を特定してくれるツールがなかなか無い...
© ZOZO, Inc.つまり... 17親子関係を持つ2つのテーブルがあった際に
© ZOZO, Inc.18外部キー(FK)が登録されていれば簡単に親子関係を 特定できるが、 つまり...
© ZOZO, Inc.つまり... 19外部キーを貼らずに、親子関係を持っているものとして運用されているテーブルに関しては特定できない
© ZOZO, Inc.それをやるためには... 20このような推定が必要 ● リレーションを持つテーブルのペアを推定 ● どちらが親でどちらが子かを推定 欲を言うとこれも推定したい ● 1:1なのか、 1:N なのかを推定 ● 0可なのか、必ず1レコード以上紐づくのかを推定
© ZOZO, Inc.解決すべき課題 21● テーブル定義書・ER図の情報が古い ● テーブル同士の繋がりが複雑で不明瞭 ● テーブル数が膨大なためER図の出力形式を工夫したい
© ZOZO, Inc.解決すべき課題 22● テーブル定義書・ER図の情報が古い ● テーブル同士の繋がりが複雑で不明瞭 ● テーブル数が膨大なためER図の出力形式を工夫したい ○ 文字列検索やURL共有ができるよう、HTMLで出力できると良いのでは? ○ 大量なテーブル数描画にも耐えられると嬉しい
© ZOZO, Inc.解決すべき課題 23● テーブル定義書・ER図の情報が古い ● テーブル同士の繋がりが複雑で不明瞭 ● テーブル数が膨大なためER図の出力形式を工夫したい ○ 文字列検索やURL共有ができるよう、HTMLで出力できると良いのでは? ○ 大量なテーブル数描画にも耐えられると嬉しい ER図単体で見れば既存ツールで解決可能かもしれないが色々考慮すると内製するのが早そう!
© ZOZO, Inc.プロジェクトのタイムライン 242020年08月 2020年11月 2021年02月 2021年07月 2021年09月 - 個人的にプロトタイプを作成し、チームリーダーに共有 - DBテックリードやCTOのフィードバックを得て、プロジェクト化 - 推定ロジックの実装が完了し、β版を社内公開 - 完成版を社内公開 - descriptionの入力など、人力作業が不可欠な部分を各チームに入力してもらう / DB周りの実装ルールを整備する
© ZOZO, Inc.25プロジェクトの概要 1. 前提 2. 背景と、既存ツールを利用せずに内製した理由 3. システムとプロジェクトの概要 システム説明 1. DEMO 2. ロジック紹介 3. どう活用されているか 作ったあとの話 1. データカタログを整え続けるための仕組み作り 目次
© ZOZO, Inc.26データを閲覧する人データのメタ情報を追加する人ER図を閲覧・作成する人データカタログ、ER図を描画するウェブサイトシステム概要 推定ロジックなどを持つ定期実行バッチ
© ZOZO, Inc.27データを閲覧する人データのメタ情報を追加する人ER図を閲覧・作成する人データカタログ、ER図を描画するウェブサイト→DEMO動画で紹介します!推定ロジックなどを持つ定期実行バッチ→後ほどロジックを紹介します!システム概要 テーブル・カラムの情報メタデータを取得するクエリを実行DBから取得した情報を POST
© ZOZO, Inc.28プロジェクトの概要 1. 前提 2. 背景と、既存ツールを利用せずに内製した理由 3. システムとプロジェクトの概要 システム説明 1. DEMO 2. ロジック紹介 3. どう活用されているか 作ったあとの話 1. データカタログを整え続けるための仕組み作り 目次
© ZOZO, Inc.29DEMO ※テーブル名、カラム名など、機密情報はすべてマスクしてあります
© ZOZO, Inc.30DEMO(スクリーンショット) - TOPページ
© ZOZO, Inc.31DEMO(スクリーンショット) - データベース選択ページ
© ZOZO, Inc.32DEMO(スクリーンショット) - テーブル定義書
© ZOZO, Inc.33DEMO(スクリーンショット) - テーブル定義書
© ZOZO, Inc.34DEMO(スクリーンショット) - カラム詳細
© ZOZO, Inc.35DEMO(スクリーンショット) - ER図選択ページ
© ZOZO, Inc.36DEMO(スクリーンショット) - ER図詳細ページ
© ZOZO, Inc.37DEMO(スクリーンショット) - ER図作成ページ
© ZOZO, Inc.38プロジェクトの概要 1. 前提 2. 背景と、既存ツールを利用せずに内製した理由 3. システムとプロジェクトの概要 システム説明 1. DEMO 2. ロジック紹介 3. どう活用されているか 作ったあとの話 1. データカタログを整え続けるための仕組み作り 目次
© ZOZO, Inc.39推定方法 本番アプリケーションや本番バッチなどで実行されているSQLクエリを解析して推定 ロジック紹介 - リレーションを持つテーブルのペアを推定
© ZOZO, Inc.40例えばこのようなクエリがあったとき ロジック紹介 - リレーションを持つテーブルのペアを推定 SELECT*FROMGoodsJOINCategoryONGoods.CategoryID = Category.ID
© ZOZO, Inc.41例えばこのようなクエリがあったとき ロジック紹介 - リレーションを持つテーブルのペアを推定 SELECT*FROMGoodsJOINCategoryONGoods.CategoryID = Category.IDJOINで結ばれているカラムがあればペアの候補とし、2つのカラムのうちどちらか、もしくは両方がPKであればリレーションを持つものとして登録する(仮想的な外部キー制約を登録する)
© ZOZO, Inc.42ロジック紹介 - 0可なのか、必ず1レコード以上紐づくのかを推定 推定方法 本番環境DBに対して解析用のSQLクエリを実行し、結果で推定 ER図上の表記 0可の場合の記号 1レコード以上紐づく場合の記号
© ZOZO, Inc.43ロジック紹介 - 0可なのか、必ず1レコード以上紐づくのかを推定 SELECT DISTINCT TOP 1 @Column1FROM @Table1WHERE NOT EXISTS(SELECT *FROM @Table2WHERE @Column1 = @Column2)※SQL Serverの文法です※TOP句は取得件数のlimitを指定する構文結果が返ってきたら Table2はTable1に1件以上紐づく なければTable2はTable1に対して0件の可能性あり Table1に対して、Table2は必ず1件以上紐づくのか否かを推定する
© ZOZO, Inc.44ロジック紹介 - 0可なのか、必ず1レコード以上紐づくのかを推定 SELECT DISTINCT TOP 1 Category.IDFROM CategoryWHERE NOT EXISTS(SELECT *FROM GoodsWHERE Category.ID = Goods.CategoryID)例えばこのようなデータに対してクエリを実行すると...
© ZOZO, Inc.45ロジック紹介 - 0可なのか、必ず1レコード以上紐づくのかを推定 SELECT DISTINCT TOP 1 Category.IDFROM CategoryWHERE NOT EXISTS(SELECT *FROM GoodsWHERE Category.ID = Goods.CategoryID)例えばこのようなデータに対してクエリを実行すると...「シューズ」に紐づくGoodsが無いのでNOT EXISTSに引っかかり、クエリ結果が1行返却される→ Categoryに対してGoodsは、0件の場合もある
© ZOZO, Inc.46ロジック紹介 - 0可なのか、必ず1レコード以上紐づくのかを推定 SELECT DISTINCT TOP 1 Category.IDFROM CategoryWHERE NOT EXISTS(SELECT *FROM GoodsWHERE Category.ID = Goods.CategoryID)例えばこのようなデータに対してクエリを実行すると...逆に、「シューズ」に紐づくGoodsが存在していればクエリ結果は0行となる→ Categoryに対してGoodsは、1件以上紐づく※その時点で1件以上紐付いていたというだけなので、今後もずっと必ず1件以上紐づくという断定はできない
© ZOZO, Inc.47ロジック紹介 - 1:1 or 1:N を推定 推定方法 本番環境DBに対して解析用のSQLクエリを実行し、結果で推定 ER図上の表記 1:1の場合の記号 1:Nの場合の記号
© ZOZO, Inc.48ロジック紹介 - 1:1 or 1:N を推定 SELECT DISTINCT TOP 1 @Column1FROM @Table1WHERE EXISTS(SELECT @Column2FROM @Table2WHERE @Column1 = @Column2GROUP BY @Column2HAVING COUNT(*) > 1)AND @Column1 IS NOT NULL※SQL Serverの文法です※TOP句は取得件数のlimitを指定する構文結果が返ってきたら Table1 : Table2 = 1 : N なければ Table1 : Table2 = 1 : 1(の可能性あり)
© ZOZO, Inc.49ロジック紹介 - 1:1 or 1:N を推定 例えばこのようなデータに対してクエリを実行すると... SELECT DISTINCT TOP 1 Category.IDFROM CategoryWHERE EXISTS(SELECT Goods.CategoryIDFROM GoodsWHERE Category.ID = Goods.CategoryIDGROUP BY Goods.CategoryIDHAVING COUNT(*) > 1)AND Category.ID IS NOT NULL
© ZOZO, Inc.50ロジック紹介 - 1:1 or 1:N を推定 例えばこのようなデータに対してクエリを実行すると... SELECT DISTINCT TOP 1 Category.IDFROM CategoryWHERE EXISTS(SELECT Goods.CategoryIDFROM GoodsWHERE Category.ID = Goods.CategoryIDGROUP BY Goods.CategoryIDHAVING COUNT(*) > 1)AND Category.ID IS NOT NULLEXISTSに引っかかる(Goodsを2個以上保有している)Categoryが無いため、結果は0件となる
© ZOZO, Inc.51ロジック紹介 - 1:1 or 1:N を推定 SELECT DISTINCT TOP 1 Category.IDFROM CategoryWHERE EXISTS(SELECT Goods.CategoryIDFROM GoodsWHERE Category.ID = Goods.CategoryIDGROUP BY Goods.CategoryIDHAVING COUNT(*) > 1)AND Category.ID IS NOT NULLパンツがEXISTSに引っかかる(Goodsを2個以上保有している)ため、結果は1件となる
© ZOZO, Inc.52プロジェクトの概要 1. 前提 2. 背景と、既存ツールを利用せずに内製した理由 3. システムとプロジェクトの概要 システム説明 1. DEMO 2. ロジック紹介 3. どう活用されているか 作ったあとの話 1. データカタログを整え続けるための仕組み作り 目次
© ZOZO, Inc.53● レプリケーション情報、テーブル定義の確認 ● 今まで資料化が難しかったリレーション情報の確認 ● 情報共有の際、関連テーブルでER図を作成して共有 どう活用されているか
© ZOZO, Inc.54プロジェクトの概要 1. 前提 2. 背景と、既存ツールを利用せずに内製した理由 3. システムとプロジェクトの概要 システム説明 1. DEMO 2. ロジック紹介 3. どう活用されているか 作ったあとの話 1. データカタログを整え続けるための仕組み作り 目次
© ZOZO, Inc.55テーブル作成時のルール ● descriptionの入力必須化 ● 外部キー制約登録を推奨 ● コード値を持つカラムは、descriptionに「【コード値】」と入力する データカタログを整え続けるための仕組み作り
© ZOZO, Inc.56最後に
© ZOZO, Inc.57ZOZOTOWN バックエンドエンジニア(マイクロサービス)https://hrmos.co/pages/zozotech/jobs/0000005バックエンドエンジニア(オープンポジション)https://hrmos.co/pages/zozotech/jobs/0000166採用情報