ApsaraDB RDS:カーディナリティ推定 (hll)

前提条件

開始する前に、以下の点を確認してください。

• ご利用の RDS インスタンスが PostgreSQL 11 以降を実行していること。

PostgreSQL 17 では hll 拡張機能はサポートされていません。

• メジャーエンジンバージョンが要件を満たしているにもかかわらず拡張機能が利用できない場合は、マイナーエンジンバージョンを更新してください。「マイナーエンジンバージョンの更新」をご参照ください。

COUNT DISTINCT ではなく hll を使用するタイミング

COUNT DISTINCT は、各クエリに対して「このデータセットに出現した一意の値はいくつあるか？」という固定された 1 つの質問に正確に答えます。この質問を異なるタイムウィンドウ（本日、今週、過去 30 日間、ローリング 7 日間など）で回答する必要がある場合、各クエリごとに生データを再スキャンする必要があります。

hll は異なります。hll はデータセットのコンパクトなスケッチ（概要）を格納します。一度、日次スケッチを格納しておけば、クエリ時にそれらを UNION することで、生データにアクセスすることなく任意の期間範囲に関する質問に応答できます。この特性により、hll は以下の分析ワークロードに最適です。

• 生イベントデータが非常に大きく、繰り返し集計するには負荷が大きすぎる場合

• 任意の日付範囲における UV や PV のカウントが必要な場合

• 近似結果で十分な場合（精度は hll_set_defaults で設定可能）

アルゴリズムの詳細については、「HyperLogLog: the analysis of a near-optimal cardinality estimation algorithm」をご参照ください。上流の拡張機能のソースコードについては、「citusdata/postgresql-hll」をご参照ください。

ハッシュ化の仕組み

値を hll 構造に追加する前に、hll_hash_* 関数のいずれかを使用してハッシュ化する必要があります。hll は生の値ではなくハッシュ値を格納します。これにより、コンパクトなサイズと型に依存しないストレージが実現されます。

ハッシュ化せずに生の整数を追加しようとすると、エラーが発生します。

SELECT 1234 || hll_empty();
-- ERROR: operator does not exist: integer || hll
-- HINT: No operator matches the given name and argument type(s).
--       You might need to add explicit type casts.

hll 操作に値を渡す前に、必ず対応する hll_hash_* 関数で入力値をラップしてください。

hll 拡張機能の有効化

データベースに接続し、以下のコマンドを実行します。

CREATE EXTENSION hll;

クイックスタート

以下の例では、最小限の hll セットをステップ・バイ・ステップで構築し、その後そのカーディナリティを取得します。

-- hll 列を含むテーブルを作成
CREATE TABLE helloworld (id integer, visitors hll);

-- 空の hll を挿入
INSERT INTO helloworld (id, visitors) VALUES (1, hll_empty());

-- ハッシュ化された整数（例：数値型のユーザー ID）を追加
UPDATE helloworld
SET visitors = hll_add(visitors, hll_hash_integer(12345))
WHERE id = 1;

-- ハッシュ化されたテキスト値（例：セッショントークン）を追加
UPDATE helloworld
SET visitors = hll_add(visitors, hll_hash_text('session-abc'))
WHERE id = 1;

-- 一意の値の数を推定
SELECT hll_cardinality(visitors) FROM helloworld WHERE id = 1;
--  hll_cardinality
-- -----------------
--                2
-- (1 row)

基本操作

hll フィールドを含むテーブルを作成します。

create table agg (id int primary key, userids hll);

INT データを hll_hashval データに変換します。

select 1::hll_hashval;

日付範囲にわたる UV の推定

この例では、典型的な PV/UV 分析パターンを示します。ポイントは、1 日ごとに 1 つの hll スケッチを格納し、クエリ時にスケッチを UNION することで、任意の日付範囲に関する質問に応答できることです。

ステップ 1：テーブルの作成と日次スケッチの登録

CREATE TABLE access_date (acc_date date UNIQUE, userids hll);

-- 日 0：ユーザー ID 1～10,000
INSERT INTO access_date
SELECT current_date, hll_add_agg(hll_hash_integer(user_id))
FROM generate_series(1, 10000) t(user_id);

-- 日 -1：ユーザー ID 5,000～20,000
INSERT INTO access_date
SELECT current_date - 1, hll_add_agg(hll_hash_integer(user_id))
FROM generate_series(5000, 20000) t(user_id);

-- 日 -2：ユーザー ID 9,000～40,000
INSERT INTO access_date
SELECT current_date - 2, hll_add_agg(hll_hash_integer(user_id))
FROM generate_series(9000, 40000) t(user_id);

ステップ 2：単一日付の UV をクエリ

# 演算子は、カーディナリティ推定値を直接返します。

SELECT #userids FROM access_date WHERE acc_date = current_date;
--      ?column?
-- ------------------
--  9725.85273370708
-- (1 row)

SELECT #userids FROM access_date WHERE acc_date = current_date - 1;
--      ?column?
-- ------------------
--  14968.6596883279
-- (1 row)

SELECT #userids FROM access_date WHERE acc_date = current_date - 2;
--      ?column?
-- ------------------
--  29361.5209149911
-- (1 row)

ステップ 3：日付範囲にわたる UV をクエリ

まず日次スケッチを UNION し、その後カーディナリティを推定します。これにより、複数日にわたって出現したユーザーの重複排除が正しく行われます。

-- 過去 3 日間のユニークビジター（日付間での重複排除済み）
SELECT hll_cardinality(hll_union_agg(userids))
FROM access_date
WHERE acc_date >= current_date - 2;

ステップ 4：ローリング 7 日間の UV ウィンドウの計算

ウィンドウ関数を使用して、生データの再スキャンなしに各日のローリングユニークビジター数を計算します。

SELECT
    acc_date,
    #hll_union_agg(userids) OVER seven_days AS rolling_uv
FROM access_date
WINDOW seven_days AS (ORDER BY acc_date ASC ROWS 6 PRECEDING);

リファレンス

次のステップ

• postgresql-hll リファレンスドキュメント

• マイナーエンジンバージョンの更新