and,a株式会社 デジタルマーケティングエンジニア 佐藤洋太
BigQueryを触っていて配列の操作などの取り回しが入ってくると途端に記述量が増え、データの扱いが大変になって来ることが多々あります。今回はBigQueryに用意されているBigQuery Utilsを使ってSQLをもっと楽に開発してみましょう。
BigQuery Utilsとは？
BigQuery UtilsとはBig Query上で使用できる便利な関数を組み込んだライブラリのようなものです。
EXCELでいう数式をもっと簡単に扱えるようにしたコードの集合体の認識でOKです。
Githubリポジトリ
https://github.com/GoogleCloudPlatform/bigquery-utils
導入する手間は一切なく、
bqutil.fnとBigQueryのエディタに記述するだけですぐに任意の関数を呼び出すことができます。
SELECT
bqutil.fn.typeof("アイウエオ"), /* 結果は STRING */
bqutil.fn.typeof(0), /* 結果は INT64 */
bqutil.fn.typeof(DATE("2022-01-01")), /* 結果は DATE */上記は引数の型を読み取る例です。
特に難しいこともなくスムーズに理解頂けると思います。
※注意事項
BigQuery UtilsはBigQueryがサンドボックスモードだと、下記のようなエラーが出てしまい動作しないのでBillingを設定し、サンドボックスモードを解除してお使いください。
Not found: Dataset ga4-quick:analytics_302974976 was not found in location US早速使ってみる
GA4でevent_paramsの配列内を取得して操作する際のよくあるこの形。
SELECT
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
FROM `ga4-quick.analytics_302974976.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20220901' AND '20220901'これをBigQueryUtilsで書くと
SELECT
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
CONCAT(
user_pseudo_id,
CAST(bqutils.fn.get_value('ga_session_id', event_params).int_value as STRING)
) AS sid,
FROM `ga4-quick.analytics_302974976.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'サブクエリが減って少しスッキリしました。
event_params, user_propertiesの配列で頻出するクエリで使えそうです。
他にも使い所を考えてみましょう。
例えば、サイト上の全てのpage_locationから、クエリパラメーターをURL毎にkey/valueで出力するとこのようになります。
WITH locations AS (
/* GAからpage_locationsを取得する処理 */
)
SELECT url, key, bqutil.fn.url_param(t1.url, key) AS val
FROM (
SELECT url, bqutil.fn.url_keys(url) AS keys
FROM locations
) AS t1, UNNEST(t1.keys) AS keyサンプルのURLで試した結果：
| 行 | url | key | val |
|---|---|---|---|
| 1 | https://www.google.com/search?q=bigquery+udf&device=pc | q | bigquery+udf |
| 2 | https://www.google.com/search?q=bigquery+udf&device=pc | device | pc |
| 3 | https://www.google.com/search?q=analytics&device=sp | q | analytics |
| 4 | https://www.google.com/search?q=analytics&device=sp | device | sp |
| 5 | https://www.google.com/search?q=firebase&device=tablet&test= | q | firebase |
| 6 | https://www.google.com/search?q=firebase&device=tablet&test= | device | tablet |
| 7 | https://www.google.com/search?q=firebase&device=tablet&test= | test |
生のSQLだと正規表現などを使って文字列を判定する必要が出てくるのでコンパクトになるのはかなり嬉しいですね。
いかがでしたでしょうか。
今回紹介した以外にもまだまだBigQuery Utilsでは様々な関数が用意されています。使い所次第ではかなり有用なものとなりそうです
詳細はこちら
https://github.com/GoogleCloudPlatform/bigquery-utils/tree/master/udfs/community
コメント