Zenn
🐡

僕なりのSQLスタイルガイドを定義してみる

2022/05/31に公開
SQL
dbt
tech

なぜ定義するか

  • コードは書く時間より読む時間の方が長い、SQLも例外ではないと思っています、読みやすく(理解しやすく)するためにスタイルガイドを使いたいと思っています
  • どういうクエリが「読みやすい」かは人によって差異があると思います、それぞれの「読みやすい」をチーム内ですり合わせるためにスタイルガイドを使いたいと思っています
  • スタイルガイドで「ここまでは揃える」を定義すると、「ここからは個人の自由で」という部分を明確にできます、これは余計なレビューコストを下げるのに役立つはずだと思っています
    • スタイルガイドを定義できればlinterに指摘してもらえます、人間に指摘されるより機械に指摘された方が心理的安全性が高いと思っています
      • 指摘する方もストレスなんやで
      • ロジックに関わらない部分で余計な議論を生むより、人間はロジックを議論した方が生産性が高いはず

僕はこれらの恩恵が得られると思っています

基本方針

dbt-labsが定めているスタイルガイドに従う

https://github.com/dbt-labs/corp/blob/main/dbt_style_guide.md

  • DWHを作っていく上でdbtがメインになっていく気がしています、なのでdbtが掲げているスタイルガイドに乗っかるのは個人的にいい方向性なのではと感じています、主観です
    • 対抗馬としてGit Labのスタイルガイドもあります、ご自身の組織に応じてどっちに乗っかるのか決めてもいいと思います
  • 弊社ではTTPSという言葉があります、徹底的にパクって進化させるという意味です、基本はdbtのSQLスタイルガイドに乗っかった上で自分達の色を加えるのが良いと思っています
    • 困った時に指針があると喧嘩にならなくていいと思ってます

dbt-labsのSQLスタイルガイド

  • カンマは末尾につけましょう
  • インデントはスペース4つにしましょう
  • 1行は80文字以下にしましょう
  • フィールド名、関数名は全て小文字にしましょう
  • asはフィールド、テーブルに別名をつけるときはつけるようにしましょう
  • 集計は他のテーブルと結合する前にできるだけ早く実行しましょう
  • order byやgroup byをするときは列名を列挙するよりも番号で並べましょう
    • もしいくつものカラムでgroup byをする必要があるときはモデル設計を見直す価値があるかもしれません
  • unionよりunion allの方が好ましい
  • joinする際にcなどの頭文字でエイリアスするのはやめましょう
  • 2つ以上のテーブルをjoinする場合は、カラム名の前にテーブルのエイリアスをつけましょう、1つのテーブルから選択するだけなら不要です
  • joinは明示的に書きましょう(joinではなくinner joinのように)、また、left joinを基本として、right joinするときはfromで参照するテーブルとjoinで参照するテーブルを入れ替えましょう
  • 行数を短くすることに最適化するのはやめましょう、改行は安い、(行数を短くすることによって生じる読みにくさが消費する)脳のリソースは高い！

上記をもとにしたSQL(dbt)のサンプル(引用元)

with

my_data as (

    select * from {{ ref('my_data') }}

),

some_cte as (

    select * from {{ ref('some_cte') }}

),

some_cte_agg as (

    select
        id,
        sum(field_4) as total_field_4,
        max(field_5) as max_field_5

    from some_cte
    group by 1

),

final as (

    select [distinct]
        my_data.field_1,
        my_data.field_2,
        my_data.field_3,

        -- use line breaks to visually separate calculations into blocks
        case
            when my_data.cancellation_date is null
                and my_data.expiration_date is not null
                then expiration_date
            when my_data.cancellation_date is null
                then my_data.start_date + 7
            else my_data.cancellation_date
        end as cancellation_date,

        some_cte_agg.total_field_4,
        some_cte_agg.max_field_5

    from my_data
    left join some_cte_agg  
        on my_data.id = some_cte_agg.id
    where my_data.field_1 = 'abc'
        and (
            my_data.field_2 = 'def' or
            my_data.field_2 = 'ghi'
        )
    having count(*) > 1

)

select * from final

自分の色を加える

基本的にdbtのスタイルガイドに乗っかりますが、これはoptionalでいいなってものは外したりします
同時に僕の解釈も追加します(頭ごなしにdbtが神だろって書くと僕が介在する余地がないので)

  • カンマは末尾につけましょう
    • カンマを頭につける方もいると思います
    • カンマが頭にあった方が、フィールドが何個あるか数えやすいという意見に納得したことがあります
    • しかし、あまりフィールドの個数を数えることがないのと、多くのフィールドを並べているときはモデルの作り方を見直した方がいいかもと思い始めました
    • カンマが頭にあるときdiffが少なくて済むという意見も同僚からもらいました、これはなるほどなと思いました、フィールドの追加が多く発生する場合はカンマは頭につけるのもありかもと思いました
    • 僕の場合は、そのようなことはそれほど多く発生しないので、カンマが後ろの方が読みやすく感じたため後ろにするようにしました
  • インデントはスペース4つにしましょう
    • ここは結構決めで4にしています
      • BQは2ですが、Googleは2にしたがる傾向があると思っています
    • 強いて言うならあんまりネストしすぎると読みづらいので、4という比較的厳しい条件をつけてあまりネストしすぎないように意識するのはいいなと思っています
  • 1行は100文字以下にしましょう
    • 80から100にしました
    • 80は結構少ないです
    • 世のディスプレイの解像度高くなっているし100くらいでも良いと思っています
  • フィールド名、関数名は全て小文字にしましょう
    • ここが結構火種になること多いと思います
    • 僕は小文字がいいと思っています
    • 大文字がいいという意見には、予約語とフィールドを見分けるために予約語は大文字が良いと聞きますが、他の言語で予約語をわざわざ大文字にすることってあまりないと感じています
    • なら小文字の方が読みやすくない？と思い小文字の方がいいなと思っています
  • asはフィールド、テーブルに別名をつけるときはつけるようにしましょう
    • 「あ、asって省略できるんだ」って言われてドヤ顔したことがある僕ですが、明示できるものは明示した方がいいと思います、コードは読む時間の方が長いので
  • 集計は他のテーブルと結合する前にできるだけ早く実行しましょう
    • 先に計算処理をして、属性情報は後でjoinしましょうという意味だと思っています
    • そのほうがクエリ全体がスッキリするからこれは心がけた方が良いと思っています
  • unionよりunion allの方が好ましい
    • BigQueryにunionがないので肌感が湧いていないのですが、意図せずレコードが落ちる可能性のある危険性は回避したほうが安全だと思います
  • joinする際にcなどの頭文字でエイリアスするのはやめましょう
    • 他の言語書くときに一文字の変数なんて滅多につけないように、クエリでもこれはやめましょう
    • Tの後にT1、T2って続いていくのも読むのが辛くなってしまうので意味を持ったエイリアスをつけてあげましょう
  • 2つ以上のテーブルをjoinする場合は、カラム名の前にテーブルのエイリアスをつけましょう、1つのテーブルから選択するだけなら不要です
    • 明示できるものは明示した方がいいと思います、コードは読む時間の方が長いので
  • joinは明示的に書きましょう(joinではなくinner joinのように)、また、left joinを基本として、right joinするときはfromで参照するテーブルとjoinで参照するテーブルを入れ替えましょう
    • 明示できるものは明示した方がいいと思います、コードは読む時間の方(ry
  • 行数を短くすることに最適化するのはやめましょう、改行は安い、(行数を短くすることによって生じる読みにくさが消費する)脳のリソースは高い！
    • フィールドは改行して並べるのもそうだし、CTEの間に改行を入れることなどがこれにつながると思っています

optionalにしたもの

order byやgroup byをするときは列名を列挙するよりも番号で並べましょう

これはoptionalにしました
僕はこの記事にとても賛成しているのでどちらかというと番号で並べているのを推しています
番号で並べても理解しやすいくらいのgroup byにするのが設計的に適していると感じているという部分にも賛成しています
ただ、列名ごとに計算するよって書き方も読みやすいとは思います
ここは揃っていなくてもlinterで注意されることもないのでかっちり揃えなくてもいいのではというのが僕の考えです

追加したいもの

  • 予約語は使わないようにしましょう
    • timestampとか使ってしまいがちですがタイムスタンプ型はhogehoge_atのように_atというsuffixで統一するなどするといいです(参考)
    • 順番を表す意味でorderとか使われてると、まさかそんなところでエラーが出てるなんてと発狂しそうになります

大きい方針(火種になりそうなところ)で挙げておきたいのは以上になります
細かいところはSQLのlinterであるSQLfluffを使って修正してしまうのがいいと思います

これらのものを踏まえた.sqlfluff
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 100
indent_unit = space
comma_style = trailing

[sqlfluff:rules:L010]
capitalisation_policy = lower

[sqlfluff:rules:L011]
# Aliasing preference for tables
aliasing = explicit

[sqlfluff:rules:L012]
# Aliasing preference for columns
aliasing = explicit

[sqlfluff:rules:L014]
capitalisation_policy = lower
unquoted_identifiers_policy = aliases

[sqlfluff:rules:L016]
ignore_comment_lines = True

[sqlfluff:rules:L057]
additional_allowed_characters = "-"

[sqlfluff:templater:dbt]
project_dir = .
profiles_dir = .
profile = hogehoge
target = dev

最後に

繰り返しになりますが、頭ごなしに僕のスタイルを適用しろっていうつもりは毛頭ありません
ご自身の環境や組織、チームの文化に合わせてスタイルは決めていってください
僕のスタンスとしては

  • 火種になりそうなスタイルは関係者と合意して、あとはlinterに任せる
  • できるだけdbtが提唱している思想に乗っかる
    です
    この記事が、火種にならず「たたき」になることを願っています

参考にさせていただいたもの

https://qiita.com/kai_data/items/6c119c43ad3626226dfc

https://techlife.cookpad.com/entry/2016/11/09/000033

Discussion