dbtplyr を使ってみる 2024/12/07 Japan.R 2024 (#JapanR) 小坪琢人 (@airspace_nobo) Here is the English version of the document

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

思い出話 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度目の共演 ? ❏ (当時)のヤフーさんが会場提供をしていたので、ここのオフィスで 発表した 。そしてまたここのオフィスが会場に！

本題へ ❏ 最近業務で使い始めた dbt というモダンなデータパイプライン ツールにdplyr の記法を組み込む packageを見つけたので紹介 する ※ 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!”

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の導入手順なども含めてもう 少し詳しく書く予定です

参考文献 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://github.com/takuto0831/japanr_dbtplyr

付録: targets package ❏ Rが生産性を高める 〜データ分析ワークフロー効率化の実践 な どでも取り上げられいた、 targetsというデータパイプラインツー ルがある。 ❏ データパイプライン + ggplotでの可視化など出力の部分もパイプ ラインに組み込むことができる