Dela via

Skapa en R-modell och spara till SQL Server (genomgång)

gäller för: SQL Server 2016 (13.x) och senare versioner

I det här steget lär du dig hur du skapar en maskininlärningsmodell och sparar modellen i SQL Server. Genom att spara en modell kan du anropa den direkt från Transact-SQL kod med hjälp av den systemlagrade proceduren, sp_execute_external_script eller funktionen PREDICT (T-SQL).

Förutsättningar

Det här steget förutsätter en pågående R-session baserat på tidigare steg i den här genomgången. Den använder anslutningssträngar och datakällans objekt som skapats i dessa steg. Följande verktyg och paket används för att köra skriptet.

  • Rgui.exe för att köra R-kommandon
  • Management Studio för att köra T-SQL
  • ROCR-paket
  • RODBC-paket

Skapa en lagrad procedur för att spara modeller

Det här steget använder en lagrad procedur för att spara en tränad modell på SQL Server. Om du skapar en lagrad procedur för att utföra den här åtgärden blir uppgiften enklare.

Kör följande T-SQL-kod i ett frågefönster i Management Studio för att skapa den lagrade proceduren.

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

Not

Om du får ett felmeddelande ska du kontrollera att ditt konto har behörighet att skapa objekt. Du kan bevilja explicita behörigheter för att skapa objekt genom att köra en T-SQL-instruktion som den här: exec sp_addrolemember 'db_owner', '<user_name>'.

Skapa en klassificeringsmodell med rxLogit

Modellen är en binär klassificerare som förutsäger om taxichauffören sannolikt kommer att få ett tips på en viss resa eller inte. Du använder datakällan som du skapade i föregående lektion för att träna tipsklassificeraren med hjälp av logistisk regression.

  1. Anropa funktionen rxLogit som ingår i RevoScaleR--paketet för att skapa en logistisk regressionsmodell.

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

    Anropet som skapar modellen omges av funktionen system.time. På så sätt kan du få den tid som krävs för att skapa modellen.

  2. När du har skapat modellen kan du inspektera den med hjälp av funktionen summary och visa koefficienterna.

    summary(logitObj);

    resultat

     *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*

Använda modellen för logistisk regression för bedömning

Nu när modellen har skapats kan du använda den för att förutsäga om föraren sannolikt kommer att få dricks på en viss körning eller inte.

  1. Använd först funktionen RxSqlServerData för att definiera ett datakällobjekt för att lagra poängresultatet.

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

    • För att göra det här exemplet enklare är indata till den logistiska regressionsmodellen samma funktionsdatakälla (sql_feature_ds) som du använde för att träna modellen. Mer normalt kan du ha några nya data att poängsätta med, eller så kanske du har avsatt vissa data för testning jämfört med träning.

    • Förutsägelseresultaten sparas i tabellen taxiscoreOutput. Observera att schemat för den här tabellen inte definieras när du skapar det med rxSqlServerData. Schemat hämtas från rxPredict-utdata.

    • Om du vill skapa tabellen som lagrar de förutsagda värdena måste SQL-inloggningen som kör datafunktionen rxSqlServer ha DDL-behörigheter i databasen. Om inloggningen inte kan skapa tabeller misslyckas instruktionen.

  2. Anropa funktionen rxPredict för att generera resultat.

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

    Om uttalandet lyckas bör det ta lite tid att köra. När du är klar kan du öppna SQL Server Management Studio och kontrollera att tabellen har skapats och att den innehåller kolumnen Poäng och andra förväntade utdata.

Precision för ritningsmodell

För att få en uppfattning om modellens noggrannhet kan du använda funktionen rxRoc för att rita mottagaroperativkurvan. Eftersom rxRoc är en av de nya funktionerna som tillhandahålls av RevoScaleR-paketet som stöder fjärrberäkningskontexter, har du två alternativ:

  • Du kan använda rxRoc-funktionen för att köra diagrammet i fjärrberäkningskontexten och sedan returnera diagrammet till din lokala klient.

  • Du kan också importera data till R-klientdatorn och använda andra R-plottningsfunktioner för att skapa prestandadiagrammet.

I det här avsnittet experimenterar du med båda teknikerna.

Köra ett diagram i fjärrberäkningskontexten (SQL Server)

  1. Anropa funktionen rxRoc och ange de data som definierades tidigare som indata.

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

    Det här anropet returnerar de värden som används för att beräkna ROC-diagrammet. Etikettkolumnen tippas, som har de faktiska resultat som du försöker förutsäga, medan kolumnen Poäng har förutsägelsen.

  2. Om du vill rita diagrammet kan du spara ROC-objektet och sedan rita det med ritningsfunktionen. Diagrammet skapas i fjärrberäkningskontexten och returneras till R-miljön.

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

    Visa diagrammet genom att öppna R-grafikenheten eller genom att klicka på fönstret Rita i RStudio.

    ROC-diagram för modellen

Skapa diagram i den lokala beräkningskontexten med hjälp av data från SQL Server

Du kan kontrollera att beräkningskontexten är lokal genom att köra rxGetComputeContext() i kommandotolken. Returvärdet ska vara "RxLocalSeq Compute Context".

  1. För den lokala beräkningskontexten är processen ungefär densamma. Du använder funktionen rxImportera för att föra in angivna data i din lokala R-miljö.

    scoredOutput = rxImport(scoredOutput)

  2. Med hjälp av data i det lokala minnet läser du in ROCR--paketet och använder förutsägelsefunktionen från paketet för att skapa några nya förutsägelser.

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

  3. Generera ett lokalt diagram baserat på de värden som lagras i utdatavariabeln 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];

    ritning av modellprestanda med R

Anteckning

Dina diagram kan se annorlunda ut än dessa, beroende på hur många datapunkter du använde.

Distribuera modellen

När du har skapat en modell och fastställt att den fungerar bra vill du förmodligen distribuera den till en webbplats där användare eller personer i din organisation kan använda modellen, eller kanske träna om och omkalibrera modellen regelbundet. Den här processen kallas ibland operationalisering av en modell. I SQL Server uppnås operationalisering genom att bädda in R-kod i en lagrad procedur. Eftersom koden finns i proceduren kan den anropas från alla program som kan ansluta till SQL Server.

Innan du kan anropa modellen från ett externt program måste du spara modellen i databasen som används för produktion. Tränade modeller lagras i binär form i en enda kolumn av typen varbinary(max).

Ett typiskt distributionsarbetsflöde består av följande steg:

  1. Serialisera modellen till en hexadecimal sträng
  2. Överföra det serialiserade objektet till databasen
  3. Spara modellen i en kolumn med "varbinary(max)"

I det här avsnittet lär du dig hur du använder en lagrad procedur för att bevara modellen och göra den tillgänglig för förutsägelser. Den lagrade proceduren som används i det här avsnittet är PersistModel. Definitionen av PersistModel finns i Krav.

  1. Växla tillbaka till din lokala R-miljö om du inte redan använder den, serialisera modellen och spara den i en variabel.

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

  2. Öppna en ODBC-anslutning med RODBC-. Du kan utelämna anropet till RODBC om du redan har installerat och aktiverat paketet.

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

  3. Anropa den lagrade proceduren PersistModel på SQL Server för att överföra det serialiserade objektet till databasen och lagra modellens binära representation i en kolumn.

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

  4. Använd Management Studio för att kontrollera att modellen finns. Högerklicka på tabellen nyc_taxi_models i Object Explorer och klicka på Välj de 1 000 översta raderna. I Resultat bör du se en binär representation i kolumnen modeller.

För att spara en modell i en tabell krävs bara en INSERT-instruktion. Det är dock ofta enklare när det omsluts i en lagrad procedur, till exempel PersistModel.

Nästa steg

I nästa och sista lektion får du lära dig hur du utför poängsättning mot den sparade modellen med Hjälp av Transact-SQL.

Distribuera R-modellen och använd i SQL

  • Last updated on