アライアンス事業部 エンジニアグループ モダンデータスタック(MDS)チームのしんやです。
dbtはクラウド型データウェアハウス（DWH）におけるデータ変換に特化したツールです。非常に使い勝手が良く便利なツールである一方、様々な機能が提供されているのでいざ使ってみよう！となると『何をどうやって作り上げていけば良いんだろう？』『この場合のルールや制限はどういうものがあるの？どういう取り決めをもって扱えば良いんだろう？』という風に思うこともあるかと思います。(実際私自身そう感じました)
そんなユーザーの疑問や悩みを解決する、いわゆるdbtユーザー向けのガードレール的な存在となりうるコンテンツがdbt社から展開されています。それが『dbtベストプラクティスガイド(Best practice guides)』です。構造、スタイル、セットアップなど、dbt Labsの現在の視点を通した「ベストプラクティス」がまとめられています。
そこで当エントリでは、幾つか展開されている「dbtベストプラクティスガイド」で紹介されているコンテンツの中から『SQLの書き方』に関するものを紹介し、読み解いていきたいと思います。
目次
SQLに関するスタイルガイド・記法
基本的な部分
- ✅ SQLにおける書式、スタイル維持には『SQLFluff』の利用がオススメです。
- SQLFluff
- SQLFluffでは、
.sqlfluff設定ファイルをニーズに合わせてカスタマイズすることが出来ます。 - dbt Labs社で展開しているSQLFluff設定ファイルの例は下記をご参照ください。
- 標準の
.sqlfluffignoreファイルを使用してファイルやディレクトリを除外することができます。構文については.sqlfluffignore構文のドキュメントを参照してください。 - dbtにおけるSQLFluffの設定や実践については下記エントリ群をご参照ください。
- dbt CloudユーザーはSQLFluff Cloud IDE統合をを使用して、SQLを自動的にlintし、フォーマットすることができます。
- デフォルトのスタイルシートは、このガイドで説明されている dbt Labs のスタイルに基づいていますが、ニーズに合わせてカスタマイズ可能です。
- 利用に際しては外部ツールをセットアップする必要はなく、Lintを押すだけです。
- また、その他のフォーマッターとしてsqlfmtも利用可能です。
- ✅ コンパイルしたSQLに含めるべきではないコメントには、Jinjaコメント(
{# #})を使用してください。 - ✅ SQLでは末尾にカンマを付けましょう。
- 参考) Snowflakeでもつい先日、末尾カンマ(Trailing Comma)に対応するようになりました。
- ✅ インデントは4スペースとします。
- ✅ SQLの行は80文字以内とします。
- ✅ フィールド名、キーワード、関数名はすべて小文字とします。
- ✅ フィールドやテーブルをエイリアスする場合は、
asキーワード使って明示的に定義します。
フィールド、集約、グループ化
- ✅ フィールドは集約やウィンドウ関数の前に記述してください。
- ✅ 集計(Aggregations)は、パフォーマンスを向上させるために、他のテーブルに結合する前に、できるだけ早い段階で（できるだけ小さなデータセットで）実行すべきです。
- 列名を列挙するよりも、番号（例：group by 1, 2）で並べたりグループ化したりする方が好ましいです。
- 理由： Write better SQL: In defense of group by 1
- 注). 数列以上でグループ化する場合は、モデル設計を見直す価値があるかもしれないことに注意してください。
結合(Joins)
- ✅ 明示的に重複を削除するのでなければ、結合には
union allを優先します。 - ✅ 2つ以上のテーブルを結合する場合は、必ずカラム名の前にテーブル名を付けてください。1つのテーブルからのみ選択する場合は、接頭辞は必要ありません。
- ✅ 結合する際は、都度joinの型を明示すべきです。
- つまり、
joinではなくinner joinと書く...ということです。
- つまり、
- ❌ 結合条件にテーブルの別名（特に頭文字）を使用するのは避けましょう。
- "customers "と比較して、"c "というテーブルを使うと、それが何であるかを理解するのが難しくなります。
- ✅ 結合を推論しやすくするために、常に左から右に移動します。
right joinsは、多くの場合、どのテーブルから選択するか、どのテーブルに結合するかを変更する必要があることを示します。
共通テーブル式(CTE)のインポート
- ✅ すべての
{{ ref('...') }}文は、ファイル先頭のCTEに記述します。 - ✅ CTEのインポートには、参照するテーブルの名前を付ける必要があります。
- ✅ CTEでスキャンするデータをできるだけ制限する。可能であれば、実際に使用する列のみを選択し、where句を使用して不要なデータをフィルタリングします。
- 記述例：
with orders as ( select order_id, customer_id, order_total, order_date from {{ ref('orders') }} where order_date >= '2020-01-01' )
共通テーブル式(CTE)の実装
- ✅ パフォーマンスが許す限り、CTE は単一の論理的な作業単位を実行すべきです。
- ✅ CTEの名前は、その機能を伝えるために必要なだけ冗長である必要があります。
user_eventsの代わりにevents_joined_to_usersを使用しましょう。- これは適切なモデル名かもしれませんが、特定の関数や変換を説明するものではありません。
- ✅ モデル間で重複する CTEは、独自の中間モデルに抽出する必要があります。
- 独自のモデルにリファクタリングする必要がある、繰り返されるロジックの塊に注意してください。
- ✅ モデルの最後の行は、最終出力されるCTEからの
select *である必要があります。- これにより、モデルの開発中に、モデル内のさまざまなステップからの出力を具体化して監査することが簡単になります。
selectステートメントで参照されている CTEを変更するだけで、そのステップからの出力が表示されます。
モデルに関する設定
- ✅ モデル固有の属性（sort/distキーなど）は、モデルで指定されるべきです。
- ✅ 特定の設定がディレクトリ内のすべてのモデルに適用される場合は、
dbt_project.ymlファイルで指定する必要があります。 - ✅ モデル内の設定については、最大限の可読性を得るために以下のような指定とすべきです。
{{
config(
materialized = 'table',
sort = 'id',
dist = 'id'
)
}}SQLサンプル例
上述のルールを踏まえたSQLモデルの記述サンプルは以下のようなものとなります。
with
events as (
...
),
{# CTE comments go here #}
filtered_events as (
...
)
select * from filtered_eventswith
my_data as (
select
field_1,
field_2,
field_3,
cancellation_date,
expiration_date,
start_date
from {{ ref('my_data') }}
),
some_cte as (
select
id,
field_4,
field_5
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
),
joined as (
select
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 joinedまとめ
という訳で、dbtにおけるベストプラクティスの1つ『SQLの記法に関するベストプラクティス』の紹介でした。
合わせて投稿した『***』同様、この辺りのポイントはdbtでモデル開発をしていくうえでの『前提』として身に付けておくべきものと位置付けることが出来ます。『dbtの流儀』として早目に慣れておきたいところですね。
2
