Dynamic Table および RoaringBitmap を活用した任意の長期にわたる UV 計算 - Hologres

データ量が大規模で、クエリ/秒（QPS）およびレイテンシーに厳しい要件があるビジネスシナリオでは、Hologres の RoaringBitmap と Dynamic Table を組み合わせることで、増分計算を実行し、任意の期間にわたる柔軟な UV 計算を実現できます。本トピックでは、INT 型および TEXT 型のフィールドそれぞれに対する実装方法について説明します。

ソリューションの概要

本ソリューションには以下の利点があります：Dynamic Table は増分更新を採用しており、毎回処理対象となるのは新規データのみであるため、高 QPS・低レイテンシーの UV 計算において、高いパフォーマンスと低いリソース消費を実現します。また、Dynamic Table が自動的にリフレッシュを実行するため、追加のスケジューリングタスクは不要です。クエリは柔軟で、任意の時間範囲をサポートします。本アプローチは、大規模データ（数億レコード規模）に対して、任意の長期にわたる高 QPS の UV 計算に適しています。

ビジネスシナリオおよびデータ型に応じて、RoaringBitmap の実装方法は以下の 2 種類から選択してください：

方法 1：INT 型フィールド向けの長期 UV 計算 — UID が INT 型である場合の正確な重複排除に最適です。Dynamic Table と組み合わせることで、任意のタグまたはプロパティに基づくユーザーグループ間の積集合、和集合、差集合演算をサポートします。
方法 2：マッピングテーブルを併用した TEXT 型フィールド — UID が TEXT 型である場合に最適です。TEXT 形式の UID を整数に変換するためのユーザーマッピングテーブルが必要です。

方法 1：INT 型フィールド向けの RoaringBitmap 長期 UV 計算

UID フィールドが int 型である場合に、大規模データ（数億レコード規模）に対して任意の長期にわたる高 QPS の UV 計算を実行する際に、本方法をご利用ください。

ワークフロー

すべてのビジネスディメンションにわたる細かい粒度のデータを格納するためのユーザーデータ詳細テーブルを作成します。
ビジネスロジックに基づき、ユーザーデータ詳細テーブルを増分処理する Dynamic Table を作成します。基本ディメンションでグループ化し、UID を RoaringBitmap に集約して Dynamic Table に格納します。
目的のディメンションで Dynamic Table をクエリします。RoaringBitmap フィールドに対して RB_OR_AGG を適用して重複を排除し、UV のカーディナリティおよび PV のカウントを算出します。これにより、サブセカンドレベルのクエリ応答が実現します。

基盤データの準備

使用前に RoaringBitmap 拡張機能を作成します：

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

ユーザーデータ詳細テーブルを作成します（日単位のパーティション分割を推奨）。以下の例では、テーブル作成およびプロパティ設定を示しています：

DROP TABLE IF EXISTS ods_app_detail;
BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
  uid int,
  country text,
  prov text,
  city text,
  ymd text NOT NULL
)
LOGICAL PARTITION BY LIST (ymd);
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,ymd');
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

データ処理用 Dynamic Table の作成

ディメンションごとにユーザーデータ詳細テーブルを集約し、uid に対して RB_BUILD_AGG を用いて RoaringBitmap を計算する Dynamic Table を作成します。増分更新を有効化します。以下の例では、最新のパーティションのみを対象とし、データ鮮度を 5 分間隔で保つように設定しています：

CREATE DYNAMIC TABLE dt_dws_app_rb
LOGICAL PARTITION BY LIST(ymd)
WITH (
  freshness = '5 minutes',
  auto_refresh_mode = 'incremental',
  auto_refresh_partition_active_time = '1 days',
  partition_key_time_format = 'YYYYMMDD'
)
AS
SELECT
  RB_BUILD_AGG(uid) AS rb_uid,
  country,
  prov,
  city,
  ymd,
  COUNT(1) AS pv
FROM ods_app_detail
GROUP BY country, prov, city, ymd;

過去のパーティションについては手動で REFRESH を実行します。例：

REFRESH DYNAMIC TABLE public.dt_dws_app_rb PARTITION(20251201) WITH (refresh_mode = 'full');

任意の長期にわたる UV クエリ

-- 特定の日の UV および PV をクエリ
SELECT
  RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
  country,
  prov,
  city,
  sum(pv) AS pv
FROM dt_dws_app_rb
WHERE ymd = '20251223'
GROUP BY country, prov, city;

-- 1 ヶ月分の UV および PV をクエリ
SELECT
  RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
  country,
  prov,
  city,
  sum(pv) AS pv
FROM dt_dws_app_rb
WHERE ymd >= '20251201' AND ymd <= '20251230'
GROUP BY country, prov, city;

方法 2：マッピングテーブルを併用した TEXT 型フィールド向けの RoaringBitmap 長期 UV 計算

UID が TEXT 型である場合に本方法をご利用ください。TEXT 形式の UID を整数にマップするユーザーマッピングテーブルと組み合わせ、RoaringBitmap 処理を実行します。Dynamic Table 内の hg_id_encoding 関数を使用することで、マッピングテーブルへの自動書き込みおよび更新が可能です。

ユーザーマッピングテーブルの作成および更新

BEGIN;
CREATE TABLE uid_mapping (
  uid text NOT NULL,
  uid_int32 serial,
  PRIMARY KEY (uid)
);
CALL set_table_property('uid_mapping', 'clustering_key', 'uid');
CALL set_table_property('uid_mapping', 'distribution_key', 'uid');
CALL set_table_property('uid_mapping', 'orientation', 'row');
COMMIT;

データ処理用 Dynamic Table の作成

Dynamic Table 内で hg_id_encoding_int4(uid, 'uid_mapping') を使用し、TEXT 形式の UID を整数にマップしてマッピングテーブルを自動的に更新します。その後、ディメンションごとに RoaringBitmap に集約します：

CREATE DYNAMIC TABLE dt_dws_app_rb
WITH (
  freshness = '5 minutes',
  auto_refresh_mode = 'incremental',
  auto_refresh_partition_active_time = '1 days',
  partition_key_time_format = 'YYYYMMDD'
)
LOGICAL PARTITION BY LIST (ymd)
AS
SELECT
  country,
  prov,
  city,
  RB_BUILD_AGG(uid_int4) AS uid_rb,
  ymd
FROM (
  SELECT
    country,
    prov,
    city,
    hg_id_encoding_int4(uid, 'uid_mapping') AS uid_int4,
    ymd
  FROM ods_app_detail
) a
GROUP BY country, prov, city, ymd;

任意の長期にわたる UV クエリ

SELECT
  country,
  prov,
  city,
  RB_CARDINALITY(RB_OR_AGG(uid_rb)) AS uv
FROM dt_dws_app_rb
WHERE ymd = '20251223'
GROUP BY country, prov, city;