♻️

[dbt] jinja sqlの変数とfor文を使ってSQLをリファクタリングしてみた

2023/11/27に公開

dwh

jinja

dbt

tech

はじめに

私が所属しているライフイズテックのデータ基盤グループでは、データウェアハウスとして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