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 &quot;&quot;&quot;
var ret = '';
try {
  ret = decodeURIComponent(s);
}catch(e){}
return ret;
&quot;&quot;&quot;;

SELECT
  raw_url,
  STRUCT(REGEXP_EXTRACT_ALL(log, r'(?:\?|&amp;)(?:([^=]+)=(?:[^&amp;]*))') AS keys,
    REGEXP_EXTRACT_ALL(decode(log), r'(?:\?|&amp;)(?:(?:[^=]+)=([^&amp;]*))') 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