@Rocky what are the tradeoffs for using ds_hll_count_distinct persistent with a varbinary column versus native HLL type?

Hello! I'm Rocky. When it comes to approximate count distinct in StarRocks, you have two primary paths: the native HLL data type and the Apache DataSketches (ds_hll) implementation stored in VARBINARY columns. Here are the key tradeoffs between using

ds_hll

(persistent in

VARBINARY

) versus the native

HLL

type: 1. Interoperability and Ecosystem * DataSketches (

VARBINARY

): This uses the industry-standard Apache DataSketches library. If you need to export your sketches to other systems (like Apache Spark, Hive, or Presto) that also support DataSketches, you should choose this. The binary format is compatible across these platforms. * Native

HLL

Type: This is internal to StarRocks. While highly optimized for the engine, it is a proprietary format and not easily consumable by external tools outside of StarRocks. 2. Precision and Configuration * DataSketches (

VARBINARY

): Offers much higher flexibility. You can tune the

log_k

parameter (from 4 to 21) to find the perfect balance between accuracy and storage. Higher

log_k

values provide significantly better precision than standard HLL implementations. * Native

HLL

Type: Uses a fixed configuration with a standard error rate (typically around 1%). You cannot tune it for higher precision if your use case requires it. 3. Storage Efficiency * Native

HLL

Type: * Empty: 80 bytes. * Sparse: Up to 1,360 bytes (for $\le$ 160 distinct values). * Dense: Fixed at 16,464 bytes once you exceed the sparse threshold. * DataSketches (

VARBINARY

): The size depends on your chosen

log_k

and

tgt_type

(

HLL_4

,

HLL_6

, or

HLL_8

). It can often be more space-efficient for lower cardinalities or significantly larger for extremely high precision settings. 4. Performance * Native

HLL

Type: Generally faster for aggregation and estimation because it is deeply integrated into the StarRocks vectorized engine. * DataSketches (

VARBINARY

): Slightly slower than native HLL functions (like

APPROX_COUNT_DISTINCT

). Because it uses the Apache DataSketches library logic, there is a small overhead for the extra precision and flexibility it provides. 5. Ease of Use * Native

HLL

Type: Very simple to use in Aggregate Tables. You can define a column as

HLL HLL_UNION

and StarRocks handles the merging automatically during data ingestion. * DataSketches (

VARBINARY

): Requires more manual effort. You must use

ds_hll_accumulate

to create the sketch, store it in a

VARBINARY

column, and use

ds_hll_estimate

or

ds_hll_union

to query it. Summary Recommendation * Choose Native

HLL

if you want the best performance, easiest integration with Aggregate tables, and a ~1% error rate is acceptable. * Choose

ds_hll

(VARBINARY) if you need to share sketches with other big data tools (Spark/Hive), or if you need higher precision than what the standard HLL type offers. Is there a specific scale of data or precision requirement you are aiming for? I can help you tune the parameters! References • sql-reference/sql-functions/aggregate-functions/ds_hll_count_distinct.md • sql-reference/data-types/other-data-types/HLL.md • using_starrocks/distinct_values/Using_HLL.md • sql-reference/sql-functions/aggregate-functions/ds_hll_accumulate.md • sql-reference/sql-functions/aggregate-functions/ds_hll_estimate.md