はじめに
私はSnowflakeを用いたデータ連携基盤を構築するPJに参画しています。
PJの振り返りからいくつか課題が出てきました。
SQLの書き方に関するルールについてもそのひとつです。
個性が出るSQLについて、PJ内で開発ルールを作成していたものの
メンバーそれぞれがルールを守っているだけで、ルールが正しく守られているのかの検証まで行っていませんでした。
ルールを作ったら検証するまでをセットで考えよう
PJの振り返りを通じて上司からもらったアドバイスです。
ルールを作って満足していましたが、きちんと検証してこそのルールだと感じたので
lintツールであるSQLfluffをPJに取り入れることにしました。
SQLfluffとは
lintツール（プログラムのエラーを検出してくれるツール）の1つです
お願いすれば修正(fix)までやってくれます
基本的な使い方
以下のドキュメントからルールを設定したファイル（.sqlfluff）を用意し
このファイルを基に対象ファイルのSQLを検証します
※右下からバージョンの確認をすること！
準備
2つのファイルを用意します
1.設定ファイル .sqlfluff （コメントアウトは「#」）
2.検証の対象ファイル xxx.sql
[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
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した結果を確認する
実行するとこんな感じで色々言われます
対象ファイルのSQLについて何が問題かを教えてくれています。
▼読み方
L（行）: 1 | P（位置）: 1 | LT09（ルール） |Use 'COALESCE' instead of 'IFNULL'（理由、内容）
公式ドキュメントを上のルール名で検索すると詳細を確認することが出来ます。
※PowerShellだと背景色の関係で左側が読みづらい
（コマンドプロンプトだと少しわかりやすいかも...?）
※All Finishedに続く文字化けは絵文字なので問題なし（All Finished 📜 🎉!）
4.fix(修正)もしてみる
※同じファイル名で上書きされるので注意する
PS C:\Users\Himeka_Kawaguchi\repos\SQLfluff> sqlfluff fix qiita_sample.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をインストール
2.Executable-Pathを設定
2-1.コマンドレットを開く(Ctrl+Shift+P)
2-2.Preferences: Open Settingsを選択
2-3.検索窓に「ExecutablePath」と入力
2-4.Sqlfluff:ExecutablePathにPath★を入力 ※Path★について画像以下
2-5.Path★はPowerShellで以下を実行した結果（Path）
PS C:\Users\Himeka_Kawaguchi\repos\SQLfluff> gcm sqlfluff | fl
3.対象ファイルにルールを適用
右クリック> Format Documentを選択し検証
結果は以下
※注意点
公式のドキュメントにはバージョンがあります。
画面右下から確認してください
まとめ
・PJを振り返り、改善点を見つけたら取り入れてみることは大事
・ルールを作ったら検証までセットで考える
・最初から完璧を求めすぎない
最後に
はじめての取り組みでしたが、やってみて良かったなと思います！
知らないだけで便利なツールは沢山あるなと知らされ、便利なもの、楽になるものはどんどん使っていくべきだと感じました。
PJを振り返り、より良いものになるような工夫はこれからも続けていきたいです。
参考になれば幸いです。