Extracting a domain or subdomain from a url in Bigquery

We come across long URL’s while working on Traffic and Web Analytics project.

The regex can get tricky while trying to get domain name out of long list of URL’s

Bigquery has native functionality which can help us bring out domain names of long URL’s

Extract subdomain from a url in Bigquery

SELECT NET.HOST('https://developers.google.com/search/blog/2008/09/dynamic-urls-vs-static-urls')

gives

Extract domain from a url in Bigquery

SELECT NET.REG_DOMAIN('https://developers.google.com/search/blog/2008/09/dynamic-urls-vs-static-urls')

gives

NET functions are useful from Google Bigquery

You can read the full documentation from Google here

Check out our work on https://warehows.io