Big Data SQL Alfred Schlaucher, Oracle Januar 2017 Hadoop-Technologie isoliert zu betrachten macht wenig Sinn. Die meisten Anwendungen speziell im Data Warehouse-Segment arbeiten mit relational gespeicherten Daten. Zudem leben Analyse-Systeme von der effizienten Strukturierung von Daten z. B. als Dimensionen in Star-Modellen. Dennoch gibt es sinnvolle Einsatzszenarien von Hadoop und daher liegt es nahe relationale Daten und Hadoop-Daten zusammenhängend abzufragen. Oracles Big Data SQL macht das möglich. Der Vortrag zeigt die technische Umsetzung und Anwendung von Big Data SQL. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 1 Business Analytics klassisch Advanced / Predictive Analytics heute Historien-Sicht, Status-,Ergebnisberichte Kontrolle und Steuerung von Abläufen Unbekannte Erklärungsmuster basierend auf noch mehr Merkmalen, Neues entdecken, Innovation erzeugen, feinere Steuerungsmöglichkeiten Transaktionale Daten OLTP-Anwendungen, Stark strukturierte Daten Statistik, Aggregationen, Lineare Betrachtung Oft relationale Datenbanken Multidimensionale Modelle Fokus / Erwartung Daten Art der Analyse Technologie Alle Daten, die man bekommen kann Bilder, Texte, Kommunikationsdaten, Sentiments, Messdaten, fließende Daten Zufallsanalysen, Wahrscheinlichkeiten, Suche nach komplexen Abhängigkeiten Endlos skalierende Plattformen für das Speichern unterschiedlichster Datenarten Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 2 Klassisches Warehousing D a t a L e g a c y Interne All Processes SAP R/3 SAP / BW Integration Layer BrokerSort Segment ArticleGrp-Unit Articlegroup Article D F Measure D Analyze D Package Unit F D Package D D Planning Control F D Key Figures N e w D a t a DeliveryUnit ProductArt Real-Business Streaming Externe D Automated Fetching Industrie 4.0 IoT Log + Production Data User View Layer Enterprise Layer Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted Simulation … 5 Erweiterter Big Data Fokus Klassisches Warehousing D a t a L e g a c y Interne All Processes SAP R/3 SAP / BW Industrie 4.0 IoT Integration Layer Alle Daten, die man bekommen kann User View Layer Enterprise Layer D Automated Fetching ProductArt Real-Business Streaming Broker- Segment Sort ArticleGrp-Unit Articlegroup D a t a N e w Externe Article Log + Production Data Bilder, Texte, Kommunikationsdaten, Sentiments, Messdaten, fließende Daten F DeliveryUnit Package Unit Package Measure D D Analyze D D F D F D D Planning Key Figures Control Simulation … Data Lake Structures Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 5 Erweiterter Big Data Fokus Klassisches Warehousing D a t a L e g a c y Interne All Processes SAP R/3 SAP / BW Industrie 4.0 IoT Integration Layer Alle Daten, die man SQL bekommen kann User View Layer Enterprise Layer D Automated Fetching ProductArt Real-Business Streaming Broker- Segment Sort ArticleGrp-Unit Articlegroup Log + Production Data Package Unit Package D Analyze D D F D F D D Planning Key Figures SQL D a t a N e w Externe Article Bilder, Texte, Kommunikationsdaten, Sentiments, Messdaten, fließende Daten F DeliveryUnit Measure D Control Simulation … Data Lake Structure Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 5 D a t e n N e u e Alle Prozesse Integration Layer User View Layer Enterprise Layer ETL Core - DWH / Info Pool Aktuelle Sichten Alle Unternehmensteile Industrie 4.0 Log + Produktions Daten D Strategische Sichten Offload B e s t e h e n d e D a t e n 2 Rollen von Hadoop in Data Warehouse Architekturen Data Lake Kosten/Nutzen-Relation bei der Speicherung bestimmter Daten passt oft nicht. Oft bleibt nur der Verzicht auf die Daten, weil es zu teuer ist. Archive / ODS / Skalierung • Spezielle Datenarten CDRs, Bondaten, Logdaten, Click-Daten, Messwertdaten • Archiv für alte Daten (ILM) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | D F D F • Große Fakten-Tabellen 6 http://hadoop.apache.org/ Kernkomponenten Hadoop Hadoop Distributed File System (HDFS) Ein über den Cluster verteiltes Files-System Hadoop Map Reduce 2-phasiges Programmier-Framework (Mapper/Reducer) YARN (Yet Another Resource Negotiator) Programmiermodell für verteile Anwendungen in einem Hadoop-Cluster, bei dem eine entsprechende Anzahl an Ressourcen zugeteilt wird (CPUs, RAM, Speicher) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 7 Anforderungen • Durchgängige Verwendung von SQL • Transparente Verwendung von HDFS durch Anwender mittels SQL • Übergreifendes Datenmodell – beinhaltet Tabellen/Files im HDFS und in der Datenbank • „Hybride“ Tabellen – Ein Teil einer Tabelle liegt in der DB, der weitaus größere Teil im HDFS SQL Oracle 12c Kunden Produkt Filiale Verkauf Zeit Kampagne komplett Region hybrid Bons • „Akzeptable“ Leseperformance • Implementierung von HintergrundTransportläufen In Memory HDFS Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted offload 8 Big Data SQL: A New Hadoop Processing Engine Processing Layer MapReduce and Hive Spark Impala Big Data Search SQL Resource Management (YARN, cgroups) Storage Layer Filesystem (HDFS) NoSQL Databases (Oracle NoSQL DB, Hbase) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 9 Oracle Big Data SQL SQL Abfragen in der Oracle DB für Hadoop & Oracle NoSQL, HBase Oracle Database Storage Server Any Hardware Cluster System Cloudera Hadoop Horton Works Hive metadata HDFS Name Node HDFS Data Node HDFS Data Node Big Data SQL All Daten mittels Oracle SQL zugreifen Smart Scan Funktionalität auf Hadoop zur Optimierung des Zugriffs Oracle Catalog Hive metadata External Table External Table create table customer_address ( ca_customer_id number(10,0) , ca_street_number char(10) , ca_state char(2) , ca_zip char(10)) organization external ( TYPE ORACLE_HIVE DEFAULT DIRECTORY DEFAULT_DIR ACCESS PARAMETERS (com.oracle.bigdata.cluster hadoop_cl_1) LOCATION ('hive://customer_address') Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | ) Oracle Database 12c RAC Cluster SQL-on-Hadoop Engines teilen Metadaten Hive Metastore Oracle Big Data SQL SparkSQL Hive Hive Metastore Impala … Table Definitions: movieapp_log_json Tweets avro_log Metastore maps DDL to Java access classes Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 11 Offload Szenario: Messwerte + Erfahrungen Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Szenario-Umgebung Firma ISE Nürnberg Exadata Quarter Rack Big Data Appliance Starter Rack (6 Cluster Knoten) Infiniband Oracle 12c Database Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 15 Big Data Appliance X6-2 (aktuelle Version) 18 mal Sun Oracle X6-2L Servers with per server: • 2 * 22 Core (2.2GHz) Intel Xeon E5-2699 v4 Processors • 256 GB DDR4-2400 Memory • 96TB Disk space (7200 RPM SAS Platten) • 2 Infiniband 40 Gb/sec Ports • 4 10 Gb Ethernet Ports Included Software (4.4): • Oracle Linux 6.7 • Oracle Big Data SQL 3.0* • Cloudera Distribution of Apache Hadoop 5.5.x – EDH Edition • Cloudera Manager 5.5.x • Oracle R Distribution • Oracle NoSQL Database CE * Oracle Big Data SQL is separately licensed Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 16 Das Demo-Star Schema D_ARTIKEL D_ZEIT DATUM_ID TAG_DES_MONATS TAG_DES_JAHRES WOCHE_DES_JAHRES MONATS_NUMMER MONAT_DESC QUARTALS_NUMMER JAHR_NUMMER ZEIT_ID PK D_REGION REGION_ID PK ORTNR ORT KREISNR KREIS LANDNR LAND REGIONNR REGION D_VERTRIEBSKANAL PK KANAL_ID VERTRIEBSKANAL KANALBESCHREIBUNG VERANTWORTLICH KLASSE ARTIKEL_NAME GRUPPE_NR GRUPPE_NAME SPARTE_NAME SPARTE_NR ARTIKEL_ID F_UMSATZ ARTIKEL_ID KUNDEN_ID ZEIT_ID REGION_ID FK KANAL_ID FK UMSATZ FK MENGE FK UMSATZ_GESAMT FK UMSATZ_GESAMT VERTRIEBS_KZ STEUER VERPACKUNGSART HANDELSKLASSE KOMMISSIONSWARE LAGERWARE BESCHREIBUNG LIEFER_DATUM RECHNUNGS_DATUM ZAHLUNGS_ZIEL D_KUNDE PK KUNDEN_ID KUNDENNR GESCHLECHT VORNAME NACHNAME TITEL ANREDE GEBDAT BRANCHE WOHNART KUNDENART BILDUNG ANZ_KINDER EINKOMMENSGRUPPE ORTNR NUMBER, BERUFSGRUPPE STATUS STRASSE TELEFON TELEFAX KONTAKTPERSON FIRMENRABATT BERUFSGRUPPEN_NR BILDUNGS_NR EINKOMMENS_NR WOHNART_NR HAUSNUMMER PLZ ORT KUNDENKARTE ZAHLUNGSZIEL_TAGE TOTAL TOTAL_NR • Faktentabelle • Nach Zeit partitioniert • 100 / 200 /400 / 800 Millionen Sätze • Komprimiert • Bitmap-indiziert • alter table f_umsatz parallel 12 • Dimensionen bleiben auf der ExdataMaschine • Abfragen auf der Exadata-Maschine und analog BDA • Unterschiedlich komplexe SQL-Abfragen • Daten-Transport mit sqoop Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Ersetzung Faktentabelle durch HDFS-Datei Exadata BDA Hive HDFS F_UMSATZ Hive External Table F_UMSATZ F_UMSATZ 3 Metadatenabruf F_Umsatz 5 Big Data SQL 4 F_UMSATZ 2 External Table sqoop 1 Ursprüngliche Fakten-Tabelle Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 18 Die Größenverhältnisse in der Datenbank und im HDFS Größenangaben in GB Oracle DB HDFS Faktor 1 Anz Sätze Faktor 3 CSV-File Parquet File CSV-File Parquet File (Mill.) Unkomp. Query low Unkomp. Komp. Unkomp. Komp. 102 15,5 6.1 19,490 6,624 7,15 58,471 19, 0 21,47 205 46,6 19,2 39 13,2 14,25 117 39,7 44,32 409 73 46,6 77,96 26,39 28,49 214,7 73,16 78,53 818 124,3 63,4 155,9 54,12 58,61 467,8 162,39 175,94 Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Das sqoop Ladeszenario Records 102 Millionen 205 Millionen 409 Millionen 818 Millionen $ > > > > sqoop import -D mapred.job.name='default oraoop' --direct --connect jdbc:oracle:thin:@21.108.5.88:1521:dbm088 --username dwh --password dwh --table F_UMSATZ \ --as-textfile --delete-target-dir --target-dir /user/sqoop_output/f_umsatz -m 100 -z Parallelität Default (Parallel 4) Parallel 100 Übertragene Datenmenge \ \ \ Transferred 18.1519 GB in 235 seconds (78.7637 MB/sec) Retrieved 102400000 records. Minuten 3,9 Transferred 18.1519 GB in 132 seconds (136.2002 MB/sec) Retrieved 102400000 records. 2,2 Parallel 150 Transferred 18.1519 GB in 108 seconds (166.3302 MB/sec) Retrieved 102400000 records. 1,8 Parallel 200 komprimiert Transferred 6.1696 GB in 66 seconds (93.385 MB/sec) Retrieved 102400000 records. 1,1 Parallel 200 komprimiert Parallel 200 Transferred 36.3469 GB in 355 seconds (101.6502 MB/sec) Retrieved 205043477 records 5,9 Transferred 12.3264 GB in 138 seconds (91.442 MB/sec) Retrieved 204800000 records. 2,3 Parallel 100 Transferred 72.6075 GB in 536 seconds (135.4587 MB/sec) Retrieved 409600000 records. 8,9 Parallel 100 Transferred 151.3408 GB in 1083 seconds (139.7421 MB/sec) Retrieved 819200000 records. 18,05 Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 20 Die Definition in Hive CREATE EXTERNAL TABLE defAULT.F_UMSATZ_HDfs ( ARTIKEL_ID int ,KUNDEN_ID int , ZEIT_ID TIMEstamp ,REGION_ID int ,KANAL_ID int ,UMSATZ int ,MENGE int ,UMSATZ_GESAMT int ,VERTRIEBS_KZ string ,STEUER DECIMAL(15,10) ,VERPACKUNGSART string ,HANDELSKLASSE string ,KOMMISSIONSWARE string ,LAGERWARE string ,BESCHREIBUNG string ,LIEFER_DATUM DATE ,RECHNUNGS_DATUM DATE ,ZAHLUNGS_ZIEL DATE ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION '/user/sqoop_output/f_umsatz'; • Kann durch den sqoop – Job automatisch erzeugt werden Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 23 Die External Table – Definition in der Datenbank (wird generiert) DECLARE DDLout VARCHAR2(4000); BEGIN dbms_hadoop.create_extddl_for_hive( CLUSTER_ID=>'bda3000', DB_NAME=>'default', HIVE_TABLE_NAME=>'ex_f_umsatz_parq', HIVE_PARTITION=>FALSE, TABLE_NAME=>'EX_F_UMSATZ_PARQ', PERFORM_DDL=>FALSE, TEXT_OF_DDL=>DDLout ); dbms_output.put_line(DDLout); END; / CREATE TABLE BIGDATAREPO.EX_F_UMSATZ_PARQ ( artikel_id NUMBER, kunden_id NUMBER, zeit_id TIMESTAMP, region_id NUMBER, kanal_id NUMBER, umsatz NUMBER, menge NUMBER, umsatz_gesamt NUMBER, vertriebs_kz VARCHAR2(4000), steuer number(15,10), verpackungsart VARCHAR2(4000), handelsklasse VARCHAR2(4000), kommissionsware VARCHAR2(4000), lagerware VARCHAR2(4000), beschreibung VARCHAR2(4000), liefer_datum DATE, rechnungs_datum DATE, zahlungs_ziel DATE) ORGANIZATION EXTERNAL (TYPE ORACLE_HIVE DEFAULT DIRECTORY DEFAULT_DIR ACCESS PARAMETERS ( com.oracle.bigdata.cluster=bda3000 com.oracle.bigdata.tablename=default.ex_f_umsatz_parq) ) PARALLEL 2 REJECT LIMIT UNLIMITED; Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 24 Ausschnitt aus den Ergebnissen für die Abfragezeiten select sum(u.umsatz) Umsatz, z.Jahr_nummer Jahr ,z.Quartals_nummer Quartal, RANK() OVER (PARTITION by z.Jahr_nummer ORDER sum(U.umsatz) ASC ) AS Rangfolge from f_umsatz u, d_zeit z where z.zeit_id = u.zeit_id and z.Jahr_nummer between 2006 and 2011 group by z.Jahr_nummer,z.Quartals_nummer order by z.Jahr_nummer,Rangfolge; Anz. Sätze 102 204 408 819 BY Nr 6 6 6 6 Kommentar Rank + 2 Joins DB-Tabelle 02.07 10.61 21.59 46.31 HDFS-Datei 03.15 12.26 10.63 14.11 Siehe separates Dokument mit allen Tests und Abfragezeiten Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 25 Ausschnitt aus den Ergebnissen für die Abfragezeiten 409 Millionen Sätze (Datenbank-Tabelle ist nicht mehr partitioniert) 1b Abfrage Nr Abfrage auf Partition DB-Tabelle 55.33 Sek HDFS-Datei 09.04 Sek 23.51 Sek 22.53 Sek 09.81 Sek 06.64 Sek 06:07.95 Min 18.08 Sek 05:52.68 Min 03:22.91 Min 21.59 Sek 23.82 Sek 01:20.16 Min 01:04.46 Min 33.01 Sek 24.24 Sek 01:24.13 Min 17.80 Sek 12.47 Sek 10.63 Sek 10.62 Sek 33.94Sek 29.02 Sek 16.59 Sek 07.56 Sek 11.00 Sek Partitioning 1c 1c Abfrage mit Zeitkriterium (Partitioning) Abfrage ohne Zeitkriterium (Partitioning) Höhere Anzahl Joins 2 Abfrage mit 5 Joins / Group / Order by Höhere Anzahl Joins und Parallel 24 2 3 6 7 16 19 20 22 23 Abfrage mit 5 Joins / Group / Order by / 5 Filter 4 Joins + 2 Filter / Group by Rank + 2 Joins Rank und Subselect Ranks / Group by / 2 Subselects Mehrere Subselects Ntile + geschachtelte Subselects Cube Vorjahresvergleich Lag www.oracledwh.de -> Downloads Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 26 Zusammenfassungen, Erfahrungen, Ausblicke Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 27 Wie entsteht die lese und Schreib-Performance Optimierungs- Optionen • Schreiben von komprimierten Daten im HDFS (Weniger IO) • Parallelisierung von Schreib- und Lesezugriffen • Partitionieren von HDFS-Daten (Partition Pruning) • Verwenden von Parquet- / ORC- File-Format • Dynamic Indexing • Push Down Functions (Smart Scan) • Oracle-DB: extreme Performance durch Partitioning und In-Memory Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 28 Erfahrungen bzgl. „Offloading Data Warehouse“ • Performance-Gewinn bei Abfragen auf größeren Tabellen im HDFS – Z. B. ab 50 GB • Offloading kann sich schon lohnen bei 10 – 20 großen Tabellen im Bereich von 100 – 2000 GB (Rechenbasis 10 TB Datenvolumen) • Know How-Anforderungen an MA sind überschaubar – Viele Techniken aus der Linux-Welt bekannt – SQL, External Tables ist bekannt – Keine neue Programmiersprache – Neu ist Umgang mit sqoop (-> Handbuch) und Hadoop-Denkweise (-> Cluster) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 29 Einsatz Big Data SQL in gewachsenen IT- und Data Warehouse - Organisationen Offloading: Optimaler Einsatz von Hadoop-Technik für das Data Warehouse • Datenmodelle und Schichtenstruktur im DATA WAREHOUSE bleiben gleich • Die Art der Zugriffe für alle auf dem DATA WAREHOUSE arbeitenden Anwendungen und Benutzer bleiben gleich • Eingesetzte Abfragewerkzeuge, BI-Tools müssen nicht ausgetauscht werden • Anwender müssen nicht umlernen. • Es ist keine strategische Richtungsfestlegung für weitere Entwicklungen in der IT des Unternehmens, d. h. man bleibt offen für künftige Entwicklungen Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 30 Oracle Produkte Übersicht Big Data Appliance + Cloudera Hadoop Exadata + Oracle Datenbank Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 31 Ausblick auf mögliche Szenarien Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Von In-Memory bis zum Online Archiv: ein Objekt! In Memory Analytic View Bons (Meta View) SQL/ MDX Oracle 12c Kunden Produkt Filiale Bons Zeit Kampagne komplett hybrid Bons HDFS Region Dimensionen/Facts Hierarchie-Objekte Drillpfade Level-Attribute Bons Kennzahl_1 Kennzahl_2 Kennzahl_3 Kennzahl_4 Kennzahl_4 ……. Kennzahl_n Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 33 Szenario Realtime Loading / Data Lake IOT Cloud Service (+Stream Analytics) JSON Rest-Endpunkt Generischer Access-Punkt Hive Metastore BDD Visualizer Erste Analysen Flume Vorsortierung und Anreicherung mit MaschinenStammdaten aus der Cloud Kafka HDFS / BDA / Data Lake Parquet AVRO ORCH Standard Interface SparkQL Metadaten Big Data SQL ORE Weltweit verteilte Industrieanlangen 100– 1000 Sensor-Punkte z.B. Temperatur, Drehzahl, Drehmomente… Big Data SQL Pattern matching Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted Beliebige BI-Tools 34 D a t e n N e u e Alle Prozesse Integration Layer User View Layer Enterprise Layer ETL Core - DWH / Info Pool Aktuelle Sichten Alle Unternehmensteile Industrie 4.0 Log + Produktions Daten Strategische Sichten Offload B e s t e h e n d e D a t e n Hadoop in Data Warehouse Architekturen Data Lake Kosten/Nutzen-Relation bei der Speicherung bestimmter Daten passt oft nicht. Oft bleibt nur der Verzicht auf die Daten, weil es zu teuer ist. Archive / ODS / Skalierung • Spezielle Datenarten CDRs, Bondaten, Logdaten, Click-Daten, Messwertdaten • Archiv für alte Daten (ILM) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | D D D F • Große Fakten-Tabellen 35 13. März 2017 10:00 - 16:00 Suissotel Neuss (kostenfrei) am Vortag der Oracle Data Warehouse / Big Data Konferenz Anmeldung formlose Mail an [email protected] Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 36 Kontakte und weitere Infos • [email protected] • Broschüre Offloading Data Warehouse mit technischen Beschreibungen zu Big Data SQL -> www.oracledwh.de -> downloads • Anmeldungen Oracle Data Warehouse / Big Data Konferenz Neuss 14./15. März 2017 - > www.oracledwh.de -> downloads Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted 37