Parsing URLs with BigQuery's REGEXP_EXTRACT (Standard SQL)

https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_extract

I will try using this.

Scenario

Suppose you have a website that handles both products for sale and items for rent, and you are logging the accessed URLs. Information such as the slug is included in the URL path, and the goal is to break that down into separate columns.

Example: Products Table

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

We will break this down as follows:

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

SQL

Key points:

• Use temporary functions
• Use REGEXP_EXTRACT
• REGEXP_EXTRACT can only specify one capture group (). When using OR conditions |, use non-capturing groups (?:)

  -- Prepare a temporary function to expand the URL using regular expressions
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+.*$') );

  -- Prepare the data using a WITH clause
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

I was able to expand it as intended. This seems useful when you want to extract part of the data and split it into different columns.