This topic describes how to use Roaring bitmap functions in Hologres.

Background information

Roaring bitmaps are efficient compressed bitmaps that are used in almost all 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 216 chunks. The integers in each chunk share the same 16 most significant bits. The 16 least significant bits of integers are stored in containers. 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. Additionally, Roaring bitmaps 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 allow you to use 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, submit a ticket to update your instance.
  • Several Roaring bitmap functions are added in Hologres V1.3. You can view the description of these functions in the function list. If you need to use these functions, submit a ticket to update your instance to V1.3 or later.
  • 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 a database, you must execute the statement again.
    -- Install an extension.
    CREATE EXTENSION roaringbitmap;
    Note To uninstall the extension, execute the following statement:
    DROP EXTENSION roaringbitmap;
  • You cannot set the fields that store Roaring bitmaps as bitmap or dictionary indexes.

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. rb_build('{1,2,3,4,5}')
rb_index roaringbitmap, integer bigint Returns the index of which the element in the roaringbitmap data starts from 0. If the element does not exist, -1 is returned.
Note Only Hologres V1.3.16 and later support the function.
rb_index(rb_build('{1,2,3}'),3)
rb_and roaringbitmap,roaringbitmap roaringbitmap Performs an AND operation. rb_and(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_and_null2empty roaringbitmap,roaringbitmap roaringbitmap Performs an AND operation. If the input value is null, the function uses a pair of empty braces ({}) as the input.
Note Only Hologres V1.1.42 and later support the function.
rb_and_null2empty(rb_build(null),rb_build('{3,4,5}'))
rb_or roaringbitmap,roaringbitmap roaringbitmap Performs an OR operation. rb_or(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_or_null2empty roaringbitmap,roaringbitmap roaringbitmap Performs an OR operation. If the input value is null, the function uses a pair of empty braces ({}) as the input.
Note Only Hologres V1.1.42 and later support the function.
rb_or_null2empty(rb_build(null),rb_build('{3,4,5}'))
rb_xor roaringbitmap,roaringbitmap roaringbitmap Performs an XOR operation. rb_xor(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_andnot roaringbitmap,roaringbitmap roaringbitmap Performs an ANDNOT operation. rb_andnot(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_andnot_null2empty roaringbitmap,roaringbitmap roaringbitmap Performs an ANDNOT operation. If the input value is null, the function uses a pair of empty braces ({}) as the input.
Note Only Hologres V1.1.42 and later support the 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 input value is null, the function uses a pair of empty braces ({}) as the input.
Note Only Hologres V1.1.42 and later support the 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 input value is null, the function uses a pair of empty braces ({}) as the input.
Note Only Hologres V1.1.42 and later support the 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 input value is null, the function uses a pair of empty braces ({}) as the input.
Note Only Hologres V1.1.42 and later support the 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 the specified range excluding the range end in a Roaring bitmap.
Note Only Hologres V1.3.16 and later support the 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 the function.
rb_clear(rb_build('{1,2,3}'), 2, 3)
rb_equals roaringbitmap,roaringbitmap boolean Checks whether two Roaring bitmaps are the same. rb_equals(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_intersect roaringbitmap,roaringbitmap boolean Checks whether two Roaring bitmaps intersect. rb_intersect(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
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_remove roaringbitmap,integer roaringbitmap Removes a specific offset from a Roaring bitmap. rb_remove(rb_build('{1,2,3}'),3)
rb_flip roaringbitmap,integer,integer roaringbitmap Flips specific offsets in a Roaring bitmap. rb_flip(rb_build('{1,2,3}'),2,3)
rb_range roaringbitmap,bigint,bigint roaringbitmap Returns a set with the specified range excluding the range end in a Roaring bitmap.
Note Only Hologres V1.3.16 and later support the function.
rb_range(rb_build('{1,2,3}'), 2, 3)
rb_range_cardinality roaringbitmap, bigint, bigint bigint Returns the cardinality of the specified range excluding the range end in a Roaring bitmap.
Note Only Hologres V1.3.16 and later support the function.
rb_range_cardinality(rb_build('{1,2,3}'), 2, 3)
rb_minimum roaringbitmap integer Returns the smallest 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 largest 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 Jaccard similarity coefficient between two Roaring bitmaps.
Note Only Hologres V1.3.16 and later support the 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=0,range_end bigint=4294967296 roaringbitmap Return 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 the 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}'))

Examples

The following example shows you how to use Roaring bitmap functions:

  1. Install an extension.
    CREATE EXTENSION roaringbitmap;
  2. Create a table that is used to store roaringbitmap data.
    -- Create a table named t1.
    CREATE TABLE public.t1 (id integer, bitmap roaringbitmap);
  3. Invoke the rb_build function to insert roaringbitmap 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;
  4. 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
    ;
  5. 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;
  6. 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;
  7. Obtain the subscripts of the bits that are set to 1.
    SELECT RB_ITERATE(bitmap) FROM public.t1 WHERE id = 1;
  8. Convert a Roaring bitmap into an array.
    SELECT RB_TO_ARRAY(bitmap) FROM public.t1 WHERE id = 1;