Ändern von R-/Python-Code zur Ausführung in SQL Server-Instanzen (datenbankintern)

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL Managed Instance

Dieser Artikel enthält allgemeine Anleitungen dazu, wie Sie R- oder Python-Code so ändern können, dass er als gespeicherte SQL Server-Prozedur ausgeführt wird, um die Leistung beim Zugriff auf SQL-Daten zu verbessern.

Wenn Sie R-/Python-Code aus einer lokalen integrierten Entwicklungsumgebung (Integrated Development Environment, IDE) oder einer anderen Umgebung in SQL Server verschieben, funktioniert der Code im Allgemeinen ohne weitere Änderung. Dies gilt insbesondere für einfachen Code, z. B. eine Funktion, die einige Eingaben verwendet und einen Wert zurückgibt. Außerdem ist es einfacher, Lösungen zu portieren, die die RevoScaleR/revoscalepy-Pakete verwenden. Diese Pakete unterstützen eine Ausführung in verschiedenen Ausführungskontexten mit minimalen Änderungen. Beachten Sie, dass MicrosoftML für SQL Server 2016 (13.x), SQL Server 2017 (14.x) und SQL Server 2019 (15.x) gilt, aber in SQL Server 2022 (16.x) nicht angezeigt wird.

Allerdings erfordert Ihr Code möglicherweise wesentliche Änderungen, wenn eine der folgenden Bedingungen vorliegt:

  • Sie verwenden R-Bibliotheken, die auf das Netzwerk zugreifen oder in SQL Server nicht installiert werden können.
  • Der Code führt separate Aufrufe von Datenquellen außerhalb von SQL Server aus, z. B. Excel-Arbeitsblätter, Dateien in Freigaben und andere Datenbanken.
  • Sie möchten die gespeicherte Prozedur parametrisieren und den Code im Parameter @script von sp_execute_external_script ausführen.
  • Ihre ursprüngliche Lösung umfasst mehrere Schritte, die bei unabhängiger Ausführung in einer Produktionsumgebung effizienter durchgeführt werden können, z. B. Datenaufbereitung oder Featureentwicklung im Vergleich zu Modelltraining, Bewertung oder Berichterstellung.
  • Sie möchten die Leistung durch Ändern der Bibliotheken optimieren, indem Sie die parallele Ausführung verwenden oder einen Teil der Verarbeitungsvorgänge auf SQL Server abladen.

Schritt 1: Planen der Anforderungen und Ressourcen

Pakete

  • Ermitteln Sie, welche Pakete Sie benötigen, und stellen Sie sicher, dass sie auf SQL Server funktionieren.

  • Installieren Sie die Pakete vorab in der Standardpaketbibliothek, die von Machine Learning Services verwendet wird. Benutzerbibliotheken werden nicht unterstützt.

Datenquellen

  • Wenn Sie Ihren Code in sp_execute_external_script einbetten möchten, identifizieren Sie die primären und sekundären Datenquellen.

    • Primäre Datenquellen sind große Datasets, z. B. Modelltrainingsdaten oder Eingabedaten für Vorhersagen. Sie sollten Ihr größtes Dataset dem Eingabeparameter von sp_execute_external_script zuordnen.

    • Sekundäre Datenquellen sind in der Regel kleinere Datasets, z. B. Faktorlisten oder zusätzliche Gruppierungsvariablen.

    Aktuell unterstützt sp_execute_external_script nur ein einzelnes Dataset als Eingabe für die gespeicherte Prozedur. Sie können jedoch mehrere skalare oder binäre Eingaben hinzufügen.

    Aufrufe gespeicherter Prozeduren, denen EXECUTE vorangestellt ist, können nicht als Eingabe für sp_execute_external_script verwendet werden. Sie können Abfragen, Sichten oder andere gültige SELECT-Anweisungen verwenden.

  • Bestimmen Sie, welche Ausgaben Sie benötigen. Wenn Sie Code mithilfe von „sp_execute_external_script“ ausführen, kann die gespeicherte Prozedur als Ergebnis nur einen Datenrahmen ausgeben. Sie können aber auch mehrere skalare Ausgaben, einschließlich Plots und Modellen im Binärformat, sowie andere Skalarwerte ausgeben, die von Code oder SQL-Parametern abgeleitet wurden.

Datentypen

Einen detaillierten Einblick in die Datentypzuordnungen zwischen R/Python und SQL Server finden Sie in diesen Artikeln:

Sehen Sie sich die in Ihrem R-/Python-Code verwendeten Datentypen an, und führen Sie die folgenden Schritte aus:

  • Stellen Sie eine Prüfliste der möglichen Probleme bei Datentypen zusammen.

    Alle R-/Python-Datentypen werden von SQL Server Machine Learning Services unterstützt. SQL Server unterstützt jedoch eine größere Vielfalt von Datentypen als R oder Python. Daher werden beim Verschieben von SQL Server-Daten in und aus Ihrem Code einige implizite Datentypkonvertierungen durchgeführt. Möglicherweise müssen Sie einige Daten explizit umwandeln oder konvertieren.

    NULL-Werte werden unterstützt. R nutzt jedoch das Datenkonstrukt na, um einen fehlenden Wert darzustellen, der dem Wert NULL ähnelt.

  • Ziehen Sie in Betracht, Abhängigkeiten bei Daten zu entfernen, die von R nicht verwendet werden können. Beispielsweise können die SQL Server-Datentypen ROWID und GUID nicht durch R verarbeitet werden, was zu Fehlern führt.

Schritt 2: Konvertieren oder erneutes Packen von Code

Wie viele Änderungen Sie an Ihrem Code vornehmen, ist davon abhängig, ob Sie den Code von einem Remoteclient zur Ausführung im SQL Server-Computekontext übermitteln oder ihn als Teil einer gespeicherten Prozedur bereitstellen möchten. Letzteres kann eine bessere Leistung und Datensicherheit bieten, ist allerdings mit einigen zusätzlichen Anforderungen verbunden.

  • Definieren Sie Ihre primären Eingabedaten nach Möglichkeit als SQL-Abfrage, um eine Datenverschiebung zu vermeiden.

  • Wenn Sie Code in einer gespeicherten Prozedur ausführen, können Sie mehrere skalare Eingaben übergeben. Fügen Sie zu allen Parametern, die Sie in der Ausgabe verwenden möchten, das Schlüsselwort OUTPUT hinzu.

    Die folgende skalare Eingabe @model_name enthält beispielsweise den Modellnamen, der auch später vom R-Skript geändert wird, sowie die Ergebnisse, die ebenfalls in einer eigenen Spalte ausgegeben werden:

    -- declare a local scalar variable which will be passed into the R script
DECLARE @local_model_name AS NVARCHAR (50) = 'DefaultModel';

-- The below defines an OUTPUT variable in the scope of the R script, called model_name
-- Syntactically, it is defined by using the @model_name name. Be aware that the sequence
-- of these parameters is very important. Mandatory parameters to sp_execute_external_script
-- must appear first, followed by the additional parameter definitions like @params, etc.
EXECUTE sp_execute_external_script @language = N'R', @script = N'
  model_name <- "Model name from R script"
  OutputDataSet <- data.frame(InputDataSet$c1, model_name)'
  , @input_data_1 = N'SELECT 1 AS c1'
  , @params = N'@model_name nvarchar(50) OUTPUT'
  , @model_name = @local_model_name OUTPUT;

-- optionally, examine the new value for the local variable:
SELECT @local_model_name;

  • Alle Variablen, die Sie der gespeicherten Prozedur sp_execute_external_script als Parameter übergeben, müssen Variablen im Code zugeordnet werden. Standardmäßig werden die Variablen nach Namen zugeordnet. Alle Spalten im Eingabedataset müssen ebenfalls Variablen im Skript zugeordnet werden.

    Angenommen, Ihr R-Skript enthält eine Formel wie die folgende:

    formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek

    Ein Fehler wird ausgelöst, wenn das Eingabedataset keine Spalten mit den folgenden übereinstimmenden Namen enthält: ArrDelay, CRSDepTime, DayOfWeek, CRSDepHour und DayOfWeek.

  • In manchen Fällen muss ein Ausgabeschema für die Ergebnisse im Voraus definiert werden.

    Beispielsweise müssen Sie die Klausel WITH RESULT SET zum Festlegen des Schemas verwenden, um die Daten in eine Tabelle einzufügen.

    Das Ausgabeschema ist ebenfalls erforderlich, wenn im Skript das Argument @parallel=1 verwendet wird. Grund hierfür ist, dass mehrere Prozesse von SQL Server zum Ausführen der Abfrage möglicherweise parallel erstellt und die Ergebnisse am Ende gesammelten werden. Daher muss das Ausgabeschema vorbereitet werden, bevor die parallelen Prozesse erstellt werden können.

    In anderen Fällen können Sie das Ergebnisschema auslassen, indem Sie die Option WITH RESULT SETS UNDEFINED verwenden. Diese Anweisung gibt das Dataset aus dem Skript zurück, ohne die Spalten zu benennen oder die SQL-Datentypen festzulegen.

  • Ziehen Sie in Betracht, Zeitsteuerungs- oder Nachverfolgungsdaten mithilfe von T-SQL statt R/Python zu generieren.

    Beispielsweise könnten Sie die für Überwachung und Speicherung verwendete Systemzeit oder andere Informationen übergeben, indem Sie einen T-SQL-Aufruf hinzufügen, der an die Ergebnisse übergeben wird, statt ähnliche Daten im Skript zu generieren.

Verbessern der Leistung und Sicherheit

  • Vermeiden Sie es, Vorhersagen oder Zwischenergebnisse in eine Datei zu schreiben. Schreiben Sie Vorhersagen stattdessen in eine Tabelle, um eine Datenverschiebung zu vermeiden.

  • Führen Sie alle Abfragen im Voraus aus, und überprüfen Sie die SQL Server-Abfragepläne, um Aufgaben zu identifizieren, die parallel ausgeführt werden können.

    Wenn die Eingabeabfrage parallelisiert werden kann, legen Sie @parallel=1 als Teil Ihrer Argumente auf sp_execute_external_script fest.

    Eine Parallelverarbeitung mit diesem Flag ist in der Regel jedes Mal möglich, wenn SQL Server mit partitionierten Tabellen arbeiten oder eine Abfrage zwischen mehreren Prozessen verteilen kann und die Ergebnisse am Ende aggregiert. Eine Parallelverarbeitung mit diesem Flag ist normalerweise nicht möglich, wenn Sie Modelle mithilfe von Algorithmen trainieren, die ein Lesen aller Daten erfordern, oder wenn Sie Aggregate erstellen müssen.

  • Überprüfen Sie Ihren Code, um Schritte zu identifizieren, die unabhängig oder effizienter ausgeführt werden können, und verwenden Sie dazu einen separat gespeicherten Prozeduraufruf. So könnten Sie beispielsweise eine bessere Leistung erzielen, indem Sie die Featureentwicklung oder Featureextraktion separat durchführen und die Werte in einer Tabelle speichern.

  • Suchen Sie nach Möglichkeiten, T-SQL statt R-/Python-Code für setbasierte Berechnungen zu verwenden.

    Diese R-Lösung veranschaulicht beispielsweise, wie benutzerdefinierte T-SQL-Funktionen und R dieselbe Featureentwicklungsaufgabe durchführen können: Exemplarische Vorgehensweise für Data Science.

  • Lassen Sie sich von einem Datenbankentwickler beraten, um Möglichkeiten für eine Leistungsverbesserung mithilfe von SQL Server-Features zu finden, z. B. mit arbeitsspeicheroptimierten Tabellen oder mit dem Resource Governor, wenn Sie über die Enterprise Edition verfügen.

  • Wenn Sie R verwenden, ersetzen Sie konventionelle R-Funktionen nach Möglichkeit durch RevoScaleR-Funktionen, die eine verteilte Ausführung unterstützen. Weitere Informationen finden Sie unter Grundlegende R- und RevoScaleR-Funktionen im Vergleich.

Schritt 3: Vorbereiten der Bereitstellung

  • Benachrichtigen Sie den Administrator, damit Pakete installiert und im Voraus getestet werden, bevor Sie Ihren Code bereitstellen.

    In einer Entwicklungsumgebung lassen sich Pakete als Teil des Codes durchaus installieren. In einer Produktionsumgebung empfiehlt sich diese Vorgehensweise jedoch nicht.

    Benutzerbibliotheken werden unabhängig davon, ob Sie eine gespeicherte Prozedur verwenden oder R-/Python-Code im SQL Server-Computekontext ausführen, nicht unterstützt.

Packen Ihres R-/Python-Codes in eine gespeicherte Prozedur

  • Erstellen Sie eine benutzerdefinierte T-SQL-Funktion, und betten Sie Ihren Code mithilfe der Anweisung sp-execute-extern-script ein.

  • Wenn Sie komplexen R-Code haben, verwenden Sie das R-Paket sqlrutils zum Konvertieren Ihres Codes. Dieses Paket wurde dazu konzipiert, erfahrene R-Benutzer beim Schreiben von gutem Code für gespeicherte Prozeduren zu unterstützen. Sie schreiben Ihren R-Code als einzelne Funktion mit klar definierten Eingaben und Ausgaben um und generieren dann die Ein- und Ausgaben mit dem Paket sqlrutils im richtigen Format. Das Paket sqlrutils generiert den gesamten Code für die gespeicherte Prozedur für Sie. Es kann die gespeicherte Prozedur auch in der Datenbank registrieren.

    Weitere Informationen und Beispiele finden Sie unter sqlrutils (SQL).

Integration in andere Workflows

  • Nutzen Sie T-SQL-Tools und ETL-Prozesse. Führen Sie Featureentwicklung, Featureextraktion und Datenbereinigung im Rahmen Ihrer Datenworkflows im Voraus durch.

    Wenn Sie in einer dedizierten Entwicklungsumgebung arbeiten, könnten Sie Daten per Pull auf Ihren Computer übertragen, sie iterativ analysieren und dann die Ergebnisse ausgeben oder anzeigen. Bei einer Migration von eigenständigem Code zu SQL Server kann ein Großteil dieses Prozesses jedoch vereinfacht oder an andere SQL Server-Tools delegiert werden.

  • Verwenden Sie sichere, asynchrone Vorgehensweisen für die Visualisierung.

    Benutzer von SQL Server können oft nicht auf Dateien auf dem Server zugreifen, und SQL-Clienttools unterstützen normalerweise nicht die R-/Python-Grafikgeräte. Wenn Sie Plots oder andere Grafiken im Rahmen der Lösung generieren, sollten Sie das Exportieren der Plots als Binärdaten und das Speichern bzw. Schreiben dieser in eine Tabelle in Betracht ziehen.

  • Umschließen Sie Vorhersage- und Bewertungsfunktionen für den direkten Zugriff von Anwendungen in gespeicherten Prozeduren.

Nächste Schritte

Beispiele dazu, wie R- und Python-Lösungen in SQL Server bereitgestellt werden können, finden Sie in diesen Tutorials:

R-Tutorials

Python-Tutorials