Skip to content
DuckDB Extensions All extensions Apache DataSketches

HLL functions in the Apache DataSketches DuckDB extension

Function category

HLL

9 functions

[HyperLogLog](https://datasketches.apache.org/docs/HLL/HllSketches.html) distinct-counting sketch — the industry standard. Fast serialize/deserialize and broad cross-system compatibility. Choose this when speed and interop matter more than storage.

datasketch_hll

Aggregate function HLL
Signature
datasketch_hll(col0: INTEGER, col1: TINYINT | INTEGER | UTINYINT | …) → sketch_hll
Arguments (Positional)
Argument col0 Type INTEGER Mode Positional Description
Argument col1 Type TINYINT | INTEGER | UTINYINT | …
12 concrete types
BIGINTBLOBDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINTVARCHAR
Mode Positional Description
Description

Aggregate input values into a HyperLogLog sketch for distinct counting. Returns a sketch_hll BLOB you can persist, ship between processes, or merge later. The first argument is lg_k — base-2 log of the number of buckets — which controls the size/accuracy trade-off. lg_k = 12 (4096 buckets, ~4 KB sketch, ~1.6% standard error) is a common default; raise it for tighter bounds, lower it for smaller sketches.

1 Approximate distinct users (lg_k = 12, ~1.6% error) 
SELECT datasketch_hll_estimate(datasketch_hll(12, user_id)) AS distinct_users
FROM events;
2 Build per-day sketches and persist them 
CREATE TABLE daily_uniques (day DATE, hll sketch_hll);
INSERT INTO daily_uniques
SELECT date_trunc('day', ts) AS day,
       datasketch_hll(12, user_id) AS hll
FROM events
GROUP BY 1;
Related functions

datasketch_hll_describe

Scalar function HLL
Signature
datasketch_hll_describe( col0: sketch_hll, col1: BOOLEAN, col2: BOOLEAN ) → VARCHAR
Arguments (Positional)
Argument col0 Type sketch_hll Mode Positional Description
Argument col1 Type BOOLEAN Mode Positional Description
Argument col2 Type BOOLEAN Mode Positional Description
Description

Return a string representation of the sketch

1 Example 1 
SELECT datasketch_hll_describe(sketch, include_summary, include_detail);
Related functions

datasketch_hll_estimate

Scalar function HLL
Signature
datasketch_hll_estimate(col0: sketch_hll) → DOUBLE
Arguments (Positional)
Argument col0 Type sketch_hll Mode Positional Description
Description

Read the estimated distinct count from an HLL sketch. Returns a DOUBLE — the cardinality estimate.

1 Example 1 
SELECT datasketch_hll_estimate(sketch);
Related functions

datasketch_hll_is_compact

Scalar function HLL
Signature
datasketch_hll_is_compact(col0: sketch_hll) → BOOLEAN
Arguments (Positional)
Argument col0 Type sketch_hll Mode Positional Description
Description

Return whether the sketch is in compact form

1 Example 1 
SELECT datasketch_hll_is_compact(sketch);
Related functions

datasketch_hll_is_empty

Scalar function HLL
Signature
datasketch_hll_is_empty(col0: sketch_hll) → BOOLEAN
Arguments (Positional)
Argument col0 Type sketch_hll Mode Positional Description
Description

Return a boolean indicating if the sketch is empty

1 Example 1 
SELECT datasketch_hll_is_empty(sketch);
Related functions

datasketch_hll_lg_config_k

Scalar function HLL
Signature
datasketch_hll_lg_config_k(col0: sketch_hll) → UTINYINT
Arguments (Positional)
Argument col0 Type sketch_hll Mode Positional Description
Description

Return the value of log base 2 K for this sketch

1 Example 1 
SELECT datasketch_hll_lg_config_k(sketch);
Related functions

datasketch_hll_lower_bound

Scalar function HLL
Signature
datasketch_hll_lower_bound(col0: sketch_hll, col1: UTINYINT) → DOUBLE
Arguments (Positional)
Argument col0 Type sketch_hll Mode Positional Description
Argument col1 Type UTINYINT Mode Positional Description
Description

Lower bound of the HLL distinct-count estimate at a given number of standard deviations.

1 Example 1 
SELECT datasketch_hll_lower_bound(sketch, std_dev);
Related functions

datasketch_hll_union

Aggregate function HLL
Signature
datasketch_hll_union(col0: INTEGER, col1: sketch_hll) → sketch_hll
Arguments (Positional)
Argument col0 Type INTEGER Mode Positional Description
Argument col1 Type sketch_hll Mode Positional Description
Description

Merge multiple HLL sketches into one. Aggregate over a column of sketch_hll BLOBs to roll per-partition or per-day sketches up to whatever window you need — without rescanning the underlying rows. The leading lg_k argument is the precision of the output sketch.

1 Rolling 7-day distinct users from per-day sketches 
SELECT datasketch_hll_estimate(datasketch_hll_union(12, hll)) AS uniques_last_7d
FROM daily_uniques
WHERE day >= CURRENT_DATE - 7;
Related functions

datasketch_hll_upper_bound

Scalar function HLL
Signature
datasketch_hll_upper_bound(col0: sketch_hll, col1: UTINYINT) → DOUBLE
Arguments (Positional)
Argument col0 Type sketch_hll Mode Positional Description
Argument col1 Type UTINYINT Mode Positional Description
Description

Upper bound of the HLL distinct-count estimate at a given number of standard deviations.

1 Example 1 
SELECT datasketch_hll_upper_bound(sketch, std_dev);
Related functions