さようなら、掛け違えたJOINキーたち

はじめに

　はじめまして、SinkCapitalの丸山です。
ギルド内部では主に webフロントエンド × データアナリスト/エンジニア で活動させていただいております。

ここ最近、SinkCapitalギルド内で、BQ JOIN checker というChrome拡張機能をリリースしました！
@yuji_sakurai 発案、自分が開発を担当しております。

BigQueryのjoin句を含むstandardSQLを入力することで、join前後でのレコード数の変化を返すSQLを自動でクリップボードにコピーします。その検算用SQLを用いることでjoin句での意図せぬレコードの増減を検知することができます。

本記事は、宣伝に加え、ご意見ご感想、バグ発見、機能提案など、ご協力頂ける方がいればと思い、書かせていただいております。

JOINキーの掛け違いの検出

さて、本題に入る前に。SQL使いのみなさま。

日々、大量に溜まっていくデータの中から、意味のある情報を抜き出すために、
日夜、たくさんのテーブルをJOINしていることと思いますが、
そのSQLで取ってきたレコード数がおかしくて調査することも多いかと思います。

テーブル間の関係が、1:Nなのか、N:Nなのか、
はたまた主キーは何で、複合キーはどれで、などと複雑に絡み合って、
あるテーブルをJOINした時点で突然にレコードが大量に増えていたり、
あるいは想定より少なくなっていたりすることがあります。

例えば、以下のように、
baseというテーブルに、sub1、sub2というテーブルをjoinしてるとして、
結果、レコード数が想定と違ったとします。

join.sql

SELECT
  *
FROM
  base
  LEFT JOIN sub ON base.id = sub.id
  INNER JOIN sub2 ON base.id = sub2.id;

こういう場合、どのテーブルをJOINしたタイミングでレコード数に変化があったのか確かめるために、
都度↓↓こんな感じで検算用のSQLを書いたりします。

check.sql

WITH
  -- baseの元々の件数
  sql1 AS (
    SELECT
      'sql1' AS _name,
      COUNT(*) AS _count
    FROM
      base
  ),
  -- baseにsub1をjoinした時点での件数
  sql2 AS (
    SELECT
      'sql2' AS _name,
      COUNT(*) AS _count
    FROM
      base
      LEFT JOIN sub1 ON base.id = sub1.id
  ),
  -- さらにsub2をjoinした時点での件数
  sql3 AS (
    SELECT
      'sql3' AS _name,
      COUNT(*) AS _count
    FROM
      base
      LEFT JOIN sub1 ON base.id = sub1.id
      INNER JOIN sub2 ON base.id = sub2.id
  )
SELECT
  _name,
  _count
FROM
  sql1
  UNION ALL
  SELECT * FROM sql2
  UNION ALL
  SELECT * FROM sql3

これをBQで実行してみると、こんな感じで出ます↓↓（_description部分にはJOINの内容を格納してみてます）

これをみる限り、3行目のsql3の箇所、つまりsub2をJOINしたタイミングで件数が変わったな、と判断することができるわけです。

BQ JOIN checker

さて、もうお分かりかと思いますが、
こういった検算用のSQLを自動で作成するツールを開発しております。

拡張機能内のテキストエリアにSQLを貼り付けて⌘(ctrl)+Enter で、上記のような検算用のSQLが作られて、クリップボードにコピーされます。

それをBQで実行すると、先ほどの例のような結果が得られます。

内部の実装としては、JavaScriptでSQLを解析して、検算用SQLに組み替えているだけなので、
拡張機能内に閉じた作りになっていて、サーバーとの通信もなく、登録や設定なども不要。
Chromeに拡張機能として追加さえすれば、サクッと利用することができます。

UIとしてはボタンとテキストエリアがあるのみで、SQLをコピペしたら実行するだけです。

@yuji_sakurai さんにLPを作っていただいたので、詳しくはそちらをご覧いただけると良いかもしれません！

直近のアップデートで追加した機能

直近 BQ JOIN checker に追加した機能が、「joinしたテーブルの関係図を表示する」という機能です。

検算SQL作成と流れは同じで、拡張機能内のテキストエリアにsqlを貼り付けて、今度は alt + Enter という隠しコマンドを叩くと新規タブで表示されます。

ちなみにLPにも拡張機能と同じテキストエリアが埋め込んであり、そこからもお試しできます！

実際に表示してみたテーブル関係図の例↓↓

ちなみに、画像としてもダウンロードできます。

どうやって関係図が書かれているのか

入力されたSQLをmermaid記法というものに変換しており、こんな感じのものです↓↓

graph RL
sales ---> RESULT
users ---> |LEFT JOIN| RESULT
products ---> |INNER JOIN| RESULT

この記法、実はqiitaでもnotionでもGithubでも使えるという。

qiitaで表示してみると↓↓こんな感じ。

Githubでも！

ってことで、現状は画像がダウンロードできるのみですが、
今後、mermaid記法に変換したmdファイルをダウンロードできたり、
あるいは、mermaid記法のコードのまま、クリップボードにコピーできるようにしたりすることで、
色んな場所で、テーブル関係図をドキュメントとして残せたり共有できる未来が...！

ちなみに
manifest v3 + Vite + TypeScript + React + Material UI で作ってます。