Big Data SQLのセットアップ

Copyright © 2016, Oracle and/or its affiliates. All rights reserved.

クラウドでOracle Big Data SQL v3を大解剖ビッグデータの新しい有り方

日本オラクル株式会社クラウド・テクノロジー事業統括Cloud/Big Data/DISプロダクト本部プリンシパルセールスコンサルタント立山重幸

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

• 以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはできません。以下の事項は、マテリアルやコード、機能を提供することをコミットメント（確約）するものではないため、購買決定を行う際の判断材料になさらないで下さい。オラクル製品に関して記載されている機能の開発、リリースおよび時期については、弊社の裁量により決定されます。OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。文中の社名、商品名等は各社の商標または登録商標である場合があります。

3


本日の内容

オラクルのビッグデータ戦略

レベルセッティング(Big Data SQLのデモ)

クラウドで簡単～ DB – BDS – Hadoop ～環境構築

Big Data SQLを大解剖

1

2

3

4

4


1.オラクルのビッグデータ戦略

5


StrategyOur long term vision and strategy for data warehousing and big data

全てのデータを処理できる分析エンジン

6

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 7

お客様における近年の状況

NoSQL

Python

GraphRnode.js Java REST

JDBCJavaScript

SpringFramework

Parquet

HDFS

ORCHIVE

CASSANDRA

MONGOAVRO

HBASE

KAFKA

開発環境及びコネクタの拡大データマネジメントコンポーネントの拡大


お客様における近年の状況

NoSQL

Python


JDBCJavaScript

SpringFramework

Parquet

HDFS

ORCHIVE

CASSANDRA

MONGOAVRO

HBASE

KAFKA

オラクルはどのように適合するのか


• Oracleは、様々なシングルモデルのデータストアをサポート– Relational– Key/Value– XML

データ統合における２つの考え方オラクルは双方をサポート – どちらの考え方のお客様でも対応可能

データを統合• Oracle データベースは、マルチモデル永続化をサポート– Relational– XML – JSON– Text– Graph & Spatial

• Oracleデータベースは全てのデータベースオブジェクトへの統合的なアクセスを提供

- Spatial- Graph- OLAP

データマネジメントを統合

• Oracleでは、シングルモデルポリグロット環境をBig Data SQLを介して統合

Spatial

Graph

OLAP

NoSQL

Relational

XML

9


データ統合における２つの考え方JSONの例

データを統合データマネジメントを統合

10

Oracle DBをJSON Storeとして利用・柔軟な開発

-容易なフォーマット変更

-視認性の良さ

アプリケーション分析

Oracle DBのJSON データをSQLで利用・柔軟な分析

SQL

NOSQLをJSON Storeとして利用・柔軟な開発

アプリケーション

SQL

分析

NOSQLのJSON データをSQLで利用・柔軟な分析

Big Data SQL


One Fast, Secure SQL Queries over All your Dataデータマネジメントを統合

Oracle SQL

Python


JDBCJavaScript

SpringFramework

Parquet

HDFS

ORCHIVE

CASSANDRA

MONGOAVRO

HBASE

KAFKA

Hadoop


Oracleは、ガートナが提唱するロジカル・データウェアハウスを実現

企業が現在必要としているのは、種別や形式が多様化した社内、社外のデータを従来型の社内データと併せて管理／処理することのできるデータ分析管理ソリューションです。

データには、インタラクション・データやIoT（Internet of Things）の各種センサー等からの観測データが含まれる可能性もあります。こうしたデータも分析可能であることが当該市場のソフトウェアの新たな要件になっており、顧客は既存のエンタープライズ・データウェアハウス戦略を大きく超越する機能を求めています。

12


ロジカルデータウェアハウスの差別化要因1. DWHのための最先端のリレーショナルデータベース

– DWHシェアNo1–インメモリ技術

2. Big Data 製品との統合–幅広いポートフォリオ: HW, data management, tools, applications–オンプレミス、クラウドを同じテクノロジで実現

3. Oracle SQLでDWHやBig dataエコシステムに横断的にアクセス– Hadoop, NoSQLやOracleデータベースなどの様々なデータにアクセス可能– Exadata Storage Serverの技術を流用し高い性能を発揮

13


オラクルはHadoopプラットフォームでもNo1SCREEN

ONLY

Forrester Wave: Big Data Hadoop-Optimized Systemsオラクルの成熟したBDAがインパクトを与え続けているHadoopに最適化したシステムを初めて提供したベンダーオラクルはHadoopの包括的なセキュリティを提供するBDAはOracle DB & Exadataと非常に密に連携するBDAは事前構成済、事前テスト済、事前最適化済BDAは多くの補完ソフトウェアも含んでいる

投影のみ

14


2.レベルセッティング(Big Data SQLのデモ）

SQL access to all your data using Big Data SQL

http://www.oracle.com/technetwork/database/bigdata-appliance/oracle-bigdatalite-2104726.html

15


このデモは、オンラインで映画を配信しているサイトを題材にしています。

売上や注文データはOraleDBにありますが、クリック履歴は、JSONフォーマットでHadoopにレコメンド情報は、Key-ValueフォーマットでNoSQLに格納されています。

16


(補足)前提となっているアーキテクチャ

17

Webサイト

APP/Webサーバー

HDFS操作履歴

売上 GoldenGatefor BigData

YARN

リコメンド

機械学習

Why？Hadoop 安価にデータを保持できスケーラブルな分散処理が可能Kafka N:Nのメッセージ交換を非同期にスケーラブルな分散キューで対応可能NoSQL 安価に低レイテンシな処理が可能

KAFKA Hadoop


Hadoopに入っている、クリック履歴のJSONファイルです。

様々な顧客が様々な映画に対して、様々なアクションをしたログが記載されています。

18


先ほどのHadoop(HDFS)上のファイルに対して、ORACLE_HDFSアクセスドライバを使って、外部表を定義する事で、Oracle DBから簡単にアクセスする事が可能になります。

もちろん、NoSQLについても同様です。

これがBig Data SQLです。19

クリック履歴のHDFSにアクセスするためのOracleDDL レコメンドのNoSQLにアクセスするためのOracleDDL


作成した外部表(movielog_t)に対して、クエリーを実行できます。

Oracle Database12cの機能により、JSONファイルをクエリ時にパースできます。※BDSに限らずDBをJSONストアとして利用した場合でも同様のJSONクエリは可能です。

20


リダクションの設定も可能です。

21


リダクションされたcustid

もちろん、他の表とJOINも可能です。

22


(補足) クリック履歴+売上×SQLでどんな分析できる？• 離反しそうな重要顧客を探す(RFM分析)

23

項目ソース適用する関数Recency：顧客が最後にサイトにアクセスした日時クリック履歴 NTILE (5) over (order by max(time))Frequency：サイト上での顧客のアクティビティレベルクリック履歴 NTILE (5) over (order by count(1))Monetary：顧客が消費した金額売上 NTILE (5) over (order by sum(sales))

抽出条件(R:F:M)5:5:1

自社のサイトで下見をした後に他社のサイトで購入していると思われる顧客群

抽出条件(R:F:M)2以下:x:4以上

最近サイトにアクセスしていない重要顧客（Recencyのスコアが低く、Monetaryのス

コアが高い)


(補足) クリック履歴+売上×SQLでどんな分析できる？• 離反しそうな重要顧客を探す(RFM分析)

24

項目ソース適用する関数Recency：顧客が最後にサイトにアクセスした日時クリック履歴 NTILE (5) over (order by max(time))Frequency：サイト上での顧客のアクティビティレベルクリック履歴 NTILE (5) over (order by count(1))Monetary：顧客が消費した金額売上 NTILE (5) over (order by sum(sales))

これは、ユーザー毎のレコード数をカウントして、５つのグループに分けている↓

クリック回数が多いユーザーが上位に来てしまう（クリックは少ないけど、毎日来てくれる人が埋もれてしまう）

↓あるユーザーが何回（セッション）アクセスしてくれたかを割り出す必要がある


(補足) クリック履歴+売上×SQLでどんな分析できる？• ユーザー毎のセッション数と滞在時間(パターンマッチング)

25

ユーザアクション時間A ログイン 7/1 10:00

B ログイン 7/1 10:15

A クリック 7/1 10:16

A クリック 7/2 10:02

A クリック 7/2 10:05

C クリック 7/2 10:10・・・

ユーザセッションID 滞在時間A 1 16分A 2 3分B 1 xxx・・・

ビジネスルールクリック間が２時間以上空くものは、別セッションとする（映画見ている間はクリックされない）

クリック履歴ユーザの行動分析



26

タスクキーワード説明

1.データを体系化するPARTITION BY

ORDER BY

行を論理的にグループに分割/パーティション化する

パーティション内で行を論理的に順序付ける

2.ビジネス・ルールを定義する

PATTERN

DEFINE

AFTER MATCH

照合する必要があるパターン変数、照合する必要があるシーケンス、および照合する必要がある行数を定義する

パターン変数を定義する条件を指定する

一致が見つかった後にマッチング・プロセスを再開する場所を決定する

3.出力メジャーの定義

MEASURES

MATCH_NUMBER

CLASSIFIER

行パターン・メジャー列を定義する

パターン変数を適用する行を見つける

特定の行に適用するパターンの要素を特定する

4.出力の制御ONE ROW PER MATCH

ALL ROWS PER MATCH

各一致の出力のサマリー行を返す

各一致の行ごとにディテール行を1つ返す

MATCH_RECOGNIZE関数



27

MATCH_RECOGNIZE関数SELECT

hogeFROM hogehoge

MATCH_RECOGNIZE

(PARTITION BY cust_id ORDER BY time_id

MEASURES MATCH_NUMBER() AS session_id,

COUNT(*) AS no_of_events,

TO_CHAR(FIRST(bgn.time_id),'hh24:mi:ss') AS start_time,

TO_CHAR(LAST(sess.time_id),'hh24:mi:ss') AS end_time,

TO_CHAR(to_date('00:00:00','HH24:MI:SS') + (LAST(sess.time_id)-FIRST(bgn.time_id)),'hh24:mi:ss') AS mins_duration

ONE ROW PER MATCH

PATTERN (bgn sess+)

DEFINE

sess as time_id <= PREV(sess.time_id) + interval '2' hour

)

1.データを体系化する顧客ID単位で束にして、時系列にソート

2.ビジネス・ルールを定義する次の行の時間が２時間以内なら同一セッションとする

3.出力メジャーの定義セッション毎にナンバリング’MATCH_NUMBER()’して、カウントや時間差異を定義

4.出力の制御セッションID毎にサマリーするか(ONE ROW)、全件出力するか(ALL ROWS)



28

実行結果

クリック履歴を分析に使うための前準備が完了した状態

↓ここから、真に Frequencyが高いユーザーを評価できる


ユーザー毎のセッション数と売上が紐付けられるようになるため、顧客マスタと照合し、顧客属性別の行動分析などが可能になります。

29


(補足) その他、ビッグデータ関連で便利な12cの関数• APPROX_COUNT_DISTINCT()

–ビッグデータの大まかな把握（数%の精度誤差）–通常のCount Distinctより5～50倍高速

• FETCH句のPERCENT– Top N% のデータを抽出–・・・

ORDER BY sales DESCFETCH FIRST 1 PERCENT ROWS ONLY;

30


3.クラウドで簡単～ DB – BDS – Hadoop ～環境構築

•構築する環境の全体像•Hadoop環境(CDH)の構築•Big Data SQLのセットアップ

31


Big Data SQL 3.1 ハイライトより多くのお客様へご提供可能に• エンジニアドシステム以外のお客様もご利用可能になりました。一般的なH/Wでの Hadoop 及び、OracleデータベースClouderaとHortonworksをサポートもちろん、SmartScan等の各種固有機能はそのまま動作します。

32

Before BDS 3.0

BDSを利用可能なお客様

Hadoopご利用のお客様

After BDS 3.1


Big Data SQLを導入可能な組み合わせHadoop Oracle Database (12c only) on: Availability

Oracle Big Data Appliance w/Cloudera Oracle Exadata v1.0 -

Commodity Cloudera or Hortonworks Clusters Intel Commodity v3.0-

Oracle Big Data Appliance w/Cloudera Intel Commodity v3.1-

33

cloudera clouderaCloudera

Hortonworks


クラウドでBig Data SQLの検証環境を構築全体像

34

BDS Agent

Oracle12c

BDS srv

CDHBig Data SQL

Plan: Oracle Database Cloud Service

version: 12.1.0.2

Edition: Enterprise Edition – Extreme Performance※要RAC

Node： 2OCPU： 4 (2 × 2)

Plan: Oracle Compute Cloud Service

OS: Oracle Linux 6.6

Node： 3OCPU： 3 (1 × 3)

Shape oc1m

CDH version : 5.7Big Data SQL version : 3.0


Database Cloud Service 構成例

35


CDHクラスタ on Compute Cloud構成例

36

Oracle Compute Block Storage

/u03 /u02 /u01

HDFS

YARN

DN

NM

/var /opt/cloudera

管理ツールCM

CDH

Big Data SQLHadoop3(Master兼Slave)

80GB 80GB 80GB 80GB 80GBOracle Compute Block Storage

/u03 /u02 /u01

HDFS

YARN

DN

NM

/opt/cloudera

CDH


80GB 80GB 80GB 80GBOracle Compute Block Storage

/u03 /u02 /u01

HDFS

YARN

DN

NM

/opt/cloudera

CDH


80GB 80GB 80GB 80GB

NN

RM

Hive HS HM

NN : NameNodeDN : DataNodeRM : ResourceManagerNM : NodeManagerHS : HiveServer2HM : HiveMetastoreCM : Cloudera Manager


Cloudera Managerのインストール 1/7

37

Hadoop3環境でCloudera Managerのインストーラ―を入手し、起動$ wget http://archive.cloudera.com/cm5/installer/latest/cloudera-manager-installer.bin

$ chmod u+x cloudera-manager-installer.bin

$ sudo ./cloudera-manager-installer.bin



38

READMEを確認し、「NEXT」



39

License TERMを確認し、「NEXT」



40

License Acceptに、「Yes」



41

Oracle Binary Code License Agreementを確認し、「NEXT」



42

インストール開始



43

インストール完了


CDHクラスタの構築 1/15

44

Hadoop3のport7180をSSHポート転送し、Cloudera Managerにアクセスブラウザで「localhost:7180」ログインは admin / admin



45

Cloudera Enterprise Data Hub Editionのトライアルを選択し「続行」



46

CDHクラスタを構築するノード(Hadoop1 , Hadoop2 , Hadoop3)のホスト名を入力し、正しく検出できる事を確認



47

リポジトリの選択画面はデフォルト（Parcelの使用)のままOK



48

JDKインストールのチェックボックスをオンにする



49

シングルユーザーモードを有効化はオフ



50

SSHログインの資格情報を入力

opc

SSHの秘密鍵をアップロード



51

インストール (10分程度待ちます)



52

この先の手順でのエラーを回避するための手続き•JAVAのパスの通し直し

ln -s /usr/java/jdk1.7.0_67-cloudera /usr/java/default

•Cloudera-scmユーザにsudo権を割り当てsudo visudo

#Defaults requiretty ←コメントアウトする%cloudera-scm ALL=(ALL) NOPASSWD: ALL ←追加する



53

クラスタ構成の選択

BDSだけであればコアHadoopでOk

将来的にBDDなどの利用を見越してSparkも入

れておく



54

クラスタ構成の設定

各サービスの配置は「CDHクラスタ on Compute Cloud構成例」を参考に設定



55

リポジトリの確認

標準設定のまま、接続確認してOK



56

コンフィグ設定

/u01 , /u02 , /u03をHDFS領域として割り当て

る



57

構築実行



58

確認Cloudera Managerのホーム画面で、サービスが正しく動作している＝青信号

事を確認


Big Data SQLのセットアップ手順概要詳しくはマニュアル参照

59

Hadoop

①前提条件の確認、設定

②BDS Serverのインストール

③BDS Agentのインストーラの作成

DB

④Node1でBDS Agentのインストール

⑤Node nでBDS AgentのインストールSCP

http://docs.oracle.com/cd/E69290_01/doc.44/e71333/installing.htm


①前提条件の確認、設定 2.3.5 Installing on the Hadoop Cluster Management Server

•edeliveryサイトからBDSをダウンロード

Big Data SQL (3.0) V137415-01.zip Oracle Big Data SQL 3.0.0 cell software only 440.6 MB

V137419-01.zip Oracle Big Data SQL 3.0.0 installer for Cloudera Enterprise 625.4 MB V137420-01.zip Oracle Big Data SQL 3.0.0 installer for Hortonworks Data Platform 625.4 MB

・前提条件で足りないものを追加でインストール（例）・finger sudo yum install finger ・PERL LibXML sudo yum install perl-XML-LibXML・oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

yum install oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm ・oracle-instantclient12.1-jdbc-12.1.0.2.0-

yum install oracle-instantclient12.1-jdbc-12.1.0.2.0-1.x86_64.rpm

これ一つだけの入手でOK



•インストーラの展開

•コンフィグファイルの編集

•インストーラの実行

# unzip V137419-01.zip

# vi /tmp/BigDataSQL-CDH-3.0.0/bds-config.json

{"CLUSTER_NAME" : "cluster","CSD_PATH" : "/opt/cloudera/csd","DATABASE_IP" : "10.196.42.70","REST_API_PORT" : "7180","WEB_SERVER_PORT" : "81"

}

クラスタ名は、CMのAPIで確認可能curl -X GET -u "admin:admin" -i

http://10.196.42.62:7180/api/v12/clusters

# ./setup-bds bds-config.json


・・・Big Data SQL: Setup complete

•インストーラが終了後、プロセスの確認を行う

•Cloudera Managerでの確認

# ps -ef |grep bdsqlsrvoracle 11326 11307 2 04:14 ? 00:00:17 /opt/oracle/cell/cellsrv/bin/bdsqlsrv 100 5000 9 5042



③BDS Agentのインストーラの作成

•BDS Agent作成用シェルを実行

•インストーラディレクトリにJDKファイルを追加する

•コンフィグファイルを修正

•再度、Agent作成シェルを実行

•ここで作成された「bds-database-install.zip」を、DBサーバ（両ノード）に転送する

# /tmp/BigDataSQL-CDH-3.0.0/db/bds-database-create-bundle.sh

# cp /tmp/jdk-7u75-linux-x64.tar.gz /tmp/BigDataSQL-CDH-3.0.0/bds-database-install

Jdkはご自身でご用意ください

# vi /tmp/BigDataSQL-CDH-3.0.0/bds-database-install/db/create-bundle.env

jdktar='jdk-7u75-linux-x64.tar.gz‘ Jdk1.8から1.7に記述

変更

# /tmp/BigDataSQL-CDH-3.0.0/db/bds-database-create-bundle.sh

2.3.6 Creating the Database-Side Installation Bundle


2.3.7 Installing on the Oracle Database Server

•cellinit.oraファイルの作成

•インストーラの展開および実行

# mkdir -p /etc/oracle/cell/network-config/# chown oracle:dba /etc/oracle/cell/network-config# chmod ug+wx /etc/oracle/cell/network-config# su - oracle$ vi /etc/oracle/cell/network-config/cellinit.ora

ipaddress1=10.196.35.150/30_skgxp_ant_options=1_skgxp_dynamic_protocol=2

node1のIP

$unzip /tmp/bds-database-install.zip$/tmp/bds-database-install/bds-database-install.sh --ip-cell=10.196.35.150/30node1のIP

④Node1でBDS Agentのインストール


•cellinit.oraファイルの作成

•インストーラの展開および実行

# mkdir -p /etc/oracle/cell/network-config/# chown oracle:dba /etc/oracle/cell/network-config# chmod ug+wx /etc/oracle/cell/network-config# su - oracle$ vi /etc/oracle/cell/network-config/cellinit.ora

ipaddress1=10.196.35.150/30_skgxp_ant_options=1_skgxp_dynamic_protocol=2

node2のIP

$unzip /tmp/bds-database-install.zip$/tmp/bds-database-install/bds-database-install.sh --ip-cell=10.196.35.150/30node2のIP

⑤Node2でBDS Agentのインストール


最後にちょっとおまじない

67

•CDBで、下記のオブジェクトを削除

•PDBで、下記のオブジェクトを作成

drop directory ORA_BIGDATA_CL_cluster; drop directory ORACLE_BIGDATA_CONFIG; drop directory DEFAULT_DIR;

create or replace directory DEFAULT_DIR as '/u01/app/oracle/product/12.1.0.2/dbhome_1/bigdatasql/default_dir';

grant read, write on directory DEFAULT_DIR to public;

create or replace directory "ORA_BIGDATA_CL_cluster" as ''; grant read, write, execute on directory "ORA_BIGDATA_CL_cluster" to public;

create or replace directory ORACLE_BIGDATA_CONFIG as '/u01/app/oracle/product/12.1.0.2/dbhome_1/bigdatasql/bigdata_config';

grant read on directory ORACLE_BIGDATA_CONFIG to public;


サービスの起動と確認

68

Hadoop（CMで操作） DB

起動 $ crsctl start res bds_dbname_clustername

停止 $ crsctl stop res bds_dbname_clustername

確認 $ crsctl stat res bds_dbname_clustername

例）bds_orcl_cluseterプロセス名は、extprocbds_orcl_cluster

プロセス名は、bdsqlsrv


BDSの動作確認をしてみようNYタクシーのオープンデータを使って簡単な分析をしてみる

69

カラム説明VendorID CMT , LLC

tpep_pickup_datetime メーター開始時間tpep_dropoff_datetime メーター終了時間Passenger_count 乗車人数Pickup_longitude 乗車緯度Pickup_latitude 乗車経度RateCodeID -Store_and_fwd_flag -Dropoff_longitude 下車緯度Dropoff_ latitude 下車経度Payment_type 支払方法Fare_amount 乗車賃Extra 割増料金MTA_tax 税金Tip_amount チップ代Tolls_amount 通行料Total_amount 合計料金

・Hadoopマシンでデータを取得し、HDFSに登録する

wget https://storage.googleapis.com/tlc-trip-data/2015/yellow_tripdata_2015-01.csvwget https://storage.googleapis.com/tlc-trip-data/2015/yellow_tripdata_2015-02.csvwget https://storage.googleapis.com/tlc-trip-data/2015/yellow_tripdata_2015-03.csv

hadoop fs -mkdir /user/oracle/NYTAXIhadoop fs -put yellow*.csv /user/oracle/NYTAXI


Hiveで見てみる

70

CREATE EXTERNAL TABLE `yellow_tripdata`(`vendorid` string ,`tpep_pickup_datetime` string ,`tpep_dropoff_datetime` string ,`passenger_count` string ,`trip_distance` string ,`pickup_longitude` string ,`pickup_latitude` string ,`ratecodeid` string ,`store_and_fwd_flag` string ,`dropoff_longitude` string ,`dropoff_latitude` string ,`payment_type` string ,`fare_amount` int ,`extra` int ,`mta_tax` int ,`tip_amount` int ,`tolls_amount` int ,`improvement_surcharge` int ,`total_amount` double)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','LOCATION '/user/oracle/NYTAXI'tblproperties ('skip.header.line.count'=‘1’);

・Hadoopマシンで「hive」コマンドから、右記のDDLを実行

・基礎集計を行ってみる

select count(*) from yellow_tripdata;

38,551,116

データ件数は約3千8百万件select min(total_amount),max(total_amount),avg(total_amount) from yellow_tripdata;

-496.3 3950611.6 15.460082037579063

平均の金額は約15ドル


Big Data SQLで見てみる

71

・DBマシンで「sqlplus」から、右記のDDLを実行

CREATE TABLE yellow_tripdata(vendorid varchar2(20),tpep_pickup_datetime date,tpep_dropoff_datetime date,passenger_count number,trip_distance varchar2(20),pickup_longitude varchar2(20),pickup_latitude varchar2(20),ratecodeid varchar2(20),store_and_fwd_flag varchar2(20),dropoff_longitude varchar2(20),dropoff_latitude varchar2(20),payment_type varchar2(20),fare_amount number,extra number,mta_tax number,tip_amount number,tolls_amount number,improvement_surcharge number,total_amount number)ORGANIZATION EXTERNAL (TYPE ORACLE_HIVEDEFAULT DIRECTORY DEFAULT_DIR);

select count(*) from yellow_tripdata;

38,551,116

データ件数は約3千8百万件(もちろん同じ）select min(total_amount),max(total_amount),avg(total_amount), median(total_amount) from yellow_tripdata;

-496.3 3950611.6 15.460082 11.5

平均の金額は約15ドルだけど中央値は11.5ドル⇒外れ値である、395万ドルが入っているので、平均の場合、大きく外している（Hiveには、median関数がない)

・基礎集計を行ってみる


Big Data SQLでDB上にあるマスタとジョイン金種マスターをルックアップして集計

72

Payment_master

CREATE TABLE payment_master(payment_type VARCHAR2(1),payment_name VARCHAR2(10));INSERT INTO payment_master VALUES ('1', 'CASH');INSERT INTO payment_master VALUES ('2', 'CREDITCARD');INSERT INTO payment_master VALUES ('3', 'TICKET');INSERT INTO payment_master VALUES ('4', 'OTHER');

datafile

Yellow_tripdata(外部表)

BDS

JOIN

select m.payment_name , sum(t.fare_amount) , min(t.fare_amount) , max(t.fare_amount) , avg(t.fare_amount) , median(t.fare_amount) from yellow_tripdata t , payment_master m where t.payment_type= m.payment_type group by m.payment_name;


Data Visualization Desktopで可視化

73


4. Big Data SQLを大解剖

74


• (Hive) Partition Pruning– クエリ実行前にHiveのカタログを参照し、必要なパーティションのみにアクセス

– Oracleデータベースのメタデータによりpartition pruningを最適化(外部表のパーティショニング)

– Goal: IO 削減• Storage Index

– IOをスキップするためのブロックに関するマーキングをメタデータで管理

– Goal: IO削減• Smart Scan

– 必要なデータのみをOracleデータベースに返します

– Goal: データ移動の最小化

• Bloom Filtering – ブルーム・フィルタリングを使ってHadoopノードにプッシュダウン

– Goal: Joinの最適化及びデータもとでの処理• Predicate and Column Projection

Pushdown– カラムストア型のファイルフォーマット

(Parquet、ORC)対応– Goal:IO削減

75

Big Data SQLのパフォーマンス向上のための機能


Big Data SQLにおけるパフォーマンス最適化

パフォーマンス最適化

読み込みを速くする

並列度

スキャンモード(C , Java)

無駄なデータを読まない

Partition Pruning

Storage Index

Predicatepushdown

無駄なものを転送しない

SmartScan

76





並列度



Partition Pruning

Storage Index

Predicatepushdown


SmartScan

77


Oracle DBの並列度とHadoop側の並列度の関係- Hadoop側の並列度は、最適化されたDOPが自動選択される

-DB側のDOP指定との関連はなく、DB側のリソースを抑えるためにシングルスレッドで実行してもHadoopレイヤでの処理は分散される

Hadoop側で実行できる処理（後述）については分散処理なので高速化できる

ただし、DBサーバ側での処理（集計等）はシングルのためボトルネックになる可能性

がある

78


Oracle DBの並列度とHadoop側の並列度の関係- DB側で並列度を上げた場合、PXサーバ毎にHadoopのリソースが割り振られる⇒DB側のスレッドを増減してもHadoop側の並列度には影響を与えない

Hadoop側の並列度に関しては、チューニング不要

79





並列度



Partition Pruning

Storage Index

Predicatepushdown


SmartScan

80


スキャンモード• パフォーマンスに影響を与える2種類のスキャンモードがあります。

C : 独自実装の高速処理モード現バージョンではデリミタ区切りのテキストのみに対応Java : 内部的にHiveのStorageHandlerを利用したデータの読取りを行う

• Data Modeは、Create Table時にcom.oracle.bigdata.datamodeで指定します。無指定の場合は、auto(デフォルト)となり自動適用されます(C優先)

CREATE TABLE BDS_SAMPLECol1 VARCHAR2(30) , Col2 NUMBER(30)

ORGANIZATION EXTERNAL (TYPE ORACLE_HDFSDEFAULT DIRECTORY DEFAULT_DIRACCESS PARAMETERS(

com.oracle.bigdata.cluster=cluster1com.oracle.bigdata.rowformat=Delimited fields terminated by '|'com.oracle.bigdata.datamode=c)

LOCATION ('hdfs://cluster1-ns/user/hive/warehouse/lineitems'));

81


スキャンモード

Data mode 内部処理

C

Java

データ

Hiveデータ型

Oracleデータ型処理

Oracleデータ型処理

変換

変換(SerDe) 変換

• Java data modeはSerDeを利用するため、利便性に優れるものの、内部的には一度、Hiveデータ型に変換された後に、Oracleデータ型への変換が行われるため、C data modeより実行速度が劣ります。

82





並列度



Partition Pruning

Storage Index

Predicatepushdown


SmartScan

83


Big Data SQLパフォーマンス機能

84

SCAN性能向上のための仕組み

100 TBUser Query Partition Pruning

10 TB

1

Storage Indexing1 TB

2

Predicate Pushdown100 GB

3

Hiveパーティション情報を利用して必要なディレクトリだけSCAN

自動作成されたIndexにより不要なブロックを読み飛ばす

Parquet/ORCフォーマットの場合は、ブロック内のスキャンを高速化


Hive Partition Pruning• BDSでもHiveパーティションが有効

Select * from website where month = 2016-01

パーティションディレクトリの配下にあるファイルのみスキャンする(無駄読みを減らす）

BDSのDDL/SQL記述は特に意識不要(Hive DDLでパーティション構造を記述）

datahr

salaries.csvwebsite

2016-01clicks1.JSONclicks2.JSON

2016-02clicks3.JSONclicks4.JSON

warehousedata.db

hrsalaries.csv

websitemonth=2016-01

clicks1.JSONclicks2.JSON

month=2016-02clicks3.JSONclicks4.JSON

hive

BDS

SCAN


Storage Index

• 初回アクセス時に自動でブロック毎の各カラムのmin/max値のインデックス情報を作成（各ノードのメモリ上）

• 2回目以降のアクセス時には、条件に合致するデータがないブロックは読み飛ばす

• BDSのDDL/SQL記述は特に意識不要

86

HDFSField1, Field2,

100110101045110910431001104516091043

11455190912430130101045019092043

Field3, … , Fieldn

HDFSBlock1

(256MB)

HDFSBlock2

(256MB)

IndexB1 – Movie_IDMin: 1001Max: 1609

B2 – Movie_IDMin: 1909Max: 13010

SQL: Select * from hogewhere MOVIE_ID = 1109

✓

✗


Predicate Push DownParquet/ORC Fileに対する処理をネイティブに実行

Select namefrom my_custwhere id = 1

Metadata for “blocks”

Columns

Rows

列の絞り込み

行の絞り込み

DBのインデックスのような役割

例) Parquet files

87


通常のSCANとPredicate Pushdownの違い

Disks

Data Node

Big Data SQL Server

External Table Services

Smart Scan

RecordReader

SerDe10

1100

1010

1100

1010

1100

10

1

2

3

HDFSからブロックを読む

表形式整形後Oracle型に変換

行、列の絞込み

1011

0010

1011

0010

1011

0010

1

2

3

列を絞込みながらHDFSからブロックを読む

行の絞込み

表形式整形後Oracle型に変換

88

1 A X

2 B X

3 C X

1 A X

2 B X

3 C X

A X

B X

A X

B X

C X

A X

B X

C X

A X

B X

DB

SQL





並列度



Partition Pruning

Storage Index

Predicatepushdown


SmartScan

89


Exadataでお馴染みのSmart Scan機能をHadoopに実装

RDBMSとHadoopに対するクエリ

Oracle SQL

HDFSData NodeBDS Server

HDFS Data NodeBDS Server

Oracle DatabaseStorage Server

Oracle DatabaseStorage Server

Fast大規模並列分散処理ローカルでの絞り込み転送データの極小化

90


Big Data SQLがHadoop側で処理できるSQL

• Hadoop scans (InputFormat, SerDe)–様々な形式のデータを読み取れます

• JSON parsing– Oracle Database12cのJSONパースの機能をHadoop側で実行

• WHERE clause evaluation• Column projection• Bloom filters for faster join• 1件処理できる関数（文字列操作など）

91


各種パフォーマンス機能の実行統計を確認

92


One Fast, Secure SQL Queries over All your Data本日のまとめ

Oracle SQL

オラクルは、Big Data SQLを中核に、RDBMSとHadoopをシームレスに統合できる世界を実現• RDBは、より安価に大量のデータを保有できるようになります。• Hadoopは、より簡単にパワフルなSQLを使ってセキュアにDBユーザーにデータを展開できます。

Parquet

HDFS

ORCHIVE

CASSANDRA

MONGOAVRO

HBASE

KAFKA


Oracle Database 12c 対応研修コースのご案内

Oracle Database 12c: SQL 基礎 I (3日間)

Oracle Database 12c: 管理ワークショップ

(5日間)

Oracle Database 12c: SQL チューニングワークショップ

(3日間)

Oracle Database 12c: PL/SQL プログラム開発 (3日間)

Oracle Database 12c: インストール＆アップグレード

(2日間)

Oracle Database 12c: バックアップ・リカバリ(5日間)

Oracle Database 12c: 新機能(5日間)

Oracle Database 12c: セキュリティ

(5日間)

Oracle Database 12c:Clusterware 管理

(4日間)

Oracle Database 12c:RAC 管理(4日間)

Oracle Database 12c: PL/SQL 基礎

(2日間)

データベース設計(3日間)

Oracle Database 12c: パフォーマンス・チューニング

(5日間)

Bronze

Silver

Platinum

Gold

Oracle Database 12c: SQL 基礎 II

(2日間)

Oracle Database 11g: データ・マイニング

手法(2 日間)

Oracle Database 12c: Database Vault

(2日間)

Oracle ではじめる統計入門(1 日間)

Oracle R Enterprise エッセンシャルズ

(2 日間)

※ Oracle Database 12cR2対応研修は順次提供予定です。詳しくはオラクルユニバーシティまでお問い合わせください。

Oracle Database 12c: マルチテナント・アーキテクチャ

(2日間)

Oracle Database 12c: ASM 管理(2日間)

Oracle Database 12c: 管理クイック・スタート(2日間)

Oracle Database 12c: 管理ネクスト・ステップ(3日間)

Adva

nced

Ana

lytic

s O

ptio

n 対応コース

基礎から上級スキルまで。Oracle Database 12c の製品機能を学習できる多彩な研修コースでスキルアップを


Oracle Digitalは、オラクル製品の導入をご検討いただく際の総合窓口。電話とインターネットによるダイレクトなコニュニケーションで、どんなお問い合わせにもすばやく対応します。

もちろん、無償。どんなことでも、ご相談ください。

http://www.oracle.com/jp/contact-us/index.html

Big Data SQLのセットアップ

Documents

Transcript of Big Data SQLのセットアップ