Oracle Big Data SQL全數(shù)據(jù)管理技術(shù)

1、Copyright 2014 Oracle and/or its affiliates. All rights reserved. |Oracle Big Data SQL全數(shù)據(jù)管理技術(shù)殷致云 2016年10月Oracle Confidential Internal/Restricted/Highly RestrictedCopyright 2014 Oracle and/or its affiliates. All rights reserved. |Safe Harbor StatementThe following is intended to outline our general p

2、roduct direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or

3、 functionality described for Oracles products remains at the sole discretion of Oracle.Oracle Confidential Internal/Restricted/Highly Restricted2Copyright 2014 Oracle and/or its affiliates. All rights reserved. |數(shù)據(jù)分析挑戰(zhàn)數(shù)據(jù)分析挑戰(zhàn)隔離的信息孤島難以分析Copyright 2014 Oracle and/or its affiliates. All rights reserved.

4、 |數(shù)據(jù)分析挑戰(zhàn)數(shù)據(jù)分析挑戰(zhàn)不同的數(shù)據(jù)訪問接口4Copyright 2014 Oracle and/or its affiliates. All rights reserved. |數(shù)據(jù)分析挑戰(zhàn)數(shù)據(jù)分析挑戰(zhàn)沒有能跨Oracle, Hadoop和NoSQL的統(tǒng)一接口Copyright 2014 Oracle and/or its affiliates. All rights reserved. |客戶想要什么客戶想要什么豐富，全面的SQL訪問企業(yè)的所有數(shù)據(jù)NoSQLCopyright 2014 Oracle and/or its affiliates. All rights reserved.

5、|突破創(chuàng)新突破創(chuàng)新 ：融合的大數(shù)據(jù)解決方案融合的大數(shù)據(jù)解決方案一條SQL可以訪問所有數(shù)據(jù)源OracleHadoopBig Data SQLNoSQLAnd moreCopyright 2014 Oracle and/or its affiliates. All rights reserved. |Oracle 大數(shù)據(jù)大數(shù)據(jù)SQL大規(guī)模并發(fā)SQL查詢，橫跨Oracle, Hadoop和NoSQLOracle Database 12c卸載查詢到Exadata存儲服務(wù)器Offload Query toExadata Storage Servers小數(shù)據(jù)集快速返回Small data subsetqu

6、ickly returnedHadoop & NoSQL卸載查詢到數(shù)據(jù)節(jié)點(diǎn)Offload Query toData NodesSQL數(shù)據(jù)子集data subsetSQLCopyright 2014 Oracle and/or its affiliates. All rights reserved. |Oracle Big Data SQL的用戶Oracle Confidential Internal/Restricted/Highly Restricted9大數(shù)據(jù)分析服務(wù) 研發(fā)、供應(yīng)鏈、客戶和消費(fèi)者的數(shù)據(jù) 集中化部署的數(shù)據(jù)科學(xué)組織提升客戶體驗(yàn)、業(yè)務(wù)創(chuàng)新 西班牙最大的零售銀行，客戶超過1

7、300萬 收集來自不同接觸渠道的多種信息，形成客戶的統(tǒng)一視圖 網(wǎng)絡(luò)威脅檢測產(chǎn)品創(chuàng)新 國際商業(yè)和技術(shù) 咨詢公司， 非/半結(jié)構(gòu)化、運(yùn)營和社交媒體大數(shù)據(jù)分析，探測網(wǎng)絡(luò)入侵BDAExadataBDAExadataBDAExadataCopyright 2014 Oracle and/or its affiliates. All rights reserved. |OracleBig Data SQL 提供的能力關(guān)鍵功能特性關(guān)鍵功能特性Confidential Oracle Internal/Restricted/Highly Restricted10Copyright 2014 Oracle and/

8、or its affiliates. All rights reserved. |Big Data SQL: 企業(yè)級海量大數(shù)據(jù)管理 智能的外部表External Tables分布式數(shù)據(jù)存儲的簡單集成 智能掃描SmartScan充分利用大數(shù)據(jù)平臺的計算能力 存儲索引Storage Indexing自動減少I / O，從根本上提高查詢分析性能 謂詞過濾下移Predicate Push-Down將分區(qū)修剪處理下移到大數(shù)據(jù)平臺Confidential Oracle Internal/Restricted/Highly Restricted11豐富的豐富的SQL、更強(qiáng)的靈活性、更多的數(shù)據(jù)源、更強(qiáng)的靈活性

9、、更多的數(shù)據(jù)源Copyright 2014 Oracle and/or its affiliates. All rights reserved. |12元數(shù)據(jù): 延伸Oracle 外部表CREATE TABLE movielog ( click VARCHAR2(4000)ORGANIZATION EXTERNAL ( TYPE ORACLE_HIVE DEFAULT DIRECTORY DEFAULT_DIR ACCESS PARAMETERS (com.oracle.bigdata.tablename logscom.oracle.bigdata.cluster mycluster ) R

10、EJECT LIMIT UNLIMITED; 新型態(tài)的外部表ORACLE_HIVE (使用 hive 元數(shù)據(jù))ORACLE_HDFS (指定元數(shù)據(jù)) 大數(shù)據(jù)參數(shù)獲取Hadoop 集群遠(yuǎn)程 Hive 數(shù)據(jù)庫/表 DBMS_HADOOP Package 用于自動導(dǎo)入Copyright 2014 Oracle and/or its affiliates. All rights reserved. |13智能外部表External TableOracle TableHDFS Data可定義可定義:可獲得可獲得: 數(shù)據(jù)表名 自動發(fā)現(xiàn)Hive 表的元數(shù)據(jù)metadata定義 Oracle數(shù)據(jù)類型 自動將數(shù)

11、據(jù)從Hadoop types轉(zhuǎn)化成Oracle數(shù)據(jù)庫類型 從任意的InputFormat自動轉(zhuǎn)換 任意的查詢并行度 在Hadoop cluster上并行執(zhí)行 Copyright 2014 Oracle and/or its affiliates. All rights reserved. |存儲層14Big Data SQL: Hadoop 的另一個處理引擎文件系統(tǒng) (HDFS)NoSQL 數(shù)據(jù)庫(Oracle NoSQL DB, Hbase)資源管理(YARN, cgroups)處理層MapReduceand HiveSparkImpalaSearchBig DataSQL元數(shù)據(jù)存儲Copy

12、right 2014 Oracle and/or its affiliates. All rights reserved. |Hadoop上的智能掃描: 優(yōu)化性能Oracle Confidential Internal/Restricted/Highly Restricted15Data NodeDiskBig Data SQL 服務(wù)服務(wù)External Table ServicesSmart Scan“Oracle服務(wù)位于上層服務(wù)位于上層”應(yīng)用謂詞過濾列投影（列過濾）半結(jié)構(gòu)化數(shù)據(jù)解析（XML、JSON）“Hadoop服務(wù)位于底層服務(wù)位于底層”貼近數(shù)據(jù)進(jìn)行處理工作使用Hadoop classe

13、s實(shí)現(xiàn)Schema On Read轉(zhuǎn)換為Oracle數(shù)據(jù)格式Copyright 2014 Oracle and/or its affiliates. All rights reserved. |Big Data SQL 數(shù)據(jù)流DisksData NodeBig Data SQL AgentExternal Table ServicesSmart ScanRecordReaderSerDe101100101011001010110010123從HDFS數(shù)據(jù)節(jié)點(diǎn)讀取數(shù)據(jù) 直接路徑讀取 盡可能使用基于C編寫的reader 否則使用Hadoop 原生的classes將數(shù)據(jù)翻譯轉(zhuǎn)換為Oracle數(shù)據(jù)格式

14、對Oracle格式數(shù)據(jù)應(yīng)用智能掃描Smart Scan 謂詞過濾 列投影 語法解析JSON/XMLCopyright 2014 Oracle and/or its affiliates. All rights reserved. |Big Data SQL 位于任何數(shù)據(jù)的頂層1717任何類型的文件InputFormat創(chuàng)建分割/n/n/n/nRecordReader創(chuàng)建記錄SerDe創(chuàng)建屬性Big Data SQL轉(zhuǎn)換數(shù)據(jù)&智能掃描Hive 元數(shù)據(jù)Copyright 2014 Oracle and/or its affiliates. All rights reserved. |Big

15、 Data SQL 細(xì)胞解析 18輸出輸入流數(shù)據(jù)轉(zhuǎn)換Exadata 存儲服務(wù)器軟件起源Copyright 2014 Oracle and/or its affiliates. All rights reserved. |Big Data SQL 細(xì)胞解析 19智能掃描 輸入輸出流數(shù)據(jù)轉(zhuǎn)換轉(zhuǎn)換成Oracle 文件快格式應(yīng)用智能掃描及其他調(diào)優(yōu)工具Copyright 2014 Oracle and/or its affiliates. All rights reserved. |Oracle Big Data SQL 存儲索引存儲索引可以通過減少HDFS數(shù)據(jù)塊的IO實(shí)現(xiàn)查詢屠蘇SQL查詢中的列通過外

16、部表定義映射到HDFS文件每個HDFS數(shù)據(jù)塊中，所定義字段的最小和最大值保存在存儲索引中20HDFSField1, Field2,10011010104511091043100110451609104311455190912430130101045019092043Field3, ,FieldnHDFSBlock1(256MB)HDFSBlock2(256MB)IndexB1 Movie_IDMin: 1001Max: 1609B2 Movie_IDMin: 1909Max: 13010Example: 查找查找MOVIE_ID 為1109的所有打分的所有打分Copyright 2014 Or

17、acle and/or its affiliates. All rights reserved. | Confidential Oracle Internal/Restricted/Highly Restricted21謂詞下推Data NodeBig Data SQL AgentExternal Table ServicesSmart ScanData NodeBig Data SQL AgentExternal Table ServicesSmart ScanSELECT ticker, AVG(price)OVER (PARTITION BY tickerORDER BY mnth) a

18、s avg_priceFROM stock_pricesWHERE mnth :yAND stx_xchange = :zORDER BY mnthWHERE mnth :yAND stx_xchange = :zSELECT ticker, price, mnthWHERE mnth :yAND stx_xchange = :zOracle 數(shù)據(jù)庫將SQL查詢語句的謂詞條件發(fā)送到Hadoop層（包括綁定變量）用于數(shù)據(jù)過濾 Parquet/ORC 文件格式的過濾 Hive 分區(qū)修剪 Hbase Subscan再通過智能掃描SmartScan過濾出滿足條件的行Copyright 2014 Ora

19、cle and/or its affiliates. All rights reserved. |Big Data SQL 效能特征22減少IO的使用帶來了綜合性效能提升的效果100 TB使用者查詢分區(qū)裁剪10 TB1存儲索引1 TB2謂詞下推100 GB3Copyright 2014 Oracle and/or its affiliates. All rights reserved. |Big Data SQL 安全特征Hadoop 安全ACLs | Sentry | HDFS 加密 | 動態(tài)加密Copyright 2014 Oracle and/or its affiliates. All

20、 rights reserved. |Big Data SQL 安全特征 相同的數(shù)據(jù)安全模塊應(yīng)用于更廣泛的數(shù)據(jù)存儲 先進(jìn)的特性如: 數(shù)據(jù)編輯,目前已經(jīng)可以應(yīng)用在不同數(shù)據(jù)源之間的結(jié)合 Oracle 安全層建構(gòu)在現(xiàn)有 Hadoop 功能之上Hadoop 安全ACLs | Sentry | HDFS 加密 | 動態(tài)加密Copyright 2014 Oracle and/or its affiliates. All rights reserved. | (Hive) 分區(qū)裁剪 在查詢運(yùn)行之前讀取 Hive 目錄跟裁剪分區(qū) 12.2 利用Oracle 數(shù)據(jù)庫元數(shù)據(jù)優(yōu)化分區(qū)裁剪 目的: 消除不必要的IO損

21、耗 存儲索引 維護(hù)元數(shù)據(jù)元素以標(biāo)記文件塊,減少不必要IO使用 目的: 消除不必要的IO損耗 智能掃描 最終過濾以確保只有被請求的數(shù)據(jù)元素會被傳送到Oracle數(shù)據(jù)庫 目的: 消除不必要的數(shù)據(jù)移動 Bloom Filtering (布隆過濾器) 聯(lián)接轉(zhuǎn)換成布隆過濾器并下推到Hadoop 節(jié)點(diǎn) 目的:連接優(yōu)化和本地化 謂詞和 Column Projection 下推 下推查詢元素到文件如 Parquet 跟 ORC 目的:減少優(yōu)化文件格式所產(chǎn)生不必的IO消耗 安全 應(yīng)用 Oracle 數(shù)據(jù)庫安全策略到非Oracle 數(shù)據(jù)存儲上 目的: 啟動高效安全特性25Big Data SQL 主要特征Copy

22、right 2014 Oracle and/or its affiliates. All rights reserved. |Oracle Big Data SQL能支持的平臺Version 3.0Confidential Oracle Internal/Restricted/Highly Restricted26Copyright 2014 Oracle and/or its affiliates. All rights reserved. |Big Data SQL 支持的平臺27XBBig Data SQLCloud ServiceorCopyright 2014 Oracle and/

23、or its affiliates. All rights reserved. |1、Big Data SQL運(yùn)行在Oracle集成系統(tǒng)平臺Exadata運(yùn)行Oracle 數(shù)據(jù)庫 12.1.0.2以上 版本，BDA運(yùn)行 CDH 5.5以上版本智能掃描Smart Scan支持所有的數(shù)據(jù)通過Infiniband網(wǎng)絡(luò)提供極致性能BDA是專門為海量數(shù)據(jù)處理而優(yōu)化的Hadoop部署方式28BBBBig Data SQLCopyright 2014 Oracle and/or its affiliates. All rights reserved. |2、Big Data SQL運(yùn)行在通用硬件平臺12c+集成平臺BDA采用通用x86硬件運(yùn)行Oracle 數(shù)據(jù)庫 12.1.0.2以上 版本，BDA運(yùn)行 CDH 5.5以上版本利用Oracle SQL 進(jìn)一步增強(qiáng)BDA Hadoop平臺的能力BDA是專門為海量數(shù)據(jù)處理而優(yōu)化的Hadoop部署方式29BBBBig Data SQLCopyright