dbt_utilsでURLパラメータを抽出する方法
こんにちは、 @kz_morita です。
以前の記事で、dbtモデルでURLからクエリパラメータを抽出する際に正規表現を使った処理が重くなり、materialized = tableで改善した話を書きました。今回は別のアプローチとして、dbt_utilsパッケージのget_url_parameterマクロを使ってURLパラメータを抽出する方法について紹介します。
dbt_utilsの導入 まず、dbt_utilsパッケージをプロジェクトに追加する必要があります。packages.ymlファイルをプロジェクトルートに作成し、以下の内容を記述します。
packages:- package:dbt-labs/dbt_utilsversion:1.1.1# 執筆時点での最新バージョン作成したら、以下のコマンドでパッケージをインストールします。
$ dbt deps これでdbt_utilsのマクロが使えるようになりました。
get_url_parameterマクロの基本的な使い方 get_url_parameterは、URLを含むカラムから特定のクエリパラメータを抽出するためのマクロです。使い方は非常にシンプルで、以下のような形式で呼び出します。
SELECTurl,{{dbt_utils.get_url_parameter(field='url',url_parameter='foo')}}ASfoo_value,{{dbt_utils.get_url_parameter(field='url',url_parameter='bar')}}ASbar_valueFROM{{ref('source_table')}}このクエリでは、urlカラムからfooとbarというパラメータの値を抽出し、新しいカラムとして取得します。
以前の正規表現処理との比較 以前の記事で紹介した正規表現を使った方法と比較してみましょう。以前のコードは以下のようなものでした:
withraw_dataas(selectid,urlfrom{{ref('source_table')}})selectid,url,-- パラメータ foo の値を抽出するための正規表現 regexp_extract(url,'.*[?&]foo=([^&]+)',1)asfoo_value,-- パラメータ bar の値を抽出するための正規表現 regexp_extract(url,'.*[?&]bar=([^&]+)',1)asbar_valuefromraw_dataこれをdbt_utilsを使って書き直すと、以下のようになります:
withraw_dataas(selectid,urlfrom{{ref('source_table')}})selectid,url,{{dbt_utils.get_url_parameter(field='url',url_parameter='foo')}}asfoo_value,{{dbt_utils.get_url_parameter(field='url',url_parameter='bar')}}asbar_valuefromraw_data複数パラメータを一度に抽出する実践例 実際のプロジェクトでは、多数のパラメータを抽出したい場合があります。例えば、マーケティングキャンペーンのUTMパラメータをすべて抽出する場合、以下のようなモデルが考えられます:
{{config(materialized='table')}}withraw_dataas(selectevent_id,timestamp,page_urlfrom{{ref('web_events')}})selectevent_id,timestamp,page_url,{{dbt_utils.get_url_parameter(field='page_url',url_parameter='utm_source')}}asutm_source,{{dbt_utils.get_url_parameter(field='page_url',url_parameter='utm_medium')}}asutm_medium,{{dbt_utils.get_url_parameter(field='page_url',url_parameter='utm_campaign')}}asutm_campaign,{{dbt_utils.get_url_parameter(field='page_url',url_parameter='utm_content')}}asutm_content,{{dbt_utils.get_url_parameter(field='page_url',url_parameter='utm_term')}}asutm_termfromraw_dataその他のURL関連マクロ dbt_utilsにはget_url_parameter以外にも、URLを処理するための便利なマクロがあります:
get_url_host: URLからホスト部分(ドメイン)を抽出 get_url_path: URLからパス部分を抽出 これらを組み合わせることで、URLの様々な部分を簡単に解析できます:
selectpage_url,{{dbt_utils.get_url_host(field='page_url')}}asdomain,{{dbt_utils.get_url_path(field='page_url')}}aspath,{{dbt_utils.get_url_parameter(field='page_url',url_parameter='search_term')}}assearch_termfrom{{ref('web_events')}}まとめ dbt_utils.get_url_parameterを使うことで、URLからのパラメータ抽出が簡単かつ保守性の高いコードで実現できます。パフォーマンスを重視する場合は、前回紹介したmaterialized = tableとの組み合わせが効果的です。
また、dbt_utilsにはURLパラメータの抽出以外にも、多数の便利なマクロが用意されています。SQL生成、テスト、Jinjaヘルパーなど様々な用途に使えるマクロが揃っているので、ぜひ公式ドキュメントやGitHubリポジトリを参照してみてください。
dbtを使ったデータ変換をより効率的に行うために、このようなユーティリティを活用していきます。