Orthogonal BITMAP calculation

    The solution is to divide the bitmap column values according to the range, and the values of different ranges are stored in different buckets, so as to ensure that the bitmap values of different buckets are orthogonal and the data distribution is more uniform. In the case of query, the orthogonal bitmap in different buckets is firstly aggregated and calculated, and then the top-level node directly combines and summarizes the aggregated calculated values and outputs them. This will greatly improve the computing efficiency and solve the bottleneck problem of the top single node computing.

    User guide

    1. Create a table and add hid column to represent bitmap column value ID range as hash bucket column
    2. Usage scenarios

    We need to use the aggregation model when building tables. The data type is bitmap, and the aggregation function is bitmap_ union

    The HID column is added to the table schema to indicate the ID range as a hash bucket column.

    Note: the HID number and buckets should be set reasonably, and the HID number should be set at least 5 times of buckets, so as to make the data hash bucket division as balanced as possible

    2. (
    3. DATA INFILE('hdfs://abc')
    4. INTO TABLE user_tag_bitmap
    5. COLUMNS TERMINATED BY ','
    6. SET (
    7. tag = tmp_tag,
    8. hid = ceil(tmp_user_id/5000000),
    9. user_id = to_bitmap(tmp_user_id)
    11. )
    12. ...

    Data format:

    Note: the first column represents the user tags, which have been converted from Chinese into numbers

    When loading data, vertically cut the bitmap value range of the user. For example, the hid value of the user ID in the range of 1-5000000 is the same, and the row with the same HID value will be allocated into a sub-bucket, so that the bitmap value in each sub-bucket is orthogonal. On the UDAF implementation of bitmap, the orthogonal feature of bitmap value in the bucket can be used to perform intersection union calculation, and the calculation results will be shuffled to the top node for aggregation.

    orthogonal_bitmap_intersect

    The bitmap intersection function

    Syntax:

    Parameters:

    the first parameter is the bitmap column, the second parameter is the dimension column for filtering, and the third parameter is the variable length parameter, which means different values of the filter dimension column

    Explain:

    on the basis of this table schema, this function has two levels of aggregation in query planning. In the first layer, be nodes (update and serialize) first press filter_ Values are used to hash aggregate the keys, and then the bitmaps of all keys are intersected. The results are serialized and sent to the second level be nodes (merge and finalize). In the second level be nodes, all the bitmap values from the first level nodes are combined circularly

    Example:

      orthogonal_bitmap_intersect_count

      To calculate the bitmap intersection count function, the syntax is the same as the original Intersect_Count, but the implementation is different

      Syntax:

      orthogonal_bitmap_intersect_count(bitmap_column, column_to_filter, filter_values)

      Parameters:

      Explain:

      on the basis of this table schema, the query planning aggregation is divided into two layers. In the first layer, be nodes (update and serialize) first press filter_ Values are used to hash aggregate the keys, and then the intersection of bitmaps of all keys is performed, and then the intersection results are counted. The count values are serialized and sent to the second level be nodes (merge and finalize). In the second level be nodes, the sum of all the count values from the first level nodes is calculated circularly

      orthogonal_bitmap_union_count

      Figure out the bitmap union count function, syntax with the original bitmap_union_count, but the implementation is different.

      Syntax:

      orthogonal_bitmap_union_count(bitmap_column)

      Explain:

      on the basis of this table schema, this function is divided into two layers. In the first layer, be nodes (update and serialize) merge all the bitmaps, and then count the resulting bitmaps. The count values are serialized and sent to the second level be nodes (merge and finalize). In the second layer, the be nodes are used to calculate the sum of all the count values from the first level nodes

      It is consistent with the scenario of orthogonal calculation of bitmap, such as calculation retention, funnel, user portrait, etc.

      Crowd selection:

      1. select orthogonal_bitmap_union_count(user_id) from user_tag_bitmap where tag in (13080800, 11110200);