Quickstart: Een voorspellend model maken en beoordelen in R met SQL Machine Learning

Van toepassing op: SQL Server 2016 (13.x) en latere versies van Azure SQL Managed Instance

U maakt en voert twee opgeslagen procedures uit die worden uitgevoerd in SQL. De eerste maakt gebruik van de mtcars-gegevensset die is opgenomen in R en genereert een eenvoudig gegeneraliseerd lineair model (GLM) dat de waarschijnlijkheid voorspelt dat een voertuig is uitgerust met een handmatige transmissie. De tweede procedure is voor scoren. Hiermee wordt het model aanroepen dat in de eerste procedure is gegenereerd om een set voorspellingen uit te voeren op basis van nieuwe gegevens. Door R-code in een opgeslagen SQL-procedure te plaatsen, bevinden bewerkingen zich in SQL, zijn ze herbruikbaar en kunnen ze worden aangeroepen door andere opgeslagen procedures en clienttoepassingen.

Door deze quickstart te voltooien, leert u het volgende:

Vereiste voorwaarden

U hebt de volgende vereisten nodig om deze quickstart uit te voeren.

• Een hulpprogramma voor het uitvoeren van SQL-query's die R-scripts bevatten. In deze quickstart wordt Gebruikgemaakt van Azure Data Studio.

Het model maken

Als u het model wilt maken, maakt u brongegevens voor training, maakt u het model en traint u het met behulp van de gegevens en slaat u het model vervolgens op in een database waar het kan worden gebruikt om voorspellingen met nieuwe gegevens te genereren.

De brongegevens maken

1. Open Azure Data Studio, maak verbinding met uw exemplaar en open een nieuw queryvenster.

2. Maak een tabel om de trainingsgegevens op te slaan.

   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. Voeg de gegevens uit de ingebouwde gegevensset mtcarsin.

   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';
   

   Aanbeveling

   Veel gegevenssets, klein en groot, zijn opgenomen in de R-runtime. Als u een lijst met datasets wilt ophalen die met R zijn geïnstalleerd, typt u library(help="datasets") vanaf een R-command prompt.

Het model maken en trainen

De gegevens van de autosnelheid bevatten twee kolommen, zowel numeriek: paardenkracht (hp) als gewicht (wt). Op basis van deze gegevens maakt u een gegeneraliseerd lineair model (GLM) dat de kans schat dat een voertuig is uitgerust met een handmatige transmissie.

Als u het model wilt bouwen, definieert u de formule in uw R-code en geeft u de gegevens door als invoerparameter.

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

• Het eerste argument glm hiervoor is de formuleparameter , die definieert am als afhankelijk van hp + wt.
• De invoergegevens worden opgeslagen in de variabele MTCarsData, die wordt gevuld door de SQL-query. Als u geen specifieke naam aan uw invoergegevens toewijst, is de standaardnaam van de variabele InputDataSet.

Het model opslaan in de database

Sla vervolgens het model op in een database, zodat u het kunt gebruiken voor voorspelling of opnieuw trainen.

1. Maak een tabel om het model op te slaan.

   De uitvoer van een R-pakket dat een model maakt, is meestal een binair object. Daarom moet de tabel waarin u het model opslaat een kolom van het type varbinary(max) hebben.

   CREATE TABLE GLM_models (
       model_name varchar(30) not null default('default model') primary key,
       model varbinary(max) not null
   );
   

2. Voer de volgende Transact-SQL instructie uit om de opgeslagen procedure aan te roepen, het model te genereren en op te slaan in de tabel die u hebt gemaakt.

   INSERT INTO GLM_models(model)
   EXEC generate_GLM;
   

   Aanbeveling

   Als u deze code een tweede keer uitvoert, krijgt u deze fout: 'Schending van DE BEPERKING PRIMAIRE SLEUTEL... Kan geen dubbele sleutel invoegen in object dbo.stopping_distance_models". Een optie voor het voorkomen van deze fout is het bijwerken van de naam voor elk nieuw model. U kunt bijvoorbeeld de naam wijzigen in iets beschrijvender en het modeltype opnemen, de dag waarop u deze hebt gemaakt, enzovoort.

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

Nieuwe gegevens beoordelen met behulp van het getrainde model

Scoren is een term die wordt gebruikt in gegevenswetenschap om te betekenen dat voorspellingen, waarschijnlijkheden of andere waarden worden gegenereerd op basis van nieuwe gegevens die worden ingevoerd in een getraind model. U gebruikt het model dat u in de vorige sectie hebt gemaakt om voorspellingen te beoordelen op basis van nieuwe gegevens.

Een tabel met nieuwe gegevens maken

Maak eerst een tabel met nieuwe gegevens.

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

Handmatige verzending voorspellen

Als u voorspellingen wilt ophalen op basis van uw model, schrijft u een SQL-script dat het volgende doet:

1. Hiermee haalt u het gewenste model op
2. Haalt de nieuwe invoergegevens op
3. Roept een R-voorspellingsfunctie aan die compatibel is met dat model

In de loop van de tijd kan de tabel meerdere R-modellen bevatten, allemaal gebouwd met verschillende parameters of algoritmen of getraind op verschillende subsets van gegevens. In dit voorbeeld gebruiken we het model met de naam 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)));

In het bovenstaande script worden de volgende stappen uitgevoerd:

• Gebruik een SELECT-instructie om één model uit de tabel op te halen en door te geven als invoerparameter.

• Nadat u het model uit de tabel hebt opgehaald, roept u de unserialize functie op het model aan.

• Pas de predict functie met de juiste argumenten toe op het model en geef de nieuwe invoergegevens op.

resultaten

Het is ook mogelijk om de PREDICT-instructie (Transact-SQL) te gebruiken om een voorspelde waarde of score te genereren op basis van een opgeslagen model.

Volgende stappen

Zie voor meer informatie over zelfstudies voor R met SQL Machine Learning:

• R-tutorials