BITMAP Type and Functions

Applies toSelf-Managed v3

1 min read

On this page

Supported Version
BITMAP Type
Scalar Functions
Aggregate Functions
Limitations

VERA Engine 4.5 introduces the BITMAP type, a native SQL data type for storing sets of 32-bit integers based on RoaringBitmap. BITMAP functions support exact user deduplication (UV counts) and set algebra operations such as union, intersection, and difference.

Supported Version

VERA Engine 4.5 or later.

BITMAP Type

A BITMAP column stores a compressed set of unsigned 32-bit integer values. Use the BITMAP type when you need to:

Count distinct integer IDs (for example, user IDs) without sampling.
Perform set algebra across multiple groups or time windows.

Scalar Functions

BITMAP_BUILD

Builds a BITMAP from a single integer value.

Syntax:

SQL

1BITMAP_BUILD(value)

Parameters:

Parameter	Type	Description
Parameter	Type	Description
value	INT	The integer value to include in the BITMAP.

Returns: BITMAP

Example:

SQL

1SELECT BITMAP_BUILD(123);

BITMAP_CARDINALITY

Returns the number of distinct integers in a BITMAP.

Syntax:

SQL

1BITMAP_CARDINALITY(bitmap)

Parameters:

Parameter	Type	Description
Parameter	Type	Description
bitmap	BITMAP	The BITMAP to count.

Returns: BIGINT

Example:

SQL

1SELECT BITMAP_CARDINALITY(user_bitmap) FROM daily_uv;

BITMAP_TO_STRING

Converts a BITMAP to a comma-separated string of its integer values.

Syntax:

SQL

1BITMAP_TO_STRING(bitmap)

Returns: STRING

BITMAP_AND / BITMAP_OR / BITMAP_XOR / BITMAP_ANDNOT

Performs set algebra on two BITMAPs.

Syntax:

SQL

1BITMAP_AND(bitmap1, bitmap2)
2BITMAP_OR(bitmap1, bitmap2)
3BITMAP_XOR(bitmap1, bitmap2)
4BITMAP_ANDNOT(bitmap1, bitmap2)

Function	Operation	Description
Function	Operation	Description
BITMAP_AND	Intersection	Returns integers present in both bitmaps.
BITMAP_OR	Union	Returns integers present in either bitmap.
BITMAP_XOR	Symmetric difference	Returns integers present in exactly one bitmap.
BITMAP_ANDNOT	Difference	Returns integers in bitmap1 that are not in bitmap2.

Returns: BITMAP

BITMAP_TO_BYTES / BITMAP_FROM_BYTES

Serializes a BITMAP to a BYTES value and deserializes it back.

Syntax:

SQL

1BITMAP_TO_BYTES(bitmap)
2BITMAP_FROM_BYTES(bytes)

Use these functions to store or transmit BITMAP values outside of SQL, for example in a Kafka topic or an external table.

Aggregate Functions

BITMAP_BUILD_AGG

Builds a BITMAP by aggregating integer values from multiple rows.

Syntax:

SQL

1BITMAP_BUILD_AGG(value)

Parameters:

Parameter	Type	Description
Parameter	Type	Description
value	INT	The integer column to aggregate.

Returns: BITMAP

Example:

SQL

1SELECT dt, BITMAP_BUILD_AGG(user_id) AS user_bitmap
2FROM user_events
3GROUP BY dt;

BITMAP_BUILD_CARDINALITY_AGG

Builds a BITMAP and returns its cardinality in a single step.

Syntax:

SQL

1BITMAP_BUILD_CARDINALITY_AGG(value)

Returns: BIGINT

BITMAP_AND_AGG / BITMAP_OR_AGG / BITMAP_XOR_AGG

Aggregates multiple BITMAP values using set algebra.

Syntax:

SQL

1BITMAP_AND_AGG(bitmap)
2BITMAP_OR_AGG(bitmap)
3BITMAP_XOR_AGG(bitmap)

Function	Operation	Description
Function	Operation	Description
BITMAP_AND_AGG	Intersection	Returns integers present in all bitmaps.
BITMAP_OR_AGG	Union	Returns integers present in any bitmap.
BITMAP_XOR_AGG	Symmetric difference	Returns integers present in an odd number of bitmaps.

Returns: BITMAP

BITMAP_AND_CARDINALITY_AGG / BITMAP_OR_CARDINALITY_AGG / BITMAP_XOR_CARDINALITY_AGG

Aggregates multiple BITMAP values and returns the cardinality of the result.

Syntax:

SQL

1BITMAP_AND_CARDINALITY_AGG(bitmap)
2BITMAP_OR_CARDINALITY_AGG(bitmap)
3BITMAP_XOR_CARDINALITY_AGG(bitmap)

Returns: BIGINT

Example:

SQL

1SELECT BITMAP_OR_CARDINALITY_AGG(user_bitmap) AS weekly_uv
2FROM daily_uv
3WHERE dt BETWEEN '2025-01-01' AND '2025-01-07';

Limitations

The BITMAP type stores unsigned 32-bit integers only. Values outside the range 0–4,294,967,295 are not supported.
Requires VERA Engine 4.5 or later.

Was this helpful?

Yes No