こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。
今回は、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
