Notice:
Some of the examples below are referenced from ClickHouse Documentation but have been adapted and modified to work in ByteHouse.

URLHierarchy

Returns an array containing the URL, truncated at the end by the symbols /,? in the path and query-string. Consecutive separator characters are counted as one. The cut is made in the position after all the consecutive separator characters.

Syntax

URLHierarchy(URL)

Arguments

  • URL — URL. Type: String.

Returned values

  • an array containing the URL

Example

SELECT URLHierarchy('https://example.com/browse/CONV-6788');
┌─URLHierarchy('https://example.com/browse/CONV-6788')────────────────────────────────────────────┐
│ ['https://example.com/', 'https://example.com/browse/', 'https://example.com/browse/CONV-6788'] │
└─────────────────────────────────────────────────────────────────────────────────────────────────┘

URLPathHierarchy

The same as above, but without the protocol and host in the result. The / element (root) is not included.

Syntax

URLPathHierarchy(URL)

Arguments

  • URL — URL. Type: String.

Returned values

  • an array containing the URL

Example

SELECT URLPathHierarchy('https://example.com/browse/CONV-6788');
┌─URLPathHierarchy('https://example.com/browse/CONV-6788')─┐
│ ['/browse/', '/browse/CONV-6788']                        │
└──────────────────────────────────────────────────────────┘

cutFragment

Removes the fragment identifier. The number sign is also removed.

Syntax

cutFragment(URL)

Arguments

  • URL – url string

Returned value

  • url without fragment

Example

SELECT cutFragment('http://example.com#fragment')
┌─cutFragment('http://example.com#fragment')─┐
│ http://example.com                         │
└────────────────────────────────────────────┘

cutQueryString

Removes query string. The question mark is also removed.

Syntax

cutQueryString(URL)

Arguments

  • URL – url string

Returned value

  • url without query

Example

SELECT cutQueryString('http://example.com/?page=1&lr=213')
┌─cutQueryString('http://example.com/?page=1&lr=213')─┐
│ http://example.com/                                 │
└─────────────────────────────────────────────────────┘

cutQueryStringAndFragment

Removes the query string and fragment identifier. The question mark and number sign are also removed.

Syntax

cutQueryStringAndFragment(URL)

Arguments

  • URL – url string

Returned value

  • url string without query string and fragment

Example

SELECT cutQueryStringAndFragment('http://example.com/?page=1&lr=213#fragment')
┌─cutQueryStringAndFragment('http://example.com/?page=1&lr=213#fragment')─┐
│ http://example.com/                                                     │
└─────────────────────────────────────────────────────────────────────────┘

cutToFirstSignificantSubdomain

Returns the part of the domain that includes top-level subdomains up to the “first significant subdomain”.

Syntax

cutToFirstSignificantSubdomain(URL)

Arguments

  • URL – url string

Returned value

  • subdomains string

Example

SELECT cutToFirstSignificantSubdomain('https://www.example.com.cn/')
┌─cutToFirstSignificantSubdomain('https://www.example.com.cn/')─┐
│ example.com.cn                                                │
└───────────────────────────────────────────────────────────────┘
## cutURLParameter

Removes the ‘name’ URL parameter, if present. This function works under the assumption that the parameter name is encoded in the URL exactly the same way as in the passed argument.

Syntax

cutURLParameter(URL, name)

Arguments

  • URL – url string
  • name - parameter name

Returned value

  • url string

Example

SELECT cutURLParameter('http://example.com/?page=1&lr=213','page')
┌─cutURLParameter('http://example.com/?page=1&lr=213', 'page')─┐
│ http://example.com/?lr=213                                   │
└──────────────────────────────────────────────────────────────┘

cutWWW

Removes no more than one ‘www.’ from the beginning of the URL’s domain, if present.

Syntax

cutWWW(URL)

Arguments

  • URL – url string
  • name - parameter name

Returned value

  • url string

Example

SELECT cutWWW('http://www.example.com/?page=1&lr=213')
┌─cutWWW('http://www.example.com/?page=1&lr=213')─┐
│ http://example.com/?page=1&lr=213               │
└─────────────────────────────────────────────────┘

decodeURLComponent

Returns the decoded URL.

Syntax

decodeURLComponent(URL) 

Arguments

  • URL – url string

Returned value

  • decoded url string

Example

SELECT decodeURLComponent('http://127.0.0.1:8123/?query=SELECT%201%3B') AS DecodedURL;
┌─DecodedURL─────────────────────────────┐
│ http://127.0.0.1:8123/?query=SELECT 1; │
└────────────────────────────────────────┘

domain

Extracts the hostname from a URL.

Syntax

domain(url)

Arguments

  • url — URL. Type: String.
    The URL can be specified with or without a scheme. Examples:
svn+ssh://some.svn-hosting.com:80/repo/trunk
some.svn-hosting.com:80/repo/trunk
https://yandex.com/time/

For these examples, the domain function returns the following results:

some.svn-hosting.com
some.svn-hosting.com
yandex.com

Returned values

  • Host name. If ByteHouse can parse the input string as a URL.
  • Empty string. If ByteHouse can’t parse the input string as a URL.
    Type: String .

Example

SELECT domain('svn+ssh://some.svn-hosting.com:80/repo/trunk');
┌─domain('svn+ssh://some.svn-hosting.com:80/repo/trunk')─┐
│ some.svn-hosting.com                                   │
└────────────────────────────────────────────────────────┘

domainWithoutWWW

Returns the domain and removes no more than one ‘www.’ from the beginning of it, if present.

Syntax

domainWithoutWWW(url)

Arguments

  • url — URL. Type: String.

Returned values

  • Host name. If ByteHouse can parse the input string as a URL.
  • Empty string. If ByteHouse can’t parse the input string as a URL.
    Type: String .

Example

SELECT domainWithoutWWW('http://www.example.com#fragment');
┌─domainWithoutWWW('http://www.example.com#fragment')─┐
│ example.com                                         │
└─────────────────────────────────────────────────────┘

extractURLParameter

Returns the value of the ‘name’ parameter in the URL, if present. Otherwise, an empty string. If there are many parameters with this name, it returns the first occurrence. This function works under the assumption that the parameter name is encoded in the URL exactly the same way as in the passed argument.

Syntax

extractURLParameter(URL, name)

Arguments

  • URL – url string
  • name - parameter name

Returned value

  • parameter value

Example

SELECT extractURLParameter('http://example.com/?page=1&lr=213','page')
┌─extractURLParameter('http://example.com/?page=1&lr=213', 'page')─┐
│ 1                                                                │
└──────────────────────────────────────────────────────────────────┘

extractURLParameterNames

Returns an array of name strings corresponding to the names of URL parameters. The values are not decoded in any way.

Syntax

extractURLParameterNames(URL)

Arguments

  • URL – url string

Returned value

  • a list of parameter names

Example

SELECT extractURLParameterNames('http://example.com/?page=1&lr=213')
┌─extractURLParameterNames('http://example.com/?page=1&lr=213')─┐
│ ['page', 'lr']                                                │
└───────────────────────────────────────────────────────────────┘

extractURLParameters

Returns an array of name=value strings corresponding to the URL parameters. The values are not decoded in any way.

Syntax

extractURLParameters(URL)

Arguments

  • URL – url string

Returned value

  • a list of parameters

Example

SELECT extractURLParameters('http://example.com/?page=1&lr=213')
┌─extractURLParameters('http://example.com/?page=1&lr=213')─┐
│ ['page=1', 'lr=213']                                      │
└───────────────────────────────────────────────────────────┘

firstSignificantSubdomain

Returns the “first significant subdomain”. This is a non-standard concept specific to Yandex.Metrica. The first significant subdomain is a second-level domain if it is ‘com’, ‘net’, ‘org’, or ‘co’. Otherwise, it is a third-level domain.

Syntax

firstSignificantSubdomain(URL)

Arguments

  • URL – url string

Returned value

  • first significant subdomain

Example

SELECT firstSignificantSubdomain('https://www.example.com.cn/')
┌─firstSignificantSubdomain('https://www.example.com.cn/')─┐
│ example                                                  │
└──────────────────────────────────────────────────────────┘
## fragment

Returns the fragment identifier. fragment does not include the initial hash symbol.

Syntax

fragment(URL)

Arguments

  • URL – url string

Returned value

  • fragment string

Example

SELECT fragment('http://example.com#fragment')
┌─fragment('http://example.com#fragment')─┐
│ fragment                                │
└─────────────────────────────────────────┘

path

Returns the path. Example: /top/news.html The path does not include the query string.

Syntax

path(URL)

Arguments

  • URL – url string

Returned value

  • path string

Example

SELECT path('http://example.com/top/news.html')
┌─path('http://example.com/top/news.html')─┐
│ /top/news.html                           │
└──────────────────────────────────────────┘

protocol

Extracts the protocol from a URL.
Examples of typical returned values: http, https, ftp, mailto, tel, magnet…

Syntax

protocol(URL)

Arguments

  • URL – url string

Returned value

  • protocol string

Example

SELECT protocol('http://example.com')
┌─protocol('http://example.com')─┐
│ http                           │
└────────────────────────────────┘

queryString

Returns the query string. Example: page=1&lr=213. query-string does not include the initial question mark, as well as # and everything after #.

Syntax

queryString(URL)

Arguments

  • URL – url string

Returned value

  • query string

Example

SELECT queryString('http://example.com/?page=1&lr=213')
┌─queryString('http://example.com/?page=1&lr=213')─┐
│ page=1&lr=213                                    │
└──────────────────────────────────────────────────┘

queryStringAndFragment

Returns the query string and fragment identifier. Example: page=1#29390.

Syntax

queryStringAndFragment(URL)

Arguments

  • URL – url string

Returned value

  • query and fragment string

Example

SELECT queryStringAndFragment('http://example.com/?page=1&lr=213#fragment')
┌─queryStringAndFragment('http://example.com/?page=1&lr=213#fragment')─┐
│ page=1&lr=213#fragment                                               │
└──────────────────────────────────────────────────────────────────────┘

topLevelDomain

Extracts the the top-level domain from a URL.

Syntax

topLevelDomain(url)

Arguments

  • url — URL. Type: String.
    The URL can be specified with or without a scheme. Examples:
svn+ssh://some.svn-hosting.com:80/repo/trunk
some.svn-hosting.com:80/repo/trunk
https://yandex.com/time/

Returned values

  • Domain name. If ByteHouse can parse the input string as a URL.
  • Empty string. If ByteHouse cannot parse the input string as a URL.
    Type: String .

Example

SELECT topLevelDomain('svn+ssh://www.some.svn-hosting.com:80/repo/trunk');
┌─topLevelDomain('svn+ssh://www.some.svn-hosting.com:80/repo/trunk')─┐
│ com                                                                │
└────────────────────────────────────────────────────────────────────┘

Did this page help you?