XXHASH64

Compute a non-cryptographic 64-bit xxHash of one or more expressions.

Category: hashReturns: BIGINTDialect: Standard

Syntax

XXHASH64(expr1, expr2, ...)

Description

## Overview Computes a 64-bit xxHash of the input expressions and returns the result as a signed BIGINT. When multiple arguments are provided they are combined into a single 64-bit hash, making XXHASH64 a good choice for row-level fingerprints and composite keys. xxHash is designed for speed with strong distribution properties; it is routinely used for bucketing, partitioning, and deduplication at scale. It is not cryptographically secure. ## Behavior - Returns BIGINT (signed 64-bit). - NULL inputs produce a deterministic hash. - Deterministic for the same input arguments within an engine version. - Multi-argument calls combine inputs; the ordering of arguments affects the output. ## Algorithm - xxHash64 (also known as XXH64) by Yann Collet. - Output width: 64 bits. - Very high throughput on modern CPUs; several GB/s on typical hardware. ## Compatibility - xxHash64 is a well-specified algorithm; implementations that use the same seed produce matching output. However, seeds vary across SQL engines: XXHASH64 values from different engines are not guaranteed to match even for identical inputs. - Not part of any cryptographic standard; do not use for security.

Parameters

Name	Type	Description
`expr`		Specifies one or more expressions of any supported data type. Multiple expressions are combined into a single 64-bit hash. Both NULL and non-NULL values participate; NULL produces a deterministic hash.

Examples

-- Hash a single value
SELECT XXHASH64('hello') AS h;

-- Hash an integer
SELECT XXHASH64(42) AS h;

-- Hash a composite key for row fingerprinting
SELECT XXHASH64(col1, col2, col3) AS row_hash
FROM (VALUES ('a', 1, TRUE), ('b', 2, FALSE)) AS t(col1, col2, col3);

-- NULL is hashed deterministically
SELECT XXHASH64(NULL) AS h;

-- Realistic: distribute rows into 64 buckets
SELECT ABS(XXHASH64(customer_id)) % 64 AS shard, COUNT(*)
FROM ecommerce.sales.orders
GROUP BY shard;

-- Realistic: deterministic dedup hash for streaming events
SELECT event_id, XXHASH64(source_system, payload_raw) AS dedup_key
FROM analytics.telemetry.raw_events;

Pitfalls

Not cryptographically secure. Do not use for signatures, tamper detection, or password hashing.
Different engines may use different seeds. XXHASH64 output is not guaranteed to match across SQL engines even for the same input.
Argument order matters: XXHASH64(a, b) and XXHASH64(b, a) produce different hashes.
NULL is hashed deterministically, not as NULL. Test for NULL explicitly when NULL semantics are required.
Output is signed BIGINT; modulo-bucketing can produce negative buckets unless you wrap with ABS() or CAST AS unsigned.