Die Titanic, R und SQL Server 2016
Die Titanic, R und SQLServer 2016
Seit der Release von SQL Server 2016 CTP3, wo erstmals die R Integration innerhalb des SQL Server zur Verfügung steht, lässt mich dieses Thema nicht mehr so richtig los. Als ich wieder einmal gerade an einem R Projekt gearbeitet habe, ist mir der Vortrag „Machine Learning in Azure – hätte ich auf der Titanic überlebt“ von Olivia Klose eingefallen, diesen könnt ihr euch hier ansehen.
Es wäre doch eine tolle Geschichte, sich die Daten herzunehmen, und mit Hilfe einiger Microsoft Tools sich dieses Desaster einmal vorzunehmen und mit R aufzubereiten.
Voraussetzungen
Um das folgende Code-Beispiel selbst ausführen zu können, werden diese Tools benötigt:
· SQL Server 2016 CTP3 mit der R Integration
· Visual Studio 2015
· SSDT October 2015 Preview in Visual Studio
Ich würde empfehlen, meinen anderen Blog-Eintrag hier zu lesen, um die prinzipielle R-Integration und deren Installation innerhalb des SQL Server 2016 erst einmal kurz zu testen, bzw. auszuprobieren.
Falls ihr gerne einen Blick auf die Skripte werfen wollt, die ich für das folgende Beispiel verwendet habe, dann empfehle ich euch den Code-Download aus meinem Github-Verzeichnis:
https://github.com/BerndtHamboeck/MVA-SQLServerTitanic
Die Daten
Die Daten findet man bei Kaggle, einer Online Community, wo man sich als Data Scientist versuchen kann. Es werden immer wieder Competitions veranstaltet, wo es Gadgets, oder aber auch von Firmen gesponserte Preise abzuräumen gilt.
Zu den aktuellen, aber auch vorangegangenen Competitions kommt ihr über den Menüpunkt.
Einer dieser Competitions waren die Daten der Titanic Passagiere, diese findet ihr, wenn ihr etwas nach unten scrollt.
Oer aber auch ganz bequem über diesen Link hier.
Jetzt könnt ihr euch die Daten abholen, diese findet ihr unter dem Menüpunkt ‚Get the Data‘. Ihr könnt auch noch eine neue Lösung hochladen, diese kann dann von der Online-Community bewertet werden.
Aber zuerst benötigen wir die Daten, diese liegen als CSV-Dateien vor. Es werden die Dateien train.csv und test.csv benötigt, also nichts wie ran an den Speck und holen wir uns diese beiden Dateien gleich einmal.
Die Daten sind im CSV-Format, d. h. es sollte einmal in Excel überprüft werden, um zu sehen, was denn an Daten zur Verfügung steht.
Soweit ist das ganz gut, die Daten sollen aber in eine Datenbank. Widmen wir uns also nun dieser Aufgabe.
Importieren der Daten
Die Daten sollen im SQL Server 2016 landen, also habe ich mir zuerst einmal eine neue Datenbank und die zugehörigen Tabellen (TitanicTest und TitanicTrain) angelegt. Die Tabellen sind eigentlich gleich und sehen so aus:
Tipp: Das zugehörige Script zum Anlegen der Datenbank und der Tabellen (setup.sql) findet ihr in meinem Github-Repository für diesen Artikel.
Zum Importieren der Daten habe ich ein Integration Services Package erstellt. Das geht erstens sehr einfach und zweitens können etwaige Transformationen einfach hinzugefügt werden. Die BI Projekte für 
Somit sind alle Voraussetzungen geschaffen, um ein SSIS-Paket für den Datenimport der CSV-Dateien zu erstellen.
Das Herzstück sind die Data Flow Tasks, diese verwenden eine Flat File Source zu der jeweiligen CSV-Datei und spielen die Daten in die jeweilige Tabelle ein.
Damit sind die Daten, dank SSIS, nun auch schon im SQL Server 2016, das sollte noch schnell überprüft werden.
Das sieht hervorragend aus, damit können wir uns dem nächsten Thema widmen, der R Analyse der Daten.
SQLServer und R
Das Herzstück der R Integration im SQL Server ist die Stored Procedure: sp_execute_external_script. Hier wird die Sprache des Scripts (in diesem Fall R) festgelegt, das Script selbst und ein Query für die Daten übergeben. Das Resultat wird in meinem Fall ein Bild sein. Dieses Bild soll darstellen, ob es auf der Titanic von Vorteil war weiblich oder männlich gewesen zu sein.
| create procedure Survival_by_Genderasbeginexec sp_execute_external_script@language = N'R',@script = N'require(ggplot2);train <- data.frame(InputDataSet);train$Survived <- factor(train$Survived, levels=c(TRUE,FALSE));levels(train$Survived) <- c("Survived", "Died");train$Pclass <- as.factor(train$Pclass);levels(train$Pclass) <- c("1st Class", "2nd Class", "3rd Class");train$Gender <- factor(train$Sex, levels=c("female", "male"));levels(train$Gender) <- c("Female", "Male");png("s2_survival_by_gender.png", width=800, height=600)mosaicplot(train$Gender ~ train$Survived, main="Passenger Survival by Gender",color=c("#12F512", "#EBDEDE"), shade=FALSE, xlab="", ylab="",off=c(0), cex.axis=1.4);dev.off();imgfile <- file("s2_Survival_by_gender.png", "rb");image1 <- readBin(imgfile, what=raw(), n=1e6);close(imgfile);OutputDataSet <- data.frame(data=image1);',@input_data_1 = N'SELECT [PassengerId],[Survived],[Pclass],[Name],[Sex],Convert(int,[Age])as Age,[SibSp],[Parch],[Ticket],[Fare],[Cabin],[Embarked] FROM [dbo].[TitanicTrain];'with result sets(([chart1] varbinary(max)));end; |
Dieses R-Script verwendet ein externes R-Package, nämlich ggplot2, um das Ergebnis grafisch darstellen zu können. Dieses Paket muss zuvor auf dem PC, wo der SQL Server läuft, zur Verfügung gestellt werden. Am einfachsten geht das mit der RGUI und dem Kommando:
install.packages("ggplot2")
Dadurch werden auch alle Pakete mit installiert, von denen ggplot2 abhängig ist (und das sind doch einige).
Nach dem Anlegen der Stored Procedure empfiehlt sich ein schneller Test innerhalb des SQLServer Management Studios, um die Funktionsweise zu überprüfen.
exec Survival_by_Gender;
Das Ergebnis ist (hoffentlich) ein Bild als Byte Array, interessanter in diesem Fall ist der Messages-Tab.
Hier wird entweder eine Fehlermeldung angezeigt, wenn es Probleme mit der R-Integration, oder einem der verwendeten R-Packages (in diesem Fall nur ggplot2) gibt, bzw. ob das Laden – wie in meinem Fall – geklappt hat.
Kommt hier ein Fehler, dass das „required package: ggplot2“ nicht exisitiert, dann wurde es entweder nicht installiert, oder es gibt ein Problem mit dem Pfad der Packages.
Sollte es nun Probleme mit dem Laden eines R-Packages im SQL Server geben, so empfiehlt sich folgendes Vorgehen:
· Überprüfen des R-Library Paths in der RGUI:
libPaths()
· Überprüfen des R-Library Paths im SQL Server:
| EXECUTE sp_execute_external_script@language=N'R',@script = N'str(.libPaths())',@input_data_1 = N'' |
Sollten die beiden voneinander abweichen, kann bei der Installation von R-Packages der Pfad als Parameter mitgegeben werden.
So liefert beispielsweise auf meiner Maschine RGUI folgendes zurück:
.libPaths()
[1] "C:/Users/bhamboeck/Documents/R/win-library/3.2"
[2] "C:/Program Files/RRO/RRO-3.2.2-for-RRE-7.5.0/R-3.2.2/library"
Der SQL Server hingegen:
Somit sieht bei mir der Aufruf für die Installation des R-Packages für ggplot so aus:
install.packages("ggplot2", .libPaths()[2])
· Überprüfen der Installation in der RGUI
| packagematrix <- installed.packages();NameOnly <- packagematrix[,1];NameOnly |
· Überprüfen der Installation im SQL Server
| EXECUTE sp_execute_external_script@language=N'R',@script = N'packagematrix <- installed.packages();NameOnly <- packagematrix[,1];OutputDataSet <- as.data.frame(NameOnly);',@input_data_1 = N''WITH RESULT SETS ((PackageName nvarchar(250) )) |
Beide sollten das geforderte R-Package installiert haben.
Visual Studio 2015 unterstützt seit dem Update 1 übrigens Syntax-Highlighting für R, das Script, welches ich zum Testen in der RGUI verwende sieht dann so aus:
Auch Visual Studio Code behrrscht das Syntax-Highlighting für R:
Ich habe weitere Stored Procedures geschrieben, die andere Charts, bzw. Auswertungen zur Verfügung stellen, insgesamt sind folgende Prozeduren vorhanden:
1. Survival by Age1
2. Survival by Age2
3. Survival by Class
4. Survival by Embarked
5. Survival by Fare
6. Survival by Gender
7. Survival by Parch
8. Survival by SibSp
9. Survival by Ticket
Das ist eine tolle Sache - wir sehen also die Bytes des Images, welches vom ggplot-Package in R erstellt wurde.
Falls ihr das gerne in der RGUI ausprobieren wollt, findet ihr die Submission des kompletten Scripts auf Kaggle, genauer gesagt - hier.
Ein Desktop Client dazu
Es stehen also Stored Procedures im SQL Server zur Verfügung, die jeweils eine Spalte als varbinary(max) retournieren. Um die Resultate in einem WPF Client darstellen zu können, benötigt man also nichts weiter als eine SQLConnection, ein SQLCommand und etwas Code, um das zurückgelieferte byte[] in eine ImageSource umzuwandeln. Das sieht dann wie folgt aus (den kompletten Code gibt es ebenfalls im Github-Repository).
| List<string> sps = new List<string>();List<Image> images = new List<Image>();sps.Add("Survival_by_Age1"); //"get_titanic_plot2"images.Add(imageSurvival_by_Age1);sps.Add("Survival_by_Age2");images.Add(imageSurvival_by_Age2);sps.Add("Survival_by_Class");images.Add(imageSurvival_by_Class);sps.Add("Survival_by_Embarked");images.Add(imageSurvival_by_Embarked);sps.Add("Survival_by_Fare");images.Add(imageSurvival_by_Fare);sps.Add("Survival_by_Gender");images.Add(imageSurvival_by_Gender);sps.Add("Survival_by_Parch");images.Add(imageSurvival_by_Parch);sps.Add("Survival_by_SibSp");images.Add(imageSurvival_by_SibSp);sps.Add("Survival_by_Ticket");images.Add(imageSurvival_by_Ticket);var connString = "Data Source= .\\Sql2k16;Initial Catalog=sqlr;Integrated Security=True";using (SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString)){conn.Open();SqlCommand cmd = conn.CreateCommand();cmd.CommandType = System.Data.CommandType.StoredProcedure;for (int i = 0; i < sps.Count; i++){try{cmd.CommandText = sps[i];using (SqlDataReader dr = cmd.ExecuteReader()){if (dr.Read()){var img = dr.GetValue(0) as byte[];#region read the image from a bytes arraySystem.IO.MemoryStream ms = new System.IO.MemoryStream(img);ms.Seek(0, System.IO.SeekOrigin.Begin);BitmapImage newBitmapImage = new BitmapImage();newBitmapImage.BeginInit();newBitmapImage.StreamSource = ms;newBitmapImage.EndInit();images[i].Source = newBitmapImage;#endregion}}}catch (Exception ex){throw;}}} |
Dann also nichts wie ran an die Auswertung, es lässt sich eindeutig erkennen, dass der liebe Leonardo DiCaprio kaum eine Chance hatte, den Untergang zu überleben. Seiner neuen Freundin hingegen erging es ja – wie wir wissen – um einiges besser, kein Wunder aufgrund der „etwas“ besseren Chancenverteilung.
Auch eine zweite Auswertung bestätigt, dass Leonardo wirklich kaum Chancen hatte, da er ja nur auf einem billigen Platz reiste und keine der teureren Klassen-Tickets hatte. Also wirklich, wirklich absolut schlechte Voraussetzungen für ihn. Ob es besser für ihn gelaufen wäre, wenn er mehr Zeit in der Oberklassenkabine verbracht hätte?
Weitere Auswertungen sind in den anderen Tab-Reitern der WPF-Applikation zu finden.
Zusammenfassung
Mit der R-Integration im SQL Server 2016 können nun Auswertungen bzw. Analysen innerhalb des SQL Servers ablaufen. Dies kann hilfreich sein, wenn die Resultate beispielsweise über Nacht erstellt werden, da zeigt sich die Stärke der Microsoft Lösung erst so richtig. Durch das Zusammenspiel verschiedener Produkte – z. B. der Integration-Services zum Datenimport, oder aber auch zum Steuern der Analysen und des Berichtswesens durch weitere Pakete – bleibt man bei dem gewohnten Toolset und kann neue Lösungen damit erarbeiten. Ich habe derzeit eine Menge Spaß mit R und dem SQL Server 2016, ich hoffe euch geht es genauso.
Berndt Hamböck ist seit 2007 MCT, darüber hinaus befasst er sich mit Lösungen für komplexe Anwendungsszenarien mit den neuesten Microsoft Technologien. Die Erfahrungen aus der Projektarbeit gibt er in Vorträgen und Trainings weiter und begleitet Entwicklerteams in Softwareunternehmen bei Projekten im Microsoft Umfeld.
Das ist ein Gastbeitrag. Die Meinung des Autors muss sich nicht mit jener von Microsoft decken. Durch den Artikel ergeben sich keinerlei Handlungsempfehlungen. Microsoft übernimmt keine Gewähr für die Richtigkeit oder Vollständigkeit der Angaben