はじめに
- SQLの書き方は開発対象や人・チーム事情によっても様々なので、チーム開発で利用しているSQLFluffの設定を公開してみる。
- なぜこのような設定・ルールになったか理由を紹介します。
- ちなみに、私達のチームではBigQueryで比較的多くのカラム数やstruct型によるネスト構造を扱っているので、他のDBや他のチームでは合う/合わないはあるかもしれない。
SQLFluff 設定
下記がチームメンバーで合意した SQLFluff 設定。
なお、下記参考として公式の Rules Reference です。
[sqlfluff]
dialect = bigquery
max_line_length = 120
exclude_rules =
# joinするときTable Aliasを使いたいので除外する
aliasing.forbid,
# Table Aliasのas句の省略ができるように除外する
aliasing.table,
# unionで * を使いたいとき違反になるため除外する
ambiguous.column_count,
# selectの末尾カンマを許容する
convention.select_trailing_comma,
# struct型のフィールド省略を使おうとすると違反になるので除外する
references.qualification,
# * exceptで違反になる場合があるので除外する
structure.column_order,
[sqlfluff:indentation]
tab_space_size = 2
# inner join も left outer join も明示する
[sqlfluff:rules:ambiguous.join]
fully_qualify_join_types = both
# 空白とコメントのみの行は無視する
[sqlfluff:rules:layout.long_lines]
ignore_comment_lines = True
# 小文字指定できるもの(予約語、リテラル、関数、識別子、型名)は小文字で統一
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower
[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower
[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = lower
# マクロを使うのでパス指定
[sqlfluff:templater:jinja]
load_macros_from_path = macros
なぜこうしたか
コメントに書いたままのものもあるがもう少し説明すると、、
max_line_length = 120
デフォルト値の80文字だと制限が厳しく、見やすく規則性を保てない形で sqlfluff fix されるケースが多々ありました。例を挙げると下記のような場合です。
# max_line_length = 80 の場合
select
(
select value.string_value
from unnest(event_params)
where key = 'transaction_id'
) as transaction_id,
(select value.string_value from unnest(event_params) where key = 'source')
as `source`,
(select value.string_value from unnest(event_params) where key = 'medium')
as medium,
(
select value.string_value from unnest(event_params) where key = 'campaign'
) as campaign,
:
max_line_length = 120 なら同じ下記のクエリで sqlfluff lint が通るので見やすいです。
※補足：Zenn記事だと横スクロールになるが、開発・運用で使うIDEやGithubページは横スクロールにならない長さ。
# max_line_length = 120 の場合
select
(select value.string_value from unnest(event_params) where key = 'transaction_id') as transaction_id,
(select value.string_value from unnest(event_params) where key = 'source') as `source`,
(select value.string_value from unnest(event_params) where key = 'medium') as medium,
(select value.string_value from unnest(event_params) where key = 'campaign') as campaign,
:
除外：ambiguous.column_count
これはルール名からルールの内容を想像しにくいですが、下記のように union で * を使いたいとき違反になるため除外しました。数個とかなら全てのカラムを書くのは辛くないですが、20個を超えるカラム数だったりすると可読性が犠牲になりがちです。
select * from foo
union all
select * from bar
除外：references.qualification
これは先ほどのルールと除外理由は近いですが、struct型のフィールド省略を使おうとすると違反になるため除外しました。
select
utm.source,
utm.medium,
utm.campaign,
utm.term,
:
例えば、このようにstruct型のumtカラムがあったとき、本ルールを除外することで下記のようにフィールド名を省略した簡潔な表現ができます。フィールド数が多ければ多いほどこの恩恵をありがたく思えます。
select
utm,
:
除外：convention.select_trailing_comma
BigQueryではselectの末尾のカンマはSyntax Errorにならないので、カラム追加のときなどで変更箇所が減り git blame でも変更元が追いやすくなるので、末尾のカンマは許容することにしました。
除外：structure.column_order
下記のように select の直後以外で * except を使いたいときがあるので除外しました。
select
concat(a, b),
* except (a, b),
：
fully_qualify_join_types = both
デフォルト値は inner で join だけなら inner join に補正するで十分ではという意見もありましたが、Bizメンバー含め社内でクエリを公開しており、SQLに明るくない一部メンバーが left join が外部結合であることを意識できていないケースもありそうなので、あえて left outer join の outer も明示することにしました。
capitalisation_policy / extended_capitalisation_policy = lower
予約語、リテラル、関数、識別子、型名といった小文字に指定できるものは小文字で統一しました。見やすさについては大文字を使わずともIDEやエディターなどによる色付けで十分で、全て小文字にすることで読むのも書くのもクエリ自体の中身に集中できます。
予約語などを大文字で書くのが今は一般的だと思いますが、SQLFluff/dbtのDocsにあるクエリは小文字ですし、今後小文字派が増えることに少し期待してたりします。
参考にしたもの
公式ドキュメント
公式ドキュメントを読むと、下記の新プロジェクト向けおすすめ設定があり参考にしました。SQLFluff/dbtのドキュメントで書かれているクエリはこの設定に近しいので、dbt Labs社が好んでいるクエリのスタイルが垣間見れますね。
既存のクエリ
SQLFluffの設定が自分たちのユースケースに合ってるかどうかを確認するには、やはり既存のクエリに適用しながら設定を少しずつ変えたりして試行錯誤していくのがよいと思います。
短い単純なクエリではデフォルトの設定で十分に思えることが多いですが、長く複雑なクエリが多い環境の場合は特に設定・ルールをユースケースに合わせて調整していく必要性が大きくなるでしょう。
まとめ
最初からこのSQLFluffの設定でいくと決めてやっているのではなく、日々の運用を通じて気になった点が出てきたら設定・ルールを微調整して運用・開発を進めています。
わりと設定が落ち着いてきたので公開してみましたが、どなたかの参考になれば嬉しいです。
Discussion