はじめに
こんにちは、データエンジニアの加藤です。
クラシルのデータ基盤ではdbt(data build tool)を使ってデータを変換しデータウエアハウス・データマートを構築しています。
今回はdbtプロジェクトにSQLFuffを導入したので紹介します。
SQLFluffとは？
様々な種類のSQLに対応するリンターです。
Jinjaやdbtにも対応しておりコーディング規約に違反した記述を自動で修正してくれます。
SQLFluff is a dialect-flexible and configurable SQL linter. Designed with ELT applications in mind, SQLFluff also works with Jinja templating and dbt. SQLFluff will auto-fix most linting errors, allowing you to focus your time on what matters.
導入の背景
これまでdbtプロジェクト内で作成されたSQLにはコーディング規約がなくSQLを書く各人の経験や暗黙的な規約で書かれていました。 そこに新メンバーがジョインしたことで暗黙的に守られていた規約に対するコードレビューに時間がかかったり、修正する手間が発生しました。
そこで以下の価値を期待してSQLFluffを導入することを決めました。
- 暗黙的なコーディング規約の明確化
- 規約違反コードのレビューコストの軽減・削減
- 統一されたコーディングによる認知・キャッチアップコストの軽減
SQLFluffの導入
SQLFluffをインストールする
SQLFluffではPython 3が必要です。
$ pip install sqlfluff
以下のコマンドでインストールが成功していることを確認しましょう。 バージョンが表示されればOKです。
$ sqlfluff version 2.1.2
SQLFluffを試してみる
テスト用にtest.sqlを用意
$ cat test.sql
sELECt a, b
, c + d,
e
FRoM
hoge;
lintコマンドでコーディング規約に違反している箇所を確認できます。
--dialectには使用したいSQLを指定します。($ sqlfluff dialectsでも確認できます)
$ sqlfluff lint test.sql --dialect snowflake
以下のように結果が表示されます。
Lは違反のある行数をPは何文字目であるかを表しています。
CP01などはSQLFluffのルールのコードを表します。
以降は違反の説明とルール名が表示されます。
L: 1 | P: 1 | CP01 | Keywords must be consistently upper case.
| [capitalisation.keywords]
L: 1 | P: 1 | LT09 | Select targets should be on a new line unless there is
| only one select target.
| [layout.select_targets]
L: 1 | P: 1 | ST06 | Select wildcards then simple targets before calculations
| and aggregates. [structure.column_order]
L: 1 | P: 7 | LT02 | Expected line break and indent of 4 spaces before 'a'.
| [layout.indent]
L: 2 | P: 1 | LT02 | Expected indent of 4 spaces.
| [layout.indent]
L: 2 | P: 8 | LT04 | Found leading comma ','. Expected only trailing near
| line breaks. [layout.commas]
L: 2 | P: 10 | AL03 | Column expression without alias. Use explicit `AS`
| clause. [aliasing.expression]
L: 3 | P: 1 | LT02 | Expected indent of 4 spaces.
| [layout.indent]
L: 4 | P: 1 | CP01 | Keywords must be consistently upper case.
| [capitalisation.keywords]
fixコマンドでコーディング規約に合わせて自動修正できます。
$ sqlfluff fix test.sql --dialect snowflake
違反箇所の修正を試みるか聞かれるのでyを入力します。
==== finding fixable violations ====
== [test.sql] FAIL
L: 1 | P: 1 | CP01 | Keywords must be consistently upper case.
| [capitalisation.keywords]
L: 1 | P: 1 | LT09 | Select targets should be on a new line unless there is
| only one select target.
| [layout.select_targets]
L: 1 | P: 1 | ST06 | Select wildcards then simple targets before calculations
| and aggregates. [structure.column_order]
L: 1 | P: 7 | LT02 | Expected line break and indent of 4 spaces before 'a'.
| [layout.indent]
L: 2 | P: 1 | LT02 | Expected indent of 4 spaces.
| [layout.indent]
L: 2 | P: 8 | LT04 | Found leading comma ','. Expected only trailing near
| line breaks. [layout.commas]
L: 3 | P: 1 | LT02 | Expected indent of 4 spaces.
| [layout.indent]
L: 4 | P: 1 | CP01 | Keywords must be consistently upper case.
| [capitalisation.keywords]
==== fixing violations ====
8 fixable linting violations found
Are you sure you wish to attempt to fix these? [Y/n] ...
Attempting fixes...
Persisting Changes...
== [test.sql] FIXED
Done. Please check your files to confirm.
All Finished 📜 🎉!
[1 unfixable linting violations found]
成功するとtest.sqlが修正されます。
$ cat test.sql
SELECT
a,
b,
e,
c + d
FROM
hoge;
.sqlfluffを作成する
独自のコーディング規約を定義するためにプロジェクトのルートディレクトリに.sqlfluffを作成しましょう。
.sqlfluffを作成しなくてもデフォルトの設定で使用することが可能です。
今回はdbt Style Guideを参考にコーディング規約を整備していくことにしました。
基本的にはデフォルトと異なる設定のみを定義するようにしています。
[sqlfluff] dialect = snowflake # templaterにdbtを指定する場合はプラグインのインストールが必要です（後述） templater = dbt # SQLFluffで定義されているcoreルールのみを使用する # https://docs.sqlfluff.com/en/stable/rules.html#core-rules rules = core # dbt templaterの設定 # https://docs.sqlfluff.com/en/stable/configuration.html#installation-configuration [sqlfluff:templater:dbt] # 環境に合った設定をする project_dir = ./ profiles_dir = ~/.dbt/ profile = default target = dev # 各種ruleについては以下を参照 # https://docs.sqlfluff.com/en/stable/rules.html:title [sqlfluff:indentation] allow_implicit_indents = true [sqlfluff:rules:aliasing.table] aliasing = explicit [sqlfluff:rules:aliasing.column] aliasing = explicit [sqlfluff:rules:capitalisation.keywords] capitalisation_policy = lower [sqlfluff:rules:capitalisation.identifiers] 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 [sqlfluff:rules:ambiguous.column_references] group_by_and_order_by_style = implicit
dbt templaterをインストールする
dbt templaterはSQLFluffのデフォルトテンプレートでないためプラグインのインストールが必要です。
dialectに対応するdbt adapterとsqlfluff-templater-dbtをインストールします。
$ pip install dbt-snowflake sqlfluff-templater-dbt
今回はdbt templaterを使用しますが用途によってはjinja templaterを使用する方が有効な場合もあるためこちらを確認して状況に応じて適切なtemplaterを使用しましょう。
SQLFluffの使用
CLIでの使用
こちらと同じように実行します。
.sqlfluffがあるディレクトリでコマンドを実行することで自動でコーディング規約を読み込んでくれます。
$ sqlfluff lint models/test.sql
$ sqlfluff fix models/test.sql
dbt Cloud IDEでの使用
dbt Cloud IDEではデフォルトでsqlfmtを使ったフォーマットができます。
プロジェクトのルートディレクトリに.sqlfluffを作成することでSQLFluffでLintとFixを行うことができます。（注意: mainブランチや読み取り専用ブランチでは使用できません）
docs.getdbt.com
さいごに
dbtプロジェクトへSQLFuffの導入を紹介しました。 現在はSQLFluffを手動で実行する必要があるのでpre-commitやGitHub Actionsを使って必ずSQLFluffが実行されるようにCI/CDパイプラインを改善しコーディング規約の徹底を目指していきたいと考えています。加えて、今回はSQLFluffのcoreルールのみを適用しているのでチームで議論しながらより効果的なコーディング規約を育てていきたいと考えています。
