SQL Linter「sqruff」を今更ながらちょっと試してみた

## はじめに
早いもので2024年も終わりを迎えようとしてますね。

今月に入ってデータ抽出の依頼が急増してまして、毎日てんやわんやしております。師走ですね～😇

これだけSQLを書く機会も多いのだから、前々から気になっていたSQL Linterの導入でも検討しようかな、というのが今回のお話です。

調べてみたところ、「[SQLFluff](https://github.com/sqlfluff/sqlfluff)」と「[sqruff](https://github.com/quarylabs/sqruff)」が出てきたのですが、「SQLFluff」はインストールするのにPython3が必要とのことで、実際のプロダクトへの導入を考えると少々アレだなぁ・・・ということで、今回は「sqruff」を試してみることにしました。

## sqruffとは
sqruffは、Rust で書かれた SQL リンターおよびフォーマッターです。

主な機能は次の通りです。
* Linting: クエリの品質を保証する、高度でカスタマイズ可能な SQL リンティング機能。
* Formatting: SQL コードの一貫性を保つための自動化された構成可能なフォーマット。
* Speed: オーバーヘッドが最小限で、高速かつ効率的です。
* Portability: Web サイトなどのさまざまな開発ワークフローに簡単に統合できるように設計されています。


この投稿の執筆時点で、次のSQL方言がサポートされています。

* ANSI SQL - 標準SQL構文(デフォルトで使用される)
* [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax)
* [Athena](https://docs.aws.amazon.com/athena/latest/ug/ddl-sql-reference.html)
* [Clickhouse](https://clickhouse.com/docs/en/sql-reference/)
* [DuckDB](https://duckdb.org/docs/sql/introduction)
* [PostgreSQL](https://www.postgresql.org/docs/current/sql.html)
* [Snowflake](https://docs.snowflake.com/en/sql-reference.html)
* [SparkSql](https://spark.apache.org/sql/)
* [SQLite](https://www.sqlite.org/lang.html)
* [Trino](https://trino.io/docs/current/sql.html)

将来的にはさらに多くの方言のサポートを追加する予定とのこと。

## まずはインストール
私の環境はMacなので、[brew](https://brew.sh/)を使ってインストールしていきます。

```bash
brew install quarylabs/quary/sqruff
```

## 基本的な使い方

実行コマンドとしては以下です。
```bash
sqruff [OPTIONS] <COMMAND>
```

COMMAND:
* `lint` -- SQLファイルを読み込み、定義ルールに基づいて問題点を指摘する
* `fix` -- SQLファイル読み込み、定義ルールに基づいた問題点の指摘およびクエリの修正を行う
* `lsp` -- LSPサーバーを実行する

OPTIONS:
* `--config <CONFIG>` -- 設定ファイルへのパスを指定する

コマンドの詳細については[こちら](https://github.com/quarylabs/sqruff/blob/main/docs/cli.md)。

### lint
SQL ファイルまたはファイル セットを lint するには、次のコマンドを実行します。

```bash
sqruff lint <file>
sqruff lint <file1> <file2> <file3>
sqruff lint <directory>
```

### fix
単一または複数のファイルを修正するには、次のコマンドを実行します。

```bash
sqruff fix <file/paths/directory>
```

## 設定ファイルの準備

lint/fixを試してみるにあたって、設定ファイルを準備します。
SQL方言、インデント、大文字化、その他の linting/スタイルオプションの設定は、`.sqruff`ファイルで構成されます。このファイルは、`sqruff` コマンドが実行されるディレクトリに配置する必要があります。

まずは、公式にあるサンプル通り、AM01とAM02を除くすべてのルールを有効にし、SQL方言はBigQueryに指定してみます。
```config:.sqruff
[sqruff]
dialect = bigquery
exclude_rules = AM01,AM02
rules = all

[sqruff:indentation]
indent_unit = space
tab_space_size = 4
indented_joins = True
```
各ルールの設定詳細や設定ファイルで定義できる項目のリストについては、以下のドキュメントをご確認ください。
* [Details on the rules](https://github.com/quarylabs/sqruff/blob/main/docs/rules.md)
* [Sample configurations](https://github.com/quarylabs/sqruff/blob/main/docs/sample_configurations.md)


## とりあえず動かしてみる

### lintの実行
`lint`コマンドを実行してみます。

```bash
% echo "  SELECT a  +  b FROM tbl;  " > sql/sample.sql
% sqruff lint sql/sample.sql
== [sql/sample.sql] FAIL
L:   1 | P:   1 | LT01 | Expected only single space before "SELECT". Found "  ".
                       | [layout.spacing]
L:   1 | P:   1 | LT02 | First line should not be indented.
                       | [layout.indent]
L:   1 | P:   1 | LT13 | Files must not begin with newlines or whitespace.
                       | [layout.start_of_file]
L:   1 | P:  11 | LT01 | Expected only single space before "+". Found "  ".
                       | [layout.spacing]
L:   1 | P:  14 | LT01 | Expected only single space before "b". Found "  ".
                       | [layout.spacing]
L:   1 | P:  27 | LT01 | Unnecessary trailing whitespace.
                       | [layout.spacing]
The linter processed 1 file(s).
All Finished 📜 🎉
```

エラーメッセージは、行数/ポジション/ルールID/エラー内容/ルール名が出力されています。

### fixの実行

今度は前述のSQLファイルに対して`fix`コマンドを実行してみます。

```bash
% sqruff fix sql/sample.sql
== [sql/sample.sql] FAIL
L:   1 | P:   1 | LT01 | Expected only single space before "SELECT". Found "  ".
                       | [layout.spacing]
L:   1 | P:   1 | LT02 | First line should not be indented.
                       | [layout.indent]
L:   1 | P:  11 | LT01 | Expected only single space before "+". Found "  ".
                       | [layout.spacing]
L:   1 | P:  14 | LT01 | Expected only single space before "b". Found "  ".
                       | [layout.spacing]
L:   1 | P:  27 | LT01 | Unnecessary trailing whitespace.
                       | [layout.spacing]
Are you sure you wish to attempt to fix these? [Y/n]
```

あれ？さっきとエラー内容変わったぞ・・・？👀
・・・一旦見なかったことにしますｗ

ここで `Y/y` を入力するとfixが実行されます。
尚、 `--force`オプションを与えると、確認プロンプトをスキップして、強制的に整形を適用します。（詳細は[こちら](https://github.com/quarylabs/sqruff/blob/main/docs/cli.md#sqruff-fix)）

```bash
Attempting fixes...
All Finished 📜 🎉
```
sample.sqlの中身を確認してみると・・・
```bash
% cat sql/sample.sql
SELECT a + b FROM tbl;
```
ちゃんと整形されています・・・！🙌

この状態でlintを再度実行してみると・・・
```bash
% sqruff lint sql/sample.sql
The linter processed 1 file(s).
All Finished 📜 🎉
```
先程までのエラーが解消されています！🎉

こんな感じで結構簡単に利用できそうなことがわかりました。

## まとめ

SQLのコーディングスタイルが統一できそうで、とても良さそうだなと思いました。
Github Actionsなんかで仕込んだり、もっと手前のpre-commitでhookしたりするばチームの生産性も上がりそうかも・・・！

とはいえ、まずはルールファイルを作成してソース管理して、各自で[VSCodeのエクステンション](https://marketplace.visualstudio.com/items?itemName=Quary.sqruff)導入から始めるのが一手としては良さそうかな？

そのためにも、まずはルールをしっかりと理解することろから始めなくては・・・😇

ということで、来年こそは導入に向けて動いていければと思います☺️

## おまけ：ルール一覧

この投稿の執筆時点で、60個のルールがありました。
`fix`コマンドで修正できるもの/できないものもあったりするので、[ルール詳細](https://github.com/quarylabs/sqruff/blob/main/docs/rules.md)をよく確認しておく必要がありますね・・・！

### aliasing

| ルールID | ルール名 | 説明 |
|-----------|-----------|-------------|
| AL01 | [aliasing.table](#aliasingtable) | テーブルの暗黙的/明示的なエイリアス。 | 
| AL02 | [aliasing.column](#aliasingcolumn) | 列の暗黙的/明示的なエイリアス。 | 
| AL03 | [aliasing.expression](#aliasingexpression) | エイリアスのない列式。明示的な`AS`句を使用してください。 | 
| AL04 | [aliasing.unique.table](#aliasinguniquetable) | テーブルエイリアスは各句内で一意である必要があります。 | 
| AL05 | [aliasing.unused](#aliasingunused) | エイリアスが使用されていない場合は、テーブルにエイリアスを設定しないでください。 | 
| AL06 | [aliasing.length](#aliasinglength) | from句と結合条件のエイリアスを識別する | 
| AL07 | [aliasing.forbid](#aliasingforbid) | from 句および結合条件でテーブル エイリアスを使用しないでください。 | 
| AL08 | [layout.cte_newline](#layoutcte_newline) | 列の別名は各句内で一意である必要があります。 | 
| AL09 | [aliasing.self_alias.column](#aliasingself_aliascolumn) | 自己エイリアス列を見つけて修正する | 


### ambiguous

| ルールID | ルール名 | 説明 |
|-----------|-----------|-------------|
| AM01 | [ambiguous.distinct](#ambiguousdistinct) | 'GROUP BY' を含む 'SELECT' ステートメントで 'DISTINCT' があいまいに使用されています。 | 
| AM02 | [ambiguous.union](#ambiguousunion) | UNIONキーワードの直後にDISTINCTまたはALLが続かないかどうかを確認します。 | 
| AM03 | [ambiguous.order_by](#ambiguousorder_by) | order by 句内の列の順序付けの指示があいまいです。 | 
| AM04 | [ambiguous.column_count](#ambiguouscolumn_count) | 最も外側のクエリは既知の数の列を生成する必要があります。 | 
| AM05 | [ambiguous.join](#ambiguousjoin) | 結合句は完全修飾されている必要があります。 | 
| AM06 | [ambiguous.column_references](#ambiguouscolumn_references) | 'GROUP BY/ORDER BY' 句内の列参照が矛盾しています。 | 
| AM07 | [ambiguous.set_columns](#ambiguousset_columns) | セット式内のすべてのクエリは同じ数の列を返す必要があります。 | 


### capitalisation

| ルールID | ルール名 | 説明 |
|-----------|-----------|-------------|
| CP01 | [capitalisation.keywords](#capitalisationkeywords) | キーワードの大文字/小文字の使い方が一貫していません。 | 
| CP02 | [capitalisation.identifiers](#capitalisationidentifiers) | 引用符で囲まれていない識別子の大文字と小文字の使い方が一貫していません。 | 
| CP03 | [capitalisation.functions](#capitalisationfunctions) | 関数名の大文字と小文字の使い方が一貫していません。 | 
| CP04 | [capitalisation.literals](#capitalisationliterals) | boolean/null リテラルの大文字/小文字の使い方が一貫していません。 | 
| CP05 | [capitalisation.types](#capitalisationtypes) | データ型の大文字と小文字の使い方が一貫していません。 | 


### convention

| ルールID | ルール名 | 説明 |
|-----------|-----------|-------------|
| CV01 | [convention.not_equal](#conventionnot_equal) | 「等しくない」演算子としての``!=``or``<>``の一貫した使用。 | 
| CV02 | [convention.coalesce](#conventioncoalesce) | 「IFNULL」または「NVL」の代わりに「COALESCE」を使用してください。 | 
| CV03 | [convention.select_trailing_comma](#conventionselect_trailing_comma) | 選択句内の末尾のカンマ | 
| CV04 | [convention.count_rows](#conventioncount_rows) | 「行数を数える」を表現するには、一貫した構文を使用します。 | 
| CV05 | [convention.is_null](#conventionis_null) | 関係演算子は NULL 値のチェックには使用しないでください。 | 
| CV06 | [convention.terminator](#conventionterminator) | ステートメントはセミコロンで終わる必要があります。 | 
| CV07 | [convention.statement_brackets](#conventionstatement_brackets) | 最上位レベルのステートメントは括弧で囲まないでください。 | 
| CV08 | [convention.left_join](#conventionleft_join) | RIGHT JOIN の代わりに LEFT JOIN を使用します。 | 
| CV09 | [convention.blocked_words](#conventionblocked_words) | 設定可能な単語のリストの使用をブロックします。 | 
| CV10 | [convention.quoted_literals](#conventionquoted_literals) | 引用符付きリテラルには、優先引用符を一貫して使用します。 | 
| CV11 | [convention.casting_style](#conventioncasting_style) | 一貫した型キャスト スタイルを適用します。 | 


### layout

| ルールID | ルール名 | 説明 |
|-----------|-----------|-------------|
| LT01 | [layout.spacing](#layoutspacing) | 不適切な間隔。 | 
| LT02 | [layout.indent](#layoutindent) | インデントが正しくありません。 | 
| LT03 | [layout.operators](#layoutoperators) | 演算子は、改行の前後の標準に従う必要があります。 | 
| LT04 | [layout.commas](#layoutcommas) | 先頭/末尾のカンマの強制。 | 
| LT05 | [layout.long_lines](#layoutlong_lines) | 行が長すぎます。 | 
| LT06 | [layout.functions](#layoutfunctions) | 関数名の直後に括弧がありません。 | 
| LT07 | [layout.cte_bracket](#layoutcte_bracket) | 「WITH」句の閉じ括弧は新しい行に置く必要があります。 | 
| LT08 | [layout.cte_newline](#layoutcte_newline) | CTE 閉じ括弧の後に空白行が必要ですが見つかりません。 | 
| LT09 | [layout.select_targets](#layoutselect_targets) | 選択ターゲットが 1 つしかない場合を除き、選択ターゲットは新しい行に記述する必要があります。 | 
| LT10 | [layout.select_modifiers](#layoutselect_modifiers) | 'SELECT' 修飾子 (例: 'DISTINCT') は 'SELECT' と同じ行になければなりません。 | 
| LT11 | [layout.set_operators](#layoutset_operators) | 集合演算子は改行で囲む必要があります。 | 
| LT12 | [layout.end_of_file](#layoutend_of_file) | ファイルは末尾に 1 つの改行文字で終わる必要があります。 | 
| LT13 | [layout.start_of_file](#layoutstart_of_file) | ファイルは改行または空白で始まってはなりません。 | 


### references

| ルールID | ルール名 | 説明 |
|-----------|-----------|-------------|
| RF01 | [references.from](#referencesfrom) | 参照では、「FROM」句に存在しないオブジェクトを参照できません。 | 
| RF02 | [references.qualification](#referencesqualification) | 選択に参照されるテーブル/ビューが複数ある場合は、参照を修飾する必要があります。 | 
| RF03 | [references.consistent](#referencesconsistent) | 参照は、単一のテーブルを含むステートメント内で一貫している必要があります。 | 
| RF04 | [references.keywords](#referenceskeywords) | キーワードは識別子として使用しないでください。 | 
| RF05 | [references.special_chars](#referencesspecial_chars) | 識別子に特殊文字を使用しないでください。 | 
| RF06 | [references.quoting](#referencesquoting) | 不要な引用符付き識別子。 | 


### structure

| ルールID | ルール名 | 説明 |
|-----------|-----------|-------------|
| ST01 | [structure.else_null](#structureelse_null) | case when ステートメントでは 'else null' を指定しないでください (冗長)。 | 
| ST02 | [structure.simple_case](#structuresimple_case) | 不要な「CASE」ステートメント。 | 
| ST03 | [structure.unused_cte](#structureunused_cte) | クエリは CTE (共通テーブル式) を定義しますが、それを使用しません。 | 
| ST04 | [structure.nested_case](#structurenested_case) | ``ELSE``句内のネストされた``CASE``ステートメントはフラット化できます。 | 
| ST05 | [structure.subquery](#structuresubquery) | Join/From 句にはサブクエリを含めないでください。代わりに CTE を使用してください。 | 
| ST06 | [structure.column_order](#structurecolumn_order) | 計算と集計の前に、ワイルドカードを選択してから単純なターゲットを選択します。 | 
| ST07 | [structure.using](#structureusing) | ``USING``を使用する代わりに、結合キーを指定することをお勧めします。 | 
| ST08 | [structure.distinct](#structuredistinct) | 括弧の前のDISTINCTを探す | 
| ST09 | [structure.join_condition_order](#structurejoin_condition_order) | 結合では、先に参照された/後に参照されたテーブルを最初にリストする必要があります。 |