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文件。