Skip to main content

BITMAP Type and Functions

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:

BITMAP_BUILD(value)

Parameters:

ParameterTypeDescription
valueINTThe integer value to include in the BITMAP.

Returns: BITMAP

Example:

SELECT BITMAP_BUILD(123);

BITMAP_CARDINALITY​

Returns the number of distinct integers in a BITMAP.

Syntax:

BITMAP_CARDINALITY(bitmap)

Parameters:

ParameterTypeDescription
bitmapBITMAPThe BITMAP to count.

Returns: BIGINT

Example:

SELECT BITMAP_CARDINALITY(user_bitmap) FROM daily_uv;

BITMAP_TO_STRING​

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

Syntax:

BITMAP_TO_STRING(bitmap)

Returns: STRING

BITMAP_AND / BITMAP_OR / BITMAP_XOR / BITMAP_ANDNOT​

Performs set algebra on two BITMAPs.

Syntax:

BITMAP_AND(bitmap1, bitmap2)
BITMAP_OR(bitmap1, bitmap2)
BITMAP_XOR(bitmap1, bitmap2)
BITMAP_ANDNOT(bitmap1, bitmap2)
FunctionOperationDescription
BITMAP_ANDIntersectionReturns integers present in both bitmaps.
BITMAP_ORUnionReturns integers present in either bitmap.
BITMAP_XORSymmetric differenceReturns integers present in exactly one bitmap.
BITMAP_ANDNOTDifferenceReturns 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:

BITMAP_TO_BYTES(bitmap)
BITMAP_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:

BITMAP_BUILD_AGG(value)

Parameters:

ParameterTypeDescription
valueINTThe integer column to aggregate.

Returns: BITMAP

Example:

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

BITMAP_BUILD_CARDINALITY_AGG​

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

Syntax:

BITMAP_BUILD_CARDINALITY_AGG(value)

Returns: BIGINT

BITMAP_AND_AGG / BITMAP_OR_AGG / BITMAP_XOR_AGG​

Aggregates multiple BITMAP values using set algebra.

Syntax:

BITMAP_AND_AGG(bitmap)
BITMAP_OR_AGG(bitmap)
BITMAP_XOR_AGG(bitmap)
FunctionOperationDescription
BITMAP_AND_AGGIntersectionReturns integers present in all bitmaps.
BITMAP_OR_AGGUnionReturns integers present in any bitmap.
BITMAP_XOR_AGGSymmetric differenceReturns 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:

BITMAP_AND_CARDINALITY_AGG(bitmap)
BITMAP_OR_CARDINALITY_AGG(bitmap)
BITMAP_XOR_CARDINALITY_AGG(bitmap)

Returns: BIGINT

Example:

SELECT BITMAP_OR_CARDINALITY_AGG(user_bitmap) AS weekly_uv
FROM daily_uv
WHERE 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.
On this page