HyperLogLog++ functions

HyperLogLog++ functions are approximate aggregate functions that use minimal memory to quickly remove duplicates from large datasets and accelerate queries.

Background

HyperLogLog (HLL) is an approximate deduplication algorithm suited for low-precision scenarios such as PV/UV statistics. It serves as a lightweight alternative to COUNT(DISTINCT). HLL stores data in a fixed-size structure called a sketch. Memory stays constant regardless of data volume, and each new value requires only one hash calculation. In big data scenarios, the deduplication error is typically within 1% or less.

For basic approximate deduplication, MaxCompute provides the APPROX_DISTINCT aggregate function. When you need to store or reuse intermediate sketches rather than just obtain final counts, MaxCompute offers HyperLogLog++ functions. These functions use an optimized algorithm that reduces memory and improves estimation accuracy.

Typical use cases:

Incremental deduplication over time: Persist the daily HLL sketch, then process only new data and merge it with the historical sketch. This avoids rescanning all historical data.
Cross-column deduplication: Build and save a sketch for each column, then merge them directly. This reuses deduplication results and reduces computing overhead.

Supported functions

MaxCompute SQL supports these HyperLogLog++ functions.

Function	Description
HLL_COUNT_INIT	Aggregates values of the same type into a new HLL++ sketch.
HLL_COUNT_MERGE_PARTIAL	Merges multiple HLL++ sketches of the same storage class into a new sketch.
HLL_COUNT_EXTRACT	Calculates the cardinality estimation from an HLL++ sketch.
HLL_COUNT_MERGE	Merges multiple HLL++ sketches of the same storage class into a new sketch and returns the cardinality estimation of the merged sketch.

Usage notes

The BINARY data used by the HLL_COUNT_EXTRACT, HLL_COUNT_MERGE, and HLL_COUNT_MERGE_PARTIAL functions must be generated by the HLL_COUNT_INIT function. Data from other systems or methods cannot be used.