X

Big Data、Data Integration、Data Lakeに関するテクノロジー、製品・サービス情報、セミナー情報などをお届けします

Big Data SQL - Storage Index

Big Data SQLの強力な機能である、Storage Indexについて説明しましょう。はじめに、Storage Indexという名前は誤解を招くかもしれませんが、それは動的であり、データスキャン後に自動的に作成されます。特別なコマンドや特別な何かを実行する必要はありません。(B-Tree Indexのように)再構成のようなメンテナンスの必要もありません。ワークロードを実行するとそのうちにより良い性能を得ることができます。
Storage IndexはBig Data SQLのための完全に新しいもの、というわけではありません。Oracle Exadataもこの機能を持っており、Big Data SQLではそれを再利用しています。

どのように動くか

主要な点は、スキャンの単位にメタデータを作成することです。例えば、あるクエリ(いくつかの述語をWHERE句に持つ、例えばwhere id =123のような)でHDFSブロックをスキャンするとします。もしこのブロックが行を返さないなら、このブロックに対し、この列に対する統計値(最大値や最小値)を作成します。

次回スキャンでは、この統計値を使用して、スキャンをスキップすることができます。

これはユニークな列値に対して非常に強力な機能です。Hadoopの場合、Storage IndexのためのFine-Granuleユニットは、HDFSブロックです(HDFSブロック単位に最大値・最小値を持ちます)。ご存知のように、HDFSブロックはとても大きく(Big Data Appliance ではデフォルト 256MB)もしスキップすることが出来れば、大幅な性能向上が得られるでしょう。

クエリはグラニュル単位でスキャンされ、もし行を返さなければ、Storage Indexが作成されます(もしブロック内で1行でもHitする場合は、Storage Indexはこのブロックに対しては作成されません)。
HDFSデータは通常3つのコピーを持ちます。性能を最大化するにはStorage Indexからの便益をできるだけ速く得ることであり、Big Data SQL(3.1以降)ではホスト(Hadoopのノード)の順番によって決定されます。
一度テーブルをスキャンしStorage Indexを最初のレプリカに作成すると、2回目のスキャンではコピーに対してもStorage Indexが適切に使用されます。
では、Storage Indexの悪い例・良い例を紹介しましょう。ユニークな列を持つテーブルがあります。

SQL> SELECT num_distinct FROM user_tab_col_statistics
WHERE table_name = 'STORE_SALES_CSV'
AND COLUMN_NAME = 'SS_TICKET_NUMBER';

num_distinct 
------------ 
849805312

これはとても大きなテーブルです。(多くの行を持ちます)

SQL> select count(1) from STORE_SALES_CSV

-------------------
6 385 178 703 

これらが意味するのは、それぞれの値は7~8回登場すような、極めてSelectiveな列(選択性が高い列)であるということです。(テーブル全体では900.1GBのデータセット)。Storage Indexがどのように動くかを見るのに、以下のようなクエリを実行します。(2行を返す)。

SQL> select count(1) from STORE_SALES_CSV where SS_TICKET_NUMBER=187378862;

1度目の実行は、大量のIOとCPUを消費し10.6分かかりました。2回目以降の実行は極めて高速で、3秒で終わりました(なぜなら、Storage Indexにより、実行したクエリのWhere句条件に確実にマッチしないブロックを知ることができるから)。Storage Indexの効果を知るために、以下のSQLを実行してセッション統計を確認します。

SQL> SELECT n.name,  CASE NAME

          WHEN 'cell XT granule predicate offload retries' THEN

   VALUE
  WHEN 'cell XT granules requested for predicate offload' THEN
           VALUE
          ELSE
           round(VALUE / 1024 / 1024 / 1024,2)
       END Val,
       CASE NAME
          WHEN 'cell XT granule predicate offload retries' THEN
           'Granules'
          WHEN 'cell XT granules requested for predicate offload' THEN
           'Granules'
          ELSE
           'GBytes'
       END Metric
  FROM v$mystat   s,
       v$statname n
 WHERE s.statistic# = n.statistic#
   AND n.name IN ('cell XT granule IO bytes saved by storage index',
                  'cell XT granule bytes requested for predicate offload')
 ORDER BY Metric;

------------------------------------------------------------------------------------- 

cell XT granule IO bytes saved by storage index         899.86  GBytes
cell XT granule bytes requested for predicate offload   900.11  GBytes

 

この統計によると、READしたのは256MB(=cell XT granule bytes requested for predicate offload - cell XT granule IO bytes saved by storage index)だけであり、これはHDFSブロック1つということです。

最初のスキャン

実環境においてStorage Indexを無効化することは推奨しませんが、1つだけ副次効果があります。Storage Indexを有効化している場合は、無効化している場合と比較し、最初の1回目のスキャンが遅くなる可能性がある点です。先の例では、最初のスキャンは10.6分でしたが、2回目以降の実行はすべて数秒でした。

ストレージインデックスを無効化すると、1回目も2回目もそれ以降も変わらず同じ時間がかかります。(約5.1分)

これらの実行時間について纏めると以下です。

  • 実行時間(ストレージインデックスが有効)
    • 1回目のスキャン: 10.3分
    • 2回目のスキャン:  3秒
  • 実行時間(ストレージインデックスを無効化したとき)
    • 1回目のスキャン: 5.1分
    • 2回目のスキャン: 5.1分

セレクティブではない述語を持つクエリ(WHERE句であまり絞り込めないクエリ)

先の例を考慮すると、Storage Indexは1回目の実行で性能劣化をもたらしていますが、セレクティブではない述語をもつクエリに対してはどうなるか、を、確認してみると興味深いです。このテーブルはSS_QUANTITY列を持ち、それは1~100の、100種類の値しかありません。

 

SQL> SELECT num_distinct FROM user_tab_col_statistics
WHERE table_name = 'STORE_SALES_CSV'
AND COLUMN_NAME = 'SS_QUANTITY';

------------ 
100

SQL> SELECT min(SS_QUANTITY, max(SS_QUANTITY) FROM STORE_SALES_CSV

----   ------ 
0       100

ストレージインデックスが有効な状態で次のクエリを3回実行しました。

SQL> select count(1) from STORE_SALES_CSV where SS_QUANTITY=82;

すると3回とも実行時間は5.3分でした。これはたくさんの行がHitします。(ただしSELECT結果としてはCOUNTしているので1行ですが)

しかし、0行Hitするような検索、つまり、WHERE SS_QUANTITY=-1のように存在しない述語を指定すると、

SQL> select count(1) from STORE_SALES_CSV where SS_QUANTITY=-1;

実行時間は冒頭の例のように、1回目の実行は10.5分、2回目以降は3秒で完了します。

最後にStorage Indexを無効化してテストを繰り返すと、いずれも5.3分でした。

この内容を纏めると以下のとおりです。

  • 実行時間(Storage Indexが有効)
    • 1回目(多くの行がHitする場合): 5.3分
    • 2回目(多くの行がHitする場合): 5.3分
    • 1回目(0行がHitする場合): 10.5分
    • 2回目(0行がHitする場合): 3秒
  • 実行時間(Storage Indexが無効化している場合)
    • 1回目(多くの行がHitする場合): 5.3分
    • 2回目(多くの行がHitする場合): 5.3分
    • 1回目(0行がHitする場合): 5.3分
    • 2回目(0行がHitする場合): 5.3分

これらをもとに以下のことが推測できます。

  1. Storage Indexはブロックが行を返さない場合のみ作成される
  2. ブロックが1行でも行を返すなら、SI(Storage Index)は作成されない
  3. つまり、1回目の実行で性能劣化がなければ、2回目以降の実行でSIによる性能向上は得られないことを意味する

Order By

先程の例で、セレクティブではない述語を持つクエリ(ソートしない限りはStorage Indexにとってよくないクエリ)を見てきました。

データがソートされている場合は、どうでしょうか?新しいデータセット(テーブル)を、オリジナルの表をベースに、ソートしながらHiveQLで作成します。

hive> create table csv.store_sales_quantity_sort stored as textfile
as select * from csv.store_sales order by SS_QUANTITY;

その後、多くの行がHitする先のクエリを2回実行します。(FROM STORE_SALES_CSV を、今作成したHive表:store_sales_quantity_sortを元にした表に置き換えて実行しています)

多くの行がHitするクエリであるにもかかわらず、2回目の実行が高速化していることがわかります。Storage Indexが使われました。このことを統計値から証明します。

cell XT granule IO bytes saved by storage index         601.72 GBytes
cell XT granule bytes requested for predicate offload   876.47 GBytes

列がどのようにソートされているかを分析したりするには、このツールも使えます。

Bucketing

性能を大幅に向上させるもうひとつのトリックはBucketingです。それは値の種類の数を知っている場合や、distinct valueの最大値を知っている場合にのみ有効です。もし事前にQuery述語を知っていれば(上の例ではSS_QUANTITY=<値>)以下のように最適化された形でデータを準備するかもしれませんね。

次の文では100個のファイルを作成し、それぞれのファイルはそれぞれに対応する値を持ちます(理想的な場合はそうなるが、そうではない場合はHASH分散のようになる)。

hive> CREATE TABLE csv.store_sales_quantity_bucketed(
  ss_sold_date_sk bigint,
  ss_sold_time_sk bigint,
  ss_item_sk bigint,
  ss_customer_sk bigint,
  ss_cdemo_sk bigint,
  ss_hdemo_sk bigint,
  ss_addr_sk bigint,
  ss_store_sk bigint,
  ss_promo_sk bigint,
  ss_ticket_number bigint,
  ss_quantity int,
  ss_wholesale_cost double,
  ss_list_price double,
  ss_sales_price double,
  ss_ext_discount_amt double,
  ss_ext_sales_price double,
  ss_ext_wholesale_cost double,
  ss_ext_list_price double,
  ss_ext_tax double,
  ss_coupon_amt double,
  ss_net_paid double,
  ss_net_paid_inc_tax double,
  ss_net_profit double)
CLUSTERED BY (SS_QUANTITY) INTO 100 BUCKETS 
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '|'
stored as textfile; 
hive> set hive.enforce.bucketing = true;
hive> insert overwrite table csv.store_sales_quantity_bucketed select * from csv.store_sales; 
Oracle外部表を作成し、クエリを2回実行すると、Storage Indexのもたらす効果を確認できます。
外部表を作成し、以下のクエリを実行します。

SQL> select count(1) from STORE_SALES_CSV_QUANTITY_BUCK  where SS_QUANTITY= 82;
.... 
elapsed time: 822 sec
SQL> select count(1) from STORE_SALES_CSV_QUANTITY_BUCK  where SS_QUANTITY= 82; 
.... 
elapsed time: 8 sec 
SQL> SELECT * FROM xt_stat;
cell XT granule IO bytes saved by storage index           867.53  GBytes
cell XT granule bytes requested for predicate offload     876.47 GBytes 

2回目の実行では、実行時間が大幅に削減できている理由として、8.94GBのデータしかREADしていません。もしWHERE句の述語に現れる列でバケットすれば、BucketingとStorage Indexによって、大幅な性能改善をもたらします。

JOINとStorage Index

ブルームフィルターを使ったJOINにおいて、Storage Indexはとても強力で便利な機能になります。このことを先の例を使って示しましょう。STORE_SALES_CSV_QUANTITY_BUCKと、小さい表(2行のみを持つ)をJOINします。

SQL> CREATE TABLE test_couple_rows AS SELECT 3 q FROM dual UNION ALL SELECT 4 q FROM dual;

では大きなファクト表(SS_QUANTITY列でバケットされている)と、SS_QUANTITY列をJOIN述語としてJOINします。
(Bloom Filterを使うかどうかはOptimizer判断ですが、それが選択されたとします。)

SQL>  SELECT /*+ use_hash(tt ss)*/  COUNT(1)
  FROM test_couple_rows tt,
       STORE_SALES_CSV_QUANTITY_BUCK SS
 WHERE ss.Ss_Quantity = tt.q
 AND tt.q>0;

実行計画を確認します。Bloom Filterが使われていることがわかります。

クエリの実行時間は12秒でした。多くのIOを除去できたことが、以下の統計値からわかります。Storage Indexよ、ありがとう!

cell XT granule IO bytes saved by storage index           859.91 GBytes
cell XT granule bytes requested for predicate offload     876.47 GBytes 

本投稿は Big Data SQL Quick Start. Storage Indexes - Part10. を元に投稿しています。

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.