[dbt] jinja sqlの変数とfor文を使ってSQLをリファクタリングしてみた
はじめに
私が所属しているライフイズテックのデータ基盤グループでは、データウェアハウスとしてSnowflakeを採用し、Data Tranformationのためにdbtを導入しています。
Google AnalyticsのRawdataをデータ基盤内で取り込んだあと、JSONをフラット化する処理をjinja sqlを用いてリファクタリングしたケースを紹介します。
リファクタリングする前の処理
Google AnalyticsのRawdataを取り込んだ際に、event_params
という要素内で、実際のkeyとvalueが1対になっておらず、また要素数や要素の順序が異なるため、当初SQLで愚直に記述する方法を取っていました
Google AnalyticsのRawdataの`event_params`の例
{
...
"event_params": [
{
"key": "batch_page_id",
"value": {
"int_value": 1700544569270
}
},
{
"key": "page_title",
"value": {
"string_value": "example title"
}
},
{
"key": "ga_session_id",
"value": {
"int_value": 1700544570
}
},
{
"key": "batch_ordering_id",
"value": {
"int_value": 1
}
},
{
"key": "page_location",
"value": {
"string_value": "https://example.com/"
}
},
{
"key": "ga_session_number",
"value": {
"int_value": 4
}
},
{
"key": "session_engaged",
"value": {
"string_value": "0"
}
}
],
...
}
select
case
when event_params[0].key = 'page_title' then event_params[0].value.string_value::varchar
when event_params[1].key = 'page_title' then event_params[1].value.string_value::varchar
when event_params[2].key = 'page_title' then event_params[2].value.string_value::varchar
when event_params[3].key = 'page_title' then event_params[3].value.string_value::varchar
when event_params[4].key = 'page_title' then event_params[4].value.string_value::varchar
when event_params[5].key = 'page_title' then event_params[5].value.string_value::varchar
end as page_title,
case
when event_params[0].key = 'page_location' then event_params[0].value.string_value::varchar
when event_params[1].key = 'page_location' then event_params[1].value.string_value::varchar
when event_params[2].key = 'page_location' then event_params[2].value.string_value::varchar
when event_params[3].key = 'page_location' then event_params[3].value.string_value::varchar
when event_params[4].key = 'page_location' then event_params[4].value.string_value::varchar
when event_params[5].key = 'page_location' then event_params[5].value.string_value::varchar
end as page_location
from
...
例えば上記のように、event_params
内のpage_title
やpage_location
をフラット化させたいと思ったときにはdbtのモデルでcaseを使って、上記のように要素の数に合わせて愚直に書いていました。
当然ですが、モデルは見通しが悪く、可読性に欠けていました。またRawdata内の要素数の増加やフラット化したいデータが変更になるごとにモデルの変更が難易度に対して工数がかかっていました。
リファクタリングの内容
case内でwhen event_params[0].key = 'page_title' then event_params[0].value.string_value::varchar
という条件が何度も出るためfor文でまとめます。
select
case
{% for n in range(6) %}
when event_params[{{ n }}].key = 'page_title' then event_params[{{ n }}].value.string_value::varchar
{% endfor %}
end as page_title,
case
{% for n in range(6) %}
when e.v:event_params[{{ n }}].key = 'page_location' then e.v:event_params[{{ n }}].value.string_value::varchar
{% endfor %}
end as page_location
from
...
続いて、range
の範囲をjinjaの変数として設定し、共通化します。
select
{% set event_params_length = 6 %}
case
{% for n in range(event_params_length) %}
when event_params[{{ n }}].key = 'page_title' then event_params[{{ n }}].value.string_value::varchar
{% endfor %}
end as page_title,
case
{% for n in range(event_params_length) %}
when e.v:event_params[{{ n }}].key = 'page_location' then e.v:event_params[{{ n }}].value.string_value::varchar
{% endfor %}
end as page_location
from
...
page_title
とpage_location
のJSONの構造が同じであるため、さらにfor文を使ってpage_title
とpage_location
をまとめます。
まとめてリファクタリングした最終的なSQLは下記のようになります。
select
{% set event_params_length = 6 %}
{% set event_params_keys = ['page_title', 'page_location'] %}
{% for i in event_params_keys %}
case
{% for n in range(event_params_length) %}
when event_params[{{ n }}].key = '{{ i }}' then event_params[{{ n }}].value.string_value::varchar
{% endfor %}
end as {{ i }},
{% endfor %}
from
...
宣伝
ライフイズテック サービス開発部では、気軽にご参加いただけるカジュアルなイベントを実施しております。
イベントの開催予定は connpass のグループ にてご案内しておりますので、興味をお持ちいただけたましたら、ぜひ、ご参加いただけると嬉しいです!
Discussion