Offloading Data Warehouse – Objekte mit Hadoop

Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
Offloading Data Warehouse –
Objekte mit Hadoop
Konzepte und technische Umsetzung
Mit Beispiel-Szenario
September 2016
1
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
2
Die Themen
Offloading Data Warehouse –Objekte mit Hadoop Konzepte und technische Umsetzung ............ 1
Big Data und mehr.. ...................................................................................................................... 3
Den schnellen Nutzen für das Data Warehouse mit Hadoop ..................................................... 3
Die Hadoop-Technologie verspricht Hilfe ...................................................................................... 4
Offloading Data Warehouse? Aber wie? ....................................................................................... 4
„Finden der richtigen Kandidaten für das Offloading“ ................................................................. 5
Die Umsetzung dieser Architektur ................................................................................................. 6
Voraussetzungen ....................................................................................................................... 6
Selektieren der Daten im DATA WAREHOUSE ......................................................................... 6
Bewegen der Daten in das HDFS .............................................................................................. 7
Die Organisation der Daten im HDFS ........................................................................................ 8
Der Abfragen-Part ......................................................................................................................... 8
SQL ............................................................................................................................................ 8
Hive ............................................................................................................................................ 8
Big Data SQL ............................................................................................................................. 9
Die Anwendung von Big Data SQL und die Hilfsobjekte .......................................................... 11
Gesplittete Fakten Tabellen und die Kombination von In-Memory Datenbank und HDFS ....... 12
Zusammenfassung der Faktoren, die die Leseperformance auf HDFS beeinflussen .............. 12
Ein Star Schema Szenario .......................................................................................................... 13
Die Ausgangsdaten .................................................................................................................. 13
Genutzte Infrastruktur............................................................................................................... 14
Übersicht zu den genutzten Objekte in dem Szenario und die Vorgehensweise ..................... 15
Schritt 1: Sqoop – Der Offload-Vorgang ................................................................................... 15
Schritt 2: Die Daten im HDFS ................................................................................................... 16
Schritt 3: Die Hive Definition ..................................................................................................... 16
Schritt 4: External Table in der Oracle Datenbank ................................................................... 17
Schritt 5. Die Abfragen ............................................................................................................. 17
Erfahrungen, Bewertung und Ausblicke ...................................................................................... 24
Datenmengen und Realität ....................................................................................................... 24
Technisches Know how ist beherrschbar ................................................................................. 24
Implementierung, Implementierungsaufwand und was sich wirklich ändert ............................. 24
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
3
Big Data und mehr..
Heute gibt es eine Reihe von Einsatzszenarien für Big Data, auch wenn der Begriff Big Data
zunehmend verwischt und oft für alles herhalten muss, was Softwarehersteller anbieten. Die
Diskussion hat aber das Bewusstsein gegenüber Daten in den Unternehmen, und mehr noch,
gegenüber den Daten in unseren Alltagssituationen geschärft. Data Warehouse-Anwendungen
haben in der Vergangenheit immer wieder versucht zumindest die Unternehmensdaten
zusammenzufassen, zu konsolidieren und sie passgenau den Anwender für Analysezwecke
anzubieten. Big Data-Konzepte versprechen heute mehr:




Sie beschäftigen sich mit neuen Datenquellen außerhalb der Unternehmen,
Sie beschäftigen sich mit neuen Datenarten, z. B. Kommunikations- und Bewegungsdaten,
Sie wenden neue Prinzipen an z. B. „zuerst sammeln dann mal sehen, was man davon hat“
…
Ohne
diese
Liste
fortzusetzen
lässt
sich
jetzt
schon
feststellen:
Das klassische Data Warehouse wird weiter seine Dienste tun, aber mit Big Data wird es noch mehr
können.
Den schnellen Nutzen für das Data Warehouse mit Hadoop
Die Big Data Diskussion ist aktuell geprägt von der Suche nach Use-Cases, neuen
Geschäftsoptionen oder der Suche nach dem, was man alles mit den uns umgebenden Daten
anfangen kann. Die vorliegende Darstellung sucht nicht nach neuen Use Cases, sondern Sie
beschäftigt sich mit den Potenzialen der Hadoop-Technologie als Erweiterung der bestehenden Data
Warehouse – Lösungen in den Unternehmen. Die Früchte der neuen Technologie liegen zumindest
für das Data Warehouse greifbar nah, wie die vorliegende Beschreibung zeigen wird. Die HadoopTechnologie liefert für das klassische Data Warehouse mindestens die folgenden drei interessanten
Nutzenaspekte:
1. Datenvolumen in den Griff bekommen
Es ist die Natur von Data Warehouse-Systemen, dass sie wachsen, und je wichtiger die Rolle des
DATA WAREHOUSE im Unternehmen wird, umso größere Datenmengen sammeln sich an und
damit steigen auch die Kosten, durch das Speichern von gewaltigen Datenmengen in
hochentwickelten relationalen Datenbanksystemen.
2. Auch bei extrem hohen Datenmengen performant auswerten
Bei großen Datenmengen wird trotz schnellerer Datenbanktechnik der Performance-Aspekt wieder
relevant. Abfragen auf 100 Terabyte große Tabellen stehen im Raum. Das Cluster-Verfahren, das
die Hadoop-Technologie mit sich bringt, verspricht die Performance-Herausforderung mit einem
überschaubaren Invest zu beherrschen.
3. Neue Datenarten: „Es muss nicht immer alles rein ins Data Warehouse“
Die Analysen zu Strategie und Taktik von Unternehmen haben sich in den vergangenen Jahren
gewandelt: hat man bislang primär den Unternehmenserfolg durch Berichte und Statistik gemessen,
so sucht man heute neue Handlungsoptionen und Geschäftsfelder wo immer man sie auch finden
kann. Bei den dazu benötigten neuen Datenarten wie Bewegungs-, Wetter-, Video- oder Audiodaten
fragen sich viele Verantwortliche, ob diese Daten noch in ein herkömmliches DATA WAREHOUSE
gehören. Anderseits will man diese Daten einem Analysevorgang unterziehen und sie mit
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
4
herkömmlichen Daten kombinieren. Also gehören sie doch wieder in das DATA WAREHOUSE und
lassen das Datenvolumen noch mehr wachsen.
Die Hadoop-Technologie verspricht Hilfe
Die Mitte des letzten Jahrzehnts entwickelte Hadoop-Technologie liefert im Kern zwei entscheidende
Merkmale:
1. Mit HDFS (Hadoop Distributed File System) ein über einen beliebig erweiterbaren RechnerVerbund (Cluster) verteiltes Dateisystem, in dem man ohne Gedanken an Ort und Platz zu
verschwenden, alle massenhaft gesammelten Daten loswerden kann.
2. Mit MapReduce (bzw. Spark als Nachfolgeverfahren) ein extrem parallelisierbares
Bearbeitungsverfahren, das auch sehr große Datenmengen bewältigen kann.
Hadoop verspricht darüber hinaus eine Reihe von Vorteilen:
Kosten: Das HDFS begnügt sich mit Standard Hardware. Man kann sehr große Datenmengen mit
kostengünstigen vorhalten und für aus dem Open Source Apache-Pool stammende Hadoop
Software sind keine Lizenzen an Hersteller zu bezahlen.
Schema on Write vs. Schema on Read: Anders als in einem Datenbanksystem, das eingehende
Daten zunächst syntaktisch prüft und z. B. in Spalten organisiert (Schema On Write), legt man bei
dem HDFS die Daten ungeachtet einer möglichen Struktur oder syntaktischen Korrektheit direkt ab.
So sind große eingehende Datenströme sehr schnell versorgt. Allerdings muss der spätere
Analysevorgang die Strukturierung dieser Daten nachholen (Schema on Read).
Offloading Data Warehouse? Aber wie?
Bzgl. Data Warehouse heißt das Zauberwort jetzt „Offloading“. D. h. man verlagert die wachsenden
Daten des DATA WAREHOUSE in das HDFS.
Ziel ist vor allem Kosten zu sparen:


bei der Datenhaltung und
durch die Aufwandminimierung bei dem ETL-Prozess (Schema on Read - Gedanke).
In einigen Vorträgen, gerade aus der Open Source Community, hört man schon Vorschläge nach
denen das DATA WAREHOUSE komplett in das HDFS gelegt werden soll. Aber bei dieser
pauschalen Betrachtung sollte man den Sinn und Zweck eines DATA WAREHOUSE nicht aus dem
Auge verlieren. Denn zum einen soll ein DATA WAREHOUSE z. B. durch Normalisierung eine
gewisse Redundanzfreiheit und Harmonisierung in den Daten gewährleisten und weiter: Ein DATA
WAREHOUSE soll durch eine bewusste Strukturierung der Daten den Analyseprozess unterstützen,
Beispiele sind hier die hierarchisch aufgebauten Dimensionen und ihre Beziehungen zu einer
Faktentabelle im multidimensionalen Star-Modell.
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
5
„Finden der richtigen Kandidaten für das Offloading“
Also bedeutet das: wir müssen die unterschiedlichen Ziele von neuer Technologie und
sinnvollen Warehouse-Konzepten miteinander in Einklang bringen. Ein erster Schritt ist die
Auswahl von Datenarten, die sich für ein Offloading anbieten1.
Ausgangsarchitektur eines Data Warehouse
Im Integration Layer (Data Stage) hatte man bislang Daten zu Prüf- und Aufbereitungszwecken
nur temporär vorgehalten und man versuchte die eingehenden Datenmengen durch Selektion
möglichst gering zu halten, um das Datenwachstum im DATA WAREHOUSE zu begrenzen. Mit
Hadoop lässt sich die Funktion eines Integration Layers jetzt offener gestalten: Man kann Daten dort
liegen lassen, von denen man noch nicht weiß, wer sie wie später analysieren wird. Man sammelt
Daten auf Vorrat und hat damit die Idee des Data Lake verwirklicht. In diesem Data Lake sammelt
sich jetzt alles ungeachtet der Struktur, Quelle, Machart und späteren Verwendung. Nur Teile daraus
wandern wirklich in das DATA WAREHOUSE. Ein als Data Lake umfunktionierter Integration Layer
wird auch für Endbenutzer geöffnet, was bislang undenkbar war.
Im Enterprise Layer (Kern-Warehouse-Schicht) finden wir die Masse der Stamm- und
Referenzdaten. Diese reflektieren die Unternehmens- und auch Geschäftsprozessstruktur. D. h.
diese stellen in Verbindung mit entsprechenden Kennzahlen einen strategischen Wert für das
Unternehmen dar. Solche Informationen gehören in einen standardisierten, strukturierten und von
Redundanzen bereinigten Raum. Der beste Platz dafür ist eine relationale Datenbank. Es bleiben
die voluminösen granularen, transaktionalen Bewegungsdaten, die einen sehr hohen Anteil der
Gesamtdatenmenge des DATA WAREHOUSE ausmachen. In diesem Pool entstehen die ersten
Kandidaten für ein Offloading.
Im User View Layer (Data Marts) mit den multidimensionalen Star Schemen bieten sich die großen
Fakten-Tabellen an. Allerdings bei nach Zeit partitionierten Faktentabellen nur die älteren Daten. Die
Partitionen der jüngsten Berichts- Analyseperioden würde man für eine In-Memory-Speicherung
vorhalten. Auch die hierarchisch strukturierten Dimensionen bleiben in der relationalen Datenbank.
1
Für diese Betrachtung wird Verständnis für eine grundlegende Warehouse-Architektur
vorausgesetzt.
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
6
Mögliche Zielarchitektur eines mit Hadoop-Technik erweiterten Data Warehouse
Die Umsetzung dieser Architektur
Voraussetzungen
Mindestens zwei Voraussetzungen
müssen bei der Umsetzung einer
solchen Architektur gegeben sein:
1. Data Warehouse-Daten
sollten selektierbar sein und
performant periodisch in das
HDFS bewegt werden
können.
2. Die HDFS-Daten sollten
kombiniert mit den
Datenbankdaten mit SQL
abfragbar sein. Mehr noch: Es
sollte möglich sein, sog.
Hybride Tabellen mit einem
einzigen Select abzufragen,
also Tabelle, die je nach Alter
der Daten ein Teil in der
Datenbank (eventuell InMemory) und einen zweiten
Teil im HDFS liegen haben.
Offload von Faktentabellen
Selektieren der Daten im DATA WAREHOUSE
Selektierbar sind Daten Im DATA WAREHOUSE über eine entsprechende SQL-Abfrage immer.
Allerdings kann man diese Selektion durch die zeitbezogene Partitionierung erleichtern und vor
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
7
allem standardisieren. Große Faktentabellen sind in einem Oracle DATA WAREHOUSE meistens
partitioniert, so dass das Verfahren aus Datenbank-Sicht leicht möglich ist.
Bewegen der Daten in das HDFS
Zur Datenbewegung aus der Datenbank in das HDFS nutzt man entweder das bidirektionale Sqoop
(Open Source und Standort HDFS-Cluster) oder das Oracle Tool Copy2Hadoop (Standort Oracle
Datenbank).
Sqoop ist ein Batch-Utility zum Exportieren und Importieren von Daten aus relationalen
Datenbanken. Oracle stellt für Sqoop ein zusätzliches Add-In (OraOop) zur Verfügung, das den
Zugriff von Sqoop auf die Oracle Datenbank optimiert. Mit Sqoop lässt sich ein Query-Parameter
formulieren, der analog zur Where-Klausel eines Select-Statements arbeitet. Man kann jedoch auch
komplette Tabellen und, was dem hier angesprochenen Konzept entgegen kommt, einzelne
Tabellen-Partitionen über deren Namen ansprechen und in das HDFS laden. Durch das Oracle-AddIn OraOop kennt Sqoop die Strukturdaten, wie sie in dem Oracle – Datenbank-Dictionary enthalten
sind, sehr genau, so werden u. a. Feldtyp-Konvertierung passgenau unterstützt.
Sqoop erzeugt auf dem Hadoop-Cluster einen MapReduce-Job. Damit ist der Vorgang auch
unabhängig von der Datenbank massiv parallelisierbar.
Mit Sqoop lässt sich aber auch die Zielwelt im HDFS sehr gut bestimmen. Sqoop unterstützt die
wichtigsten File-Typen wie Parquet, ORC2, AVRO und natürlich einfache CSV-Dateien. Die
Zielobjekte sind beim Schreiben komprimierbar, man kann sie partitionieren und auch automatisch
Hive-Metadaten erzeugen lassen.
Die beispielhaften Ladezeiten in der folgenden Tabelle entstammen aus einem Szenario mit einer
Exadata Quarter Rack und Big Data Appliance Starter Rack (6 Cluster Knoten):
Ladezeiten von sqoop
2
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ORC#LanguageManualORC-FileStructure
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
8
Das Oracle-Utility Copy2Hadoop erzeugt mit Hilfe einer External Table ein Datapump-File und legt
es im HDFS ab. Dieses Datapump-File ist später wieder über eine External Table aus der Datenbank
heraus lesbar. Und, das sollte nicht vergessen werden, dieses File kann auch über Hive 3 im HDFS
gelesen werden. Es besteht also durchaus eine gewisse Offenheit.
Setzt man eine Oracle Exadata-Maschine in Verbindung mit Oracle Big Data Appliance ein, so kann
man den Ladevorgang mit beiden Tools durch das dazwischen eingesetzte Infiniband-Protokoll
gegenüber TCP/IP erheblich beschleunigen.
Die Organisation der Daten im HDFS
Die Ablage der Daten im HDFS sollte nicht zufällig erfolgen, denn man hat Optionen, die sich auf
das spätere Lesen der Daten deutlich auswirken können.
Eine erste Option ist die Komprimierung. Komprimierungs-Utilities (Gzip, Snappy) können z. B.
während des Schreibvorgangs durch Sqoop angesprochen werden. Komprimierungsfaktoren von 24 lassen sich gut erreichen und damit minimiert sich vor allem der später aufzubringende IOLeseaufwand. Das Lesen wird also schneller.
Performantes Lesen wird auch durch den Dateityp unterstützt. Auch in der Hadoop-Szene geht man
dazu über, Daten in einer organisierten und strukturierten Form zu speichern. Parquet, ORC und
AVRO Files sammeln Metainformationen über die Struktur der Daten und speichern sie in separaten
Objekten. Solche Dateien sind schon fast eigenständige Datenbanken mit Indexblöcken, ColumnStrukturen und Metadatenverweisen. Es ist also zu überlegen, die ausgelagerten Warehouse-Daten
z. B. als Parquet- oder AVRO-File abzulegen. Das später von Oracle eingesetzte Big Data SQL wird
mit allen Dateitypen zurechtkommen und von der Optimierung, den diese File-Typen mitbringen
profitieren.
Der Abfragen-Part
SQL
Die ausgelagerten DATA WAREHOUSE-Daten befinden sich jetzt auf einem Hadoop-System. Die
Anwender arbeiten jedoch weiterhin mit den Daten in dem DATA WAREHOUSE. Sie werden von
der zusätzlichen Datenhaltung im Hadoop nichts merken. Der Grund dafür ist die Verwendung von
SQL. SQL wird in vielen Business Intelligence- und anderen Analysewerkzeugen als die
Abfragesprache schlechthin genutzt. Gelingt es mit SQL auch auf HDFS-Daten zuzugreifen, so sind
all diese Werkzeuge ohne Einschränkung oder zusätzlichen Lernaufwand für die Anwender
weiterhin nutzbar.
Hive
In aktuellen Hadoop-Umgebungen wird das Lesen u. a. durch Hive unterstützt. Hive stellt eine Art
SQL-Engine in der Hadoop-Welt dar. Die wichtigste Eigenschaft von Hive ist dabei die Metadatenartige Strukturierung von HDFS-Files. Danach erscheinen z. B. CSV-Files als wenn es Tabellen
einer relationalen Datenbank wären, auf die man mit HiveQL zugreifen kann. Zudem unterscheidet
Hive zwischen externen und internen Tabellen. Interne Tabellen organisiert Hive in eigenen
3
Auf Hive wird weiter unten noch eingegangen.
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
9
Dateiverzeichnissen. Beide Tabellenarten sind jedoch letztlich Files in dem HDFS-Directory, die
auch ohne Hive geschrieben, gelesen und bearbeitet werden können.
Big Data SQL
Oracle Big Data SQL nutzt diese Metadaten-Funktion von Hive. Für das Lesen der eigentlichen
Daten wendet man die bereits von der Exadata her bekannten Smart-Scan-Prozesse an. Dabei
überträgt man die Abfrageanforderung des Selects aus der Oracle Datenbank auf ein kleines
Softwarepaket auf jedem einzelnen Cluster-Knoten des Hadoop-Systems.
Man verfolgt zwei Ziele:


Das schnelle Lesen und Interpretieren der HDFS-Daten,
Das Reduzieren der Datenmenge, die zur eigentlichen Datenbank-Instanz und damit zum
Anwender fliest.
Wie in dem Beispiel weiter unten zu sehen ist, entstehen dabei extrem gute Performancewerte mit
einer horizontalen Skalierung, denn das Verfahren nutzt die Hadoop-eigene Parallelisierung über
die Rechenkerne der Hadoop-Clusterknoten. Zum Verstehen der Daten in den HDFS-Datenblöcken
kommen C-basierte Lese-Routinen und native Hadoop Klassen zum Einsatz. Erst danach findet eine
Konvertierung in „Oracle-Verstehbare“-Column- und Feldstrukturen statt.
Der Lesevorgang in Hadoop durch Big Data SQL
Oracle hat in diesem Verfahren eine Reihe von Optimierungen eingebaut:

Der Storage-Index merkt sich z. B. Max/Min-Werte pro Column und Block. Dieser Effekt wird
natürlich erst spürbar sein, nachdem die HDFS-Daten bereits einmal gelesen und der Index
dynamisch aufgebaut wurde.
Der Storage-Index berücksichtigt Abfrage-Bestandteile wie:
o Equality (=)
o Inequality (<, !=, or >)
o Less than or equal (<=)
o Greater than or equal (>=)
o IS NULL
o IS NOT NULL
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
10
Ein Storage-Index ist Knoten-lokal. D. h. er bezieht sich immer nur auf die Daten, die auf einem
einzelnen Hadoop-Cluster-Konten liegen. Sind Daten von einem weiteren Knoten von der
Abfrage betroffen, so muss dieser neu aufgebaut werden. Für eine HDFS-Datei (Hive-Table) sind
bis zu 32 Columns indizierbar. Wird allerdings aus der Datenbank noch eine zweite External
Table (siehe weiter unten) definiert, so erhält man weitere 32 Indexe.Die Storage Indexe arbeiten
für numerische Felder genauso wie für Character und Date-Felder.

Viele, von der Datenbank her bekannten SQL-Funktionen, bringt Big Data SQL in einem
Offload-Schritt auf die Cluster-Knoten. Das Ausnutzen solcher Funktionen auf der HadoopSeite verlagert den wohl größten Teil des Interpretationsaufwands auf die Hadoop-Seite und
wenn es einschränkende Funktionen sind, dann minimiert sich die zu bewegende Datenmenge
enorm. Hier ein Ausschnitt aus den „Offloadable Functions“:
!=
<
<=
=
>
>=
ABS
ACOS
ADD_MONTHS
ADJ_DATE
ASCII
ASCIISTR
ASIN
ATAN
ATAN2
BIN_TO_NUM
BITAND
CAST
CEIL
CHARTOROWID
CHR
CLUSTER_ID
CLUSTER_PROBABILI
TY
COALESCE
COMPOSE
CONCAT
CONVERT
COS

COSH
CSCONVERT
DECODE
DECOMPOSE
DUMP
EXISTSNODE
EXP
EXTRACTVALUE
FEATURE_ID
FEATURE_VALUE
FLOOR
FROM_TZ
GREATEST
GROUPING
HEXTORAW
INITCAP
INSTR
INSTR2
INSTR4
INSTRB
INSTRC
JSON
JSON_EXISTS
JSON_QUERY
JSON_VALUE
LAST_DAY
LEAST
LENGTH
LENGTH2
LENGTH4
LENGTHB
LENGTHC
LN
LNNVL
LOG
LOWER
LPAD
LTRIM
MAX
MIN
MOD
MONTHS_BETWEEN
NANVL
NCHR
NEW_TIME
NEXT_DAY
NLSSORT
NLS_CHARSET_ID
NLS_CHARSET_NAME
NLS_INITCAP
NLS_LOWER
NLS_UPPER
NUMTODSINTERVAL
NUMTOYMINTERVAL
NVL
NVL2
ORA_CLUSTERING
ORA_HASH
ORA_RAWCOMPARE
ORA_RAWCONCAT
POWER
PREDICTION
PREDICTION_COST
PREDICTION_PROBA
BILITY
RAWTOHEX
RAWTONHEX
REGEXP_COUNT
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR
REMAINDER
REPLACE
REVERSE
ROUND
ROWIDTOCHAR
ROWIDTONCHAR
RPAD
RTRIM
SESSIONTIMEZONE
SIGN
SIN
SINH
SOUNDEX
SQRT
SUBSTR
SUBSTR2
SUBSTR4
SUBSTRB
SUBSTRC
TAN
TANH
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR
TO_DATE
TO_DSINTERVAL
TO_MULTI_BYTE
TO_NCHAR
TO_NUMBER
TO_SINGLE_BYTE
TO_TIME
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_TIME_TZ
TO_YMINTERVAL
TRANSLATE
TRIM
TRUNC
TZ_OFFSET
UNISTR
UPPER
VSIZE
Der Grad der Parallelisierung hängt in der Datenbank von der Anzahl möglicher Prozesse ab
(Anz. Threads). Das gilt jedoch nur für den Datenbank-Anteil einer Abfrageaktion. Der hier
beschriebene Hadoop-Anteil unterliegt eigenen und Datenbank-unabhängigen Regeln. Die
Hadoop-Technik ist eine Parallelisierungs-Technik. Z. B. verfügt die Oracle Big Data Appliance
Maschine in der 6-Knoten-Starter Rack-Variante bereits über 264 Rechenkerne, womit eine
Parallelisierung von 264 auch unter Berücksichtigung weiterer paralleler anderer Aktivitäten auf
der Maschine möglich ist. D. h. im Ergebnis wird der Smart-Scan-Vorgang auf der HadoopMaschine mit 264 parallelen Scan-Anteilen und der Datenbank-Anteil des Selects vielleicht mit
parallel 24 durchgeführt. Der Parallelisierungs-Faktor auf der Hadoop-Maschine wird intern
bestimmt und kann nur durch interne, nicht öffentliche, Parameter beeinflusst werden.
Mit diesem Verständnis im Hintergrund kann man die potenzielle Performance einer Abfrage
auf HDFS-Daten gut einschätzen und zumindest auf die Parallelisierung der Datenbank
einwirken:
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung

11
o Hat man eine Abfrage mit einem hohen Anteil an „Offloadable“-Anteilen, so wird die
Parallelität des Hadoop-Systems gute Dienste leisten. Parallelisierung in der Datenbank
hilft hier nicht.
o Hat man wenige Offload-Anteile, so muss man sich Gedanken um eine höhere
Parallelisierung in der Datenbank machen.
Bloom Filter sind in der Datenbank-Technik zur Join-Bildung bereits bekannt. Auch zur JoinBildung zwischen HDFS-Dateien oder zwischen einer HDFS-Datei und Oracle DatenbankTabellen nutzt Big Data SQL Bloom-Filter. Dabei wird sinnvoll ein großes Objekt mit mehrere
kleinen „gejoint“. Das passt ideal für ein Szenario, in dem man eine große Fakten-Tabelle eines
Star Schemas in das HDFS verlagert, während die stark strukturierten Dimensionen in der
Datenbank bleiben. Das Verfahren erzeugt aus den kleinen Tabellen in der Datenbank
sogenannte Bloom-Filter-Objekte, sendet sie auf die Hadoop-Cluster-Knoten und nutzt sie dort
im Verlauf der Smart Scan-Prozesse zum Filtern der großen HDFS-Dateien.
Verringerung der bewegten Datenmengen zwischen Hadoop und Datenbank durch Bloom Filter
Die Anwendung von Big Data SQL und die Hilfsobjekte
Die Anwender arbeiten weiterhin aus der Oracle heraus. Die HDFS-Daten befinden sich jedoch jetzt
auf dem Hadoop-Cluster. Das sind neben dem Datenbank-Server eine Reihe von separaten
Maschinen. Das Oracle-Mittel für den Zugriff auf außerhalb der Datenbank liegende Daten heißt
External Table. Um das Zusammenspiel der Offload-Lösung zu vervollständigen benötigt man
innerhalb der Oracle Datenbank eine External Table Definition, die über passende
Konfigurationseinträge (DEFAULT DIRECTORY DEFAULT_DIR) den Weg auf den HDFS Name
Node des Hadoop Systems zeigt. Diese External Table Definition lässt man sich am einfachsten von
dem Datenbank-Package
dbms_hadoop.create_extddl_for_hive
erzeugen. (Ein Beispiel dazu ist unten in dem Szenario abgedruckt).
Die zweite Komponente in diesem Zusammenspiel ist eine Hive-Tabelle zur Dokumentation der
Column-Struktur des jeweiligen HDFS-Files. Auch diese Hive-Definition lässt man sich am
einfachsten durch das Sqoop-Utility erzeugen. Sqoop verfügt über einen Steuerparameter, der aus
der zu transportierenden Datenbank-Tabelle eine DDL-Definition in Hive hinterlegt. Will man in Hive
eine externe Tabelle anlegen, was wir in unserem Offload-Szenario tun wollen, so starten man einen
Dummy-Sqoop-Job mit der Query-Option z. B. 1=2. Dann bewegt Sqoop keine Daten aber legt eine
Hive-Definition an, die man manuell auf „external“ ändert.
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
12
Gesplittete Fakten Tabellen und die Kombination von In-Memory Datenbank und
HDFS
An dieser Stelle sollte noch ein besonderer Fall erläutert werden. In der Praxis will man
gegebenenfalls unterschiedliche Bereiche der Analysedaten unterschiedlich performant den
Anwendern anbieten. Z. B. sollen Daten der letzten Berichtsperiode (letzter Tag, letzte Woche, letzte
Dekade, letzter Monat, letztes Quartal ..) besonders performant für die Masse der Anwender
bereitstehen, während Abfragen auf ältere Daten für eine kleinere Benutzergruppe auch etwas
länger dauern können. Die Oracle-Datenbank kennt die Möglichkeit der Partitionierung. Mit diesem
Hilfsmittel lassen sich aktuelle Daten (die jüngsten Partitionen) In-Memory für Abfragen im
Millisekunden-Bereich vorhalten, während ältere Daten im HDFS liegen können.
Soweit ist das vorstellbar, aber offen bleibt die Anforderung nach dem „Single-SQL-Statement“. Die
Anwender wollen u. U. mit einem einzigen Select-Statement gleichzeitig auf In-Memory und HDFS
zugreifen können. Sie werden ihre Abfragetools oder ihr Abfrageverhalten nicht ändern wollen, nur
weil das DATA WAREHOUSE einen Teil der Daten in das HDFS ausgelagert hat.
Hierfür gibt es sicher mehrere Lösungen. Eine davon ist eine geschickte Kombination von Views, die
letztlich mit UNION gekoppelt werden. „Geschickt“ bedeutet in diesem Fall, dass das Verhalten von
Big Data SQL, also z. B. das Offloading von Funktionen in der Where-Klausel, nicht verändert wird.
4
Zusammenfassung der Faktoren, die die Leseperformance auf HDFS
beeinflussen
Nach der Darstellung aller Performance-relevanten Punkte hier eine Zusammenfassung und eine
kleine Best Practice Liste für den Einsatz des Offloading:







4
Als HDFS-Ersatz für Datenbank-Tabellen sollte ein passender File-Type z. B. Parket, AVRO
oder ORC gewählt werden. Dies wird zwar die Schreibperformance des Sqoop-Jobs
verschlechtern, aber Abfragen sind performanter.
Die Dateien sollten im HDFS partitioniert sein.
Das Komprimieren der Daten in dem HDFS sorgt für weniger IO-Aufwand beim späteren
Lesen.
Parallel Query in der Datenbank nutzt nur für den Datenbank-Anteil der Abfrage. Bei sehr
vielen Offload-Funktionen bringt das nicht viel.
Der Storage-Index wird erst beim zweiten Lesevorgang seinen vollen Nutzen erzeugen.
Joins zwischen sehr großen Objekten sollten eher vermieden werden. Joins zwischen sehr
großen Objekten und vielen kleinen Tabellen sind sehr performant.
Bei Abfragen mit einem Partitionierungskriterium in der Where-Klausel kann ein Vergleich
zwischen HDFS und Oracle-Datenbank schnell zu Gunsten der Oracle Datenbank ausfallen.
Fehlt dieses Partitionierungskriterium, so ist HDFS schnell im Vorteil.
Eine Lösung hierfür kann von Autor bezogen werden.
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
13
Ein Star Schema Szenario
Jetzt verfügen wir über alle Bestandteile um das Ganze in einem Szenario komplett durchzuspielen.
Das Konzept, nach dem wir das Szenario ausrichten ist ein Star Schema mit einer partitionierten
Fakten-Tabelle, die komplett oder in Teilen (bzgl. der historischen Partitionen) in das HDFS
ausgelagert werden soll. Diese Fakten-Tabelle wird zu Testzwecken unterschiedlich groß gewählt
um Skalierungseffekte zu erkennen. Die Dimensionstabellen liegen weiterhin in der Oracle
Datenbank. Die Daten werden schließlich mit Sqoop in das HDFS transportiert. Danach sind
Vergleiche mit Beispielabfragen in beiden Umgebungen, also in der herkömmlichen Datenbankbasierten Umgebung als auch in der Hadoop-unterstützten Umgebung, möglich.
Die Ausgangsdaten
Die Ausgangsdaten bestehen aus einem Star Schema mit einer partitionierten Faktentabelle mit
folgenden Einstellungen:





Nach Zeit (Monat) partitioniert. Daten von 2011 – 2016 und damit etwa 60 Partitionen.
Hybrid Columnar Compression - Query low – Variante. Dies meint, dass zwar mit Hybrid
Columnar Compression gearbeitet wird, aber so, dass eine möglichst gute
Abfrageperformance erzielt wird.
(Die Faktentabelle beinhaltet zufallsgenerierte Testdaten. Damit sind alle Felder bis zu dem
definierten Maximum gefüllt und haben sehr heterogene Werte. Unter diesen Umständen
erzielt man naturgemäß geringere Kompressionsraten. In diesem Szenario wurde tatsächlich
auch nur ein Faktor 2 erreicht, gegenüber Faktor 4-10 unter Echtbedingungen. Das hat auch
Auswirkungen auf die Testergebnisse, die besser wären, als sie auch unter diesen
eingeschränkten Bedingungen jetzt schon sind).
Bitmap-Indizierung auf den Fremdschlüsselfeldern der Faktentabelle (Voraussetzung für Star
Query Transformation).
Star Query Transformation wurde aktiviert.
Die Faktentabelle wurde mit „parallel 12“ markiert. D. h. es ist davon auszugehen, dass die
Datenbank-bezogenen Anteile der Abfragen 12-fach parallelisiert durchgeführt wurden.
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
14
Die Größenverhältnisse der Faktentabelle kann man der folgenden Übersicht entnehmen. Es sind
die Größenangaben für die Daten in der Datenbank, im HDFS, in komprimierter und nicht
komprimierter Form für die CSV- und Parquet-File-Formate enthalten. Die Spalten unter Faktor 3
geben die absoluten Mengen nach 3-fach-Spiegelung durch das HDFS an.
Größenverhältnisse der Tabellen und entsprechenden HDFS-Dateien
Genutzte Infrastruktur
Als Testumgebung nutzte man eine Exadata Quarter Rack und eine Big Data Appliance 5. Möglich
wären natürlich auch Umgebungen ohne diese Hardware gewesen. Aber eine Big Data Appliance
Maschine verfügt bereits über eine fertig installierte und konfigurierte Hadoop-Infrastruktur. Man
kann davon ausgehen, dass man in dieser Umgebung optimale Testbedingungen vorfindet. 6
Auf der Big data Maschine ist die Cloudera-Distribution vorinstalliert:








Apache Hadoop (CDH)
Cloudera Impala
Cloudera Search
HBase
Accumulo
Apache Spark
Apache Kafka
Cloudera Manager
Die Maschine verfügt über 6 Cluster-Knoten mit jeweils:
 2 x 22 Core (2.2GHz) Intel ® Xeon ® E5 2699 v4
 8 x 32 GB DDR4 2400 Memory
 12 x 8 TB 7,200 RPM High Capacity SAS Drives
 2 x QDR 40Gb/sec InfiniBand Ports
 4 x 10 Gb Ethernet Ports
 1 x ILOM Ethernet Port
Die Exadata-Maschine ist mit der Big Data Appliance über Infiniband gekoppelt.
5 Fact Sheet: http://www.oracle.com/technetwork/database/bigdata-appliance/overview/bigdataappliance-datasheet-1883358.pdf
6
An dieser Stelle Dank an die Firma ISE Information Systems Engineering GmbH in Nürnberg, die
ihr Exa-Demo Center für diese Tests bereitgestellt hat. In diesem Demo Center können Firmen
ihren Anforderungen testen. www.ise-informatik.de/
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
15
Übersicht zu den genutzten Objekte in dem Szenario und die Vorgehensweise
In dem Szenario sind die folgenden 5 Schritte durchgeführt worden. Schritt 1 und 5 natürlich
mehrfach für die unterschiedlich großen Tabellen (100 / 200 / 400 und 800 Millionen Sätze) und
Komprimierungsvarianten sowie File-Typen.
1. Sqoop-Job auf die Fakten-Tabelle mit den entsprechenden Eintragungen
2. Die Redistribution des neu erstellten Files mit Faktor 3 erfolgt automatisch durch das durch
HDFS
3. Der Eintrag der Metadaten im Hive-Metastore erfolgt manuell nachdem man sich eine DDLVorlage durch sqoop hat generieren lassen
4. Erstellen einer External Table in der Datenbank für späteres SQL
– Hier hilft das Package „dbms_hadoop.create_extddl_for_hive“
5. Big Data SQL Aufruf / SQL – Abfragen. Das sind die eigentlichen Abfrage-Test.
Ablauf des Vergleichs-Test-Szenarios
Schritt 1: Sqoop – Der Offload-Vorgang
Sqoop ist ein Batch-Utility, das man von einem Linux-Cluster-Knoten aus startet. Das folgende
Beispiel baut eine Verbindung zu einer Oracle-Datenbank und dem Schema „DWH“ auf. Es liest die
Tabelle „F_UMSATZ“ und überführt diese in eine Textdatei (Default CSV). Das Add-In „oraoop“ ist
einbezogen. Eine Datei im HDFS wird über ein Verzeichnis abgebildet. Möglicherweise existierende
Daten werden zuvor gelöscht. Ansonsten würde die Datei auch fortgeschrieben werden können.
Sqoop erzeugt einen Map Reduce – Job. Dieser wird hier mit parallel 100 parametrisiert, d. h. es
starten 100 Mapper. Die Datei soll bei dem Schreiben in dem HDFS komprimiert werden (Parameter
–z).
sqoop import -D mapred.job.name='default oraoop'
> --direct --connect jdbc:oracle:thin:@21.108.5.88:1521:dbm088
> --username DHW --password DWH --table F_UMSATZ
\
> --as-textfile --delete-target-dir
> --target-dir /user/sqoop_output/f_umsatz -m 100 -z
\
\
\
Das ist nur eine kleine Auswahl von möglichen Parametern. In dem Sqoop Users Guide sind alle
Parameter beschrieben7.
Die folgende Übersicht enthält die Ladezeiten für die verschieden großen Tabellen und sqoopParallelisierungsgrößen also Anzahl Mapper:
7
https://sqoop.apache.org/docs/1.4.2/SqoopUserGuide.html#_controlling_type_mapping
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
16
Offload-Ladezeiten von sqoop
Schritt 2: Die Daten im HDFS
Das HDFS repliziert die neu erstellte Datei im Hintergrund mit dem Faktor 3 auf unterschiedliche
Cluster-Knoten. Diesen automatischen Vorgang muss man nicht weiter kommentieren. Das passiert
einfach. Hier eine Sicht auf die Daten im HDFS einmal auf die Datei selbst, bestehend aus 149
einzelnen Files (Stripes) in dem Directory „f_umsatz“ und dann ein Blick auf die ersten 4 Sätze. Man
sieht, dass es sich durchaus um lesbare Daten handelt.
Die Datei F_UMSATZ ist im HDFS ist als Directory realisiert
Schritt 3: Die Hive Definition
Das Szenario arbeitet mit einer in dem HDFS direkt liegenden Datei. Hive hat nur die Aufgabe, die
Metadaten zu beschreiben. Daher handelt es sich hier aus der Sicht von Hive um eine External
Table. Also Hive wird nur die Metadaten bereithalten und „überlässt“ die Daten sich selbst. Für
interne Hive Tabellen legt sich Hive eine eigene Struktur an einer anderen Stelle in dem HDFS an.
Bei der Übertragung kann es zu Typ-Konvertierungsfragestellungen kommen. In diesem Beispiel ist
das für das Feld Zeit_ID tatsächlich geschehen. Der ursprüngliche Typ DATE wurde durch Sqoop
nicht entsprechend übertragen, so dass die späteren Join-Abfragen auf das Star-Modell leider leere
Ergebnismengen lieferten. Erst die Korrektur nach Timestamp war letztlich erfolgreich. Auf diese
Oracle-spezifischen Typ-Konvertierungen wird in dem Sqoop-Users-Guide8 explizit hingewiesen.
8 https://sqoop.apache.org/docs/1.4.2/SqoopUserGuide.html#_controlling_type_mapping
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
17
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';
Schritt 4: External Table in der Oracle Datenbank
Die
Definition
der
passenden
External
dbms_hadoop.create_extddl_for_hive.
Table
dazu
liefert
das
Package
Dieses Package greift auf den Hadoop-Cluster zu und liest in Hive die entsprechenden Metadaten
und auch die Speicherorte aus. Wenn die Hive-Einträge korrekt sind, kann man diese External Table
– Definition bereits sofort in der Datenbank aktivieren und direkt danach erste Abfragen formulieren.
Generierung External Table aus Hive-Metadaten
Schritt 5. Die Abfragen
Hier ist ein Ausschnitt aus den Abfragetests dokumentiert. Die Abfragen entstammen der SeminarReihe „Data Warehouse-Technik im Fokus“ und sind dort im Begleitheft9 abgedruckt. Für diese Tests
wurde eine etwas erweiterte Faktentabelle genutzt, um der Praxis etwas näher zu kommen. Hier ist
immer nur die Datenbank-Skript-Variante abgedruckt. Die HDFS-Variante sieht gleich aus. Dort ist
nur in der FROM-Klausel anstelle von F_UMSATZ die External Table F_UMSATZ_HDFS aufgeführt.
Vorbemerkung zu diesen Ergebnissen: Die Ergebniswerte dieses Tests dienen lediglich zur
9 http://oracledwh.de/downloads/AutoIndex2.2.4/index.php?dir=downloads/01_DWH_Seminare_Kurs_Materialien_Folien_und_Skripte/01_01_DWH%20Technik%20im%20Fokus%20Reihe_TI
F/&file=CMD_DWH_Kurzreferenz_V3.docx
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
18
Feststellung der Machbarkeit von Star Schema-Abfragen auf Objekte im HDFS. Sie sind weniger
geeignet, um eine Performance-Vergleichsaussage zwischen dem relationalen Datenbanksystem
und HDFS zu treffen. Hierzu müsste auf beiden Seiten sicherlich noch mehr optimiert werden, z. B.
würde man in der Oracle Datenbank für Performance-kritische Abfragen InMemory einsetzen oder
zusätzliche Techniken wie Caching, Flash, Materialized Views und weitere verwenden. Auf der
HDFS-Seite würde man unterschiedliche File-Typen (Parquet, ORC) einsetzen. Zu erwähnen ist
auch, dass die guten Abfrage-Performancewerte bei der hier dargestellte HDFS-Variante nur durch
die Optimierung von Oracles Big Data SQL mit Function-Offloading, Bloom-Filter, Storage Index,
Column-Pruning und der technischen Architektur der Big Data Appliance Maschine (z. B. durch das
Infiniband Protokoll) bedingt sind. Solche Abfragewerte sind z. B. nur mit Hive nicht zu erzielen.
Letztliche laufen die Datenbankabfragen mit Parallel 12, während die HDFS-Variante auf im 3stelligen Bereich parallelisieren kann. Auch die relationale Datenbank lässt eine Parallelität von z.
B. 128 zu.
Allgemeine Abfrage über die Anzahl Zeilen bei der größten Tabellenausprägung
select count(*) from f_umsatz;
select count(*) from F_UMSATZ_HDFS;
COUNT(*)
---------819200000
COUNT(*)
---------819200132
Elapsed: 00:00:02.85
Elapsed: 00:00: 17.97
Kommentar:
Die Datenbank-Abfrage läuft über den bestehenden Bitmap-Index und ist daher besonders schnell.
Abfrage 1b) Abfrage mit direktem Partition-Kriterium
SELECT /*+ no cache */ sum(UMSATZ) Umsatz
FROM F_UMSATZ WHERE
zeit_ID = to_date('10.03.2011','DD.MM.YYYY');
102
204
408
819
Nr
1b
1b
1b
1b
Kommentar
Abfrage auf Partition
DB-Tabelle
00.66
00.30
00.33
00.89
HDFS-Datei
03.14
04.65
09.04
08.52
Kommentar:
Die Abfrage enthält eine Zeiteinschränkung auf einen Tag und damit trifft man das
Partitionierungskriterium in der Datenbank. Auch wenn die Gesamttabelle größer wird, steigt die
Menge der Daten in einer Partition nicht in gleichem Maße. Der Datenbank ist es in dieser Abfrage
gleichgültig, wie groß die Tabelle wirklich ist. Das Segment einer Partition bleibt immer gleich groß.
Anders verhält es sich bei dem HDFS. Hier steigen die Datenmengen und offenbar auch der
Aufwand bei der Abfrage.
Diese HDFS-Abfragezeiten könnte man sicherlich verbessern, wenn die HDFS-Datei auch
partitioniert wäre. Das ist sie hier aber nicht. So gesehen ist dieser Vergleich nicht korrekt.
Abfrage 1c) mit und ohne Zeit-Kriterium
1. Versuch
SELECT /*+ no cache */ sum(UMSATZ) Umsatz
FROM F_UMSATZ U, D_vertriebskanal V
WHERE
u.kanal_id = v.kanal_id
and zeit_ID = to_date('10.03.2011','DD.MM.YYYY')
and V.vertriebskanal = 'Shop'
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
19
2. Versuch
SELECT /*+ no cache */ sum(UMSATZ) Umsatz
FROM F_UMSATZ U, D_vertriebskanal V
WHERE
u.kanal_id = v.kanal_id
-- and zeit_ID = to_date('10.03.2011','DD.MM.YYYY')
and V.vertriebskanal = 'Shop'
Nr
Kommentar
DB-Tabelle
HDFS-Datei
102
204
408
819
1c
1c
1c
1c
Abfrage mit Zeitkriterium
(Partitioning)
00.05
00.75
00.51
01.04
05.12
02.05
09.81
18.01
102
204
408
819
1c
1c
1c
1c
Abfrage ohne Zeitkriterium (ohne
Partitioning)
12.53
15.51
18.71
35.11
06.64
09.81
06.64
18.61
Kommentar:
Dieses Beispiel hat zwei einschränkende Bedingungen. Eines das auf das Partitionierungskriterium
trifft und eines, das nicht dem Partitionierungskriterium entspricht. Der Test wird zweimal
durchgeführt. Beim zweiten Mal hat man das Zeitkriterium auskommentiert, so dass nur noch die
Einschränkung auf ein Nicht-Partition-Merkmal erfolgt. In dem zweiten Testversuch fällt die
Datenbankabfrage hinter die HDFS-Abfrage deutlich zurück. Ihr fehlt die Möglichkeit die
Datenmenge über die Partitionierung einzuschränken.
Abfrage 2 – Höhere Anzahl Joins / Group by / Order by / 5 Filter / mit und ohne DatenbankParallel 24
SELECT /*+ parallel(F_UMSATZ 24) */ sum(u.UMSATZ) Umsatz,
R.REGION,
Z.Quartals_nummer Quartal
FROM
F_UMSATZ U,
D_Artikel A,
D_Region R,
D_Zeit Z,
D_Kunde K,
D_Vertriebskanal V
WHERE
U.Kunden_ID
= K.Kunden_ID
AND U.Zeit_ID
= Z.Zeit_ID
AND U.REGION_ID
= R.Region_ID
AND U.Artikel_ID
= A.Artikel_ID
AND U.Kanal_ID
= V.Kanal_ID
AND Z.JAHR_NUMMER
= 2011
AND A.GRUPPE_NAME
= 'Bad_Sanitaer'
AND K.BERUFSGRUPPE
= 'Arbeiter'
AND R.REGION
IN
('Mitte','Sued','Nord')
AND V.Vertriebskanal
= 'Shop'
Group by R.Region,Z.Quartals_nummer
Order by Z.Quartals_nummer;
Nr
102
204
408
819
2
2
2
2
102 2
Kommentar
DB-Tabelle
Höhere Anzahl Joins und DB-Parallel 12
5 Joins / Group / Order by 5 02.40
Filter
03.65
04.95
06.81
Höhere Anzahl Joins und DB-Parallel 24
01.59
HDFS-Datei
03.85
13.44
18.08
20.69
03.10
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
204 2
408 2
819 2
5 Joins / Group / Order by / 5 02.48
Filter
03.43
04.07
20
13.36
17.80
21.20
Kommentar:
Man erkennt in diesem Beispiel, dass die Parallelisierung in der Datenbank die Abfrage beschleunigt.
Für die HDFS-basierte Abfrage hat diese Parallelisierung keinen Effekt. Die Hadoop-Umgebung folgt
ihrem eigenen Parallelisierungsverhalten und das wirkt auch ohne die Datenbank-Parallelisierung,
also in diesem Fall auch schon bei dem ersten Test mit Parallel 12.
Abfrage 3: 4 Joins / 2 Filter / Höherer Group by – Datenbank-Rechenanteil
SELECT /*+ no cache */ a.sparte_name,
z.Jahr_Nummer,
r.land,
sum(U.umsatz),
sum(U.menge)
FROM
f_Umsatz U,
d_region r,
d_zeit z,
D_artikel a
WHERE
U.zeit_id
= z.zeit_id
AND
U.REGION_ID
= R.REGION_ID
AND
U.artikel_id
= a.artikel_id and
z.jahr_nummer
= 2012
and
R.Land = 'Bayern'
GROUP by
a.sparte_name, z.Jahr_Nummer, r.land;
102
204
408
819
Nr
3
3
3
3
Kommentar
DB-Tabelle
4 Joins + 2 Filter / Group 02.55
by
03.60
04.81
06.82
HDFS-Datei
08.41
13.73
12.47
18.23
Kommentar:
Dieses Beispiel verhält sich ähnlich wie das zuvor. Allerdings muss hier die Datenbank durch den
größeren Group By – Anteil und die größere Menge an Select-Feldern mehr rechnen. Bei der HDFSAbfrage sind die Antwortzeiten nahezu gleich.
Abfrage 6: Analytische Funktion Rank
select sum(u.umsatz) Umsatz,
z.Jahr_nummer Jahr
,z.Quartals_nummer Quartal,
RANK() OVER (PARTITION by z.Jahr_nummer ORDER
from
f_umsatz u,
d_zeit z
where
z.zeit_id = u.zeit_id and
z.Jahr_nummer between 2010 and 2013
group by z.Jahr_nummer,z.Quartals_nummer
order by z.Jahr_nummer,Rangfolge;
102
204
408
819
Nr
6
6
6
6
Kommentar
Rank + 2 Joins
Kommentar:
BY sum(U.umsatz) ASC ) AS Rangfolge
DB-Tabelle
02.07
10.61
15.50
22.47
HDFS-Datei
03.15
12.26
10.63
14.11
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
21
Die Antwortzeit der Datenbankabfrage wächst mit der Datenmenge, die HDFS-Abfrage wächst
zunächst. Sie stagniert dann aber auf einem niedrigen Niveau, während die Datenbank-Antwortzeit
ebenfalls steigt aber übe die HDFS-Zeit hinausläuft. Zu beachten ist die Einschränkung auf die ZeitDimension. Bei dieser Abfrage hilft die Partitionierung auf Zeit.
Abfrage 7: Analytische Funktion Rank und Subselect
Select /* + no cache */ * from
(select sum(u.umsatz) Umsatz,
z.Jahr_nummer Jahr
,z.Quartals_nummer "Top-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 2010 and 2013
group by z.Jahr_nummer,z.Quartals_nummer)
where Rangfolge = 1
order by Jahr;
102
204
408
819
Nr
7
7
7
7
BY
Kommentar
Rank und Subselect
DB-Tabelle
04.00
08.07
10.39
20.11
HDFS-Datei
02.93
12.30
10.62
17.08
Kommentar:
Analog zu Abfrage 6.
Abfrage 16: Analytische Funktion Rank / Group by / 2 Subselects
select Top.Bundesland, top.Prod_grp,Top.top_umsatz,top.Top_folge,
Bottom.Prod_grp,bottom.Bottom_Umsatz, bottom.Bottom_folge From
(SELECT * FROM
(SELECT r.land Bundesland, a.gruppe_name Prod_Grp,
sum(U.Umsatz) AS Top_Umsatz,
RANK() OVER (PARTITION by r.land ORDER BY
sum(U.umsatz) DESC ) AS Top_folge
FROM f_umsatz U, d_artikel A , d_region r
WHERE
U.artikel_id = a.artikel_id and
U.region_id =r.region_id
GROUP by r.land,a.gruppe_name
ORDER by r.land)
WHERE Top_folge < 3) Top,
(SELECT * FROM
(SELECT r.land Bundesland, a.gruppe_name Prod_Grp,
sum(U.Umsatz) AS Bottom_Umsatz,
RANK() OVER (PARTITION by r.land ORDER BY
sum(U.umsatz) ASC ) AS Bottom_folge
FROM f_umsatz U, d_artikel A , d_region r
WHERE
U.artikel_id = a.artikel_id and
U.region_id =r.region_id
GROUP by r.land,a.gruppe_name
ORDER by r.land)
WHERE Bottom_folge < 3) Bottom
where top.Bundesland = bottom.Bundesland and
top.Top_folge = bottom.Bottom_folge
order by Top.Bundesland;
Nr
102 16
204 16
Kommentar
Ranks / Group by / 2 Subselects
DB-Tabelle
17.69
44.84
HDFS-Datei
09.69
21.65
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
408 16
819 16
01:19.20
02:35.01
22
33.94
01:04.53
Kommentar:
Bei dieser Abfrage wachsen auch die Antwortzeiten bei der HDFS-Abfrage. Allerdings benötigt sie
nur etwa die Hälfte der Zeit der Datenbankabfrage. Bei dieser Abfrage, hilft kein einschränkendes
Partitionierungskriterium. In der Where-Klausel ist keine Einschränkung auf die Zeit-Dimension.
Abfrage 19: Mehrere Subselects
select a.land,
round(a.top_3_umsatz/1000000,2) Top_3_In_Mill,
round(b.Gesamt_pro_land/1000000,2) Gesamt_pro_Land_in_Mill,
round(a.top_3_umsatz/b.Gesamt_pro_land*100,2) Anteil_Prozent
from
(SELECT land, sum(umsatz) Top_3_Umsatz
FROM
(SELECT r.land, artikel_name Artikel, sum(U.Umsatz) AS Umsatz,
RANK() OVER (PARTITION by r.land ORDER BY sum(U.umsatz) DESC ) AS Rangfolge
FROM f_umsatz U, d_artikel A, d_region r
WHERE U.artikel_id = a.artikel_id and
U.region_id = r.region_id
GROUP by r.land, a.artikel_name
ORDER by r.land)
WHERE Rangfolge < 4
group by land) a,
(SELECT land, sum(umsatz) Gesamt_pro_Land
from
f_umsatz U, d_region r
where
U.region_id = r.region_id
group by land) b
where
a.land = b.land;
102
204
408
819
Nr
19
19
19
19
Kommentar
Mehrere Subselects
DB-Tabelle
29.86
43.67
01:04.46
01:48.54
HDFS-Datei
08.02
21.48
29.02
50.31
Kommentar:
Analog zur Abfrage 16..
Abfrage 20: Ntile + geschachtelte Subselects
SELECT sum(umsatz), anteil,
(sum(umsatz)*100/Gesamt_umsatz) as Prozent
FROM
(SELECT k.nachName as kunde, sum(u.umsatz) as Umsatz,
ntile(4) over (order by sum(u.umsatz)) as Anteil
FROM d_kunde K, f_umsatz U
WHERE k.kunden_id = u.kunden_id
GROUP by K.nachName),
(SELECT sum(u.umsatz) as Gesamt_Umsatz FROM f_umsatz_HDFS U) GROUP by
anteil,Gesamt_umsatz;
102
204
408
819
Nr
20
20
20
20
Kommentar
Ntile
+
Subselects
Kommentar:
Analog zur Abfrage 16..
DB-Tabelle
geschachtelte 15.33
20.21
33.01
58.04
HDFS-Datei
04.99
20.04
16.59
40.14
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
Abfrage 22:Cube Funktion
SELECT
a.gruppe_name,
Region,
Land,
sum(u.menge),
grouping(gruppe_name) as AG,
grouping(Region) as RE
FROM
d_artikel a,
f_umsatz U,
d_region R
WHERE
U.artikel_id = a.artikel_id
AND
R.Region_id = U.Region_id AND
Land = 'Schleswig Holstein'
GROUP by cube (gruppe_name,Region,land);
102
204
408
819
Nr
22
22
22
22
Kommentar
Cube
DB-Tabelle
08.57
16.47
18.66
32.11
HDFS-Datei
02.50
10.93
07.56
19.01
Kommentar:
Analog zur Abfrage 16.
Abfrage 23: Vorjahresvergleich
SELECT /*+ no cache */ k.nachName,
z.Jahr_Nummer as Jahr,
z.Monats_Nummer as Mon,
sum(u.umsatz) as Umsatz,
lag(sum(u.umsatz),12) over (ORDER by z.Jahr_Nummer,
z.Monats_Nummer) as vorjahr
FROM D_kunde k, F_Umsatz U, D_zeit Z
WHERE
k.kunden_id = u.kunden_id
AND
Z.zeit_id
= u.zeit_id
AND
z.Jahr_nummer in (2010,2011)
AND
k.nachname = 'Bauer'
group by K.nachName, z.Jahr_Nummer, z.Monats_Nummer
order by z.Jahr_Nummer, z.Monats_Nummer;
102
204
408
819
Nr
23
23
23
23
Kommentar
Vorjahresvergleich Lag
Kommentar:
Analog zur Abfrage 16..
DB-Tabelle
01.02
01.55
02.13
04.25
HDFS-Datei
03.21
12.46
11.00
13.38
23
Offloading Data Warehouse Objekte mit Hadoop – Konzept und technische Umsetzung
24
Erfahrungen, Bewertung und Ausblicke
Datenmengen und Realität
Das Szenario hat gezeigt, dass es durchaus sinnvoll sein kein größere Datenmengen in das HDFS
zu verlagern. Das Szenario wurde mit Tabellen im unteren 3 stelligen GB-Bereich gemacht. Schon
in diesem überschaubaren Datenvolumen konnte die HDFS-Variante ihre Stärken ausspielen.
Allerdings wird man nicht wegen ein paar Sekunden schnellerer Abfragen schon gleich eine neue
Infrastruktur in Form einer Hadoop-Umgebung einführen. Aber vorstellbar sind Tabellen im
Terabyte-Bereich und einigen Milliarden Sätzen und Warehouse-Umgebungen, die 50, 100 Terabyte
Datenvolumen überschreiten oder auch nur Umgebungen in denen eine Hand voll Tabellen mit 5,
10, 20 usw. Terabyte liegen. In diesen Fällen lohnt sich eine HDFS-Ergänzung.
Technisches Know how ist beherrschbar
Ganz nebenbei zeigt die Beschäftigung mit dem Szenario, dass der Know how – Aufbau zum
Bewältigen der technischen Umgebung überschaubar ist. Für erfahrene WarehouseAdministratoren ist ein solches Szenario nach wenigen Tagen beherrschbar.






Viele Punkte sind aus der Linux-Welt bekannt. Hadoop läuft auf Linux-Rechnern. HadoopKommandos lehnen sich an Linux-Kommandos an.
SQL ist bekannt. Hive ist ein SQL-Vehikel.
Die Oracle-Datenbank, also z. B. das Verhalten von External Tables sollte ebenfalls
bekannt sein.
Eine neue Programmier- oder Skriptsprache muss nicht erlernt werden. Man kommt ohne
Java-Kenntnisse aus.
Einzelne Utilities wie sqoop bringen letztlich ihr Handbuch mit, in dem alle Aufrufparameter
stehen. Hier kennt man aus der Oracle-Welt z. B. den SQL Loader, der sich ähnlich verhält.
Wirklich neu ist nur ein gewisses Verständnis für die Hadoop-Denkweise. D. h. das Denken
in Cluster-Knoten und die Art der Parallelisierung.
Implementierung, Implementierungsaufwand und was sich wirklich ändert
Das Szenario zeigt, dass nicht sehr viele Komponenten zu berücksichtigen sind. Wer sich allerdings
zunächst mit der technischen Architektur eines Hadoop-Clusters beschäftigt und versucht die
Cloudera Hadoop Distribution zu installieren oder Performancegrenzen eines selbst gebauten
Clusters austestet, der wird erst sehr spät zu den eigentlichen Zielen gelangen. Mit einer fertigen
Appliance wie Oracles Big Data Appliance (BDA) verfügt man bereits nach 2-3 Wochen über eine
fertige Lösung in der auch schon das Offload-Konzept realisiert ist.
Der besondere Vorteil dieser Herangehensweise ist gerade der, dass an dem logischen Konzept
des bestehenden Data Warehouse nichts geändert werden muss, also:





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 Zukunft.
Rückfragen zu dem Test und zu dem Dokument: Alfred.Schlaucher@oracle Tel 0177 5949132