SQLfluffをPJに取り入れてみるよ

# はじめに
私はSnowflakeを用いたデータ連携基盤を構築するPJに参画しています。
PJの振り返りからいくつか課題が出てきました。

SQLの書き方に関するルールについてもそのひとつです。

個性が出るSQLについて、PJ内で開発ルールを作成していたものの
メンバーそれぞれがルールを守っているだけで、ルールが正しく守られているのかの検証まで行っていませんでした。

## ルールを作ったら検証するまでをセットで考えよう
PJの振り返りを通じて上司からもらったアドバイスです。
ルールを作って満足していましたが、きちんと検証してこそのルールだと感じたので
lintツールであるSQLfluffをPJに取り入れることにしました。

## :gear: SQLfluffとは
lintツール（プログラムのエラーを検出してくれるツール）の1つです
お願いすれば修正(fix)までやってくれます

https://sqlfluff.com/

### 基本的な使い方
以下のドキュメントからルールを設定したファイル（`.sqlfluff`）を用意し
このファイルを基に対象ファイルのSQLを検証します

https://docs.sqlfluff.com/en/latest/rules.html

※右下からバージョンの確認をすること！

### 準備
2つのファイルを用意します

　1.設定ファイル　`.sqlfluff`　（コメントアウトは「`#`」）
　2.検証の対象ファイル　`xxx.sql`


```:.sqlfluff
[sqlfluff]
  dialect = snowflake
  templater = raw
  max_line_length = 80
  exclude_rules = L042

[sqlfluff:layout:type:comma]
  spacing_after = single
  line_position = leading

[sqlfluff:rules:aliasing.table]
  aliasing = explicit

[sqlfluff:rules:aliasing.column]
  aliasing = explicit

[sqlfluff:rules:references.consistent]
  force_enable = true
```


```sql:sample_qiita.sql
SELECT id,firstname f_name,lastname l_name,age FROM
user U
LEFT JOIN
(SELECT id,name FROM
customer) ON U.id=id
```
# 設定ファイルはどんなルールに？
以下のルールで作成してみました。まずは最低限のルールにしています。
```
・SELECTする項目のカンマは前に
・エイリアス、修飾子を明示する
・サブクエリはWITH句にしない
```
### 使用したルールまとめ

| Code |Rule|Rule|
|:-----------|------------:|------------:|
| LT05  | layout.long_lines|1行に何文字書くか（改行のタイミング）|
| ST05 | structure.subquery|サブクエリはWITH句に|
| LT04 | layout.commas|コンマの場所|
| AL01 | aliasing.table|テーブル名のエイリアス|
| AL02 | aliasing.column|カラム名のエイリアス|
| RF03 | references.consistent|修飾子を付ける|

# いざ実践！
SQlfluffはCLIやVScodeから使う事が出来ます

## まずはCLIからやってみる
※python、SQLfluffがインストールされていることを前提とする

### 1.SQLfluffがインストール出来ているか確認
```:
PS C:\Users\Himeka_Kawaguchi> sqlfluff --version
sqlfluff, version 3.0.7
```
インストールしたSQlfluffのバージョンが表示されたらOK

### 2.対象のSQLファイルを検証
```:
PS C:\Users\Himeka_Kawaguchi\repos\SQLfluff> sqlfluff lint <ファイル名>.sql
```
※`.sqlfluff`と`sample_qiita.sql`が格納されているディレクトリのパスを指していること
※ディレクトリの移動は `cd ディレクトリ名`

### 3.lintした結果を確認する
実行するとこんな感じで色々言われます:smiling_imp: 

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3586761/c25b5fbe-718c-2637-44ed-48a6f28b8afb.png)

対象ファイルのSQLについて何が問題かを教えてくれています。

▼読み方
`L（行）:   1 | P（位置）:   1 | LT09（ルール） |Use 'COALESCE' instead of 'IFNULL'（理由、内容）`

公式ドキュメントを上のルール名で検索すると詳細を確認することが出来ます。


※PowerShellだと背景色の関係で左側が読みづらい
（コマンドプロンプトだと少しわかりやすいかも...?）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3586761/42639b60-8bbc-7809-c4e9-0e1ad7b60a6c.png)

※All Finishedに続く文字化けは絵文字なので問題なし（All Finished 📜 🎉!）

### 4.fix(修正)もしてみる
※同じファイル名で上書きされるので注意する
```:
PS C:\Users\Himeka_Kawaguchi\repos\SQLfluff> sqlfluff fix qiita_sample.sql
```

### 実行結果

```sql:sample_qiita.sql
SELECT
    id
    , firstname AS f_name
    , lastname AS l_name
    , age
FROM
    user AS u
LEFT JOIN
    (
        SELECT
            id
            , name
        FROM
            customer
    ) ON u.id = id

```
設定したルールに沿って修正されていることが確認できました！

## 次はVScodeからも使ってみる
1.VScodeの拡張機能から`sqlfluff`をインストール

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3586761/f18eef6c-8197-0d05-caf0-c010d279cf0e.png)

2.Executable-Pathを設定
　2-1.コマンドレットを開く(Ctrl+Shift+P)
　2-2.`Preferences: Open Settings`を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3586761/b0430b1c-b03a-3299-a458-1c040a9941a2.png)
　2-3.検索窓に「ExecutablePath」と入力
　2-4.Sqlfluff:ExecutablePathにPath★を入力　※Path★について画像以下
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3586761/0de16566-fec3-c5ee-85ac-9c88226d424b.png)
　2-5.Path★はPowerShellで以下を実行した結果（Path）
 ```:
 PS C:\Users\Himeka_Kawaguchi\repos\SQLfluff> gcm sqlfluff | fl
 ```
3.対象ファイルにルールを適用
右クリック> `Format Document`を選択し検証

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3586761/3ce81fe4-6761-8e4f-3f6b-2a14634d4bd5.png)

結果は以下

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3586761/14b0a93e-9fa9-3d8a-1d8d-bc700e587bfa.png)


### ※注意点
公式のドキュメントにはバージョンがあります。
画面右下から確認してください

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3586761/67d3451a-9e9a-f23a-1045-c90a5cc34b1e.png)


## まとめ
・PJを振り返り、改善点を見つけたら取り入れてみることは大事
・ルールを作ったら検証までセットで考える
・最初から完璧を求めすぎない

## 最後に
はじめての取り組みでしたが、やってみて良かったなと思います！
知らないだけで便利なツールは沢山あるなと知らされ、便利なもの、楽になるものはどんどん使っていくべきだと感じました。
PJを振り返り、より良いものになるような工夫はこれからも続けていきたいです。
参考になれば幸いです。