Adobe Analytics

BigQuery: テーブルに格納されたURL文字列をKey-ValueのSTRUCT型で抽出する

こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。

今回は、BigQueryのテーブルに格納されているURL文字列をキー・バリュー形式のSTRUCT型にして抽出する方法について紹介します。

Web解析を行っていると、生のURLがデータウェアハウスに記録される事もあります。
URLそのままだと見づらい&扱いづらいので、これをキーと値に分けてみましょう。

REGEXP_EXTRACT_ALL関数と、STRUCT型を使います。

 

実際にクエリを試す

 
クエリはこうなります。

CREATE TEMPORARY FUNCTION
  decode(s string)
  RETURNS string
  LANGUAGE js AS """
var ret = '';
try {
  ret = decodeURIComponent(s);
}catch(e){}
return ret;
""";

SELECT
  raw_url,
  STRUCT(REGEXP_EXTRACT_ALL(log, r'(?:\?|&)(?:([^=]+)=(?:[^&]*))') AS keys,
    REGEXP_EXTRACT_ALL(decode(log), r'(?:\?|&)(?:(?:[^=]+)=([^&]*))') AS vals ) AS log
FROM
  adobe_beacon.hit_log
ORDER BY 1

 
結果はこうなります。

URLパラメータをkey-value形式に変換


 
REGEXP_EXTRACT_ALL関数を使って、正規表現でURLパラメータがキーと値に分かれてARRAY型で抽出してます。
そしてSTRUCT型のネストされたデータにしています。

生のURLよりも断然見やすくて扱いやすくなりました。

 
なお、ここで使ったURLログは以前Adobe AEP SDKが送るビーコンペイロードをリバースプロキシを使ってキャプチャする方法について紹介しましたが、その時取得したログをBigQueryにロードしたものです。
↓↓↓

Adobe AEP SDK: リバースプロキシを使ったアプリ計測検証方法

 
弊社では、Adobe Analytics認定エキスパート資格・Google Cloud認定プロフェショナルデータエンジニア資格・Linux Professional Institute認定資格を持ったエンジニア達が各種マーテックツールの導入実装コンサルティングサービスや、GCP/AWS/Azureなどのパブリッククラウドを使ったデータ分析基盤構築コンサルティングサービスを提供しております。

お問い合わせはこちらからどうぞ。

参考記事: Extract parameter from url in Google BigQuery using regex – Stack Overflow

 

ピックアップ記事

  1. 最速で理解したい人のためのIT用語集

関連記事

  1. Adobe Analytics

    Adobe AnalyticsとGoogle Analyticsの違い② サンプリングの有無

    こんにちは。CEOの原田です。AAとGAの違いの2段目にいきます。…

  2. Adobe Analytics

    Adobe AnalyticsからDWHレポートをGoogle Cloud Storageにアップロ…

    こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。…

  3. Google BigQuery

    OWOX BI: GoogleSheetsアドオンでBigQueryをお手軽ビジュアライズ

    こんにちは、エクスチュアの權泳東(コン・ヨンドン)です。今日は…

  4. Adobe Analytics

    Adobe Analytics: データフィードをGoogle BigQueryのテーブルにロードす…

    ※2019年9月4日追記この記事は情報が古いので、新しい記事を書き…

  5. Adobe Analytics

    PV(ページビュー)とは-Adobe Analyticsの指標説明

    今回はPV(ページビュー)について説明いたします。PV (ページビュ…

  6. Adobe Analytics

    カスタムイベントとは-Adobe Analyticsの指標説明

    今回はカスタムイベントの指標について説明します。カスタムイベントとは…

最近の記事

  1. LangChainって何?: 次世代AIアプリケーション構築…
  2. 回帰分析はかく語りき Part1 単回帰分析
  3. GitHub ActionsでGCEへのデプロイを楽にしてみ…
  4. Snowflake の Copilot が優秀すぎる件につい…
  5. Snowflake の新しいData Clean Roomの…
  1. ブログ

    ⑤おまけーDMPに関する用語まとめ
  2. 未分類

    BigQueryで高額課金が発生しているクエリの呼び出し元を特定する
  3. Adobe Analytics

    購入とは-Adobe Analyticsの指標説明
  4. Spread Sheets / GAS / GDS

    スプレッドシートで動的な縦持ちデータを作る〜GAS(GoogleAppsScri…
  5. IT用語集

    エンタープライズサーチ(Enterprise Search)って何?
PAGE TOP