X

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

Big Data SQL - Semi-structured data

以前の投稿で、Schema on ReadとSchema on Writeの長所短所について触れました。結論として、HDFSはデータをオリジナルフォーマットで置いておくのに適していることが分かりました。時折、ユーザはXMLやJSONのような半構造化データ(semi-structured data)を持ちます。今回は、それをどのように扱うかを紹介します。

半構造化データのユースケース

HDFS上に半構造化データを格納する一般的なユースケースのひとつは、全てのオリジナルデータを格納し、そのうち部分的にリレーショナルデータベースに移動することです。これは日常的にはデータの一部が必要だが、その他の部分は極めて稀にアクセスされる(深い分析に必要になるかも知れない)ということかもしれません。例えば、以下のようなXMLフォーマットがあります。


 ...
 ...
 ...
 ... 
 ... 

日常的には、リレーショナルデータベースで名前と年齢のみ必要:

Name     Age
----     ----
....     .... 

他の列はごくまれにアクセスされます。この例では、HDFSはオリジナルデータを格納するのによいソリューションで、Big Data SQLはそれをデータベースからアクセスするのに良いツールのように見えます。そのことを例を使って説明しましょう。

Big Data SQLとXML

Big Data SQLを使ってXMLデータにクエリを実行するために、HiveメタデータをOracle XQuery for Hadoopを使ってHiveメタデータを定義する必要があります。それから、Oracle Databaseで外部表を定義します。

ではデータとDDLの例を見ていきましょう。

例として、何らかのマシンデータを挙げます(スマートメーターのようなものを想定)。

81708374/04/2013 12:290.197000
81708374/04/2013 12:590.296000
81708374/04/2013 13:290.24000
81708374/04/2013 13:590.253000
81708374/04/2013 14:290.24000

これをHDFS上に置きます。

[Linux] $ hadoop fs -put source.xml hdfs://cluster-ns/user/hive/warehouse/xmldata/

次にHiveメタデータの定義をします。

hive> CREATE EXTERNAL TABLE meter_counts(
  customer_key string,
  end_datetime string,
  general_supply_kwh float,
  off_peak_kwh int,
  gross_generation_kwh int,
  net_generation_kwh int)
ROW FORMAT SERDE
  'oracle.hadoop.xquery.hive.OXMLSerDe'
STORED AS INPUTFORMAT
 'oracle.hadoop.xquery.hive.OXMLInputFormat'
OUTPUTFORMAT
 'oracle.hadoop.xquery.hive.OXMLOutputFormat'
LOCATION 'hdfs://cluster-ns/user/hive/warehouse/xmldata/'
TBLPROPERTIES (
  'oxh-column.CUSTOMER_KEY'='./CUSTOMER_KEY',
  'oxh-column.End_Datetime'='./End_Datetime',
  'oxh-column.General_Supply_KWH'='./General_Supply_KWH',
  'oxh-column.Gross_Generation_KWH'='./Gross_Generation_KWH',
  'oxh-column.Net_Generation_KWH'='./Net_Generation_KWH',
  'oxh-column.Off_Peak_KWH'='./Off_Peak_KWH',
  'oxh-elements'='row');

XML表を作成するための情報は、Oracle XQuery for Hadoop (こちら)から参照できます。

次に、Oracle Databaseで、外部表としてHive表にリンクするよう定義します。

SQL> CREATE TABLE OXH_EXAMPLE 
   ( CUSTOMER_KEY VARCHAR2(4000), 
      END_DATETIME VARCHAR2(4000),
      GENERAL_SUPPLY_KWH BINARY_FLOAT,
      OFF_PEAK_KWH NUMBER,
      GROSS_GENERATION_KWH NUMBER,
      NET_GENERATION_KWH NUMBER
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_HIVE
      DEFAULT DIRECTORY DEFAULT_DIR
      ACCESS PARAMETERS
(com.oracle.bigdata.tablename=default.meter_counts)    
    )
   REJECT LIMIT UNLIMITED 
  PARALLEL;

完成です。XMLデータに対しクエリを実行する準備ができました。Oracle Database上で以下を実行します。

SQL> SELECT * FROM oxh_example WHERE ROWNUM <= 3;
...... 
8170837 4/04/2013 12:29 0.196999997 0 0 0
8170837 4/04/2013 12:59 0.296000004 0 0 0
8170837 4/04/2013 13:29 0.239999995 0 0 0

素晴らしい。XMLデータをデータベースの中で構造化して見えるようになりました。

Big Data SQLでもう1点素晴らしいのは、パースと処理の一部がHadoop側にプッシュダウンされることです。

例えば次のクエリを実行します。

SQL> SELECT COUNT(1) FROM oxh_example WHERE customer_key='8170837';

このクエリはHadoopにプッシュされ、データベースリソースを使用しません。Enterprise Managerからは”cell external table smart scan” イベントだけが確認できます。

Cloudera Managerからは3つのHadoopノードが使用されていることが分かります。同時刻にデータベースノードはIdle状態です。

以下の統計は、100GBのうち8KBだけがデータベース側に送られてきたことを示します(残りはCell側(=Hadoop側)でフィルターされています)。

SQL> SELECT n.name, VALUE
  FROM v$mystat   s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name LIKE '%XT%';
... 
cell interconnect bytes returned by XT smart scan         8192 Bytes
cell XT granule bytes requested for predicate offload     115035953517 Bytes

Big Data SQLとJSON

半構造化データでもう一つ有名なものはJSON形式です。Oracle Databaseはここにも良い意味で驚きがあります。Oracle Database の バージョン12cから、JSONデータに対しとても便利でフレキシブルなAPIが利用できます(データベース内の表でもデータベースの外(=外部表)であっても)。紹介しましょう。

例として、以下の入力データを使用します。

{wr_returned_date_sk:37890,wr_returned_time_sk:8001,wr_item_sk:107856,wr_refunded_customer_sk:5528377,wr_refunded_cdemo_sk:172813,wr_refunded_hdemo_sk:3391,wr_refunded_addr_sk:2919542,wr_returning_customer_sk:5528377,wr_returning_cdemo_sk:172813,wr_returning_hdemo_sk:3391,wr_returning_addr_sk:2919542,wr_web_page_sk:1165,wr_reason_sk:489,wr_order_number:338223251,wr_return_quantity:4,wr_return_amt:157.88,wr_return_tax:11.05,wr_return_amt_inc_tax:168.93,wr_fee:11.67,wr_return_ship_cost:335.88,wr_refunded_cash:63.15,wr_reversed_charge:87.15,wr_account_credit:7.58,wr_net_loss:357.98}

{wr_returned_date_sk:37650,wr_returned_time_sk:63404,wr_item_sk:1229906,wr_refunded_customer_sk:5528377,wr_refunded_cdemo_sk:172813,wr_refunded_hdemo_sk:3391,wr_refunded_addr_sk:2919542,wr_returning_customer_sk:5528377,wr_returning_cdemo_sk:172813,wr_returning_hdemo_sk:3391,wr_returning_addr_sk:2919542,wr_web_page_sk:1052,wr_reason_sk:118,wr_order_number:338223251,wr_return_quantity:19,wr_return_amt:3804.37,wr_return_tax:266.31,wr_return_amt_inc_tax:4070.68,wr_fee:47.27,wr_return_ship_cost:3921.98,wr_refunded_cash:1521.75,wr_reversed_charge:2100.01,wr_account_credit:182.61,wr_net_loss:4454.6}

 

このデータをLinuxのファイルシステム上からHDFS上に置き、1列のみのHive表として作成します。その後、Oracle Databaseから1列のみの外部表を作成します。

[Linux] $ hadoop fs -put source.json hdfs://cluster-ns/user/hive/warehouse/jsondata/
hive> CREATE TABLE json_string(
  json_str string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://cluster-ns/user/hive/warehouse/jsondata/'
SQL> CREATE TABLE WEB_RETURNS_JSON_STRING
   ( JSON_STR VARCHAR2(4000)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_HIVE
      DEFAULT DIRECTORY "DEFAULT_DIR"
      ACCESS PARAMETERS
(com.oracle.bigdata.tablename=json.json_string)
    )
   REJECT LIMIT UNLIMITED 
  PARALLEL;

OK、ここまでは無選別に見えます。なぜ1列だけのテーブルが必要なのでしょうか?

Oracle Database 12cでは、JSONデータの扱いが大きく拡張しており、それはBig Data SQLでも自動的に利用できるものです(Big Data SQLはOracle SQLであることは皆さん覚えてますよね)。

JSONをOracle SQLでパースすることは極めて簡単です。列名の後に. (ドット)をつけてフィールド名を書くだけです。

SQL> SELECT j.json_str.wr_returned_date_sk,
       j.json_str.wr_returned_time_sk
  FROM web_returns_json_string j
 WHERE j.json_str.wr_returned_time_sk = 8645
   AND ROWNUM <= 5;
... 
38195 8645
38301 8645
37820 8645
38985 8645
37976 8645

System Statをチェックすると、Hadoop側で多くのデータがフィルターされていることが分かります。

SQL> SELECT n.name, VALUE
  FROM v$mystat   s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name LIKE '%XT%';
... 
cell interconnect bytes returned by XT smart scan      507904 Bytes
cell XT granule bytes requested for predicate offload  16922334453 Bytes

注:パースとフィルターがHadoop側で行われています!

Big Data SQL と JSONにおける制限と回避策

JSON 文字列が4000文字になるまでは、テーブルにVARCHAR2(4000)の列を定義することで、全てはうまくいきます。しかし、4000文字以上のJSON文字列を扱う場合はどうなるでしょうか?それをCLOBで定義すると、このケースでは全てのパースとフィルターがデータベース側で行われます。

テストケース

先の例のテーブル定義(列定義がVARCHAR2)を使用し、クエリを実行します。

SQL> SELECT COUNT(1)
  FROM web_returns_json_string j
 WHERE j.json_str.wr_returned_time_sk = 8645;

クエリは75秒で完了しました。Enterprise Managerは、待機イベントの殆どが、スキャンをオフロードしストレージ側で行われたことを意味する”cell external table smart scan”であることを示しています。

次に、同じHive表に対し、Oracleの外部表を定義しますが、今回は列をCLOBで定義します。

SQL> CREATE TABLE WEB_RETURNS_JSON_STRING
   ( JSON_STR CLOB
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_HIVE
      DEFAULT DIRECTORY "DEFAULT_DIR"
      ACCESS PARAMETERS      (com.oracle.bigdata.tablename=json.json_string)
    )
   REJECT LIMIT UNLIMITED 
  PARALLEL ; 

同じクエリを実行します。

SQL> SELECT COUNT(1)
  FROM web_returns_json_string j
 WHERE j.json_str.wr_returned_time_sk = 8645;

実行時間はなんと90分!3600倍も遅いです。Enterprise Managerは待機イベントの殆どがDatabase CPUであることを示しています。これはCLOB列であるために、オフロードができなかったことを意味します。

Cloudera Managerはこれら2つのクエリの違いを示します。最初のクエリはCell側(3つのHadoopノード)のリソースを使用し、2つ目のクエリはデータベース側のみ使用しています。

4000文字を超えるJSONの場合、性能が劣化するという問題があることが分かりました。ではどうやって回避すればよいでしょうか?

簡単です!XMLの例のようにHiveメタストア内の構造を定義してHive表とOracle表のマッピングをするとよいです。

 hive> CREATE EXTERNAL TABLE j1_openx(
  wr_returned_date_sk bigint,
  wr_returned_time_sk bigint,
  wr_item_sk bigint,
  wr_refunded_customer_sk bigint,
  wr_refunded_cdemo_sk bigint,
  wr_refunded_hdemo_sk bigint,
  wr_refunded_addr_sk bigint,
  wr_returning_customer_sk bigint,
  wr_returning_cdemo_sk bigint,
  wr_returning_hdemo_sk bigint,
  wr_returning_addr_sk bigint,
  wr_web_page_sk bigint,
  wr_reason_sk bigint,
  wr_order_number bigint,
  wr_return_quantity int,
  wr_return_amt double,
  wr_return_tax double,
  wr_return_amt_inc_tax double,
  wr_fee double,
  wr_return_ship_cost double,
  wr_refunded_cash double,
  wr_reversed_charge double,
  wr_account_credit double,
  wr_net_loss double)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://cluster-ns/user/hive/warehouse/jsondata/'

JSON用のSarDeは何種類かあるが、性能の観点で個人的にorg.openx.data.jsonserde.JsonSerDeをお勧めします。

この後はOracleの外部表を定義すればOKです。

SQL> CREATE TABLE WEB_RETURNS_JSON_SD_OPENX 
   ( WR_RETURNED_DATE_SK NUMBER(10,0), 
WR_RETURNED_TIME_SK NUMBER(10,0), 
WR_ITEM_SK NUMBER(10,0), 
WR_REFUNDED_CUSTOMER_SK NUMBER(10,0), 
WR_REFUNDED_CDEMO_SK NUMBER(10,0), 
WR_REFUNDED_HDEMO_SK NUMBER(10,0), 
WR_REFUNDED_ADDR_SK NUMBER(10,0), 
WR_RETURNING_CUSTOMER_SK NUMBER(10,0), 
WR_RETURNING_CDEMO_SK NUMBER(10,0), 
WR_RETURNING_HDEMO_SK NUMBER(10,0), 
WR_RETURNING_ADDR_SK NUMBER(10,0), 
WR_WEB_PAGE_SK NUMBER(10,0), 
WR_REASON_SK NUMBER(10,0), 
WR_ORDER_NUMBER NUMBER(10,0), 
WR_RETURN_QUANTITY NUMBER(10,0), 
WR_RETURN_AMT NUMBER, 
WR_RETURN_TAX NUMBER, 
WR_RETURN_AMT_INC_TAX NUMBER, 
WR_FEE NUMBER, 
WR_RETURN_SHIP_COST NUMBER, 
WR_REFUNDED_CASH NUMBER, 
WR_REVERSED_CHARGE NUMBER, 
WR_ACCOUNT_CREDIT NUMBER, 
WR_NET_LOSS NUMBER
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_HIVE
      DEFAULT DIRECTORY DEFAULT_DIR
      ACCESS PARAMETERS
      ( com.oracle.bigdata.cluster:bds30
        com.oracle.bigdata.tablename:json.j1_openx))
   REJECT LIMIT UNLIMITED 
  PARALLEL ;

クエリを実行します。

SQL> SELECT COUNT(1) FROM WEB_RETURNS_JSON_SD_OPENX j WHERE j.wr_returned_time_sk = 8645; 

実行時間は141秒でした。Hadoop側へオフロードしていました。VARCHAR2のネイティブ処理と比較して2倍遅いですが、CLOBとしてネイティブ処理するよりも1800倍速いです。

まとめ

  1. HDFSはオリジナルフォーマットでのデータ格納に向いています
  2. Big Data SQLは半構造化データへの Wide Capabilityを提供します
  3. JSONファイルフォーマットにとって、out of the boxで扱うのに便利なAPIを持ちます

 

本投稿は Big Data SQL Quick Start. Semi-structured data. - Part12.   を元に投稿しています。

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.