X

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

Big Data SQL - Predicate Push Down機能

Big Data SQLは、version 3.0からPredicate Push Down(PPD)という素晴らしい機能があります。PPDはORCファイル、Parquetファイル、いくつかのNoSQL Database(HBaseのような)に対し有効です。ここではORCとParquetファイルのケースを説明しましょう。ORCとParquetはどちらのファイルも同じアプローチなので、ORCで説明します。もしこれらのファイルタイプについて詳しくなければ、ParquetORCに関する文章を読むことを推奨します。

ORCファイルはメタデータを内部に持ちます。値の最小値・最大値を10,000行ごとに持ちます。(10,000はデフォルト値で、orc.row.index.strideパラメータで定義されます)

このことは、スキャンすべきストライプとそうではないストライプを高速に見極められることを意味します。例えば、PK列のように、極めて一意性の高い列をスキャンするなら、これは多くのIO処理をスキップすることができます。
例を見てみましょう。2つにストライプ化されたテーブルを検索したいとします。それぞれのストライプに含まれる、B列の値の最大値・最小値は以下の図のとおりとします。


もしWHERE B=6 なら、最初のストライプはスキップ可能です。もしWHERE B=15なら、両方のストライプをスキップ可能です。

Big Data SQL ワークフロー

Big Data SQLがどのように動くか説明しましょう。STOCK_PRICES表がORC(またはParquet)形式でHDFS上にあるとします。以下のクエリを実行します。

SQL> SELECT ticker, AVG(price) OVER (PARTITION BY ticker ORDER BY mnth) as avg_price
FROM stock_prices
WHERE
mnth < :x
AND mnth > :y
AND stx_xchange = :z
ORDER BY mnth;

ワークフローは以下です。

1). DiskからデータをREADし、External Table Service 層に送る

最初のステップでは、DiskからデータをREADします。ここでは少しだがとても効果的な最適化を施すことができます。

  • Column Pruning:仮にstock_price表が100列あるとすれば、クエリで4列しかリストしていないので(ticker, price, mnth, stx_xchange)、他の96列はDiskから読まずに済む
  • Predicate Push Down:例では、WHERE句にある列と2つの文をプッシュする必要があります。ORC索引を使うとREADの一部分を除去することができます。WHERE句の指定に確実にマッチしないデータはREADしません。
mnth < :x AND mnth > :y
AND stx_xchange = :z


Column PruningとPredicate Push Downをどうやって実現するかを指し示す図を1枚お借りします。(引用元)

2). Oracle RDBMSフォーマットに変換し、SmartScanに送る

2番目のステップでは、HDFSフォーマット(Binary, Avro, ORC, Parquet)からOracle RDBMSフォーマットに変換します。これはとてもCPU intensiveなステップで、SarDeの適用とOracle RDBMSのデータ型への変換を行います。行や列を事前にプルーニング(除去)できていれば、CPU枯渇を防ぐことができますね。ひとつ前のステップの、Predicate Push Down機能よ、ありがとう!

3). Smart Scan

次のステップは、WHERE句に対しSmart Scanを適用します。

mnth < :x AND mnth > :y
AND stx_xchange = :z

Predicate Push Downがデータの一部を除去するものであれば、Smart Scanはきっちりと厳密にフィルターします。更に、ただPredicate(述語処理)だけではなく、関数処理を行います。スマートスキャンが可能な関数は以下のSQLで得られます。

SQL> SELECT * FROM v$sqlfn_metadata WHERE offloadable = 'YES';

4). Oracle RDBMS パート

このステップは私の絵の範囲外ですが、少しご紹介します。全ての関数がスマートスキャンで適用されるわけではありません(例えばPL/SQLやウィンドウ関数)。この部分はDatabaseにパスされます。今回のクエリの例では、AVG(price) OVER (PARTITION BY ticker ORDER BY mnth) as avg_price や、ORDER BYは、データベース層で実行されます。


 

Predicate Push Downの良い/悪いユースケース

Predicate Push Down機能の良い/悪いユースケースをテストするために、Intel Big Benchから巨大なテーブルを使用します。

SQL> SELECT COUNT(1)  FROM BDS.store_sales_orc;
6 385 178 703 rows

データはZLIB コーデックで圧縮されています。(ORCのデフォルト)

$ hadoop fs -du -s -h /user/hive/warehouse/orc.db/store_sales/
297.8 G  893.5 G  /user/hive/warehouse/orc.db/store_sales

以下のSQLで、テーブルの統計情報より、「最もSELECTIVEな列」、「最もSELECTIVEではない列」を確認します。user_tab_col_statistics(列の統計情報を格納)より、列の値の種類が最も多い列(最もSELECTIVEな列)と、最も少ない列(最もSELECTIVEではない列)を検索しています。

SQL> SELECT *  FROM (SELECT num_distinct,
               Column_name
          FROM user_tab_col_statistics
         WHERE table_name = UPPER('STORE_SALES_ORC')
         ORDER BY num_distinct FETCH FIRST 1 ROW ONLY)
UNION ALL
SELECT *
  FROM (SELECT num_distinct,
               Column_name
          FROM user_tab_col_statistics
         WHERE table_name = UPPER('STORE_SALES_ORC')
         ORDER BY num_distinct DESC FETCH FIRST 1 ROW ONLY);
-------------------------------------------
100        SS_QUANTITY
849805312  SS_TICKET_NUMBER
  • 最もSELECTIVEな列: SS_TICKET_NUMBER
  • 最もSELECTIVEではない列: SS_QUANTITY

ここから2つの外部表を作成します。STORE_SALES_ORC表は、Predicate Push Downが可能な表、もう片方STORE_SALES_ORC_NOPPD表は、その機能を無効にします。Predicate Push Downはデフォルトで有効ですが、無効にするには以下のようにCREATE TABLE時にcom.oracle.bigdata.ppd=FALSEパラメータを追加することで設定できます:

CREATE TABLE STORE_SALES_ORC_NOPPD   (
...
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_HIVE
      DEFAULT DIRECTORY "DEFAULT_DIR"
      ACCESS PARAMETERS 
 ACCESS PARAMETERS
      ( ...
       com.oracle.bigdata.ppd=FALSE
        )

ではテストをはじめましょう。

1). 「最もSELECTIVEではない列」でフィルタリング

SQL> SELECT COUNT(1) FROM store_sales_orc WHERE SS_QUANTITY=6;

63849869 

→実行時間: 89秒

SQL> SELECT COUNT(1) FROM store_sales_orc_noppd WHERE SS_QUANTITY=6; 

63849869

→実行時間: 84秒

2). 「最もSELECTIVEな列」でフィルタリング

SQL> SELECT COUNT(1) FROM store_sales_orc WHERE SS_TICKET_NUMBER=187378860; 

2

→実行時間: 9秒

SQL> SELECT COUNT(1) FROM store_sales_orc_noppd WHERE SS_TICKET_NUMBER=187378860; 

2

→実行時間: 87秒

 

3). Join

Predicate Push Down機能はJOIN処理をも加速化することができるかもしれません。全てのJOINではないが、WHERE句に述語処理があり、かつ暗黙的にNested Loopで実行されるような場合です。

大きなファクト表(store_sales_orc 表またはstore_sales_orc_noppd 表、それぞれ6,385,178,703 行)を、小さなディメンジョン表date_dim_orc 表(109,573行)とJOINしてみましょう。
デモ用途で、Nested Loopを強制させるようヒントを追加します。(本番システムではやらないように!Optimizerに任せましょう)

PPD(Predicate Push Down)が利用可能な表に対するクエリ:

SQL> SELECT /*+ use_nl(store_sales_orc)*/
 dt.d_year,
 SUM(ss_ext_sales_price) sum_agg
  FROM date_dim_orc    dt,
       store_sales_orc 
WHERE dt.d_date_sk = store_sales_orc.ss_sold_date_sk
AND dt.d_moy = 12
AND dt.d_dom = 24
AND dt.d_year = 2004
GROUP BY dt.d_year
ORDER BY dt.d_year,sum_agg DESC
FETCH FIRST 100 ROWS ONLY;
PPD不可の表に対しても、上のクエリのテーブル名store_sales_orcを、store_sales_orc_noppdに変えて実行します。

どちらも同じ実行計画を持ちます:


STORE_SALES表にPredicateを持ちます

 

PPD有効な表(STORE_SALES_ORC)で実行:実行時間  11秒
PPD無効な表(STORE_SALES_ORC_NOPPD)で実行:実行時間 128秒

STORE_SALES表(ファクト表)から小さなデータセットしか必要ではない場合にのみ、この機能の効果が得られます。データのほとんど全てが必要な場合は、それほど効果的ではありません。
注:この違いはJOINがNested Loopの場合のみ発生します

4). 累積のPredicate
Predicateは累積です、すなわち、「SELECTIVEではない列」(BDSにとっては嬉しくない)であっても、複数指定することで、Predicateが積み重なりデータセットはSELECTIVEになり(=とても絞り込まれた状態)性能も向上します。先の例で、5つの「SELECTIVEではない列」でPredicateを指定します。

SQL> SELECT /*+ MONITOR*/ COUNT(1)   FROM store_sales_orc SS
WHERE ss.Ss_Quantity = 84  
AND ss.Ss_Store_Sk = 21  
AND ss.Ss_Sold_Date_Sk = 38046  
AND ss.Ss_Promo_Sk = 90  
AND ss.ss_hdemo_sk = 3702;

 

Predicate Pushdownをどうやって確認するか

Predicate Pushdownが動作したかを確認するには、実行計画を確認する必要があります。その方法は一般的に2つあり、Enterprise ManagerまたはExplain Planです。

 

 

 

両方のケースでHDFS上の大きな表にPredicateが存在することを確認するべきです。

Findings

  1. Predicate Push DownはSelective Query(少ない行を返す)に効果的です。完璧に動作します。
  2. Predicate Push DownはUnselectiveな場合は少し遅くなります(全てのストライプのスキャン時に、インデックス検索分の余分なオーバヘッドがかかるため)。しかしその差は小さく、無視できます。
  3. Column Projectionは常に適用されます。データ型変換ではたくさんのCPUを必要とするがこれがセーブできるので、とてもよいことです。
  4. 大きなファクト表から少数のデータを必要とするようなJOINも、高速化されることがあります。(それがNested Loopで動く場合)

 

 

本投稿は Big Data SQL Quick Start. Predicate Push Down - Part6. を元に投稿しています。

 

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.