All Products
Search

This Product

    Hologres:Roaring bitmap functions

    Document Center

    Hologres:Roaring bitmap functions

    Last Updated:Dec 02, 2024

    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 216 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 Upgrade instances. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.

    • Only exclusive instances support roaring bitmap functions. Hologres Shared Cluster instances do not support roaring bitmap functions.

    • Roaring bitmap functions are loaded in the public schema by default and can only be loaded in the public schema.

    • 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 Upgrade instances. 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;

      If you want to drop the extension for roaring bitmap functions, execute the following statement: 

      DROP EXTENSION roaringbitmap;
      Important

      We recommend that you do not execute the DROP EXTENSION <extension_name> CASCADE; statement to drop an extension. The CASCADE statement drops not only the specified extension but also the extension data and the objects that depend on the extension. The extension data includes the PostGIS data, roaring bitmap data, Proxima data, binary log data, and BSI data. The objects include metadata, tables, views, and server data.

    • 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

    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

    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

    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

    roaringbitmap_in

    text

    roaringbitmap

    Converts data of the TEXT type into the roaring bitmap type.

    Note

    Hologres V2.1.33 and later support this function. 

    -- Create a sample table. 
create table rb_text (
    id int,
    a text
);
-- Insert data into the sample table. 
insert into rb_text values (1,'\x3a300000010000000000090010000000010002000300040005000600070008000900c800');
-- Convert the data type of field a into the roaring bitmap type and perform an AND operation. 
select rb_and_cardinality_agg(roaringbitmap_in(a::cstring)) from rb_text;

-- The following result is returned:
rb_and_cardinality_agg|
-----------------------
                    10|

    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. If you set this parameter to false, invalid bitmap data is returned.

    roaringbitmap_text(':0', true)

    rb_to_text

    roaringbitmap

    text

    Converts a roaring bitmap into a binary roaring bitmap of the TEXT type.

    rb_to_text(rb_build('{1,2,3}'))

    Examples

    The following examples describe how to use roaring bitmap functions.

    1. Install an extension. 

      CREATE EXTENSION roaringbitmap;

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

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

    3. 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;

    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;