Matching other systems functions in the Hashfuncs DuckDB extension
Sample data for the examples
Each browser example starts with these sample tables; sample files are shared across examples on this page. Run this setup first when trying the examples in your own DuckDB session.
-- Fixtures for the hashfuncs cookbook / technical-details examples.
--
-- The hashfuncs extension itself is pure CPU (no catalog, no network),
-- so these fixtures only exist to satisfy the *table references* the
-- documentation makes ("FROM events", "FROM users", ...). The numbers
-- chosen are tiny and deterministic so re-running the suite is stable.
--
-- Idempotent: every CREATE uses OR REPLACE.
-- The bitfilters cookbook section pairs hashfuncs with the bitfilters
-- extension. Load it best-effort so xor8_filter / xor8_filter_contains
-- resolve at parse time. If the community binary isn't available on the
-- current platform we still want the rest of the fixtures to apply.
INSTALL bitfilters FROM query_farm;
LOAD bitfilters FROM query_farm;
-- cookbook.mdx — "Hash partitioning" and "Independent hashes via seeds"
CREATE OR REPLACE TABLE users AS
SELECT i AS user_id
FROM range(0, 8) t(i);
-- cookbook.mdx — "Cross-system routing (Cassandra / Spark)"
CREATE OR REPLACE TABLE rows_to_route AS
SELECT 'k' || i::VARCHAR AS key
FROM range(0, 4) t(i);
-- cookbook.mdx — "String cache keys"
CREATE OR REPLACE TABLE query_log AS
SELECT i AS user_id,
'select ' || i::VARCHAR AS query_text
FROM range(0, 4) t(i);
-- cookbook.mdx — "Bloom / XOR / Cuckoo filter input"
CREATE OR REPLACE TABLE allowed_users AS
SELECT 'user' || i::VARCHAR || '@example.com' AS email
FROM range(0, 4) t(i);
-- cookbook.mdx — second bitfilters block reads from `filter_table f`
-- (a one-row table holding a pre-built xor8 filter).
CREATE OR REPLACE TABLE filter_table AS
SELECT xor8_filter(xxh3_64(email)) AS filter
FROM allowed_users;
-- cookbook.mdx — "Independent hashes via seeds" uses `FROM keys`
CREATE OR REPLACE TABLE keys AS
SELECT 'k' || i::VARCHAR AS key
FROM range(0, 4) t(i);
-- cookbook.mdx + technical-details.mdx — "events" with event_id, payload
-- Used by sharding / dedup / "magic moment" examples.
CREATE OR REPLACE TABLE events AS
SELECT i AS event_id,
'evt-' || i::VARCHAR AS payload,
'user' || i::VARCHAR || '@example.com' AS email
FROM range(0, 8) t(i);
-- technical-details.mdx — "double-hash with two seeds" reads `FROM data`
CREATE OR REPLACE TABLE data AS
SELECT 'k' || i::VARCHAR AS key
FROM range(0, 4) t(i);
Function category
Matching other systems
8 functionsFunctions that reproduce another system's hash exactly — same algorithm, same byte encoding, same seed and same signed or unsigned result — so DuckDB can compute keys, buckets and partitions that line up with BigQuery, Apache Spark, Apache Kafka, Apache Iceberg and ClickHouse. Each is tested against values produced by the real system.
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
value
|
Type
VARCHAR | BLOB
2 concrete typesBLOBVARCHAR
|
Mode Positional | Description |
Description
ClickHouse's cityHash64 of a String: CityHash64 from CityHash v1.0.2, the version ClickHouse ships, as UBIGINT. Accepts VARCHAR or BLOB. ClickHouse hashes numbers and multiple arguments differently, so only string and binary inputs are supported. For ClickHouse's sipHash64 of a String, use siphash24 without a key.
SELECT clickhouse_cityhash64('hello world');
Output
| clickhouse_cityhash64('hello world') |
|---|
| 12386028635079221413 |
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
value
|
Type
VARCHAR | BLOB
2 concrete typesBLOBVARCHAR
|
Mode Positional | Description |
Description
FarmHash Fingerprint64 as a signed BIGINT — the same value as BigQuery's FARM_FINGERPRINT for the same STRING or BYTES. Accepts VARCHAR or BLOB.
Use it to reproduce BigQuery fingerprints, sampling splits (MOD(ABS(FARM_FINGERPRINT(id)), 10)) and keys in DuckDB.
SELECT farm_fingerprint('1footrue') AS row_fingerprint;
Output
| row_fingerprint |
|---|
| -1541654101129638711 |
SELECT abs(farm_fingerprint('user-42')) % 10 AS bucket;
Output
| bucket |
|---|
| 9 |
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
value
|
Type
ANY
|
Mode Positional | Description |
Argument
buckets
|
Type
BIGINT
|
Mode Positional | Description |
Description
The partition Apache Iceberg's bucket[N] transform assigns a value: (murmur3_x86_32(value) & Integer.MAX_VALUE) % N, using the Iceberg spec's encoding of each type. Use it to predict which bucket partition a row lands in, prune files by bucket, or write data partitioned the way an Iceberg table expects.
Accepts the types Iceberg can bucket: integers up to BIGINT (every width hashes alike), DECIMAL, DATE, TIME, TIMESTAMP at any precision, TIMESTAMPTZ, VARCHAR, BLOB and UUID. BOOLEAN, FLOAT, DOUBLE and other types are rejected, as Iceberg rejects them. A NULL value returns NULL (Iceberg's null partition). buckets must be between 1 and 2147483647.
SELECT iceberg_bucket('iceberg', 16);
Output
| iceberg_bucket('iceberg', 16) |
|---|
| 9 |
SELECT iceberg_bucket(34, 16), iceberg_bucket(34::BIGINT, 16);
Output
| iceberg_bucket(34, 16) | iceberg_bucket(CAST(34 AS BIGINT), 16) |
|---|---|
| 3 | 3 |
Related functions
- iceberg_hash() — Iceberg's 32-bit bucket hash (spec Appendix B) as a signed `INTEGER`: MurmurHash3 x86_32 with seed 0 over Iceberg's encoding of the value — integers as 8-byte little-endian longs, `DECIMAL` as the minimal big-endian bytes of its unscaled value, dates as days, times and timestamps as microseconds (nanosecond timestamps floored), strings as UTF-8 and UUIDs in big-endian order
- spark_hash() — Spark's [`hash()`](https://spark
- jump_hash() — [Jump consistent hash](https://arxiv
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
value
|
Type
ANY
|
Mode Positional | Description |
Description
Iceberg's 32-bit bucket hash (spec Appendix B) as a signed INTEGER: MurmurHash3 x86_32 with seed 0 over Iceberg's encoding of the value — integers as 8-byte little-endian longs, DECIMAL as the minimal big-endian bytes of its unscaled value, dates as days, times and timestamps as microseconds (nanosecond timestamps floored), strings as UTF-8 and UUIDs in big-endian order. iceberg_bucket is this hash's low 31 bits modulo the bucket count.
SELECT iceberg_hash(34), iceberg_hash('iceberg'), iceberg_hash(14.20::DECIMAL(4,2));
Output
| iceberg_hash(34) | iceberg_hash('iceberg') | iceberg_hash(CAST(14.20 AS DECIMAL(4, 2))) |
|---|---|---|
| 2017239379 | 1210000089 | -500754589 |
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
value
|
Type
BLOB | VARCHAR
2 concrete typesBLOBVARCHAR
|
Mode Positional | Description |
Description
Kafka's murmur2 hash of a serialized record key (VARCHAR as UTF-8 bytes, or BLOB), as a signed INTEGER — the hash Kafka's default partitioner applies to keyed records.
SELECT kafka_murmur2('order-42');
Output
| kafka_murmur2('order-42') |
|---|
| 501153024 |
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
key
|
Type
VARCHAR | BLOB
2 concrete typesBLOBVARCHAR
|
Mode Positional | Description |
Argument
partitions
|
Type
BIGINT
|
Mode Positional | Description |
Description
The partition Kafka's default partitioner assigns a keyed record: toPositive(murmur2(key)) % partitions. Use it to predict which partition a key lands on, or to co-partition DuckDB output with a topic.
The key is the record key's serialized bytes — a VARCHAR is hashed as UTF-8, which matches Kafka's StringSerializer. A NULL key returns NULL, because Kafka doesn't hash records without a key. partitions must be between 1 and 2147483647.
SELECT kafka_partition('order-42', 12);
Output
| kafka_partition('order-42', 12) |
|---|
| 0 |
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
value
|
Type
ANY
|
Mode Positional | Description |
Argument
varargs
|
Type
ANY
|
Mode Positional | Description Varargs |
Description
Spark's hash(): Murmur3 x86_32 with seed 42, returning a signed INTEGER. Takes one or more values; each value's hash seeds the next and NULLs are skipped, exactly as Spark does. Spark's string and binary hashing mixes trailing bytes non-standardly, so this differs from murmurhash3_32.
Values are hashed by Spark's type rules: BOOLEAN, TINYINT/SMALLINT/INTEGER/DATE as int, BIGINT/TIMESTAMP as long, -0.0 folded into 0.0, DECIMAL by unscaled value, and LIST/ARRAY/STRUCT element by element. Types Spark doesn't have (unsigned integers, UUID, MAP, ...) are rejected. Spark's bucket id is pmod(hash(cols), n).
SELECT spark_hash('Spark', [123], 2);
Output
| spark_hash('Spark', list_value(123), 2) |
|---|
| -1321691492 |
SELECT ((spark_hash('user-42') % 16) + 16) % 16 AS bucket;
Output
| bucket |
|---|
| 8 |
Signature
Arguments (Positional)
| Argument | Type | Mode | Description |
|---|---|---|---|
Argument
value
|
Type
ANY
|
Mode Positional | Description |
Argument
varargs
|
Type
ANY
|
Mode Positional | Description Varargs |
Description
Spark's xxhash64(): XXH64 with seed 42, returning a signed BIGINT, with the same multi-value seeding, NULL skipping and type rules as spark_hash.
SELECT spark_xxhash64('Spark', [123], 2);
Output
| spark_xxhash64('Spark', list_value(123), 2) |
|---|
| 5602566077635097486 |