DOMAIN Function

Finds the value for the domain from a valid URL. Input values must be of URL or String type.

In this implementation, a domain value is all data between 1) the protocol identifier (if present) and the sub-domain and 2) the trailing, top-level domain information (e.g. .com).

NOTE: When the DOMAIN function parses a multi-tiered top-level domain such as co.uk, the output is the first part of the domain value (e.g. co). To return other parts of the domain information, you can use the HOST function. See HOST Function.

Basic Usage

URL literal examples:

derive type:single value:DOMAIN('http://www.example.com' ) as: 'myDomain'

Output: Generates a column containing the value example.

derive type:single value:DOMAIN('http://www.exampl.e.com' ) as: 'myDomain'

Output: Generates a column containing the value e.

Column reference example:

derive type:single value:DOMAIN(myURLs) as: 'myDomain'

Output: Generates the new myDomain column containing the domain values extracted from the myURLs column.

Syntax

derive type:single value:DOMAIN(column_url)

ArgumentRequired?Data TypeDescription
column_urlYstringName of column or String or URL literal containing the domain value to extract

For more information on syntax standards, see Language Documentation Syntax Notes.

column_url

Name of the column or URL or String literal whose values are used to extract the domain value.

  • Missing input values generate missing results.
  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?Data TypeExample Value
YesString literal or column reference (URL)http://www.example.com

Examples

Example - Filter out internal users

Here is some example web visitor information, including the name of the individual and the referring URL. You would like to filter out the internal users, whose referrer values include test-value.

NameReferrer
Joe Guyhttp://www.example.com
Ian Holmeshttp://www.test-value.com/support
Nick Knight http://www.test-value.com
Axel Adams http://www.example.com
Teri Towns http://www.example.com/test-value

Transform:

The referrrer values include test-value as a non-domain value and varying URLs from the test-value.com domain. So, you should use the DOMAIN function to parse only the domain versions of these values. The following evaluates the Referrer column values for the test-value domain and generates true/false answers in a new column accordingly:

derive type:single value:IF(DOMAIN(Referrer)=='test-value',true,false) as:'isInternal'

Now that these values are flagged, you can filter out the internal names:

delete row:(isInternal == 'true')

Results:

NameReferrerisInternal
Joe Guyhttp://www.example.comfalse
Axel Adamshttp://www.example.comfalse
Teri Townshttp://www.example.com/test-valuefalse

Example - Domain, Subdomain, Host, and Suffix functions

This examples illustrates how you can extract component parts of a URL using the following functions:

  • DOMAIN - extracts the domain value from a URL. See DOMAIN Function.
  • SUBDOMAIN - extracts the first group after the protocol identifier and before the domain value. See SUBDOMAIN Function.
  • HOST - returns the complete value of the host from an URL. See HOST Function.
  • SUFFIX - extracts the suffix of a URL. See SUFFIX Function.
  • URLPARAMS - extracts the query parameters and values from a URL. See URLPARAMS Function.
  • FILTEROBJECT - filters an Object value to show only the elements for a specified key. See FILTEROBJECT Function.

Source:

Your dataset includes the following values for URLs:

URL
www.example.com
example.com/support
http://www.example.com/products/
http://1.2.3.4
https://www.example.com/free-download
https://www.example.com/about-us/careers
www.app.example.com
www.some.app.example.com
some.app.example.com
some.example.com
example.com
http://www.example.com?q1=broken%20record
http://www.example.com?query=khakis&app=pants
http://www.example.com?q1=broken%20record&q2=broken%20tape&q3=broken%20wrist

Transform:

When the above data is imported into the application, the column is recognized as a URL. All values are registered as valid, even the IPv4 address.

To extract the domain and subdomain values:

derive type:single value: DOMAIN(URL) as: 'domain_URL'

derive type:single value: SUBDOMAIN(URL) as:'subdomain_URL'

derive type:single value: HOST(URL) as:host_URL'

derive type:single value: SUFFIX(URL) as:'suffix_URL'

You can use the Cloud Dataprep pattern in the following transform to extract protocol identifiers, if present, into a new column:

extract col:URL on:`{start}%*://`

To clean this up, you might want to rename the column to protocol_URL.

To extract the path values, you can use the following regular expression:

NOTE: Regular expressions are considered a developer-level method for pattern matching. Please use them with caution. See Text Matching.

extract col: URL on: /[^*:\/\/]\/.*$/

The above transform grabs a little too much of the URL. If you rename the column to path_URL, you can use the following regular expression to clean it up:

extract col:path_URL on:/[!^\/].*$/


Drop the path_URL column and rename the path_URL1 column to the dropped one. Then:

derive type:single value: URLPARAMS(URL) as: 'urlParams'

If you wanted to just see the values for the q1 parameter, you could add the following:

derive type:single value: FILTEROBJECT(urlParams,'q1') as: 'urlParam_q1'

Results:

Column set 1:

URLhost_URLpath_URLprotocol_URL
www.example.comwww.example.com
example.com/supportexample.com/support
http://www.example.com/products/www.example.com/products/http://
http://1.2.3.41.2.3.4 http://
https://www.example.com/free-downloadwww.example.com/free-downloadhttps://
https://www.example.com/about-us/careerswww.example.com/about-us/careershttps://
www.app.example.comwww.app.example.com
www.some.app.example.comwww.some.app.example.com
some.app.example.comsome.app.example.com
some.example.comsome.example.com
example.comexample.com
http://www.example.com?q1=broken%20recordwww.example.com http://
http://www.example.com?query=khakis&app=pantswww.example.com http://
http://www.example.com?q1=broken%20record&q2=broken%20tape&q3=broken%20wristwww.example.com http://

Column set 2:

subdomain_URLdomain_URLsuffix_URLurlParamsurlParam_q1
wwwexamplecom
examplecom
wwwexamplecom
wwwexamplecom
wwwexamplecom
www.appexamplecom
www.some.appexamplecom
some.appexamplecom
someexamplecom
examplecom
wwwexamplecom{"q1":"broken record"}{"q1":"broken record"}
wwwexamplecom{"query":"khakis","app":"pants"}
wwwexamplecom{"q1":"broken record", "q2":"broken tape", "q3":"broken wrist"}{"q1":"broken record"}

Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation
Need help? Visit our support page.