この記事はスタンバイ 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.cfgtox.inipep8.ini.sqlfluffpyproject.toml
以下のように記述します。#を使ってのコメント・コメントアウトも可能です。
[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: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: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]
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)は公式ドキュメントに以下のように書かれています。
SELECT a, b
FROM foo
LEFT JOIN vee ON vee.a = foo.a
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) です。以下のようにカラムの順序が変わってしまいます。
select
a,
*,
row_number() over (partition by id order by date) as y,
b
from x
select
*,
a,
b,
row_number() over (partition by id order by date) as y
from x
カラムが順不同でもうまく動作することもあるでしょうが、そうでないケースが圧倒的に多いと思いますので、以下のように設定ファイルに除外ルールを追加することを強く推奨します。
[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スタイルにはこだわりを持っているので、統一できたというのがとても良かったです。
既存のプロジェクトに導入すると苦労する点も多いと思いますが、新規プロジェクトを構築する場合は得られるメリットは大きいと感じました。