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

はじめに

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

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

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

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

sqruffとは

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

主な機能は次の通りです。

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

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

• ANSI SQL - 標準SQL構文(デフォルトで使用される)
• BigQuery
• Athena
• Clickhouse
• DuckDB
• PostgreSQL
• Snowflake
• SparkSql
• SQLite
• Trino

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

まずはインストール

私の環境はMacなので、brewを使ってインストールしていきます。

brew install quarylabs/quary/sqruff

基本的な使い方

実行コマンドとしては以下です。

sqruff [OPTIONS] <COMMAND>

COMMAND:

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

OPTIONS:

• --config <CONFIG> -- 設定ファイルへのパスを指定する

コマンドの詳細についてはこちら。

lint

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

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

fix

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

sqruff fix <file/paths/directory>

設定ファイルの準備

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

まずは、公式にあるサンプル通り、AM01とAM02を除くすべてのルールを有効にし、SQL方言はBigQueryに指定してみます。

.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
• Sample configurations

とりあえず動かしてみる

lintの実行

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

% 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コマンドを実行してみます。

% 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オプションを与えると、確認プロンプトをスキップして、強制的に整形を適用します。（詳細はこちら）

Attempting fixes...
All Finished 📜 🎉

sample.sqlの中身を確認してみると・・・

% cat sql/sample.sql
SELECT a + b FROM tbl;

ちゃんと整形されています・・・！🙌

この状態でlintを再度実行してみると・・・

% sqruff lint sql/sample.sql
The linter processed 1 file(s).
All Finished 📜 🎉

先程までのエラーが解消されています！🎉

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

まとめ

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

とはいえ、まずはルールファイルを作成してソース管理して、各自でVSCodeのエクステンション導入から始めるのが一手としては良さそうかな？

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

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

おまけ：ルール一覧

この投稿の執筆時点で、60個のルールがありました。
fixコマンドで修正できるもの/できないものもあったりするので、ルール詳細をよく確認しておく必要がありますね・・・！

aliasing

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

ambiguous

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

capitalisation

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

convention

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

layout

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

references

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

structure

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