dbtplyr を使ってみる

dbtplyr を使ってみる 2024/12/07 Japan.R 2024 (#JapanR) 小坪琢人 (@airspace_nobo) Here is the English version of the document (I also upload Engilsh version on X with #JapanR tag.)

• https://x.com/airspace_nobo
• https://www.docswell.com/s/airspace_nobo/K4V4ML-try_using_dbtplyr_japanr_english_ver/1
• https://x.com/hashtag/JapanR?src=hashtag_click

自己紹介 新卒でLINE入社、LINEヤフーでデータサイエンティスト 5年目 ❏ 最近はアナリティクスエンジニアリングチームで データモデリング の改善をやっている ❏ 本日は会場スタッフとして参加 趣味 ❏ お笑い鑑賞、もうすぐ M1グランプリ ❏ フットサル

• https://speakerdeck.com/kazaneya/20240116

思い出話 Tokyo.R での過去の発表歴 ❏ Use R from python @Tokyo.R #80 ❏ How to predict the wind direction of tomorrow @Tokyo.R #71 Tokyo.R #71 以来のHadley wickham との2度目の共演 ? ❏ (当時)のヤフーさんが会場提供をしていたので、ここのオフィスで 発表した 。そしてまたここのオフィスが会場に！

• https://speakerdeck.com/airspace_nobo/use-r-from-python
• https://nob0r.github.io/TokyoR/tokyor0715.html#/
• https://tokyor.connpass.com/event/92522/

本題へ ❏ 最近業務で使い始めた dbt というモダンなデータパイプライン ツールに dplyr の記法を組み込む packageを見つけたので紹介 する ※ dbtというツールを初めて見る人も多いと思うので、 ”こんなことが できるんだ ”くらいの感じで理解していただければ！ “dbt dplyr”で検索したら、このパッケージを見つけた。 Rでdbt使うみ たいなものかと思ったら逆だった

本日お伝えしたいこと ❏ dbtのSQLでdplyr っぽい書き方ができる ❏ Rで書いてた前処理などを dplyrの良さを残しつつ dbtと組み合わ せてSQLに置き換えられるかも ? ❏ やっぱり dplyrってすごい。無限の可能性がある

発表の流れ ❏ データモデリングについて紹介 ❏ dbt について紹介 ❏ dbt で何が解決できるか ? ❏ SQLを書く時のお悩み ❏ macro で解決できること ❏ dbtplyr で解決できること ❏ まとめ

データモデリングとは データモデリングとはデータを整理し、分析しやすくするための論理 的な構造を設計・構築するプロセス ❏ 分析やクエリの速度向上や手間を省く ❏ 中間処理の管理を一元化する あまり良くない状況例 ❏ raw データに近い情報を利用者全員が前処理 ->集計->可視化 などを個別に行っている

データモデリングとは 改善された状態 ❏ 利用者全員に共通する前処理が一元化されている ❏ クエリを叩く時に秘伝のタレ的な情報を追わなくて良い このテーブル を参照する raw data 前処理/変換 綺麗なテーブル

dbtとは SQLとPythonで使用されるテンプレート言語である Jinjaを組み合わ せたコード ❏ データモデリングを実装しやすく、ドキュメント・処理の可視化な どが充実している ❏ macroと呼ばれる機能があり、 SQLだけだとやりづらい処理を実 現できる

dbtとは ❏ 左記のqueryをstg_orgers.sql として作成して dbt を動かすと、設 定から適切な DB、tableを参照される ❏ queryから、「 raw_ordersを参照して stg_ordersを作成した」とい う処理の依存関係を可視化できる select id as order_id, user_id as customer_id, order_date, status from {{ source('main', 'raw_orders') }} select id as order_id, user_id as customer_id, order_date, status from "tutorial"."main"."raw_orders"

dbtとは 他のテーブルの前処理や joinを同じprojectで行うといい感じの関係 図を作れる ❏ 同様にraw_paymentsを stg_paymentsに変換する ❏ stg_ordersと stg_paymentsをjoinして orders というテーブルを作 成する

dbtで何が解決できるか ? dbtを使うと SQLをベースにデータモデリングを手軽に実装できる ❏ 一方でSQLだけだとやりづらい処理を実現するため、 dbtには macroと呼ばれる機能がある ❏ dbtplyrはSQLでdplyr っぽい記法を使えるようにするための package

SQLを書く時のお悩み
❏ 関数的なものがないので規則性に複数のカラムを処理する時にも
繰り返し書く必要がある
❏ 繰り返し書くことで、 typoなどが起きる可能性も高まる (例; numを
nuと書き間違える )
select
avg(num_a) as avg_num_a,
avg(num_b) as avg_num_b,
avg(num_c) as avg_nu_c,
from
data

data |>
summarise(across(starts_with('num'),
~mean(.x, na.rm=TRUE),
.names = "avg_{.col}"))

macroで解決できること ❏ payment_methodごとに横持ちで集計したい時、 SQLだと繰り返 して書く必要がある with order_payments as ( select order_id, sum(case when payment_method = 'credit_card' then amount else 0 end) as credit_card_amount, sum(case when payment_method = 'coupon' then amount else 0 end) as coupon_amount, sum(case when payment_method = 'bank_transfer' then amount else 0 end) as bank_transfer_amount, sum(case when payment_method = 'gift_card' then amount else 0 end) as gift_card_amount, sum(amount) as total_amount

macroで解決できること ❏ dbt のmacroを使うと for loopや処理を一般化できる {% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %} with order_payments as ( select order_id, {% for payment_method in payment_methods -%} sum(case when payment_method = '{{ payment_method }}' then amount else 0 end) as {{ payment_method }}_amount, {% endfor -%} sum(amount) as total_amount

macroで解決できること
❏ macroを関数とし、他の queryでも利用可能 (例: macro/util.sql)
{% macro get_payment_methods() %}
['credit_card', 'coupon', 'bank_transfer', 'gift_card']
{% endmacro %}
{% macro calculate_method_amounts(payment_methods, amount_column='amount',
payment_method_column='payment_method') %}
{% for payment_method in payment_methods -%}
sum(case when {{ payment_method_column }} = '{{ payment_method }}' then
{{ amount_column }} else 0 end) as {{ payment_method }}_amount,
{% endfor -%}
{% endmacro %}

dbtplyrで解決できること ❏ dbtplyrはdbtのmacroで dplyrっぽい記法を実現するために作ら れたパッケージです ❏ Xのプロフィールからも Rが好きなこと伝わる、 Emily Riederer が 開発したパッケージ “Three R's in my last name, but it's not enough #rstats for me!”

• https://x.com/emilyriederer

dbtplyrで解決できること ❏ acrossを使って繰り返しの集計をまとめる {% set cols = dbtplyr.get_column_names( ref('customers') ) %} {% set cols_num = dbtplyr.starts_with('num_', cols) %} select customer_id, {{ dbtplyr.across(cols_num, "sum({{var}}) as {{var}}_tot") }} from {{ ref('customers') }} group by 1 select customer_id, sum(num_total_orders) as num_total_orders_tot, sum(num_total_amount) as num_total_amount_tot from "tutorial"."main"."customers" group by 1

dbtplyrで解決できること ❏ acrossを使って繰り返しの変換をまとめる {% set cols = dbtplyr.get_column_names( ref('customers') ) %} {% set cols_date = dbtplyr.starts_with('date_', cols) %} select customer_id, {{ dbtplyr.across(cols_date, "cast({{var}} as timestamp) as {{var}}")}} from {{ ref('customers') }} select customer_id, cast(date_first_order as date) as date_first_order, cast(date_most_recent_order as date) as date_most_recent_order from "tutorial"."main"."customers"

dbtplyrで解決できること
❏ for loop と組み合わせて where条件にも使える
{% set cols_date =
dbtplyr.starts_with('date_', cols) %}
...
where
{% for col in cols_date -%}
{{col}} >= cast('2018-02-01' as date)
or
{% endfor %}
FALSE

...
where
date_first_order >=
cast('2018-02-01' as date) or
date_most_recent_order >=
cast('2018-02-01' as date) or
FALSE

dbtplyrではdplyrの下記の記法が使える starts_with(string, relation or list) ends_with(string, relation or list) contains(string, relation or list) not_contains(string, relation or list) one_of(string_list, relation or list) not_one_of(string_list, relation or list) matches(string, relation) everything(relation) where(fn, relation) if_any(var_list, script_string) if_all(var_list, script_string)

まとめ ❏ Data Engineering (dbt) × Data Science (dplyr) の活用について 紹介した ❏ SQLの中で使っている処理はシンプルかつ便利な機能なので、 dplyrがdbtの世界でも広まっていくかもしれない ❏ dbt Advent Calendar 2024 でもdbtの導入手順なども含めてもう 少し詳しく書く予定です

• https://qiita.com/advent-calendar/2024/dbt

参考文献 dbtplyr: Bringing Column-Name Contracts from R to dbt posit::conf(2023) What, exactly, is dbt? Apply a function (or functions) across multiple columns Rが生産性を高める 〜データ分析ワークフロー効率化の実践

• https://youtu.be/tbylZHG-ahE?feature=shared
• https://www.getdbt.com/blog/what-exactly-is-dbt
• https://dplyr.tidyverse.org/reference/across.html
• https://gihyo.jp/book/2022/978-4-297-12524-0

付録: ソースコード ❏ https://github.com/takuto0831/japanr_dbtplyr

• https://github.com/takuto0831/japanr_dbtplyr

付録: targets package ❏ Rが生産性を高める 〜データ分析ワークフロー効率化の実践 な どでも取り上げられていた、 targetsというデータパイプラインツー ルがある。 ❏ データパイプライン + ggplotでの可視化など出力の部分もパイプ ラインに組み込むことができる ❏ 今回紹介した機能を使うとデータパイプラインの部分は Rの外で 行うことになるので、比較して話したかったけど時間の都合で省 略

• https://gihyo.jp/book/2022/978-4-297-12524-0