NET.HOST関数でURLからドメインを簡単に抽出するSQLクエリ

今回はNET.HOST関数を使ってURLからホスト（ドメイン）を簡単に抽出する方法について紹介する。

SQLクエリの概要

event_params から抽出した page_referrer に格納されているURLを、ホスト（ドメイン）に変換したい時に、 NET.HOST 関数を活用すると正規表現などを使用せず、簡単にクエリを書くことができる。

（参考）

NET.HOST

URL を STRING として受け入れ、ホストを STRING として返します。最適な結果を得るには、URL 値が RFC 3986 で定義されている形式に準拠する必要があります。URL 値が RFC 3986 の形式設定に準拠していない場合、この関数は入力をベストエフォートで解析し、関連する結果を返します。入力を解析できない場合は NULL を返します。

https://cloud.google.com/bigquery/docs/reference/standard-sql/net_functions?hl=ja#nethost

NET.HOST関数

実際にSQLクエリを見てみよう。

SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS referrer,
NET.HOST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer')) AS referrer_host
FROM
  `technogram-354804.analytics_291973511.events_intraday_202209*`
WHERE
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') IS NOT NULL

BigQueryでの集計結果

BigQueryでの集計結果は、以下のようになった。