Hologres:Analyze large user properties with roaring bitmaps

Property tags vs. action tags

Tags in a user profiling system fall into two categories with different characteristics that determine whether bitmap storage makes sense:

Why action tags are not suitable: Fact tables are organized by time (for example, one partition per day), and each day's slice contains limited rows. Compressing sparse per-day slices into bitmaps wastes storage due to row-oriented overhead. Action data also changes frequently, and the [option→bitmap] storage structure cannot identify which records need updating. For action data queries, Hologres uses its original columnar storage format instead.

Step 1: Encode user IDs

Bitmaps require integer input. Create a dictionary table using BIGSERIAL to map string user IDs to sequential integers:

-- Create a UID dictionary table
CREATE TABLE dws_uid_dict (
    encode_uid bigserial,
    uid text primary key
);

-- Populate from your source tag table
INSERT INTO dws_uid_dict(uid)
SELECT uid
FROM dws_userbase ON CONFLICT DO NOTHING;

This produces compact, sequential integer IDs — efficient to store as bitmaps. Sparse integer ranges consume significantly more space than dense ranges.

When to encode:

Step 2: Design bitmap tables

Split tag columns into separate bitmap tables. Each table uses bucket as the distribution key so queries run in parallel across nodes.

Correct approach — combined correlated columns:

What goes wrong when splitting correlated columns:

For the dws_shop_cust table, cust_type depends on shop_id. Splitting them into separate bitmap tables and filtering [shop_id = A] & [cust_type = Fresh] returns UID [1] — but that user is not actually a Fresh customer of shop A. Use a combined bitmap table instead.

Create the bitmap tables:

-- Enable the roaringbitmap extension
CREATE EXTENSION roaringbitmap;

-- Bitmap table: province property
BEGIN;
CREATE TABLE rb_dws_userbase_province (
    province text,
    bucket   int,
    bitmap   roaringbitmap
);
CALL set_table_property('rb_dws_userbase_province', 'distribution_key', 'bucket');
END;

-- Bitmap table: shop + customer type (correlated columns combined)
BEGIN;
CREATE TABLE rb_dws_shop_cust_sid_ctype (
    shop_id   text,
    cust_type text,
    bucket    int,
    bitmap    roaringbitmap
);
CALL set_table_property('rb_dws_shop_cust_sid_ctype', 'distribution_key', 'bucket');
END;

Step 3: Write data into bitmap tables

Use encode_uid / 65536 to assign each user to a bucket, then aggregate with rb_build_agg:

-- Write property bitmap data
INSERT INTO rb_dws_userbase_province
SELECT province,
       encode_uid / 65536 AS bucket,
       rb_build_agg(b.encode_uid) AS bitmap
FROM dws_userbase a
JOIN dws_uid_dict b ON a.uid = b.uid
GROUP BY province, bucket;

-- Write shop + customer type bitmap data
INSERT INTO rb_dws_shop_cust_sid_ctype
SELECT shop_id,
       cust_type,
       encode_uid / 65536 AS bucket,
       rb_build_agg(b.encode_uid) AS bitmap
FROM dws_shop_cust a
JOIN dws_uid_dict b ON a.uid = b.uid
GROUP BY shop_id, cust_type, bucket;

Step 4: Query with bitmap operations

To count users matching [shop_id = A] & [cust_type = Fresh] & [province = Beijing], combine bitmaps from both tables with RB_AND and count with RB_CARDINALITY:

SELECT SUM(RB_CARDINALITY(rb_and(ub.bitmap, uc.bitmap)))
FROM
    (SELECT rb_or_agg(bitmap) AS bitmap,
            bucket
     FROM rb_dws_userbase_province
     WHERE province = 'Beijing'
     GROUP BY bucket) ub
JOIN
    (SELECT rb_or_agg(bitmap) AS bitmap,
            bucket
     FROM rb_dws_shop_cust_sid_ctype
     WHERE shop_id = 'A'
       AND cust_type = 'Fresh'
     GROUP BY bucket) uc ON ub.bucket = uc.bucket;

Each subquery retrieves matching buckets and merges within-bucket bitmaps using rb_or_agg. The outer query intersects the two result sets per bucket and sums the cardinalities.

Build bitmaps in MaxCompute

MaxCompute requires a JAR-based user-defined function (UDF) for bitmap operations. All steps mirror the online approach: encode UIDs, assign buckets, and aggregate into bitmaps.

-- Select the MaxCompute project
USE bitmap_demo;

-- Create source tables
CREATE TABLE mc_dws_uid_dict (
    encode_uid bigint,
    bucket     bigint,
    uid        string
);

CREATE TABLE mc_dws_userbase (
    uid       string,
    province  string,
    gender    string,
    marriaged string
);

-- Encode UID values (incremental — skips already-encoded UIDs)
WITH uids_to_encode AS (
    SELECT DISTINCT(ub.uid),
           CAST(ub.uid / 65536 AS BIGINT) AS bucket
    FROM mc_dws_userbase ub
    LEFT JOIN mc_dws_uid_dict d ON ub.uid = d.uid
    WHERE d.uid IS NULL
),
uids_bucket_encode_offset AS (
    SELECT bucket,
           sum(cnt) OVER (ORDER BY bucket ASC) - cnt AS bucket_offset
    FROM (
        SELECT count(1) AS cnt, bucket
        FROM uids_to_encode
        GROUP BY bucket
    ) x
),
dict_used_id_offset AS (
    SELECT max(encode_uid) AS used_id_offset FROM mc_dws_uid_dict
)
-- New encode_uid = max existing + bucket offset + row number within bucket
INSERT INTO mc_dws_uid_dict
SELECT COALESCE((SELECT used_id_offset FROM dict_used_id_offset), 0) + bucket_offset + rn,
       bucket,
       uid
FROM (
    SELECT row_number() OVER (PARTITION BY ub.bucket ORDER BY ub.uid) AS rn,
           ub.bucket,
           bo.bucket_offset,
           uid
    FROM uids_to_encode ub
    JOIN uids_bucket_encode_offset bo ON ub.bucket = bo.bucket
) j;

-- Register bitmap UDFs from the mc-bitmap JAR
ADD JAR function_jar_dir/mc-bitmap-functions.jar AS mc_bitmap_func.jar -f;
CREATE FUNCTION mc_rb_cardinality AS com.alibaba.hologres.RbCardinalityUDF USING mc_bitmap_func.jar;
CREATE FUNCTION mc_rb_build_agg AS com.alibaba.hologres.RbBuildAggUDAF USING mc_bitmap_func.jar;

-- Create and populate the bitmap table
CREATE TABLE mc_rb_dws_userbase_province (
    province string,
    bucket   int,
    bitmap   string
);

INSERT INTO mc_rb_dws_userbase_province
SELECT province,
       b.bucket_num,
       mc_rb_build_agg(b.encode_uid) AS bitmap
FROM mc_dws_userbase a
JOIN mc_dws_uid_dict b ON a.uid = b.uid
GROUP BY province, b.bucket_num;

Download the mc-bitmap JAR to use bitmap functions in MaxCompute.

Import into Hologres

Create a foreign table pointing to the MaxCompute bitmap table, then insert into the Hologres bitmap table using roaringbitmap_text to convert the serialized string format:

-- Create a MaxCompute foreign table in Hologres
CREATE TABLE mc_rb_dws_userbase_province (
    province text,
    bucket   int,
    bitmap   text
) SERVER odps_server OPTIONS (project_name 'bitmap_demo', table_name 'mc_rb_dws_userbase_province');

-- Write bitmap data from MaxCompute into Hologres
INSERT INTO rb_dws_userbase_province
SELECT province,
       bucket::INT,
       roaringbitmap_text(bitmap, FALSE)
FROM mc_rb_dws_userbase_province;

After the import completes, run the same bitwise queries described in Step 4: Query with bitmap operations.

For a detailed walkthrough of offline UV calculation, see Batch UV calculation.