User segmentation functions (Roaring Bitmap)
Roaring Bitmap is a compressed bitmap format optimized for high-performance set operations. Use it in AnalyticDB for MySQL for deduplication, tag-based user filtering, and time-series calculations over large datasets.
When to use Roaring Bitmap
Roaring Bitmap outperforms standard COUNT(DISTINCT ...) queries when each group in the bitmap contains fewer than 100 million entries. For larger UID spaces, increase the number of groups accordingly (for example, a UID space of 10 billion requires at least 100 groups).
Version requirements
-
V3.1.6.4 or later: The ROARINGBITMAP type and related functions are supported in OSS external tables.
-
V3.2.1.0 or later: The ROARINGBITMAP type and related functions are supported in internal tables.
-
V3.2.8.0 or later: Roaring Bitmap supports BIGINT data, which extends the value range to 64-bit integers. To enable this feature, contact technical support. After the feature is enabled, some functions accept BIGINT arguments and return BIGINT instead of INTEGER. For details, see Functions.
To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.
Limits
The following limits apply when you use Roaring Bitmap functions in AnalyticDB for MySQL:
-
You cannot directly SELECT a ROARINGBITMAP value. To view the elements in a ROARINGBITMAP column, use UNNEST. Example:
SELECT * FROM unnest(RB_BUILD(ARRAY[1,2,3])); -
In versions earlier than V3.2.1.0, the ROARINGBITMAP type is natively supported only in OSS external tables, and you cannot create tables that contain ROARINGBITMAP columns. To use Roaring Bitmap functions on AnalyticDB for MySQL internal tables, use
rb_build_varbinaryto convert VARBINARY values to the ROARINGBITMAP type. Example:// Define an internal table. CREATE TABLE test_rb_cstore (id INT, rb VARBINARY); // Use a Roaring Bitmap function for calculation. SELECT RB_CARDINALITY(RB_BUILD_VARBINARY(rb)) FROM test_rb_cstore;
Functions
Roaring Bitmap functions include scalar functions and aggregate functions.
Scalar functions
|
Function |
Input type |
Output type |
Description |
Example |
|
|
ARRAY(INT) or ARRAY(BIGINT) |
ROARING BITMAP |
Creates a bitmap from an array. |
|
|
|
INT, INT or BIGINT, BIGINT |
ROARING BITMAP |
Creates a bitmap from a range of numbers, from start (inclusive) to end (exclusive). |
|
|
|
VARBINARY |
ROARING BITMAP |
Creates a bitmap from a VARBINARY value. |
|
|
|
ROARING BITMAP |
BIGINT |
Returns the cardinality of the bitmap. |
|
|
|
ROARING BITMAP, INT or ROARING BITMAP, BIGINT |
BOOLEAN |
Checks whether the bitmap contains the specified number. |
|
|
|
ROARING BITMAP, ROARING BITMAP |
ROARING BITMAP |
Returns the intersection of two bitmaps. |
|
|
|
ROARING BITMAP, ROARING BITMAP |
ROARING BITMAP |
Returns the union of two bitmaps. |
|
|
|
ROARING BITMAP, ROARING BITMAP |
ROARING BITMAP |
Returns the symmetric difference (XOR) of two bitmaps. |
|
|
|
ROARING BITMAP, ROARING BITMAP |
ROARING BITMAP |
Performs an AND operation. If one input is NULL, the other input is returned. If one input is {}, {} is returned. |
|
|
|
ROARING BITMAP, ROARING BITMAP |
ROARING BITMAP |
Performs an OR operation. A NULL input is treated as an empty bitmap ({}). |
|
|
|
ROARING BITMAP, ROARING BITMAP |
ROARING BITMAP |
Performs an ANDNOT operation. A NULL input is treated as an empty bitmap ({}). |
|
|
|
ROARING BITMAP, ROARING BITMAP |
BIGINT |
Performs an AND operation and returns the cardinality. |
|
|
|
ROARING BITMAP, ROARING BITMAP |
BIGINT |
Performs an AND operation and returns the cardinality. A NULL input is treated as an empty bitmap ({}). |
|
|
|
ROARING BITMAP, ROARING BITMAP |
BIGINT |
Performs an OR operation and returns the cardinality. |
|
|
|
ROARING BITMAP, ROARING BITMAP |
BIGINT |
Performs an OR operation and returns the cardinality. A NULL input is treated as an empty bitmap ({}). |
|
|
|
ROARING BITMAP, ROARING BITMAP |
BIGINT |
Performs an XOR operation and returns the cardinality. |
|
|
|
ROARING BITMAP, ROARING BITMAP |
BIGINT |
Performs an ANDNOT operation and returns the cardinality. |
|
|
|
ROARING BITMAP, ROARING BITMAP |
BIGINT |
Performs an ANDNOT operation and returns the cardinality. A NULL input is treated as an empty bitmap ({}). |
|
|
|
ROARING BITMAP |
BOOLEAN |
Checks whether the bitmap is empty. |
|
|
|
ROARING BITMAP, BIGINT, BIGINT |
ROARING BITMAP |
Clears the specified range from the bitmap. range_end is excluded. |
|
|
|
ROARING BITMAP, ROARING BITMAP |
BOOLEAN |
Checks whether the first bitmap contains the second bitmap. |
|
|
|
ROARING BITMAP, INT, INT or ROARING BITMAP, BIGINT, BIGINT |
ROARING BITMAP |
Flips the offsets in the specified range of the bitmap. |
|
|
|
ROARING BITMAP |
INT or BIGINT |
Returns the smallest offset in the bitmap. If the bitmap is empty, an error is returned. |
|
|
|
ROARING BITMAP |
INT or BIGINT |
Returns the largest offset in the bitmap. If the bitmap is empty, an error is returned. |
|
|
|
ROARING BITMAP, INT or ROARING BITMAP, BIGINT |
BIGINT |
Returns the number of offsets in the bitmap that are less than or equal to the specified offset. |
|
|
|
ROARING BITMAP |
ARRAY(INT) |
Returns the bitmap as an INT array. Important
This function may generate a very large array, which can cause memory issues. Use it with caution. |
|
|
|
ROARING BITMAP |
VARBINARY |
Returns the bitmap serialized as VARBINARY. |
|
|
|
ROARING BITMAP, INT, INT or ROARING BITMAP, BIGINT, BIGINT |
BIGINT |
Returns the cardinality for elements at positions start (inclusive) to end (exclusive). Position is 1-based. Requires version 3.1.10.0 or later. |
|
|
|
ROARING BITMAP, BIGINT, BIGINT |
ROARING BITMAP |
Returns elements at positions start (inclusive) to end (exclusive). Position is 1-based. Requires version 3.1.10.0 or later. |
|
Aggregate functions
|
Function |
Input type |
Output type |
Description |
Example |
|
|
INT or BIGINT |
ROARING BITMAP |
Aggregates integer values from multiple rows into a bitmap. |
|
|
|
ROARING BITMAP |
ROARING BITMAP |
Performs OR aggregation across multiple bitmaps. |
|
|
|
ROARING BITMAP |
ROARING BITMAP |
Performs AND aggregation across multiple bitmaps. |
|
|
|
ROARING BITMAP |
ROARING BITMAP |
Performs XOR aggregation across multiple bitmaps. |
|
|
|
ROARING BITMAP |
INT or BIGINT |
Performs OR aggregation and returns the cardinality. |
|
|
|
ROARING BITMAP |
INT or BIGINT |
Performs AND aggregation and returns the cardinality. |
|
|
|
ROARING BITMAP |
INT or BIGINT |
Performs XOR aggregation and returns the cardinality. |
|
Basic usage
These examples show how to create a table, insert bitmap data, and run scalar and aggregate queries.
Internal table
-
Create an internal table with a ROARINGBITMAP column.
CREATE TABLE `test_rb` ( `id` INT, `rb` ROARINGBITMAP ); -
Insert bitmap data.
INSERT INTO test_rb VALUES (1, '[1, 2, 3]'); INSERT INTO test_rb VALUES (2, '[2, 3, 4, 5, 6]'); -
Get the cardinality of each row.
SELECT id, RB_CARDINALITY(rb) FROM test_rb;+------+--------------------+ | id | rb_cardinality(rb) | +------+--------------------+ | 2 | 5 | | 1 | 3 | +------+--------------------+ -
Get the cardinality of the union across all rows.
SELECT RB_OR_CARDINALITY_AGG(rb) FROM test_rb;+---------------------------+ | rb_or_cardinality_agg(rb) | +---------------------------+ | 6 | +---------------------------+
External table
-
Create an OSS external table with a ROARINGBITMAP column.
CREATE TABLE `test_rb` ( `id` INT, `rb` ROARINGBITMAP ) engine = 'oss' TABLE_PROPERTIES = '{ "endpoint": "oss-cn-zhangjiakou.aliyuncs.com", "accessid": "************", "accesskey": "************", "url": "oss://testBucketName/roaringbitmap/test_for_user/", "format": "parquet" }';For external table parameters, see OSS non-partitioned external table.
-
Insert bitmap data.
ImportantINSERT INTO is inefficient for large-scale writes. For large datasets, generate Parquet files using an ETL tool and upload them to the OSS path before creating the external table.
INSERT INTO test_rb SELECT 1, rb_build(ARRAY[1,2,3]); INSERT INTO test_rb SELECT 2, rb_build(ARRAY[2,3,4,5]); -
Get the cardinality of each row.
SELECT id, RB_CARDINALITY(rb) FROM test_rb;+------+--------------------+ | id | rb_cardinality(rb) | +------+--------------------+ | 2 | 4 | | 1 | 3 | +------+--------------------+ -
Get the cardinality of the union across all rows.
SELECT RB_OR_CARDINALITY_AGG(rb) FROM test_rb;+---------------------------+ | rb_or_cardinality_agg(rb) | +---------------------------+ | 5 | +---------------------------+
User profiling tutorial
This tutorial walks through a complete user profiling workflow: building a tag table from raw user data, converting it to bitmap format for efficient set operations, and running multi-dimensional analyses.

Step 1: Prepare the source tag table
-
Create the source table
users_base.CREATE TABLE users_base ( uid INT, tag1 STRING, -- Valid values: x, y, z tag2 STRING, -- Valid values: a, b tag3 INT -- Valid values: 1 to 10 ); -
Generate 100 million rows of random test data using a cross join of two bitmap ranges (10,000 × 10,000 = 100,000,000 rows).
SUBMIT JOB INSERT OVERWRITE users_base SELECT CAST(ROW_NUMBER() OVER (ORDER BY c1) AS INT) AS uid, SUBSTRING('xyz', FLOOR(RAND() * 3) + 1, 1) AS tag1, SUBSTRING('ab', FLOOR(RAND() * 2) + 1, 1) AS tag2, CAST(FLOOR(RAND() * 10) + 1 AS INT) AS tag3 FROM ( SELECT A.c1 FROM UNNEST(RB_BUILD_RANGE(0, 10000)) AS A(c1) JOIN (SELECT c1 FROM UNNEST(RB_BUILD_RANGE(0, 10000)) AS B(c1)) ); -
Verify the data.
SELECT * FROM users_base LIMIT 10;+--------+------+------+------+ | uid | tag1 | tag2 | tag3 | +--------+------+------+------+ | 74526 | y | b | 3 | | 75611 | z | b | 10 | | 80850 | x | b | 5 | | 81656 | z | b | 7 | | 163845 | x | b | 2 | | 167007 | y | b | 4 | | 170541 | y | b | 9 | | 213108 | x | a | 10 | | 66056 | y | b | 4 | | 67761 | z | a | 2 | +--------+------+------+------+
Step 2: Add grouping fields
Bitmap operations in a distributed engine run in parallel across groups. Add a user_group field to partition UIDs across groups, and an offset field to encode the UID position within each group.
This example uses 16 groups with the formula uid = 16 × offset + user_group:
-
user_group = uid % 16— the group the UID belongs to -
offset = uid / 16— the UID's position within the group
Sizing your groups: Each group's bitmap should contain fewer than 100 million entries. For a UID space of 10 billion, use 100 groups of 100 million each. Adjust the number of groups based on your cluster's ACUs and total UID space.
The grouping formula above is for illustration only. Design your own grouping function based on your data distribution.
-
Create the
userstable with the grouping fields.CREATE TABLE users ( uid INT, tag1 STRING, tag2 STRING, tag3 INT, user_group INT, -- Grouping field: uid % 16 offset INT -- Offset field: uid / 16 ); -
Populate
usersfromusers_base.SUBMIT JOB INSERT OVERWRITE users SELECT uid, tag1, tag2, tag3, CAST(uid % 16 AS INT), CAST(FLOOR(uid / 16) AS INT) FROM users_base; -
Verify the data.
SELECT * FROM users LIMIT 10;+---------+------+------+------+------------+--------+ | uid | tag1 | tag2 | tag3 | user_group | offset | +---------+------+------+------+------------+--------+ | 377194 | z | b | 10 | 10 | 23574 | | 309440 | x | a | 1 | 0 | 19340 | | 601745 | z | a | 7 | 1 | 37609 | | 753751 | z | b | 3 | 7 | 47109 | | 988186 | y | a | 10 | 10 | 61761 | | 883822 | x | a | 9 | 14 | 55238 | | 325065 | x | b | 6 | 9 | 20316 | | 1042875 | z | a | 10 | 11 | 65179 | | 928606 | y | b | 5 | 14 | 58037 | | 990858 | z | a | 8 | 10 | 61928 | +---------+------+------+------+------------+--------+
Step 3: Build bitmap tag tables
For each tag dimension, create a bitmap tag table where each row stores one bitmap per (tag_value, user_group) pair. The bitmap encodes the offsets of all matching users in that group.
Internal table
-
Create and populate the bitmap tag table for
tag1.CREATE TABLE `tag_tbl_1` ( `tag1` STRING, `rb` ROARINGBITMAP, `user_group` INT ); INSERT OVERWRITE tag_tbl_1 SELECT tag1, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag1, user_group; -
Verify the tag table.
SELECT tag1, user_group, RB_CARDINALITY(rb) FROM tag_tbl_1;+------+------------+--------------------+ | tag1 | user_group | rb_cardinality(rb) | +------+------------+--------------------+ | y | 13 | 563654 | | x | 11 | 565013 | | z | 2 | 564428 | | x | 4 | 564377 | ... | z | 5 | 564333 | | x | 8 | 564808 | | x | 0 | 564228 | | y | 3 | 563325 | +------+------------+--------------------+ -
Create and populate the bitmap tag table for
tag2.CREATE TABLE `tag_tbl_2` ( `tag2` STRING, `rb` ROARINGBITMAP, `user_group` INT ); INSERT OVERWRITE tag_tbl_2 SELECT tag2, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag2, user_group; -
Verify the tag table.
SELECT tag2, user_group, RB_CARDINALITY(rb) FROM tag_tbl_2;+------+------------+--------------------+ | tag2 | user_group | rb_cardinality(rb) | +------+------------+--------------------+ | a | 9 | 3123039 | | a | 5 | 3123973 | | a | 12 | 3122414 | | a | 7 | 3127218 | | a | 15 | 3125403 | ... | a | 10 | 3122698 | | b | 4 | 3126091 | | b | 3 | 3124626 | | b | 9 | 3126961 | | b | 14 | 3125351 | +------+------------+--------------------+
External table
-
Create and populate the bitmap tag table for
tag1in OSS.CREATE TABLE `tag_tbl_1` ( `tag1` STRING, `rb` ROARINGBITMAP, `user_group` INT ) engine = 'oss' TABLE_PROPERTIES = '{ "endpoint": "oss-cn-zhangjiakou.aliyuncs.com", "accessid": "************", "accesskey": "************", "url": "oss://testBucketName/roaringbitmap/tag_tbl_1/", "format": "parquet" }'; INSERT OVERWRITE tag_tbl_1 SELECT tag1, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag1, user_group; -
Verify the tag table.
SELECT tag1, user_group, RB_CARDINALITY(rb) FROM tag_tbl_1;+------+------------+--------------------+ | tag1 | user_group | rb_cardinality(rb) | +------+------------+--------------------+ | z | 7 | 2082608 | | x | 10 | 2082953 | | y | 7 | 2084730 | | x | 14 | 2084856 | ... | z | 15 | 2084535 | | z | 5 | 2083204 | | x | 11 | 2085239 | | z | 1 | 2084879 | +------+------------+--------------------+ -
Create and populate the bitmap tag table for
tag2in OSS.CREATE TABLE `tag_tbl_2` ( `tag2` STRING, `rb` ROARINGBITMAP, `user_group` INT ) engine = 'oss' TABLE_PROPERTIES = '{ "endpoint": "oss-cn-zhangjiakou.aliyuncs.com", "accessid": "************", "accesskey": "************", "url": "oss://testBucketName/roaringbitmap/tag_tbl_2/", "format": "parquet" }'; INSERT OVERWRITE tag_tbl_2 SELECT tag2, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag2, user_group; -
Verify the tag table.
SELECT tag2, user_group, RB_CARDINALITY(rb) FROM tag_tbl_2;+------+------------+--------------------+ | tag2 | user_group | rb_cardinality(rb) | +------+------------+--------------------+ | b | 11 | 3121361 | | a | 6 | 3124750 | | a | 1 | 3125433 | ... | b | 2 | 3126523 | | b | 12 | 3123452 | | a | 4 | 3126111 | | a | 13 | 3123316 | | a | 2 | 3123477 | +------+------------+--------------------+
Step 4: Analyze with bitmap tag tables
The following scenarios show common analysis patterns using the bitmap tag tables built in Step 3.
Scenario 1: Filter and group
Count users where tag1 IN ('x', 'y'), grouped by tag2.
All queries follow the same pattern: join bitmap tag tables on user_group, apply set operations per group, then aggregate across groups.
-
Inspect the per-group intermediate results.
SELECT tag2, t1.user_group AS user_group, RB_CARDINALITY(RB_AND(t2.rb, rb1)) AS rb FROM tag_tbl_2 AS t2 JOIN ( SELECT user_group, rb AS rb1 FROM tag_tbl_1 WHERE tag1 IN ('x', 'y') ) AS t1 ON t1.user_group = t2.user_group;+------+------------+---------+ | tag2 | user_group | rb | +------+------------+---------+ | b | 3 | 1041828 | | a | 15 | 1039859 | | a | 9 | 1039140 | | b | 1 | 1041524 | | a | 4 | 1041599 | | b | 1 | 1041381 | | b | 10 | 1041026 | | b | 6 | 1042289 | +------+------------+---------+ -
Sum the per-group counts to get the final totals.
SELECT tag2, SUM(cnt) FROM ( SELECT tag2, t1.user_group AS user_group, RB_CARDINALITY(RB_AND(t2.rb, rb1)) AS cnt FROM tag_tbl_2 AS t2 JOIN ( SELECT user_group, rb AS rb1 FROM tag_tbl_1 WHERE tag1 IN ('x', 'y') ) AS t1 ON t1.user_group = t2.user_group ) GROUP BY tag2;+------+----------+ | tag2 | sum(cnt) | +------+----------+ | a | 33327868 | | b | 33335220 | +------+----------+
Scenario 2: Intersection between two bitmap tag tables
Find users where (tag1 = 'x' OR tag1 = 'y') AND tag2 = 'b'.
Both inputs come from bitmap tag tables. First OR-aggregate within each tag table, then AND the results across the two.
SELECT user_group, RB_CARDINALITY(rb) FROM (
SELECT
t1.user_group AS user_group,
RB_AND(rb1, rb2) AS rb
FROM (
SELECT user_group, RB_OR_AGG(rb) AS rb1
FROM tag_tbl_1
WHERE tag1 = 'x' OR tag1 = 'y'
GROUP BY user_group
) AS t1
JOIN (
SELECT user_group, RB_OR_AGG(rb) AS rb2
FROM tag_tbl_2
WHERE tag2 = 'b'
GROUP BY user_group
) AS t2 ON t1.user_group = t2.user_group
GROUP BY user_group
);+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
| 10 | 2083679 |
| 3 | 2082370 |
| 9 | 2082847 |
| 2 | 2086511 |
...
| 1 | 2082291 |
| 4 | 2083290 |
| 14 | 2083581 |
| 15 | 2084110 |
+------------+--------------------+
Scenario 3: Intersection between a bitmap tag table and the source table
Find users where (tag1 = 'x' OR tag1 = 'y') AND tag2 = 'b', where the second condition is filtered from the raw users table rather than a pre-built bitmap tag table.
Use RB_BUILD_AGG to build a bitmap on the fly from the users table, then AND it with the pre-built bitmap from tag_tbl_1.
SELECT user_group, RB_CARDINALITY(rb) FROM (
SELECT
t1.user_group AS user_group,
RB_AND(rb1, rb2) AS rb
FROM (
SELECT user_group, RB_OR_AGG(rb) AS rb1
FROM tag_tbl_1
WHERE tag1 = 'x' OR tag1 = 'y'
GROUP BY user_group
) AS t1
JOIN (
SELECT user_group, RB_BUILD_AGG(offset) AS rb2
FROM users
WHERE tag2 = 'b'
GROUP BY user_group
) AS t2 ON t1.user_group = t2.user_group
GROUP BY user_group
);+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
| 3 | 2082370 |
| 1 | 2082291 |
| 0 | 2082383 |
| 4 | 2083290 |
| 11 | 2081662 |
| 13 | 2085280 |
...
| 14 | 2083581 |
| 15 | 2084110 |
| 9 | 2082847 |
| 8 | 2084860 |
| 5 | 2083056 |
| 7 | 2083275 |
+------------+--------------------+
Scenario 4: Export bitmap results to OSS
Save the bitmap results from Scenario 2 to an OSS external table for downstream use.
-
Create the output table.
CREATE TABLE `tag_tbl_3` ( `user_group` INT, `rb` ROARINGBITMAP ) engine = 'oss' TABLE_PROPERTIES = '{ "endpoint": "oss-cn-zhangjiakou.aliyuncs.com", "accessid": "************", "accesskey": "************", "url": "oss://testBucketName/roaringbitmap/tag_tbl_3/", "format": "parquet" }'; -
Write the Scenario 2 results to
tag_tbl_3.INSERT OVERWRITE tag_tbl_3 SELECT t1.user_group AS user_group, RB_AND(rb1, rb2) AS rb FROM ( SELECT user_group, RB_OR_AGG(rb) AS rb1 FROM tag_tbl_1 WHERE tag1 = 'x' OR tag1 = 'y' GROUP BY user_group ) AS t1 JOIN ( SELECT user_group, RB_OR_AGG(rb) AS rb2 FROM tag_tbl_2 WHERE tag2 = 'b' GROUP BY user_group ) AS t2 ON t1.user_group = t2.user_group;After the query completes, the result is stored in Parquet format at
oss://testBucketName/roaringbitmap/tag_tbl_3/.
Scenario 5: Accelerate queries with an internal cache table (for external tables)
Import bitmap data from an OSS external table into an internal table to speed up repeated queries. Because internal tables earlier than V3.2.1.0 do not support the ROARINGBITMAP type natively, store the bitmaps as VARBINARY and convert at query time.
-
Create the internal cache table with a VARBINARY column for the bitmap.
CREATE TABLE `tag_tbl_1_cstore` ( `tag1` VARCHAR, `rb` VARBINARY, `user_group` INT ); -
Import from the OSS table, serializing each bitmap to VARBINARY.
INSERT INTO tag_tbl_1_cstore SELECT tag1, RB_TO_VARBINARY(rb), user_group FROM tag_tbl_1; -
Query the cache table, deserializing VARBINARY back to bitmaps at query time.
SELECT tag1, user_group, RB_CARDINALITY(RB_OR_AGG(RB_BUILD_VARBINARY(rb))) FROM tag_tbl_1_cstore GROUP BY tag1, user_group;+------+------------+---------------------------------------------------+ | tag1 | user_group | rb_cardinality(rb_or_agg(rb_build_varbinary(rb))) | +------+------------+---------------------------------------------------+ | y | 3 | 2082919 | | x | 9 | 2083085 | | x | 3 | 2082140 | | y | 11 | 2082268 | | z | 4 | 2082451 | ... | z | 2 | 2081560 | | y | 6 | 2082194 | | z | 7 | 2082608 | +------+------------+---------------------------------------------------+