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

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

ここ最近、SinkCapitalギルド内で、[BQ JOIN checker](https://chrome.google.com/webstore/detail/bq-join-checker/gnnenfnpfdlonkonljannnmiengokimj) というChrome拡張機能をリリースしました！
@yuji_sakurai 発案、自分が開発を担当しております。

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


https://chrome.google.com/webstore/detail/bq-join-checker/gnnenfnpfdlonkonljannnmiengokimj

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


## JOINキーの掛け違いの検出
さて、本題に入る前に。SQL使いのみなさま。

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

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

例えば、以下のように、
baseというテーブルに、sub1、sub2というテーブルをjoinしてるとして、
結果、レコード数が想定と違ったとします。
```sql:join.sql
SELECT
  *
FROM
  base
  LEFT JOIN sub ON base.id = sub.id
  INNER JOIN sub2 ON base.id = sub2.id;
```

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

```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の内容を格納してみてます）
![screenshot4 (1).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/418046/bc6ef8cf-1b02-0c15-c500-fd8a3ad9ac36.png)

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

# BQ JOIN checker 

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

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

![screenshot3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/418046/b48536be-d3ae-f1fd-0d3a-5d4e5708b3f7.png)

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

![screenshot4 (1).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/418046/bc6ef8cf-1b02-0c15-c500-fd8a3ad9ac36.png)


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

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



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


https://bq-help-extension.sink-capital.com/




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

直近 [BQ JOIN checker](https://chrome.google.com/webstore/detail/bq-join-checker/gnnenfnpfdlonkonljannnmiengokimj) に追加した機能が、「joinしたテーブルの関係図を表示する」という機能です。

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

ちなみに[LP](https://bq-help-extension.sink-capital.com/)にも拡張機能と同じテキストエリアが埋め込んであり、そこからもお試しできます！
![スクリーンショット 2022-12-15 9.57.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/418046/1658c152-82ef-e473-5ec6-d92daf263e9b.png)


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

![スクリーンショット 2022-12-17 23.20.34.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/418046/d2443ea0-37e5-4445-0b6e-27e814f5d1ad.png)


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

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

入力されたSQLをmermaid記法というものに変換しており、こんな感じのものです↓↓
```md
graph RL
sales ---> RESULT
users ---> |LEFT JOIN| RESULT
products ---> |INNER JOIN| RESULT
```
この記法、実はqiitaでもnotionでもGithubでも使えるという。

## qiitaで表示してみると↓↓こんな感じ。
```mermaid
graph RL
sales ---> RESULT
users ---> |LEFT JOIN| RESULT
products ---> |INNER JOIN| RESULT
```

## Githubでも！

![スクリーンショット 2022-12-17 21.50.30.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/418046/e68a4a7b-2943-2560-4316-88ce5b777987.png)



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


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