Skip to main content
Skip to main content
Edit this page

Hash functions

Hash functions can be used for the deterministic pseudo-random shuffling of elements.

Simhash is a hash function, which returns close hash values for close (similar) arguments.

Most hash functions accept any number of arguments of any types.

Note

Hash of NULL is NULL. To get a non-NULL hash of a Nullable column, wrap it in a tuple:

SELECT cityHash64(tuple(NULL))
Note

To calculate hash of the whole contents of a table, use sum(cityHash64(tuple(*))) (or other hash function). tuple ensures that rows with NULL values are not skipped. sum ensures that the order of rows doesn't matter.

halfMD5​

Interprets all the input parameters as strings and calculates the MD5 hash value for each of them. Then combines hashes, takes the first 8 bytes of the hash of the resulting string, and interprets them as UInt64 in big-endian byte order.

halfMD5(par1, ...)

The function is relatively slow (5 million short strings per second per processor core). Consider using the sipHash64 function instead.

Arguments

The function takes a variable number of input parameters. Arguments can be any of the supported data types. For some data types calculated value of hash function may be the same for the same values even if types of arguments differ (integers of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data).

Returned Value

A UInt64 data type hash value.

Example

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

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

MD4​

Calculates the MD4 from a string and returns the resulting set of bytes as FixedString(16).

MD5​

Calculates the MD5 from a string and returns the resulting set of bytes as FixedString(16). If you do not need MD5 in particular, but you need a decent cryptographic 128-bit hash, use the 'sipHash128' function instead. If you want to get the same result as output by the md5sum utility, use lower(hex(MD5(s))).

RIPEMD160​

Produces RIPEMD-160 hash value.

Syntax

RIPEMD160(input)

Parameters

Returned value

Example

Use the hex function to represent the result as a hex-encoded string.

Query:

SELECT HEX(RIPEMD160('The quick brown fox jumps over the lazy dog'));

┌─HEX(RIPEMD160('The quick brown fox jumps over the lazy dog'))─┐
│ 37F332F68DB77BD9D7EDD4969571AD671CF9DD3B                      │
└───────────────────────────────────────────────────────────────┘

sipHash64​

Produces a 64-bit SipHash hash value.

sipHash64(par1,...)

This is a cryptographic hash function. It works at least three times faster than the MD5 hash function.

The function interprets all the input parameters as strings and calculates the hash value for each of them. It then combines the hashes by the following algorithm:

  1. The first and the second hash value are concatenated to an array which is hashed.
  2. The previously calculated hash value and the hash of the third input parameter are hashed in a similar way.
  3. This calculation is repeated for all remaining hash values of the original input.

Arguments

The function takes a variable number of input parameters of any of the supported data types.

Returned Value

A UInt64 data type hash value.

Note that the calculated hash values may be equal for the same input values of different argument types. This affects for example integer types of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data.

Example

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

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

sipHash64Keyed​

Same as sipHash64 but additionally takes an explicit key argument instead of using a fixed key.

Syntax

sipHash64Keyed((k0, k1), par1,...)

Arguments

Same as sipHash64, but the first argument is a tuple of two UInt64 values representing the key.

Returned value

A UInt64 data type hash value.

Example

Query:

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

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

sipHash128​

Like sipHash64 but produces a 128-bit hash value, i.e. the final xor-folding state is done up to 128 bits.

Note

This 128-bit variant differs from the reference implementation and it's weaker. This version exists because, when it was written, there was no official 128-bit extension for SipHash. New projects should probably use sipHash128Reference.

Syntax

sipHash128(par1,...)

Arguments

Same as for sipHash64.

Returned value

A 128-bit SipHash hash value of type FixedString(16).

Example

Query:

SELECT hex(sipHash128('foo', '\x01', 3));

Result:

┌─hex(sipHash128('foo', '', 3))────┐
│ 9DE516A64A414D4B1B609415E4523F24 │
└──────────────────────────────────┘

sipHash128Keyed​

Same as sipHash128 but additionally takes an explicit key argument instead of using a fixed key.

Note

This 128-bit variant differs from the reference implementation and it's weaker. This version exists because, when it was written, there was no official 128-bit extension for SipHash. New projects should probably use sipHash128ReferenceKeyed.

Syntax

sipHash128Keyed((k0, k1), par1,...)

Arguments

Same as sipHash128, but the first argument is a tuple of two UInt64 values representing the key.

Returned value

A 128-bit SipHash hash value of type FixedString(16).

Example

Query:

SELECT hex(sipHash128Keyed((506097522914230528, 1084818905618843912),'foo', '\x01', 3));

Result:

┌─hex(sipHash128Keyed((506097522914230528, 1084818905618843912), 'foo', '', 3))─┐
│ B8467F65C8B4CFD9A5F8BD733917D9BF                                              │
└───────────────────────────────────────────────────────────────────────────────┘

sipHash128Reference​

Like sipHash128 but implements the 128-bit algorithm from the original authors of SipHash.

Syntax

sipHash128Reference(par1,...)

Arguments

Same as for sipHash128.

Returned value

A 128-bit SipHash hash value of type FixedString(16).

Example

Query:

SELECT hex(sipHash128Reference('foo', '\x01', 3));

Result:

┌─hex(sipHash128Reference('foo', '', 3))─┐
│ 4D1BE1A22D7F5933C0873E1698426260       │
└────────────────────────────────────────┘

sipHash128ReferenceKeyed​

Same as sipHash128Reference but additionally takes an explicit key argument instead of using a fixed key.

Syntax

sipHash128ReferenceKeyed((k0, k1), par1,...)

Arguments

Same as sipHash128Reference, but the first argument is a tuple of two UInt64 values representing the key.

Returned value

A 128-bit SipHash hash value of type FixedString(16).

Example

Query:

SELECT hex(sipHash128ReferenceKeyed((506097522914230528, 1084818905618843912),'foo', '\x01', 3));

Result:

┌─hex(sipHash128ReferenceKeyed((506097522914230528, 1084818905618843912), 'foo', '', 3))─┐
│ 630133C9722DC08646156B8130C4CDC8                                                       │
└────────────────────────────────────────────────────────────────────────────────────────┘

cityHash64​

Produces a 64-bit CityHash hash value.

cityHash64(par1,...)

This is a fast non-cryptographic hash function. It uses the CityHash algorithm for string parameters and implementation-specific fast non-cryptographic hash function for parameters with other data types. The function uses the CityHash combinator to get the final results.

Note that Google changed the algorithm of CityHash after it has been added to ClickHouse. In other words, ClickHouse's cityHash64 and Google's upstream CityHash now produce different results. ClickHouse cityHash64 corresponds to CityHash v1.0.2.

Arguments

The function takes a variable number of input parameters. Arguments can be any of the supported data types. For some data types calculated value of hash function may be the same for the same values even if types of arguments differ (integers of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data).

Returned Value

A UInt64 data type hash value.

Examples

Call example:

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

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

The following example shows how to compute the checksum of the entire table with accuracy up to the row order:

SELECT groupBitXor(cityHash64(*)) FROM table

intHash32​

Calculates a 32-bit hash code from any type of integer. This is a relatively fast non-cryptographic hash function of average quality for numbers.

Syntax

intHash32(int)

Arguments

  • int — Integer to hash. (U)Int*.

Returned value

Example

Query:

SELECT intHash32(42);

Result:

┌─intHash32(42)─┐
│    1228623923 │
└───────────────┘

intHash64​

Calculates a 64-bit hash code from any type of integer. This is a relatively fast non-cryptographic hash function of average quality for numbers. It works faster than intHash32.

Syntax

intHash64(int)

Arguments

  • int — Integer to hash. (U)Int*.

Returned value

Example

Query:

SELECT intHash64(42);

Result:

┌────────intHash64(42)─┐
│ 11490350930367293593 │
└──────────────────────┘

SHA1, SHA224, SHA256, SHA512, SHA512_256​

Calculates SHA-1, SHA-224, SHA-256, SHA-512, SHA-512-256 hash from a string and returns the resulting set of bytes as FixedString.

Syntax

SHA1('s')
...
SHA512('s')

The function works fairly slowly (SHA-1 processes about 5 million short strings per second per processor core, while SHA-224 and SHA-256 process about 2.2 million). We recommend using this function only in cases when you need a specific hash function and you can't select it. Even in these cases, we recommend applying the function offline and pre-calculating values when inserting them into the table, instead of applying it in SELECT queries.

Arguments

  • s — Input string for SHA hash calculation. String.

Returned value

  • SHA hash as a hex-unencoded FixedString. SHA-1 returns as FixedString(20), SHA-224 as FixedString(28), SHA-256 — FixedString(32), SHA-512 — FixedString(64). FixedString.

Example

Use the hex function to represent the result as a hex-encoded string.

Query:

SELECT hex(SHA1('abc'));

Result:

┌─hex(SHA1('abc'))─────────────────────────┐
│ A9993E364706816ABA3E25717850C26C9CD0D89D │
└──────────────────────────────────────────┘

BLAKE3​

Calculates BLAKE3 hash string and returns the resulting set of bytes as FixedString.

Syntax

BLAKE3('s')

This cryptographic hash-function is integrated into ClickHouse with BLAKE3 Rust library. The function is rather fast and shows approximately two times faster performance compared to SHA-2, while generating hashes of the same length as SHA-256.

Arguments

  • s - input string for BLAKE3 hash calculation. String.

Return value

  • BLAKE3 hash as a byte array with type FixedString(32). FixedString.

Example

Use function hex to represent the result as a hex-encoded string.

Query:

SELECT hex(BLAKE3('ABC'))

Result:

┌─hex(BLAKE3('ABC'))───────────────────────────────────────────────┐
│ D1717274597CF0289694F75D96D444B992A096F1AFD8E7BBFA6EBB1D360FEDFC │
└──────────────────────────────────────────────────────────────────┘

URLHash(url[, N])​

A fast, decent-quality non-cryptographic hash function for a string obtained from a URL using some type of normalization. URLHash(s) – Calculates a hash from a string without one of the trailing symbols /,? or # at the end, if present. URLHash(s, N) – Calculates a hash from a string up to the N level in the URL hierarchy, without one of the trailing symbols /,? or # at the end, if present. Levels are the same as in URLHierarchy.

farmFingerprint64​

farmHash64​

Produces a 64-bit FarmHash or Fingerprint value. farmFingerprint64 is preferred for a stable and portable value.

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

These functions use the Fingerprint64 and Hash64 methods respectively from all available methods.

Arguments

The function takes a variable number of input parameters. Arguments can be any of the supported data types. For some data types calculated value of hash function may be the same for the same values even if types of arguments differ (integers of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data).

Returned Value

A UInt64 data type hash value.

Example

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

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

javaHash​

Calculates JavaHash from a string, Byte, Short, Integer, Long. This hash function is neither fast nor having a good quality. The only reason to use it is when this algorithm is already used in another system and you have to calculate exactly the same result.

Note that Java only support calculating signed integers hash, so if you want to calculate unsigned integers hash you must cast it to proper signed ClickHouse types.

Syntax

SELECT javaHash('')

Returned value

A Int32 data type hash value.

Example

Query:

SELECT javaHash(toInt32(123));

Result:

┌─javaHash(toInt32(123))─┐
│               123      │
└────────────────────────┘

Query:

SELECT javaHash('Hello, world!');

Result:

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

javaHashUTF16LE​

Calculates JavaHash from a string, assuming it contains bytes representing a string in UTF-16LE encoding.

Syntax

javaHashUTF16LE(stringUtf16le)

Arguments

  • stringUtf16le — a string in UTF-16LE encoding.

Returned value

A Int32 data type hash value.

Example

Correct query with UTF-16LE encoded string.

Query:

SELECT javaHashUTF16LE(convertCharset('test', 'utf-8', 'utf-16le'));

Result:

┌─javaHashUTF16LE(convertCharset('test', 'utf-8', 'utf-16le'))─┐
│                                                      3556498 │
└──────────────────────────────────────────────────────────────┘

hiveHash​

Calculates HiveHash from a string.

SELECT hiveHash('')

This is just JavaHash with zeroed out sign bit. This function is used in Apache Hive for versions before 3.0. This hash function is neither fast nor having a good quality. The only reason to use it is when this algorithm is already used in another system and you have to calculate exactly the same result.

Returned value

  • hiveHash hash value. Int32.

Example

Query:

SELECT hiveHash('Hello, world!');

Result:

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

metroHash64​

Produces a 64-bit MetroHash hash value.

metroHash64(par1, ...)

Arguments

The function takes a variable number of input parameters. Arguments can be any of the supported data types. For some data types calculated value of hash function may be the same for the same values even if types of arguments differ (integers of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data).

Returned Value

A UInt64 data type hash value.

Example

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

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

jumpConsistentHash​

Calculates JumpConsistentHash form a UInt64. Accepts two arguments: a UInt64-type key and the number of buckets. Returns Int32. For more information, see the link: JumpConsistentHash

kostikConsistentHash​

An O(1) time and space consistent hash algorithm by Konstantin 'kostik' Oblakov. Previously yandexConsistentHash.

Syntax

kostikConsistentHash(input, n)

Alias: yandexConsistentHash (left for backwards compatibility sake).

Parameters

  • input: A UInt64-type key UInt64.
  • n: Number of buckets. UInt16.

Returned value

  • A UInt16 data type hash value.

Implementation details

It is efficient only if n <= 32768.

Example

Query:

SELECT kostikConsistentHash(16045690984833335023, 2);

┌─kostikConsistentHash(16045690984833335023, 2)─┐
│                                             1 │
└───────────────────────────────────────────────┘

murmurHash2_32, murmurHash2_64​

Produces a MurmurHash2 hash value.

murmurHash2_32(par1, ...)
murmurHash2_64(par1, ...)

Arguments

Both functions take a variable number of input parameters. Arguments can be any of the supported data types. For some data types calculated value of hash function may be the same for the same values even if types of arguments differ (integers of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data).

Returned Value

  • The murmurHash2_32 function returns hash value having the UInt32 data type.
  • The murmurHash2_64 function returns hash value having the UInt64 data type.

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───┐
│ 11832096901709403633 │ UInt64 │
└──────────────────────┴────────┘

gccMurmurHash​

Calculates a 64-bit MurmurHash2 hash value using the same hash seed as gcc. It is portable between Clang and GCC builds.

Syntax

gccMurmurHash(par1, ...)

Arguments

Returned value

  • Calculated hash value. UInt64.

Example

Query:

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

Result:

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

kafkaMurmurHash​

Calculates a 32-bit MurmurHash2 hash value using the same hash seed as Kafka and without the highest bit to be compatible with Default Partitioner.

Syntax

MurmurHash(par1, ...)

Arguments

Returned value

  • Calculated hash value. UInt32.

Example

Query:

SELECT
    kafkaMurmurHash('foobar') AS res1,
    kafkaMurmurHash(array('e','x','a'), 'mple', 10, toDateTime('2019-06-15 23:00:00')) AS res2

Result:

┌───────res1─┬─────res2─┐
│ 1357151166 │ 85479775 │
└────────────┴──────────┘

murmurHash3_32, murmurHash3_64​

Produces a MurmurHash3 hash value.

murmurHash3_32(par1, ...)
murmurHash3_64(par1, ...)

Arguments

Both functions take a variable number of input parameters. Arguments can be any of the supported data types. For some data types calculated value of hash function may be the same for the same values even if types of arguments differ (integers of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data).

Returned Value

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

Example

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

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

murmurHash3_128​

Produces a 128-bit MurmurHash3 hash value.

Syntax

murmurHash3_128(expr)

Arguments

Returned value

A 128-bit MurmurHash3 hash value. FixedString(16).

Example

Query:

SELECT hex(murmurHash3_128('foo', 'foo', 'foo'));

Result:

┌─hex(murmurHash3_128('foo', 'foo', 'foo'))─┐
│ F8F7AD9B6CD4CF117A71E277E2EC2931          │
└───────────────────────────────────────────┘

xxh3​

Produces a 64-bit xxh3 hash value.

Syntax

xxh3(expr)

Arguments

Returned value

A 64-bit xxh3 hash value. UInt64.

Example

Query:

SELECT xxh3('Hello', 'world')

Result:

┌─xxh3('Hello', 'world')─┐
│    5607458076371731292 │
└────────────────────────┘

xxHash32, xxHash64​

Calculates xxHash from a string. It is proposed in two flavors, 32 and 64 bits.

SELECT xxHash32('')

OR

SELECT xxHash64('')

Returned value

Note

The return type will be UInt32 for xxHash32 and UInt64 for xxHash64.

Example

Query:

SELECT xxHash32('Hello, world!');

Result:

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

See Also

ngramSimHash​

Splits a ASCII string into n-grams of ngramsize symbols and returns the n-gram simhash. Is case sensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller is the Hamming Distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

ngramSimHash(string[, ngramsize])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.

Returned value

Example

Query:

SELECT ngramSimHash('ClickHouse') AS Hash;

Result:

┌───────Hash─┐
│ 1627567969 │
└────────────┘

ngramSimHashCaseInsensitive​

Splits a ASCII string into n-grams of ngramsize symbols and returns the n-gram simhash. Is case insensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller is the Hamming Distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

ngramSimHashCaseInsensitive(string[, ngramsize])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.

Returned value

Example

Query:

SELECT ngramSimHashCaseInsensitive('ClickHouse') AS Hash;

Result:

┌──────Hash─┐
│ 562180645 │
└───────────┘

ngramSimHashUTF8​

Splits a UTF-8 string into n-grams of ngramsize symbols and returns the n-gram simhash. Is case sensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller is the Hamming Distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

ngramSimHashUTF8(string[, ngramsize])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.

Returned value

Example

Query:

SELECT ngramSimHashUTF8('ClickHouse') AS Hash;

Result:

┌───────Hash─┐
│ 1628157797 │
└────────────┘

ngramSimHashCaseInsensitiveUTF8​

Splits a UTF-8 string into n-grams of ngramsize symbols and returns the n-gram simhash. Is case insensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller is the Hamming Distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

ngramSimHashCaseInsensitiveUTF8(string[, ngramsize])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.

Returned value

Example

Query:

SELECT ngramSimHashCaseInsensitiveUTF8('ClickHouse') AS Hash;

Result:

┌───────Hash─┐
│ 1636742693 │
└────────────┘

wordShingleSimHash​

Splits a ASCII string into parts (shingles) of shinglesize words and returns the word shingle simhash. Is case sensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller is the Hamming Distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

wordShingleSimHash(string[, shinglesize])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.

Returned value

Example

Query:

SELECT wordShingleSimHash('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Hash;

Result:

┌───────Hash─┐
│ 2328277067 │
└────────────┘

wordShingleSimHashCaseInsensitive​

Splits a ASCII string into parts (shingles) of shinglesize words and returns the word shingle simhash. Is case insensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller is the Hamming Distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

wordShingleSimHashCaseInsensitive(string[, shinglesize])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.

Returned value

Example

Query:

SELECT wordShingleSimHashCaseInsensitive('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Hash;

Result:

┌───────Hash─┐
│ 2194812424 │
└────────────┘

wordShingleSimHashUTF8​

Splits a UTF-8 string into parts (shingles) of shinglesize words and returns the word shingle simhash. Is case sensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller is the Hamming Distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

wordShingleSimHashUTF8(string[, shinglesize])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.

Returned value

Example

Query:

SELECT wordShingleSimHashUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Hash;

Result:

┌───────Hash─┐
│ 2328277067 │
└────────────┘

wordShingleSimHashCaseInsensitiveUTF8​

Splits a UTF-8 string into parts (shingles) of shinglesize words and returns the word shingle simhash. Is case insensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller is the Hamming Distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

wordShingleSimHashCaseInsensitiveUTF8(string[, shinglesize])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.

Returned value

Example

Query:

SELECT wordShingleSimHashCaseInsensitiveUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Hash;

Result:

┌───────Hash─┐
│ 2194812424 │
└────────────┘

wyHash64​

Produces a 64-bit wyHash64 hash value.

Syntax

wyHash64(string)

Arguments

Returned value

Example

Query:

SELECT wyHash64('ClickHouse') AS Hash;

Result:

┌─────────────────Hash─┐
│ 12336419557878201794 │
└──────────────────────┘

ngramMinHash​

Splits a ASCII string into n-grams of ngramsize symbols and calculates hash values for each n-gram. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. Returns a tuple with these hashes. Is case sensitive.

Can be used for detection of semi-duplicate strings with tupleHammingDistance. For two strings: if one of the returned hashes is the same for both strings, we think that those strings are the same.

Syntax

ngramMinHash(string[, ngramsize, hashnum])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT ngramMinHash('ClickHouse') AS Tuple;

Result:

┌─Tuple──────────────────────────────────────┐
│ (18333312859352735453,9054248444481805918) │
└────────────────────────────────────────────┘

ngramMinHashCaseInsensitive​

Splits a ASCII string into n-grams of ngramsize symbols and calculates hash values for each n-gram. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. Returns a tuple with these hashes. Is case insensitive.

Can be used for detection of semi-duplicate strings with tupleHammingDistance. For two strings: if one of the returned hashes is the same for both strings, we think that those strings are the same.

Syntax

ngramMinHashCaseInsensitive(string[, ngramsize, hashnum])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT ngramMinHashCaseInsensitive('ClickHouse') AS Tuple;

Result:

┌─Tuple──────────────────────────────────────┐
│ (2106263556442004574,13203602793651726206) │
└────────────────────────────────────────────┘

ngramMinHashUTF8​

Splits a UTF-8 string into n-grams of ngramsize symbols and calculates hash values for each n-gram. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. Returns a tuple with these hashes. Is case sensitive.

Can be used for detection of semi-duplicate strings with tupleHammingDistance. For two strings: if one of the returned hashes is the same for both strings, we think that those strings are the same.

Syntax

ngramMinHashUTF8(string[, ngramsize, hashnum])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT ngramMinHashUTF8('ClickHouse') AS Tuple;

Result:

┌─Tuple──────────────────────────────────────┐
│ (18333312859352735453,6742163577938632877) │
└────────────────────────────────────────────┘

ngramMinHashCaseInsensitiveUTF8​

Splits a UTF-8 string into n-grams of ngramsize symbols and calculates hash values for each n-gram. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. Returns a tuple with these hashes. Is case insensitive.

Can be used for detection of semi-duplicate strings with tupleHammingDistance. For two strings: if one of the returned hashes is the same for both strings, we think that those strings are the same.

Syntax

ngramMinHashCaseInsensitiveUTF8(string [, ngramsize, hashnum])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT ngramMinHashCaseInsensitiveUTF8('ClickHouse') AS Tuple;

Result:

┌─Tuple───────────────────────────────────────┐
│ (12493625717655877135,13203602793651726206) │
└─────────────────────────────────────────────┘

ngramMinHashArg​

Splits a ASCII string into n-grams of ngramsize symbols and returns the n-grams with minimum and maximum hashes, calculated by the ngramMinHash function with the same input. Is case sensitive.

Syntax

ngramMinHashArg(string[, ngramsize, hashnum])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT ngramMinHashArg('ClickHouse') AS Tuple;

Result:

┌─Tuple─────────────────────────────────────────────────────────────────────────┐
│ (('ous','ick','lic','Hou','kHo','use'),('Hou','lic','ick','ous','ckH','Cli')) │
└───────────────────────────────────────────────────────────────────────────────┘

ngramMinHashArgCaseInsensitive​

Splits a ASCII string into n-grams of ngramsize symbols and returns the n-grams with minimum and maximum hashes, calculated by the ngramMinHashCaseInsensitive function with the same input. Is case insensitive.

Syntax

ngramMinHashArgCaseInsensitive(string[, ngramsize, hashnum])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT ngramMinHashArgCaseInsensitive('ClickHouse') AS Tuple;

Result:

┌─Tuple─────────────────────────────────────────────────────────────────────────┐
│ (('ous','ick','lic','kHo','use','Cli'),('kHo','lic','ick','ous','ckH','Hou')) │
└───────────────────────────────────────────────────────────────────────────────┘

ngramMinHashArgUTF8​

Splits a UTF-8 string into n-grams of ngramsize symbols and returns the n-grams with minimum and maximum hashes, calculated by the ngramMinHashUTF8 function with the same input. Is case sensitive.

Syntax

ngramMinHashArgUTF8(string[, ngramsize, hashnum])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT ngramMinHashArgUTF8('ClickHouse') AS Tuple;

Result:

┌─Tuple─────────────────────────────────────────────────────────────────────────┐
│ (('ous','ick','lic','Hou','kHo','use'),('kHo','Hou','lic','ick','ous','ckH')) │
└───────────────────────────────────────────────────────────────────────────────┘

ngramMinHashArgCaseInsensitiveUTF8​

Splits a UTF-8 string into n-grams of ngramsize symbols and returns the n-grams with minimum and maximum hashes, calculated by the ngramMinHashCaseInsensitiveUTF8 function with the same input. Is case insensitive.

Syntax

ngramMinHashArgCaseInsensitiveUTF8(string[, ngramsize, hashnum])

Arguments

  • string — String. String.
  • ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT ngramMinHashArgCaseInsensitiveUTF8('ClickHouse') AS Tuple;

Result:

┌─Tuple─────────────────────────────────────────────────────────────────────────┐
│ (('ckH','ous','ick','lic','kHo','use'),('kHo','lic','ick','ous','ckH','Hou')) │
└───────────────────────────────────────────────────────────────────────────────┘

wordShingleMinHash​

Splits a ASCII string into parts (shingles) of shinglesize words and calculates hash values for each word shingle. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. Returns a tuple with these hashes. Is case sensitive.

Can be used for detection of semi-duplicate strings with tupleHammingDistance. For two strings: if one of the returned hashes is the same for both strings, we think that those strings are the same.

Syntax

wordShingleMinHash(string[, shinglesize, hashnum])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT wordShingleMinHash('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Tuple;

Result:

┌─Tuple──────────────────────────────────────┐
│ (16452112859864147620,5844417301642981317) │
└────────────────────────────────────────────┘

wordShingleMinHashCaseInsensitive​

Splits a ASCII string into parts (shingles) of shinglesize words and calculates hash values for each word shingle. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. Returns a tuple with these hashes. Is case insensitive.

Can be used for detection of semi-duplicate strings with tupleHammingDistance. For two strings: if one of the returned hashes is the same for both strings, we think that those strings are the same.

Syntax

wordShingleMinHashCaseInsensitive(string[, shinglesize, hashnum])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT wordShingleMinHashCaseInsensitive('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Tuple;

Result:

┌─Tuple─────────────────────────────────────┐
│ (3065874883688416519,1634050779997673240) │
└───────────────────────────────────────────┘

wordShingleMinHashUTF8​

Splits a UTF-8 string into parts (shingles) of shinglesize words and calculates hash values for each word shingle. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. Returns a tuple with these hashes. Is case sensitive.

Can be used for detection of semi-duplicate strings with tupleHammingDistance. For two strings: if one of the returned hashes is the same for both strings, we think that those strings are the same.

Syntax

wordShingleMinHashUTF8(string[, shinglesize, hashnum])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT wordShingleMinHashUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Tuple;

Result:

┌─Tuple──────────────────────────────────────┐
│ (16452112859864147620,5844417301642981317) │
└────────────────────────────────────────────┘

wordShingleMinHashCaseInsensitiveUTF8​

Splits a UTF-8 string into parts (shingles) of shinglesize words and calculates hash values for each word shingle. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. Returns a tuple with these hashes. Is case insensitive.

Can be used for detection of semi-duplicate strings with tupleHammingDistance. For two strings: if one of the returned hashes is the same for both strings, we think that those strings are the same.

Syntax

wordShingleMinHashCaseInsensitiveUTF8(string[, shinglesize, hashnum])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT wordShingleMinHashCaseInsensitiveUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Tuple;

Result:

┌─Tuple─────────────────────────────────────┐
│ (3065874883688416519,1634050779997673240) │
└───────────────────────────────────────────┘

wordShingleMinHashArg​

Splits a ASCII string into parts (shingles) of shinglesize words each and returns the shingles with minimum and maximum word hashes, calculated by the wordshingleMinHash function with the same input. Is case sensitive.

Syntax

wordShingleMinHashArg(string[, shinglesize, hashnum])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT wordShingleMinHashArg('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).', 1, 3) AS Tuple;

Result:

┌─Tuple─────────────────────────────────────────────────────────────────┐
│ (('OLAP','database','analytical'),('online','oriented','processing')) │
└───────────────────────────────────────────────────────────────────────┘

wordShingleMinHashArgCaseInsensitive​

Splits a ASCII string into parts (shingles) of shinglesize words each and returns the shingles with minimum and maximum word hashes, calculated by the wordShingleMinHashCaseInsensitive function with the same input. Is case insensitive.

Syntax

wordShingleMinHashArgCaseInsensitive(string[, shinglesize, hashnum])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT wordShingleMinHashArgCaseInsensitive('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).', 1, 3) AS Tuple;

Result:

┌─Tuple──────────────────────────────────────────────────────────────────┐
│ (('queries','database','analytical'),('oriented','processing','DBMS')) │
└────────────────────────────────────────────────────────────────────────┘

wordShingleMinHashArgUTF8​

Splits a UTF-8 string into parts (shingles) of shinglesize words each and returns the shingles with minimum and maximum word hashes, calculated by the wordShingleMinHashUTF8 function with the same input. Is case sensitive.

Syntax

wordShingleMinHashArgUTF8(string[, shinglesize, hashnum])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT wordShingleMinHashArgUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).', 1, 3) AS Tuple;

Result:

┌─Tuple─────────────────────────────────────────────────────────────────┐
│ (('OLAP','database','analytical'),('online','oriented','processing')) │
└───────────────────────────────────────────────────────────────────────┘

wordShingleMinHashArgCaseInsensitiveUTF8​

Splits a UTF-8 string into parts (shingles) of shinglesize words each and returns the shingles with minimum and maximum word hashes, calculated by the wordShingleMinHashCaseInsensitiveUTF8 function with the same input. Is case insensitive.

Syntax

wordShingleMinHashArgCaseInsensitiveUTF8(string[, shinglesize, hashnum])

Arguments

  • string — String. String.
  • shinglesize — The size of a word shingle. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8.
  • hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Example

Query:

SELECT wordShingleMinHashArgCaseInsensitiveUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).', 1, 3) AS Tuple;

Result:

┌─Tuple──────────────────────────────────────────────────────────────────┐
│ (('queries','database','analytical'),('oriented','processing','DBMS')) │
└────────────────────────────────────────────────────────────────────────┘

sqidEncode​

Encodes numbers as a Sqid which is a YouTube-like ID string. The output alphabet is abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789. Do not use this function for hashing - the generated IDs can be decoded back into the original numbers.

Syntax

sqidEncode(number1, ...)

Alias: sqid

Arguments

  • A variable number of UInt8, UInt16, UInt32 or UInt64 numbers.

Returned Value

A sqid String.

Example

SELECT sqidEncode(1, 2, 3, 4, 5);

┌─sqidEncode(1, 2, 3, 4, 5)─┐
│ gXHfJ1C6dN                │
└───────────────────────────┘

sqidDecode​

Decodes a Sqid back into its original numbers. Returns an empty array in case the input string is not a valid sqid.

Syntax

sqidDecode(sqid)

Arguments

Returned Value

The sqid transformed to numbers Array(UInt64).

Example

SELECT sqidDecode('gXHfJ1C6dN');

┌─sqidDecode('gXHfJ1C6dN')─┐
│ [1,2,3,4,5]              │
└──────────────────────────┘

keccak256​

Calculates Keccak-256 hash string and returns the resulting set of bytes as FixedString.

Syntax

keccak256('s')

This cryptographic hash-function is used a lot in EVM-based blockchains.

Arguments

  • s - input string for Keccak-256 hash calculation. String.

Return value

  • Keccak-256 hash as a byte array with type FixedString(32). FixedString.

Example

Use function hex to format the result as a hex-encoded string.

Query:

SELECT hex(keccak256('hello'))

Result:

   ┌─hex(keccak256('hello'))──────────────────────────────────────────┐
1. │ 1C8AFF950685C2ED4BC3174F3472287B56D9517B9C948127319A09A7A36DEAC8 │
   └──────────────────────────────────────────────────────────────────┘

BLAKE3​

Introduced in: v22.10

Calculates BLAKE3 hash string and returns the resulting set of bytes as FixedString. This cryptographic hash-function is integrated into ClickHouse with BLAKE3 Rust library. The function is rather fast and shows approximately two times faster performance compared to SHA-2, while generating hashes of the same length as SHA-256. It returns a BLAKE3 hash as a byte array with type FixedString(32).

Syntax

BLAKE3(message)

Arguments

  • message — The input string to hash. String

Returned value

Returns the 32-byte BLAKE3 hash of the input string as a fixed-length string. FixedString(32)

Examples

hash

SELECT hex(BLAKE3('ABC'))

┌─hex(BLAKE3('ABC'))───────────────────────────────────────────────┐
│ D1717274597CF0289694F75D96D444B992A096F1AFD8E7BBFA6EBB1D360FEDFC │
└──────────────────────────────────────────────────────────────────┘

MD4​

Introduced in: v21.11

Calculates the MD4 hash of the given string.

Syntax

MD4(s)

Arguments

  • s — The input string to hash. String

Returned value

Returns the MD4 hash of the given input string as a fixed-length string. FixedString(16)

Examples

Usage example

SELECT HEX(MD4('abc'));

┌─hex(MD4('abc'))──────────────────┐
│ A448017AAF21D8525FC10AE87AA6729D │
└──────────────────────────────────┘

MD5​

Introduced in: v1.1

Calculates the MD5 hash of the given string.

Syntax

MD5(s)

Arguments

  • s — The input string to hash. String

Returned value

Returns the MD5 hash of the given input string as a fixed-length string. FixedString(16)

Examples

Usage example

SELECT HEX(MD5('abc'));

┌─hex(MD5('abc'))──────────────────┐
│ 900150983CD24FB0D6963F7D28E17F72 │
└──────────────────────────────────┘

RIPEMD160​

Introduced in: v24.10

Calculates the RIPEMD-160 hash of the given string.

Syntax

RIPEMD160(s)

Arguments

  • s — The input string to hash. String

Returned value

Returns the RIPEMD160 hash of the given input string as a fixed-length string. FixedString(20)

Examples

Usage example

SELECT HEX(RIPEMD160('The quick brown fox jumps over the lazy dog'));

┌─HEX(RIPEMD160('The quick brown fox jumps over the lazy dog'))─┐
│ 37F332F68DB77BD9D7EDD4969571AD671CF9DD3B                      │
└───────────────────────────────────────────────────────────────┘

SHA1​

Introduced in: v1.1

Calculates the SHA1 hash of the given string.

Syntax

SHA1(s)

Arguments

  • s — The input string to hash String

Returned value

Returns the SHA1 hash of the given input string as a fixed-length string. FixedString(20)

Examples

Usage example

SELECT HEX(SHA1('abc'));

┌─hex(SHA1('abc'))─────────────────────────┐
│ A9993E364706816ABA3E25717850C26C9CD0D89D │
└──────────────────────────────────────────┘

SHA224​

Introduced in: v1.1

Calculates the SHA224 hash of the given string.

Syntax

SHA224(s)

Arguments

  • s — The input value to hash. String

Returned value

Returns the SHA224 hash of the given input string as a fixed-length string. FixedString(28)

Examples

Usage example

SELECT HEX(SHA224('abc'));

┌─hex(SHA224('abc'))───────────────────────────────────────┐
│ 23097D223405D8228642A477BDA255B32AADBCE4BDA0B3F7E36C9DA7 │
└──────────────────────────────────────────────────────────┘

SHA256​

Introduced in: v1.1

Calculates the SHA256 hash of the given string.

Syntax

SHA256(s)

Arguments

  • s — The input string to hash. String

Returned value

Returns the SHA256 hash of the given input string as a fixed-length string. FixedString(32)

Examples

Usage example

SELECT HEX(SHA256('abc'));

┌─hex(SHA256('abc'))───────────────────────────────────────────────┐
│ BA7816BF8F01CFEA414140DE5DAE2223B00361A396177A9CB410FF61F20015AD │
└──────────────────────────────────────────────────────────────────┘

SHA384​

Introduced in: v1.1

Calculates the SHA384 hash of the given string.

Syntax

SHA384(s)

Arguments

  • s — The input string to hash. String

Returned value

Returns the SHA384 hash of the given input string as a fixed-length string. FixedString(48)

Examples

Usage example

SELECT HEX(SHA384('abc'));

┌─hex(SHA384('abc'))───────────────────────────────────────────────────────────────────────────────┐
│ CB00753F45A35E8BB5A03D699AC65007272C32AB0EDED1631A8B605A43FF5BED8086072BA1E7CC2358BAECA134C825A7 │
└──────────────────────────────────────────────────────────────────────────────────────────────────┘

SHA512​

Introduced in: v1.1

Calculates the SHA512 hash of the given string.

Syntax

SHA512(s)

Arguments

  • s — The input string to hash String

Returned value

Returns the SHA512 hash of the given input string as a fixed-length string. FixedString(64)

Examples

Usage example

SELECT HEX(SHA512('abc'));

┌─hex(SHA512('abc'))───────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ DDAF35A193617ABACC417349AE20413112E6FA4E89A97EA20A9EEEE64B55D39A2192992A274FC1A836BA3C23A3FEEBBD454D4423643CE80E2A9AC94FA54CA49F │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

SHA512_256​

Introduced in: v1.1

Calculates the SHA512_256 hash of the given string.

Syntax

SHA512_256(s)

Arguments

  • s — The input string to hash. String

Returned value

Returns the SHA512_256 hash of the given input string as a fixed-length string. FixedString(32)

Examples

Usage example

SELECT HEX(SHA512_256('abc'));

┌─hex(SHA512_256('abc'))───────────────────────────────────────────┐
│ 53048E2681941EF99B2E29B76B4C7DABE4C2D0C634FC6D46E0E2F13107E7AF23 │
└──────────────────────────────────────────────────────────────────┘

URLHash​

Introduced in: v1.1

A fast, decent-quality non-cryptographic hash function for a string obtained from a URL using some type of normalization.

This hash function has two modes:

ModeDescription
URLHash(url)Calculates a hash from a string without one of the trailing symbols /,? or # at the end, if present.
URLHash(url, N)Calculates a hash from a string up to the N level in the URL hierarchy, without one of the trailing symbols /,? or # at the end, if present. Levels are the same as in URLHierarchy.

Syntax

URLHash(url[, N])

Arguments

  • url — URL string to hash. String
  • N — Optional. Level in the URL hierarchy. (U)Int*

Returned value

Returns the computed hash value of url. UInt64

Examples

Usage example

SELECT URLHash('https://www.clickhouse.com')

┌─URLHash('htt⋯house.com')─┐
│     13614512636072854701 │
└──────────────────────────┘

Hash of url with specified level

SELECT URLHash('https://www.clickhouse.com/docs', 0);
SELECT URLHash('https://www.clickhouse.com/docs', 1);

-- hash of https://www.clickhouse.com
┌─URLHash('htt⋯m/docs', 0)─┐
│     13614512636072854701 │
└──────────────────────────┘
-- hash of https://www.clickhouse.com/docs
┌─URLHash('htt⋯m/docs', 1)─┐
│     13167253331440520598 │
└──────────────────────────┘

cityHash64​

Introduced in: v1.1

Produces a 64-bit CityHash hash value.

This is a fast non-cryptographic hash function. It uses the CityHash algorithm for string parameters and implementation-specific fast non-cryptographic hash function for parameters with other data types. The function uses the CityHash combinator to get the final results.

Info

Google changed the algorithm of CityHash after it was added to ClickHouse. In other words, ClickHouse's cityHash64 and Google's upstream CityHash now produce different results. ClickHouse cityHash64 corresponds to CityHash v1.0.2.

Note

The calculated hash values may be equal for the same input values of different argument types. This affects for example integer types of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data.

Syntax

cityHash64(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns the computed hash of the input arguments. UInt64

Examples

Call example

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

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

Computing the checksum of the entire table with accuracy up to the row order

CREATE TABLE users (
    id UInt32,
    name String,
    age UInt8,
    city String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO users VALUES
(1, 'Alice', 25, 'New York'),
(2, 'Bob', 30, 'London'),
(3, 'Charlie', 35, 'Tokyo');

SELECT groupBitXor(cityHash64(*)) FROM users;

┌─groupBitXor(⋯age, city))─┐
│     11639977218258521182 │
└──────────────────────────┘

farmFingerprint64​

Introduced in: v20.12

Produces a 64-bit FarmHash value using the Fingerprint64 method.

Tip

farmFingerprint64 is preferred for a stable and portable value over farmHash64.

Note

The calculated hash values may be equal for the same input values of different argument types. This affects for example integer types of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data.

Syntax

farmFingerprint64(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns the computed hash value of the input arguments. UInt64

Examples

Usage example

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

┌─────FarmFingerprint─┬─type───┐
│ 5752020380710916328 │ UInt64 │
└─────────────────────┴────────┘

farmHash64​

Introduced in: v1.1

Produces a 64-bit FarmHash using the Hash64 method.

Tip

farmFingerprint64 is preferred for a stable and portable value.

Note

The calculated hash values may be equal for the same input values of different argument types. This affects for example integer types of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data.

Syntax

farmHash64(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns the computed hash value of the input arguments. UInt64

Examples

Usage example

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

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

gccMurmurHash​

Introduced in: v20.1

Computes the 64-bit MurmurHash2 hash of the input value using the same seed as used by GCC.

It is portable between Clang and GCC builds.

Syntax

gccMurmurHash(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of arguments for which to compute the hash. Any

Returned value

Returns the calculated hash value of the input arguments. UInt64

Examples

Usage example

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

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

halfMD5​

Introduced in: v1.1

Interprets all the input parameters as strings and calculates the MD5 hash value for each of them. Then combines hashes, takes the first 8 bytes of the hash of the resulting string, and interprets them as UInt64 in big-endian byte order. The function is relatively slow (5 million short strings per second per processor core).

Consider using the sipHash64 function instead.

The function takes a variable number of input parameters. Arguments can be any of the supported data types. For some data types calculated value of hash function may be the same for the same values even if types of arguments differ (integers of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data).

Syntax

halfMD5(arg1[, arg2, ..., argN])

Arguments

  • arg1[, arg2, ..., argN] — Variable number of arguments for which to compute the hash. Any

Returned value

Returns the computed half MD5 hash of the given input params returned as a UInt64 in big-endian byte order. UInt64

Examples

Usage example

SELECT HEX(halfMD5('abc', 'cde', 'fgh'));

┌─hex(halfMD5('abc', 'cde', 'fgh'))─┐
│ 2C9506B7374CFAF4                  │
└───────────────────────────────────┘

hiveHash​

Introduced in: v20.1

Calculates a "HiveHash" from a string. This is just JavaHash with zeroed out sign bits. This function is used in Apache Hive for versions before 3.0.

This hash function is unperformant. Use it only when this algorithm is already used in another system and you need to calculate the same result. :::

Syntax

hiveHash(arg)

Arguments

  • arg — Input string to hash. String

Returned value

Returns the computed "hive hash" of the input string. Int32

Examples

Usage example

SELECT hiveHash('Hello, world!');

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

intHash32​

Introduced in: v1.1

Calculates a 32-bit hash of an integer.

The hash function is relatively fast but not cryptographic hash function.

Syntax

intHash32(arg)

Arguments

Returned value

Returns the computed 32-bit hash code of the input integer UInt32

Examples

Usage example

SELECT intHash32(42);

┌─intHash32(42)─┐
│    1228623923 │
└───────────────┘

intHash64​

Introduced in: v1.1

Calculates a 64-bit hash of an integer.

The hash function is relatively fast (even faster than intHash32) but not a cryptographic hash function.

Syntax

intHash64(int)

Arguments

Returned value

64-bit hash code. UInt64

Examples

Usage example

SELECT intHash64(42);

┌────────intHash64(42)─┐
│ 11490350930367293593 │
└──────────────────────┘

javaHash​

Introduced in: v20.1

Calculates JavaHash from:

Caution

This hash function is unperformant. Use it only when this algorithm is already in use in another system and you need to calculate the same result.

Note

Java only supports calculating the hash of signed integers, so if you want to calculate a hash of unsigned integers you must cast them to the proper signed ClickHouse types.

Syntax

javaHash(arg)

Arguments

  • arg — Input value to hash. Any

Returned value

Returns the computed hash of arg Int32

Examples

Usage example 1

SELECT javaHash(toInt32(123));

┌─javaHash(toInt32(123))─┐
│               123      │
└────────────────────────┘

Usage example 2

SELECT javaHash('Hello, world!');

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

javaHashUTF16LE​

Introduced in: v20.1

Calculates JavaHash from a string, assuming it contains bytes representing a string in UTF-16LE encoding.

Syntax

javaHashUTF16LE(arg)

Arguments

  • arg — A string in UTF-16LE encoding. String

Returned value

Returns the computed hash of the UTF-16LE encoded string. Int32

Examples

Usage example

SELECT javaHashUTF16LE(convertCharset('test', 'utf-8', 'utf-16le'));

┌─javaHashUTF16LE(convertCharset('test', 'utf-8', 'utf-16le'))─┐
│                                                      3556498 │
└──────────────────────────────────────────────────────────────┘

jumpConsistentHash​

Introduced in: v1.1

Calculates the jump consistent hash for an integer.

Syntax

jumpConsistentHash(key, buckets)

Arguments

  • key — The input key. UInt64
  • buckets — The number of buckets. Int32

Returned value

Returns the computed hash value. Int32

Examples

Usage example

SELECT jumpConsistentHash(256, 4)

┌─jumpConsistentHash(256, 4)─┐
│                          3 │
└────────────────────────────┘

kafkaMurmurHash​

Introduced in: v23.4

Calculates the 32-bit MurmurHash2 hash of the input value using the same seed as used by Kafka and without the highest bit to be compatible with Default Partitioner.

Syntax

kafkaMurmurHash(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of parameters for which to compute the hash. Any

Returned value

Returns the calculated hash value of the input arguments. UInt32

Examples

Usage example

SELECT
    kafkaMurmurHash('foobar') AS res1,
    kafkaMurmurHash(array('e','x','a'), 'mple', 10, toDateTime('2019-06-15 23:00:00')) AS res2

┌───────res1─┬─────res2─┐
│ 1357151166 │ 85479775 │
└────────────┴──────────┘

keccak256​

Introduced in: v25.4

Calculates the Keccak-256 cryptographic hash of the given string. This hash function is widely used in blockchain applications, particularly Ethereum.

Syntax

keccak256(message)

Arguments

  • message — The input string to hash. String

Returned value

Returns the 32-byte Keccak-256 hash of the input string as a fixed-length string. FixedString(32)

Examples

Usage example

SELECT hex(keccak256('hello'))

┌─hex(keccak256('hello'))──────────────────────────────────────────┐
│ 1C8AFF950685C2ED4BC3174F3472287B56D9517B9C948127319A09A7A36DEAC8 │
└──────────────────────────────────────────────────────────────────┘

kostikConsistentHash​

Introduced in: v22.6

An O(1) time and space consistent hash algorithm by Konstantin 'Kostik' Oblakov. Only efficient with n <= 32768.

Syntax

kostikConsistentHash(input, n)

Arguments

  • input — An integer key. UInt64
  • n — The number of buckets. UInt16

Returned value

Returns the computed hash value. UInt16

Examples

Usage example

SELECT kostikConsistentHash(16045690984833335023, 2);

┌─kostikConsistentHash(16045690984833335023, 2)─┐
│                                             1 │
└───────────────────────────────────────────────┘

metroHash64​

Introduced in: v1.1

Produces a 64-bit MetroHash hash value.

Note

The calculated hash values may be equal for the same input values of different argument types. This affects for example integer types of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data.

Syntax

metroHash64(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns the computed hash of the input arguments. UInt64

Examples

Usage example

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

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

murmurHash2_32​

Introduced in: v18.5

Computes the MurmurHash2 hash of the input value.

Note

The calculated hash values may be equal for the same input values of different argument types. This affects for example integer types of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data.

Syntax

murmurHash2_32(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns the computed hash value of the input arguments. UInt32

Examples

Usage example

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

┌─MurmurHash2─┬─type───┐
│  3681770635 │ UInt32 │
└─────────────┴────────┘

murmurHash2_64​

Introduced in: v18.10

Computes the MurmurHash2 hash of the input value.

Note

The calculated hash values may be equal for the same input values of different argument types. This affects for example integer types of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data.

Syntax

murmurHash2_64(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns the computed hash of the input arguments. UInt64

Examples

Usage 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───┐
│ 11832096901709403633 │ UInt64 │
└──────────────────────┴────────┘

murmurHash3_128​

Introduced in: v18.10

Computes the 128-bit MurmurHash3 hash of the input value.

Syntax

murmurHash3_128(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns the computed 128-bit MurmurHash3 hash value of the input arguments. FixedString(16)

Examples

Usage example

SELECT hex(murmurHash3_128('foo', 'foo', 'foo'));

┌─hex(murmurHash3_128('foo', 'foo', 'foo'))─┐
│ F8F7AD9B6CD4CF117A71E277E2EC2931          │
└───────────────────────────────────────────┘

murmurHash3_32​

Introduced in: v18.10

Produces a MurmurHash3 hash value.

Note

The calculated hash values may be equal for the same input values of different argument types. This affects for example integer types of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data.

Syntax

murmurHash3_32(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns the computed hash value of the input arguments. UInt32

Examples

Usage example

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

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

murmurHash3_64​

Introduced in: v18.10

Computes the MurmurHash3 hash of the input value.

Note

The calculated hash values may be equal for the same input values of different argument types. This affects for example integer types of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data.

Syntax

murmurHash3_64(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns the computed hash value of the input arguments. UInt64

Examples

Usage example

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

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

ngramMinHash​

Introduced in: v21.1

Splits a ASCII string into n-grams of ngramsize symbols and calculates hash values for each n-gram and returns a tuple with these hashes. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. It is case sensitive.

Can be used to detect semi-duplicate strings with tupleHammingDistance. For two strings, if the returned hashes are the same for both strings, then those strings are the same.

Syntax

ngramMinHash(string[, ngramsize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • ngramsize — Optional. The size of an n-gram, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two hashes — the minimum and the maximum. Tuple

Examples

Usage example

SELECT ngramMinHash('ClickHouse') AS Tuple;

┌─Tuple──────────────────────────────────────┐
│ (18333312859352735453,9054248444481805918) │
└────────────────────────────────────────────┘

ngramMinHashArg​

Introduced in: v21.1

Splits a ASCII string into n-grams of ngramsize symbols and returns the n-grams with minimum and maximum hashes, calculated by the ngramMinHash function with the same input. It is case sensitive.

Syntax

ngramMinHashArg(string[, ngramsize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • ngramsize — Optional. The size of an n-gram, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two tuples with hashnum n-grams each. Tuple(String)

Examples

Usage example

SELECT ngramMinHashArg('ClickHouse') AS Tuple;

┌─Tuple─────────────────────────────────────────────────────────────────────────┐
│ (('ous','ick','lic','Hou','kHo','use'),('Hou','lic','ick','ous','ckH','Cli')) │
└───────────────────────────────────────────────────────────────────────────────┘

ngramMinHashArgCaseInsensitive​

Introduced in: v21.1

Splits a ASCII string into n-grams of ngramsize symbols and returns the n-grams with minimum and maximum hashes, calculated by the ngramMinHashCaseInsensitive function with the same input. It is case insensitive.

Syntax

ngramMinHashArgCaseInsensitive(string[, ngramsize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • ngramsize — Optional. The size of an n-gram, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two tuples with hashnum n-grams each. Tuple(Tuple(String))

Examples

Usage example

SELECT ngramMinHashArgCaseInsensitive('ClickHouse') AS Tuple;

┌─Tuple─────────────────────────────────────────────────────────────────────────┐
│ (('ous','ick','lic','kHo','use','Cli'),('kHo','lic','ick','ous','ckH','Hou')) │
└───────────────────────────────────────────────────────────────────────────────┘

ngramMinHashArgCaseInsensitiveUTF8​

Introduced in: v21.1

Splits a UTF-8 string into n-grams of ngramsize symbols and returns the n-grams with minimum and maximum hashes, calculated by the ngramMinHashCaseInsensitiveUTF8 function with the same input. It is case insensitive.

Syntax

ngramMinHashArgCaseInsensitiveUTF8(string[, ngramsize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • ngramsize — Optional. The size of an n-gram, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two tuples with hashnum n-grams each. Tuple(Tuple(String))

Examples

Usage example

SELECT ngramMinHashArgCaseInsensitiveUTF8('ClickHouse') AS Tuple;

┌─Tuple─────────────────────────────────────────────────────────────────────────┐
│ (('ckH','ous','ick','lic','kHo','use'),('kHo','lic','ick','ous','ckH','Hou')) │
└───────────────────────────────────────────────────────────────────────────────┘

ngramMinHashArgUTF8​

Introduced in: v21.1

Splits a UTF-8 string into n-grams of ngramsize symbols and returns the n-grams with minimum and maximum hashes, calculated by the ngramMinHashUTF8 function with the same input. It is case sensitive.

Syntax

ngramMinHashArgUTF8(string[, ngramsize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • ngramsize — Optional. The size of an n-gram, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two tuples with hashnum n-grams each. Tuple(Tuple(String))

Examples

Usage example

SELECT ngramMinHashArgUTF8('ClickHouse') AS Tuple;

┌─Tuple─────────────────────────────────────────────────────────────────────────┐
│ (('ous','ick','lic','Hou','kHo','use'),('kHo','Hou','lic','ick','ous','ckH')) │
└───────────────────────────────────────────────────────────────────────────────┘

ngramMinHashCaseInsensitive​

Introduced in: v21.1

Splits a ASCII string into n-grams of ngramsize symbols and calculates hash values for each n-gram and returns a tuple with these hashes Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. It is case insensitive.

Can be used to detect semi-duplicate strings with tupleHammingDistance. For two strings, if the returned hashes are the same for both strings, then those strings are the same.

Syntax

ngramMinHashCaseInsensitive(string[, ngramsize, hashnum])

Arguments

  • string — String. String. - ngramsize — The size of an n-gram. Optional. Possible values: any number from 1 to 25. Default value: 3. UInt8. - hashnum — The number of minimum and maximum hashes used to calculate the result. Optional. Possible values: any number from 1 to 25. Default value: 6. UInt8.

Returned value

Tuple with two hashes — the minimum and the maximum. Tuple(UInt64, UInt64). Tuple

Examples

Usage example

SELECT ngramMinHashCaseInsensitive('ClickHouse') AS Tuple;

┌─Tuple──────────────────────────────────────┐
│ (2106263556442004574,13203602793651726206) │
└────────────────────────────────────────────┘

ngramMinHashCaseInsensitiveUTF8​

Introduced in: v21.1

Splits a UTF-8 string into n-grams of ngramsize symbols and calculates hash values for each n-gram and returns a tuple with these hashes.. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. It is case insensitive.

Can be used to detect semi-duplicate strings with tupleHammingDistance. For two strings, if the returned hashes are the same for both strings, then those strings are the same.

Syntax

ngramMinHashCaseInsensitiveUTF8(string [, ngramsize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • ngramsize — Optional. The size of an n-gram, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two hashes — the minimum and the maximum. Tuple

Examples

Usage example

SELECT ngramMinHashCaseInsensitiveUTF8('ClickHouse') AS Tuple;

┌─Tuple───────────────────────────────────────┐
│ (12493625717655877135,13203602793651726206) │
└─────────────────────────────────────────────┘

ngramMinHashUTF8​

Introduced in: v21.1

Splits a UTF-8 string into n-grams of ngramsize symbols and calculates hash values for each n-gram and returns a tuple with these hashes. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. It is case sensitive.

Can be used to detect semi-duplicate strings with tupleHammingDistance. For two strings, if the returned hashes are the same for both strings, then those strings are the same.

Syntax

ngramMinHashUTF8(string[, ngramsize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • ngramsize — Optional. The size of an n-gram, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two hashes — the minimum and the maximum. Tuple

Examples

Usage example

SELECT ngramMinHashUTF8('ClickHouse') AS Tuple;

┌─Tuple──────────────────────────────────────┐
│ (18333312859352735453,6742163577938632877) │
└────────────────────────────────────────────┘

ngramSimHash​

Introduced in: v21.1

Splits a ASCII string into n-grams of ngramsize symbols and returns the n-gram simhash.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller the Hamming distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

ngramSimHash(string[, ngramsize])

Arguments

  • string — String for which to compute the case sensitive simhash. String
  • ngramsize — Optional. The size of an n-gram, any number from 1 to 25. The default value is3. UInt8

Returned value

Returns the computed hash of the input string. UInt64

Examples

Usage example

SELECT ngramSimHash('ClickHouse') AS Hash;

┌───────Hash─┐
│ 1627567969 │
└────────────┘

ngramSimHashCaseInsensitive​

Introduced in: v21.1

Splits a ASCII string into n-grams of ngramsize symbols and returns the n-gram simhash. It is case insensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller the Hamming distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

ngramSimHashCaseInsensitive(string[, ngramsize])

Arguments

  • string — String for which to compute the case insensitive simhash. String
  • ngramsize — Optional. The size of an n-gram, any number from 1 to 25. The default value is 3. UInt8

Returned value

Hash value. UInt64. UInt64

Examples

Usage example

SELECT ngramSimHashCaseInsensitive('ClickHouse') AS Hash;

┌──────Hash─┐
│ 562180645 │
└───────────┘

ngramSimHashCaseInsensitiveUTF8​

Introduced in: v21.1

Splits a UTF-8 string into n-grams of ngramsize symbols and returns the n-gram simhash. It is case insensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller is the Hamming Distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

ngramSimHashCaseInsensitiveUTF8(string[, ngramsize])

Arguments

  • string — String for which to compute the hash. String
  • ngramsize — Optional. The size of an n-gram, any number from 1 to 25. The default value is 3. UInt8

Returned value

Returns the computed hash value. UInt64

Examples

Usage example

SELECT ngramSimHashCaseInsensitiveUTF8('ClickHouse') AS Hash;

┌───────Hash─┐
│ 1636742693 │
└────────────┘

ngramSimHashUTF8​

Introduced in: v21.1

Splits a UTF-8 encoded string into n-grams of ngramsize symbols and returns the n-gram simhash. It is case sensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller the Hamming distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

ngramSimHashUTF8(string[, ngramsize])

Arguments

  • string — String for which to compute the hash. String
  • ngramsize — Optional. The size of an n-gram, any number from 1 to 25. The default value is 3. UInt8

Returned value

Returns the computed hash value. UInt64

Examples

Usage example

SELECT ngramSimHashUTF8('ClickHouse') AS Hash;

┌───────Hash─┐
│ 1628157797 │
└────────────┘

sipHash128​

Introduced in: v1.1

Like sipHash64 but produces a 128-bit hash value, i.e. the final xor-folding state is done up to 128 bits.

use sipHash128Reference for new projects

This 128-bit variant differs from the reference implementation and is weaker. This version exists because, when it was written, there was no official 128-bit extension for SipHash. New projects are advised to use sipHash128Reference.

Syntax

sipHash128(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns a 128-bit SipHash hash value. FixedString(16)

Examples

Usage example

SELECT hex(sipHash128('foo', '\x01', 3));

┌─hex(sipHash128('foo', '', 3))────┐
│ 9DE516A64A414D4B1B609415E4523F24 │
└──────────────────────────────────┘

sipHash128Keyed​

Introduced in: v23.2

Same as sipHash128 but additionally takes an explicit key argument instead of using a fixed key.

use sipHash128ReferenceKeyed for new projects

This 128-bit variant differs from the reference implementation and it's weaker. This version exists because, when it was written, there was no official 128-bit extension for SipHash. New projects should probably use sipHash128ReferenceKeyed.

Syntax

sipHash128Keyed((k0, k1), [arg1, arg2, ...])

Arguments

  • (k0, k1) — A tuple of two UInt64 values representing the key. Tuple(UInt64, UInt64)
  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

A 128-bit SipHash hash value of type FixedString(16). FixedString(16)

Examples

Usage example

SELECT hex(sipHash128Keyed((506097522914230528, 1084818905618843912),'foo', '\x01', 3));

┌─hex(sipHash128Keyed((506097522914230528, 1084818905618843912), 'foo', '', 3))─┐
│ B8467F65C8B4CFD9A5F8BD733917D9BF                                              │
└───────────────────────────────────────────────────────────────────────────────┘

sipHash128Reference​

Introduced in: v23.2

Like sipHash128 but implements the 128-bit algorithm from the original authors of SipHash.

Syntax

sipHash128Reference(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns the computed 128-bit SipHash hash value of the input arguments. FixedString(16)

Examples

Usage example

SELECT hex(sipHash128Reference('foo', '', 3));

┌─hex(sipHash128Reference('foo', '', 3))─┐
│ 4D1BE1A22D7F5933C0873E1698426260       │
└────────────────────────────────────────┘

sipHash128ReferenceKeyed​

Introduced in: v23.2

Same as sipHash128Reference but additionally takes an explicit key argument instead of using a fixed key.

Syntax

sipHash128ReferenceKeyed((k0, k1), arg1[, arg2, ...])

Arguments

  • (k0, k1) — Tuple of two values representing the key Tuple(UInt64, UInt64)
  • arg1[, arg2, ...] — A variable number of input arguments for which to compute the hash. Any

Returned value

Returns the computed 128-bit SipHash hash value of the input arguments. FixedString(16)

Examples

Usage example

SELECT hex(sipHash128Reference('foo', '', 3));

┌─hex(sipHash128Reference('foo', '', 3))─┐
│ 4D1BE1A22D7F5933C0873E1698426260       │
└────────────────────────────────────────┘

sipHash64​

Introduced in: v1.1

Produces a 64-bit SipHash hash value.

This is a cryptographic hash function. It works at least three times faster than the MD5 hash function.

The function interprets all the input parameters as strings and calculates the hash value for each of them. It then combines the hashes using the following algorithm:

  1. The first and the second hash value are concatenated to an array which is hashed.
  2. The previously calculated hash value and the hash of the third input parameter are hashed in a similar way.
  3. This calculation is repeated for all remaining hash values of the original input.
Note

the calculated hash values may be equal for the same input values of different argument types. This affects for example integer types of different size, named and unnamed Tuple with the same data, Map and the corresponding Array(Tuple(key, value)) type with the same data.

Syntax

sipHash64(arg1[, arg2, ...])

Arguments

  • arg1[, arg2, ...] — A variable number of input arguments. Any

Returned value

Returns a computed hash value of the input arguments. UInt64

Examples

Usage example

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

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

sipHash64Keyed​

Introduced in: v23.2

Like sipHash64 but additionally takes an explicit key argument instead of using a fixed key.

Syntax

sipHash64Keyed((k0, k1), arg1[,arg2, ...])

Arguments

  • (k0, k1) — A tuple of two values representing the key. Tuple(UInt64, UInt64)
  • arg1[,arg2, ...] — A variable number of input arguments. Any

Returned value

Returns the computed hash of the input values. UInt64

Examples

Usage example

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

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

wordShingleMinHash​

Introduced in: v21.1

Splits a ASCII string into parts (shingles) of shinglesize words, calculates hash values for each word shingle and returns a tuple with these hashes. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. It is case sensitive.

Can be used to detect semi-duplicate strings with tupleHammingDistance. For two strings, if the returned hashes are the same for both strings, then those strings are the same.

Syntax

wordShingleMinHash(string[, shinglesize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two hashes — the minimum and the maximum. Tuple(UInt64, UInt64)

Examples

Usage example

SELECT wordShingleMinHash('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Tuple;

┌─Tuple──────────────────────────────────────┐
│ (16452112859864147620,5844417301642981317) │
└────────────────────────────────────────────┘

wordShingleMinHashArg​

Introduced in: v1.1

Splits a ASCII string into parts (shingles) of shinglesize words each and returns the shingles with minimum and maximum word hashes, calculated by the wordShingleMinHash function with the same input. It is case sensitive.

Syntax

wordShingleMinHashArg(string[, shinglesize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two tuples with hashnum word shingles each. Tuple(Tuple(String))

Examples

Usage example

SELECT wordShingleMinHashArg('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).', 1, 3) AS Tuple;

┌─Tuple─────────────────────────────────────────────────────────────────┐
│ (('OLAP','database','analytical'),('online','oriented','processing')) │
└───────────────────────────────────────────────────────────────────────┘

wordShingleMinHashArgCaseInsensitive​

Introduced in: v21.1

Splits a ASCII string into parts (shingles) of shinglesize words each and returns the shingles with minimum and maximum word hashes, calculated by the wordShingleMinHashCaseInsensitive function with the same input. It is case insensitive.

Syntax

wordShingleMinHashArgCaseInsensitive(string[, shinglesize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two tuples with hashnum word shingles each. Tuple(Tuple(String))

Examples

Usage example

SELECT wordShingleMinHashArgCaseInsensitive('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).', 1, 3) AS Tuple;

┌─Tuple──────────────────────────────────────────────────────────────────┐
│ (('queries','database','analytical'),('oriented','processing','DBMS')) │
└────────────────────────────────────────────────────────────────────────┘

wordShingleMinHashArgCaseInsensitiveUTF8​

Introduced in: v21.1

Splits a UTF-8 string into parts (shingles) of shinglesize words each and returns the shingles with minimum and maximum word hashes, calculated by the wordShingleMinHashCaseInsensitiveUTF8 function with the same input. It is case insensitive.

Syntax

wordShingleMinHashArgCaseInsensitiveUTF8(string[, shinglesize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two tuples with hashnum word shingles each. Tuple(Tuple(String))

Examples

Usage example

SELECT wordShingleMinHashArgCaseInsensitiveUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).', 1, 3) AS Tuple;

┌─Tuple──────────────────────────────────────────────────────────────────┐
│ (('queries','database','analytical'),('oriented','processing','DBMS')) │
└────────────────────────────────────────────────────────────────────────┘

wordShingleMinHashArgUTF8​

Introduced in: v21.1

Splits a UTF-8 string into parts (shingles) of shinglesize words each and returns the shingles with minimum and maximum word hashes, calculated by the wordShingleMinHashUTF8 function with the same input. It is case sensitive.

Syntax

wordShingleMinHashArgUTF8(string[, shinglesize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two tuples with hashnum word shingles each. Tuple(Tuple(String))

Examples

Usage example

SELECT wordShingleMinHashArgUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).', 1, 3) AS Tuple;

┌─Tuple─────────────────────────────────────────────────────────────────┐
│ (('OLAP','database','analytical'),('online','oriented','processing')) │
└───────────────────────────────────────────────────────────────────────┘

wordShingleMinHashCaseInsensitive​

Introduced in: v21.1

Splits a ASCII string into parts (shingles) of shinglesize words, calculates hash values for each word shingle and returns a tuple with these hashes. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. It is case insensitive.

Can be used to detect semi-duplicate strings with tupleHammingDistance. For two strings, if the returned hashes are the same for both strings, then those strings are the same.

Syntax

wordShingleMinHashCaseInsensitive(string[, shinglesize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two hashes — the minimum and the maximum. Tuple(UInt64, UInt64)

Examples

Usage example

SELECT wordShingleMinHashCaseInsensitive('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Tuple;

┌─Tuple─────────────────────────────────────┐
│ (3065874883688416519,1634050779997673240) │
└───────────────────────────────────────────┘

wordShingleMinHashCaseInsensitiveUTF8​

Introduced in: v21.1

Splits a UTF-8 string into parts (shingles) of shinglesize words, calculates hash values for each word shingle and returns a tuple with these hashes. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. It is case insensitive.

Can be used to detect semi-duplicate strings with tupleHammingDistance. For two strings, if the returned hashes are the same for both strings, then those strings are the same.

Syntax

wordShingleMinHashCaseInsensitiveUTF8(string[, shinglesize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two hashes — the minimum and the maximum. Tuple(UInt64, UInt64)

Examples

Usage example

SELECT wordShingleMinHashCaseInsensitiveUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Tuple;

┌─Tuple─────────────────────────────────────┐
│ (3065874883688416519,1634050779997673240) │
└───────────────────────────────────────────┘

wordShingleMinHashUTF8​

Introduced in: v21.1

Splits a UTF-8 string into parts (shingles) of shinglesize words, calculates hash values for each word shingle and returns a tuple with these hashes. Uses hashnum minimum hashes to calculate the minimum hash and hashnum maximum hashes to calculate the maximum hash. It is case sensitive.

Can be used to detect semi-duplicate strings with tupleHammingDistance. For two strings, if the returned hashes are the same for both strings, then those strings are the same.

Syntax

wordShingleMinHashUTF8(string[, shinglesize, hashnum])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8
  • hashnum — Optional. The number of minimum and maximum hashes used to calculate the result, any number from 1 to 25. The default value is 6. UInt8

Returned value

Returns a tuple with two hashes — the minimum and the maximum. Tuple(UInt64, UInt64)

Examples

Usage example

SELECT wordShingleMinHashUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Tuple;

┌─Tuple──────────────────────────────────────┐
│ (16452112859864147620,5844417301642981317) │
└────────────────────────────────────────────┘

wordShingleSimHash​

Introduced in: v21.1

Splits a ASCII string into parts (shingles) of shinglesize words and returns the word shingle simhash. Is is case sensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller the Hamming distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

wordShingleSimHash(string[, shinglesize])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8

Returned value

Returns the computed hash value. UInt64

Examples

Usage example

SELECT wordShingleSimHash('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Hash;

┌───────Hash─┐
│ 2328277067 │
└────────────┘

wordShingleSimHashCaseInsensitive​

Introduced in: v21.1

Splits a ASCII string into parts (shingles) of shinglesize words and returns the word shingle simhash. It is case insensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller the Hamming distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

wordShingleSimHashCaseInsensitive(string[, shinglesize])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8

Returned value

Returns the computed hash value. UInt64

Examples

Usage example

SELECT wordShingleSimHashCaseInsensitive('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Hash;

┌───────Hash─┐
│ 2194812424 │
└────────────┘

wordShingleSimHashCaseInsensitiveUTF8​

Introduced in: v1.1

Splits a UTF-8 encoded string into parts (shingles) of shinglesize words and returns the word shingle simhash. It is case insensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller the Hamming Distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

wordShingleSimHashCaseInsensitiveUTF8(string[, shinglesize])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8

Returned value

Returns the computed hash value. UInt64

Examples

Usage example

SELECT wordShingleSimHashCaseInsensitiveUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Hash;

┌───────Hash─┐
│ 2194812424 │
└────────────┘

wordShingleSimHashUTF8​

Introduced in: v21.1

Splits a UTF-8 string into parts (shingles) of shinglesize words and returns the word shingle simhash. It is case sensitive.

Can be used for detection of semi-duplicate strings with bitHammingDistance. The smaller the Hamming distance of the calculated simhashes of two strings, the more likely these strings are the same.

Syntax

wordShingleSimHashUTF8(string[, shinglesize])

Arguments

  • string — String for which to compute the hash. String
  • shinglesize — Optional. The size of a word shingle, any number from 1 to 25. The default value is 3. UInt8

Returned value

Returns the computed hash value. UInt64

Examples

Usage example

SELECT wordShingleSimHashUTF8('ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP).') AS Hash;

┌───────Hash─┐
│ 2328277067 │
└────────────┘

wyHash64​

Introduced in: v22.7

Computes a 64-bit wyHash64 hash value.

Syntax

wyHash64(arg)

Arguments

  • arg — String argument for which to compute the hash. String

Returned value

Returns the computed 64-bit hash value UInt64

Examples

Usage example

SELECT wyHash64('ClickHouse') AS Hash;

12336419557878201794

xxHash32​

Introduced in: v20.1

Calculates a xxHash from a string.

For the 64-bit version see xxHash64

Syntax

xxHash32(arg)

Arguments

  • arg — Input string to hash. String

Returned value

Returns the computed 32-bit hash of the input string. UInt32

Examples

Usage example

SELECT xxHash32('Hello, world!');

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

xxHash64​

Introduced in: v20.1

Calculates a xxHash from a string.

For the 32-bit version see xxHash32

Syntax

xxHash64(arg)

Arguments

  • arg — Input string to hash. String

Returned value

Returns the computed 64-bit hash of the input string. UInt64

Examples

Usage example

SELECT xxHash64('Hello, world!');

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

xxh3​

Introduced in: v22.12

Computes a XXH3 64-bit hash value.

Syntax

xxh3(expr)

Arguments

  • expr — A list of expressions of any data type. Any

Returned value

Returns the computed 64-bit xxh3 hash value UInt64

Examples

Usage example

SELECT xxh3('ClickHouse')

18009318874338624809