IP & URL 函数

请注意:
下文中的一些示例引用自 ClickHouse 社区文档并经过一定修改确保可以在 ByteHouse 中正常使用。

IPv4NumToString

Takes a UInt32 number. Interprets it as an IPv4 address in big endian. Returns a string containing the corresponding IPv4 address in the format A.B.C.d (dot-separated numbers in decimal form).

Syntax

IPv4NumToString(num)

Arguments

num – a UInt32 number.

Returned value

A string in ipv4 representation.

Examples

SELECT toIPv4('116.106.34.242') as ipv4, toTypeName(ipv4), IPv4NumToString(ipv4) as ipv4_string, toTypeName(ipv4_string)

┌─ipv4───────────┬─toTypeName(toIPv4('116.106.34.242'))─┬─ipv4_string────┬─toTypeName(IPv4NumToString(toIPv4('116.106.34.242')))─┐
│ 242.34.106.116 │ IPv4                                 │ 116.106.34.242 │ String                                                │
└────────────────┴──────────────────────────────────────┴────────────────┴───────────────────────────────────────────────────────┘

IPv4NumToStringClassC

Similar to IPv4NumToString, but using xxx instead of the last octet.

Syntax

IPv4NumToStringClassC(num)

Arguments

num – a UInt32 number.

Returned value

An string in ipv4 representation, but using xxx instead of the last octet.

Examples

SELECT toIPv4('116.106.34.242') as ipv4, toTypeName(ipv4), IPv4NumToStringClassC(ipv4) as ipv4_string, toTypeName(ipv4_string)

┌─ipv4───────────┬─toTypeName(toIPv4('116.106.34.242'))─┬─ipv4_string────┬─toTypeName(IPv4NumToStringClassC(toIPv4('116.106.34.242')))─┐
│ 242.34.106.116 │ IPv4                                 │ 116.106.34.xxx │ String                                                      │
└────────────────┴──────────────────────────────────────┴────────────────┴─────────────────────────────────────────────────────────────┘

IPv4StringToNum

The reverse function of IPv4NumToString. If the IPv4 address has an invalid format, it returns 0.

Syntax

IPv4StringToNum(s)

Arguments

s – ipv4 in string representation.

Returned value

UInt32.

Examples

SELECT IPv4StringToNum('116.106.34.242') as ipv4, toTypeName(ipv4), IPv4NumToString(ipv4) as ipv4_string, toTypeName(ipv4_string)

┌─ipv4───────┬─toTypeName(IPv4StringToNum('116.106.34.242'))─┬─ipv4_string────┬─toTypeName(IPv4NumToString(IPv4StringToNum('116.106.34.242')))─┐
│ 1953112818 │ UInt32                                        │ 116.106.34.242 │ String                                                         │
└────────────┴───────────────────────────────────────────────┴────────────────┴────────────────────────────────────────────────────────────────┘

IPv4ToIPv6

Takes a UInt32 number. Interprets it as an IPv4 address in big endian . Returns a FixedString(16) value containing the IPv6 address in binary format.

Syntax

IPv4ToIPv6(x)

Arguments

x – a UInt32 number

Returned value

IPv6 address in binary format.FixedString(16)

Examples

SELECT IPv4StringToNum('192.168.0.1') as ipv4, IPv6NumToString(IPv4ToIPv6(ipv4)) as ipv6_string

┌─ipv4───────┬─ipv6_string────────┐
│ 3232235521 │ ::ffff:192.168.0.1 │
└────────────┴────────────────────┘

IPv6NumToString

Accepts a FixedString(16) value containing the IPv6 address in binary format. Returns a string containing this address in text format.

IPv6-mapped IPv4 addresses are output in the format ::ffff:111.222.33.44.

Syntax

IPv6NumToString(x)

Arguments

x – FixedString(16) value containing the IPv6 address in binary format

Returned value

A string in ipv6 representation.

Examples

SELECT IPv6NumToString(toFixedString(unhex('2A0206B8000000000000000000000011'), 16)) AS addr;

┌─addr─────────┐
│ 2a02:6b8::11 │
└──────────────┘

IPv6StringToNum

The reverse function of IPv6NumToString. If the IPv6 address has an invalid format, it returns a string of null bytes.
If the input string contains a valid IPv4 address, returns its IPv6 equivalent.
HEX can be uppercase or lowercase.

Syntax

IPv6StringToNum(string)

Argument

string — IP address. String.

Returned value

IPv6 address in binary format. Type: FixedString(16).

Example

SELECT addr, cutIPv6(IPv6StringToNum(addr), 0, 0) FROM (SELECT ['notaddress', '127.0.0.1', '1111::ffff'] AS addr) ARRAY JOIN addr;

┌─addr───────┬─cutIPv6(IPv6StringToNum(addr), 0, 0)─┐
│ notaddress │ ::                                   │
│ 127.0.0.1  │ ::                                   │
│ 1111::ffff │ 1111::ffff                           │
└────────────┴──────────────────────────────────────┘

cutIPv6

Accepts a FixedString(16) value containing the IPv6 address in binary format. Returns a string containing the address of the specified number of bytes removed in text format.

Syntax

cutIPv6(x, bytesToCutForIPv6, bytesToCutForIPv4)

Arguments

x – a FixedString(16) value containing the IPv6 address in binary format
bytesToCutForIPv6 - number of bytes to cut for ipv6 represenration
bytesToCutForIPv4 - number of bytes to cut for ipv4 represenration

Returned value
A Uint64 data type hash value.

Type: UInt64

Examples

WITH
    IPv6StringToNum('2001:0DB8:AC10:FE01:FEED:BABE:CAFE:F00D') AS ipv6,
    IPv4ToIPv6(IPv4StringToNum('192.168.0.1')) AS ipv4
SELECT
    cutIPv6(ipv6, 2, 0),
    cutIPv6(ipv4, 0, 2)

┌─cutIPv6(ipv6, 2, 0)─────────────────┬─cutIPv6(ipv4, 0, 2)─┐
│ 2001:db8:ac10:fe01:feed:babe:cafe:0 │ ::ffff:192.168.0.0  │
└─────────────────────────────────────┴─────────────────────┘

toIPv4

An alias to IPv4StringToNum() that takes a string form of IPv4 address and returns value of IPv4 type, which is binary equal to value returned by IPv4StringToNum() .

Syntax

toIPv4(string)

Argument

string — IP address. String.

Returned value

IPv4 type

Example

WITH
    '171.225.130.45' as IPv4_string
SELECT
    toTypeName(IPv4StringToNum(IPv4_string)),
    toTypeName(toIPv4(IPv4_string))

┌─toTypeName(IPv4StringToNum(IPv4_string))─┬─toTypeName(toIPv4(IPv4_string))─┐
│ UInt32                                   │ IPv4                            │
└──────────────────────────────────────────┴─────────────────────────────────┘

WITH
    '171.225.130.45' as IPv4_string
SELECT
    hex(IPv4StringToNum(IPv4_string)),
    hex(toIPv4(IPv4_string))

┌─hex(IPv4StringToNum(IPv4_string))─┬─hex(toIPv4(IPv4_string))─┐
│ ABE1822D                          │ ABE1822D                 │
└───────────────────────────────────┴──────────────────────────┘

toIPv6

Converts a string form of IPv6 address to IPv6 type. If the IPv6 address has an invalid format, returns an empty value.
Similar to IPv6StringToNum function, which converts IPv6 address to binary format.

Syntax

toIPv6(string)

Argument

string — IP address. String

Returned value

IP address. Type: IPv6.

Examples

WITH '2001:438:ffff::407d:1bc1' AS IPv6_string
SELECT
    hex(IPv6StringToNum(IPv6_string)),
    hex(toIPv6(IPv6_string));

┌─hex(IPv6StringToNum(IPv6_string))─┬─hex(toIPv6(IPv6_string))─────────┐
│ 20010438FFFF000000000000407D1BC1  │ 20010438FFFF000000000000407D1BC1 │
└───────────────────────────────────┴──────────────────────────────────┘

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