DATA WAREHOUSE Oracle Data Warehouse Mit Big Data neue Horizonte für das Data Warehouse ermöglichen Alfred Schlaucher, Detlef Schroeder 1 DATA WAREHOUSE Themen 2 Big Data Buzz Word oder eine neue Dimension und Möglichkeiten Oracles Technologie zu Speichern von unstrukturierten und teilstrukturierten Massendaten Cloudera Framwork „Connectors“ in die neue Welt Oracle Loader for Hadoop und HDFS Big Data Appliance Mit Oracle R Enterprise neue Analyse-Horizonte entdecken Big Data Analysen mit Endeca Themen Anforderungen Warum R Die R-Entwicklungsumgebung Oracle R- Enterprise Der transparente Tabellen-Zugriff Statistische Analysen mit R in der Datenbank Visualisierung von Ergebnissen (Plotting) Visualisierung von Ergebnissen (OBIEE) Einbinden in umfangreiche Analyse-Szenarien 3 Anforderungen: Schnellere und elegantere Analyse-Abläufe Ressourcen Geringere Latenzen bei der Datenbereitstellung Linerare Skalierung Vorhersehbare Aufwände und Durchsatz 4 Anforderungen: Effizienterer und leichterer Umgang mit Daten und Informationen Ressourcen Weniger Datenbewegung Leichte Zugänge Flexiblere Tools und flexibler Datenaustausch Mehr Gesamtunternehmensdaten in der Analyse 5 Mehr Informationen Versteckte Beziehungen entdecken R Statistische Programmiersprache OPEN SOURCE Sprache und Umgebung STATISTISCHE BERECHNUNGEN und Graphik PCA 5 online returns users history STÄRKE liegt in der schnellen graphischen Aufbereitung (Plots) >5,300 statistische Packages sales Clustering 4 Groups Factor 1 28 LEICHT ERWEITERBAR durch Open Source Community 6 16 2 80 60 40 20 0 1 Factor 3 Anforderungen: Flexibles Arbeiten Analysieren integrierter Data Ressourcen User Leichte Facts Zugänge Mehr Möglichkeiten Mehr Informationen Interaktives Arbeiten, Erstellen von Ergebnissen und Weiterverarbeiten Ad Hoc Analysieren 7 Warum nutzen immer mehr Anwender R Warum R Kann mehr Oft mehr Funktionen, als in klassischen Tools Ist schnell Neue Funktionen, sind oft in R als erstes implemtiert Point 8 Ist ansprechbar offene Kommunikation Die Entwickler der Funktionen sind oft per Mail direkt erreichbar “Schläft nicht” Weltweit wird mit R gearbeitet Ist günstiger Was kosten die übrigen Tools? Warum sollte man sich für R interessieren Gehört zu den neuen aufkommenden Trends – Next “big thing” in Avanced Analytics – Moderne statistische Programmiersprache – Ausbildungsinstitute und Universitäten nutzen R für die Ausbildung (Sie ersetzen traditionelle Tools) – Advanced Analytics ist zunehmend kritisches Unterscheidungsmerkmal im DWH Technologie Stack R wird durch Oracle R Enterprise skalierbar Kostengünstige Alternative zu SAS 9 Graphische Bediener-Oberflächen Auswahl bei den GUIs Bereiche: – R Console Plot-Bereich Ergebnis-Bereich Messages Standard GUI / Rstudio / Rcommander/.../... 10 Live Live Daten und Objekte 11 Live Live Einfache Beispiele zur Darstellung der Sprachmimik > alter <- c(19,20,20,19,25,26,22,25,29) Vektor > geschl <- c(1,2,2,1,2,2,2,1,2) > geschl.faktor <- factor(geschl) Faktor > bsp.data.frame Data-Frame <- data.frame(alter,geschl.faktor) round(tapply(alter ,geschl,mean,na.rm=TRUE),0) 12 Live Live Daten-Handling Lokale Daten Datenbank R Engine df <-read.csv(file.choose()) 13 class(df) names(df) objects() dim(df) Daten-Visualisierung mit R http:// gallery.r-enthusiasts.com/ 14 Live Live Oracle R Enterprise Oracle Advanced Analytics - Oracle R Enterprise and Oracle Data Mining R code und/oder SQL Modelle laufen “In-Database” Große Datenmengen Built-in security 15 Oracle R-Angebote Oracle R Distribution – Free download, pre-installed on Oracle Big Data Appliance, bundled with Oracle Linux – Enterprise support for customers of Oracle R Enterprise, Big Data Appliance, and Oracle Linux – Contribute bug fixes and enhancements to open source R ROracle – Open source Oracle database interface driver for R based on OCI – Maintainer is Oracle – rebuilt from the ground up – Many bug fixes and optimizations Oracle R Enterprise – Transparent access to database-resident data from R – Embedded R script execution through database managed R engines – Statistics engine Oracle R Connector for Hadoop 16 – R interface to Oracle Hadoop Cluster on BDA – Access and manipulate data in HDFS, database, and file system – Write MapReduce functions using R and execute through natural R interface Mögliche Szenarien mit Oracle R-Enterprise RAM RAM File System R Engine Direkten Zugriff auf alle Tabellen in der Datenbank RAM RAM File System R Engine Parallelisierung durch die Datenbank Auslagern der Analysen in die Datenbank Zurückholen der Ergebnisse R Engine R Engine R Engine R Engine Anlegen neuer Objekte in der Datenbank RAM RAM R Engine Parallelisierung durch die Datenbank R-Analysen über SQL-Funktionen (Batch) File System SQL R Engine R Engine R Engine R Engine RAM 17 Oracle R Enterprise – Data Sources R user on desktop R Engine Andere Datenbanken Direkter Zugriff RODBC, DBI, etc Oracle R Enterprise Andere R packages Packages Direkter Zugriff Import / Load Data Push Pull Results SQL Transparent Layer Parallel Aufrufe Select ...Fro ..Table(....) begin User tables Oracle Datenbank Database Links Andere Datenbanken 18 File systems Create Function end External Tables File systems Bulk import R Engine R Engine Engine Oracle R R Enterprise Andere R Oracle R Enterprise Other R packages Packages Oracle R Enterprise Other R packages packages packages packages Transparency Layer Support ORE bietet eine “in-database execution” – Funktionalität als transparente Schicht an What’s transparent about it? – R Benutzte benötigen nur R Syntax – Benutzer sehen Datenbank-Objecke as spezielle R Objekte – Unterstützt weden fast alle R-Funktionen des Basis-Pakets – Unterstützt R's Statistik und Graphik-Pakete Funktional vergleichbar mit SAS DATA STEP, läuft allerdings in- Datenbank! 19 ORE Packages Package ORE Description Top Level Package for Oracle R Enterprise OREbase Corresponds to R’s base package OREstat Corresponds to R’s stat package OREgraphics Corresponds to R’s graphics package OREeda ORE’s exploratory data analysis package containing SAS PROCequivalent functionality ORExml ORE’s package supporting XML translation between R and Oracle Database - internal ORHC Oracle R Hadoop Connector 20 ©2012 Oracle – All Rights Reserved Funktionen und Methoden 21 Funktions- und Methodenübersicht • Mathematische Funktionen: abs, sign, sqrt, ceiling, floor, trunc, cummax, cummin, cumprod, cumsum, log, log10, log2, log1p, acos, acosh, asin, asinh, atan, atanh, exp, expm1, cos, cosh, sin, sinh, tan, tanh, gamma, lgamma, digamma, trigamma, round, signif, pmin, pmax, zapsmall • Basis Statistik: mean, summary, min, max, sum, any, all, median, range, IQR, fivenum, mad, quantile, sd, var, table, rowSums, colSums, rowMeans, colMeans • Rechnen: +, -, *, /, ^, %%, %/% • Vergleichen: ==, >, <, !=, <=, >= • Logik: &, |, xor • Mengenbezogene Abgleiche: unique, %in% • Zuweisungen: <-, =, -> 22 Live Live Funktions- und Methodenübersicht • Strings: tolower, toupper, casefold, toString, chatr, sub, gsub, substr, substring, paste, nchar • Data Frames Kombinieren / Ergänzen: cbind, rbind, merge • Combine vectors: append • Vector creation: ifelse • Subset: [, [[, $, head, tail, window, subset, Filter, na.omit, na.exclude, complete.cases • Daten umgruppieren: split, unlist • Datenverwalten: eval, with, within, transform • Apply Varianten: tapply, aggregate, by • Regression: ore.lm() - a variant of lm() • Werte-Checks: is.na, is.finite, is.infinite, is.nan • Metadaten Funktionen: attributes, nrow, NROW, ncol, NCOL, nlevels, names, row, col, dimnames, dim, length, row.names, col.names, levels, reorder 23 Live Live Funktions- und Methodenübersicht • Graphik: hist, boxplot, plot, smoothScatter • Garbage collection: gc (Löschen von temp. tabellen) • Konvertierungen: as.ore.{character, factor, integer, logical, numeric, vector} • Test Funktionen: is.ore.{character, factor, integer, logical, numeric, vector} • Save: ore.push (table is automatically refreshed in R memory) • Hypothesen-Test: wilcox.test, ks.test, var.test, binom.test, chisq.test, t.test, bartlett.test • Bessel Funktionen: Bessel(I,J,K,Y) • Gamma Funktionen: gamma, lgamma, digamma, trigamma (part of mathematical functions group) • Verteilungen: Density, cumulative distribution, and quantile functions for standard distributions • Matrix: %*% (matrix multiplication), crossprod (matrix cross-product), tcrossprod (matrix cross-product A times transpose of B) 24 Live Live Arbeiten mit Tabellen in der Datenbank 25 Beispiele für den transparenten Zugriff library(ORE) Connect to a specific schema and database ore.connect("RQUSER","SID","HOST", "PASSWORD",1521) One connection active at a time ore.create( ONTIME_S, table = "NEW_ONTIME_S") Create a database table from a data.frame, ore.frame. Create a view from an ore.frame. ore.create( ONTIME_S, view = "NEW_ONTIME_S_VIEW") ore.drop(table="NEW_ONTIME_S") Drop table or view in database ore.drop(view="NEW_ONTIME_S_VIEW") t <- ore.get("ONTIME_S","RQUSER") Store R object in database as temporary object, returns handle to object. Data frame, matrix, and vector to table, list/model/others to serialized object ore.attach() v <- ore.push(c(1,2,3,4,5)) ore.sync() ore.sync("RQUSER") Synchronize ORE proxy objects in R with tables/views available in database, on a per schema basis ore.sync(table=c("ONTIME_S", "NARROW")) ore.sync("RQUSER", table=c("ONTIME_S", "NARROW")) ore.exists("ONTIME_S", "RQUSER") ore.exec("create table F2 as select * from ONTIME_S") 26 Returns TRUE if named table or view exists in schema Live Live Zugriff auf Datenbank-Tabellen ore.connect(user = "RU",sid = "ORCL",host = "localhost",password = "RU",port = 1521) ore.sync() ore.ls() class(GESAMTSICHT) 27 Orientierung über die Struktur der Tabellen dim(GESAMTSICHT) names(GESAMTSICHT) 28 Orientierung über die Struktur der Tabellen str(GESAMTSICHT) 29 Ausschnitt der Daten anzeigen lassen head(GESAMTSICHT) 30 Durchschnittliche Bewertung Berechnen tapply(NUTZ_NR,PRODUKT_NR,mean) 31 Einfache visuelle Darstellung Welche Produkte zeigen auffälliges Verhalten? plot(round(tapply(NUTZ_NR,PRODUKT_NR,mean),2),type="h",xlab = "Produktnummern", ylab ="Bewertungsindex") Produkte mit schlechten Bewertungen 32 Histogram hist(round(tapply(NUTZ_NR,PRODUKT_NR,mean),2),ylab = "Produktnummern", xlab ="Bewertungsindex") Die meisten Produkte haben gute Bewertungen im Bereich von 4 - 6 33 Neue Datenstrukturen erstellen Durchschnittliche Bewertungen pro Produkt bew_prod <- aggregate(GESAMTSICHT$NUTZ_NR, by=GESAMTSICHT$PRODUKT_NR, FUN = mean) Spaltennamen ungeschickt PRODUKT_NR <- c(bew_prod$Group.1) BEWERTUNG <- c(bew_prod$x) df_bew_prod <- data.frame(PRODUKT_NR,BEWERTUNG) Neue Spaltennamen erstellt 34 Neue Datenbanktabelle anlegen ore.create(df_bew_prod,table = "PRODUKT_BEWERTUNG") CREATE table UMSATZ_2012_PRO_ARTIKEL as SELECT a.artikel_id PRODUKT_NR,sum(U.umsatz) UMSATZ_2012 FROM f_Umsatz_range U, D_zeit z, D_artikel a WHERE U.zeit_id = z.zeit_id AND U.artikel_id = a.artikel_id and z.jahr_nummer = 2012 GROUP by a.artikel_id ; Über die R-Engine erstellt In der Datenbank erstellt produkt_umsatz_bewertung <merge(PRODUKT_BEWERTUNG,UMSATZ_2012_PRO_ARTIKEL, by="PRODUKT_NR",all=FALSE) Das Objekt produkt_umsatz_bewertung ist neu erstellt worden und könnte auch in die Datenbank gebracht werden. 35 Neue Strukturen aufbauen Gibt es eine Korrelation zwischen Bewertung und Umsatz? Die neue Tabelle enthält die Spalten Umsatz und Bewertung. Gibt es dazwischen einen Zusammenhang. > names(produkt_umsatz_bewertung) [1] "PRODUKT_NR" "BEWERTUNG" "UMSATZ_2012„ > attach(produkt_umsatz_bewertung) > df_bewertung_umsatz <- data.frame(BEWERTUNG,UMSATZ_2012) 36 Neue Strukturen aufbauen Gibt es eine Korrelation zwischen Bewertung und Umsatz Umsatz pro Produkt in create table Umsatz_2012_pro_Artikel as select distinct Produkt_nr, umsatz_2012 from gesamtsicht; 37 38 intersect(colnames(GESAMTSICHT), colnames(UMSATZ_2012_PRO_ARTIKEL)) 39 Gezielte Steuerung von Scripten innerhalb der Datenbank with(ERSTIS,split(alter,geschl)) Lokale R-Engine auf PC erstis Tabelle wird in den lokalen Speicher kopiert und lokal analysiert 42 Oracle Datenbank Lokale R-Engine auf PC Tabelle bleibt in der DB. Analyse findet im Speicher des DB-Servers statt Das Ergebnis wird zurückgeliefert erstis R-Engineauf aufDB-Server DB-Server R-Engine R-Engine auf DB-Server DB-Server-Maschine mod <- ore.doEval( function(param) { library(ORE) ore.connect(user="RQUSER", password="RQUSER", sid="ORCL", host="192.168.1.16",port=1521) ore.sync() ore.attach() mod <- with(ERSTIS,split(alter,geschl)) }); DB-Server-Maschine Oracle Datenbank OREeda Package Functions spezielle Funktionen (SAS analog) ore.corr Oracle Datenbank ore.freq ore.lm ore.rank ore.sort ore.summary Lokale R-Engine auf PC DB Memory erstis R Memory R-Engineauf aufDB-Server DB-Server R-Engine R-Engine auf DB-Server ore.univariate Die Abarbeitung im Memory der Datenbank ist schneller als im Memory der R Engine auf dem Server 43 DB-Server-Maschine ore.crosstab Gezieltes Ansteuern einer Verarbeitungsvariante (Beispiel Regressions Modell) mod <- ore.doEval( function(param) { library(ORE) ore.connect(user="RQUSER", password="RQUSER„, sid="ORCL", host="192.168.1.16",port=1521) ore.sync() ore.attach() mod <- ore.lm(lz.1 ~ zuf.inh.1,ERSTIS) mod }); mod_local <- ore.pull(mod) class(mod_local) summary(mod_local) 44 mod <- ore.doEval( function(param) { dat <- ore.pull(ONTIME_S) mod <- lm(ARRDELAY ~ DISTANCE + DEPDELAY, dat) mod }); mod_local <- ore.pull(mod) class(mod_local) summary(mod_local) Daten bleiben im Memory Der Oracle Datenbank Daten im Memory der R-Engine auf dem DB-Server Laufzeit: 3 Sekunden Laufzeit: 110 Sekunden Funktionen und Prozeduren in der Datenbank 45 Aufrufen von R-Scripten über SQL-Statements komplett in der Datenbank (sys.rqScriptCreate) Select * from Table() select * from table(rqEval(NULL, 'select 1 id, 1 res from dual', 'Example1')); begin sys.rqScriptCreate('Example1‘, 'function() { ID <- 1:10 res <- data.frame(ID = ID, RES = ID / 100) res}'); end; / 46 DB Memory R Memory R-Engineauf aufDB-Server DB-Server R-Engine R-Engine auf DB-Server DB-Server-Maschine Oracle Datenbank Visualisieren von Ergebnissen (Plotting) 47 48 49 50 51 Visualisieren von Ergebnissen (OBIEE) 52 Anzeige über Business Intelligence Als Funktion oder gespeicherte Ergebnisse Das Kundenranking wird mit in den Berichten angezeigt 53 Einbindung von R-Grafiken in OBIEE Mit Parametern 54 Einbinden von R in umfangreichere Analyse-Szenarien 55 Cluster-Analyse Baum - Darstellung 56 Cluster-Analyse library(cluster) 57 Integrierte R Umgebung Oracle R Connector for Hadoop Client Host R Engine Oracle Big Data Oracle Exadata Appliance R Engine ORE Native R MapReduce ORHC ORHC Hadoop Cluster Software MapReduce Nodes Native R HDFS Zugriff Mehr Produktivität HDFS 58 R Engine ORE Big Data Connectors R Environment R-Package R-Package Oracle R Connector for Hadoop Oracle R Enterprise (Advanced Analytics) Oracle Server-Machine HDFS Cluster-Machines Oracle 11.2 hdfs_stream Oracle Direct Connector for HDFS HDFS External Table Preprocessor: hdfs_stream Hive Table Target Table CSV + /n CSV Offline Mode Data pump direct path Oracle Loader for Hadoop LoaderMap MapReduce Job Framework 59 Parallel Execution Online Mode Partitioned + sorted convential path OCI JDBC 60