【SQL】SQL Linter ～ SQLFluff ～

SQL

■ はじめに

SQL の Linter (リンター) について
使用したいって旨を頂いたので、調べてみたら
「SQLFluff」ってのがでてきたので、メモっておく

今回のこととは、まったく関係ないが、業務で以下のサイト教えてもらった。
キャリア形成や面接などで使えそう。

https://roadmap.sh/
Backend Developer
https://roadmap.sh/backend
Full Stack Developer
https://roadmap.sh/full-stack

目次

【１】SQLFluff
　１）主な機能
　２）ライセンス
【２】サポートしているDB
　１）補足：sqlfluff dialects
【３】環境構築
　１）VS Code拡張：vscode-sqlfluff
【４】Hello world
【５】CLI Reference
　１）lint
　２）fix
　３）format
　４）parse
【６】Rules Reference

【１】SQLFluff

* Fishtown Analytics社（dbtを作っている会社）製のSQLリントツール
* Python製

cf. Fluff (フラフ) = 綿毛、表面的なもの、へま、失敗、しくじり、〈米俗〉拒絶、拒否

https://docs.sqlfluff.com/en/stable/
Github
https://github.com/sqlfluff/sqlfluff

１）主な機能

# Function Explanations Memo
1 lint チェック 【５】の１）を参照
2 fix SQL自動修正 【５】の２）を参照

２）ライセンス

* MIT license
 => 無料で使える！

https://github.com/sqlfluff/sqlfluff/blob/main/LICENSE.md

【２】サポートしているDB

* 主要なDBは、ほぼほぼカバーしてそう

https://docs.sqlfluff.com/en/stable/dialects.html

* MySQL
* Oracle
* PostgreSQL
* Redshift
* Snowflake

などなど

１）補足：sqlfluff dialects

# 以下のコマンドでも確認できる
sqlfluff dialects

【３】環境構築

https://docs.sqlfluff.com/en/stable/gettingstarted.html

# 前提条件となるソフトウェアの確認
python --version
pip --version

# インストール
sudo pip install sqlfluff

# pip install sqlfluff
# だと「sqlfluff: command not found」になったので
# sudo つけてインストールした

# 確認
sqlfluff --version

１）VS Code拡張：vscode-sqlfluff

* VS Code用のプラグインもある模様

https://github.com/sqlfluff/vscode-sqlfluff

【４】Hello world

# 動作確認用SQL生成 (SELECTの前に空白行)
$ echo "  SELECT a  +  b FROM tbl;  " > test.sql

# Linter実行（--dialect 【対象DB】）
$ 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:  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:  27 | LT01 | Unnecessary trailing whitespace.
                       | [layout.spacing]
All Finished � �!

【５】CLI Reference

~$ sqlfluff --help

Usage: sqlfluff [OPTIONS] COMMAND [ARGS]...

  SQLFluff is a modular SQL linter for humans.

Options

Options Explanations
--version バージョン表示
-h, --help ヘルプ表示

Commands

Options Explanations
dialects 現状使用可能なDBを表示
fix SQLファイルの修正
format SQLファイルの自動フォーマット
lint Lint SQL files via passing a list of files or using stdin.
parse SQLのパース結果を表示
render Render SQL files and just spit out the result.
rules 現状使用可能なルールを表示
version sqlfluffのバージョン表示

Examples 

sqlfluff lint --dialect postgres .
sqlfluff lint --dialect postgres --rules ST05 .
sqlfluff fix --dialect sqlite --rules LT10,ST05 src/queries
sqlfluff parse --dialect sqlite --templater jinja src/queries/common.sql

https://docs.sqlfluff.com/en/stable/cli.html

Configuration

https://docs.sqlfluff.com/en/stable/configuration.html

Options Default Explanations
max_line_length 80 一行の最大行数

１）lint

* フォーマットチェック

https://docs.sqlfluff.com/en/stable/cli.html#sqlfluff-lint

Options Explanations memo
--nofail エラーにならずに exit code : 0を返す https://docs.sqlfluff.com/en/stable/cli.html#cmdoption-sqlfluff-lint-nofail
--write-output <write_output> ファイル出力（かつ標準出力。なので tee コマンド的に使える） https://docs.sqlfluff.com/en/stable/cli.html#cmdoption-sqlfluff-lint-write-output
-f, --format 出力フォーマット (human/json/yaml/github-annotation/github-annotation-native/none) https://docs.sqlfluff.com/en/stable/cli.html#cmdoption-sqlfluff-lint-f 
* 「--format github-annotation-native」は、以下の関連記事でも使っている

Github Actions ～ pull_request / pull_request_target ～
https://dk521123.hatenablog.com/entry/2024/04/10/152101

コマンド例 

$ sqlfluff lint test.sql --dialect snowflake -f yaml
- filepath: test.sql
  violations:
  - line_no: 1
    line_pos: 1
    code: LT01
    description: Expected only single space before 'SELECT' keyword. Found '  '.
    name: layout.spacing
  - line_no: 1
    line_pos: 1
    code: LT02
    description: First line should not be indented.
    name: layout.indent
  - line_no: 1
    line_pos: 1
    code: LT13
    description: Files must not begin with newlines or whitespace.
    name: layout.start_of_file
  - line_no: 1
    line_pos: 11
    code: LT01
    description: Expected only single space before binary operator '+'. Found '  '.
    name: layout.spacing
  - line_no: 1
    line_pos: 14
    code: LT01
    description: Expected only single space before naked identifier. Found '  '.
    name: layout.spacing
  - line_no: 1
    line_pos: 27
    code: LT01
    description: Unnecessary trailing whitespace.
    name: layout.spacing

２）fix

* SQLの書式を修正

https://docs.sqlfluff.com/en/stable/cli.html#sqlfluff-fix

コマンド例 

$ sqlfluff fix test.sql --dialect ansi

$ cat test.sql
SELECT a + b FROM tbl; << SELECT前の空白がなくなっている

３）format

* SQLの自動フォーマット

https://docs.sqlfluff.com/en/stable/cli.html#sqlfluff-format

コマンド例 

$ sqlfluff format ./sqls/test.sql --dialect snowflake
==== finding fixable violations ====
== [sqls/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:  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:  27 | LT01 | Unnecessary trailing whitespace.
                       | [layout.spacing]
== [sqls/test.sql] FIXED
5 fixable linting violations found

# 2回目は直っているので、エラーなし
$ sqlfluff format ./sqls/test.sql --dialect snowflake
==== finding fixable violations ====
==== no fixable linting violations found ====
All Finished � �!

４）parse

* SQLのパース結果を表示

https://docs.sqlfluff.com/en/stable/cli.html#sqlfluff-parse

コマンド例 

$ sqlfluff parse ./sqls/test.sql --dialect snowflake
[L:  1, P:  1]      |file:
[L:  1, P:  1]      |    whitespace:                                               '  '
[L:  1, P:  3]      |    statement:
[L:  1, P:  3]      |        select_statement:
[L:  1, P:  3]      |            select_clause:
[L:  1, P:  3]      |                keyword:                                      'SELECT'
[L:  1, P:  9]      |                [META] indent:
[L:  1, P:  9]      |                whitespace:                                   ' '
[L:  1, P: 10]      |                select_clause_element:
[L:  1, P: 10]      |                    expression:
[L:  1, P: 10]      |                        column_reference:
[L:  1, P: 10]      |                            naked_identifier:                 'a'
[L:  1, P: 11]      |                        whitespace:                           '  '
[L:  1, P: 13]      |                        binary_operator:                      '+'
[L:  1, P: 14]      |                        whitespace:                           '  '
[L:  1, P: 16]      |                        column_reference:
[L:  1, P: 16]      |                            naked_identifier:                 'b'
[L:  1, P: 17]      |                [META] dedent:
[L:  1, P: 17]      |            whitespace:                                       ' '
[L:  1, P: 18]      |            from_clause:
[L:  1, P: 18]      |                keyword:                                      'FROM'
[L:  1, P: 22]      |                whitespace:                                   ' '
[L:  1, P: 23]      |                from_expression:
[L:  1, P: 23]      |                    [META] indent:
[L:  1, P: 23]      |                    from_expression_element:
[L:  1, P: 23]      |                        table_expression:
[L:  1, P: 23]      |                            table_reference:
[L:  1, P: 23]      |                                naked_identifier:             'tbl'
[L:  1, P: 26]      |                    [META] dedent:
[L:  1, P: 26]      |    statement_terminator:                                     ';'
[L:  1, P: 27]      |    whitespace:                                               '  '
[L:  1, P: 29]      |    newline:                                                  '\n'
[L:  2, P:  1]      |    [META] end_of_file:

【６】Rules Reference

* 以下の公式ドキュメントを参照

https://docs.sqlfluff.com/en/stable/rules.html

* 日本語サイト

https://zenn.dev/gak_t12/articles/5e19585d53b862

参考文献

https://dev.classmethod.jp/articles/sqlfluff/
https://dev.classmethod.jp/articles/sqlfluff-2/
https://qiita.com/n-gondo123/items/455047a063d39789df2b
https://zenn.dev/takimo/articles/918a51c9bdd486
https://zenn.dev/dozo/articles/846b756d64fdc7
https://www.yasuhisay.info/entry/2023/10/14/165217
https://tokukichi.com/posts/913

関連記事

SQL Linter ～ SQLFluff / Custom rule ～
https://dk521123.hatenablog.com/entry/2024/03/31/232907
SQLFluffでエラー「ConfigLoader.get_global() is deprecated」が発生
https://dk521123.hatenablog.com/entry/2024/09/26/160549
SQL Linter ～ sqlfmt ～
https://dk521123.hatenablog.com/entry/2024/03/01/163922
Github Actions ～ SQL Linter ～
https://dk521123.hatenablog.com/entry/2024/03/04/180308
dbt ～ 入門編 ～
https://dk521123.hatenablog.com/entry/2023/05/30/151003
パッケージ管理 ～ pip ～
https://dk521123.hatenablog.com/entry/2021/07/02/000000
オフライン環境下で pip install するには
https://dk521123.hatenablog.com/entry/2021/07/10/164833
lnコマンド / update-alternativesコマンド
https://dk521123.hatenablog.com/entry/2024/02/25/233428
Github Actions ～ pull_request / pull_request_target ～
https://dk521123.hatenablog.com/entry/2024/04/10/152101