R im MS SQL Server 2016: Installation und Grundlagen

28. September 2016

R ist eine freie Programmiersprache, welche sich heute vor allem an Hochschulen in vielen mathematischen Disziplinen – besonders in der Statistik und Stochastik – als feste Größe etabliert hat. Aber nicht nur in der Mathematik, auch im Bereich Business Intelligence (kurz: BI) gewinnt dieses Analyse-Tool immer mehr an Bedeutung.

Auch Microsoft ist diese Entwicklung nicht verborgen geblieben und hat reagiert. Der Softwarehersteller aus Redmond ist quasi auf den R-Zug aufgesprungen und hat dafür gesorgt, dass die skriptbasierte Statistiksprache innerhalb der neuesten Version seines intern entwickelten Datenbankmanagementsystems, dem MS SQL-Server 2016, genutzt werden kann.

In meinem letzten Flurfunkartikel mit Titel Advanced T-SQL: IF EXISTS mit MS SQL Server 2016 wurde ein neues SQL-Server-Feature in der Abfragesprache T-SQL vorgestellt. Dieser aktuelle Beitrag soll insbesondere die R-Services, welche ebenfalls als Neuheit in den SQL-Server Einzug gehalten haben und die Grundlage für die Interaktion mit der R bilden, näher analysieren. Dabei geht es zum einen um die Installation der notwendigsten Komponenten sowie deren Konfiguration und zum anderen um das Ausführen eines einfachen R-Statements aus dem SQL-Server heraus.

Allgemeine Installation von R

Bei R handelt es sich um ein GNU-Projekt, dessen Sourcen im Internet für unterschiedliche Betriebssysteme und Plattformen heruntergeladen werden können. Nach der erfolgreichen Installation können R-Befehle entweder direkt über eine Konsole oder eine der vielen im Internet verfügbaren IDEs abgesetzt werden. Die bekannteste Entwicklungsumgebung dürfte vermutlich R-Studio sein, dessen Installationspaket gleich alle notwendigen Komponenten in einem Paket mitliefert.

Der folgende Screenshot zeigt ein erfolgreich installiertes und gestartetes R-Studio, welches sofort verwendet werden kann und für diesen Artikel weiter genutzt werden soll:

R-Studio

Zuweisungen und Grundoperationen in R

Sicherlich wäre es zu umfangfangreich R in diesem Artikel bis in das kleinste Detail zu beleuchten. Aus diesem Grund soll sich der folgende Abschnitt nur auf ein paar rudimentäre Grundlagen beschränken, die notwendig sind um R-Befehle innerhalb des SQL-Servers 2016 ausführen zu können.

Wie bereits in der Einleitung erwähnt, handelt es sich bei R um eine Skript- bzw. Interpretersprache, deren Anweisungen zur Laufzeit ausgewertet und ausgeführt werden. Eine der wichtigsten Grundbefehle stellen Zuweisungen dar, welche über syntaktisch entweder über den ‘<-‘- oder den “=”-Operator erfolgen können.

Die folgenden Code-Zeilen stellen exemplarisch dar, wie eine das Zuweisen eines konstanten Wertes zu einer Variablen erfolgt:

   1: wert <- 73;

 

Es können aber nicht nur einfache Werte in Variablen gespeichert werden, auch Funktionen können, ähnlich wie bei JavaScript oder C#-Delegates, in Variablen abgelegt werden. Das folgende Snippet demonstriert dies an Hand einer einfachen Additions-Funktion, welche die Summe zweier Parameter zurück liefert:

   1: summe <- function(v1, v2) { v1 + v2 };

 

Der Aufruf des zugewiesenen Wertes bzw. der Funktion sieht dann wie folgt aus:

   1: wert;

 

   2: summe(17, 4);

und liefert die erwarteten Ergebnisse 73 bzw. 21 (=17 + 4).

Die folgende Abbildung stellt die eben erläuterten Schritte in der der Reihenfolge Initialisierung (rot), Aufruf (grün) und Ergebnisausgabe (blau) von Wert- und Funktionsvariablen in R-Studio bildlich und zusammenfassend dar:

R-Studio Operationen

Mehr R-(Grund-)Kenntnisse werden an dieser Stelle erst einmal nicht benötigt. Für weitere Information sei an dieser Stelle auf Fachliteratur oder auf diverse Tutorials im Internet verwiesen.

 

R Services im SQL-Server einrichten

Um R-Skripte innerhalb des SQL-Servers 2016 nutzen zu können, müssen die R-Services installiert werden. Dies kann bereits bei der initialen Konfiguration des Datenbank-Servers erfolgen, welche hier detaillierter beschrieben und unter anderem durch folgenden Screenshot veranschaulicht wird:

R-Services-Install

Nachdem der SQL-Server zusammen mit den R-Services erfolgreich installiert wurde, gilt es noch die externe Skriptausführung zu aktivieren. Dies kann – die entsprechenden Berechtigungen vorausgesetzt – durch das Aufrufen der sp_configure-Prozedur mit folgenden Parametern geschehen:

   1: sp_configure 'external scripts enabled', 1;

 

   2: RECONFIGURE

Im Anschluss muss die SQL-Server-Instanz auf jeden Fall neu gestartet werden. Des Weiteren sollte nach dem Neustart verifiziert werden, ob die externe Skriptausführung tatsächlich aktiviert ist. Dies kann durch folgende Kontrollabfrage geprüft werden:

   1: SELECT * FROM sys.configurations

 

   2: WHERE name = 'external scripts enabled'

 

Für ein positives Prüfergebnis wird dann folgendes Abfrageresultat zurückgeliefert:

Externe Skriptausführung aktiv

Damit wäre die Installation der R-Services sowie die Aktivierung der externen Skriptausführung auch schon abgeschlossen.

Skripte mit T-SQL ausführen

Die so aktivierten R-Services können dann im via T-SQL mit der sp_execute_external_script-Prozedur aufgerufen werden. Diese Prozedur ist neuer Bestandteil des SQL-Servers 2016 und ermöglicht generell die Ausführung von Skript-Komponenten.

Der Aufruf der Prozedur kann beispielsweise nach folgendem Syntax-Pattern erfolgen:

   1: EXEC sp_execute_external_script

 

   2: @language = N'<Language>',

 

   3: @script = N'<Script-Code>',

 

   4: @input_data_1 = N'<InputData>'

 

   5: [WITH [<ExecuteOption>]];

Die erste Zeile mit der EXEC-Anweisung sorgt für die Ausführung der sp_execute_external_script-Prozedur. Der @language-Parameter in Zeile 2 legt fest, in welcher (Programmier-)Sprache das Skript implementiert ist. Im zweiten Prozedurargument, dem @script-Parameter (Zeile 3), wird anschließend der auszuführende Skript-Code mitgegeben. Dieser ist natürlich abhängig von der mit dem @language-Parameter festgelegten Sprache. Über den dritten Parameter (Zeile 4), welcher den Namen @input_data_1 trägt, können zusätzlich Daten für die Ausführung des Script-Codes mitgegeben werden. Auch diese hängen wieder von der konkret eingesetzten Skriptsprache ab. Den Abschluss des oben aufgeführten Code-Snippet stellt in Zeile 5 der optionale WITH-Part dar, welcher allgemeine Information bzgl. der Ausführung beinhaltet.

Es gibt noch weitere optionale Parameter, welche beim Aufruf der sp_execute_external_script-Prozedur mitgegeben werden können, allerdings spielen diese im Kontext des aktuellen Artikels keine Rolle. Für nähere Informationen sei auf die Dokumentation der MSDN (=Microsoft Developer Network), welche die neue SQL-Server-Prozedur detailliert beschreibt. Dort steht auch der Hinweis, dass nur registrierte Skriptsprachen ausgeführt werden können und die Ausführung der sp_execute_external_script-Prozedur über Ressource Pooling kontrolliert werden kann. Somit können einige sicherheitsrelevante Risiken, wie beispielsweise die heute immer noch häufig auftretenden Code-Injections, vermindert werden.

R-Skript im SQL-Server ausführen

In den bisherigen Abschnitten wurden die Grundlagen dargelegt. Im Folgenden soll nun tatsächlich R-Code innerhalb des SQL-Servers ausgeführt werden. R stellt dem SQL-Server zwei Variablen zur Verfügung, welche die Namen InputDataSet und OutputDataSet tragen. Mit der InputDataSet-Variablen wird Zugriff auf den Inhalt des @input_data_1-Parameters gewährt. Die OutputDataSet-Variable dient zur Ausgabe von Ergebnissen, welche durch das R-Skript ermittelt werden. Es soll noch erwähnt werden, dass das Ergebnis eines Skripts ein data.frame-Objekt sein muss. Dieser R-spezifische Datentyp repräsentiert eine Tabelle mit Spalten und Zeilen, wobei jede Spalte einen anderen Datentyp besitzen kann.

Im folgenden Beispiel soll eine R-Variable namens wert mit konstantem Wert 73 ausgelesen werden. Die Syntax hierfür sieht wie folgt aus:

   1: EXEC sp_execute_external_script

 

   2: @language = N'R',

 

   3: @script = N' wert <- 73;

 

   4:              OutputDataSet <- data.frame(wert);',

 

   5: @input_data_1 = N''

 

   6: WITH RESULT SETS (([Value] INT NOT NULL));

Der @language-Parameter wird mit “R” initialisiert (Zeile 2), somit wird dem SQL-Server signalisiert, dass es sich beim auszuführenden Skript-Code um R-Befehle handelt. Im @script-Parameter selbst wird dann der eigentliche Code (Zeile 3 und Zeile 4) übergeben. Zunächst erfolgt darin die einfache Zuweisung des numerischen Wertes 73 an die Variable wert (Zeile 3). Anschließend wird der Inhalt der wert-Variablen an die OutputDataSet -Variable weitergereicht (Zeile 4). Der Parameter @input_data_1 enthält nur eine leere Zeichenkette (Zeile 5), da in diesem Beispiel keine Daten an den das Skript übergeben werden. Mit dem “WITH RESULT SETS (([Value] INT NOT NULL));”-Part in Zeile 6 wird abschließend noch ein Alias erzeugt, um das Endergebnis mit dem Spaltenkopf “Value” zu versehen.

Das Resultat dieses T-SQL-Befehls liefert im SSMS (=SQL-Server Management Studio) folgendes Ergebnis:

Berechnungsergebnis von R

Fazit

Dieser erste Artikel  zeigt, welche Voraussetzungen anfangs getroffen werden müssen, damit die Statistiksprache R innerhalb der neusten Version des MS SQL-Server genutzt werden kann. Der gesamte Installationsvorgang des SQL-Servers kann sicherlich etwas länger dauern, was bei großen Microsoft-Produkten aber sicher nicht unüblich ist. Nachdem die Konfiguration abgeschlossen ist, kann auch schon mit der Ausführen von R-Skripten im MS-SQL-Servers begonnen werden.

Sicherlich mag es für den ein oder anderen Entwickler zunächst etwas befremdlich wirken, wenn R-Befehle als einfache Strings-Parameter in den SQL-Server eingegeben werden. Allerdings sollte beachtet werden, dass die R-Integration in den Microsoft SQL-Server vermutlich erst am Anfang steht. Allein die Tatsache, dass Microsoft mit R Open eine kostenlose Client-Applikation anbietet, deutet darauf hin, dass R weiterhin Beachtung durch Microsoft erfahren wird. Es darf deshalb stark davon ausgegangen werden, dass das Zusammenspiel zwischen R und dem SQL-Server noch länger weiter geht oder gar intensiviert wird. In meinem nächsten Artikel zu dieser Reihe werden auch komplexere Bespiele vorgestellt.

 

Quellen