如何用Python估算Snowflake导出的HLL Sketch的基数？

Snowflake的HLL_EXPORT()会根据Sketch大小输出两种格式的HLL Sketch：

• 密集格式：包含version、precision和dense数组
• 稀疏格式：包含version、precision和sparse对象（内含indices和maxLzCounts数组）

实现步骤 #

1. 安装依赖

先安装hyperloglog库（遵循标准HLL算法，计算结果和Snowflake原生函数一致）：

pip install hyperloglog

2. 处理密集格式

import json
from hyperloglog import HyperLogLog

def get_dense_cardinality(sketch_json):
    hll_obj = json.loads(sketch_json)
    precision = hll_obj['precision']
    hll = HyperLogLog(precision)
    
    # Snowflake存储的是max前导零数+1，需减1还原
    for count in hll_obj['dense']:
        hll.register_raw(count - 1)
    
    return hll.cardinality()

# 示例调用
dense_example = '''{
  "version" : 3,
  "precision" : 12,
  "dense" : [3,3,3,3,5,3,4,3,5,6,2,4,4,7,5,6,6,3,2,2,3,2,4,5,5,5,2,5,5,3,6,1,4,2,2,4,4,5,2,5,4,6,3]
}'''
print("密集格式估算基数:", get_dense_cardinality(dense_example))

3. 处理稀疏格式

def get_sparse_cardinality(sketch_json):
    hll_obj = json.loads(sketch_json)
    precision = hll_obj['precision']
    hll = HyperLogLog(precision)
    
    indices = hll_obj['sparse']['indices']
    counts = hll_obj['sparse']['maxLzCounts']
    
    # 同样还原max前导零数，直接赋值到对应桶位置
    for idx, count in zip(indices, counts):
        hll.buckets[idx] = count - 1
    
    return hll.cardinality()

# 示例调用
sparse_example = '''{
  "version" : 3,
  "precision" : 12,
  "sparse" : {
    "indices": [1131,1241,1256,1864,2579,2699,3730],
    "maxLzCounts":[2,4,2,1,3,2,1]
  }
}'''
print("稀疏格式估算基数:", get_sparse_cardinality(sparse_example))

关键说明

Snowflake的HLL导出值是最大前导零数+1，必须减1才能匹配标准HLL算法的输入要求，否则计算结果会失真。

内容的提问来源于stack exchange，提问作者Felipe Hoffa