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.
  • 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.
    CREATE EXTENSION roaringbitmap;

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_and roaringbitmap,roaringbitmap roaringbitmap Performs an AND operation. rb_and(rb_build('{1,2,3}'),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_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_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_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_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_is_empty roaringbitmap boolean Checks whether a Roaring bitmap is empty. rb_is_empty(rb_build('{1,2,3,4,5}'))
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_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_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_iterate roaringbitmap setof integer Returns a list of offsets from a Roaring bitmap. rb_iterate(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;