Google Cloudのデータエンジニアをしています、はんざわです。
今回はSQLのリンターであるSQLFluffを触りながら理解を深めたいと思います。
検証環境
macOS： 13.3.1
Python： 3.9.5
SQLFluffとは
SQLFluffとは、SQLのフォーマットを自動で問題点の指摘や修正をしてくれるオープンソースサービスです。
さっそくインストールして使ってみたいと思います。
インストール
SQLFluffをインストールするにはPython3が必要です。
$ pip3 install sqlfluff正常にインストールできているか確認します。
$ sqlfluff version
2.0.7インストールが完了しました。実際にクエリを用意し、使ってみたいと思います。
さっそく使ってみる
sqlfluffには大きくlintとfixの2つの機能があります。
まずはlintを使ってみます。
※ 今回はBigQueryを想定した設定にしています
lintとは
lintの公式ドキュメント
lintはSQLのクエリを読み込んで問題点を指摘してくれます。
例として、以下のようなクエリを用意しました。
$ cat example1.sql
SELECT
AAA, BBB, CCC
fRom
TMPこのクエリをlintに読み込ませてみます。
$ sqlfluff lint example1.sql --dialect=bigquery
== [example1.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: 8 | LT09 | Select targets should be on a new line unless there is
| only one select target.
| [layout.select_targets]
L: 2 | P: 1 | LT02 | Expected indent of 4 spaces.
| [layout.indent]
L: 3 | P: 3 | CP01 | Keywords must be consistently upper case.
| [capitalisation.keywords]
L: 4 | P: 1 | LT02 | Expected indent of 4 spaces.
| [layout.indent]
L: 4 | P: 4 | LT12 | Files must end with a single trailing newline.
| [layout.end_of_file]
All Finished ? ?!ここで出力の左からLは何行目かを表し、Pは何文字目かを表します。
その隣のLT〇〇やCP〇〇がエラーコードでエラーメッセージも一緒に確認できます。
エラーコードは次の公式ドキュメントで確認できます。
簡単なクエリのサンプル付きなのでエラーのクエリと比較することができ、非常に便利です。
dialectとは
最初のコマンドの引数で--dialect=bigqueryと指定しました。dialectは必須引数でどの種類のSQLを使うか指定する必要があります。
dialectは以下の中から選べます。
$ 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']
materialize: materialize dialect [inherits from 'postgres']
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']
tsql: tsql dialect [inherits from 'ansi']ルールのカスタマイズ
SQLFluffは全ルールや各ルールのカスタマイズ、ルールの適用範囲など細かく独自のルールをカスタマイズすることができます。
ルールの設定変更は、以下のファイルに設定を書き込むことで変更することができます。
今回の検証では.sqlfluffを使います。
- setup.cfg
- tox.ini
- pep8.ini
- .sqlfluff
- pyproject.toml
設定ファイルを以下のように設定し、作業ディレクトリ配下に設置しました。
[sqlfluff]
# 上記のdialectの中から選ぶ
dialect = bigquery
# ルールの中から取り除くものを選ぶ
exclude_rules = L036この例の場合、L036のルールを除いた全てのルールが適用されます。
L036はSELECT句の対象が1つでない場合は改行しましょうというルールです。この例ではこのルールを取り除いているため、以下のようなクエリでも指摘されません。
$ cat example2.sql
SELECT
AAA, BBB, CCC
FROM
TMP;
$ sqlfluff lint example2.sql
All Finished ? ?!このようにルールをlintに適用することができました。
この記事ではルールについて簡潔にしか説明していませんので（ルールの量が非常に多いため）公式ドキュメントをしっかり読むことを推奨します。
fixとは
fixの公式ドキュメント
lintはルールに沿って、問題点を指摘するだけでしたが、fixはクエリを修正してくれます。
$ cat example1.sql
SELECT
AAA, BBB, CCC
fRom
TMP
$ sqlfluff fix example1.sql --dialect=bigquery
==== finding fixable violations ====
== [example1.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: 8 | LT09 | Select targets should be on a new line unless there is
| only one select target.
| [layout.select_targets]
L: 2 | P: 1 | LT02 | Expected indent of 4 spaces.
| [layout.indent]
L: 3 | P: 3 | CP01 | Keywords must be consistently upper case.
| [capitalisation.keywords]
L: 4 | P: 1 | LT02 | Expected indent of 4 spaces.
| [layout.indent]
L: 4 | P: 4 | LT12 | Files must end with a single trailing newline.
| [layout.end_of_file]
==== fixing violations ====
7 fixable linting violations found
Are you sure you wish to attempt to fix these? [Y/n]上記のコマンドを入力するとAre you sure you wish to attempt to fix these? [Y/n]と聞かれるのでYと入力すると以下のように続き、クエリを自動で修正してくれます。
Attempting fixes...
Persisting Changes...
== [example1.sql] FIXED
Done. Please check your files to confirm.
All Finished ? ?!
$ cat example1.sql
SELECT
AAA,
BBB,
CCC
FROM
TMPfixもlint同様にルールを設定することができます。
おまけ： SQLFluffのVScode向け拡張機能を使ってみる
VScodeにSQLFluffの拡張機能を追加し、使ってみたいと思います。
利用するのは以下の拡張機能です。
VScodeの拡張機能検索画面でsqlfluffと検索し、インストールします。
細かなルールの設定はワークディレクトリ配下に.vscode/settings.jsonを設置し、追記することで変更できるようです。
公式ドキュメントに記載のようにsettings.jsonに直接ルールを記述することもできます。
"sqlfluff.config": "${workspaceFolder}/.sqlfluff",
"sqlfluff.dialect": "mysql",
"sqlfluff.excludeRules": ["L009"],
"sqlfluff.executablePath": "sqlfluff",
"sqlfluff.ignoreLocalConfig": false,
"sqlfluff.ignoreParsing": false,
"sqlfluff.rules": [],
"sqlfluff.suppressNotifications": false,
"sqlfluff.workingDirectory": "",
/* Linter */
"sqlfluff.linter.arguments": [],
"sqlfluff.linter.run": "onType",
"sqlfluff.linter.diagnosticSeverity": "error",
"sqlfluff.linter.diagnosticSeverityByRule": [
{
"rule": "L010",
"severity": "warning"
}
],
"sqlfluff.linter.lintEntireProject": true,
/* Formatter */
"sqlfluff.format.arguments": ["--FIX-EVEN-UNPARSABLE"],
"sqlfluff.format.enabled": true,もしくは、.sqlfluffにルールを記載し、settings.jsonでパスだけ繋ぎルールを適用することもできます。
以下のような構造で.sqlfluffのルールをSQLに適用することができます。
チームで開発するようなケースの場合は、この方法の方がいいかもしれませんね。
/* ディレクトリ構造を確認 */
$ tree -a ./
./
├── .vscode
│ └── settings.json
├── linter
│ └── .sqlfluff
└── sql
├── example1.sql
└── example2.sql
4 directories, 4 files
/* ファイルの中身を確認 */
$ cat .vscode/settings.json
{
"sqlfluff.config": "${workspaceFolder}/linter/.sqlfluff"
}まとめ
今回はSQLFluffの使い方を紹介しました。
あくまで単体での使い方を紹介しましたがさらにGitHub Actionsなどと組み合わせたCI/CDの構築なども今後紹介したいと思います。
SQLのフォーマットをチーム独自で設定し、コードチェックと運用を行っていくことは非常に困難です。これらの処理を機械にやらせることでチームで統一させることができますので是非使ってみてください。
2
