Konvertieren von R-Code in eine gespeicherte Prozedur mithilfe von sqlrutils

In diesem Artikel werden die Schritte für die Verwendung des sqlrutils-Pakets zum Konvertieren des R-Codes beschrieben, die als gespeicherte T-SQL-Prozedur ausgeführt werden. Um optimale Ergebnisse zu erzielen, muss Der Code möglicherweise etwas geändert werden, um sicherzustellen, dass alle Eingaben parametrisiert werden können.

Schritt 1. Rewrite R Script

Um optimale Ergebnisse zu erzielen, sollten Sie den R-Code neu schreiben, um ihn als einzelne Funktion zu kapseln.

Alle variablen, die von der Funktion verwendet werden, sollten innerhalb der Funktion definiert oder als Eingabeparameter definiert werden. Weitere Informationen finden Sie im Beispielcode in diesem Artikel.

Da die Eingabeparameter für die R-Funktion zu den Eingabeparametern der gespeicherten SQL-Prozedur werden, müssen Sie außerdem sicherstellen, dass Ihre Eingaben und Ausgaben den folgenden Typanforderungen entsprechen:

Eingaben

Unter den Eingabeparametern kann es höchstens einen Datenrahmen geben.

Die Objekte innerhalb des Datenrahmens sowie alle anderen Eingabeparameter der Funktion müssen die folgenden R-Datentypen aufweisen:

• POSIXct
• numerisch
• character
• integer
• logisch
• raw

Wenn ein Eingabetyp nicht einer der oben genannten Typen ist, muss er serialisiert und als unformatiert an die Funktion übergeben werden. In diesem Fall muss die Funktion auch Code enthalten, um die Eingabe zu deserialisieren.

Outputs

Die Funktion kann eine der folgenden Aktionen ausgeben:

• Ein Datenrahmen, der die unterstützten Datentypen enthält. Alle Objekte im Datenframe müssen einen der unterstützten Datentypen verwenden.
• Eine benannte Liste, die höchstens einen Datenrahmen enthält. Alle Mitglieder der Liste sollten einen der unterstützten Datentypen verwenden.
• Ein NULL-Wert, wenn die Funktion kein Ergebnis zurückgibt

Schritt 2. Generieren erforderlicher Objekte

Nachdem Ihr R-Code bereinigt wurde und als einzelne Funktion aufgerufen werden kann, verwenden Sie die Funktionen im sqlrutils-Paket , um die Eingaben und Ausgaben in einem Formular vorzubereiten, das an den Konstruktor übergeben werden kann, der die gespeicherte Prozedur tatsächlich erstellt.

sqlrutils stellt Funktionen bereit, die das Eingabedatenschema und den Typ definieren und das Ausgabedatenschema und den Typ definieren. Sie enthält auch Funktionen, mit denen R-Objekte in den erforderlichen Ausgabetyp konvertiert werden können. Sie können mehrere Funktionsaufrufe ausführen, um die erforderlichen Objekte zu erstellen, je nach den datentypen, die Ihr Code verwendet.

Eingaben

Wenn Ihre Funktion Eingaben übernimmt, rufen Sie für jede Eingabe die folgenden Funktionen auf:

• setInputData wenn es sich bei der Eingabe um einen Datenrahmen handelt
• setInputParameter für alle anderen Eingabetypen

Wenn Sie jeden Funktionsaufruf ausführen, wird ein R-Objekt erstellt, das Sie später als Argument StoredProcedureübergeben werden, um die vollständige gespeicherte Prozedur zu erstellen.

Outputs

sqlrutils stellt mehrere Funktionen zum Konvertieren von R-Objekten wie Listen in den von SQL Server erforderlichen Data.frame bereit. Wenn Ihre Funktion einen Datenframe direkt ausgibt, ohne ihn zuerst in eine Liste umschließen zu müssen, können Sie diesen Schritt überspringen. Sie können die Konvertierung dieses Schritts auch überspringen, wenn Ihre Funktion NULL zurückgibt.

Wenn Sie eine Liste konvertieren oder ein bestimmtes Element aus einer Liste abrufen, wählen Sie aus den folgenden Funktionen aus:

• setOutputData wenn die Variable, die aus der Liste abgerufen werden soll, ein Datenrahmen ist
• setOutputParameter für alle anderen Mitglieder der Liste

Wenn Sie jeden Funktionsaufruf ausführen, wird ein R-Objekt erstellt, das Sie später als Argument StoredProcedureübergeben werden, um die vollständige gespeicherte Prozedur zu erstellen.

Schritt 3: Generieren der gespeicherten Prozedur

Wenn alle Eingabe- und Ausgabeparameter bereit sind, rufen Sie den StoredProcedure Konstruktor auf.

Usage

StoredProcedure (func, spName, ..., filePath = NULL ,dbName = NULL, connectionString = NULL, batchSeparator = "GO")

Gehen Sie zur Veranschaulichung davon aus, dass Sie eine gespeicherte Prozedur mit dem Namen sp_rsample mit diesen Parametern erstellen möchten:

• Verwendet eine vorhandene Funktion foosql. Die Funktion basiert auf vorhandenem Code in der Funktion R-Funktion foo, aber Sie schreiben die Funktion um, um die Anforderungen zu erfüllen, wie in diesem Abschnitt beschrieben, und die aktualisierte Funktion als foosql benannt.
• Verwendet die Datenframeabfrage als Eingabe
• Generiert als Ausgabe eines Datenrahmens mit dem Variablennamen R , sqloutput
• Sie möchten den T-SQL-Code als Datei im C:\Temp Ordner erstellen, damit Sie ihn mit SQL Server Management Studio später ausführen können.

StoredProcedure (foosql, sp_rsample, queryinput, sqloutput, filePath = "C:\\Temp")

Die Ausgabe der Funktion ist eine gespeicherte T-SQL-Prozedur, die für eine Instanz von SQL Server 2016 (erfordert R Services) oder SQL Server 2017 ausgeführt werden kann (erfordert Machine Learning Dienste mit R).

Weitere Beispiele finden Sie in der Pakethilfe, indem Sie aus einer R-Umgebung aufrufen help(StoredProcedure) .

Schritt 4. Registrieren und Ausführen der gespeicherten Prozedur

Es gibt zwei Möglichkeiten, die gespeicherte Prozedur auszuführen:

• Verwenden von T-SQL von jedem Client, der Verbindungen mit der SQL Server 2016- oder SQL Server 2017-Instanz unterstützt
• Aus einer R-Umgebung

Beide Methoden erfordern, dass die gespeicherte Prozedur in der Datenbank registriert wird, in der Sie die gespeicherte Prozedur verwenden möchten.

Registrieren der gespeicherten Prozedur

Sie können die gespeicherte Prozedur mit R registrieren oder die CREATE PROCEDURE Anweisung in T-SQL ausführen.

• Verwenden von T-SQL. Wenn Sie mit T-SQL vertraut sind, öffnen Sie SQL Server Management Studio (oder einen anderen Client, der SQL DDL-Befehle ausführen kann), und führen Sie die CREATE PROCEDURE Anweisung mit dem von der StoredProcedure Funktion vorbereiteten Code aus.

• Verwenden von R. Während Sie sich noch in Ihrer R-Umgebung befinden, können Sie die registerStoredProcedure Funktion in sqlrutils verwenden, um die gespeicherte Prozedur mit der Datenbank zu registrieren.

  Sie können beispielsweise die gespeicherte Prozedur sp_rsample in der Instanz und Datenbank registrieren, die in sqlConnStr definiert ist, indem Sie diesen R-Aufruf ausführen:

  registerStoredProcedure(sp_rsample, sqlConnStr)
  

Ausführen mit SQL

Nachdem die gespeicherte Prozedur erstellt wurde, öffnen Sie eine Verbindung mit der SQL-Datenbank mit einem beliebigen Client, der T-SQL unterstützt, und übergeben Sie Werte für alle Parameter, die für die gespeicherte Prozedur erforderlich sind.

Ausführen mit R

Einige zusätzliche Vorbereitungen sind erforderlich, wenn Sie die gespeicherte Prozedur aus R-Code ausführen möchten, anstatt von SQL Server. Wenn die gespeicherte Prozedur z. B. Eingabewerte erfordert, müssen Sie diese Eingabeparameter festlegen, bevor die Funktion ausgeführt werden kann, und diese Objekte dann in Ihrem R-Code an die gespeicherte Prozedur übergeben.

Der allgemeine Prozess des Aufrufens der vorbereiteten gespeicherten SQL-Prozedur lautet wie folgt:

1. Rufen Sie getInputParameters auf, um eine Liste der Eingabeparameterobjekte abzurufen.
2. Definieren oder Festlegen einer $query$value für jeden Eingabeparameter.
3. Wird executeStoredProcedure verwendet, um die gespeicherte Prozedur aus der R-Entwicklungsumgebung auszuführen und die Liste der von Ihnen festgelegten Eingabeparameterobjekte zu übergeben.

Beispiel

Dieses Beispiel zeigt die Vor- und Nachversionen eines R-Skripts, die Daten aus einer SQL Server-Datenbank abruft, einige Transformationen für die Daten durchführt und in einer anderen Datenbank speichert.

Dieses einfache Beispiel wird nur verwendet, um zu veranschaulichen, wie Sie Den R-Code neu anordnen können, um die Konvertierung in eine gespeicherte Prozedur zu vereinfachen.

Vor der Codevorbereitung

sqlConnFrom <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=AirlineSrc;Trusted_Connection=Yes;"
  
sqlConnTo <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=AirlineTest;Trusted_Connection=Yes;"
  
sqlQueryAirline <- "SELECT TOP 10000 ArrDelay, CRSDepTime, DayOfWeek FROM [AirlineDemoSmall]"
  
dsSqlFrom <- RxSqlServerData(sqlQuery = sqlQueryAirline, connectionString = sqlConnFrom)
  
dsSqlTo <- RxSqlServerData(table = "cleanData", connectionString = sqlConnTo)
  
xFunc <- function(data) {
    data$CRSDepHour <- as.integer(trunc(data$CRSDepTime))
    return(data)
    }
  
xVars <- c("CRSDepTime")
  
sqlCompute <- RxInSqlServer(numTasks = 4, connectionString = sqlConnTo)
  
rxOpen(dsSqlFrom)
rxOpen(dsSqlTo)
  
if (rxSqlServerTableExists("cleanData", connectionString = sqlConnTo))   {
    rxSqlServerDropTable("cleanData")}
  
rxDataStep(inData = dsSqlFrom, 
     outFile = dsSqlTo,
     transformFunc = xFunc,
     transformVars = xVars,
     overwrite = TRUE)

Nach der Codevorbereitung

In der aktualisierten Version definiert die erste Zeile den Funktionsnamen. Alle anderen Code aus der ursprünglichen R-Lösung werden Teil dieser Funktion.

myetl1function <- function() { 
   sqlConnFrom <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=Airline01;Trusted_Connection=Yes;"
   sqlConnTo <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer02;Database=Airline02;Trusted_Connection=Yes;"
    
   sqlQueryAirline <- "SELECT TOP 10000 ArrDelay, CRSDepTime, DayOfWeek FROM [AirlineDemoSmall]"

   dsSqlFrom <- RxSqlServerData(sqlQuery = sqlQueryAirline, connectionString = sqlConnFrom)
  
   dsSqlTo <- RxSqlServerData(table = "cleanData", connectionString = sqlConnTo)
  
   xFunc <- function(data) {
     data$CRSDepHour <- as.integer(trunc(data$CRSDepTime))
     return(data)}
  
   xVars <- c("CRSDepTime")
  
   sqlCompute <- RxInSqlServer(numTasks = 4, connectionString = sqlConnTo)
  
   if (rxSqlServerTableExists("cleanData", connectionString = sqlConnTo)) {rxSqlServerDropTable("cleanData")}
  
   rxDataStep(inData = dsSqlFrom, 
        outFile = dsSqlTo,
        transformFunc = xFunc,
        transformVars = xVars,
        overwrite = TRUE)
   return(NULL)
}

Siehe auch

sqlrutils-Referenz