HyperLogLog++ functions are approximate aggregate functions. When the data volume is large, they can quickly deduplicate data by using a small amount of memory, accelerating queries. This topic describes HyperLogLog++ functions.
Background
HyperLogLog (HLL for short) is an efficient approximate deduplication algorithm. It is suitable for scenarios where high precision is not required, such as page view (PV) and unique visitor (UV) statistics, and can serve as a lightweight alternative to COUNT(DISTINCT). Unlike exact deduplication methods such as Bitmap, HLL uses a fixed-size data structure (sketch) internally, so its memory usage does not increase with the data volume. When new data flows in, only one Hash calculation is required. For large data volumes, the deduplication error of HLL is typically controlled within 1% or even lower, balancing efficiency and usability.
To meet basic approximate deduplication requirements, MaxCompute provides the aggregate functionAPPROX_DISTINCT. As business scenarios become increasingly diverse, more users want to not only output the final deduplication result but also store or reuse intermediate sketch data structures. To address this need, MaxCompute further supports a complete set of HyperLogLog++ functions and has optimized the underlying algorithm. This reduces memory usage while further improving estimation accuracy, better supporting complex analytics scenarios.
The following are two typical scenarios for HLL:
-
Scenarios that require repeated queries by time dimension: By persisting the daily generated HLL sketch, subsequent calculations only need to process the newly added data of the current day and merge it with the historical sketch. There is no need to repeatedly scan the full historical data, significantly improving query efficiency.
-
Scenarios that require joint deduplication of multiple columns of the same type: You can build and retain corresponding sketches for each column, and then directly perform merge operations at the sketch level. This enables efficient reuse of deduplication results and significantly reduces computational overhead.
Function list
MaxCompute SQL supports the following HyperLogLog++ functions.
|
Function |
Description |
|
Aggregates values of the same type into a new HLL++ sketch. |
|
|
Merges multiple HLL++ sketches of the same storage type into a new sketch. |
|
|
Calculates the cardinality estimate from an HLL++ sketch. |
|
|
Merges multiple HLL++ sketches of the same storage type into a new sketch and returns the cardinality estimate of the merged sketch. |
Notes
HLL_COUNT_EXTRACT/HLL_COUNT_MERGE/HLL_COUNT_MERGE_PARTIAL The BINARY data used by the functions must come from theHLL_COUNT_INIT function, and cannot come from other systems or other methods.