BigQueryでコミュニティUDFを利用してURLのパースを行ってみた

#Google BigQuery

#SQL

k.uehara

2024.11.06

データアナリティクス事業本部のueharaです。

今回は、BigQueryでコミュニティUDFを利用してURLのパースを行ってみたいと思います。

コミュニティUDFについて

コミュニティUDFについては以下の記事で紹介をされています。

概要だけ説明すると、BigQueryの中で簡単に呼び出すことができるコミュニティが提供するUDFになります。

上記の記事に記載されている通り、以前までは当該UDFの利用が米国のリージョンに限定されていたため、例えば日本にあるデータセットに対して利用したい場合はユーザー自身でUDFのデプロイをする必要がありました。

しかし、2024年6月からこの制限が撤廃され、現在は全てのリージョンで使用できるようになっています。

使い方は簡単で、以下のように指定することで自身のリージョンでUDFを使用することができます。

bqutil.<dataset>_<region>.<function>()

例えば日本の場合は以下のような形で利用可能です。

bqutil.fn_asia_northeast1.url_keys()

今回はこのコミュニティUDFを、特に日本リージョンで利用してURLのパースを行いたいと思います。

やってみた

データの準備

テスト用に、以下のような url というカラムを1つだけ持つテーブルを作成しました。

CREATE TABLE `test_dataset.url_data` (
  url STRING
);

INSERT INTO `test_dataset.url_data` (url)
SELECT 'https://example.com/aaa/bbb/ccc' AS url
UNION ALL
SELECT 'https://hoge.co.jp/ddd/?test1=foo&test2=bar' AS url;

20241106_bq_url_01

URLのパース

URLのパースは url_parse を利用して以下のように実行できます。

SELECT
  url,
  bqutil.fn_asia_northeast1.url_parse(url, 'HOST') AS host,
  bqutil.fn_asia_northeast1.url_parse(url, 'PATH') AS path,
  bqutil.fn_asia_northeast1.url_parse(url, 'QUERY') AS query,
  bqutil.fn_asia_northeast1.url_parse(url, 'REF') AS ref,
  bqutil.fn_asia_northeast1.url_parse(url, 'PROTOCOL') AS protocol
FROM test_dataset.url_data

20241106_bq_url_02_2

url_parse(urlString STRING, partToExtract STRING) の partToExtract に HOST、PATH、QUERY、REF、PROTOCOL などを指定すると、URLの指定部分を抽出することができます。

例えばパスの階層を1階層ずつ表示したい場合は、パースしたものを利用して更に以下のように書くことができます。

WITH parsed_urls AS (
  SELECT
    url,
    bqutil.fn_asia_northeast1.url_parse(url, 'PATH') AS path
  FROM test_dataset.url_data
)
SELECT
  url,
  path_segment
FROM parsed_urls,
UNNEST(SPLIT(TRIM(path, '/'), '/')) AS path_segment WITH OFFSET AS segment_index
ORDER BY url, segment_index

20241106_bq_url_03

パラメータのキー・バリュー値の取得

パラメータのキーは url_keys を利用することで抽出できます。

SELECT
  url,
  bqutil.fn_asia_northeast1.url_keys(url) AS keys
FROM test_dataset.url_data

20241106_bq_url_04

指定したパラメータのキーに対するバリューは url_param を利用することで取得できます。

SELECT
  url,
  bqutil.fn_asia_northeast1.url_param(url, 'test1') AS value
FROM test_dataset.url_data

20241106_bq_url_05

これらを組み合わせることで、パラメータのキー・バリュー値を1つのレコードとして取得することもできます。

WITH url_params AS (
  SELECT
    url,
    param_key,
    bqutil.fn_asia_northeast1.url_param(url, param_key) AS param_value
  FROM test_dataset.url_data,
  UNNEST(bqutil.fn_asia_northeast1.url_keys(url)) AS param_key
)
SELECT
  u.url,
  p.param_key,
  p.param_value
FROM test_dataset.url_data u
LEFT JOIN url_params p ON u.url = p.url
ORDER BY u.url, p.param_key;