Implementing the Logical Data Warehouse with Oracle Big Data SQL

www.ise-informatik.de
Implementing the Logical Data Warehouse
with Oracle Big Data SQL
Matthias Fuchs
DWH Architekt
ISE Information Systems Engineering GmbH
Enable
eXtreme
Performance.
ISE Information Systems Engineering
www.ise-informatik.de





Gegründet 1991
Mitarbeiteranzahl: 60
Hauptsitz in Gräfenberg, Niederlassungen in München und Nürnberg
Schwerpunkte:
 Oracle Engineered Systems (Exadata / Exalogic / Exalytics)
 Data Warehousing & Business Intelligence
 Oracle DB – Migrationen, Optimierungen, Hochverfügbarkeit
 Managed Service für Datenbanken, BI und Middlewareapplikationen
Oracle Partner Engineered Systems Award 2013
Copyright (C) ISE GmbH - All Rights Reserved
2
Enable
eXtreme
Performance.
www.ise-informatik.de
ISE Oracle Technology Center
Copyright (C) ISE GmbH - All Rights Reserved
3
Enable
eXtreme
Performance.
ISE Oracle Technology Center
www.ise-informatik.de
Erstes und einziges Exastack Technology Center in
Deutschland in Nürnberg
Coming soon
Copyright (C) ISE GmbH - All Rights Reserved
ODA X5
4
Enable
eXtreme
Performance.
Agenda
www.ise-informatik.de




LDW - Logical Datawarehouse
Big Data SQL Infrastructure
Sqoop - der Anfang
Customer case
Copyright (C) ISE GmbH - All Rights Reserved
5
Enable
eXtreme
Performance.
www.ise-informatik.de
LDW – Logical Datawarehouse
Copyright (C) ISE GmbH - All Rights Reserved
6
Logical Data Warehouse
Enable
eXtreme
Performance.
www.ise-informatik.de
Gartner Hype Cycle for Information Infrastructure, 2012,
“the Logical Data Warehouse (LDW) is a new data
management architecture for analytics which combines the
strengths of traditional repository warehouses with
alternative data management and access strategy. The
LDW will form a new best practices by the end of 2015.”
Copyright (C) ISE GmbH - All Rights Reserved
7
Gartner: Logical Dataware House
Enable
eXtreme
Performance.
www.ise-informatik.de







Repository Management
 Verschiedene Typen u.a. Metadaten
 Konsolidierung
Data Virtualization
 Virtuelle Daten Schicht
Distributed Processes
 Aufruf externer Prozesse
 z.B. Bilder oder Content Analyse, aber auch MapReduce
 Cloud
Auditing statistics and performance Evaluation
 Statistik über Performance
 End User, Applikationen oder Verbindungen
SLA Management
 Metadataset über erwartete Ausführungenzeiten etc.
 Überwachung und ggf. Änderung der Ausführung
Taxonomy - Ontology resolution
 a taxonomy “tree” in an ontological “forest”
Metadata Management
Copyright (C) ISE GmbH - All Rights Reserved
8
Gartner: Logical Dataware House
Enable
eXtreme
Performance.
www.ise-informatik.de







Repository Management
 Verschiedene Typen u.a. Metadaten
 Konsolidierung
Data Virtualization
 Virtuelle Daten Schicht ‚Data-to-insight cycle ' schneller
Distributed Processes
 Aufruf externer Prozesse
 z.B. Bilder oder Contentgünstiges
Analyse, aberFramework
auch MapReduce
um neue
 Cloud
Inhalte einzubeziehen
Auditing statistics and performance Evaluation
 Statistik über Performance
 End User, ApplikationenHöhere
oder Verbindungen
Flexibilität
SLA Management
 Metadataset über erwartete Ausführungenzeiten etc.
 Überwachung und ggf. Änderung der Ausführung
Taxonomy - Ontology resolution
 a taxonomy “tree” in an ontological “forest”
Metadata Management
Copyright (C) ISE GmbH - All Rights Reserved
9
Gartner: Übersicht
Enable
eXtreme
Performance.
www.ise-informatik.de
Aus Gartner Newsletter Logical Data Warehousing for Big Data
Copyright (C) ISE GmbH - All Rights Reserved
10
Enable
eXtreme
Performance.
Information Management Reference Architecture
Oracle
Data Reservoir & Enterprise Information Store – complete view
www.ise-informatik.de
Data Sources
Enterprise
Performance
Management
Data Ingestion
Data Engines &
Poly-structured
sources
Past, current and future interpretation of
enterprise data. Structured to support
agile access & navigation
•
•
•
Operational Data
COTS Data
Streaming & BAM
Master &
Reference
Data Sources
Virtualisation &
Query Federation
Structured
Data
Sources
Access & Performance Layer
Foundation Data Layer
Immutable modelled data. Business
Process Neutral form. Abstracted
from business process changes
Raw Data Reservoir
Pre-built &
Ad-hoc
BI Assets
Information
Services
Immutable raw data reservoir
Raw data at rest is not interpreted
Information Interpretation
Discovery Lab Sandboxes
Content
Rapid Development Sandboxes
SMS
Docs
Web & Social Media
Project based data stores
to support specific
discovery objectives
Project based data
stored to facilitate rapid
content / presentation
delivery
Auditing statistics/performance Evaluation
Data
Science
SLA Management
http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/2297765.pdf
Copyright (C) ISE GmbH - All Rights Reserved
11
Enable
eXtreme
Performance.
www.ise-informatik.de
Big Data SQL Infrastructure
Copyright (C) ISE GmbH - All Rights Reserved
12
Enable
eXtreme
Performance.
Big Data Sql - Übersicht
www.ise-informatik.de
Oracle Big Data SQL
Cloudera
Hadoop
NOSQL
R Advanced
Analytics
Exadata
Connectors
ODI
Advanced
Analytics
Advanced
Security
Or BigData Lite VM
Copyright (C) ISE GmbH - All Rights Reserved
13
Enable
eXtreme
Performance.
Big Data Systemübersicht
www.ise-informatik.de
Processing Layer
Big Data SQL
Resource Management YARN + MapReduce
Storage Layer
Filesystem (HDFS)
Copyright (C) ISE GmbH - All Rights Reserved
14
Enable
eXtreme
Performance.
Big Data und DB im LDW
www.ise-informatik.de
Repository Management
Oracle Big Data
Appliance
Data Virtualization
Distributed Processes
Auditing statistics and performance
SLA Management
ODI, BPM, SOA
Taxonomy - Ontology resolution
Enterprise Metadata
Management
Copyright (C) ISE GmbH - All Rights Reserved
15
Enable
eXtreme
Performance.
www.ise-informatik.de
Sqoop - der Anfang
Copyright (C) ISE GmbH - All Rights Reserved
16
Enable
eXtreme
Performance.
Sqoop
www.ise-informatik.de



Sqoop = SQL- to – Hadoop
Paralleles kopieren von JDBC <-> HDFS
MapReduce jobs zum Daten laden/schreiben
HDFS
DB
Map
Reduce
Copyright (C) ISE GmbH - All Rights Reserved
17
Enable
eXtreme
Performance.
Sqoop mit Oracle
www.ise-informatik.de





OraOOP Guy Harrison team Quest (Dell)
Ab version 1.4.5 (CDH 5.1)
Oracle direct path (non-buffered) IO for all reads
Auf mappers werden Anzahl Blöcke verteilt
Bei partitionierten Tabellen, kann der Mapper pro Partition arbeiten
HDFS
HADOOP
MAPPER
ORACLE
SESSION
HADOOP
MAPPER
ORACLE
SESSION
Copyright (C) ISE GmbH - All Rights Reserved
ORACLE
TABLE
18
Enable
eXtreme
Performance.
Real Time
www.ise-informatik.de

Oracle Change Data Capture
 Supported in 11.2 – but not recommended by Oracle
 Desupported in 12.1

Oracle Golden Gate
 1. RDBMS to HIVE
 2. RDBMS to Flume
 3. RDBMS to HDFS

Andere Hersteller:
 (Dell) Quest SharePlex – Auslesen redologs
 (VMWare) Continuent Tungsten – uses CDC im Hintergrund
 Libelle
Copyright (C) ISE GmbH - All Rights Reserved
19
Enable
eXtreme
Performance.
www.ise-informatik.de
Customer case
Copyright (C) ISE GmbH - All Rights Reserved
20
Enable
eXtreme
Performance.
Analyse von Infrastrukturdaten
www.ise-informatik.de

Ziel






Daten von Servicecalls (OSB) auswerten
Daten Historisieren
Feststellen von Anomalien
Mappen von Strukturierten und Unstrukturierten Daten
Tabellen/View und Datei Import
Auswertung mit ausgewählten Werkzeugen
•
•
Analytic
output
R
Elasticsearch
YARN/MR
Weblogs
Flume
SQOOP
HDFS
Copyright (C) ISE GmbH - All Rights Reserved
CC
RDBMS
21
Enable
eXtreme
Performance.
Vorbereitung
www.ise-informatik.de

Wahl der Hadoop Distribution
 Cloudera – Oracle supported
 Ohne -> sehr aufwendig

Filedaten





Flume
Weblogic und Apache Logs
Gut dokumentiert im Netz
Ggf. Realtime Auswertung mit Elasticsearch or Solr
Hive
 CDH 5.1
 OCRFile Format
Copyright (C) ISE GmbH - All Rights Reserved
22
Enable
eXtreme
Performance.
Hive ORCFile
www.ise-informatik.de



Optimized Row Columnar File Format
light-weight indexes bereits im Fileformat
block-mode compression auf basis des Datentyps
Größenvergleich
über
verschiedene
Typen
585
• Encoded Text
• CSV File
505
• RCFile
• Record Columnar File
221
• Parquet
• Columnar Storage Format, impala
131
• ORCFile
• Hive
TPC-DS Scale 500 Dataset GB, Hortonworks
Copyright (C) ISE GmbH - All Rights Reserved
23
Enable
eXtreme
Performance.
Ablauf Datenintegration
www.ise-informatik.de

Teil 1 Datenladen
DB
HDFS
HIVE
Oracle Big Data SQL

Teil 2
Create Big Data SQL Layer
Copyright (C) ISE GmbH - All Rights Reserved
24
Enable
eXtreme
Performance.
Prozess Teil 1
www.ise-informatik.de
•
•
DB
•
•
•
•
•
•
Start sqoop job to HDFS
Create external table on
HDFS Files
insert as select in hive ocr
data table
HDFS
HIVE
Import parallel 1, da view daten
Kein primary key, keine parallelen MapReduce Prozesse
Direct read notwendig, da sonst tmp Tablespace zu klein
Start mit sqoop2, ende mit sqoop1 inklusiv Optimierung
ODI statt oozie
Copyright (C) ISE GmbH - All Rights Reserved
25
Prozess Teil 2
Enable
eXtreme
Performance.
www.ise-informatik.de


Suche Tabelle in Hive aus DB
select table_name, input_format, Location from ALL_HIVE_tables
where table_name like '%oem%';
Copyright (C) ISE GmbH - All Rights Reserved
26
Enable
eXtreme
Performance.
Prozess Teil 2
www.ise-informatik.de

Create Table in DB (nur in Test VM)
 DDL mit CREATE_EXTDDL_FOR_HIVE erzeugen
 DDL ausführen
DDL Erzeugen
dbms_hadoop.create_extddl_for_hive(
CLUSTER_ID=>'bigdatalite',
DB_NAME=>'default',
HIVE_TABLE_NAME=>'oem_data',
HIVE_PARTITION=>FALSE,
TABLE_NAME=>'oem_data',
PERFORM_DDL=>FALSE,
TEXT_OF_DDL=>DDLout
);
DDL Asuführen
CREATE TABLE OEM_DATA ( target_name
VARCHAR2(4000), target_guid …..
key_value6 VARCHAR2(4000),
collection_timestamp VARCHAR2(4000))
ORGANIZATION EXTERNAL
(TYPE ORACLE_HIVE
DEFAULT DIRECTORY DEFAULT_DIR
ACCESS PARAMETERS (
com.oracle.bigdata.cluster=bigdatalite
com.oracle.bigdata.tablename=default.oem_
data)
);
Copyright (C) ISE GmbH - All Rights Reserved
27
Ausführungsplan
Enable
eXtreme
Performance.
www.ise-informatik.de
Copyright (C) ISE GmbH - All Rights Reserved
28
Enable
eXtreme
Performance.
Ergebnisse: Laden der Daten
www.ise-informatik.de

Daten für einen Tag
 ~ 239.634.928 Zeilen/12 Spalten
 TXT Files

Teil 1
~100 G unkomprimiert
Ladezeit ca. 1h aus CC DB
OCR Files in hive
~ 27 M komprimiert
~ Ladezeit ca. 30 Minuten
Type
Größe
Select Where
count
Oem_data
BigDataSQL
2,8 MB
2,1 Mio
11s
Oem_data local
kopiert
Oracle
558 MB 0,5s
2,1 Mio
0,5s
Oem_data
Hive
57s
50s
Copyright (C) ISE GmbH - All Rights Reserved
8s
Teil 2
29
Enable
eXtreme
Performance.
Lastverteilung Big Data SQL
www.ise-informatik.de


Only data retrieval (TABLE ACCESS FULL und Filter ) werden
offloaded!
Datenbearbeitung im DB Layer



GROUP BY, ORDER BY, JOIN, PL/SQL etc
BigDataSQL 2.0 (Aggregation in Hadoop?)
Alternativ Connect über ODBC
Tool
Beschreibung
Sqoop
Oracle SQL
Connector
für HDFS
Text Dateien HDFS
oder DataPump
HDFS
Big Data
SQL
12c
Exadata&BDA
ODBC
Decompress
CPU
Filtering CPU
Datatype
Conversion
Hadoop
Oracle
Oracle
Oracle
Oracle
Hadoop
Hadoop
Hadoop
Hadoop
Hadoop
Oracle
Copyright (C) ISE GmbH - All Rights Reserved
30
Enable
eXtreme
Performance.
Zusammenfassung
www.ise-informatik.de
Vorher:
Exadata €€€
DB/EMC €€€
Nacher:
Hadoop
€
Exadata €
Integration Layer
DB/EMC €
Copyright (C) ISE GmbH - All Rights Reserved
31
Enable
eXtreme
Performance.
www.ise-informatik.de
Q& A
Copyright (C) ISE GmbH - All Rights Reserved
32