Zenn
👋

データ基盤のためのリーダブルSQL

2024/05/21に公開
SQL
BigQuery
Snowflake
dbt
idea

これは何？

  • 私tenajimaがデータ基盤のパイプラインを作るとき、レビューするときに意識している点を言語化したものです
  • データ基盤を作る上での考え方の一つに役立てていただければ幸いです

この記事の前提

  • dbtを使ったデータ基盤構築を念頭に置いて書いています、dbtの記法が出てきます
  • CTEsが使える環境を想定しています
  • 記事内でデータエンジニアもアナリティクスエンジニアも総称してデータエンジニアと呼んでいます

データ基盤を「使う側」のクエリと「作る側」のクエリの違い

最近ではファーストキャリアからデータエンジニアの方も出てきているかもしれませんが、データサイエンティスト、アナリスト、ソフトウェアエンジニアを経験してデータエンジニアを行っている人が一般的と考えています。
特にデータサイエンティスト、アナリストからデータエンジニアへの転向は私の周りでは多いように感じており、その方達は(過去の私も含め)最初に大きな考え方の違いに気づくことが重要と考えます。
それはデータ基盤を「使う側」のクエリの書き方と「作る側」のクエリの違いです。

使う側 作る側
データ抽出のためにショットでクエリを行う バッチ実行として定期的に動くクエリが実行される
自分が考えるケースのみの処理でも良いことが多い 境界値や例外処理、今は仕様にないが過去の仕様にはあったものも扱う必要がある
そのクエリのコストはショットで考えれば良い デイリーバッチならそのクエリが30日走った時に許容できるコストなのかを考える必要がある
ショットのクエリの時はそのクエリが動いて、妥当性が確かめられれば良い そのクエリを半年後の自分がリファクタする際に理解できるのか、他の人が読んだ時に理解できるのか、デバッグ、テストが行いやすい形になっているかを考える必要がある
書く時間が読む時間より長い 読む時間が書く時間より長い

正直最後だけ頭に置いてもらっても良いかなと思います。
書きやすいクエリと、読みやすいクエリは違うよね、を最初に理解して次に進んでいただければと思います。

なぜ読む時間を重視するか

  • データ基盤は作って終わりではなく、継続的に運用し、バグがあれば修正する必要があります
  • バグが出ないデータ基盤などもちろんなく、バグが出てしまっても発見・修正を早く行えるようなクエリを書く必要があると考えます
  • 読みやすさは脳のメモリの削減に役立つため、書くときはロジックのミスを低減、デバッグの際はバグの発見に一役買います
  • コード(クエリ)の仕様を知りたい時、チームのメンバーも未来のあなた自身もあなたのコード(クエリ)を読むからです
    • いくらドキュメントが充実していても、結局中身を見た方が理解が進むということはコードを書いてる時もよくあることです
    • データ基盤においてはクエリがコードになることが多いため、読みやすいクエリは将来のあなたたちの助けになります

なんか...似たような本読んだことあるぞ...?

はい、なんとなく感じてきた方も多いかもしれませんが「リーダブルコード」の資産をデータ基盤のクエリにも適用しようぜということです。
データ基盤のクエリもコードのように書こうぜ、が言いたいことです。

https://qiita.com/KNR109/items/3b14e2e8f89a33c0f959

クエリをコードのように書くこと、管理することの難しさ

少し前に「SQL 滅ぶべし」というスライドが話題になりました。
https://www.docswell.com/s/hoxo-m_inc/Z4Q8NL-2024-05-06-203800

私の考えとしては「SQLはコードとして上手に管理していくにはツールがあまりにもないから、SQLをコンパイルしてくれる他ツールがいいよねとなるの分かるよ」です。
ただし同時に、アナリストともエンジニアとも共通で使えるこの言語は、あまりにもデファクトになりすぎていて代替しきるのは難しいとも考えています。
なので今の気持ちとしては「SQLを代替しようぜ」よりも「管理しやすいSQLを書こうぜ」という気持ちが強いのです。

https://x.com/tenajima/status/1787823736948994242

リーダブルなSQLを目指して意識していること

ここまでの考えを理解してもらえたらこの記事の8割は完了しているのですが、具体的なtipsを少しだけ添えておきます。
SQLは現状「管理しやすいように書くのを頑張る！」という他あまりないのですがlinterを使いながら頑張る方法もあり、それについて書いた過去の記事もあるのでよかったら併せて読んでください。
先にまとめると、

  1. 適切なCTEsをつけてあげる
  2. サブクエリを使わない
  3. finalのCTEsを必ず使う
  4. import CTEsとFunctional CTEsを分けて書く
  5. (import) CTEsでは必要なものを明記する
  6. joinしたカラムにはjoin元のCTEsを明記する

意識してることとwhyを書き連ねていくスタイルで書いていきます。

適切なCTEsをつけてあげる

従わない場合

with
    T1 as (
        ...
    ),

    src as (
        ...
    ),

    all_data as (
        ...
    ),

why

  • プロダクションコード書いてる人は広いスコープに渡る変数にaとかxとか使わないですよね、感覚的にはそれと同じです
    • ただjoinする時だけだからとleft join {{ "dim_category" }} as Tというクエリはよく見られます
      • selct文が先に書かれる特性上、このjoin文を見る前にselect T.nameのようなselect文に出会ってしまうため、やはり適切なCTEsにしてあげてからjoinする方が脳に優しいと考えます
      • 「改行(や行数が増える)ことに使われるメモリより、脳のメモリの方が高い」とはどこかで読んだ好きな言葉です
  • 読み進めてるうちに「T1って何だっけ？」となると戻ってT1の中身を把握し直す必要が出てきてしまいます
  • src単体やjoinしたデータにall_dataと付けることもやってしまいがちかもしれません
    • 最初は一つのテーブルしか参照しないと思っていても、結果他のテーブルも読み込むことになった時に「src」とは？となってしまいます
    • all_dataは処理の内容かもしれませんが、それで何を表しているのかが不透明でリーダブルではないと考えます

サブクエリを使わない

従わない場合

select
    id,
    event_name,
    event_count,
from (
    select
        id,
        event_name,
        count(*)
    from {{ ref("fct_event") }}
    left join {{ ref("dim_category") }}
    group by id, event_name
)

why

  • 急に見たことない存在が飛び込んでくる
    • 上から読んでいき「event_countなんてどのテーブルでも見たことないぞ？」となります
    • event_countって何だっけ？と脳のメモリに置いた状態で読み進めていき、ようやくサブクエリのjoinを見たら解決します
  • サブクエリを許容するとサブクエリのサブクエリも許容する余地が生まれてしまいます
    • 二段ならまだ読める、では三段は...?よりロジックが複雑になったら...?
    • (ネストはできるだけ浅く！Pythonを書いてた時も師匠に口酸っぱく言われました)

finalのCTEsを必ず使う

従わない場合

with 
    fct_event as (
        ...
    ),

    logic_a as (
        ...
    )

select *
from logic_a

修正後

with 
    fct_event as (
        ...
    ),

    logic_a as (
        ...
    ),

    final as (
        select
            id,
            event_name,
        from logic_a
    )

select *
from final

というようにクエリを書くというものです。finalの中身を最後に書いてもいいですが、finalというCTEsを必ず作った方が良いと考えます。
特にfinalのselect文は selct * from を絶対に書かないようにすると最終アウトプットも明確になります。

why

  • デバッグのしやすさが上がります
  • logic_aのアウトプットを確かめたいときは最後のselect * from finalをselect * from logic_aに変えるだけで行えます

import CTEsとFunctional CTEsを分けて書く

Xのポストでも言及していたdbtが出しているStyle Guideでとても好きなやつです
https://docs.getdbt.com/best-practices/how-we-style/2-how-we-style-our-sql#import-ctes

従わない場合

with 
    logic_a as (
        select
            id,
            event_name,
            category
        from {{ ref("fct_event") }} as fct_event
        inner join {{ ref("dim_category") }} as dim_category using (id)
        order by event_timestamp
    ),

    final as (
        ...
    )

select *
from final

修正後

with 
    fct_event as (
        select
            id,
            event_name,
            event_timestamp,
        from {{ ref("fct_event") }}
    ),

    dim_category as (
        select
            id,
            category,
        from {{ ref("dim_category") }}
    ),

    logic_a as (
        select
            id,
            event_name,
            category
        from fct_event
        inner join dim_category using (id)
        order by event_timestamp
    ),

    final as (
        ...
    )

select *
from final

why

  • デバッグのしやすさが上がる
    • dbtでコンパイルしたクエリの気になる部分だけ実行できます(上で示したのと同様)
  • importとjoin、はたまたgroup byを同時に行っていると気にしなきゃいけないものが増える
    • 「event_nameってfct_eventが持ってるんだっけ？dim_categoryが持ってるんだっけ？」
    • 「event_timestampってfct_eventにはないんだっけ？両方あったらambiguous errorが出ちゃう気がするぞ」
    • これらを考えることは脳のメモリを多く使うことに繋がります
  • 前段のfct_eventの処理がおかしいからこのモデルがバグったのか、logic_aのCTEsの処理がおかしいからバグったのかの切り分けが行いやすくなります

(import) CTEsでは必要なものを明記する

上記のimportの規則に従ってもimport分でselect * していると似たような苦しみが生まれてしまいます。
括弧で括ったのは、データ基盤を作る時には基本的にselect * を使わない方がいいと考えているからです。

従わない場合

with 
    fct_event as (
        select
            * 
        from {{ ref("fct_event") }}
    ),

    dim_category as (
        select
            *
        from {{ ref("dim_category") }}
    ),

    logic_a as (
        select
            id,
            event_name,
            category
        from fct_event
        inner join dim_category using (id)
        order by event_timestamp
    ),

    final as (
        ...
    )

select *
from final

修正後

with 
    fct_event as (
        select
            id,
            event_name,
            event_timestamp,
        from {{ ref("fct_event") }}
    ),

    dim_category as (
        select
            id,
            category,
        from {{ ref("dim_category") }}
    ),

    logic_a as (
        select
            id,
            event_name,
            category
        from fct_event
        inner join dim_category using (id)
        order by event_timestamp
    ),

    final as (
        ...
    )

select *
from final

why

  • 脳のメモリの削減につながります
    • 「event_nameってfct_eventが持ってるんだっけ？dim_categoryが持ってるんだっけ？」
    • 「event_timestampってfct_eventにはないんだっけ？両方あったらambiguous errorが出ちゃう気がするぞ」
    • これらを考えることは脳のメモリを多く使うことに繋がります
  • どうしてもSQLという言語は大事な情報が後から出てきます
    • 最初に全部importして後で使うもののみ列挙しても最近のOLAPは賢いので最適化してくれてしまいますが、人間の脳には優しくありません
    • 使うものはあらかじめ宣言してあげる方が、コードで変数を宣言するように読みやすいと考えます
  • 「でもselect *してた方が、カラム追加会った時に吸収できるんじゃない？」という意見に対して
    • 確かにその通りですが、それは書く手間と時間を最小化している訳で、読む時間は増えていると考えます
    • 読む時間の最小化を目指すためには明記しているべきと考えています

joinしたカラムにはjoin元のCTEsを明記する

従わない場合

with 
    fct_event as (
        select
            id,
            event_name,
            event_timestamp,
        from {{ ref("fct_event") }}
    ),

    dim_category as (
        select
            id,
            category,
            sub_category,
        from {{ ref("dim_category") }}
    ),

    logic_a as (
        select
            id,
            event_name,
            category
        from fct_event
        inner join dim_category using (id)
        order by event_timestamp
    ),

    final as (
        ...
    )

select *
from final

修正後

with 
    fct_event as (
        select
            id,
            event_name,
            event_timestamp,
        from {{ ref("fct_event") }}
    ),

    dim_category as (
        select
            id,
            category,
            sub_category,
        from {{ ref("dim_category") }}
    ),

    logic_a as (
        select
            fct_event.id,
            fct_event.event_name,
            dim_category.category
        from fct_event
        inner join dim_category using (id)
        order by fct_event.event_timestamp
    ),

    final as (
        ...
    )

select *
from final

why

  • より脳のメモリの削減に役立つ
    • logic_aだけ見ればどこから何の要素を取り出しているかがわかりやすくなります
  • 不要な項目を見つけやすい
    • dim_category CTEsおsub_categoryは最初必要かと思ってimportしましたがどうやら使っていないようです
    • この時「dim_category.sub_category」で検索して1件もヒットしなければこのカラムは不要だと判断しやすくなります
  • 「import CTEsとFunctional CTEsを分ける」「import CTEsは必要なものを明記する」とともに使うと効果的です

最後に

改めて大切にしているのは、「読む時間の長さへの着目」「デバッグ容易性」だなと感じます。
SQLをコードのようにちゃんと書いていきたいと思っています。
上記で挙げたものは併せて使うとより効果的です。

  1. 適切なCTEsをつけてあげる
    1. 4のFunctional CTEsには処理の内容や、その成果物を表す名前をつけてあげるとよりリーダブルになる
  2. サブクエリを使わない
    1. サブクエリを使わない方が読みやすくなるのは他のCTEsのルールを明確にしているから
  3. finalのCTEsを必ず使う
  4. import CTEsとFunctional CTEsを分けて書く
    1. 適切な名前をつけてあげることで、行が増えることよりも大きなリターンが生まれる
  5. (import) CTEsでは必要なものを明記する
  6. joinしたカラムにはjoin元のCTEsを明記する

こう見ると4が中心になるためimportとfunctionalを分けるのを好んでいるのかもしれません。
いろんな意見あると思いますが、ここをベースラインに議論が生まれることを願っています。

Discussion