BigQuery中的HLL算法：如何通过HyperLogLog将COUNT(DISTINCT)查询成本降低93%

TL;DR: （以下为摘要）一家数据提供商在处理6.5TB数据时遇到了麻烦的大规模COUNT(DISTINCT)查询，通过实现HyperLogLog（HLL）算法找到了解决方法。我们合作取得了以下成果：

• 大幅度成本削减： 将BigQuery按需槽使用量从2,000个减少至仅135个槽，从而无需购买额外的预留槽
• 超快的性能： 查询时间从数小时缩短到仅7秒
• 高效扩展： 每查询的数据扫描量从6.5TB降至16.25GB，同时保持了准确性
• 可持续增长： 采用新的HLL（HyperLogLog）方法，即使数据量增加也能保持高效扩展

理解客户独特的行为模式对于电子商务、数字营销和数据服务公司来说至关重要。我曾经合作过的一位客户，一家大型数据提供商，通过追踪用户的独特互动在30天的时间段内，为他们的营销活动和产品推荐提供支持。

不过，当他们的数据量达到数十亿条记录时，他们运行的 COUNT(DISTINCT) 查询来了解用户行为的独特性开始需要花费数小时才能完成，成本急剧增加——有时甚至完全无法完成。

最初只是个简单的数据分析任务，谁料后来却变成了一个重大的技术难题，影响了他们做决策的能力。

在这篇文章里，我会分析：

1. 为什么COUNT(DISTINCT)函数在大规模数据处理时会变得计算成本高昂？
2. 这对查询性能和成本的影响（我们的客户每次查询都要扫描6.5TB的数据！）
3. 我们是如何使用HyperLogLog（HLL）来解决这个问题的，将查询时间从小时缩短到秒，并将资源使用减少了93%

Hyper City

COUNT DISTINCT 虽然可以帮助你计算指定列中的唯一值数量，但随着数据规模的增大，每次查询运行时都需要处理整个数据集，从而导致性能问题。

咱们来看一个简单的例子。

    - 第1天数据  
    用户A来访  
    用户B来访  
    用户C来访  
    唯一访客数 = 3位用户  

     - 第2天数据  
    用户B来访  
    用户C来访  
    用户D来访  
    唯一访客数 = 3位用户

当你把这两天的每日计数（3+3=6）加起来算总用户数时，你就会发现这是不正确的，因为用户 B 和用户 C 将会被重复计算，这样就不准确了。

虽然许多聚合函数，比如SUM，可以进一步聚合，但COUNT DISTINCT却不行。为了正确计算不同值的总数，我们需要先将两天的数据合并，然后对整个合并的数据集使用COUNT DISTINCT函数。

我们客户的情况是，他们每天不得不查看过去30天内滚动周期的所有原始数据。这涉及查询一个经过分区过滤后仍包含18,570,335,647条记录的庞大表格，每次扫描大约6.5TB左右的数据。

这为他们的运营制造了瓶颈。

• 由于槽位消耗过高： 他们用完了2,000个槽位，触发了BigQuery的限制，导致作业失败。
• 由于长查询执行时间： 如果作业没有失败，需要数小时来完成。这使得他们的决策者无法及时获取所需数据。
• 由于昂贵的查询： 每天重复计算COUNT(DISTINCT)并扫描数十亿条记录，自然会带来高昂的成本。

因此，我们很快就发现继续使用这种方法行不通了。

Bigquery HLL 如何近似计算唯一计数

这是HyperLogLog (HLL)发挥作用的地方是。HLL是一种概率算法，可以估计数据集中不同项目的数量。HLL提供了一个高度准确的估算，而无需重新计算确切的不同项目数量，并且使用了显著更少的资源。

BigQuery 通过 APPROX_COUNT_DISTINCT（近似唯一计数函数）提供了这项功能，该函数基于 HLL（HyperLogLog）。这种近似满足了客户需求，精度上的折衷在速度和成本上的显著改进面前显得不那么重要了。

我们来看一下这30天里一个典型的COUNT(DISTINCT)查询会是怎么样的：

    SELECT  
    COUNT(DISTINCT user_id) AS unique_users  
    FROM  
    project.dataset.user_interactions  
    WHERE  
    event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE();

这将计算过去30天内唯一用户的数量。

此查询计算了过去30天内的唯一用户，在下一次运行时，我们不会使用之前运行中得到的结果，因为使用之前的缓存结果可能较慢且成本较高。

通过在 BigQuery 中使用 HyperLogLog 和 APPROXIMATE_COUNT_DISTINCT 函数，我们可以更快、更便宜地获得结果。

以下的SQL查询用于计算过去30天内大约唯一的用户数量：
SELECT  
APPROX_COUNT_DISTINCT(user_id) AS approx_unique_users  
FROM  
project.dataset.user_interactions  
WHERE  
event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE();

然而，即使使用了 APPROX_COUNT_DISTINCT，我们仍然需要对每次查询扫描30天的数据。这时HLL概要就派上用场了，它们允许我们预先计算并存储每日摘要数据，这样我们就可以高效地合并这些数据了。

HLL 草图 是一种紧凑的数据结构，使用哈希函数来总结信息。而不是保存每个用户访问的完整列表（这可能会有数百万条记录），你存储一个仅几千字节大小的 压缩版本 —— 即草图，可以在多个时间段内用来估算去重计数。

我们帮助我们的客户创建了每日HLL草图，保留了“HLL”作为特定术语。

• 每天，系统会生成一个HLL摘要图，根据客户需求概括出独立用户组。
• 然后聚合这些摘要，以计算任意时间段内的独立用户数，例如过去30天，而无需重新计算。

这种聚合是关键优势：HLL 概览可以高效合并到不同时间段，避免重复计数的误差，也不需要进行全面数据扫描。这使我们的客户能够查询 30 天内的独立用户数，使用少量资源和时间进行查询。

以下是存储每日HLL草图在BigQuery中的方法：

    CREATE OR REPLACE TABLE 项目.数据集.每日概览 AS  
    SELECT  
    事件日期,  
    HLL_COUNT.INIT(user_id) AS hll_sketch  
    FROM  
    项目.数据集.用户互动  
    GROUP BY  
    事件日期;

在这个查询中，我们为每一天创建一个每日HLL速写图，每个图汇总了user_interactions表中那一天的不同用户。

    SELECT  
    HLL_COUNT.MERGE(hll_sketch) AS 近似唯一用户  
    FROM  
    project.dataset.每日概览  
    WHERE  
    event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE();

在这里，我们使用 HLL_COUNT.MERGE 来合并每日的 HLL 概要，从而可以在整个 30 天内估算独立用户数，而无需重新计算。

使用 HLL 概要前后的槽的使用情况。（2K 槽代表最大的分配容量； 在使用 HLL 之前，实际消耗要高得多）

转换到HLL Sketch为我们的客户带来了显著的优势：

• 减少查询成本： 初始情况下，查询消耗了全部分配的2,000个按需定价模型下的插槽（slot）。客户的选择是购买额外的预留插槽（BigQuery 版本），这将显著增加他们的成本。然而，在实施了HLL摘要图和创建了聚合表后，插槽消耗降至仅剩135个插槽，大大减少了查询时间和成本。
• 更快的查询： 从几小时缩短到几秒（7秒），使客户能够更快生成报告，并满足实时决策需求。
• 可扩展性： 随着数据集的增长，使用HLL的方法继续扩展，无需全表扫描或重新计算，从而保持了成本的低廉。
• 足够准确： 虽然HLL提供了唯一访客数据的近似值，但客户发现可以配置的准确性对于他们的报告需求来说已经足够，没有对业务结果产生任何明显不利影响。

结果非常明显，

• 运行时间提高了超过99%。
• 槽位：槽位消耗从超过2,000个减少到仅135个。
• 时间：30天内不同计数查询的时间从几个小时缩短到几秒。
• 扫描的数据：每个查询扫描的数据从6.5TB减少至16.25GB。
• 成本节约：客户能够减少每个查询所消耗的槽位和扫描的数据，在实施后无需再购买槽位。
• 增强数据驱动的决策制定：因为能更快获取关键信息，客户可以更有效地响应用户行为并调整其营销策略。

对于在 BigQuery 中使用 COUNT(DISTINCT) 查询计算唯一值时遇到性能瓶颈的公司——尤其是在处理大规模数据集和长时间段时——HLL 概率性计数提供了一个高效的解决方案。通过切换到近似唯一值计数并存储每日概要，你可以显著降低查询成本并提升性能超过 99%，如这个实际例子所示。

如果你的业务依赖于快速且成本效益高的唯一计数统计，考虑采用HLL（HyperLogLog算法）并看到你在数据处理流程中的类似改进。

如果您希望优化您的云使用成本和时间——最重要的是时间——请点击这里联系我们，让我们一起看看如何将您的数据挑战变成增长的机会。