hologres-uv-compute

name

hologres-uv-compute

description

Hologres UV/PV computation using Dynamic Tables and RoaringBitmap for real-time deduplication at scale. Use for building incremental UV/PV pipelines, RoaringBitmap-based user deduplication, flexible time-range UV aggregation, and text-to-int UID encoding for bitmap compatibility. Triggers: "hologres uv", "hologres pv", "roaringbitmap", "rb_build_agg", "rb_or_agg", "去重", "UV计算", "用户去重", "bitmap去重", "实时UV", "hg_id_encoding"

Prerequisites

This skill requires hologres-cli to be installed first:

pip install hologres-cli
export HOLOGRES_SKILL=hologres-uv-compute

All SQL execution and Dynamic Table operations depend on hologres-cli commands (hologres sql run --write, hologres dt create).

Hologres UV/PV Computation with Dynamic Table & RoaringBitmap

Build real-time, incremental UV/PV computation pipelines using Dynamic Tables and RoaringBitmap in Hologres. This approach supports flexible time-range aggregation over billions of records with low latency.

Why This Approach

Traditional COUNT DISTINCT	RoaringBitmap + Dynamic Table
Full scan on every query	Pre-aggregated bitmaps, incremental refresh
Slow with high-cardinality UIDs	Compressed bitmap, sub-second UV queries
Cannot merge across time ranges	RB_OR_AGG merges bitmaps for any date range
Heavy resource usage	Incremental computation, minimal resources

Quick Start

-- 1. Enable RoaringBitmap extension
CREATE EXTENSION IF NOT EXISTS roaringbitmap;

-- 2. Create ODS detail table (source data)
BEGIN;
CREATE TABLE 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', 'distribution_key', 'uid');
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,ymd');
COMMIT;

-- 3. Create DWS Dynamic Table (bitmap aggregation layer)
CREATE DYNAMIC TABLE dt_dws_app_rb (
  country, prov, city, rb_uid, pv, ymd
)
LOGICAL PARTITION BY LIST (ymd)
WITH (
  freshness = '5 minutes',
  auto_refresh_mode = 'incremental',
  auto_refresh_partition_active_time = '2 days',
  partition_key_time_format = 'YYYYMMDD'
)
AS
SELECT country, prov, city,
       RB_BUILD_AGG(uid) AS rb_uid,
       COUNT(1) AS pv,
       ymd
FROM ods_app_detail
GROUP BY country, prov, city, ymd;

-- 4. Query UV/PV for a single day
SELECT country, prov, city,
       RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
       SUM(pv) AS pv
FROM dt_dws_app_rb
WHERE ymd = '20251223'
GROUP BY country, prov, city;

Architecture Overview

ODS (Detail)                DWS (Bitmap Aggregation)         Query
┌─────────────┐  Dynamic   ┌──────────────────────┐    ┌─────────────┐
│ods_app_detail│──Table────>│ dt_dws_app_rb        │───>│ RB_OR_AGG   │
│  uid, dims,  │ incremental│  rb_uid (bitmap),    │    │ + CARDINALITY│
│  ymd         │  refresh   │  pv, dims, ymd       │    │ = UV for any │
└─────────────┘            └──────────────────────┘    │ time range  │
                                                       └─────────────┘

Data flow:

1. Raw events flow into ods_app_detail (partitioned by day)
2. Dynamic Table dt_dws_app_rb incrementally aggregates UIDs into bitmaps per dimension per day
3. Queries merge bitmaps across any date range using RB_OR_AGG for exact UV

ODS Detail Table Design

The source table stores raw event data, partitioned by date.

BEGIN;
CREATE TABLE 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', 'distribution_key', 'uid');
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,ymd');
COMMIT;

Key design choices:

Property	Value	Reason
orientation	column	Columnar storage for analytical queries
distribution_key	uid	Distribute by user for aggregation locality
clustering_key	ymd	Optimize time-range scans
event_time_column	ymd	Segment key for partition pruning
bitmap_columns	dimension columns	Accelerate dimension filtering

DWS Dynamic Table (Bitmap Aggregation)

The Dynamic Table pre-aggregates UIDs into RoaringBitmaps per dimension per day using incremental refresh.

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

Key Dynamic Table parameters:

Parameter	Value	Description
freshness	5 minutes	Target data freshness
auto_refresh_mode	incremental	Only compute new/changed data
auto_refresh_partition_active_time	2 days	Only auto-refresh recent 2 days
partition_key_time_format	YYYYMMDD	Parse partition key as date

Refresh Historical Partitions

Auto-refresh only covers active partitions. For historical data, trigger manually:

-- Full refresh a specific partition
REFRESH DYNAMIC TABLE dt_dws_app_rb
  PARTITION(20251201)
  WITH (refresh_mode = 'full');

-- Full refresh a date range (one by one)
REFRESH DYNAMIC TABLE dt_dws_app_rb
  PARTITION(20251201)
  WITH (refresh_mode = 'full');

UV/PV Queries

Single Day

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

Date Range (e.g., Monthly)

SELECT country, prov, city,
       RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
       SUM(pv) AS pv
FROM dt_dws_app_rb
WHERE ymd >= '20251201' AND ymd <= '20251231'
GROUP BY country, prov, city;

Global UV (All Dimensions)

SELECT RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS total_uv,
       SUM(pv) AS total_pv
FROM dt_dws_app_rb
WHERE ymd >= '20251201' AND ymd <= '20251231';

How it works: RB_OR_AGG merges daily bitmaps with bitwise OR — a user appearing on multiple days is counted only once. RB_CARDINALITY returns the count of distinct bits (= distinct users).

Text UID Encoding

When UIDs are text type (e.g., device IDs, UUIDs), they must be mapped to integers for RoaringBitmap. Two approaches:

Approach 1: Mapping Table (Manual)

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

-- Use in Dynamic Table
CREATE DYNAMIC TABLE dt_dws_app_rb (...)
AS
SELECT country, prov, city,
       RB_BUILD_AGG(m.uid_int32) AS uid_rb,
       COUNT(1) AS pv, ymd
FROM ods_app_detail o
JOIN uid_mapping m ON o.uid_text = m.uid
GROUP BY country, prov, city, ymd;

Approach 2: hg_id_encoding (V4.1+, Recommended)

Built-in function that auto-manages the mapping table.

-- Create mapping table (single text PK + one serial column)
BEGIN;
CREATE TABLE uid_mapping (
  uid text NOT NULL,
  uid_int32 serial,
  PRIMARY KEY (uid)
);
CALL set_table_property('uid_mapping', 'orientation', 'row');
CALL set_table_property('uid_mapping', 'distribution_key', 'uid');
COMMIT;

-- Use hg_id_encoding_int4 in Dynamic Table query
CREATE DYNAMIC TABLE dt_dws_app_rb (...)
AS
SELECT country, prov, city,
       RB_BUILD_AGG(hg_id_encoding_int4(uid_text, 'uid_mapping')) AS uid_rb,
       COUNT(1) AS pv, ymd
FROM ods_app_detail
GROUP BY country, prov, city, ymd;

Constraints for hg_id_encoding_int4:

• Mapping table must have exactly: one text PK column + one serial column
• Input UID must not be NULL
• Requires Hologres V4.1+
• Supports incremental refresh in Dynamic Tables

Core RoaringBitmap Functions

Function	Description	Example
RB_BUILD_AGG(int)	Aggregate integers into a bitmap	RB_BUILD_AGG(uid)
RB_OR_AGG(roaringbitmap)	Merge bitmaps (union / deduplicate)	RB_OR_AGG(rb_uid)
RB_AND_AGG(roaringbitmap)	Intersect bitmaps (common users)	RB_AND_AGG(rb_uid)
RB_CARDINALITY(roaringbitmap)	Count distinct elements in bitmap	RB_CARDINALITY(rb)
RB_OR(rb, rb)	Union two bitmaps	RB_OR(a, b)
RB_AND(rb, rb)	Intersect two bitmaps	RB_AND(a, b)
RB_ANDNOT(rb, rb)	Difference (in A but not in B)	RB_ANDNOT(a, b)
RB_TO_ARRAY(roaringbitmap)	Convert bitmap to integer array	RB_TO_ARRAY(rb)
RB_BUILD(int[])	Build bitmap from integer array	RB_BUILD(ARRAY[1,2,3])
RB_CONTAINS(rb, int)	Check if bitmap contains a value	RB_CONTAINS(rb, 42)
RB_IS_EMPTY(roaringbitmap)	Check if bitmap is empty	RB_IS_EMPTY(rb)

For complete function reference, see references/roaringbitmap-functions.md.

References

Document	Content
roaringbitmap-functions.md	Complete RoaringBitmap function reference
dynamic-table-patterns.md	Dynamic Table configuration patterns for UV
advanced-scenarios.md	Advanced UV scenarios: retention, funnel, cross-platform

Best Practices

1. Always use LOGICAL PARTITION BY LIST (ymd) on both ODS and DWS tables for time-range partition pruning
2. Set auto_refresh_mode = 'incremental' to avoid recomputing all data on each refresh
3. Configure auto_refresh_partition_active_time to limit auto-refresh to recent partitions only
4. Manually refresh historical partitions with REFRESH DYNAMIC TABLE ... PARTITION(...) WITH (refresh_mode = 'full')
5. Use RB_OR_AGG for cross-day UV — it merges daily bitmaps for exact deduplication over any date range
6. For text UIDs, use hg_id_encoding_int4 (V4.1+) instead of manual mapping tables
7. Set distribution_key = 'uid' on ODS table for aggregation locality
8. Install extension first — CREATE EXTENSION IF NOT EXISTS roaringbitmap before any bitmap operations