Bitmaps can be constructed using two different methods:

  1. Aggregation function: Use the groupBitmap function with the -State suffix to aggregate values into a bitmap.

  2. Array conversion: Convert an existing Array object into a bitmap data structure.

These methods allow for efficient creation and manipulation of bitmap data structures.

​
ClickHouse function reference

​
bitmapBuild

Builds a bitmap from an unsigned integer array.

Syntax:

bitmapBuild(array)

Arguments:

  • array - Unsigned integer array.

Returns:

A bitmap object constructed from the input array.

Example:

SELECT
  bitmapBuild([1, 2, 3, 4, 5]) AS bitmap,
  toTypeName(bitmap) AS type

Result:

| bitmap | type                                        |
|--------|---------------------------------------------|
|        | AggregateFunction(groupBitmap, UInt8)       |

In this example, bitmapBuild constructs a bitmap from the array of IDs [1, 2, 3, 4, 5]. The resulting bitmap is of type AggregateFunction(groupBitmap, UInt8).

​
bitmapToArray

Converts a bitmap object to an array of unsigned integers.

Syntax:

bitmapToArray(bitmap)

Arguments:

  • bitmap - A bitmap object.

Returns:

An array of unsigned integers containing the set bits from the bitmap.

Example:

SELECT bitmapToArray(bitmapBuild([1, 3, 5, 7, 9])) AS taco_ids;

Result:

| taco_ids        |
|-----------------|
| [1, 3, 5, 7, 9] |

In this example, bitmapToArray converts a bitmap containing the taco IDs 1, 3, 5, 7, and 9 back into an array of integers.

The returned array will be sorted in ascending order of the set bits, regardless of the order they were originally added to the bitmap.

​
bitmapSubsetInRange

Returns a subset of a bitmap containing only the bits within a specified range.

Syntax:

bitmapSubsetInRange(bitmap, range_start, range_end)

Arguments:

  • bitmap (Bitmap object): The input bitmap.
  • range_start (UInt32): The start of the range (inclusive).
  • range_end (UInt32): The end of the range (exclusive).

Returns:

A new bitmap containing only the bits from the input bitmap that fall within the specified range.

Example:

SELECT
  bitmapToArray(
    bitmapSubsetInRange(
      bitmapBuild([1,2,3,4,5,6,7,8,9,10,11,12]),
      toUInt32(3),
      toUInt32(8)
    )
  ) AS taco_subset;

Result:

| taco_subset |
|-------------|
| [3,4,5,6,7] |

In this example, bitmapSubsetInRange extracts a subset of the bitmap containing only the values between 3 (inclusive) and 8 (exclusive), which represents taco IDs 3 through 7.

This function is useful for efficiently extracting a range of values from a bitmap without needing to materialize the entire bitmap as an array.

​
bitmapSubsetLimit

Returns a subset of a bitmap with smallest bit value range_start and at most cardinality_limit elements.

Syntax:

bitmapSubsetLimit(bitmap, range_start, cardinality_limit)

Arguments:

  • bitmap (Bitmap) - The input bitmap.
  • range_start (UInt32) - The start of the range (inclusive).
  • cardinality_limit (UInt32) - The maximum cardinality of the subset.

Returns:

A bitmap subset with at most cardinality_limit elements, starting from range_start.

Example:

SELECT bitmapToArray(bitmapSubsetLimit(
  bitmapBuild([1,2,3,4,5,6,7,8,9,10,11,12]),
  toUInt32(5),
  toUInt32(3)
)) AS taco_subset;

Result:

| taco_subset |
|-------------|
| [5,6,7]     |

In this example, bitmapSubsetLimit returns a subset of the bitmap starting from value 5, with a maximum of 3 elements. The result is then converted to an array for display.

​
subBitmap

Returns a subset of the bitmap, starting from position offset. The maximum cardinality of the returned bitmap is cardinality_limit.

Syntax:

subBitmap(bitmap, offset, cardinality_limit)

Arguments:

  • bitmap (Bitmap) - The input bitmap.
  • offset (UInt32) - The position of the first element of the subset.
  • cardinality_limit (UInt32) - The maximum number of elements in the subset.

Returns:

A subset of the input bitmap.

Example:

SELECT bitmapToArray(subBitmap(bitmapBuild([1,2,3,4,5,6,7,8,9,10]), 3, 4)) AS subset;

Result:

| subset    |
|-----------|
| [4,5,6,7] |

In this example, subBitmap returns a subset of the bitmap starting from the 4th element (offset 3) and including up to 4 elements. The result is then converted to an array for display.

​
bitmapContains

Checks whether a bitmap contains a specific element.

Syntax:

bitmapContains(bitmap, needle)

Arguments:

  • bitmap (Bitmap): The input bitmap.
  • needle (UInt32): The element to search for in the bitmap.

Returns:

  • 1 if the bitmap contains the needle element. [UInt8]
  • 0 if the bitmap does not contain the needle element. [UInt8]

Example:

SELECT
  taco_id,
  toppings_bitmap,
  bitmapContains(toppings_bitmap, 3) AS has_salsa
FROM taco_orders;

Result:

| taco_id | toppings_bitmap | has_salsa |
|---------|-----------------|-----------|
| 1       | [1,2,3,4]       | 1         |
| 2       | [1,2,4]         | 0         |
| 3       | [2,3,5]         | 1         |

In this example, bitmapContains checks if each taco’s toppings bitmap contains the topping with ID 3 (assuming 3 represents salsa). The result is 1 if salsa is present and 0 if it’s not.

​
bitmapHasAny

Checks whether two bitmaps intersect (have any elements in common).

Syntax:

bitmapHasAny(bitmap1, bitmap2)

Arguments:

  • bitmap1 (Bitmap): The first bitmap object
  • bitmap2 (Bitmap): The second bitmap object

Returns:

  • 1 if the bitmaps intersect (have at least one element in common)
  • 0 if the bitmaps do not intersect

Example:

SELECT
  bitmapHasAny(
    bitmapBuild([1, 2, 3, 4, 5]),
    bitmapBuild([3, 4, 5, 6, 7])
  ) AS has_intersection,
  bitmapHasAny(
    bitmapBuild([1, 2, 3]),
    bitmapBuild([4, 5, 6])
  ) AS no_intersection
FROM taco_orders;

Result:

| has_intersection  | no_intersection |
|-------------------|-----------------|
| 1                 | 0               |

If bitmap2 contains exactly one element, consider using bitmapContains instead as it is more efficient for that case.

​
bitmapHasAll

Returns 1 if the first bitmap contains all elements of the second bitmap, otherwise 0. If the second bitmap is empty, returns 1.

Syntax:

bitmapHasAll(bitmap1, bitmap2)

Arguments:

  • bitmap1 (Bitmap): The first bitmap object to check.
  • bitmap2 (Bitmap): The second bitmap object to check against.

Returns:

  • 1 if bitmap1 contains all elements in bitmap2
  • 0 otherwise
  • 1 if bitmap2 is empty

Example:

SELECT
  bitmapHasAll(
    bitmapBuild([1,2,3,4,5]),
    bitmapBuild([3,4,5])
  ) AS all_tacos_available,
  bitmapHasAll(
    bitmapBuild([1,2,3]),
    bitmapBuild([3,4,5])
  ) AS some_tacos_missing

Result:

| all_tacos_available | some_tacos_missing  |
|---------------------|---------------------|
| 1                   | 0                   |

In this example:

  • The first bitmapHasAll returns 1 because [1,2,3,4,5] contains all elements of [3,4,5].
  • The second bitmapHasAll returns 0 because [1,2,3] does not contain all elements of [3,4,5].

​
bitmapCardinality

Returns the cardinality (number of set bits) of a bitmap.

Syntax:

bitmapCardinality(bitmap)

Arguments:

  • bitmap (Bitmap): A bitmap object.

Returns:

The number of set bits in the bitmap. [UInt64]

Example:

SELECT bitmapCardinality(bitmapBuild([1, 2, 3, 4, 5])) AS count;

Result:

| count |
|-------|
| 5     |

In this example, bitmapCardinality counts the number of elements in the bitmap created from the array [1, 2, 3, 4, 5].

​
bitmapMin

Computes the smallest bit set in a bitmap, or UINT32_MAX if the bitmap is empty.

Syntax:

bitmapMin(bitmap)

Arguments:

  • bitmap (Bitmap): Bitmap object.

Returns:

The smallest bit set in the bitmap, or UINT32_MAX (4294967295) if the bitmap is empty. [UInt32]

Example:

SELECT bitmapMin(bitmapBuild([3, 5, 7, 9])) AS min_taco_id;

Result:

| min_taco_id |
|-------------|
| 3           |

If the bitmap is empty, the function returns UINT32_MAX (4294967295).

​
bitmapMax

Computes the greatest bit set in a bitmap, or 0 if the bitmap is empty.

Syntax:

bitmapMax(bitmap)

Arguments:

  • bitmap (Bitmap): Bitmap object.

Returns:

The greatest bit set in the bitmap, or 0 if empty. [UInt32]

Example:

SELECT bitmapMax(bitmapBuild([1, 2, 3, 4, 5])) AS max_taco_id;

Result:

| max_taco_id |
|-------------|
| 5           |

In this example, bitmapMax finds the largest taco ID (5) from a bitmap of taco IDs.

​
bitmapTransform

Replaces bits in a bitmap based on provided mapping arrays.

Syntax:

bitmapTransform(bitmap, from_array, to_array)

Arguments:

  • bitmap (Bitmap): The input bitmap to transform.
  • from_array (Array(UInt32)): Array of bit values to replace.
  • to_array (Array(UInt32)): Array of new bit values to set.

Returns:

A new bitmap with the specified bits replaced. [Bitmap]

  • The from_array and to_array must have the same length.
  • For each index i, if the bitmap contains from_array[i], it is replaced with to_array[i].
  • The result depends on the order of elements in from_array and to_array.

Example:

SELECT
  bitmapToArray(
    bitmapTransform(
      bitmapBuild([1, 2, 3, 4, 5]),
      cast([2, 4] as Array(UInt32)),
      cast([10, 20] as Array(UInt32))
    )
  ) AS transformed_bitmap;

Result:

| transformed_bitmap |
|--------------------|
| [1,3,5,10,20]      |

In this example, the bitmap [1,2,3,4,5] is transformed by replacing 2 with 10 and 4 with 20, resulting in [1,3,5,10,20].

​
bitmapAnd

Computes the logical AND (intersection) of two bitmaps.

Syntax:

bitmapAnd(bitmap1, bitmap2)

Arguments:

  • bitmap1 (Bitmap): The first bitmap object
  • bitmap2 (Bitmap): The second bitmap object

Returns:

A new bitmap object containing the intersection of the input bitmaps. [Bitmap]

Example:

SELECT
  bitmapToArray(
    bitmapAnd(
      bitmapBuild([1,2,3,4,5]),
      bitmapBuild([3,4,5,6,7])
    )
  ) AS intersection;

Result:

| intersection |
|--------------|
| [3,4,5]      |

​
bitmapOr

Computes the logical OR (disjunction) of two bitmaps.

Syntax:

bitmapOr(bitmap1, bitmap2)

Arguments:

  • bitmap1 (Bitmap): The first bitmap object.
  • bitmap2 (Bitmap): The second bitmap object.

Returns:

A new bitmap object representing the union of the input bitmaps.

Example:

SELECT
  bitmapToArray(
    bitmapOr(
      bitmapBuild([1,3,5,7,9]),
      bitmapBuild([2,4,6,8,10])
    )
  ) AS combined_taco_ids;

Result:

| combined_taco_ids         |
|---------------------------|
| [1,2,3,4,5,6,7,8,9,10]    |

In this example, bitmapOr combines two bitmaps representing odd and even taco IDs, resulting in a bitmap containing all IDs from 1 to 10. The bitmapToArray function is used to convert the result back to an array for display.

​
bitmapXor

Performs a bitwise XOR operation on two bitmaps.

Syntax:

bitmapXor(bitmap1, bitmap2)

Arguments:

  • bitmap1 (Bitmap): The first bitmap object.
  • bitmap2 (Bitmap): The second bitmap object.

Returns:

A new bitmap object containing the result of the XOR operation. [Bitmap]

Example:

SELECT
  bitmapToArray(
    bitmapXor(
      bitmapBuild([1,2,3]),
      bitmapBuild([3,4,5])
    )
  ) AS xor_result;

Result:

| xor_result |
|------------|
| [1,2,4,5]  |

​
bitmapAndnot

Computes the logical AND of the first bitmap with the negation of the second bitmap.

Syntax:

bitmapAndnot(bitmap1, bitmap2)

Arguments:

  • bitmap1 (Bitmap): The first bitmap operand.
  • bitmap2 (Bitmap): The second bitmap operand to be negated.

Returns:

A new bitmap containing the result of bitmap1 AND (NOT bitmap2). [Bitmap]

Example:

SELECT bitmapToArray(bitmapAndnot(
  bitmapBuild([1,2,3,4,5]),
  bitmapBuild([3,4,5,6,7])
)) AS result;

Result:

| result |
|--------|
| [1,2]  |

In this example, bitmapAndnot performs the following operation:

  1. Takes the first bitmap [1,2,3,4,5]
  2. Negates the second bitmap [3,4,5,6,7] to get [1,2]
  3. Performs a logical AND between [1,2,3,4,5] and [1,2]
  4. Returns the result [1,2]

This function is useful for finding elements that are in the first set but not in the second set.

​
bitmapAndCardinality

Returns the cardinality of the logical AND (conjunction) of two bitmaps.

Syntax:

bitmapAndCardinality(bitmap1, bitmap2)

Arguments:

  • bitmap1 (Bitmap): The first bitmap object
  • bitmap2 (Bitmap): The second bitmap object

Returns:

The number of set bits in the result of ANDing the two input bitmaps. [UInt64]

Example:

SELECT bitmapAndCardinality(
  bitmapBuild([1, 2, 3, 4, 5]),
  bitmapBuild([3, 4, 5, 6, 7])
) AS common_taco_toppings;

Result:

| common_taco_toppings |
|----------------------|
| 3                    |

In this example, bitmapAndCardinality calculates how many taco toppings are common between two sets of toppings represented as bitmaps. The result 3 indicates that there are 3 toppings (3, 4, and 5) present in both bitmaps.

​
bitmapOrCardinality

Returns the cardinality of the logical OR (disjunction) of two bitmaps.

Syntax:

bitmapOrCardinality(bitmap1, bitmap2)

Arguments:

  • bitmap1 (Bitmap): First bitmap object
  • bitmap2 (Bitmap): Second bitmap object

Returns:

The number of set bits in the result of ORing the two input bitmaps. [UInt64]

Example:

SELECT bitmapOrCardinality(
  bitmapBuild([1, 2, 3]),
  bitmapBuild([3, 4, 5])
) AS taco_combinations;

Result:

| taco_combinations  |
|--------------------|
| 5                  |

In this example, bitmapOrCardinality calculates how many unique elements are available when combining two sets. The first bitmap represents [1, 2, 3] and the second represents [3, 4, 5]. The OR operation results in [1, 2, 3, 4, 5], and the cardinality (count of unique elements) is 5.

​
bitmapXorCardinality

Returns the cardinality of the XOR (exclusive or) of two bitmaps.

Syntax:

bitmapXorCardinality(bitmap1, bitmap2)

Arguments:

  • bitmap1 (Bitmap): The first bitmap object
  • bitmap2 (Bitmap): The second bitmap object

Returns:

The cardinality (number of set bits) of the XOR of the two input bitmaps. [UInt64]

Example:

SELECT bitmapXorCardinality(
  bitmapBuild([1,2,3,4,5]),
  bitmapBuild([3,4,5,6,7])
) AS xor_cardinality;

Result:

| xor_cardinality |
|-----------------|
| 4               |

In this example, the XOR of the two bitmaps is [1,2,6,7], which has a cardinality of 4.

This function efficiently computes the cardinality of the XOR without materializing the full result bitmap, which can be useful for large bitmaps.

​
bitmapAndnotCardinality

Returns the cardinality of the AND-NOT operation of two bitmaps.

Syntax:

bitmapAndnotCardinality(bitmap1, bitmap2)

Arguments:

  • bitmap1 (Bitmap): The first bitmap object.
  • bitmap2 (Bitmap): The second bitmap object.

Returns:

The number of elements in bitmap1 that are not in bitmap2. [UInt64]

Example:

SELECT
  bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) AS bitmap1,
  bitmapToArray(bitmapBuild([3, 4, 5, 6, 7])) AS bitmap2,
  bitmapAndnotCardinality(
    bitmapBuild([1, 2, 3, 4, 5]),
    bitmapBuild([3, 4, 5, 6, 7])
  ) AS andnot_cardinality;

Result:

| bitmap1        | bitmap2          | andnot_cardinality   |
|----------------|------------------|----------------------|
| [1, 2, 3, 4, 5] | [3, 4, 5, 6, 7] | 2                    |

In this example, bitmapAndnotCardinality calculates the number of elements in bitmap1 that are not in bitmap2. The result is 2, corresponding to the elements [1, 2] which are in bitmap1 but not in bitmap2.

Was this page helpful?

BitComparison