BITMAP_HASH

Name

BITMAP_HASH

Description

Calculating hash value for what your input and return a BITMAP which contain the hash value. MurMur3 is used for this function because it is high-performance with low collision rate. More important, the MurMur3 distribution is "simili-random"; the Chi-Square distribution test is used to prove it. By the way, Different hardware platforms and different SEED may change the result of MurMur3. For more information about its performance, see Smhasher (opens in a new tab).

Syntax

BITMAP BITMAP_HASH(<any_value>)

Arguments

<any_value> any value or expression.

Return Type

BITMAP

Remarks

Generally, MurMurHash 32 is friendly to random, short STRING with low collision rate about one-billionth. But for longer STRING, such as your path of system, can cause more frequent collision. If you indexed your system path, you will find a lot of collisions.

The following two values are the same.

SELECT bitmap_to_string(bitmap_hash('/System/Volumes/Data/Library/Developer/CommandLineTools/SDKs/MacOSX12.3.sdk/System/Library/Frameworks/KernelManagement.framework/KernelManagement.tbd')) AS a ,
       bitmap_to_string(bitmap_hash('/System/Library/PrivateFrameworks/Install.framework/Versions/Current/Resources/es_419.lproj/Architectures.strings')) AS b;

Here is the result.

+-----------+-----------+
| a         | b         |
+-----------+-----------+
| 282251871 | 282251871 |
+-----------+-----------+

Example

If you want to calculate MurMur3 of a certain value, you can

select bitmap_to_array(bitmap_hash('hello'))[1];

Here is the result.

+-------------------------------------------------------------+
| %element_extract%(bitmap_to_array(bitmap_hash('hello')), 1) |
+-------------------------------------------------------------+
|                                                  1321743225 |
+-------------------------------------------------------------+

If you want to count distinct some columns, using bitmap has higher performance in some scenes.

select bitmap_count(bitmap_union(bitmap_hash(`word`))) from `words`;

Here is the result.

+-------------------------------------------------+
| bitmap_count(bitmap_union(bitmap_hash(`word`))) |
+-------------------------------------------------+
|                                        33263478 |
+-------------------------------------------------+

Keywords

BITMAP_HASH,BITMAP

Best Practice

For more information, see also:

BITMAP_HASH64

BITMAP_HAS_ANY BITMAP_HASH64