ApsaraDB for SelectDB：BITMAP精準去重

概述

ApsaraDB for SelectDB支援Bitmap類型，在Aggregate資料模型中，Bitmap類型的Value欄位可以和集合的交並集彙總函式配合，實現資料的精確去重功能。

傳統資料倉儲中的Bitmap去重功能，對億層級以上Bitmap大基數的交並集計算效能較差，有兩個主要原因：一是當Bitmap基數較大（超過1 GB）時，網路和磁碟IO處理時間比較長；二是叢集在掃描資料後，會全部傳輸到頂層節點進行並集運算，給頂層單節點帶來壓力。

SelectDB針對分布式環境進行了最佳化設計，可以將Bitmap列的值按照Hash分桶劃分打散，使得不同值儲存在不同的分桶中，保證不同分桶的Bitmap值是正交的，基數大幅降低。當查詢時，先分別對不同分桶中的正交Bitmap進行彙總計算，然後再由頂層節點將彙總計算後的值合并輸出。如此會大大提高計算效率，也解決了頂層節點的單點計算瓶頸問題。

使用樣本

1. 建表時需要使用Aggregate資料模型，Value欄位的資料類型是Bitmap，彙總函式是集合的交並集合函式，例如Bitmap_UNION，同時需要建立一個列用來表示分桶ID，樣本如下。

   說明

   用於分桶ID的列和BUCKETS要設定合理，用於分桶ID的列的基數至少是BUCKETS的5倍以上，以使資料Hash分桶後盡量均衡。

   CREATE TABLE `user_tag_Bitmap` (
     `tag` bigint(20) NULL COMMENT "使用者標籤",
     `hid` smallint(6) NULL COMMENT "分桶id",
     `user_id` Bitmap Bitmap_UNION NULL COMMENT ""
   ) ENGINE=OLAP
   AGGREGATE KEY(`tag`, `hid`)
   COMMENT "OLAP"
   DISTRIBUTED BY HASH(`hid`) BUCKETS 3

   在建立表的Schema中增加了列hid，作為Hash分桶列，可代表user_id列值的範圍。

2. 資料匯入。

   LOAD LABEL user_tag_Bitmap_test
   (
       DATA INFILE('hdfs://abc')
       INTO TABLE user_tag_Bitmap
       COLUMNS TERMINATED BY ','
       (tmp_tag, tmp_user_id)
       SET (
           tag = tmp_tag,
           hid = ceil(tmp_user_id/500),
           user_id = to_Bitmap(tmp_user_id)
       )
   )
   # 注意：500這個數不固定，可按需調整

3. 匯入資料的格式如下，兩列資料分別為tag、user_id。

   11111111,1
   11111112,2
   11111113,3
   11111114,4
   ...

   在匯入資料時，SelectDB會對列user_id的Bitmap值的Range進行切割。例如，user_id在[1,5000000)區間時hid值相同，會分配到一個分桶內，因此每個分桶內到的Bitmap都是正交的。以這種方式進行切割，可以利用桶間Bitmap值正交的特性，大幅降低集合交並集的計算量。

4. 在完成匯入之後，可以通過調用Bitmap函數來進行Bitmap的相關操作。具體可調用的Bitmap函數，可以參考SQL手冊中的Bitmap函數章節。

正交Bitmap函數

Bitmap查詢提供了Bitmap_orthogonal_intersect函數、orthogonal_Bitmap_intersect_count​函數、orthogonal_Bitmap_union_count函數、orthogonal_Bitmap_expr_calculate函數、orthogonal_Bitmap_expr_calculate_count函數供正交情境使用。下文將分別介紹這五種函數。

Bitmap_orthogonal_intersect

對欄位的Bitmap交集，求計算出的具體值。

文法

orthogonal_Bitmap_intersect(Bitmap_column, column_to_filter, filter_values)

參數說明

樣本

SELECT Bitmap_COUNT(orthogonal_Bitmap_intersect(user_id, tag, 13080800, 11110200)) FROM user_tag_Bitmap WHERE tag IN (13080800, 11110200);

查詢執行時彙總分為2層：在第一層，SelectDB先按filter_values對tag進行過濾，然後對SQL中的所有彙總Key列（此處為tag列）的Bitmap求交集；在第二層，SelectDB對所有來源於第一層的Bitmap值迴圈求並集。

orthogonal_Bitmap_intersect_count​

對欄位的Bitmap交集，求值的數量。文法同intersect_count函數，但實現不同。

文法

orthogonal_Bitmap_intersect_count(Bitmap_column, column_to_filter, filter_values)

參數說明

樣本

SELECT orthogonal_Bitmap_intersect_count(user_id, tag, 1150000, 1150001, 390006) FROM user_tag_Bitmap WHERE tag IN (1150000, 1150001, 390006);

查詢執行時彙總分為2層：在第一層，SelectDB先按filter_values對tag進行過濾，然後對SQL中的所有彙總Key列（此處為tag列）的Bitmap求交集，再計算結果Bitmap的count值；在第二層，SelectDB對所有來源於第一層的count值迴圈求sum。

orthogonal_Bitmap_union_count

對欄位的Bitmap並集，求值的數量。文法同Bitmap_union_count，但實現不同。

文法

orthogonal_Bitmap_union_count(Bitmap_column)

參數說明

樣本

SELECT orthogonal_Bitmap_union_count(user_id) FROM user_tag_Bitmap WHERE tag IN (1150000, 1150001, 390006);

查詢執行時彙總分為2層：在第一層，SelectDB先對所有Bitmap求並集，再計算結果Bitmap的count值；在第二層，SelectDB對所有來源於第一層的count值迴圈求sum。

orthogonal_Bitmap_expr_calculate

對錶達式的Bitmap交/並/差集合，求計算出的具體值。

文法

orthogonal_Bitmap_expr_calculate(Bitmap_column, filter_column, input_string)

參數說明

樣本

SELECT orthogonal_Bitmap_expr_calculate(user_id, tag, '(833736|999777)&(1308083|231207)&(1000|20000-30000)') FROM user_tag_Bitmap WHERE tag IN (833736,999777,130808,231207,1000,20000,30000);

查詢執行時彙總分為2層：在第一層，SelectDB先解析input_string擷取tag過濾條件，然後進行資料過濾，過濾後的資料按照input_string運算式進行Bitmap計算；在第二層，SelectDB對所有來源於第一層的Bitmap值求並集，並返回最終Bitmap結果。

orthogonal_Bitmap_expr_calculate_count

對錶達式的Bitmap交/並/差集合，求計算出的值的數量，文法和參數同orthogonal_Bitmap_expr_calculate。

文法

orthogonal_Bitmap_expr_calculate_count(Bitmap_column, filter_column, input_string)

參數說明

樣本

SELECT orthogonal_Bitmap_expr_calculate_count(user_id, tag, '(833736|999777)&(1308083|231207)&(1000|20000-30000)') FROM user_tag_Bitmap WHERE tag IN (833736,999777,130808,231207,1000,20000,30000);

查詢執行時彙總分為2層：在第一層，SelectDB先解析input_string擷取tag過濾條件，然後進行資料過濾，過濾後的資料按照input_string運算式進行Bitmap計算，最後計算結果Bitmap的count值；在第二層，SelectDB對所有來源於第一層的Bitmap值求並集，並返回最終Bitmap結果。