X

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

Big Data SQL - データ型と型変換

Big Data SQLの性能を向上する簡単な方法を共有しましょう。Big Data SQLは2つの主要なピース(DatabaseとHadoop)を持つ、複雑なシステムです。それぞれのシステムはそれぞれのデータ型 - Oracle RDBMSとJava – を持ちます。Oracle Databaseからクエリを実行する度にデータ型変換が発生します。データ変換はCPU負荷の高い処理です。

AVRO, RCFile, ORC and Parquet files.

Hadoopサーバに着目すると、内部的には複数のコンポーネント、Hadoop Part(Data Node)とOracle Part(Smart Scan)があります。さらに”External Table Service”(Big Data SQLソフトウェアの一部)があり、ここでデータ型の変換を行います(ここでCPUをたくさん消費)

ここで良いニュースがあります。既にETLを実行済みでソースデータからParquetまたはORCに変換されているなら、適切なマッピング(Hiveデータ型からOracle Databaseのデータ型へ)によって、データ変換をすることなく、パススルーすることができます。変換表はこちら。百聞は一見に如かず、ということで例を見てみましょう。ここにHive表(ORCファイル)があります。

hive> show create table store_sales;
OK
CREATE TABLE store_sales(
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)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
'hdfs://bds30-ns/user/hive/warehouse/orc.db/store_sales'

そしてOracle RDBMSに外部表を2つ作成します。
1つ目のテーブルはHIVEのDOUBLE型をOracleのNUMBER(7,2)と定義します。

SQL> CREATE TABLE STORE_SALES_ORC_NUM    (
SS_SOLD_DATE_SK NUMBER(10,0),
SS_SOLD_TIME_SK NUMBER(10,0),
SS_ITEM_SK NUMBER(10,0),
SS_CUSTOMER_SK NUMBER(10,0),
SS_CDEMO_SK NUMBER(10,0),
SS_HDEMO_SK NUMBER(10,0),
SS_ADDR_SK NUMBER(10,0),
SS_STORE_SK NUMBER(10,0),
SS_PROMO_SK NUMBER(10,0),
SS_TICKET_NUMBER NUMBER(10,0),
SS_QUANTITY NUMBER(10,0),
SS_WHOLESALE_COST NUMBER(7,2),
SS_LIST_PRICE NUMBER(7,2),
SS_SALES_PRICE NUMBER(7,2),
SS_EXT_DISCOUNT_AMT NUMBER(7,2),
SS_EXT_SALES_PRICE NUMBER(7,2),
SS_EXT_WHOLESALE_COST NUMBER(7,2),
SS_EXT_LIST_PRICE NUMBER(7,2),
SS_EXT_TAX NUMBER(7,2),
SS_COUPON_AMT NUMBER(7,2),
SS_NET_PAID NUMBER(7,2),
SS_NET_PAID_INC_TAX NUMBER(7,2),
SS_NET_PROFIT NUMBER(7,2)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_HIVE
DEFAULT DIRECTORY DEFAULT_DIR
ACCESS PARAMETERS
(
com.oracle.bigdata.cluster=bds30
com.oracle.bigdata.tablename=orc.store_sales)
)
REJECT LIMIT UNLIMITED
PARALLEL ;

2つ目のテーブルはマッピングマトリックスの定義に従って、HiveのDOUBLE型を、OracleのNUMBER(7,2)ではなく、BINARY_DOUBLEで定義します。

パフォーマンステストでは10同時実行で行い、それぞれのクエリは列でフィルタリング(列の値でWHERE句による絞込が発生)する。1つ目のテーブルではDOUBLE→NUMBERのデータ型変換が発生し、2つ目のテーブルではDOUBLE→BINARY_DOUBLEのデータ型変換が発生します。

SQL> SELECT  COUNT(1) FROM STORE_SALES_ORC WHERE ss_net_paid_inc_tax=:bind
全10クエリは、ほぼ同時に終了しました。データ型の変換と実行時間は以下の通りです。
  • double (hive) → number (oracle) : 16.1分 ※マッピングは適切か?NO
  • double (hive) → binary_double (oracle): 10.8分 ※マッピングは適切か?YES

適切なデータ型のマッピングを行うことで、より良いパフォーマンスが得られました。背後で何が起きているか見てみましょう。

CPU使用率はどちらの場合も、とても高いです。

CPU上限に達しており、次はIOスループットが興味深いメトリックとなります。

1つ目のテーブルでは複雑なデータ変換であるため、Cell側で多くのCPU時間を使用しCPUバウンドになりそれ以上速くREADできません。2つ目のテーブルではデータ変換がないので、データを単にSmart Scanステップにパスするだけです。

TextFiles と Sequence Files.

AVRO, RCFile, ORC, Parquetであればいいのだが、テキストファイルやSequenceFileの場合は、全く違った動きをします。HadoopのCSVファイルのInputFormatはバイトストリームをREADします。テキスト行(通常は改行コードで終端とされている)を読み、列のパースを行います。どのように動くか順を追って説明します。

  1. Javaパートの”External Table Service”がHDFSブロックをREADし、Cのbyte bufferに送る
  2. Cパートの”External Table Service”が、バッファを改行コードでパースし行を見つける
  3. Cパートの”External Table Service”が、行を “|”でパースし列の値を見つける。ここでは常にString(文字列)として扱う。例えば”-11.52” のように。(数字ではなく常に文字列として)
  4. Cパートの”External Table Service”が、見つかった文字列 -11.52をOracleのNUMBER型に変換する

ここでの違いは、最初の例のString型→IEEE バイナリ浮動小数点(OracleのBINARY_DOUBLE)への変換よりも、String型→OracleのNUMBERへの変換のほうが、はるかに効率が良いことが挙げられます。
このことを確かめるために、先に作成したような例でテストを行います。今回はCSVファイルで格納したHive表に対し、外部表を作成しテストします。1つはNUMBERで、もう一つはBINALY_DOUBLEを使用します。

 

SQL> CREATE TABLE STORE_SALES_CSV_NUM    (
SS_SOLD_DATE_SK NUMBER(10,0),
SS_SOLD_TIME_SK NUMBER(10,0),
SS_ITEM_SK NUMBER(10,0),
SS_CUSTOMER_SK NUMBER(10,0),
SS_CDEMO_SK NUMBER(10,0),
SS_HDEMO_SK NUMBER(10,0),
SS_ADDR_SK NUMBER(10,0),
SS_STORE_SK NUMBER(10,0),
SS_PROMO_SK NUMBER(10,0),
SS_TICKET_NUMBER NUMBER(10,0),
SS_QUANTITY NUMBER(10,0),
SS_WHOLESALE_COST NUMBER(7,2),
SS_LIST_PRICE NUMBER(7,2),
SS_SALES_PRICE NUMBER(7,2),
SS_EXT_DISCOUNT_AMT NUMBER(7,2),
SS_EXT_SALES_PRICE NUMBER(7,2),
SS_EXT_WHOLESALE_COST NUMBER(7,2),
SS_EXT_LIST_PRICE NUMBER(7,2),
SS_EXT_TAX NUMBER(7,2),
SS_COUPON_AMT NUMBER(7,2),
SS_NET_PAID NUMBER(7,2),
SS_NET_PAID_INC_TAX NUMBER(7,2),
SS_NET_PROFIT NUMBER(7,2) )
ORGANIZATION EXTERNAL (
TYPE ORACLE_HIVE
DEFAULT DIRECTORY DEFAULT_DIR
ACCESS PARAMETERS (
com.oracle.bigdata.cluster=bds30
com.oracle.bigdata.tablename=csv.store_sales) )
REJECT LIMIT UNLIMITED
PARALLEL ;

2つ目の表ではNUMBER(7,2)の代わりに、BINARY_DOUBLEで定義します。

SQL> CREATE TABLE STORE_SALES_CSV (
SS_SOLD_DATE_SK NUMBER(10,0),
SS_SOLD_TIME_SK NUMBER(10,0),
SS_ITEM_SK NUMBER(10,0),
SS_CUSTOMER_SK NUMBER(10,0),
SS_CDEMO_SK NUMBER(10,0),
SS_HDEMO_SK NUMBER(10,0),
SS_ADDR_SK NUMBER(10,0),
SS_STORE_SK NUMBER(10,0),
SS_PROMO_SK NUMBER(10,0),
SS_TICKET_NUMBER NUMBER(10,0),
SS_QUANTITY NUMBER(10,0),
SS_WHOLESALE_COST BINARY_DOUBLE,
SS_LIST_PRICE BINARY_DOUBLE,
SS_SALES_PRICE BINARY_DOUBLE,
SS_EXT_DISCOUNT_AMT BINARY_DOUBLE,
SS_EXT_SALES_PRICE BINARY_DOUBLE,
SS_EXT_WHOLESALE_COST BINARY_DOUBLE,
SS_EXT_LIST_PRICE BINARY_DOUBLE,
SS_EXT_TAX BINARY_DOUBLE,
SS_COUPON_AMT BINARY_DOUBLE,
SS_NET_PAID BINARY_DOUBLE,
SS_NET_PAID_INC_TAX BINARY_DOUBLE,
SS_NET_PROFIT BINARY_DOUBLE)
ORGANIZATION EXTERNAL (
TYPE ORACLE_HIVE
DEFAULT DIRECTORY DEFAULT_DIR
ACCESS PARAMETERS (
com.oracle.bigdata.cluster=bds30
com.oracle.bigdata.tablename=csv.store_sales )
REJECT LIMIT UNLIMITED
PARALLEL;

性能テストのために、フルテーブルスキャンするクエリを使います。(実はこのクエリは統計情報を収集する際に実行するクエリです)

SQL> SELECT
to_char(COUNT(SS_SOLD_DATE_SK)),
substrb(dump(MIN(SS_SOLD_DATE_SK),16,0,64),1,240),
substrb(dump(MAX(SS_SOLD_DATE_SK),16,0,64),1,240),
...
to_char(COUNT(SS_NET_PROFIT)),
substrb(dump(MIN(SS_NET_PROFIT),16,0,64),1,240),
substrb(dump(MAX(SS_NET_PROFIT),16,0,64),1,240)
FROM STORE_SALES_CSV

2つ目の表にも同様に実行:

SQL> SELECT
to_char(COUNT(SS_SOLD_DATE_SK)),
substrb(dump(MIN(SS_SOLD_DATE_SK),16,0,64),1,240),
substrb(dump(MAX(SS_SOLD_DATE_SK),16,0,64),1,240),
...
to_char(COUNT(SS_NET_PROFIT)),
substrb(dump(MIN(SS_NET_PROFIT),16,0,64),1,240),
substrb(dump(MAX(SS_NET_PROFIT),16,0,64),1,240)
FROM STORE_SALES_CSV_NUM

結果は以下のように異なりました。

  • string (hive) → number (oracle): 18分
    • String→OracleのNUMBER型への変換を実施
  • string (hive) → binary_double (oracle): 64分
    • String→Oracleの binary_double 型への変換を実施、これは非常に高負荷な処理

以下のグラフからも、BINARY_DOUBLE型への変換はCPUを多く消費することが明白です。

重要

  • HadoopでParque, ORC, RC, AVROフォーマットでDOUBLE型を使用している場合にOracle RDBMSでBINARY_DOUBLE型を定義すると、この場合はデータ型変換は行われません。データをOracle SmartScanのために直接パスするだけです。
  • テキストファイルやSequencefileの場合は、常にデータ変換が行われます(なぜならText Inputformatは常に文字列とみなされるから)。その場合は、最も軽い方法を選択するべきです(BINARY_DOUBLEではなく、NUMBER(7,2)を選択します)。

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

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.