Skip to content

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 functions

clickhouse_cityhash64

Scalar function Matching other systems
Signature
clickhouse_cityhash64(value: VARCHAR | BLOB) → UBIGINT
Arguments (Positional)
Argument value Type VARCHAR | BLOB
2 concrete types
BLOBVARCHAR
Mode Positional Description
Description
1 Hash a string
SELECT clickhouse_cityhash64('hello world');

Output

clickhouse_cityhash64('hello world')
12386028635079221413

farm_fingerprint

Scalar function Matching other systems
Signature
farm_fingerprint(value: VARCHAR | BLOB) → BIGINT
Arguments (Positional)
Argument value Type VARCHAR | BLOB
2 concrete types
BLOBVARCHAR
Mode Positional Description
Description
1 BigQuery's documented example
SELECT farm_fingerprint('1footrue') AS row_fingerprint;

Output

row_fingerprint
-1541654101129638711
2 Same 10% sample split as BigQuery
SELECT abs(farm_fingerprint('user-42')) % 10 AS bucket;

Output

bucket
9

iceberg_bucket

Scalar function Matching other systems
Signature
iceberg_bucket(value: ANY, buckets: BIGINT) → INTEGER
Arguments (Positional)
Argument value Type ANY Mode Positional Description
Argument buckets Type BIGINT Mode Positional Description
Description
1 Bucket of a string in a bucket[16] partition
SELECT iceberg_bucket('iceberg', 16);

Output

iceberg_bucket('iceberg', 16)
9
2 int and long values land in the same bucket
SELECT iceberg_bucket(34, 16), iceberg_bucket(34::BIGINT, 16);

Output

iceberg_bucket(34, 16) iceberg_bucket(CAST(34 AS BIGINT), 16)
3 3

iceberg_hash

Scalar function Matching other systems
Signature
iceberg_hash(value: ANY) → INTEGER
Arguments (Positional)
Argument value Type ANY Mode Positional Description
Description
1 The spec's test values
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

kafka_murmur2

Scalar function Matching other systems
Signature
kafka_murmur2(value: BLOB | VARCHAR) → INTEGER
Arguments (Positional)
Argument value Type BLOB | VARCHAR
2 concrete types
BLOBVARCHAR
Mode Positional Description
Description
1 Hash a record key
SELECT kafka_murmur2('order-42');

Output

kafka_murmur2('order-42')
501153024

kafka_partition

Scalar function Matching other systems
Signature
kafka_partition(key: VARCHAR | BLOB, partitions: BIGINT) → INTEGER
Arguments (Positional)
Argument key Type VARCHAR | BLOB
2 concrete types
BLOBVARCHAR
Mode Positional Description
Argument partitions Type BIGINT Mode Positional Description
Description
1 Partition for a key on a 12-partition topic
SELECT kafka_partition('order-42', 12);

Output

kafka_partition('order-42', 12)
0

spark_hash

Scalar function Matching other systems
Signature
spark_hash(value: ANY, varargs: ANY) → INTEGER
Arguments (Positional)
Argument value Type ANY Mode Positional Description
Argument varargs Type ANY Mode Positional Description Varargs
Description
1 Spark's documented example
SELECT spark_hash('Spark', [123], 2);

Output

spark_hash('Spark', list_value(123), 2)
-1321691492
2 Spark bucket id for 16 buckets
SELECT ((spark_hash('user-42') % 16) + 16) % 16 AS bucket;

Output

bucket
8

spark_xxhash64

Scalar function Matching other systems
Signature
spark_xxhash64(value: ANY, varargs: ANY) → BIGINT
Arguments (Positional)
Argument value Type ANY Mode Positional Description
Argument varargs Type ANY Mode Positional Description Varargs
Description
1 Spark's documented example
SELECT spark_xxhash64('Spark', [123], 2);

Output

spark_xxhash64('Spark', list_value(123), 2)
5602566077635097486