背景

BigQueryでURLを分解したい！そんな時、ありますよね。
仕事で使う都合上色々と調べていたのですが、自分が欲しい粒度でまとまっているものがなかったので自分で書いてみました。その結果を共有します！

まず、前提としてBigQueryにはURLを分解する関数が存在しないため、正規表現を用いて行う必要があります。
今回はBigQuery上でURLの下記部分の抽出方法を書いていきたいと思います。

FQDN(ホスト名+ドメイン名)
path
utmパラメータ各種
パラメータ部分全て

使用する関数

今回使用する関数は『 REGEXP_EXTRACT 』。

REGEXP_EXTRACT(value, regexp[, position[, occurrence]])

正規表現部分(regexp)と一致するvalue内の最初の文字列を返して、一致部分がないときはNULLを返す関数です。position部分に正の整数を入れると、最初の文字列以外を指定することもできます。
正規表現内にキャプチャグループ()がある場合、キャプチャ部分のみを抜き出します。便利！

▼REGEXP_EXTRACT関数の公式ドキュメントが読みたい方はこちらから。

今回用いる正規表現メタ文字

下記表の通り。

メタ文字	説明
\	メタ文字の直前につけることでメタ文字の効果を打ち消します。
.	改行文字以外の任意の1文字
*	直前の文字の0個以上の連続
+	直前の文字の1個以上の連続
?	直前の文字はあってもなくてもいいよ〜という意味。
^	行頭になります。
[^ ]	〜以外の任意の文字を表す。
()	キャプチャグループ。

正規表現の先頭についている『r』はraw stringといって、「メタ文字を特別扱いしないでね」という印である \ がそれ以外の意味を持つと認識されてしまうことを防ぐ役割を持っています。rがあってもなくてもアウトプットが変わらないケースもあります。

①FQDN(ホスト名+ドメイン名)を取得する

ホスト名+ドメイン名(FQDN)は下記で一発で取得できます。
FQDNとは、URLの　『https:// 〜〜〜 /』の〜〜部分のことを指します。

REGEXP_EXTRACT(url, r’https?://([^/]+)/.*‘)

(追記)
こちらの処理は、正規表現を使わずともBigQueryのデフォルトの関数の組み合わせを使って取得できそうでした。

REPLACE(FORMAT('%T', net.host(url)),'"','')

②パス(path)を取得する

パスは下記で一発で取得できます。パスとは、ドメインネームより後〜パラメータ部分より前の部分のことを指しています。
『https://〜/〜/〜/?abcdefg』の太字部分のことです。

REGEXP_EXTRACT(url, r’https?://[^/]+(/[^\?]*)\??.*‘)

③utmパラメータの中身を取得する

utmパラメータの中身は下記で一発で取得できます。

REGEXP_EXTRACT(url, r’^.*utm_source=([^&]*)&?.*‘)
REGEXP_EXTRACT(url, r’^.*utm_medium=([^&]*)&?.*‘)
REGEXP_EXTRACT(url, r’^.*utm_campaign=([^&]*)&?.*‘)

もちろん、「utm_medium=」の部分を任意のパラメータ名に変えるだけでutmパラメータ以外にも応用できます。

④パラメータ部分全部を取得する

パラメータ部分全部は下記で一発で取得できます。
URLの?より後の文字列全てを取得するという意味の正規表現ですね。

REGEXP_EXTRACT(url,r’^[^\?]+\?(.*)’)

UDFで作る一時関数と相性が良さそうという話

正規表現ってややこしいので、一つのクエリの中で何回も書きたくないですよね。コピペミスにも気づきにくいですし。
そんな時におすすめなのが、UDF(user-defined function)として自分で関数化してしまうことです。

BigQueryではクエリの頭で定義しておけば、同一クエリ内で何度も作った関数が呼び出し可能ですので、ぜひ使ってみてください。

以下に例文を貼っておきます。

一時関数を使用したクエリ例

CREATE TEMPORARY FUNCTION
  extract_url_host(url STRING ) AS ( REGEXP_EXTRACT(url, r’https?://([^/]+)/.*‘) ); -- FQDNを取得する一時関数
CREATE TEMPORARY FUNCTION
  extract_url_path(url STRING) AS (REGEXP_EXTRACT(url, r’https?://[^/]+(/[^\?]*)\??.*‘)); -- URLのパス部分を取得する一時関数
CREATE TEMPORARY FUNCTION
  extract_utm_source(url STRING) AS (REGEXP_EXTRACT(url, r’^.*utm_source=([^&]*)&?.*‘)); -- utm_sourceの中身を取得する一時関数
CREATE TEMPORARY FUNCTION
  extract_utm_medium(url STRING) AS (REGEXP_EXTRACT(url, r’^.*utm_medium=([^&]*)&?.*‘)); -- utm_mediumの中身を取得する一時関数
CREATE TEMPORARY FUNCTION
  extract_utm_campaign(url STRING) AS (REGEXP_EXTRACT(url, r’^.*utm_campaign=([^&]*)&?.*‘)); -- utm_campaignの中身を取得する一時関数
CREATE TEMPORARY FUNCTION
  extract_url_query(url STRING) AS (REGEXP_EXTRACT(url,r’^[^\?]+\?(.*)’)); -- URLのパラメータ部分を全て取得する一時関数

WITH
table01 AS (
SELECT
'https://qiita.com/tags/sql?utm_source=aaa&utm_medium=bbb&utm_campaign=ccc' AS url
)
SELECT
    extract_url_host(url) AS host,
    extract_url_path(url) AS path_name,
    extract_utm_source(url) AS utm_source,
    extract_utm_medium(url) AS utm_medium,
    extract_utm_campaign(url) AS utm_campaign,
    extract_url_query(url) AS parameter
FROM table01

アウトプット

host	path_name	utm_source	utm_medium	utm_campaign	parameter
qiita.com	/tags/sql	aaa	bbb	ccc	utm_source=aaa&utm_medium=bbb&utm_campaign=ccc

追記 - 汎用的な形にしてみた

上記のパラメータ内部の値を取得する関数を一つにまとめると、以下のようになります。

CREATE TEMPORARY FUNCTION 
    extract_parameter(url string, parameter_name string) AS (REGEXP_EXTRACT(url,r'^.*'||parameter_name||'=([^&]*)&?.*'));

この関数は、1つめの引数に 「パラメータを抜き出したい元のURL」 、2つめの引数に 「パラメータ名」 を入れると、中の値を返してくれます。

引数を2つ取る一時関数を使用したクエリ例

CREATE TEMPORARY FUNCTION 
    extract_parameter(url string, parameter_name string) AS (REGEXP_EXTRACT(url,r'^.*'||parameter_name||'=([^&]*)&?.*'));

WITH
table01 AS (
SELECT
'https://qiita.com/tags/sql?utm_source=aaa&utm_medium=bbb&utm_campaign=ccc' AS url
)
SELECT
    extract_parameter(url,'utm_source') as utm_source,
    extract_parameter(url,'utm_medium') as utm_medium,
    extract_parameter(url,'utm_campaign') as utm_campaign
FROM table01

アウトプット

utm_source	utm_medium	utm_campaign
aaa	bbb	ccc