この記事はスタンバイ Advent Calendar 2023の16日目の記事です。

今回は、SQL LinterであるSQLFLuffについて紹介したいと思います。

SQL Linter導入の背景

筆者は職場ではデータエンジニアを担当しており、Pythonコードを書くことが多いです。Pythonプロジェクトについてはflake8やblackといったlinter・formatterを導入しています。

しかし、SQLファイルについては導入できておらず、当初はSQLを書くメンバーが限られていたためさほど問題になりませんでしたが、規模が大きくなるに連れてだんだん保守性が下がっていきました。
アナリストの方々が書いたSQLがそのままレビューに落ちてくることもあるのですが、書き方が全く違うためレビューアの立場からすると違和感を覚えるシーンもありました。

PEP8のようなコーディング規約も無いため「こうあるべき」という基準を定めるのも難しく、それらの問題も含めて解決できそうなツールを探すこととなりました。

SQLFluffの機能紹介

SQLファイルのlinter・formatterの機能を持っています。

以下のような観点から、採用に至りました。

マルチSQL言語に対応している
既存のpythonプロジェクトに導入しやすい
VS Code拡張があるため、エディタとの相性も良い
適用するルールをカスタマイズしやすい

インストール

% python -V
Python 3.10.9

% pip install sqlfluff
(割愛)

% sqlfluff --version
sqlfluff, version 2.3.5

lintの実行

lintコマンドを実行します。
以下、公式ドキュメントにならっての実行結果です。

% echo "  SELECT a  +  b FROM tbl;  " > test.sql
% sqlfluff lint test.sql --dialect ansi
== [test.sql] FAIL
L:   1 | P:   1 | LT01 | Expected only single space before 'SELECT' keyword.
                       | 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:   3 | CP01 | Keywords must be lower case.
                       | [capitalisation.keywords]
L:   1 | P:  11 | LT01 | Expected only single space before binary operator '+'.
                       | Found '  '. [layout.spacing]
L:   1 | P:  14 | LT01 | Expected only single space before naked identifier.
                       | Found '  '. [layout.spacing]
L:   1 | P:  18 | CP01 | Keywords must be lower case.
                       | [capitalisation.keywords]
L:   1 | P:  27 | LT01 | Unnecessary trailing whitespace.
                       | [layout.spacing]
All Finished 📜 🎉!

エラーメッセージは、行番号・ポジション・ルールID・エラー内容・ルール名を表しています。

formatの実行

fixコマンドを実行すると、自動で整形してくれます。

% sqlfluff fix test.sql --dialect ansi
==== finding fixable violations ====
== [test.sql] FAIL
L:   1 | P:   1 | LT01 | Expected only single space before 'select' keyword.
                       | Found '  '. [layout.spacing]
L:   1 | P:   1 | LT02 | First line should not be indented.
                       | [layout.indent]
L:   1 | P:   3 | CP01 | Keywords must be lower case.
                       | [capitalisation.keywords]
L:   1 | P:  11 | LT01 | Expected only single space before binary operator '+'.
                       | Found '  '. [layout.spacing]
L:   1 | P:  14 | LT01 | Expected only single space before naked identifier.
                       | Found '  '. [layout.spacing]
L:   1 | P:  18 | CP01 | Keywords must be lower case.
                       | [capitalisation.keywords]
L:   1 | P:  27 | LT01 | Unnecessary trailing whitespace.
                       | [layout.spacing]
==== fixing violations ====
7 fixable linting violations found
Are you sure you wish to attempt to fix these? [Y/n]

最後の確認メッセージでYを選択すると整形が実行されます。確認メッセージ無しで強制的に実行する場合は、-fまたは--forceオプションを使います。

test.sqlファイルを確認すると、整形されていることがわかります。

% cat test.sql
select a + b from tbl;

対応しているSQL言語

先ほどのコマンドではオプションで--dialect ansiを指定しましたが、実際の運用では--dialect mysqlといったかたちで指定することになります。
対応言語の一覧は公式ドキュメントにありますが、dialectsコマンドでも確認できます。

% sqlfluff dialects
==== sqlfluff - dialects ====
ansi:                 ansi dialect [inherits from 'nothing']
athena:                athena dialect [inherits from 'ansi']
bigquery:            bigquery dialect [inherits from 'ansi']
clickhouse:        clickhouse dialect [inherits from 'ansi']
databricks:    databricks dialect [inherits from 'sparksql']
db2:                      db2 dialect [inherits from 'ansi']
duckdb:            duckdb dialect [inherits from 'postgres']
exasol:                exasol dialect [inherits from 'ansi']
greenplum:      greenplum dialect [inherits from 'postgres']
hive:                    hive dialect [inherits from 'ansi']
materializ:   materialize dialect [inherits from 'postgres']
e
mysql:                  mysql dialect [inherits from 'ansi']
oracle:                oracle dialect [inherits from 'ansi']
postgres:            postgres dialect [inherits from 'ansi']
redshift:        redshift dialect [inherits from 'postgres']
snowflake:          snowflake dialect [inherits from 'ansi']
soql:                    soql dialect [inherits from 'ansi']
sparksql:            sparksql dialect [inherits from 'ansi']
sqlite:                sqlite dialect [inherits from 'ansi']
teradata:            teradata dialect [inherits from 'ansi']
trino:                  trino dialect [inherits from 'ansi']
tsql:                    tsql dialect [inherits from 'ansi']

マルチSQL言語対応ですが、1回のコマンドで指定できるdialectは1つだけです。同一プロジェクト内に異なる言語のSQLが存在する場合は、複数回に分けて実行する必要があります。

適用する対象ファイルの指定

先程の例では引数でtest.sqlと指定しましたが、代わりにフォルダを指定することもできます。デフォルト設定では以下の拡張子ファイルが対象となりますが、これは設定ファイルで変えることが可能です。

.sql
.sql.j2
.dml
.ddl

この他、xxx/*.sqlといったワイルドカード形式で指定することもできます。

実は省略することも可能で、その場合はカレントディレクトリ.を指定した場合と同じ挙動になるようです。が、通常はフォルダを指定することになるでしょう。

CLIコマンドとオプション

コマンド数・オプション数ともにそこまで多くはないです。lintとfixコマンドでは共通するものも多いです。

一部のオプションは、後述の設定ファイルでも指定が可能です。設定ファイルとCLIオプションの両方で指定した場合、CLIオプションのほうが優先されます。

特筆オプションは-p, --processes <processes>で、並列実行が可能になります。
SQLファイルの数や規模が大きくなるとコマンド実行時間も長くなるため、pre-commitやCIに組み込む際に便利です。

設定ファイル

デフォルト設定値を上書きし、カスタマイズすることができます。

以下のファイルで設定可能ですが、メンテナンス性を考慮して独立した.sqlfluffを採用することにしました。

setup.cfg
tox.ini
pep8.ini
.sqlfluff
pyproject.toml

以下のように記述します。#を使ってのコメント・コメントアウトも可能です。

.sqlfluff

[sqlfluff]
dialect = athena
max_line_length = 120

[sqlfluff:rules:layout.long_lines]
# コメント行は長くても可とする。URLなどが入るため
ignore_comment_lines = True
ignore_comment_clauses = True

ルール

執筆時点で、合計60個ほどあります。
不要なスペースの除去・インデントの統一といったシンプルなものが多いですが、中には構文の最適化といったものもあります。

以下、一例を挙げます。

Capitalisation bundle

いわゆる「大文字小文字の統一」になります。以下のように5種類に分かれています。

ルールID	ルール名	内容
CP01	capitalisation.keywords	予約語の大文字小文字
CP02	capitalisation.identifiers	カラム名の大文字小文字
CP03	capitalisation.functions	関数名の大文字小文字
CP04	capitalisation.literals	リテラル値 (null・true・false) の大文字小文字
CP05	capitalisation.types	データ型の大文字小文字

デフォルト値はいずれもconsistentで「大文字か小文字かで一貫していれば良い」となっています。全部小文字に統一したい場合はlowerを設定します。

.sqlfluff

[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower

[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = lower

除外ワードの設定

筆者のプロジェクトでは小文字に統一した結果、一部の関数が動かくなる箇所が出ました。

Trinoの normalize関数をnormalize(str, 'NFKC')といった形式で呼び出しいる箇所がnormalize(str, 'nfkc')となってしまう、という事象です。

以下のようにignore_wordsに設定を追加することで、この問題を回避しました。

.sqlfluff

[sqlfluff:rules:capitalisation.identifiers]
ignore_words=NFD,NFC,NFKD,NFKC

Layout bundle

インデントや空白スペース、改行についてのルールになります。

ルール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	WITH句の閉じ括弧の次の行を改行
LT09	layout.select_targets	SELECT句で複数の列を指定する場合は改行
LT10	layout.select_modifiers	SELECT修飾子 (ex:`DISTINCT`) はSELECTと同一行
LT11	layout.set_operators	集合演算子 (ex:`UNION ALL`) は改行
LT12	layout.end_of_file	ファイルの末尾は改行
LT13	layout.start_of_file	ファイルの先頭はスペースで始めない

基本的にはデフォルトの設定を踏襲しましたが、行の最大文字数は80 -> 120に増やしました。

.sqlfluff

[sqlfluff]
max_line_length = 120

noqaコメント

「この行にはlintを適用しない」という設定です。SQLコメント形式で以下のように指定することで、特定のルールを無視することができます。(設定方法: https://docs.sqlfluff.com/en/stable/configuration.html#ignoring-errors-files)

SeLeCt  1 from tBl ;    -- noqa: CP02,CP03

積極的に活用するものではありませんが、改行が難しい行に対してLT05 (layout.long_lines)を適用するケースがありました。

注意点

ここからは、実際に導入してみての所感・やらかしをもとに得られた知見を挙げていきます。

自動整形できないルールがある

fixコマンドを実行しても適用されず、手動での修正が必要となるケースがあります。特にReferences bundleのルールはその傾向が強いです。

例えば、RF02 (references.qualification)は公式ドキュメントに以下のように書かれています。

Anti-pattern.sql

SELECT a, b
FROM foo
LEFT JOIN vee ON vee.a = foo.a

Best-practice.sql

SELECT foo.a, vee.b
FROM foo
LEFT JOIN vee ON vee.a = foo.a

これは考えてみれば当然で、sqlfluff側はテーブル定義の情報を知らず、どのカラムがどのテーブルに存在するかはわからないため、自動整形できないのでしょう。

PRSエラーが厄介

lintコマンド実行時に以下のようなエラーが出ることがあります。端的に言うと解析エラーというやつです。
SQLが不正である場合は当然こうなるのですが、正しく動作する場合でも起きることがあります。

L:   2 | P:  15 |  PRS | Line 2, Position 15: Found unparsable section:
                       | 'id string'

該当エラー行の末尾に -- noqa: PRSと付けることによりlintエラーは回避できますが、この状態になると該当SQLファイルに対してfixコマンドの自動整形が一切働かなくなり、全てのルールに対して手動整形を余儀なくされます。

エラーの原因について、ここからは推測になりますが、ベンターSQL特有のデータ型だと発生確率が高いように思えます。
筆者が確認したのはAWS Athenaでの以下のようなケースで、array (varchar)という型が原因であると見ています。下記の他、 row型にCASTしている箇所もエラーとなったことも、この推測の裏付けとなっています。

select
   cast(json_parse(feature) as array (varchar)) as feature -- noqa: PRS

中には、「最初はPRSエラーとなっていたが、SQLの怪しい箇所を直した結果解決した」というものもありました。
手動整形の作業も楽ではありませんが、SQLファイルを頻繁に変更することがない場合は初回だけの作業になる、と考えることもできます。

除外ルールの設定はほぼ必須

ルールの中には、適用すると挙動が変わってしまう危ないものもあります。

特に強調したいルールは ST06 (structure.column_order) です。以下のようにカラムの順序が変わってしまいます。

Anti-pattern.sql

select
    a,
    *,
    row_number() over (partition by id order by date) as y,
    b
from x

Best-practice.sql

select
    *,
    a,
    b,
    row_number() over (partition by id order by date) as y
from x

カラムが順不同でもうまく動作することもあるでしょうが、そうでないケースが圧倒的に多いと思いますので、以下のように設定ファイルに除外ルールを追加することを強く推奨します。

.sqlfluff

[sqlfluff]
exclude_rules = ST06

バージョンアップ時に差分が出る

Pythonのマイナーバージョンアップに伴い、sqlfluffのversionを2.3.2 -> 2.3.5に上げたらlintエラーとなってしまいました。
筆者が遭遇したケースでは「Configのデフォルト値が変わったこと」が原因だったのですが、バグfixや新規ルール追加によって挙動が変わるケースもあると考えられます。

一般的なプログラミング言語でのユニットテストと違い、SQLは「修正前後で結果が変わらないこと」を保証するには少々手間がかかります。基本的には自前で仕組みを構築する必要があり、ベンダーDWHのSQLだとCIでの実行に制約が増えるためさらに難易度が上がると思われます。

この課題に対してのベストな解決策は確立しておらず、現在も模索しながらの運用となっています。

既存プロジェクトへの導入は小さく始める

今回の最大の失敗は、一度にプロジェクト内のSQLファイル全てに対して全てのルールを適用してしまったことです。必然的にPull Requestでの差分も多くなってしまい、バグに気づけずにレビューで漏れてしまったというものになります。

部分的に適用するやり方はいくつかあります。

適用するファイル・フォルダを絞る (CLIコマンドの引数を変える)
適用するルールを増やしていく (設定ファイルのrules = allを上書き)
除外するルールを定義しておき、徐々に減らしていく (設定ファイルのexclude_rules = Noneを上書き)

プロジェクトの規模が大きくなるにつれて導入難易度が上がるのは一般的なプログラミング言語と同じですので、プロジェクト作成最初から導入されている状態が理想的ですね。

まとめ

個人的にSQLスタイルにはこだわりを持っているので、統一できたというのがとても良かったです。
既存のプロジェクトに導入すると苦労する点も多いと思いますが、新規プロジェクトを構築する場合は得られるメリットは大きいと感じました。

SQL Linter導入の背景

SQLFluffの機能紹介

インストール

lintの実行

formatの実行

対応しているSQL言語

適用する対象ファイルの指定

CLIコマンドとオプション

設定ファイル

ルール

Capitalisation bundle

除外ワードの設定

Layout bundle

noqaコメント

注意点

自動整形できないルールがある

PRSエラーが厄介

除外ルールの設定はほぼ必須

バージョンアップ時に差分が出る

既存プロジェクトへの導入は小さく始める

まとめ

参考記事