Analytik in der Datenbank - glt14-programm.linuxtage.at · Analytik in der DB Überblick und...

Analytik in der DB

Analytik in der DatenbankModellanwendung näher an den Daten

Balázs Bárány

Berater für Analytik und Data Warehousing

Linuxtage Graz 2014

Analytik in der DB

Inhalt

Überblick und GrundlagenVorgehensmodell

Analytische Modellierung

Modellanwendung in der DatenbankEinfache ModellartenKomplexe Modelle

PL/R in PostgreSQL

Analytik in der DB

Überblick und Grundlagen

Analytik bzw. analytics

I Deskriptiv

I Prädiktiv

I Explorativ

I Statistik, Data Mining, KDD, Machine Learning, DataScience, ...?

I Anwendungsfelder

I Klassi�zierung, Regression, Assoziation, Clustering

Analytik in der DB



I Deskriptiv

I Prädiktiv

I Explorativ


I Anwendungsfelder


Analytik in der DB



I Deskriptiv

I Prädiktiv

I Explorativ


I Anwendungsfelder


Analytik in der DB


Methoden und Werkzeuge

I Verfahren und Algorithmen

I InformiertI Uninformiert

I Software

I Open Source: R, RapidMiner, Weka, KNIME, ...I Proprietär: SPSS, SAS, ...

Analytik in der DB


Methoden und Werkzeuge

I Verfahren und Algorithmen

I InformiertI Uninformiert

I Software

I Open Source: R, RapidMiner, Weka, KNIME, ...I Proprietär: SPSS, SAS, ...

Analytik in der DB


Freie Werkzeuge

I RapidMiner

I �Commercial source model�I Community-Version von Sourceforge.net und GithubI Aktuelle Version 6: interaktives Tutorial,Usability-Verbesserungen

I R �environment for statistical computing and graphics�

I Befehlszeile, SkriptingI GUIs

Analytik in der DB


Freie Werkzeuge

I RapidMiner

I �Commercial source model�I Community-Version von Sourceforge.net und GithubI Aktuelle Version 6: interaktives Tutorial,Usability-Verbesserungen

I R �environment for statistical computing and graphics�

I Befehlszeile, SkriptingI GUIs

Analytik in der DB


Vorgehensmodell

Data-Mining-Prozess

Cross Industry Standard Process for Data Mining, Kenneth Jensen/Wikimedia Commons

Analytik in der DB


Vorgehensmodell

Themen dieses Vortrags

Cross Industry Standard Process for Data Mining, Kenneth Jensen/Wikimedia Commons

Analytik in der DB


Vorgehensmodell

Projekt in der Praxis

I Daten liegen in einer relationalen Datenbank

I Analyse, Modellerstellung, Evaluierung in der Analysesoftware

I Ziel: Anwendung des Modells auf neue Daten

I Neue Daten lesen, ID-Feld zwischenspeichernI Modell anwendenI Ergebnis zurückschreiben

I Probleme:

I Ine�ziente RoundtripsI Interaktion zwischen mehreren SystemenI Batch-Verarbeitung, Latenz

Analytik in der DB


Vorgehensmodell






I Probleme:


Analytik in der DB


Vorgehensmodell






I Probleme:


Analytik in der DB


Vorgehensmodell






I Probleme:


Analytik in der DB


Vorgehensmodell

Integration in die Datenerfassung

I Etwas besser:

I Integrierte Software

I Ablösung des Legacy-Systems?

I Integration über o�ene Schnittstellen

I Webservices, remote procedure calls, ...

I Predictive Model Markup Language (PMML)

I Integration in den Datenbankserver

Analytik in der DB


Vorgehensmodell


I Etwas besser:







Analytik in der DB


Vorgehensmodell


I Etwas besser:







Analytik in der DB


Vorgehensmodell


I Etwas besser:







Analytik in der DB


Vorgehensmodell


I Etwas besser:







Analytik in der DB


Modellierungsverfahren

I Lineare Regression

I Gleichung in der Form y = ax+b

I Erweiterungen: nicht normalverteilte Daten, nichtlinear,multivariat

I Naive-Bayes-Methode

I Entscheidungsbäume

I Support Vector Machines

I ... und viele mehr

Analytik in der DB










Analytik in der DB










Analytik in der DB










Analytik in der DB










Analytik in der DB










Analytik in der DB


Beispiel: lineare Regression

I Fragestellung: Höchstgeschwindigkeit von �Kurvigkeit� desStraÿenabschnitts abhängig?

I Methode: Länge des Straÿenabschnitts / Länge der Geradenzwischen Start- und Endpunkt

Beispiel

> mod <- lm(geschw ~ kurvigkeit, data=strkurv)

> coef(mod)

(Intercept) kurvigkeit

86.14846 -63.79144

Analytik in der DB


Beispiel: lineare Regression

I Fragestellung: Höchstgeschwindigkeit von �Kurvigkeit� desStraÿenabschnitts abhängig?

I Methode: Länge des Straÿenabschnitts / Länge der Geradenzwischen Start- und Endpunkt

Beispiel

> mod <- lm(geschw ~ kurvigkeit, data=strkurv)

> coef(mod)

(Intercept) kurvigkeit

86.14846 -63.79144

Analytik in der DB


Beispiel: Entscheidungsbaum

I Fragestellung: Darf die Straÿe mit Fahrrädern befahrenwerden?

I Methode: Entscheidungsbaum in RapidMiner

Analytik in der DB


Beispiel: Entscheidungsbaum

I Fragestellung: Darf die Straÿe mit Fahrrädern befahrenwerden?

I Methode: Entscheidungsbaum in RapidMiner

Analytik in der DB


Komplexer Entscheidungsbaum

genauer, aber viel komplexer!

Analytik in der DB

Modellanwendung in der Datenbank

Einfache Modellarten

Modelle in SQL-Datenbanken

I Lineare Regression und Varianten: trivial

I Parameter eintragen, View oder Stored Procedure erstellen undaktualisieren

Beispiel

CREATE VIEW maxspeed_prediction ASSELECT osm_id,GREATEST(86.14846 + -63.79144 * (ST_Length(geo) / ST_Distance(ST_StartPoint(geo), ST_EndPoint(geo)) - 1),20.0

) AS maxspeed_predFROM österreich_strasseWHERE maxspeed is nullAND ST_Distance(ST_StartPoint(geo), ST_EndPoint(geo)) > 0

Analytik in der DB



Variante für einfachere Wartung

I View-Lösung nicht leicht automatisch zu aktualisieren

I Besser: Parameter-Tabelle und Funktion

Beispiel

CREATE TABLE maxspeed_pred_param (intercept double precision, kurvigkeit double precision

);

CREATE FUNCTION kurvigkeit ... ;

SELECT osm_id, GREATEST(20.0,mpp.intercept + mpp.kurvigkeit * kurvigkeit(way)

) AS maxspeed_predFROM osm_austria_roadsCROSS JOIN maxspeed_pred_param mpp ;

Analytik in der DB



Regelbasierte Modelle

I Entscheidungsbäume und Rule-Modelle in SQLI Baum: Pfade verfolgen, Entscheidungen in CASE WHEN ...THEN überführen

I �Tree to Rules�-Operator in RapidMiner

Beispiel

1 if oneway = no then yes (608 / 439)2 if oneway = yes and highway = motorway then no (0 / 32)3 if oneway = yes and highway = motorway_link then no (0 / 5)4 if oneway = yes and highway = primary then no (44 / 256)5 if oneway = yes and highway = primary_link then no (2 / 82)6 if oneway = yes and highway = residential then yes (5 / 0)7 if oneway = yes and highway = secondary then no (11 / 47)8 if oneway = yes and highway = secondary_link then no (2 / 3)9 if oneway = yes and highway = trunk then no (0 / 28)10 if oneway = yes and highway = trunk_link then no (0 / 11)11 if oneway = yes and highway = unclassi�ed then yes (2 / 0)

Analytik in der DB



Regelbasierte Modelle

I Entscheidungsbäume und Rule-Modelle in SQLI Baum: Pfade verfolgen, Entscheidungen in CASE WHEN ...THEN überführen

I �Tree to Rules�-Operator in RapidMiner

Beispiel

1 if oneway = no then yes (608 / 439)2 if oneway = yes and highway = motorway then no (0 / 32)3 if oneway = yes and highway = motorway_link then no (0 / 5)4 if oneway = yes and highway = primary then no (44 / 256)5 if oneway = yes and highway = primary_link then no (2 / 82)6 if oneway = yes and highway = residential then yes (5 / 0)7 if oneway = yes and highway = secondary then no (11 / 47)8 if oneway = yes and highway = secondary_link then no (2 / 3)9 if oneway = yes and highway = trunk then no (0 / 28)10 if oneway = yes and highway = trunk_link then no (0 / 11)11 if oneway = yes and highway = unclassi�ed then yes (2 / 0)

Analytik in der DB



Regeln in SQL überführt

I Syntaxänderungen für SQL

Beispiel

SELECT osm_id,

CASE WHEN oneway = 'no' THEN true

WHEN oneway = 'yes' AND highway = 'motorway' THEN false

WHEN oneway = 'yes' AND highway = 'residential' THEN true

WHEN ...

ELSE null

END as fahrrad_erlaubt

FROM osm_austria_roads;

Wartung aufwändig und fehleranfällig!

Analytik in der DB


Komplexe Modelle

Komplexe Modelle

I komplexe Datenstrukturen und Berechnungen

I schwer in SQL abzubilden, Wartungsaufwand bei Änderungen

I Muÿ es SQL sein?

I PostgreSQL ist multilingualI PL/Perl, PL/Python, PL/JavaScript, PL/Java ...

I PL/R

Analytik in der DB


Komplexe Modelle

Komplexe Modelle



I Muÿ es SQL sein?


I PL/R

Analytik in der DB


Komplexe Modelle

Komplexe Modelle



I Muÿ es SQL sein?


I PL/R

Analytik in der DB

PL/R in PostgreSQL

Überblick: PL/R

I Erstes Release 2003

I Oracle: 2010 (mind. 23.000 $)

I Aktuelle Version 8.3.0.15 für PostgreSQL 9.3

I Lizenz: GPL

Analytik in der DB

PL/R in PostgreSQL

Überblick: PL/R


I Oracle: 2010 (mind. 23.000 $)


I Lizenz: GPL

Analytik in der DB

PL/R in PostgreSQL

Überblick: PL/R


I Oracle: 2010 (mind. 23.000 $)


I Lizenz: GPL

Analytik in der DB

PL/R in PostgreSQL

Überblick: PL/R


I Oracle: 2010 (mind. 23.000 $)


I Lizenz: GPL

Analytik in der DB

PL/R in PostgreSQL

Sicherheitsüberlegungen

I Installation als �untrusted language�

I Funktionen erstellen nur als Datenbankadministrator

I Freigabe der Ausführung für unprivilegierte Benutzer

Beispiel

# Mit Admin-Rechten

CREATE FUNCTION r_add(zahl1 double precision, zahl2 double

precision)

RETURNS double precision AS $func$

return(zahl1 + zahl2)

$func$

LANGUAGE 'plr';

GRANT EXECUTE ON FUNCTION r_add(double precision, double

precision)

TO normaluser;

Analytik in der DB

PL/R in PostgreSQL





Beispiel

# Mit Admin-Rechten


precision)



$func$

LANGUAGE 'plr';


precision)

TO normaluser;

Analytik in der DB

PL/R in PostgreSQL





Beispiel

# Mit Admin-Rechten


precision)



$func$

LANGUAGE 'plr';


precision)

TO normaluser;

Analytik in der DB

PL/R in PostgreSQL





Beispiel

# Mit Admin-Rechten


precision)



$func$

LANGUAGE 'plr';


precision)

TO normaluser;

Analytik in der DB

PL/R in PostgreSQL

Funktionalität

I Aggregations- und Window-Funktionen

I Trigger-Funktionen in R

I Datenbankabfragen, PostgreSQL-Umgebung

I Gleichnamige R-Funktion ohne Funktions-Body einbinden

Beispiel

# Liefert n normalverteilte Zufallszahlen

CREATE FUNCTION rnorm(n integer, mean double precision, sd

double precision)

RETURNS double precision[]

AS �

LANGUAGE 'plr';

Analytik in der DB

PL/R in PostgreSQL

Funktionalität





Beispiel



double precision)


AS �

LANGUAGE 'plr';

Analytik in der DB

PL/R in PostgreSQL

Funktionalität





Beispiel



double precision)


AS �

LANGUAGE 'plr';

Analytik in der DB

PL/R in PostgreSQL

Funktionalität





Beispiel



double precision)


AS �

LANGUAGE 'plr';

Analytik in der DB

PL/R in PostgreSQL

Implementierung einer Vorhersagefunktion

Vorbereitungen

I Benötigte Funktionen im R-Interpreter anlegen

einmalig:

Beispiel

select install_rcmd('meinefunktion <-function(x)

{print(x)}

');

permanent: Tabelle plr_modules

I Funktionskontext vs. globaler Kontext

Analytik in der DB

PL/R in PostgreSQL


Vorbereitungen


einmalig:

Beispiel


{print(x)}

');



Analytik in der DB

PL/R in PostgreSQL


Vorbereitungen


einmalig:

Beispiel


{print(x)}

');



Analytik in der DB

PL/R in PostgreSQL


Vorbereitungen


einmalig:

Beispiel


{print(x)}

');



Analytik in der DB

PL/R in PostgreSQL

Daten und Modelle in PL/R bringen

I Entwicklung des Modells am DesktopI Speichern: save(objekt, file="/pfad/zu/objekt.Rdata")

I (Übertragung auf den Server oder gemeinsames Dateisystem)

I Laden in den Datenbankserver

I load("/pfad/zu/objekt.Rdata", .GlobalEnv)

I Beim Start aus der plr_modules-Tabelle

Beispiel

for (datafile in Sys.glob("/pfad/*.Rdata")) {

load(datafile, .GlobalEnv)

}

Analytik in der DB

PL/R in PostgreSQL







Beispiel



}

Analytik in der DB

PL/R in PostgreSQL







Beispiel



}

Analytik in der DB

PL/R in PostgreSQL







Beispiel



}

Analytik in der DB

PL/R in PostgreSQL

Anwendung des Modells in der Datenbank

Beispiel

CREATE OR REPLACE FUNCTION bicycle_vorhersage(

highway TEXT, maxspeed TEXT, oneway TEXT

) RETURNS TEXT AS $func$

daten <- data.frame(bicycle=NA,

highway=highway, maxspeed=maxspeed, oneway=oneway)

bicycle <- predict(bicycle_mod_modell, newdata=daten)

return(as.character(bicycle))

$func$ LANGUAGE 'plr';

I Anwendung:

Beispiel

select bicycle_vorhersage('residential', '70', 'yes');

no

select bicycle_vorhersage('residential', '30', 'no');

yes

Analytik in der DB

PL/R in PostgreSQL

Anwendung des Modells in der Datenbank

Beispiel

CREATE OR REPLACE FUNCTION bicycle_vorhersage(

highway TEXT, maxspeed TEXT, oneway TEXT

) RETURNS TEXT AS $func$

daten <- data.frame(bicycle=NA,

highway=highway, maxspeed=maxspeed, oneway=oneway)

bicycle <- predict(bicycle_mod_modell, newdata=daten)

return(as.character(bicycle))

$func$ LANGUAGE 'plr';

I Anwendung:

Beispiel

select bicycle_vorhersage('residential', '70', 'yes');

no

select bicycle_vorhersage('residential', '30', 'no');

yes

Analytik in der DB

PL/R in PostgreSQL

Vorhersage in der Tabelle nachtragen

Beispiel

UPDATE osm_austria_roads

SET bicycle = bicycle_vorhersage(coalesce(highway, 'NA'),

coalesce(maxspeed, 'NA'), coalesce(oneway, 'NA'))

WHERE highway = 'residential'

AND oneway = 'yes'

AND bicycle IS NULL;

Analytik in der DB

PL/R in PostgreSQL

Probleme

I character <> factor

I NULL <> NA

I Datenbankzugri� aus PL/R

I allgemein: viel Ausprobieren und Übung

Analytik in der DB

PL/R in PostgreSQL

Probleme


I NULL <> NA



Analytik in der DB

PL/R in PostgreSQL

Probleme


I NULL <> NA



Analytik in der DB

PL/R in PostgreSQL

Probleme


I NULL <> NA



Analytik in der DB

Ausblick

Ausblick

I RapidMiner mit PL/Java

I Python-basierte Modelle mit PL/Python

I Direkte Erweiterungen in der Datenbank

I PMML-Unterstützung

Analytik in der DB

Ausblick

Ausblick





Analytik in der DB

Ausblick

Ausblick





Analytik in der DB

Ausblick

Fragen?

I Balázs Bárány, <[email protected]>

Date post:	18-Aug-2019
Category:	Documents
Upload:	phamhanh
View:	217 times
Download:	0 times

Analytik in der Datenbank - glt14-programm.linuxtage.at · Analytik in der DB Überblick und...

Documents