SQL用フォーマッター「SQLFluff」を使ってみた

SQLのコーディングスタイルは機械に任せる

データ分析

#ビジネス・アナリティクス

#データ分析

#SQL

たまちゃん

2021.01.13

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

大阪オフィスの玉井です。

プログラミング言語の書式をいい感じに修正してくれるツールは色々ありますが、SQL用って意外とありませんよね。下記のカンファレンスでSQLFluffというツールの存在を知ったので、使ってみました。

検証環境

macOS Catalina 10.15.7
Python 3.8.2
pip 19.2.3

インストール

SQLFluffは、Python3系がPCにインストールされている必要があります。

インストールには、pipを使います。

$ pip install sqlfluff

正しくインストールされているか確認するために、SQLFluffのバージョンを確認します。

$ sqlfluff version
0.3.6

とりあえず試してみる（`lint`）

まず、基本となるlintコマンドを試してみたいと思います。これは、SQL文の書き方としておかしい部分をチェックしてくれる機能になります。

めちゃくちゃ酷いSQL（本番でこんな書き方したら家を放火されるレベル）を用意します。

select id as customer_id             ,


first_name           ,







                    last_name

from 




customers

こちらに対して、lintを実行すると、下記のようになりました。

$ sqlfluff lint kitanai.sql
== [kitanai.sql] FAIL
L:   1 | P:  25 | L005 | Commas should not have whitespace directly before them.
L:   4 | P:   1 | L003 | Indent expected and not found compared to line #1
L:   4 | P:  11 | L005 | Commas should not have whitespace directly before them.
L:  12 | P:  21 | L003 | Line over-indented compared to line #1
L:  14 | P:   5 | L001 | Unneccessary trailing whitespace.
L:  19 | P:   1 | L003 | Indent expected and not found compared to line #14
L:  19 | P:   1 | L009 | Files must end with a trailing newline.

Lは何行目か、Pは何文字目か、L00Xというのは、SQLFluffに設定されている書式のルールです。例えば、1行目で指摘されているのは「カンマの直前に空白を入れないでください」という内容です。

ちなみに、リントの基準となるこのルールですが、最初からSQLFluffに設定されています。ですので、インストールした直後からこのように使い始められることができました。もちろん、このルールは自分でカスタマイズすることができます。

ルールのリファレンスは下記に載っています。

SQLの書式を修正する（`fix`）

先程はルールに沿ってない部分を指摘する機能にとどまりましたが、実際に書式を修正する機能もあります。注意点としては、「修正するルールを必ず指定する必要がある」ということです。

先程のSQLクエリを修正してみます。前述したように、修正したいルールを指定する必要があるので、今回はL003とL005のルールを修正してみます。

まず、lintが走り、この内容を修正するかどうか聞いてきます。

$ sqlfluff fix kitanai.sql --rules L003,L005
==== finding fixable violations ====
== [kitanai.sql] FAIL
L:   1 | P:  25 | L005 | Commas should not have whitespace directly before them.
L:   4 | P:   1 | L003 | Indent expected and not found compared to line #1
L:   4 | P:  11 | L005 | Commas should not have whitespace directly before them.
L:  12 | P:  21 | L003 | Line over-indented compared to line #1
L:  19 | P:   1 | L003 | Indent expected and not found compared to line #14
==== fixing violations ====
5 fixable linting violations found
Are you sure you wish to attempt to fix these? [Y/n]

YESを回答すると、下記のように実行されました。

Are you sure you wish to attempt to fix these? [Y/n] ...
Attempting fixes...
Persisting Changes...
== [kitanai.sql] PASS
Done. Please check your files to confirm.

実際に修正されたSQLはこちら。カンマに関する部分とインデントに関する部分が修正されました。

select id as customer_id,


    first_name,







    last_name

from 




    customers

ルールのカスタマイズ

前述したように、SQLFluffには初期設定の書式ルールが備わっていますが、当然自分たちの書き方に合わせたルールを使いたいと思います。もちろん、それもできます。

対象のSQLファイルと同じディレクトリに.sqlfluffファイルを作成し、そこに自分ルールを記述することができます。

[sqlfluff:rules:L010]
capitalisation_policy = lower

L010ルールは、クエリ中の予約語を、大文字か小文字のどちらかに統一するべき、というルールです。そして、このルールのデフォルトは、大文字に統一するようになっているのですが、上記のカスタムルールを指定することで、小文字統一をしてくれるようになります。

他にもルールのいじくり方は色々あるので、公式ドキュメントをご覧ください。

基準となるSQL（DB）を指定する

今まで何も考えず色々試してきましたが、ここらへんで大事なことに気づきました。

「このツールは、何のDBのSQLを元に動いているのか？」

一口にSQLといっても、DBやDWHによってSQLの文法は異なってきます。当然、それによって書式の修正も変わってきます。

SQLFluffは、デフォルト設定だとANSI SQLとしてリント等を行います。もし、他のDBを想定したSQLとしてリントを行いたい場合は、コマンド実行時に（DBを）指定することができます。

下記のコマンドで選択できるDBを確認することが出来ます。

$ sqlfluff dialects
==== sqlfluff - dialects ====
ansi:                 ansi dialect [inherits from 'nothing']
bigquery:            bigquery dialect [inherits from 'ansi']
mysql:                  mysql dialect [inherits from 'ansi']
teradata:            teradata dialect [inherits from 'ansi']
postgres:            postgres dialect [inherits from 'ansi']
snowflake:      snowflake dialect [inherits from 'postgres']

DBを指定して`lint`する

例えば、公式ドキュメントにあるSnowflakeのSQLを…（DATE_TRUNC — Snowflake Documentation）

select to_time('23:39:20.123') as "TIME1",
       date_trunc('MINUTE', "TIME1") as "TRUNCATED TO MINUTE";

Snowflakeとして扱うと下記のような結果ですが…

$ sqlfluff lint snowflakesample.sql --dialect snowflake
== [snowflakesample.sql] FAIL
L:   2 | P:   8 | L003 | Indentation not hanging or a multiple of 4 spaces
L:   2 | P:  62 | L009 | Files must end with a trailing newline.

BigQueryとして扱うと結果が変わりました。SnowflakeのクエリをBigQuery基準で見ているので、指摘事項は当然増えます。

$ sqlfluff lint snowflakesample.sql --dialect bigquery
== [snowflakesample.sql] FAIL
L:   1 | P:   8 | L013 | Column expression without alias. Use explicit `AS`
                       | clause.
L:   1 | P:  31 |  PRS | Found unparsable section: ' as "TIME1"'
L:   2 | P:   8 | L003 | Indentation not hanging or a multiple of 4 spaces
L:   2 | P:   8 | L013 | Column expression without alias. Use explicit `AS`
                       | clause.
L:   2 | P:  37 |  PRS | Found unparsable section: ' as "TRUNCATED TO MINUTE"'
L:   2 | P:  62 | L009 | Files must end with a trailing newline.