データカタログを作成してZOZOTOWNデータベース定義をまとめた話

こんにちは。ZOZOTOWN開発本部バックエンド1ブロックの山本です。普段はZOZOTOWNのバックエンドやマイクロサービスAPIなどの開発に携わっています。

ZOZOTOWNは膨大なデータを有しており、テーブルやカラムの数も膨大です。しかし、ER図やテーブル定義に関するドキュメントは手動で更新されていたため情報遅れが生じ、信頼性が低いものとなっていました。

本記事ではその問題を解決するための取り組み、「データカタログ作成プロジェクト」について紹介します。

データカタログとは

はじめに、データカタログという言葉について説明します。

データカタログとは、データベースのテーブル定義やカラム定義、メタ情報などをまとめた、辞書のようなものをさす言葉です。

データベースの情報はアナリストやビジネスサイドのチームなど開発者以外からも需要がありますが、情報が複雑なため人力で資料を整え続けるのは困難です。

そこで、ER図の出力やメタ情報の管理などを自動化してくれる製品やツールを活用することで資料を効率よくまとめることができます。

参考として、データカタログ関連の既存製品を2つ紹介します。

Dataedo

https://dataedo.fbpp.jp

DataedoはポーランドのDataedo社によって開発されたデータカタログツールであり、データベース関連の資料管理を効率化、資料をもとにER図の出力や情報の分類などを行う製品です。

日本ではFBP Partners社が公認のセールスパートナーとして、2020年7月よりDataedoのサービス提供を行っています。

以下の機能を持っており、情報整理の工数を大幅に削減できることが特徴です。

データベースからメタデータを抽出
テーブル定義書の描画
ER図の描画

dbdocs

https://dbdocs.io

こちらは2022年5月現在まだベータ版の製品です。

DBMLというマークアップ言語で記述されたデータベース定義を読み込み、テーブル定義書やER図を生成します。

メタ抽出機能は付いていないためDBMLファイルを利用者側で用意する必要がありますが、利用者側でDBMLファイルの出力を自動化できれば優れたソリューションとなるでしょう。

ER図やリレーションシップの表現がとてもわかり易く、UIが高品質であることが特徴です。

背景・目的

「データカタログ作成プロジェクト」を開始した背景、目的について話します。

私は2020年4月に新卒で入社し、同じ年の5月にバックエンドブロック配属となりました。

当時のZOZOTOWNでは、データベースの資料を以下のように管理していました。

テーブル定義書の管理方法
- テーブルリスト、カラムリストを手動で追加・削除
- 説明文やメタ情報は任意で手動入力
ER図の管理方法
- ER図は画像出力されたものを共有（文字列検索などはできない）
- テーブル定義書の情報とは独立して管理

また、ZOZOTOWNに関わる多くの人がデータベースに関わりデータベースの定義を知る必要がありますが、資料が整備されておらず以下のような問題も発生していました。

ドキュメント・ER図が手動更新されているため
- 情報遅れが発生していた
- 管理・更新のための工数発生していた
ER図が画像で共有されていたため
- ER図内で文字列検索ができなかった
- 更新が面倒だった（実際に4年以上放置されていた）

その結果以下のような状況が生まれ、自分のチームにも多数の問い合わせが発生し、それに答えるための工数が多く発生していました。

情報が更新されなくなる
調べたくても資料が整っておらず、資料の信頼性が低い
知るためには、知っていると思われる人・部署に問い合わせるしか無い
質問のたび両者に工数が発生している

そういった工数を削減するとともに、資料を整備することで開発者体験を向上したいと思いデータカタログ作成プロジェクトをスタートしました。

課題の解決手段

既存製品がカバーしている点は以下でした。

メタデータなど定義の抽出
定義のER図化
リレーションシップの可視化（外部キー制約が貼られているものと、手動で設定したリレーションシップの可視化）

しかしZOZOTOWNでは、既存製品ではカバーできない以下のような需要がありました。

「外部キー制約は貼られていないけどリレーションシップを持っているものとして運用されているテーブル」が、手動で整理するのは難しいほどたくさんある
データベース1つあたりのテーブル数が膨大なため、ER図は分割する必要がある

他にも様々な需要を考慮すると既存製品ではカバーできないと判断し、内製することにしました。

内製したソフトウェアのアーキテクチャと基本機能

大きく分けて、以下2つのアプリケーションを作成しました。

Webアプリケーション
- 使用技術
  - Vue.js, Ruby on Rails, MySQL
- 主要機能
  - DBから取り込んだ情報の保持
  - データカタログの閲覧
  - メタデータの追加・編集
  - ER図の作成・閲覧
バッチアプリケーション
- 使用技術
  - Python
- 主要機能
  - 利用実績に基づくリレーションシップ・カーディナリティの推定
  - 本番DBサーバーから情報取得
  - 取り込んだ情報や推定した情報をWebアプリケーション側に定期送信

▼アーキテクチャ図

アーキテクチャ図

▼テーブル定義の閲覧ページ

テーブル定義を確認する画面

▼ER図の閲覧ページ

ER図を閲覧する画面

※非公開情報には table_〇〇〇、column_〇〇〇 のようにマスクしてあります。実際には本番環境で利用されているテーブル名、カラム名が入っています。

仕様やロジックを一部紹介します。

ER図作成UI

気軽にER図を生成・共有できる画面を導入しました。

ER図を閲覧する画面

また、生成されたER図を一覧化し、気軽に共有や検索ができる機能を持っています。

利用実績に基づく仮想外部キーの作成、カーディナリティの推定

ZOZOTOWNでは、外部キー制約は貼られていないがリレーションシップを持っているものとして運用されているテーブルが多数存在しており、その関係を手動でまとめるのは困難でした。

そこで、本システムでは以下のような仕組みを用意して「仮想外部キー」を制定しました。

リレーションシップを持っているテーブルペアの洗い出し

SQL Serverの sys.dm_exec_query_stats より、本番環境で実際に実行されたクエリを大量に入手。その中からJOIN句でつながっているテーブル名とカラム名を抜き出し、どちらかがPKであれば仮想外部キーの候補とする。

取得した候補は、続く推定作業で利用する。

1:N or 1:1の推定

上で取得した候補に対して以下のクエリを実行し、結果が1行なら1:Nとした。

※ 例で示すクエリはSQL Server用の文法です。

-- @ColumnNameMain 解析対象のカラム名(1)
-- @ColumnNameSub  解析対象のカラム名(2)
-- @TableNameMain  解析対象のカラム(1)が属しているテーブル名
-- @TableNameSub   解析対象のカラム(2)が属しているテーブル名

-- 結果が1行であれば、 @TableNameMain : @TableNameSub = 1 : N

SELECT DISTINCT TOP 1
  @ColumnNameMain
FROM
  @TableNameMain WITH(NOLOCK)
WHERE
  EXISTS(
    SELECT
      @ColumnNameSub
    FROM
      @TableNameSub WITH(NOLOCK)
    WHERE
      @ColumnNameMain = @ColumnNameSub
    GROUP BY
      @ColumnNameSub
    HAVING
      COUNT(*) > 1
  )
  AND @ColumnNameMain IS NOT NULL

0以上か1以上の推定

1で取得した候補に対して以下のクエリを実行し、結果が0行なら1以上、1行なら0以上とした。

※ 例で示すクエリはSQL Server用の文法です。

-- @ColumnNameMain 解析対象のカラム名(1)
-- @ColumnNameSub  解析対象のカラム名(2)
-- @TableNameMain  解析対象のカラム(1)が属しているテーブル名
-- @TableNameSub   解析対象のカラム(2)が属しているテーブル名

-- 結果が0行であれば、 @TableNameMainに@TableNameSubは1個以上紐づく。
-- 結果が1行であれば、 @TableNameMainに@TableNameSubは0個以上紐づく。

SELECT DISTINCT TOP 1
  @ColumnNameMain
FROM
  @TableNameMain
WHERE

  NOT EXISTS(
    SELECT *
    FROM
      @TableNameSub
    WHERE
    @ColumnNameMain = @ColumnNameSub
  )

リレーションシップ、カーディナリティのUI表現

このようなルールで自動推定されたリレーションシップやカーディナリティは、以下のようにER図やカラム定義から確認できるようにしました。

▼カラムの詳細画面

特定カラムの関連テーブル一覧を表示する画面

▼ER図閲覧画面

ER図が閲覧できる画面

導入効果

情報の自動更新と自動推定により、発生していた課題が解決されました。

情報遅れが発生していた → 常に最新の情報へアクセス可能となった。
管理・更新のための工数発生していた → 管理・更新のための工数はほぼ0となった。

また、ターゲットユーザーの母数およそ400人に対し、UU数とPV数は以下のようになっています。

1日あたり	約40UU, 300~1000PV
1週間あたり	約80UU, 2000~4000PV

完成したデータカタログはテーブル定義の調査だけでなく、新入社員の研修など様々な場面で活用されており、活用の場面を増やすべく今も機能拡張中です。

おわりに

本記事ではデータカタログ作成プロジェクトの背景と実装内容について紹介しました。新鮮で信頼性の高いドキュメントが自動的に整う環境は、開発者体験の向上においても大きなメリットとなるかと思います。

今後運用と改善を重ねて、より働きやすい環境を整えていきたいと思います。

ZOZOでは一緒にサービスを作り上げてくれる仲間を募集中です。ご興味のある方は、以下のリンクからぜひご応募ください！

corp.zozo.com hrmos.co

ZOZO TECH BLOG

TECH BLOG