Szybki start: tworzenie i ocenianie modelu predykcyjnego w języku R przy użyciu uczenia maszynowego SQL

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje usługi Azure SQL Managed Instance

W tym przewodniku szybkiego startu utworzysz i przygotujesz model predykcyjny przy użyciu T. Zapiszesz model w tabeli w wystąpieniu programu SQL Server, a następnie użyjesz modelu do przewidywania wartości z nowych danych przy użyciu usług SQL Server Machine Learning Services lub Big Data Clusters.

Utworzysz i wykonasz dwie procedury składowane w SQL. Pierwszy używa zestawu danych mtcars dołączonego do języka R i generuje prosty uogólniony model liniowy (GLM), który przewiduje prawdopodobieństwo, że pojazd został wyposażony w ręczną skrzynię biegów. Druga procedura dotyczy oceniania — wywołuje model wygenerowany w pierwszej procedurze, aby wygenerować zestaw przewidywań na podstawie nowych danych. Umieszczając kod języka R w procedurze składowanej SQL, operacje są zawarte w języku SQL, są wielokrotnego użytku i mogą być wywoływane przez inne procedury składowane i aplikacje klienckie.

Wskazówka

Jeśli potrzebujesz odświeżenia wiedzy o modelach liniowych, wypróbuj ten samouczek, który opisuje proces dopasowywania modelu przy użyciu rxLinMod: Dopasowywanie modeli liniowych

Wykonując czynności z tego przewodnika Szybki start, dowiesz się, jak wykonywać następujące czynności:

• Jak osadzić kod języka R w procedurze składowanej
• Jak przekazać dane wejściowe do kodu za pośrednictwem danych wejściowych w procedurze składowanej
• Jak wykorzystuje się procedury składowane do operacjonalizacji modeli

Wymagania wstępne

Aby uruchomić szybki start, potrzebne są następujące prerekwizyty.

• SQL Server Machine Learning Services. Aby zainstalować usługi Machine Learning Services, zobacz przewodnik instalacji systemu Windows lub przewodnik instalacji systemu Linux. Usługi Machine Learning Services można również włączyć w klastrach danych big data programu SQL Server.

• Narzędzie do uruchamiania zapytań SQL zawierających skrypty języka R. W tym przewodniku Szybki start jest używany program Azure Data Studio.

Tworzenie modelu

Aby utworzyć model, utworzysz dane źródłowe na potrzeby trenowania, utworzysz model i wytrenujesz go przy użyciu danych, a następnie zapiszesz model w bazie danych, w której może służyć do generowania przewidywań przy użyciu nowych danych.

Tworzenie danych źródłowych

1. Otwórz Azure Data Studio, połącz się z instancją i otwórz nowe okno zapytania.

2. Utwórz tabelę w celu zapisania danych treningowych.

   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. Wstaw dane z wbudowanego zestawu danych 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';
   

   Wskazówka

   Wiele zestawów danych, małych i dużych, jest dołączonych do środowiska uruchomieniowego języka R. Aby uzyskać listę zestawów danych zainstalowanych z językiem R, wpisz library(help="datasets") polecenie w wierszu polecenia języka R.

Tworzenie i trenowanie modelu

Dane prędkości samochodu zawierają dwie kolumny, zarówno numeryczne: koni mechanicznych (hp) i wagi (wt). Na podstawie tych danych utworzysz uogólniony model liniowy (GLM), który szacuje prawdopodobieństwo, że pojazd został wyposażony w ręczną skrzynię biegów.

Aby skompilować model, należy zdefiniować formułę wewnątrz kodu języka R i przekazać dane jako parametr wejściowy.

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

• Pierwszym argumentem do glm jest parametr formuły, który definiuje am jako zależny od hp + wt.
• Dane wejściowe są przechowywane w zmiennej MTCarsData, która jest wypełniana przez zapytanie SQL. Jeśli nie przypiszesz określonej nazwy do danych wejściowych, domyślną nazwą zmiennej będzie InputDataSet.

Przechowywanie modelu w bazie danych

Następnie zapisz model w bazie danych, aby można było go użyć do przewidywania lub ponownego trenowania.

1. Utwórz tabelę do przechowywania modelu.

   Dane wyjściowe pakietu języka R tworzącego model są zwykle obiektem binarnym. W związku z tym tabela, w której jest przechowywany model, musi zawierać kolumnę typu varbinary(max).

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

2. Uruchom następującą instrukcję Transact-SQL, aby wywołać procedurę składowaną, wygenerować model i zapisać go w utworzonej tabeli.

   INSERT INTO GLM_models(model)
   EXEC generate_GLM;
   

   Wskazówka

   Jeśli uruchomisz ten kod po raz drugi, zostanie wyświetlony następujący błąd: "Naruszenie ograniczenia KLUCZA PODSTAWOWEgo... Nie można wstawić zduplikowanego klucza w dbo.stopping_distance_models obiektu". Jedną z opcji uniknięcia tego błędu jest zaktualizowanie nazwy dla każdego nowego modelu. Możesz na przykład zmienić nazwę na bardziej opisową i uwzględnić typ modelu, dzień jego utworzenia itd.

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

Ocenianie nowych danych przy użyciu wytrenowanego modelu

Ocenianie to termin używany w nauce o danych, który oznacza generowanie przewidywań, prawdopodobieństwa lub innych wartości na podstawie nowych danych, które są przekazywane do wytrenowanego modelu. Użyjesz modelu utworzonego w poprzedniej sekcji, aby ocenić przewidywania pod kątem nowych danych.

Tworzenie tabeli nowych danych

Najpierw utwórz tabelę z nowymi danymi.

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

Przewidywanie ręcznej transmisji

Aby uzyskać przewidywania na podstawie modelu, napisz skrypt SQL, który wykonuje następujące czynności:

1. Pobiera żądany model
2. Pobiera nowe dane wejściowe
3. Wywołuje funkcję przewidywania języka R zgodną z tym modelem

W czasie tabela może zawierać wiele modeli języka R, wszystkie utworzone przy użyciu różnych parametrów lub algorytmów albo wytrenowane na różnych podzestawach danych. W tym przykładzie użyjemy modelu o nazwie 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)));

Powyższy skrypt wykonuje następujące kroki:

• Użyj instrukcji SELECT, aby pobrać pojedynczy model z tabeli i przekazać go jako parametr wejściowy.

• Po pobraniu modelu z tabeli wywołaj funkcję unserialize na modelu.

• predict Zastosuj funkcję z odpowiednimi argumentami do modelu i podaj nowe dane wejściowe.

Uwaga / Notatka

W tym przykładzie str funkcja jest dodawana podczas fazy testowania, aby sprawdzić schemat danych zwracanych z języka R. Instrukcję można usunąć później.

Nazwy kolumn używane w skrypce języka R nie muszą być przekazywane do danych wyjściowych procedury składowanej. Tutaj klauzula WITH RESULTS służy do definiowania niektórych nowych nazw kolumn.

Results

Można również użyć instrukcji PREDICT (Transact-SQL), aby wygenerować przewidywaną wartość lub ocenę na podstawie przechowywanego modelu.

Dalsze kroki

Aby uzyskać więcej informacji na temat samouczków dotyczących języka R z uczeniem maszynowym SQL, zobacz:

• Samouczki języka R