Roaring bitmap functions - Hologres - Alibaba Cloud Documentation Center

This topic describes parameters in roaring bitmap functions and how to use roaring bitmap functions in Hologres.

Background information

Roaring bitmaps are efficiently compressed bitmaps that are used in different popular programming languages on various big data platforms. Roaring bitmaps are suitable for ultra-high-cardinality dimensions and can be used for deduplication, tag-based filtering, and collection of time series data.

In a roaring bitmap, 32-bit integers are divided into 2¹⁶ chunks. The integers in each chunk share the same 16 most significant bits. The 16 least significant bits of integers are stored in a container. A roaring bitmap stores containers in a dynamic array as primary indexes. Two types of containers are available: array containers for sparse chunks and bitmap containers for dense chunks. An array container can store up to 4,096 integers. A bitmap container can store more than 4,096 integers.

Roaring bitmaps can use this storage structure to rapidly retrieve specific values. Roaring bitmaps also provide bitwise operations such as AND, OR, and XOR between the two types of containers. Therefore, roaring bitmaps can deliver excellent storage and computing performance.

Limits

When you use roaring bitmap functions in Hologres, take note of the following limits:

Only Hologres V0.10 and later support roaring bitmap functions. You can view the version of your Hologres instance in the Hologres console. If the version of your Hologres instance is earlier than V0.10, manually upgrade your Hologres instance in the Hologres console or join a Hologres DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.
Several roaring bitmap functions are added in Hologres V1.3. For more information about the roaring bitmap functions, see the Note part in the Description column of the roaring bitmap calculation functions section. If you want to use these functions, manually upgrade your Hologres instance in the Hologres console or join a Hologres DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.
Before you use roaring bitmap functions, you must execute the following statement to install an extension. An extension is installed at the database level. For each database, you need to install an extension only once. If you create another database, you must execute the statement again.
```
-- Install an extension.
CREATE EXTENSION roaringbitmap;
```
Note
To drop the extension, execute the following statement:
```
DROP EXTENSION roaringbitmap;
```
You cannot specify the fields that store roaring bitmaps as bitmap or dictionary indexes.

Operators

Operator	Input data type	Output data type	Description	Example	Remarks
&	roaringbitmap, roaringbitmap	roaringbitmap	Performs an AND operation.	rb_build('{1,2,3}') & rb_build('{3,4,5}')	Not supported.
\|	roaringbitmap, roaringbitmap	roaringbitmap	Performs an OR operation.	rb_build('{1,2,3}') \| rb_build('{3,4,5}')	Not supported.
	roaringbitmap, integer	roaringbitmap		rb_build('{1,2,3}') \| 6	Only Hologres V1.3.16 and later support this operator.
	integer, roaringbitmap	roaringbitmap		6 \| rb_build('{1,2,3}')	Only Hologres V1.3.16 and later support the function.
#	roaringbitmap, roaringbitmap	roaringbitmap	Performs an XOR operation.	rb_build('{1,2,3}') # rb_build('{3,4,5}')	Not supported.
<<	roaringbitmap, bigint	roaringbitmap	Shifts a value left by a specific number of places.	rb_build('{1,2,3}') << 2	Only Hologres V1.3.16 and later support this function.
>>	roaringbitmap, bigint	roaringbitmap	Shifts a value right by a specific number of places.	rb_build('{1,2,3}') >> 3	Only Hologres V1.3.16 and later support this function.
-	roaringbitmap, roaringbitmap	roaringbitmap	Performs an ANDNOT operation.	rb_build('{1,2,3}') - rb_build('{3,4,5}')	Only Hologres V1.3.16 and later support this function.
-	roaringbitmap, integer	roaringbitmap	Performs an ANDNOT operation.	rb_build('{1,2,3}') - 3	Not supported.
@>	roaringbitmap, roaringbitmap	bool	Checks whether A contains B.	rb_build('{1,2,3}') @> rb_build('{3,4,5}')	Not supported.
@>	roaringbitmap, integer	bool	Checks whether A contains B.	rb_build('{1,2,3}') @> 3	Not supported.
<@	roaringbitmap, roaringbitmap	bool	Checks whether A is contained by B.	rb_build('{1,2,3}') <@ rb_build('{3,4,5}')	Not supported.
<@	integer, roaringbitmap	bool	Checks whether A is contained by B.	3 <@ rb_build('{1,2,3}')	Not supported.
&&	roaringbitmap, roaringbitmap	bool	Checks whether A intersects with B.	rb_build('{1,2,3}') && rb_build('{3,4,5}')	Not supported.
=	roaringbitmap, roaringbitmap	bool	Checks whether two objects are equal.	rb_build('{1,2,3}') = rb_build('{3,4,5}')	Not supported.
<>	roaringbitmap, roaringbitmap	bool	Checks whether two objects are not equal.	rb_build('{1,2,3}') <> rb_build('{3,4,5}')	Not supported.

Roaring bitmap calculation functions

The following table describes roaring bitmap calculation functions.

Function	Input data type	Output data type	Description	Example
rb_build	integer[]	roaringbitmap	Creates a roaring bitmap from an integer array. Note In versions earlier than Hologres V1.3.39, only constant arrays can be used as input parameters. In Hologres V1.3.39 and later, columns of the ARRAY type can be used as input parameters.	`rb_build('{1,2,3,4,5}')`
rb_index	roaringbitmap, integer	bigint	Returns the index of which the element in the roaring bitmap data starts from 0. If the element does not exist, -1 is returned. Note Only Hologres V1.3.16 and later support this function.	`rb_index(rb_build('{1,2,3}'),3)`
rb_and_null2empty	roaringbitmap, roaringbitmap	roaringbitmap	Performs an AND operation. If the value of an input parameter is null, the function considers the parameter value as empty. Note Only Hologres V1.1.42 and later support this function.	`rb_and_null2empty(rb_build(null),rb_build('{3,4,5}'))`
rb_or_null2empty	roaringbitmap, roaringbitmap	roaringbitmap	Performs an OR operation. If the value of an input parameter is null, the function considers the parameter value as empty. Note Only Hologres V1.1.42 and later support this function.	`rb_or_null2empty(rb_build(null),rb_build('{3,4,5}'))`
rb_andnot_null2empty	roaringbitmap, roaringbitmap	roaringbitmap	Performs an ANDNOT operation. If the value of an input parameter is null, the function considers the parameter value as empty. Note Only Hologres V1.1.42 and later support this function.	`rb_andnot_null2empty(rb_build(null),rb_build('{3,4,5}'))`
rb_cardinality	roaringbitmap	integer	Calculates the cardinality.	`rb_cardinality(rb_build('{1,2,3,4,5}'))`
rb_and_cardinality	roaringbitmap, roaringbitmap	integer	Calculates the cardinality from an AND operation on two roaring bitmaps.	`rb_and_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))`
rb_and_null2empty_cardinality	roaringbitmap, roaringbitmap	integer	Calculates the cardinality from an AND operation on two roaring bitmaps. If the value of an input parameter is null, the function considers the parameter value as empty. Note Only Hologres V1.1.42 and later support this function.	`rb_and_null2empty_cardinality(rb_build(null),rb_build('{3,4,5}'))`
rb_or_cardinality	roaringbitmap, roaringbitmap	integer	Calculates the cardinality from an OR operation on two roaring bitmaps.	`rb_or_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))`
rb_or_null2empty_cardinality	roaringbitmap, roaringbitmap	integer	Calculates the cardinality from an OR operation on two roaring bitmaps. If the value of an input parameter is null, the function considers the parameter value as empty. Note Only Hologres V1.1.42 and later support this function.	`rb_or_null2empty_cardinality(rb_build(null),rb_build('{3,4,5}'))`
rb_xor_cardinality	roaringbitmap, roaringbitmap	integer	Calculates the cardinality from an XOR operation on two roaring bitmaps.	`rb_xor_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))`
rb_andnot_cardinality	roaringbitmap, roaringbitmap	integer	Calculates the cardinality from an ANDNOT operation on two roaring bitmaps.	`rb_andnot_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))`
rb_andnot_null2empty_cardinality	roaringbitmap, roaringbitmap	integer	Calculates the cardinality from an ANDNOT operation on two roaring bitmaps. If the value of an input parameter is null, the function considers the parameter value as empty. Note Only Hologres V1.1.42 and later support this function.	`rb_andnot_null2empty_cardinality(rb_build(null),rb_build('{3,4,5}'))`
rb_is_empty	roaringbitmap	boolean	Checks whether a roaring bitmap is empty.	`rb_is_empty(rb_build('{1,2,3,4,5}'))`
rb_fill	roaringbitmap, bigint, bigint	roaringbitmap	Fills in the specified range excluding the range end in a roaring bitmap. Note Only Hologres V1.3.16 and later support this function.	`rb_fill(rb_build('{1,2,3}'), 5, 7)`
rb_clear	roaringbitmap, bigint, bigint	roaringbitmap	Clears the specified range excluding the range end in a roaring bitmap. Note Only Hologres V1.3.16 and later support this function.	`rb_clear(rb_build('{1,2,3}'), 2, 3)`
rb_contains	roaringbitmap, roaringbitmap	boolean	Checks whether the first roaring bitmap contains the second roaring bitmap.	`rb_contains(rb_build('{1,2,3}'),rb_build('{3}'))`
rb_flip	roaringbitmap,integer,integer	roaringbitmap	Flips the specified offsets in a roaring bitmap.	`rb_flip(rb_build('{1,2,3}'),2,3)`
rb_range	roaringbitmap, bigint, bigint	roaringbitmap	Returns a new collection that ranges from the start position (included) to the end position (not included). The start position is 1. Note Only Hologres V1.3.16 and later support this function.	`rb_range(rb_build('{1,2,3}'), 2, 3)`
rb_range_cardinality	roaringbitmap, bigint, bigint	bigint	Returns the cardinality of the range from the start position (included) to the end position (not included). The start position is 1. Note Only Hologres V1.3.16 and later support this function.	`rb_range_cardinality(rb_build('{1,2,3}'), 2, 3)`
rb_minimum	roaringbitmap	integer	Returns the minimum offset in a roaring bitmap. If the roaring bitmap is empty, -1 is returned.	`rb_minimum(rb_build('{1,2,3}'))`
rb_maximum	roaringbitmap	integer	Returns the maximum offset in a roaring bitmap. If the roaring bitmap is empty, 0 is returned.	`rb_maximum(rb_build('{1,2,3}'))`
rb_rank	roaringbitmap,integer	integer	Returns the number of elements that are smaller than or equal to a specified offset in a roaring bitmap.	`rb_rank(rb_build('{1,2,3}'),3)`
rb_jaccard_dist	roaringbitmap, roaringbitmap	double precision	Returns the Jaccard distance or the Jaccard similarity coefficient between two roaring bitmaps. Note Only Hologres V1.3.16 and later support this function.	`rb_jaccard_dist(rb_build('{1,2,3}'), rb_build('{3,4}'))`
rb_select	roaringbitmap, bitset_limit bigint, bitset_offset bigint=0, reverse boolean=false, range_start bigint=-2147483648, range_end bigint=2147483647	roaringbitmap	Returns the [bitset_offset,bitset_offset +bitset_limit) subset of the [range_start,range_end) range in a roaring bitmap. Note Only Hologres V1.3.16 and later support this function.	`rb_select(rb_build('{1,2,3,4,5,6,7,8,9}'), 5, 2)`
rb_iterate	roaringbitmap	set of integer	Returns a list of offsets from a roaring bitmap.	`rb_iterate(rb_build('{1,2,3}'))`
rb_to_array	roaringbitmap	integer[]	Returns an integer array from which a roaring bitmap is created.	`rb_to_array(rb_build('{1,2,3}'))`
rb_to_array_string	roaringbitmap, text	text	Returns a string that is generated by concatenating the integer array from which a roaring bitmap is created with input text.	`rb_to_array_string(rb_build('{1,2,3}'),',')`

Roaring bitmap aggregate functions

The following table describes roaring bitmap aggregate functions.

Function	Input data type	Output data type	Description	Example
rb_build_agg	integer	roaringbitmap	Creates a roaring bitmap from a group of offsets.	`rb_build_agg(1)`
rb_or_agg	roaringbitmap	roaringbitmap	Performs an OR aggregate operation.	`rb_or_agg(rb_build('{1,2,3}'))`
rb_and_agg	roaringbitmap	roaringbitmap	Performs an AND aggregate operation.	`rb_and_agg(rb_build('{1,2,3}'))`
rb_xor_agg	roaringbitmap	roaringbitmap	Performs an XOR aggregate operation.	`rb_xor_agg(rb_build('{1,2,3}'))`
rb_or_cardinality_agg	roaringbitmap	integer	Calculates the cardinality from an OR aggregate operation on two roaring bitmaps.	`rb_or_cardinality_agg(rb_build('{1,2,3}'))`
rb_and_cardinality_agg	roaringbitmap	integer	Calculates the cardinality from an AND aggregate operation on two roaring bitmaps.	`rb_and_cardinality_agg(rb_build('{1,2,3}'))`
rb_xor_cardinality_agg	roaringbitmap	integer	Calculates the cardinality from an XOR aggregate operation on two roaring bitmaps.	`rb_xor_cardinality_agg(rb_build('{1,2,3}'))`

Other roaring bitmap functions

Function	Input data type	Output data type	Description	Example
roaringbitmap_text	text, bool	roaringbitmap	Converts the binary roaring bitmap in the deserialized data of the TEXT type to a roaring bitmap. The second parameter indicates whether to verify the bitmap data format. We recommend that you set this parameter to true. Otherwise, invalid bitmap data is returned.	`roaringbitmap_text(':0', true)`

Examples

The following examples describe how to use roaring bitmap functions.

Install an extension.
```
CREATE EXTENSION roaringbitmap;
```

Create a table that is used to store roaring bitmap data.

-- Create a table named t1.
CREATE TABLE public.t1 (id integer, bitmap roaringbitmap);

Use the rb_build function to insert roaring bitmap data into the table.

-- Set the bit value of an array to 1.
INSERT INTO public.t1 SELECT 1,RB_BUILD(ARRAY[1,2,3,4,5,6,7,8,9,200]);

-- Set the bit values of multiple elements to 1 and aggregate the bit values into a roaring bitmap.  
INSERT INTO public.t1 SELECT 2,RB_BUILD_AGG(e) FROM GENERATE_SERIES(1,100) e;

Perform bitwise operations such as OR, AND, XOR, and ANDNOT.

SELECT  RB_OR(a.bitmap,b.bitmap)
FROM    (
            SELECT  bitmap
            FROM    public.t1
            WHERE   id = 1
        ) AS a
        ,(
            SELECT  bitmap
            FROM    public.t1
            WHERE   id = 2
        ) AS b
;

Perform bitwise aggregate operations such as OR, AND, XOR, and BUILD to generate a new roaring bitmap.

SELECT RB_OR_AGG(bitmap) FROM public.t1;
SELECT RB_AND_AGG(bitmap) FROM public.t1;
SELECT RB_XOR_AGG(bitmap) FROM public.t1;
SELECT RB_BUILD_AGG(id) FROM public.t1;

Calculate the cardinality of the roaring bitmap. The cardinality is the number of bits that are set to 1 in the roaring bitmap.
```
SELECT RB_CARDINALITY(bitmap) FROM public.t1;
```

Obtain the subscripts of the bits that are set to 1.

SELECT RB_ITERATE(bitmap) FROM public.t1 WHERE id = 1;

Convert a roaring bitmap into an array.

SELECT RB_TO_ARRAY(bitmap) FROM public.t1 WHERE id = 1;