Schnellstart: Erstellen und Bewerten eines Vorhersagemodells in R mit SQL Server Machine Learning ServicesQuickstart: Create and score a predictive model in R with SQL Server Machine Learning Services

Gilt für: JaSQL Server NeinAzure SQL-Datenbank NeinAzure Synapse Analytics (SQL DW) NeinParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

In diesem Schnellstart erstellen und trainieren Sie ein Vorhersagemodell mit R, speichern das Modell in einer Tabelle in Ihrer SQL Server-Instanz und verwenden es dann zur Vorhersage von Werten aus neuen Daten mit SQL Server Machine Learning Services.In this quickstart, you'll create and train a predictive model using T. You'll save the model to a table in your SQL Server instance, and then use the model to predict values from new data using SQL Server Machine Learning Services.

Hierzu erstellen Sie zwei gespeicherte Prozeduren, die in SQL ausgeführt werden.You'll create and execute two stored procedures running in SQL. Die erste Prozedur nutzt das in R enthaltene Dataset mtcars und generiert ein einfaches, verallgemeinertes lineares Modell, das die Wahrscheinlichkeit vorhersagt, mit der ein Fahrzeug mit einem Handschaltgetriebe ausgestattet wurde.The first one uses the mtcars dataset included with R and generates a simple generalized linear model (GLM) that predicts the probability that a vehicle has been fitted with a manual transmission. Die zweite Prozedur ist für die Bewertung vorgesehen: Sie ruft das in der ersten Prozedur generierte Modell auf, um mehrere Vorhersagen basierend auf neuen Daten auszugeben.The second procedure is for scoring - it calls the model generated in the first procedure to output a set of predictions based on new data. Durch das Platzieren von Python-Code in einer gespeicherten SQL-Prozedur werden Vorgänge in SQL eingefügt, die wiederverwendbar sind und von anderen gespeicherten Prozeduren und Clientanwendungen aufgerufen werden können.By placing R code in a SQL stored procedure, operations are contained in SQL, are reusable, and can be called by other stored procedures and client applications.

Tipp

Mit dem folgenden Tutorial, in dem der Prozess zum Anpassen eines Modells mithilfe von rxLinMod beschrieben wird, können Sie Ihr Wissen über lineare Modelle auffrischen: Fitting Linear Models (Anpassen von linearen Modellen).If you need a refresher on linear models, try this tutorial which describes the process of fitting a model using rxLinMod: Fitting Linear Models

In diesem Schnellstart lernen Sie Folgendes:By completing this quickstart, you'll learn:

  • Einbetten von R-Code in eine gespeicherte ProzedurHow to embed R code in a stored procedure
  • Übergeben von Eingaben an Ihren Code mithilfe von Eingaben in der gespeicherten ProzedurHow to pass inputs to your code through inputs on the stored procedure
  • Verwenden von gespeicherten Prozeduren zum Operationalisieren von ModellenHow stored procedures are used to operationalize models

VoraussetzungenPrerequisites

  • Für diesen Schnellstart benötigen Sie Zugriff auf eine SQL Server-Instanz mit SQL Server Machine Learning Services, in der die R-Sprache installiert ist.This quickstart requires access to an instance of SQL Server with SQL Server Machine Learning Services with the R language installed.

    Ihre SQL Server-Instanz kann sich auf einem virtuellen Azure-Computer oder einem lokalen Computer befinden.Your SQL Server instance can be in an Azure virtual machine or on-premises. Achten Sie darauf, dass das externe Skripterstellungsfeature standardmäßig deaktiviert ist. Sie müssen die externe Skripterstellung also möglicherweise aktivieren und überprüfen, ob das SQL Server-Launchpad ausgeführt wird, bevor Sie beginnen.Just be aware that the external scripting feature is disabled by default, so you might need to enable external scripting and verify that SQL Server Launchpad service is running before you start.

  • Außerdem benötigen Sie ein Tool zum Ausführen von SQL-Abfragen, die R-Skripts enthalten.You also need a tool for running SQL queries that contain R scripts. Sie können diese Skripts mit einem beliebigen Tool für die Datenbankverwaltung oder -abfrage verwalten, sofern dieses eine Verbindung mit SQL Server-Instanzen herstellen und T-SQL-Abfragen oder gespeicherte Prozeduren ausführen kann.You can run these scripts using any database management or query tool, as long as it can connect to a SQL Server instance, and run a T-SQL query or stored procedure. Für diesen Schnellstart wird SQL Server Management Studio (SSMS) verwendet.This quickstart uses SQL Server Management Studio (SSMS).

Erstellen des ModellsCreate the model

Zum Erstellen des Modells erstellen Sie Quelldaten für das Training, erstellen das Modell und trainieren es mit diesen Daten, und speichern das Modell anschließend in einer SQL-Datenbank, in der es zum Erstellen von Vorhersagen mit neuen Daten verwendet werden kann.To create the model, you'll create source data for training, create the model and train it using the data, then store the model in a SQL database where it can be used to generate predictions with new data.

Erstellen der QuelldatenCreate the source data

  1. Öffnen Sie SSMS, stellen Sie eine Verbindung mit Ihrer SQL Server-Instanz her, und öffnen Sie ein neues Abfragefenster.Open SSMS, connect to your SQL Server instance, and open a new query window.

  2. Erstellen Sie eine Tabelle zum Speichern der Trainingsdaten.Create a table to save the training data.

    CREATE TABLE dbo.MTCars(
        mpg decimal(10, 1) NOT NULL,
        cyl int NOT NULL,
        disp decimal(10, 1) NOT NULL,
        hp int NOT NULL,
        drat decimal(10, 2) NOT NULL,
        wt decimal(10, 3) NOT NULL,
        qsec decimal(10, 2) NOT NULL,
        vs int NOT NULL,
        am int NOT NULL,
        gear int NOT NULL,
        carb int NOT NULL
    );
    
  3. Fügen Sie die Daten aus dem integrierten Dataset mtcars ein.Insert the data from the built-in dataset mtcars.

    INSERT INTO dbo.MTCars
    EXEC sp_execute_external_script @language = N'R'
        , @script = N'MTCars <- mtcars;'
        , @input_data_1 = N''
        , @output_data_1_name = N'MTCars';
    

    Tipp

    In der R-Runtime sind viele kleine und große Datasets enthalten.Many datasets, small and large, are included with the R runtime. Geben Sie library(help="datasets") in eine R-Eingabeaufforderung ein, um eine Liste von installierten Datasets mit R abzurufen.To get a list of datasets installed with R, type library(help="datasets") from an R command prompt.

Erstellen und Trainieren des ModellsCreate and train the model

Die Daten der Autogeschwindigkeit enthalten zwei numerische Spalten: Pferdestärke (hp) und Gewicht (wt).The car speed data contains two columns, both numeric: horsepower (hp) and weight (wt). Aus diesen Daten erstellen Sie ein verallgemeinertes lineares Modell, das die Wahrscheinlichkeit schätzt, mit der ein Fahrzeug mit einem Handschaltgetriebe ausgestattet wurde.From this data, you'll create a generalized linear model (GLM) that estimates the probability that a vehicle has been fitted with a manual transmission.

Sie definieren die Formel in Ihrem R-Code und übergeben die Daten als Eingabeparameter, um das Modell zu erstellen.To build the model, you define the formula inside your R code, and pass the data as an input parameter.

DROP PROCEDURE IF EXISTS generate_GLM;
GO
CREATE PROCEDURE generate_GLM
AS
BEGIN
    EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'carsModel <- glm(formula = am ~ hp + wt, data = MTCarsData, family = binomial);
        trained_model <- data.frame(payload = as.raw(serialize(carsModel, connection=NULL)));'
    , @input_data_1 = N'SELECT hp, wt, am FROM MTCars'
    , @input_data_1_name = N'MTCarsData'
    , @output_data_1_name = N'trained_model'
    WITH RESULT SETS ((model VARBINARY(max)));
END;
GO
  • Das erste Argument für glm ist der Parameter formula, der am als abhängig von hp + wt definiert.The first argument to glm is the formula parameter, which defines am as dependent on hp + wt.
  • Die Eingabedaten werden in der Variablen MTCarsData gespeichert, die mit der SQL-Abfrage aufgefüllt wird.The input data is stored in the variable MTCarsData, which is populated by the SQL query. Wenn Sie Ihren Eingabedaten nicht einen spezifischen Namen zuweisen, lautet der Standardvariablenname InputDataSet.If you don't assign a specific name to your input data, the default variable name would be InputDataSet.

Speichern des Modells in der SQL-DatenbankStore the model in the SQL database

Als Nächstes speichern Sie das Modell in einer SQL-Datenbank, damit Sie es für Vorhersagen verwenden oder erneut trainieren können.Next, store the model in a SQL database so you can use it for prediction or retrain it.

  1. Erstellen Sie eine Tabelle zum Speichern des Modells.Create a table to store the model.

    Die Ausgabe eines R-Pakets, das ein Modell erstellt, ist normalerweise ein binäres Objekt.The output of an R package that creates a model is usually a binary object. Daher muss die Tabelle, in der Sie das Modell speichern, eine Spalte vom Typ varbinary(max) bereitstellen.Therefore, the table where you store the model must provide a column of varbinary(max) type.

    CREATE TABLE GLM_models (
        model_name varchar(30) not null default('default model') primary key,
        model varbinary(max) not null
    );
    
  2. Führen Sie die folgende Transact-SQL-Anweisung aus, um die gespeicherte Prozedur aufzurufen, das Modell zu generieren und dieses in der erstellten Tabelle zu speichern.Run the following Transact-SQL statement to call the stored procedure, generate the model, and save it to the table you created.

    INSERT INTO GLM_models(model)
    EXEC generate_GLM;
    

    Tipp

    Wenn Sie diesen Code ein zweites Mal ausführen, erhalten Sie den folgenden Fehler: „Violation of PRIMARY KEY constraint...Cannot insert duplicate key in object dbo.stopping_distance_models.“ (Verstoß gegen die PRIMARY KEY-Einschränkung... Ein doppelter Schlüssel kann nicht in das Objekt „dbo.stopping_distance_models“ eingefügt werden.)If you run this code a second time, you get this error: "Violation of PRIMARY KEY constraint...Cannot insert duplicate key in object dbo.stopping_distance_models". Eine Option zur Vermeidung dieses Fehlers ist das Aktualisieren des Namens für jedes neue Modell.One option for avoiding this error is to update the name for each new model. Sie können den Namen beispielsweise in eine aussagekräftigere Zeichenfolge ändern und den Modelltyp, den Tag der Erstellung usw. einfügen.For example, you could change the name to something more descriptive, and include the model type, the day you created it, and so forth.

    UPDATE GLM_models
    SET model_name = 'GLM_' + format(getdate(), 'yyyy.MM.HH.mm', 'en-gb')
    WHERE model_name = 'default model'
    

Bewerten neuer Daten mithilfe des trainierten ModellsScore new data using the trained model

Der Begriff Bewertung bezieht sich bei Data Science auf die Erstellung von Vorhersagen, Wahrscheinlichkeiten oder anderen Werten, die auf neuen Daten basieren, die einem trainierten Modell zugeführt werden.Scoring is a term used in data science to mean generating predictions, probabilities, or other values based on new data fed into a trained model. Sie verwenden das Modell, das Sie im vorherigen Abschnitt erstellt haben, um Vorhersagen für neue Daten zu bewerten.You'll use the model you created in the previous section to score predictions against new data.

Erstellen einer Tabelle aus neuen DatenCreate a table of new data

Erstellen Sie zunächst eine Tabelle mit neuen Daten.First, create a table with new data.

CREATE TABLE dbo.NewMTCars(
    hp INT NOT NULL
    , wt DECIMAL(10,3) NOT NULL
    , am INT NULL
)
GO

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (110, 2.634)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (72, 3.435)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (220, 5.220)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (120, 2.800)
GO

Vorhersagen von HandschaltgetriebenPredict manual transmission

Schreiben Sie ein SQL-Skript mit den folgenden Funktionen, um Vorhersagen basierend auf Ihrem Modell zu erhalten:To get predictions based on your model, write a SQL script that does the following:

  1. Ruft das gewünschte Modell abGets the model you want
  2. Ruft die neuen Eingabedaten abGets the new input data
  3. Ruft eine R-Vorhersagefunktion auf, die mit dem Modell kompatibel istCalls an R prediction function that is compatible with that model

Mit der Zeit sammeln sich womöglich mehrere R-Modelle in der Tabelle an, die alle mit unterschiedlichen Parametern oder Algorithmen erstellt oder mit verschiedenen Datenteilmengen trainiert wurden.Over time, the table might contain multiple R models, all built using different parameters or algorithms, or trained on different subsets of data. In diesem Beispiel wird das Modell namens default model verwendet.In this example, we'll use the model named default model.

DECLARE @glmmodel varbinary(max) = 
    (SELECT model FROM dbo.GLM_models WHERE model_name = 'default model');

EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'
            current_model <- unserialize(as.raw(glmmodel));
            new <- data.frame(NewMTCars);
            predicted.am <- predict(current_model, new, type = "response");
            str(predicted.am);
            OutputDataSet <- cbind(new, predicted.am);
            '
    , @input_data_1 = N'SELECT hp, wt FROM dbo.NewMTCars'
    , @input_data_1_name = N'NewMTCars'
    , @params = N'@glmmodel varbinary(max)'
    , @glmmodel = @glmmodel
WITH RESULT SETS ((new_hp INT, new_wt DECIMAL(10,3), predicted_am DECIMAL(10,3)));

Das obige Skript für die folgenden Schritte aus:The script above performs the following steps:

  • Verwenden Sie eine SELECT-Anweisung, um ein einzelnes Modell aus der Tabelle abzurufen, und übergeben Sie es als Eingabeparameter.Use a SELECT statement to get a single model from the table, and pass it as an input parameter.

  • Rufen Sie nach dem Abruf des Modells aus der Tabelle die Funktion unserialize für das Modell auf.After retrieving the model from the table, call the unserialize function on the model.

  • Wenden Sie die Funktion predict mit den passenden Argumenten auf das Modell an, und geben Sie die neuen Eingabedaten an.Apply the predict function with appropriate arguments to the model, and provide the new input data.

Hinweis

Im Beispiel wird die str-Funktion während der Testphase hinzugefügt, um das Schema der von R zurückgegebenen Daten zu überprüfen. Sie können diese Anweisung später entfernen.In the example, the str function is added during the testing phase, to check the schema of data being returned from R. You can remove the statement later.

Die im R-Skript verwendeten Spaltennamen werden nicht zwangsläufig an die Ausgabe der gespeicherten Prozedur übergeben.The column names used in the R script are not necessarily passed to the stored procedure output. Hier wird die Klausel WITH RESULTS verwenden, um einige neue Spaltennamen zu definieren.Here the WITH RESULTS clause is used to define some new column names.

ErgebnisseResults

Resultset für die Vorhersage der Wahrscheinlichkeit von Handschaltgetrieben

Es ist auch möglich, die PREDICT (Transact-SQL)-Anweisung zum Generieren eines vorhergesagten Werts oder einer Bewertung für ein gespeichertes Modell zu verwenden.It's also possible to use the PREDICT (Transact-SQL) statement to generate a predicted value or score based on a stored model.

Nächste SchritteNext steps

Weitere Informationen zu SQL Server Machine Learning Services finden Sie unter:For more information on SQL Server Machine Learning Services, see: