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. Adobe Analytics

    Adobe Analytics:セグメントの落とし穴:滞在時間がおかしくなる

    Adobe Analyticsの持つ機能の中でも特に強力で便利な機能の…

  2. Google BigQuery

    Tableau×BigQueryをコスパ良く使う方法

    こんにちは、エクスチュア渡部です。TableauでBigQue…

  3. Google BigQuery

    GCP: 今月のGCP課金額をslackに自動的に書き込む

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

  4. Adobe Analytics

    Adobe Analytics: カスタムリンク計測を1行で書く

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

  5. Adobe Analytics

    Adobe Analyticsに入り切らないデータをBigQueryに投入する

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

  6. Adobe Analytics

    Adobe Analytics: BigQuery+Lookerでアトリビューション分析

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

最近の記事

  1. KARTE:最低限!KARTEの運用管理のためにやっておくこ…
  2. YOTTAA:新機能「Anomaly AI」のご紹介
  3. Tableau 2021.1 新機能紹介
  4. YOTTAA:ECサイトで見るべき8つのサイトパフォーマンス…
  5. 【初心者向け】AWSを学ぶ前に確認したい用語
  1. Google Cloud Platform

    Google Compute Engine のLinuxVMにVNC接続する
  2. Adobe Analytics

    AdobeAnalytics: スクロールで目標に到達したらカスタムリンク
  3. Google Cloud Platform

    Node.js+GAE: 日本語自然文を形態素解析してネガポジ判定をする
  4. Databricks

    Databricks: Spark DataFrameでユーザー定義関数を使う
  5. Adobe Experience Cloud

    Adobe Marketing Cloud:サインインで問題が起きたときの対処法…
PAGE TOP