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

Did this page help you?