Een R-model bouwen en opslaan in SQL Server (overzicht)

Van toepassing op: SQL Server 2016 (13.x) en latere versies

In deze stap leert u hoe u een machine learning-model bouwt en het model opslaat in SQL Server. Door een model op te slaan, kunt u het rechtstreeks aanroepen vanuit Transact-SQL code, met behulp van de door het systeem opgeslagen procedure, sp_execute_external_script of de functie PREDICT (T-SQL).

Prerequisites

In deze stap wordt ervan uitgegaan dat er een lopende R-sessie is op basis van de vorige stappen in deze handleiding. Hierbij worden de verbindingsreeksen en gegevensbronobjecten gebruikt die in deze stappen zijn gemaakt. De volgende hulpprogramma's en pakketten worden gebruikt om het script uit te voeren.

  • Rgui.exe R-opdrachten uitvoeren
  • Management Studio voor het uitvoeren van T-SQL
  • ROCR-pakket
  • RODBC-pakket

Een opgeslagen procedure maken om modellen op te slaan

In deze stap wordt een opgeslagen procedure gebruikt om een getraind model op te slaan in SQL Server. Als u een opgeslagen procedure maakt om deze bewerking uit te voeren, is de taak eenvoudiger.

Voer de volgende T-SQL-code uit in een queryvenster in Management Studio om de opgeslagen procedure te maken.

USE [NYCTaxi_Sample]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'PersistModel')
  DROP PROCEDURE PersistModel
GO

CREATE PROCEDURE [dbo].[PersistModel] @m nvarchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	insert into nyc_taxi_models (model) values (convert(varbinary(max),@m,2))
END
GO

Note

Als u een foutmelding krijgt, zorg er dan voor dat uw account machtigingen heeft om objecten te maken. U kunt expliciete machtigingen verlenen om objecten te maken door een T-SQL-instructie als volgt uit te voeren: exec sp_addrolemember 'db_owner', '<user_name>'.

Een classificatiemodel maken met rxLogit

Het model is een binaire classificatie die voorspelt of de taxichauffeur waarschijnlijk een tip krijgt over een bepaalde rit of niet. U gebruikt de gegevensbron die u in de vorige les hebt gemaakt om de tipclassificatie te trainen met behulp van logistieke regressie.

  1. Roep de rxLogit-functie aan, opgenomen in het RevoScaleR-pakket , om een logistiek regressiemodel te maken.

    system.time(logitObj <- rxLogit(tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance, data = featureDataSource));

    De aanroep waarmee het model wordt gebouwd, wordt ingesloten in de system.time-functie. Hiermee kunt u de tijd krijgen die nodig is om het model te bouwen.

  2. Nadat u het model hebt gemaakt, kunt u het inspecteren met behulp van de summary functie en de coëfficiënten bekijken.

    summary(logitObj);

    Resultaten

     *Logistic Regression Results for: tipped ~ passenger_count + trip_distance + trip_time_in_secs +*
 direct_distance* 
 *Data: featureDataSource (RxSqlServerData Data Source)*
 *Dependent variable(s): tipped*
 *Total independent variables: 5*
 *Number of valid observations: 17068*
 *Number of missing observations: 0*
 *-2\*LogLikelihood: 23540.0602 (Residual deviance on 17063 degrees of freedom)*
 *Coefficients:*
 *Estimate Std. Error z value Pr(>|z|)*
 *(Intercept)       -2.509e-03  3.223e-02  -0.078  0.93793*
 *passenger_count   -5.753e-02  1.088e-02  -5.289 1.23e-07 \*\*\**
 *trip_distance     -3.896e-02  1.466e-02  -2.658  0.00786 \*\**
 *trip_time_in_secs  2.115e-04  4.336e-05   4.878 1.07e-06 \*\*\**
 *direct_distance    6.156e-02  2.076e-02   2.966  0.00302 \*\**
 *---*
 *Signif. codes:  0 '\*\*\*' 0.001 '\*\*' 0.01 '\*' 0.05 '.' 0.1 ' ' 1*
 *Condition number of final variance-covariance matrix: 48.3933*
 *Number of iterations: 4*

Het logistieke regressiemodel gebruiken voor scoren

Nu het model is gebouwd, kunt u het gebruiken om te voorspellen of de chauffeur bij een bepaalde rit waarschijnlijk een fooi krijgt of niet.

  1. Gebruik eerst de functie RxSqlServerData om een gegevensbronobject te definiëren voor het opslaan van het scoreresultaat.

    scoredOutput <- RxSqlServerData(
  connectionString = connStr,
  table = "taxiScoreOutput"  )

    • Om dit voorbeeld eenvoudiger te maken, is de invoer voor het logistieke regressiemodel dezelfde functiegegevensbron (sql_feature_ds) die u hebt gebruikt om het model te trainen. Normaal gesproken hebt u mogelijk een aantal nieuwe gegevens om mee te scoren, of hebt u mogelijk enkele gegevens voor het testen versus trainen gereserveerd.

    • De voorspellingsresultaten worden opgeslagen in de tabel taxiscoreOutput. U ziet dat het schema voor deze tabel niet is gedefinieerd wanneer u deze maakt met rxSqlServerData. Het schema wordt verkregen uit de rxPredict-uitvoer.

    • Als u de tabel wilt maken waarin de voorspelde waarden worden opgeslagen, moet de SQL-aanmelding met de gegevensfunctie rxSqlServer DDL-bevoegdheden hebben in de database. Als het aanmeldaccount geen tabellen kan maken, mislukt de instructie.

  2. Roep de rxPredict-functie aan om resultaten te genereren.

    rxPredict(modelObject = logitObj,
    data = featureDataSource,
    outData = scoredOutput,
    predVarNames = "Score",
    type = "response",
    writeModelVars = TRUE, overwrite = TRUE)

    Als de instructie slaagt, zou het uitvoeren ervan enige tijd in beslag moeten nemen. Wanneer u klaar bent, kunt u SQL Server Management Studio openen en controleren of de tabel is gemaakt en of deze de kolom Score en andere verwachte uitvoer bevat.

Nauwkeurigheid van plotmodel

Om een idee te krijgen van de nauwkeurigheid van het model, kunt u de rxRoc-functie gebruiken om de ontvanger bedrijfscurve te tekenen. Omdat rxRoc een van de nieuwe functies is die worden geleverd door het RevoScaleR-pakket dat externe rekencontexten ondersteunt, hebt u twee opties:

  • U kunt de rxRoc-functie gebruiken om de plot uit te voeren in de externe rekencontext en vervolgens de plot terug te sturen naar uw lokale client.

  • U kunt de gegevens ook importeren op uw R-clientcomputer en andere R-plottingfuncties gebruiken om de prestatiegrafiek te maken.

In deze sectie gaat u experimenteren met beide technieken.

Een plot uitvoeren in de externe rekencontext (SQL Server)

  1. Roep de functie rxRoc aan en geef de eerder gedefinieerde gegevens op als invoer.

    scoredOutput = rxImport(scoredOutput);
rxRoc(actualVarName= "tipped", predVarNames = "Score", scoredOutput);

    Met deze aanroep worden de waarden geretourneerd die worden gebruikt bij het berekenen van de ROC-grafiek. De labelkolom is tipped en bevat de werkelijke resultaten die u probeert te voorspellen, terwijl de kolom Score de voorspelling bevat.

  2. Als u de grafiek daadwerkelijk wilt uitzetten, kunt u het ROC-object opslaan en vervolgens tekenen met de tekenfunctie. De grafiek wordt gemaakt in de externe rekencontext en geretourneerd naar uw R-omgeving.

    scoredOutput = rxImport(scoredOutput);
rocObjectOut <- rxRoc(actualVarName= "tipped", predVarNames = "Score", scoredOutput);
plot(rocObjectOut);

    Bekijk de grafiek door het R-grafische apparaat te openen of door op het venster Plot in RStudio te klikken.

    ROC-plot voor het model

Maak de plots in de lokale rekencontext met behulp van gegevens uit SQL Server

U kunt controleren of de rekencontext lokaal is door deze uit te voeren rxGetComputeContext() via de opdrachtprompt. De retourwaarde moet 'RxLocalSeq Compute-context' zijn.

  1. Voor de lokale rekencontext is het proces veel hetzelfde. U gebruikt de functie rxImport om de opgegeven gegevens naar uw lokale R-omgeving te brengen.

    scoredOutput = rxImport(scoredOutput)

  2. Met behulp van de gegevens in het lokale geheugen laadt u het ROCR-pakket en gebruikt u de voorspellingsfunctie van dat pakket om enkele nieuwe voorspellingen te maken.

    library('ROCR');
pred <- prediction(scoredOutput$Score, scoredOutput$tipped);

  3. Genereer een lokale plot op basis van de waarden die zijn opgeslagen in de uitvoervariabele pred.

    acc.perf = performance(pred, measure = 'acc');
plot(acc.perf);
ind = which.max( slot(acc.perf, 'y.values')[[1]] );
acc = slot(acc.perf, 'y.values')[[1]][ind];
cutoff = slot(acc.perf, 'x.values')[[1]][ind];

    modelprestaties plotten met R

Note

Uw grafieken kunnen er anders uitzien, afhankelijk van het aantal gegevenspunten dat u hebt gebruikt.

Het model implementeren

Nadat u een model hebt gemaakt en hebt vastgesteld dat het goed presteert, wilt u het waarschijnlijk implementeren op een site waar gebruikers of personen in uw organisatie het model kunnen gebruiken, of misschien het model regelmatig opnieuw trainen en opnieuw controleren. Dit proces wordt ook wel het operationeel maken van een model genoemd. In SQL Server wordt operationalisering bereikt door R-code in te bedden in een stored procedure. Omdat code zich in de procedure bevindt, kan deze worden aangeroepen vanuit elke toepassing die verbinding kan maken met SQL Server.

Voordat u het model vanuit een externe toepassing kunt aanroepen, moet u het model opslaan in de database die wordt gebruikt voor productie. Getrainde modellen worden opgeslagen in binaire vorm, in één kolom van het type varbinary(max).

Een typische implementatiewerkstroom bestaat uit de volgende stappen:

  1. Het model serialiseren in een hexadecimale tekenreeks
  2. Het geserialiseerde object verzenden naar de database
  3. Het model opslaan in een varbinary(max)-kolom

In deze sectie leert u hoe u een opgeslagen procedure gebruikt om het model te behouden en beschikbaar te maken voor voorspellingen. De opgeslagen procedure die in deze sectie wordt gebruikt, is PersistModel. De definitie van PersistModel bevindt zich in vereisten.

  1. Ga terug naar uw lokale R-omgeving als u deze nog niet gebruikt, serialiseer het model en sla het op in een variabele.

    rxSetComputeContext("local");
modelbin <- serialize(logitObj, NULL);
modelbinstr=paste(modelbin, collapse="");

  2. Open een ODBC-verbinding met RODBC. U kunt de aanroep naar RODBC weglaten als u het pakket al hebt geladen.

    library(RODBC);
conn <- odbcDriverConnect(connStr);

  3. Roep de opgeslagen procedure PersistentModel aan op SQL Server om het geserialiseerde object naar de database te verzenden en de binaire weergave van het model op te slaan in een kolom.

    q <- paste("EXEC PersistModel @m='", modelbinstr,"'", sep="");
sqlQuery (conn, q);

  4. Gebruik Management Studio om te controleren of het model bestaat. Klik in Objectverkenner met de rechtermuisknop op de nyc_taxi_models tabel en klik op Bovenste 1000 rijen selecteren. In Resultaten ziet u een binaire weergave in de kolom modellen .

Voor het opslaan van een model in een tabel is alleen een INSERT instructie vereist. Het is echter vaak eenvoudiger wanneer ze zijn verpakt in een opgeslagen procedure, zoals PersistModel.

Volgende stappen 

In de volgende en laatste les leert u hoe u scoren uitvoert op het opgeslagen model met behulp van Transact-SQL.

Het R-model implementeren en gebruiken in SQL

  • Last updated on