Skip to main content
Version: 0.2.0

URLs

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

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                                                │
└───────────────────────────────────────────────────────────────┘
danger

GATEWAY-CLIENT not work as expected

SELECT cutToFirstSignificantSubdomain('www.tr')
┌─cutToFirstSignificantSubdomain('www.tr')─┐
│ tr                                       │
└──────────────────────────────────────────┘

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 ByConity can parse the input string as a URL.
  • Empty string. If ByConity 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 ByConity can parse the input string as a URL.
  • Empty string. If ByConity 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                                                  │
└──────────────────────────────────────────────────────────┘
danger

GATEWAY-CLIENT not work as expected

SELECT firstSignificantSubdomain('www.tr')
┌─firstSignificantSubdomain('www.tr')─┐
│ tr                                  │
└─────────────────────────────────────┘
```

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 ByConity can parse the input string as a URL.
  • Empty string. If ByConity 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                                                                │
└────────────────────────────────────────────────────────────────────┘