MaxCompute:ブルームフィルターインデックス (ベータ版)

利点と欠点

• 利点

  • 高効率: 挿入およびクエリ操作のリソース消費量は、通常のインデックスのリソース消費量よりも少なくなります。

  • 省スペース: ビットアレイが使用されます。 2 ³²= 4294967296。 これは、4,200万ビットのビットアレイが512 MBのメモリスペースしか占有しないことを示しています。

    説明

    ビット配列によって占有されるメモリスペースは、次の式を使用して計算されます。4294967296/8/1024/1024 = 512 MB

• デメリット

  特定の偽陽性確率 (FPP) が存在する。 セット外の要素は、セット内の要素と誤って見なされる可能性があります。 ただし、ほとんどのシナリオでは、空のファイルを読み取るためのリソース消費は全体的な効率に影響を与えず、結果は最終的なビジネスの正確性に影響を与えません。

シナリオ

• テーブル内の1つ以上の列を条件として使用してポイントクエリのフィルタリングを実行する場合、明らかなフィルタリング効果を持つクエリ列にBloomフィルターインデックスを作成できます。

• クラスター化されたテーブルのフィールドに対して、クラスター化キー以外のポイントクエリのフィルタリングを実行する場合は、クエリ列にBloomフィルターインデックスを作成できます。

• クラスタ化キー、ソートキー、またはZorder関数を使用してクラスタ化テーブル内のデータをソートした後、挿入された列にBloomフィルターインデックスを作成して、データをさらにフィルタリングできます。

制限事項

• ブルームフィルタインデックスは、=やinなどの等価比較演算子に適しています。 ブルームフィルタインデックスは、> 、>=、<、<= などの演算子が関与する範囲クエリと、IS NULLおよびIS not NULLが関与するクエリではサポートされません。

• フィルタリング効果は、データ分布に依存する。 データ分布が離散的である場合、Bloomフィルターインデックスを使用してもデータフィルター効果は明らかではありません。

• ブルームフィルタインデックスは、DECIMAL、INTERVAL_DAY_TIME、INTERVAL_YEAR_MONTH、またはSTRUCT、MAP、ARRAY、JSONなどの複合型ではサポートされていません。

Bloomフィルターインデックスの生成

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 ......

Bloomフィルターインデックスの使用

データをクエリする前に、次のコマンドを実行して、Bloomフィルターインデックス機能を有効にします。

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

次のコードは、Bloomフィルターインデックスを有効にする方法の例を示しています。

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

次のLogViewスナップショットは、job_1がファイルの剪定にBloomフィルターインデックスを使用するインデックスジョブであることを示しています。

[サマリ] タブの名前にbfが付いているテーブルは、Bloomフィルターインデックスファイルに対応する仮想テーブルです。

テーブルのBloomフィルターインデックスの照会

SHOW INDEXES ON <table_name>;

パラメーター：

table_name: インデックスが属するテーブルの名前。

Bloomフィルターインデックスの削除

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

パラメーター：

• idx_name: インデックスの名前。

• table_name: インデックスが属するテーブルの名前。

Bloomフィルターインデックスのプロパティの変更

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

パラメーター：

• idx_name: インデックスの名前。

• table_name: インデックスが属するテーブルの名前。

例1: 共通のパーティションテーブルに基づいてブルームフィルターインデックスを作成する

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] タブに次のキーワードが表示されている場合、Bloomフィルターインデックスは正常にマージされます。

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スナップショットは、Bloomフィルターインデックスが有効になったことを示しています。 名前の末尾にbfが付いているテーブルは、Bloomフィルターインデックスファイルに対応する仮想テーブルです。

   

6. テーブルのBloomフィルターインデックスを照会します。

   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. Bloomフィルターインデックスのプロパティを変更します。

   前の手順で返された情報に基づいて、numitemsの元のプロパティ値が1000000されます。 次のステートメントを実行して、プロパティ値を10000に変更し、変更結果を表示します。

   -- Change the property value.
   ALTER INDEX call_center_test_idx01 ON call_center_test SET IDXPROPERTIES('fpp' = '0.03', 'numitems'='10000');
   
   -- View the property.
   SHOW INDEXES ON call_center_test;

   次の結果が返されます。

例2: ハッシュクラスタ化パーティションテーブルに基づいてBloomフィルターインデックスを作成する

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テーブルにデータをアップロードします。 次のサンプルコマンドを使用して、MaxComputeクライアントのbinディレクトリにscope2.csvファイルをアップロードします。

      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] タブに次のキーワードが表示されている場合、Bloomフィルターインデックスは正常にマージされます。

   

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スナップショットは、Bloomフィルターインデックスが有効になったことを示しています。 名前の末尾にbfが付いているテーブルは、Bloomフィルターインデックスファイルに対応する仮想テーブルです。

   

例3: パーティションテーブルのZorderデータソートに基づいてBloomフィルターインデックスを作成する

1. データを準備します。 詳細については、「例2: ハッシュクラスター化パーティションテーブルに基づいてブルームフィルターインデックスを作成する」の手順1をご参照ください。

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. 次のコマンドを実行して、2つのJARパッケージをリソースとしてアップロードします。

      -- Add resources.
      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パーティションテーブルにデータを書き込みます。

      -- Set the odps.sql.validate.orderby.limit parameter to false.
      SET odps.sql.validate.orderby.limit=false;
      
      -- Write data.
      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] タブに次のキーワードが表示されている場合、Bloomフィルターインデックスは正常にマージされます。

      

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スナップショットは、Bloomフィルターインデックスが有効になったことを示しています。 名前の末尾にbfが付いているテーブルは、Bloomフィルターインデックスファイルに対応する仮想テーブルです。