Zenn
🔍

SQLFluffを使ってSQLをリント（Lint）する

2021/05/13に公開
SQL
BigQuery
dbt
tech

SQLFluffって何？

Fishtown Analytics社（dbtを作っている会社）が作ったSQLのリントツールです。

Dataformやdbt等を活用することで、多人数でのSQLのメンテナンスを実行していく上でコーディング規約の策定やその準拠が出来ているかというのを継続的に保つ必要性がでてくると思います。

このSQLFluffは他のプログラム言語でよくあるリントがある開発を導入することができます。

SQLFluff

インストール

$ python --version
Python 3.6.7

3以上が必要

$ brew install pyenv

pythonのバージョンをコントロールすためにpyenvを入れる

$ pyenv install 3.6.7

$ pyenv global 3.6.7

3.6.7を標準で使えるようにする

$ python --version
Python 3.6.7

$ pip install sqlfluff

pipでインストール

利用検証

$ echo "  SELECT a  +  b FROM tbl;  " > test.sql
$ sqlfluff lint test.sql
== [test.sql] FAIL
L:   1 | P:   1 | L036 | Select targets should be on a new line unless there is
                       | only one select target.
L:   1 | P:   8 | L034 | Use wildcards then simple select targets before
                       | calculations and aggregates.
L:   1 | P:   9 | L006 | Missing whitespace before +
L:   1 | P:   9 | L006 | Missing whitespace after +
L:   1 | P:  11 | L039 | Unnecessary whitespace found.
L:   2 | P:   1 | L003 | Indent expected and not found compared to line #1
L:   2 | P:  10 | L010 | Inconsistent capitalisation of keywords.

L:は行数、P:は何文字目かを示しており、Lから始まるのはルール番号（Rules Reference）になり、どこがルールに違反しているのかが表示される

$ sqlfluff lint test.sql --rules L003,L009,L010

== [test.sql] FAIL
L:   2 | P:   3 | L003 | Indentation not hanging or a multiple of 4 spaces
L:   3 | P:   1 | L003 | Indent expected and not found compared to line #1
L:   3 | P:  10 | L010 | Inconsistent capitalisation of keywords.

ルールを指定して実行もできる（上記の場合はL003とL009とL010のルールが実行された

ルールのカスタム

実行する同一階層に.sqlfluffという設定ファイルを格納することで振る舞いを変更することができる

[sqlfluff:rules]
tab_space_size = 2

[sqlfluff:rules:L010]
capitalisation_policy = lower

ルール例

L001: 不必要なホワイトスペース

🙅🏻‍♂️ NG

SELECT
    a
FROM foo••

👍 OK

SELECT
    a
FROM foo

L002: 1行の中にタブとスペースが混ざっている

（→はタブ、•はスペース）
🙅🏻‍♂️ NG

SELECT
••→a
FROM foo

👍 OK

SELECT
••••a
FROM foo

L010: キーワードの大文字・小文字が統一されていない

🙅🏻‍♂️ NG

select
    a
FROM foo

👍 OK

select
    a
from foo

L016: 1行の最大文字数

0から1000で指定できる

L019: カンマの位置（先頭、行末）

[‘leading’, ‘trailing’]

trailing（行末）を指定した場合以下はエラーになる
🙅🏻‍♂️ NG

SELECT
    a
    , b,
    c
FROM foo

👍 OK

SELECT
    a
    , b
    , c
FROM foo

L022: WITH句のあとに1行空行をあけているか

🙅🏻‍♂️ NG

WITH plop AS (
    SELECT * FROM foo
)
SELECT a FROM plop

👍 OK

WITH plop AS (
    SELECT * FROM foo
)

SELECT a FROM plop

L023: WITH句のasの後ろに空白が入っているか

🙅🏻‍♂️ NG

WITH plop AS(
    SELECT * FROM foo
)

SELECT a FROM plop

👍 OK

WITH plop AS (
    SELECT * FROM foo
)

SELECT a FROM plop

L030: 関数の小文字、大文字

L010の設定を継承。

BigQueryを想定したコーディング規約を元にしたリントルールを作ってみる

以下のような定義で設定してみようと思います。

  • L001: 不必要なホワイトスペース
  • L002: 1行の中にタブとスペースが混ざっている
  • L010: 予約語/関数は小文字
    • capitalisation_policy = lower
  • L019: カンマは文頭
  • L003: インデントはスペース2つ
    • tab_space_size = 2
  • L016: 1行の行数制限
    • ignore_comment_lines = True
    • indent_unit = space
    • max_line_length = 80
    • tab_space_size = 2

.sqlfluffのファイルに書き設定内容を掲載

[sqlfluff:rules]
tab_space_size = 2

[sqlfluff:rules:L010]
capitalisation_policy = lower

[sqlfluff:rules:L016]
ignore_comment_lines = True
indent_unit = space
max_line_length = 80
tab_space_size = 2

[sqlfluff:rules:L019]
comma_style = leading

ドキュメントの通り記載。
上記の設定ファイルを.sqlfluffに書き出し。

SELECT
  a+b  AS foo,
c AS bar from my_table

検証するSQLはこちら

$ sqlfluff lint test.sql --rules L001,L002,L003,L010,L016,L019

リントを実行する

== [test.sql] FAIL
L:   1 | P:   1 | L010 | Inconsistent capitalisation of keywords.
L:   2 | P:   8 | L010 | Inconsistent capitalisation of keywords.
L:   2 | P:  14 | L019 | Found trailing comma. Expected only leading.
L:   3 | P:   1 | L003 | Indentation not consistent with line #2
L:   3 | P:   3 | L010 | Inconsistent capitalisation of keywords.

エラーが表示される

select
  a+b as foo
  , c as bar
from my_table

指摘されたポイントを修正し再度実行すると

$ sqlfluff lint test.sql --rules L001,L002,L003,L010,L016,L019

何もエラーが表示されなかったら成功です

Discussion