MD5 #

Calculate MD5 from a string and return the result byte set as FixedString(16).
If you do not need a specific MD5 but require a suitable encrypted 128-bit hash, use the [sipHash128] function instead.
To get the same result as the md5sum utility output, use lower(hex(MD5(s))).
Syntax

select MD5(string)

Parameter

• string– a string

Return value

• The MD5 value.

Type: FixedString(16)
Example

select MD5('test the MD5 function')

As a result:

┌─MD5('test the MD5 function')─┐ 
│ ो?N?iq@?5G?:a               │ 
└──────────────────────────────┘

SHA1 #

Calculate SHA-1, SHA-224, or SHA-256 from a string and return the result byte set as FixedString(20), FixedString(28), or FixedString(32).
This function runs rather slowly (SHA-1 processes about 5 million short strings per core per second, while SHA-224 and SHA-256 process about 2.2 million).
We suggest using this function only when a specific hash function is required and cannot be substituted.
Even in these cases, we recommend applying the function offline and pre-calculating when inserting values into the table, rather than applying it in SELECT statements.
Syntax

SHA1(string)

Parameter

• string– a string.

Return value

• SHA-1 encrypted string in FixedString(20).

Type: FixedString(20)
Example

SELECT base64Encode(toString(SHA1('SAH1 test')))

As a result:

┌─base64Encode(toString(SHA1('SAH1 test')))─┐ 
│ x5bDmJsgE+YzTir1+BPt4S98AEc=              │ 
└───────────────────────────────────────────┘

SHA224 #

Calculate SHA-1, SHA-224, or SHA-256 from a string and return the result byte set as FixedString(20), FixedString(28), or FixedString(32).
This function runs rather slowly (SHA-1 processes about 5 million short strings per core per second, while SHA-224 and SHA-256 process about 2.2 million).
We suggest using this function only when a specific hash function is required and cannot be substituted.
Even in these cases, we recommend applying the function offline and pre-calculating when inserting values into the table, rather than applying it in SELECT statements.
Syntax

SHA224(string)

Parameter

• string– a string.

Return value

• SHA-224 encrypted string in FixedString(28).

Type: FixedString(28)
Example

SELECT base64Encode(toString(SHA224('SAH224 test')))

As a result:

┌─base64Encode(toString(SHA224('SAH224 test')))─┐ 
│ eptUvYjJG4AeQfQI9kZ/qViECg0gRbuwPJ5UlA==      │ 
└───────────────────────────────────────────────┘

SHA256 #

Calculate SHA-1, SHA-224, or SHA-256 from a string and return the result byte set as FixedString(20), FixedString(28), or FixedString(32).
This function runs rather slowly (SHA-1 processes about 5 million short strings per core per second, while SHA-224 and SHA-256 process about 2.2 million).
We suggest using this function only when a specific hash function is required and cannot be substituted.
Even in these cases, we suggest applying this function offline and pre-calculating the Value when inserting it into the table, rather than applying it in SELECTS.
Syntax

SHA256(string)

Param

• string– a String.

Return value

• SHA-256 encrypted String in FixedString(32).

Type: FixedString(32)
Example

SELECT base64Encode(toString(SHA256('SAH256 test')))

As a result:

┌─base64Encode(toString(SHA256('SAH256 test')))─┐ 
│ I+OvFrLmD2Ofq1xBuFmKYCuB6iSg5/OrhcIbI5Qezs8=  │ 
└───────────────────────────────────────────────┘

URLHash #

A fast, high-quality Not encrypted hash function used to obtain a String from a URL using a certain type of normalization.
URLHash(s)/– Calculate the hash Value of a String that does not include trailing symbols (? or # at the end, if present).
URLHash(s, N)– Calculate the hash Value of a String from N levels in the URL hierarchy, excluding trailing symbols /, ?, or # at the end (if present).
The levels are the same as those in URLHierarchy. This Function is specific to Yandex.Metrica.
Syntax

URLHash(s,N)

Param

• s– URL String.
• N– Number of levels in UInt.

Return value

• Hash Value in UInt64

Type: Uint64
Example

SELECT URLHash('https://www.bytedance.com/en/news',2)

As a result:

┌─URLHash('https://www.bytedance.com/en/news', 2)─┐ 
│ 11898456355197509749                            │ 
└─────────────────────────────────────────────────┘

cityHash64 #

generate a 64 Digits CityHash hash Value.
This is a fast Not encrypted hash function. It uses the CityHash algorithm for String params, and a specific implementation-dependent fast Not encrypted hash function for Other Data types. This function uses the CityHash combiner to obtain the final result.
Syntax

cityHash64(par1,...)

Param
This function accepts a variable Quantity of Input params. Params can be any Support project Data type.
Return value
Hash Value of Data UInt64 type.
Type: UInt64
Example
Example call:

SELECT cityHash64(array('e','x','a'), 'mple', 10, toDateTime('2019-06-15 23:00:00')) AS CityHash, toTypeName(CityHash) AS type;

As a result:

┌─CityHash─────────────┬─type───┐ 
│ 16754903278969853710 │ UInt64 │ 
└──────────────────────┴────────┘

The following example shows how to Calculate the checksum of the entire table, with precision up to row order:

SELECT groupBitXor(cityHash64(*)) FROM table

farmHash64 #

generate a 64 Digits [FarmHash] or fingerprint Value. farmFingerprint64 is more suitable for stable and portable Values.
Syntax

farmFingerprint64(par1, ...) 
farmHash64(par1, ...)

These functions respectively use Fingerprint64 and Hash64 methods among all [Available methods]. Hash64
Param
This function accepts a variable Quantity of Input params. Params can be any [Support project Data type].
Return value
A hash Value of UInt64 Data type.
Type: UInt64
Example

SELECT farmHash64(array('e','x','a'), 'mple', 10, toDateTime('2019-06-15 23:00:00')) AS FarmHash, toTypeName(FarmHash) AS type;

As a result:

┌─FarmHash─────────────┬─type───┐ 
│ 16660843471760112397 │ UInt64 │ 
└──────────────────────┴────────┘

gccMurmurHash #

use the same hash seed as gcctoCalculatea64DigitsMurmurHash2
Syntax

gccMurmurHash(par1, ...)

Param

• par1, ...— variable Quantity of params, can be any [Support project Data type].

Return value

• Calculated hash Value.

Type: UInt64.
Example
Query:

SELECT gccMurmurHash(1, 2, 3) AS res1,gccMurmurHash(('a', [1, 2, 3], 4, (4, ['foo', 'bar'], 1, (1, 2)))) AS res2;

As a result:

┌─res1─────────────────┬─res2────────────────┐ 
│ 12384823029245979431 │ 1188926775431157506 │ 
└──────────────────────┴─────────────────────┘

halfMD5 #

[Explanation] All input parameters are strings, and the MD5 hash value is calculated for each parameter. Then, the hash values are combined, the first 8 bytes of the resulting string hash value are taken, and these are interpreted as a UInt64 in big-endian byte order.
Syntax

halfMD5(par1, ...)

This function is relatively slow (five million short strings per processor core per second).
Consider using the [sipHash64] function.
Parameters
This function accepts a variable number of input parameters. Parameters can be any [supported data type].
Return value
[UInt64] data type hash value.
Type: UInt64
Example

SELECT halfMD5(array('e','x','a'), 'mple', 10, toDateTime('2019-06-15 23:00:00')) AS halfMD5hash, toTypeName(halfMD5hash) AS type;

┌─halfMD5hash────────┬─type───┐ 
│ 236874400738930661 │ UInt64 │ 
└────────────────────┴────────┘

hiveHash #

HiveHash is calculated based on a string.
Syntax

SELECT hiveHash('')

This is just [JavaHash] with the sign bit cleared. This function is used in versions of Apache Hive prior to 3.0. This hash function is neither fast nor high-quality. The only reason to use it is when this algorithm has already been used in another system and you must calculate exactly the same result.
Return value
Data Int32 type hash value.
Type: hiveHash.
Example
Query:

SELECT hiveHash('Hello, world!');

As a result:

┌─hiveHash('Hello, world!')─┐ 
│ 267439093                 │ 
└───────────────────────────┘

intHash32 #

Calculates a 32-bit hash code from any type of integer.
This is a relatively fast non-cryptographic hash function with general numeric quality.
Syntax

intHash32(integer)

Parameter

• integer – integer.

Return value

• 32-bit hash code.

Type: UInt32
Example

SELECT intHash32(12072650598913549138);

As a result:

┌─intHash32(12072650598913549138)─┐ 
│ 3406876673                      │ 
└─────────────────────────────────┘

intHash64 #

Calculates a 64-bit hash code from any type of integer. It runs faster than intHash32.
Syntax

intHash64(integer)

Parameter

• integer – integer.

Return value

• 64-bit hash code.

Type: UInt64
Example

SELECT intHash64(12072650598913549138);

As a result:

┌─intHash32(12072650598913549138)─┐ 
│ 3406876673                      │ 
└─────────────────────────────────┘

javaHash #

Calculates JavaHash based on a string >#l1452. This hash function is neither fast nor high-quality. The only reason to use it is when this algorithm has already been used in another system and you must calculate exactly the same result.
Syntax

SELECT javaHash('')

Parameter

• Astring

Return value

• Data Int32 type hash value.

Example
Query:

SELECT javaHash('Hello, world!');

As a result:

┌─javaHash('Hello, world!')─┐ 
│ -1880044555               │ 
└───────────────────────────┘

JumpConsistentHash #

Calculates JumpConsistentHash from a UInt64 value.
Accepts two parameters: a UInt64 key and the number of buckets. Returns Int32.
Syntax

JumpConsistentHash(key, buckets)

Parameters

• key – UInt64 type key.
• buckets – number of buckets.

Return value

• Results in Int32.

Type: Int32
Example

SELECT jumpConsistentHash(18446744073709551615, 12);

As a result:

┌─jumpConsistentHash(18446744073709551615, 12)─┐ 
│ 10                                           │ 
└──────────────────────────────────────────────┘

metroHash64 #

Generates a 64-bit MetroHash hash value.
Syntax

metroHash64(par1, ...)

Parameters
This function accepts a variable number of input parameters. Parameters can be any [supported data type].
Return value
[UInt64] data type hash value.
Type: UInt64
Example

SELECT metroHash64(array('e','x','a'), 'mple', 10, toDateTime('2019-06-15 23:00:00')) AS MetroHash, toTypeName(MetroHash) AS type;

┌─MetroHash────────────┬─type───┐ 
│ 15061233052885528061 │ UInt64 │ 
└──────────────────────┴────────┘

murmurHash2_32 #

Generates a MurmurHash2 hash value.
Syntax

murmurHash2_32(par1, ...)

Parameters
Both functions accept a variable number of input parameters. Parameters can be of any supported data type.
Return value

• The murmurHash2_32 function returns a hash value of data type [UInt32].

Type: Uint32
Example

SELECT murmurHash2_32('test');

As a result:

┌─murmurHash2_32('test')─┐ 
│ 403862830              │ 
└────────────────────────┘

murmurHash2_64 #

Generates a MurmurHash2 hash value.
Syntax

murmurHash2_64(par1, ...)

Parameters
Both functions accept a variable number of input parameters. Parameters can be of any supported data type.
Return value

• This function returns a hash value of data type UInt64 for murmurHash2_64.

Type: Uint64
Example

SELECT murmurHash2_64(array('e','x','a'), 'mple', 10, toDateTime('2019-06-15 23:00:00')) AS MurmurHash2, toTypeName(MurmurHash2) AS type;

┌─MurmurHash2────────┬─type───┐ 
│ 178997598777428518 │ UInt64 │ 
└────────────────────┴────────┘

murmurHash3_128 #

Generates a 128-bit MurmurHash3 hash value.
Syntax

murmurHash3_128( expr )

Parameters

• expr — An expression that returns a value of string type.

Return value

• Hash value of data type FixedString(16).

Type: FixedString(16)
Example

SELECT hex(murmurHash3_128('example_string')) AS MurmurHash3, toTypeName(MurmurHash3) AS type;

As a result:

┌─MurmurHash3──────────────────────┬─type───┐ 
│ 368A1A311CB7342253354B548E7E7E71 │ String │ 
└──────────────────────────────────┴────────┘

murmurHash3_32 #

Generates a MurmurHash3 hash value.
Syntax

murmurHash3_32(par1, ...)

Parameters
Both functions accept a variable number of input parameters. Parameters can be of any supported data type.
Return value

• The murmurHash3_32 function returns a hash value of data type UInt32.

Type: Uint32
Example

SELECT murmurHash3_32(array('e','x','a'), 'mple', 10, toDateTime('2019-06-15 23:00:00')) AS MurmurHash3, toTypeName(MurmurHash3) AS type;

As a result:

┌─MurmurHash3─┬─type───┐ 
│ 3163913960  │ UInt32 │ 
└─────────────┴────────┘

murmurHash3_64 #

Generates a MurmurHash3 hash value.
Syntax

murmurHash3_64(par1, ...)

Parameters
Both functions accept a variable number of input parameters. Parameters can be of any supported data type.
Return value

• The murmurHash3_64 function returns a hash value of data type UInt64.

Type: Uint64
Example

SELECT murmurHash3_64(array('e','x','a'), 'mple', 10, toDateTime('2019-06-15 23:00:00')) AS MurmurHash3, toTypeName(MurmurHash3) AS type;

As a result:

┌─MurmurHash3──────────┬─type───┐ 
│ 12839777463072566684 │ UInt64 │ 
└──────────────────────┴────────┘

sipHash128 #

Calculates the SipHash value from a string.
Accepts parameters of string type. Returns FixedString(16).
The difference from sipHash64 is that the final XOR folded state is only 128 bits.
Syntax

sipHash128(par1,...)

Parameters
This function accepts a variable number of input parameters. Parameters can be of any supported data type.
Return value
Hash value of data type UInt64.
Type: Uint64
Example

select sipHash128('test sipHash128');

As a result:

┌─sipHash128('test sipHash128')─┐ 
│ av'??"????bk??:              │ 
└───────────────────────────────┘

sipHash64 #

Generates a 64-bit SipHash hash value.
This is a cryptographic hash function. Its running speed is at least three times faster than the MD5 function.
The function interprets all input parameters as strings and calculates the hash value for each parameter. Then, the hash values are combined using the following algorithm:

• After processing all input parameters, the function will obtain a hash array.
• The function takes the first and second elements and calculates the hash value of an array containing them.
• Then, the function takes the hash value calculated in the previous step and the third element of the initial hash array, and calculates the hash value of an array containing them.
• Repeat the above step for all remaining elements of the initial hash array.

Syntax

sipHash64(par1,...)

Param
This function accepts a variable number of input Param. Param can be any supported data type.
Return value
Hash value of UInt64 data type.
Type: Uint64
Example

SELECT sipHash64(array('e','x','a'), 'mple', 10, toDateTime('2019-06-15 23:00:00')) AS SipHash, toTypeName(SipHash) AS type;

As a result:

┌─SipHash─────────────┬─type───┐ 
│ 7934052906670448870 │ UInt64 │ 
└─────────────────────┴────────┘

xxHash32 #

xxHash calculates the hash value of a string.
xxHash is an extremely fast non-cryptographic hash algorithm that operates within RAM speed limitations.
Syntax

SELECT xxHash32(s)

Param

• s – string.

Return value
Hash value of data Uint32 type.
Type: UInt32
Example

SELECT xxHash32('Hello, world!')

As a result:

┌─xxHash32('Hello, world!')─┐ 
│ 834093149                 │ 
└───────────────────────────┘

See also

• xxHash.

xxHash64 #

xxHash calculates the hash value of a string.
xxHash is an extremely fast non-cryptographic hash algorithm that operates within RAM speed limitations.
Syntax

SELECT xxHash64(s)

Param

• s – string.

Return value
Hash value of data Uint64 type.
Type: UInt64
Example

SELECT xxHash64('Hello, world!')

As a result:

┌─xxHash64('Hello, world!')─┐ 
│ 17691043854468224118      │ 
└───────────────────────────┘

#