MaxCompute：Bloomfilter index（Beta）

Bloomfilter index的優缺點

• 優點

  • 效率高，插入和查詢操作的資源消耗都比普通索引低。

  • 節省空間的，採用位元組，2³²=4294967296，可以看到42億長度的位元組只需佔用512 MB的記憶體空間。

    說明

    位元組佔用的記憶體空間計算方式：4294967296/8/1024/1024=512 MB。

• 缺點

  有一定誤判率，可能會把不屬於該集合的元素誤判為存在於該集合，但是對大多數情境而言，消耗一點資源讀取無資料的檔案並不影響整體效率，且結果不影響最終的業務準確性。

Bloomfilter index適用情境

• 以表中的一列或多列作為條件進行點查過濾，則在有明顯過濾效果的查詢列上構建Bloomfilter index。

• 對聚簇表Clustering Key之外的欄位進行點查過濾，則在查詢列上構建Bloomfilter index。

• 聚簇表的Clustering Key、Sorted Key、Zorder函數排序後，在插入的資料列上再構建Bloomfilter index，可以獲得進一步的過濾效果。

Bloomfilter index使用限制

• 只適合等值判斷（包括=、in），不支援>、>=、<、<=等範圍查詢及IS NULL、IS NOT NULL查詢。

• 過濾效果的好壞依賴於資料分布，如果資料分布比較離散，即使使用Bloomfilter index，也可能無法有效地過濾資料。

• 不支援在DECIMAL、INTERVAL_DAY_TIME、INTERVAL_YEAR_MONTH，或複雜類型STRUCT、MAP、ARRAY、JSON上建立Bloomfilter index。

產生Bloomfilter index

CREATE BLOOMFILTER INDEX <index_name>
ON TABLE <table_name>
FOR COLUMNS(<column_name>)
IDXPROPERTIES('numitems'='xxx', 'fpp' = 'xx')
[COMMENT 'idxcomment']
;

INSERT OVERWRITE TABLE <table_name> [PARTITION <partition_spec>]
SELECT ......

使用Bloomfilter index

在查詢前執行如下語句，啟用布隆過濾器索引功能：

SET odps.sql.enable.bloom.filter.index=true;

Bloomfilter index生效的任務樣本如下：

SET odps.sql.enable.bloom.filter.index=true;
SELECT * FROM bloomfilter_index_test WHERE key=392 AND value="val_392";

查看Logview可知，下圖中的job_1即為使用Bloomfilter index進行檔案裁剪的job（稱為index job）。

Summary中攜帶bf尾碼的是虛擬表，對應Bloomfilter index檔案。

查看錶的Bloomfilter index

SHOW INDEXES ON <table_name>;

參數說明如下。

table_name：索引所在的表名。

刪除Bloomfilter index

DROP INDEX [IF EXISTS] <idx_name> ON TABLE <table_name>;

參數說明如下。

• idx_name：索引名稱。

• table_name：索引所在的表名。

更改Bloomfilter index屬性

ALTER INDEX <idx_name> ON <table_name> 
SET IDXPROPERTIES(['comment' = 'a'], ['fpp' = '0.01']);

參數說明如下。

• idx_name：索引名稱。

• table_name：索引所在的表名。

樣本1：基於普通分區表建立Bloomfilter index

1. 資料準備。

   SET odps.namespace.schema=true;
   SELECT * FROM bigdata_public_dataset.TPCDS_10G.call_center;

2. 建立分區測試表call_center_test。

   CREATE TABLE IF NOT EXISTS call_center_test(
       cc_call_center_sk BIGINT NOT NULL, cc_call_center_id CHAR(16) NOT NULL, 
       cc_rec_start_date DATE, cc_rec_end_date DATE, 
       cc_closed_date_sk BIGINT, cc_open_date_sk BIGINT, 
       cc_name VARCHAR(50), cc_class VARCHAR(50), 
       cc_employees BIGINT, cc_sq_ft BIGINT, 
       cc_hours CHAR(20), cc_manager VARCHAR(40), 
       cc_mkt_id BIGINT, cc_mkt_class CHAR(50), 
       cc_mkt_desc VARCHAR(100), cc_market_manager VARCHAR(40), 
       cc_division BIGINT, cc_division_name VARCHAR(50), 
       cc_company BIGINT, cc_company_name CHAR(50), 
       cc_street_number CHAR(10), cc_street_name VARCHAR(60), 
       cc_street_type CHAR(15), cc_suite_number CHAR(10), 
       cc_city VARCHAR(60), cc_county VARCHAR(30), 
       cc_state CHAR(2), cc_zip CHAR(10), 
       cc_country VARCHAR(20), cc_gmt_offset DECIMAL(5,2), 
       cc_tax_percentage DECIMAL(5,2)
   )
   PARTITIONED BY (ds STRING )
   ;

3. 建立索引。

   CREATE BLOOMFILTER INDEX call_center_test_idx01 
   ON table call_center_test 
   FOR columns(cc_call_center_sk) 
   IDXPROPERTIES('fpp' = '0.03', 'numitems'='1000000') 
   COMMENT 'cc_call_center_sk index';

4. 匯入資料。

   將公用資料集中的bigdata_public_dataset.TPCDS_10G.call_center表資料匯入至call_center_test。

   SET odps.namespace.schema=true;
   INSERT OVERWRITE TABLE call_center_test PARTITION (ds='20241115') SELECT * FROM bigdata_public_dataset.TPCDS_10G.call_center LIMIT 10000;

   Logview中Json Summary出現如下關鍵字則說明Bloomfilter index彙總成功：

5. 查詢資料。

   SET odps.sql.enable.bloom.filter.index=true;
   SELECT * FROM call_center_test WHERE cc_call_center_sk =10 AND ds='20241115';

   返回結果如下：

   +-------------------+-------------------+-------------------+-----------------+-------------------+-----------------+---------------+------------+--------------+------------+------------+----------------+------------+----------------------------+---------------------------------------------------------------------------------------+-------------------+-------------+------------------+------------+-----------------+------------------+----------------+----------------+-----------------+------------+---------------+------------+------------+---------------+---------------+-------------------+------------+
   | cc_call_center_sk | cc_call_center_id | cc_rec_start_date | cc_rec_end_date | cc_closed_date_sk | cc_open_date_sk | cc_name       | cc_class   | cc_employees | cc_sq_ft   | cc_hours   | cc_manager     | cc_mkt_id  | cc_mkt_class               | cc_mkt_desc                                                                           | cc_market_manager | cc_division | cc_division_name | cc_company | cc_company_name | cc_street_number | cc_street_name | cc_street_type | cc_suite_number | cc_city    | cc_county     | cc_state   | cc_zip     | cc_country    | cc_gmt_offset | cc_tax_percentage | ds         |
   +-------------------+-------------------+-------------------+-----------------+-------------------+-----------------+---------------+------------+--------------+------------+------------+----------------+------------+--------------+-------------+---------------------------------------------------------------------------------------+-------------------+-------------+------------------+------------+-----------------+------------------+----------------+----------------+-----------------+------------+---------------+------------+------------+---------------+---------------+-------------------+------------+
   | 10                | AAAAAAAAKAAAAAAA  | 1998-01-01        | 2000-01-01      | NULL              | 2451050         | Hawaii/Alaska | large      | 187          | 95744      | 8AM-8AM    | Gregory Altman | 2          | Just back responses ought  | As existing eyebrows miss as the matters. Realistic stories may not face almost by a  | James Mcdonald    | 3           | pri              | 3          | pri             | 457              | 1st            | Boulevard      | Suite B         | Midway     | Walker County | AL         | 31904      | United States | -6            | 0.02              | 20241115   |
   +-------------------+-------------------+-------------------+-----------------+-------------------+-----------------+---------------+------------+--------------+------------+------------+----------------+------------+----------------------------+---------------------------------------------------------------------------------------+-------------------+-------------+------------------+------------+-----------------+------------------+----------------+----------------+-----------------+------------+---------------+------------+------------+---------------+---------------+-------------------+------------+

   Logview中顯示如下資訊，表示Bloomfilter index已經生效，攜帶bf尾碼的是虛擬表，對應Bloomfilter index檔案。

   

6. 查看錶的Bloomfilter index。

   SHOW INDEXES ON call_center_test;

   返回結果如下：

   ID = 20241115093930589g9biyii****
   {"Indexes": [{
               "id": "aabdaeb10a7b4e99a94716dabad8****",
               "indexColumns": [{"name": "cc_call_center_sk"}],
               "name": "call_center_test_idx01",
               "properties": {
                   "comment": "cc_call_center_sk index",
                   "fpp": "0.03",
                   "numitems": "1000000"},
               "type": "BLOOMFILTER"}]}
   
   OK

7. 更改Bloomfilter index屬性。

   由上一步可知，原始numitems屬性值為1000000，執行以下命令，將其屬性值更改為10000。

   -- 更改屬性
   ALTER INDEX call_center_test_idx01 ON call_center_test SET IDXPROPERTIES('fpp' = '0.03', 'numitems'='10000');
   
   -- 查看屬性
   SHOW INDEXES ON call_center_test;

   返回結果如下：

樣本2：基於Hash Cluster分區表建立Bloomfilter index

1. 資料準備。

   1. 建立scope_tmp表。

      CREATE TABLE if NOT EXISTS scope_tmp(
          phone STRING, 
          card STRING, 
          machine STRING, 
          geohash STRING);

   2. 在MaxCompute用戶端（odpscmd）中使用Tunnel命令向scope_tmp表中上傳資料。以scope2.csv檔案為例，假設scope2.csv檔案位於MaxCompute用戶端（odpscmd）的bin目錄下，Tunnel命令如下：

      Tunnel upload scope2.csv scope_tmp;

2. 建立分區測試表scope_hash_pt。

   CREATE TABLE scope_hash_pt (
       phone STRING, 
       card STRING, 
       machine STRING,
       geohash STRING 
   )
   PARTITIONED BY (ds STRING)
   clustered by (phone) 
   sorted by (card) 
   into 512 buckets; 

3. 為分區表scope_hash_pt建立索引。

   CREATE BLOOMFILTER INDEX scope_hash_pt_index01 
   ON TABLE scope_hash_pt 
   FOR columns(card) 
   IDXPROPERTIES('fpp' = '0.03', 'numitems'='1000000') 
   COMMENT 'card index';

4. 匯入資料。

   INSERT OVERWRITE TABLE scope_hash_pt PARTITION (ds='20241115') SELECT * FROM scope_tmp;

   Logview中Json Summary出現如下關鍵字則說明Bloomfilter index彙總成功：

   

5. 查詢scope_hash_pt表的資料。

   SET odps.sql.enable.bloom.filter.index=true;
   SELECT * FROM scope_hash_pt WHERE card='073415764266290' and ds='20241115';

   返回結果如下：

   +-------------+-----------------+----------------+------------+------------+
   | phone       | card            | machine        | geohash    | ds         |
   +-------------+-----------------+----------------+------------+------------+
   | 1576426**** | 073415764266290 | 51133960245770 | fWbDDsf    | 20241115   |
   +-------------+-----------------+----------------+------------+------------+

   Logview中顯示如下資訊，表示Bloomfilter index已經生效，攜帶bf尾碼的是虛擬表，對應Bloomfilter index檔案。

   

樣本3：基於Zorder資料重分布的分區表建立Bloomfilter index

1. 資料準備。具體操作請參見Hash BloomFilter資料準備。

2. 建立分區測試表scope_zorder_pt。

   CREATE TABLE scope_zorder_pt(
       phone STRING, 
       card STRING, 
       machine STRING, 
       geohash STRING, 
       zvalue BIGINT
   )
   PARTITIONED BY (ds STRING)
   ;

3. 建立索引。

   CREATE BLOOMFILTER INDEX scope_zorder_pt_index01 
   ON TABLE scope_zorder_pt 
   FOR COLUMNS (card) 
   IDXPROPERTIES('fpp' = '0.05', 'numitems'='1000000')
   COMMENT 'idxcomment';

4. 匯入資料。

   1. 下載以下兩個JAR包，並將其存放在本地（假設存放路徑為D:\）。

      • odps-zorder-1.0-SNAPSHOT.jar

      • odps-zorder-1.0-SNAPSHOT-jar-with-dependencies.jar

   2. 執行以下命令將其上傳為JAR資源。

      -- 添加資源
      ADD JAR D:\odps-zorder-1.0-SNAPSHOT.jar;
      ADD JAR D:\odps-zorder-1.0-SNAPSHOT-jar-with-dependencies.jar;

   3. 建立函數，命令如下：

      CREATE FUNCTION zorder AS 'com.aliyun.odps.zorder.evaluateZValue2WithSize' USING ' odps-zorder-1.0-SNAPSHOT-jar-with-dependencies.jar';

   4. 使用函數寫入資料至分區測試表scope_zorder_pt。

      -- ORDER BY放開LIMIT限制
      SET odps.sql.validate.orderby.limit=false;
      
      --寫入資料
      INSERT OVERWRITE TABLE scope_zorder_pt PARTITION (ds='20241115') SELECT *,zorder(HASH(phone), 100000000, HASH(card), 100000000) AS zvalue FROM scope_tmp ORDER BY zvalue;

      Logview中Json Summary出現如下關鍵字則說明Bloomfilter index彙總成功：

      

5. 查詢資料。

   SET odps.sql.enable.bloom.filter.index=true;  
   SELECT * FROM scope_zorder_pt WHERE card='073415764266290' AND ds='20241115';

   返回結果如下：

   +-------------+-----------------+----------------+------------+---------------------+------------+
   | phone       | card            | machine        | geohash    | zvalue              | ds         |
   +-------------+-----------------+----------------+------------+---------------------+------------+
   | 1576426**** | 073415764266290 | 51133960245770 | fWbDDsf    | 3590549286038929408 | 20241115   |
   +-------------+-----------------+----------------+------------+---------------------+------------+

   Logview中顯示如下資訊，表示Bloomfilter index已經生效，攜帶bf尾碼的是虛擬表，對應Bloomfilter index檔案。