🌊

BigQuery の REGEXP_EXTRACT を使ってURLをバラしてみようぜ【Standard Query】

2021/09/09に公開

Google Cloud

BigQuery

tech

これを使ってみます。

状況設定

売る用の商品とレンタル用の商品を取り扱っているWebサイトがあり、アクセスされたURLを記録しています。URLのパスに Slug などの情報が入っているので、それをばらしてカラムにしたい、という状況です。

例：Products テーブル

id	URL
1	https://shop.example.com/waddy/products/power-chaege-buttery
2	https://shop.example.com/kitakyu-man/rentals/hiyoko-senbei

これを以下のようにバラします。

id	shop_name	product_slug	rental_slug
1	waddy	power-chaege-buttery	null
2	kitakyu-man	null	hiyoko-senbei

SQL

ポイント：

一時関数を使う
REGEXP_EXTRACT を使う
REGEXP_EXTRACT はキャプチャグループ()をひとつしか指定できない。OR条件|を使うときは非キャプチャグループ(?:)を使う

  -- 正規表現でURLを展開する一時関数を用意する
CREATE TEMPORARY FUNCTION
  EXTRACT_PRODUCT_SLUG(url STRING ) AS ( REGEXP_EXTRACT(url, r'https://.+?/.+?/products/(\w+).*$') );
CREATE TEMPORARY FUNCTION
  EXTRACT_RENTAL_SLUG(url STRING ) AS ( REGEXP_EXTRACT(url, r'https://.+?/.+?/rentals/(\w+).*$') );
CREATE TEMPORARY FUNCTION
  EXTRACT_SHOP_NAME(url STRING ) AS ( REGEXP_EXTRACT(url, r'https://.+?/(.+?)/(?:products|rentals)/\w+.*$') );

  -- データは WITH句 で用意してしまいます
WITH
  Products AS (
  SELECT
    1 AS id,
    "https://shop.example.com/waddy/products/power-chaege-buttery" AS url
  UNION ALL
  SELECT
    2,
    "https://shop.example.com/kitakyu-man/rentals/hiyoko-senbei" )
SELECT
  id,
  EXTRACT_SHOP_NAME(url) AS shop_name,
  EXTRACT_PRODUCT_SLUG(url) AS product_slug,
  EXTRACT_RENTAL_SLUG(url) AS rental_slug
FROM
  Products

意図どおりの展開ができました。データの一部を取り出して、別のカラムにバラしたいときなどに使えそうです。

状況設定

例：Products テーブル

SQL

Discussion