UV calculation over arbitrary long periods using Dynamic Table and RoaringBitmap - Hologres

RoaringBitmap pré-agrège les comptes de visiteurs uniques (UV) dans des bitmaps compressés lors de chaque actualisation incrémentielle, réduisant ainsi le travail au moment de la requête à un simple calcul de cardinalité sur ces bitmaps. Combinée à l'actualisation incrémentielle des tables dynamiques (Dynamic Table), qui traite uniquement les nouvelles lignes, ne nécessite aucune planification manuelle et gère les requêtes sur n'importe quelle plage temporelle, cette approche permet d'obtenir des calculs UV en moins d'une seconde sur des centaines de millions d'enregistrements, même sous des charges de travail à haute fréquence de requêtes par seconde (QPS) et à faible latence.

Choisissez votre implémentation en fonction du type de champ UID :

Type d'UID	Méthode
`int`	Méthode 1 : UV RoaringBitmap pour les champs INT
`text`	Méthode 2 : UV RoaringBitmap pour les champs TEXT

RoaringBitmap fonctionne avec des entrées entières. Pour les champs UID de type int, aucune conversion n'est nécessaire. Pour les champs UID de type text (Méthode 2), une table de mappage convertit les UID textuels en valeurs int4 avant l'agrégation.

Quand utiliser cette approche

Utilisez RoaringBitmap avec les tables dynamiques lorsque :

Le volume de données atteint des centaines de millions d'enregistrements ou plus
Les requêtes exigent un débit élevé (QPS) avec une faible latence
Les calculs UV doivent couvrir des plages temporelles arbitraires

Prérequis

Avant de commencer, assurez-vous de disposer des éléments suivants :

Une instance Hologres avec les permissions nécessaires pour créer des tables et des tables dynamiques
L'extension RoaringBitmap installée

Installez l'extension avant de créer des tables ou des tables dynamiques :

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

Méthode 1 : UV RoaringBitmap pour les champs INT

Utilisez cette méthode lorsque le champ UID est de type int. Elle prend en charge les opérations d'intersection, d'union et de différence sur les groupes d'utilisateurs basés sur n'importe quel tag ou propriété.

Fonctionnement

Stockez les données d'événements granulaires dans une table de détails partitionnée par jour.
Une table dynamique lit la table de détails de manière incrémentielle, regroupe les lignes selon les dimensions métier et agrège les UID dans un RoaringBitmap par groupe à l'aide de RB_BUILD_AGG. Chaque ligne de la table dynamique contient un bitmap représentant tous les UID distincts pour cette combinaison de dimensions et cette date.
Au moment de la requête, RB_OR_AGG fusionne les bitmaps sur la plage de dates demandée — en dédupliquant les UID entre les jours — et RB_CARDINALITY renvoie le compte UV.

Créer la table de détails

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;

La table est partitionnée par ymd (format YYYYMMDD) pour permettre un élagage efficace des partitions au moment de la requête. La propriété bitmap_columns accélère le filtrage sur country, prov, city et ymd.

Créer la table dynamique

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,  -- aggregates all UIDs in the group into a RoaringBitmap
  country,
  prov,
  city,
  ymd,
  COUNT(1) AS pv
FROM ods_app_detail
GROUP BY country, prov, city, ymd;

La table dynamique actualise uniquement la partition active (au cours du dernier jour) toutes les 5 minutes, en traitant seulement les nouvelles lignes. Après chaque actualisation, chaque ligne de dt_dws_app_rb contient un bitmap rb_uid pour une combinaison spécifique de (country, prov, city, ymd) — représentant tous les UID distincts apparus à cette date avec ces valeurs de dimension. Par exemple, la ligne pour (US, California, San Francisco, 20251223) contient un seul bitmap avec chaque UID dédupliqué ayant visité ce jour-là.

Actualiser les partitions historiques

Les nouvelles partitions s'actualisent automatiquement. Pour les partitions historiques en dehors de la fenêtre active, effectuez une actualisation complète manuellement. Par exemple, pour remplir rétroactivement le 1er décembre 2025 :

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

Interroger les UV sur n'importe quelle plage temporelle

Les deux requêtes utilisent RB_OR_AGG pour unir les bitmaps entre les lignes — en dédupliquant les UID entre les groupes de dimensions et les dates — puis RB_CARDINALITY pour compter les UID distincts.

-- UV and PV for a single day
SELECT
  RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,  -- union bitmaps across rows, then count distinct UIDs
  country,
  prov,
  city,
  SUM(pv) AS pv
FROM dt_dws_app_rb
WHERE ymd = '20251223'
GROUP BY country, prov, city;

-- UV and PV for a month
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;

Méthode 2 : UV RoaringBitmap pour les champs TEXT

Utilisez cette méthode lorsque le champ UID est de type text. Comme RoaringBitmap nécessite des entrées entières, une table de mappage utilisateur stocke la correspondance text vers int4. La fonction hg_id_encoding_int4 gère cette conversion automatiquement au sein de la table dynamique — en recherchant ou en insérant chaque UID textuel dans la table de mappage et en renvoyant son équivalent entier — vous n'avez donc pas besoin de gérer le dictionnaire manuellement ni d'exécuter des tâches de jointure séparées.

Créer la table de mappage utilisateur

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;

La table de mappage utilise le stockage en ligne (row store) pour des recherches ponctuelles rapides par uid. La colonne uid_int32 serial attribue automatiquement un entier unique à chaque nouvel UID textuel.

Créer la table dynamique

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,  -- aggregates mapped integer UIDs into a RoaringBitmap
  ymd
FROM (
  SELECT
    country,
    prov,
    city,
    hg_id_encoding_int4(uid, 'uid_mapping') AS uid_int4,  -- converts text UID to int4, writes new entries to uid_mapping
    ymd
  FROM ods_app_detail
) a
GROUP BY country, prov, city, ymd;

La sous-requête appelle hg_id_encoding_int4(uid, 'uid_mapping') pour rechercher ou insérer chaque UID textuel dans uid_mapping, en renvoyant son équivalent entier. La requête externe agrège ces entiers dans des bitmaps par groupe de dimensions.

Interroger les UV sur n'importe quelle plage temporelle

SELECT
  country,
  prov,
  city,
  RB_CARDINALITY(RB_OR_AGG(uid_rb)) AS uv  -- union bitmaps across rows, then count distinct UIDs
FROM dt_dws_app_rb
WHERE ymd = '20251223'
GROUP BY country, prov, city;

Étendez la clause WHERE à n'importe quelle plage de dates, comme pour la Méthode 1.

Hologres:Calculate UV with dynamic table and roaring bitmaps

Quand utiliser cette approche

Prérequis

Méthode 1 : UV RoaringBitmap pour les champs INT

Fonctionnement

Créer la table de détails

Créer la table dynamique

Actualiser les partitions historiques

Interroger les UV sur n'importe quelle plage temporelle

Méthode 2 : UV RoaringBitmap pour les champs TEXT

Créer la table de mappage utilisateur

Créer la table dynamique

Interroger les UV sur n'importe quelle plage temporelle

Étapes suivantes