Zenn
📝

CTEs(Common Table Expressions)について学び直す

2022/12/17に公開
SQL
BigQuery
dbt
ctes
idea

本記事はBigQuery Advent Calendar 2022に寄稿した記事となります。
17日目はBigQueryに直接関する内容ではなく、BigQueryで取り扱うSQLの構成について語らせていただきます。

☕️Ice Break

Who are you? / お前は何者だ？

初めましての方は初めまして。ご存知の方はお久しぶりです。
@kazuya_araki_jpと申します。事業会社でデータ関連の職に就いている者です。
詳細はnoteの方が詳しいのでそちらをご覧ください。

Recent state / 最近何している？

  • 本業がﾄｯﾃﾓﾄｯﾃﾓｲｿｶﾞｼｶｯﾀYO!
  • 法人準備中。
  • Splatoon 3

Introduction / 導入

ここ最近に限らずですが、BigQueryを扱う上でSQLの書き方に悩み続けています。
というのも、大昔(少なくとも私がSQLおよびデータベースを知ることになった20数年前)からSQLの記法に明確な正解がないのが最大のお悩みポイントです。
また、SQLフォーマットにまつわる話題を出したが最後、SQLフォーマット宗教戦争が勃発し、人類は破滅します。

以上の背景の中、今年からdbtに触れることになり、よりSQLについて向き合うシーンが増えることになりました。
SQLのレビュワーとしての責務として、SQLの細かい記法の差異を気にするよりは、SQL全体の構成について意識し、その結果翻って学び直す必要性を感じることになります。

こうしてSQLについて日々試行錯誤した結果、CTEsに辿り着いたので、私の理解度と学習を兼ねて紹介させていただきます。
特に、dbtのドキュメントがとても理解しやすい内容となっていますので、簡単な意訳を兼ねて解説できればと思います。
BigQueryのアドベントカレンダーなのに、なぜdbt(ry

What is a CTEs? / CTEsとはなんぞや？

Meaning of word / 言葉の意味

by DeepL兄貴
CTEs = Common Table Expressions = 一般的なテーブル表現

という結果が出てきて驚いたので参考までに紹介します。
データベース界隈での翻訳は 共通テーブル式 の方が一般的でした。

Meaning in the database or data warehouse / データベース、データウェアハウスとしての意味

CTEsは古から存在するデータベースにて既に存在が確認されている概念です。
端的に言うと、 WITH句を用いてクエリを分割する 行為、と筆者は理解しています。

主要なデータベース、データウェアハウスのCTEs対応状況は以下にリストアップしておきます。

Why you should care about CTEs? / なぜ、CTEsに注目するのか？

例えば、以下のケースで困ったことはありませんか？

  • 昨日サクッと書いたSQL、ロジックを忘れてしまって説明できない。
  • ●●さん(注釈: 社内のスーパークエラー)の書いたSQL、すごいことをしているけど、複雑すぎて意味わからん。
  • ▲▲さん(注釈: 最近SQLを覚え始めたnewbieクエラー)の書いたSQL、どこかのクエリをコピペした集合体となってサグラダファミリアになっている。
  • なぜ、このクエリの書き方なんだろう？もう少しわかりやすくしてくれないかな...
  • SQLの結果が思ったのと違う。どこに問題があるのだろう...
  • このクエリをリファクタリングしたいが、どこから手をつけていいかわからない。

これらのissueは個人でSQLを書く分には絶対に見えてこないissueです。
さらに言うと、当人以外の関係者が存在した場合に発生するケースとなっています。
厄介なことに、これらを完璧に解消する銀の弾丸が存在しないのが悩ましいところです。
というのも、SQLが登場以降、SQLには明確にコーディングルールが存在するわけではなく、SQLの書き方は諸説ありな現状が続いているのが影響しています。

話は変わりますが、最近データエンジニアリングの領域でCTEsの考え方が再燃してきています。
(再掲ですが、CTEsの概念や考え方自体は各データベースにて古くから存在していました)
そのため、ad hocや個人利用で閉じていた論争から、エンジニアリングの観点が追加され、SQLの構造化や単純化の推奨をするコミュニティやベンダーを見かけるようになりました。

以上の歴史的背景やissueの解決の糸口となる情報はdbtのCTE in SQLにも書かれています。

How do CTEs work? / CTEsはどのように機能するのか？

結論だけ乱暴に書くと、

  • WHTH句を使え！ネストした構造でクエリを書くな！
  • クソデカクエリにするな！適宜分割して単純化しろ！

これだけを意識するだけで劇的にクエリがシンプルに、かつ理解しやすい構造になります。
サンプルとして、What's a CTE?というドキュメントがあるので、私見を交えて紹介します。

overall / 全体像

example query(What's a CTE?より引用)
with

import_orders as (

    -- query only non-test orders
    select * from {{ source('jaffle_shop', 'orders') }}
    where amount > 0
),

import_customers as (
    select * from {{ source('jaffle_shop', 'customers') }}
),

logical_cte_1 as (

    -- perform some math on import_orders

),

logical_cte_2 as (

    -- perform some math on import_customers
),

final_cte as (

    -- join together logical_cte_1 and logical_cte_2
)

select * from final_cte

1. Import CTE

example query の import_orders, import_customers サブクエリを指します。
このサブクエリでは単にデータ元から必要なデータを取得するだけの処理を行います。
ただし、必要であればフィルターを持つことが可能です。

※以下、私見です。
  • select * from table where ... 程度のシンプルなサブクエリにする意味を考える。
    • CASE文の使用やGROUP BYなどを使った集計処理など、元データを加工する処理は後工程で対応する。
    • 必要なフィールドを絞った方が良いと感じたが、データ取得元のデータ構造を崩さないように心がける意図を感じた。
  • フィルターは同意。Import CTEの時点で必要なレコード数を定義するべきと思った。
    • 特にBigQueryはクエリコストがネックなので、必要なデータを必要な分だけを絶対遵守する。
    • 日時フィールドを含むテーブルの場合、データ取得期間でレコード数を絞るのが比較的効果的。
    • 日時フィールドを含まないテーブルの場合、パーティショニングやクラスタ化したフィールドを条件にすると良さそう。

2. Logical CTE

example query の logical_cte_1, logical_cte_2 サブクエリを指します。
Import CTEで取得したデータを加工する工程をこちらのサブクエリで担います。

※以下、私見です。
  • SQLのロジックはここに集約する。
    • 計算処理
    • 集計関数
    • WINDOW関数
    • CASE文
    • 欠損値を意味のある値に変換する
    • IDを意味のある文字列に置換する
      • ディメンションテーブル[1]があればJOINして名前解決する。
      • ディメンションテーブルがなければCASE文やユーザー定義関数などを駆使して名前解決する。
    • など
  • 日時データのタイムゾーンの変更はこのタイミングで行った方がいいか悩ましい。
    • データアナリスト目線、データ分析用途として、Import CTEで取得する時点でタイムゾーンの配慮がされたテーブルを使うのが理想。
    • データエンジニア目線、上記の理想のテーブルの提供を主とするのであれば、Logical CTEで対処するのは違和感がない。
    • 以上により、データエンジニアとデータアナリストの役割に応じて使い分けるのが良さそう。

3. Final CTE

example query の final_cte サブクエリを指します。
クエリの最終成果物を構成するサブクエリを構成します。
他の人の理解を助けるために、フィールド名に固有の名前を付けることを推奨しています。

※以下、私見です。
  • Logical CTEの時点でデータ加工は完了しているので、Final CTEではJOINやUNION ALLだけの構成になる(はず)。
    • よほど複雑なことをしなければ、データ加工済みのサブクエリを結合するだけで最終成果物になる。
    • むしろ、Final CTEの時点で複雑なクエリ構造となっている場合は単純化の余地がある、Logical CTEへ立ち戻ってリファクタリングすることが望ましい。
  • 個人的には、フィールド名のリネームはLogical CTEで行った方がいいと思った。
    • ロジックが確定した段階でフィールド名のリネームを行わないと、ロジックの理解ができないので。
    • Import CTEで取得したテーブルをLogical CTEを経ずにFinal CTEで使用する場合はありそうなので、その場合はFinal CTEで対処するしかない。

4. Simple Query

Final CTEの後に、 select * from final(または最終的なCTE名によっては同様のもの) 程度の単純なSQLで最終成果物とします。
この単純な構造により、トラブルシューティングの際にネストしたクエリを解読する必要がなく、他の人が簡単にCTEの内容を把握することが可能となります。

※以下、私見です。
  • 正直、 select * from final(または最終的なCTE名によっては同様のもの) じゃなくてもいいのでは？
    • 冗長な感覚を覚えた。具体的にどこが？と言われると困る感覚で。
    • これまでのCTEsの効能と比べると、大きなメリットは感じられなかった。
  • 一方で、この構造を遵守するとdbt初学者にもdbt管理下のSQLであることが伝えられそう。
    • Jinja tamplateなどを使用しているのでdbt本体のSQLは一発でバレますが
    • データエンジニアとしては、SQLの構成を理解するのが早くなる(気がする)ので、メリットはある。

Main Subject / 閑話休題

ここまで読んでいただいた読者のみなさまはお気づきの通り、本記事はdbt CTEsのドキュメントについて深く踏み込んだ内容ではありません。
dbt CTEsの詳細を知りたい方は、先人の方々がdbtのドキュメントの解説や翻訳をしているので参考にしていただいた方が良いかと思います。筆者もとても助けられました。

では、本記事では何を述べたいのか？
ここまでは一般的なCTEsのお作法や利用方法について述べましたが、ここからは利用者目線でCTEsについて考察してみます。

Who should use CTEs? / 誰がCTEsを使用すべきなのか？

データエンジニア: データパイプラインやデータ前処理を担う人々

dbtのドキュメントから読み取れた情報としては、データエンジニアがCTEsを取り組むことで最も恩恵を受ける印象を受けました。
(dbtのドキュメントなので、当たり前と言えば当たり前ですが)
データパイプラインを管理したり、データ前処理を行い、データアナリストやデータサイエンティストにテーブルを提供する業務を担う際はすべからくGitHub等でコード管理をすることになるため、混沌とするSQLフォーマットに対するデファクトスタンダートとして機能することでしょう。

データアナリスト: 2人以上の組織、かつ成果物を共有する機会がある人々

データアナリストがCTEsの恩恵を受ける条件として、

  • 組織であるか？(会社で1人データアナリストとして個人商店[2]状態ではないか？)
  • SQLを共有すべき成果物として捉えているか？

がポイントになります。
あなたが会社で唯一のデータアナリストであるならば、新しい記法を学ぶよりも、慣れ親しんだSQLの記法の方がアウトプットは早いはずです。そのため、誰にも見せないし共有もしないSQLに対して払うコストとしてCTEsを遵守することは見合いません。

一方、あなたがデータアナリスト組織所属で、かつSQLを再利用するような組織の場合はどうでしょう？
Why you should care about CTEs? / なぜ、CTEsに注目するのか？ のissueはまさにこうした環境に身を置くデータアナリストの悩みそのものになります。ですので、CTEsはデータアナリスト組織に対して非常に効果的に機能する概念ではないでしょうか。
(重ねて言いますが、CTEsに限らずSQLのスタイルガイドには正解がないので、issueが爆発する前に一度議論してみることをおすすめします)

データサイエンティスト: モデル作成時にSQLを使用する人々

データエンジニアの項と同じく、データサイエンティストもコード管理を行う場合はCTEsの考え方は通用するかと思います。
特に、構築したモデルのロジックを解読するためのコストを減らす効果があるかと思います。

※筆者の実体験(失敗事例)

大昔、とある案件で簡単な予測モデルを構築する業務を担ったことがあります。
要件が二転三転した結果、パラメーターに必要なデータを取得する際のSQLを雑に書いてしまい、2ヶ月後にモデル評価した際にSQLのロジックを理解するのに時間がかかりました。

あのときCTEsについて意識していれば、過去自分が手がけたSQLの解読時間の短縮につながったのかもしれません。

そのほかの職種の人々: だいたい知っておくだけ。それでまあまあそれなりにOK

バックエンドエンジニアなどのエンジニアや、デジタルマーケターといった非エンジニアの人々もSQLに触れる機会があるかと思います。
私見ですが、雑学としておさえておくだけで基本的には問題ない範囲かと思います。
CTEsを導入すべきかの観点は、データアナリストの項に近いですが、

  • SQLを書くことが組織の業務であるか？
  • SQLを共有すべき成果物として捉えているか？

以上をどう捉えるか吟味すると良い解が得られるかと思います。

When using CTEs? / いつ、CTEsを使用するのか？

以下の3つのポイントがCTEsに対する恩恵だと考えています。
これらのポイントが有効だと判断した際、CTEsを適用するタイミングとなるはずです。

Simplification / 単純化

SQLの経験があるほど、複雑なSQLに対しての耐性と理解が高まります。
しかし、玄人であればあるほど、SQL初学者が理解し難い構造になりがちなので、適度な単純化を施す必要があります。

CTEsの中でも、特にLogical CTEが単純化を促進する考え方であるので、複雑化したSQL、肥大化したSQLに対してのリファクタリングのアプローチとして有効です。

Easy Maintenance / 簡単なメンテナンス

CTEsの構造を理解している組織やチームは、SQLに異常が発生した場合の原因調査が簡単になります。
また、SQLの改修やリファクタリングも容易になるため、メンテナンス業務全体の効率化が図れます。

※実際現場で起きていること

2022年12月現在、筆者はdbtとBigQueryを利用した業務を1年経験しました。
メンテナンスタスクとしてSQLのリファクタリングや環境移行のためにSQLの改修を手がけていますが、CTEsの思想は大いに影響しています。
特に、リファクタリング面で恩恵を受けている印象です。
(実際のSQLで解説できれば良いのですが、Confidentialな業務のため、コメントのみの紹介とさせてください)

High Availability / 高可用性

CTEsは紐解くとサブクエリの集合体ですので、

  • 異なるSQLのサブクエリ同士を共有クエリとして切り出すことができる。
    • よく使うサブクエリが共通化され、関係者に共有できる。
    • サブクエリが共通化され、さらにSQLの単純化ができる。
  • テストやデバッグが容易になる。
    • 途中成果物が明確なので、全てのサブクエリを実行する必要がなくなる。
    • 障害発生時の原因特定が楽になる。
  • SSOT[3]に向かうことができる。
    • 冗長なSQLの拡散を防げる。
    • サブクエリ自体は単純構造なので、SQL初学者にとってやさしい。

以上のことが言えます。
そのため、これらの状態に向かえば向かうほど、安定したSQL業務を維持することができるため、高可用性であると判断できます。

Conclusion / まとめ

  • (BigQueryに限らず)SQLを書く際はCTEsの思想は役に立つ。
  • CTEs自体は銀の弾丸ではないので、適用する組織、チーム、人々と議論を重ねることが超大事！
  • dbtはいいぞ

今回はCTEsについて改めて学び直した際にメモした内容を中心に記事化しました。
BigQueryについて全く触れませんでしたが、本記事が何かの役に立てれば幸いです。

脚注

  1. マスタテーブルと言った方が通じるかもしれませんが配慮しました。 ↩︎

  2. 1人データアナリストのことを指す表現としてよく使っています。一般的な用語ではありません。 ↩︎

  3. Single Source Of True: すべてのデータが1か所でのみ作成、あるいは編集される構造や状態のこと。 ↩︎

GitHubで編集を提案

Discussion