SQLServer 2016 CTP 3 und die R Integration
| Letzte Woche war es (eeeeendliiiiich) soweit, die CTP 3 des SQLServer 2016 wurde von Microsoft zum Download zur Verfügung gestellt (hier).
Damit ist jetzt erstmals die R Integration im Server vorhanden (natürlich neben vielen anderen tollen Features) und wir können diese - in diesem Blog-Eintrag - einem kleinen Test unterzeihen. Also nichts wie ran an die Software, installieren und ein wenig mit R im SQLServer 2016 herumskripten. Das klingt doch nach einer Menge Spass. Klingt nicht nur so, den haben wir bestimmt! |
 |
Für ungeduldige habe ich das auch in einem Video auf Channel9 durchgespielt, dieses kann hier betrachtet werden. Von Vorteil ist es möglicherweise auch, sich meinen vorhergehenden Blog-Eintrag über R durchzulesen, diesen findet man hier (das dauert nur ein paar Minuten und, falls noch nichts mit R gemacht wurde, bekommt man damit einen kleinen Überblick).
Voraussetzungen
Zuerst einmal brauchen wir nicht viel, nämlich eine:
· Installation des SQLServer 2016 CTP3 mit dem Feature „Advanced Analytics Extension“.
Die Feature Auswahl von meiner SQLServer 2016 CTP3 Installation sind hier zu sehen (die Management Extensions, also SSMS habe ich ebenfalls mit installiert, dies ist auf dem Bild allerdings nicht mehr zu sehen).
Es steht übrigens bereits in der Azure Galerie eine vorbereitete VM mit dem SQLServer 2016 CTP3 bereit, dann müsst ihr keine eurer eigenen Maschinen verwenden…..
Die weiteren Schritte – nach der Installation der CTP3 - sind:
- Installation von Revolution R Open 3.2.2 for Revolution R Enterprise 7.5.0.
- Installation des Revolution R Enterprise 7.5. Revolution R Enterprise Packages für den SQLServer.
Step-by-Step Anweisungen - für diese beiden Schritte - findet ihr hier.
Abschließend müssen nach der Installation noch zwei weitere Kleinigkeiten erledigt werden.
Post Installation Task
Der SQLServer muss umkonfiguriert werden, insofern als dass dieser externe Scripts zulässt, dies erledigt man im SSMS mit folgenden Kommandos:
Exec sp_configure 'external scripts enabled', 1;
reconfigure;
Exec sp_configure
Abschließend ist ein Konfigurationsskript auszuführen, welches mit der Installation des Revolution R Packages mitgeliefert wurde. Dieses muss aus einer CMD-Shell mit Administratoren-Berechtigungen aufgerufen werden:
“%programfiles%\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\RegisterRExt” /install /instance:<INSTANZNAME>
Der Durchlauf dieses Skripts sieht dann so aus:
Damit haben wir alle notwendigen Schritte erledigt und wir können R im SQLServer 2016 testen.
Eigene R Skripte im SQLServer
Jetzt sollte allerdings alles bereit sein und ich möchte nun versuchen die neue Stored Procedure sp_execute_external_script mit einem einfachen R Skript aus dem SQLServer Management Studio (SSMS) auszuführen. Der erste Parameter @language gibt an, in welcher Sprache das Skript ist, welches im zweiten Parameter (@script) als Unicode String mitgegeben wird. Der dritte Parameter dient dazu Daten in das R Skript zu übergeben, was wir auch etwas später testen wollen.
In unserem ersten R Skript soll eine Variable mysum angelegt werden und dieser das Resultat aus der Berechnung 40 +2 zugewiesen werden. Das Resultat hätten wir gerne im SSMS angezeigt. R im SQLServer stellt zwei Variablen zur Verfügung, nämlich InputDataSet und OutputDataSet. Mit ersterer erhält man Zugriff auf die Daten von @input_data_1 und letztere dient zur Rückgabe aus dem Skript und hält unser Resultat. Wichtig ist, dass das Resultat ein data.frame ist, was quasi einer Tabelle entspricht (mehrere Spalten, jede Spalte möglicherweise mit einem anderen Datentyp).
execute sp_execute_external_script
@language = N'R'
, @script = N' mysum <- 40 + 2;
OutputDataSet <- data.frame(mysum);'
, @input_data_1 = N''
WITH RESULT SETS (([col] int NOT NULL));
Das Resultat ist wie schon in der RGUI im letzten Artikel 42:
Mal sehen, ob R im SQLServer auch eine Gaußsche Berechnung der Zahlen von 1 – 15 zu Stande bringt. Dafür verwende ich die sum Funktion aus R.
execute sp_execute_external_script
@language = N'R'
, @script = N' mysum <- sum(1:15);
OutputDataSet <- data.frame(mysum);'
, @input_data_1 = N''
WITH RESULT SETS (([col] int NOT NULL));
Und das Resultat ist wie erwartet korrekt:
Nun hätte ich gerne wieder einen Vektor, oder ein Array mit den Zahlen 1-15 und würde gerne (ganz R like) zu jeder Zahl 10 dazu addieren.
execute sp_execute_external_script
@language = N'R'
, @script = N' x <- array(1:15); y <- 10;
OutputDataSet <- data.frame(x + y);'
, @input_data_1 = N''
WITH RESULT SETS (([col] int NOT NULL));
Auch das klappt ohne Probleme, wie man am Resultat erkennen kann:
Jetzt würde ich aber gerne die Zahl 10 für die Variable y der Stored Procedure übergeben, das sieht dann so aus:
execute sp_execute_external_script
@language = N'R'
, @script = N' x <- 1:15;
#InputDataSet is a data.frame
str(InputDataSet)
y <- as.vector(t(InputDataSet));
z <- x + y;
OutputDataSet <- data.frame(z);'
, @input_data_1 = N' SELECT 10 as y'
WITH RESULT SETS (([col] int NOT NULL));
Ich habe hier ein Select-Statement übergeben, welches die Zahl 10 zurückliefert, wichtig ist hier, dass nun im InputDataSet ein data.frame mit dieser Zahl vorhanden ist, diese wird in einen Vektor umgewandelt, um damit weiter rechnen zu können. Das Resultat ist wie zuvor, allerdings habe ich mir noch Informationen über das InputDataSet mit der R Methode str anzeigen lassen:
Also simple, bzw. sehr einfache Berechnungen bekommen wir nun schon mit Hilfe von R und dem SQLServer hin, kommen wir nun also zu etwas schwierigerem, nämlich dem Roulettebeispiel. Es gibt 5 Tage an denen Roulette gespielt wurde, mal mit Gewinn (Mittwoch, Freitag), mal mit Verlust (Montag, Dienstag, Donnerstag). Ich würde nun gerne von R folgendes berechnen lassen:
- Anzahl der Tage mit Gewinn (wo wir im Plus waren)
- Die Summe der Tage, wo Gewinn gemacht wurde
- Die Summe über die ganze Woche
- Die Umsetzung mit einem R-Skript sieht nun so aus:
execute sp_execute_external_script
@language = N'R'
, @script = N'
rouletteWoche <- c(-120, -50, 200, -150, 210);
#wochenTage <- c("Montag", "Dienstag", "Mittwoch", "Donnerstag", "Freitag");
#names(rouletteWoche) <- wochenTage;
anzahlTage <- sum(rouletteWoche > 0);
rouletteTotalPlus <- sum(rouletteWoche[rouletteWoche > 0]);
rouletteTotal <- sum(rouletteWoche);
OutputDataSet <- data.frame(anzahlTage, rouletteTotalPlus, rouletteTotal);'
, @input_data_1 = N''
WITH RESULT SETS (([anzahlTage] int, [rouletteTotalPlus] int, [rouletteTotal] int));
Als nächstes wäre es aber wünschenswert, wenn die Werte für den Vektor rouletteWoche aus der Datenbank kommen, gut, d. h. wir benötigen eine neue Datenbank (ich habe diese IntegrateR genannt , welche im SSMS mit der rechten Maustaste auf Databases angelegt werden kann), eine Tabelle und Daten. Die Tabelle soll recht einfach gehalten werden, besteht also nur aus einer Spalte, wo die Beträge eingetragen werden.
USE [IntegrateR]
CREATE TABLE [dbo].[Roulette](
[Won] [int] NOT NULL,
) ON [PRIMARY]
go
Die Daten sind einfach die 5 Werte von zuvor, das Beispiel kann auch aufgebohrt werden, um den Tag und die Woche als eigene Spalten zu halten, dies würde aber nur zu einem komplexeren SELECT-Statement führen, was ich aus Gründen der leichten Nachvollziehbarkeit nicht getan habe.
insert into Roulette
values (-120), (-50), (200), (-150), (210)
go
Jetzt ist also alles vorhanden, das R-Skript kann erweitert werden und ein Select-Statement übergeben werden, welches die 5 Zeilen aus der Tabelle Roulette retouniert:
use IntegrateR
go
execute sp_execute_external_script
@language = N'R'
, @script = N'
rouletteWoche <- as.matrix(InputDataSet);
anzahlTage <- sum(rouletteWoche > 0);
rouletteTotalPlus <- sum(rouletteWoche[rouletteWoche > 0]);
rouletteTotal <- sum(rouletteWoche);
OutputDataSet <- data.frame(anzahlTage, rouletteTotalPlus, rouletteTotal);'
, @input_data_1 = N' Select Won from Roulette'
WITH RESULT SETS (([anzahlTage] int, [rouletteTotalPlus] int, [rouletteTotal] int));
Die Eingangsdaten wurden in eine Matrix umgewandelt, danach kann mit den Daten wie zuvor gerechnet werden, auch das Resultat ist wie zuletzt:
Abschließend würde ich gerne die beiden Tage sehen, wo Gewinn gemacht wurde und zwar den Wochentag und den Gewinn:
execute sp_execute_external_script
@language = N'R'
, @script = N'
rouletteWoche <- as.matrix(InputDataSet);
wochenTage <- c("Montag", "Dienstag", "Mittwoch", "Donnerstag", "Freitag");
names(rouletteWoche) <- wochenTage;
rouletteTagePlus <- rouletteWoche[rouletteWoche > 0];
colNames <- cbind(rouletteTagePlus);
#colNames <- data.frame(rouletteTagePlus, check.names = FALSE);
OutputDataSet <- data.frame(rownames(colNames), rouletteTagePlus);'
, @input_data_1 = N' Select Won from Roulette'
WITH RESULT SETS (([day] nvarchar(20), [rouletteTagePlus] int));
Das trickreiche hier ist das Binden an die Variable rouletteTagePlus, um die Spaltennamen ausgeben zu können. Das Ergebnis ist wie gewünscht, es ist der Name des Wochentags zu sehen und die zugehörigen Werte:
Zusammenfassung
R ist eine absolut anerkannte Skriptsprache in Wirtschaft und Wissenschaft, wenn es um die Analyse von Daten geht. Microsoft unterstützt R nun auch direkt im SQLServer 2016, wodurch sich R-Skripte direkt im Server ausführen lassen.
Ich hoffe ich konnte weiter euer Interesse für R wecken und ich wünsche euch viel Spaß mit R und dem SQLServer 2016! Weitere SQLServer 2016 Videos findet ihr übrigens auch auf Channel9:
SQLServer 2016 CTP3 in 15 Minuten - Temporal Tables
SQLServer 2016 CTP3 in 15 Minuten - Dynamic Data Masking
Und wie eingangs erwähnt auch zu diesem Blog-Eintrag:
SQLServer 2016 CTP3 in 15 Minuten – R Integration
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