Rychlý start: Vytvoření a určení skóre prediktivního modelu v R pomocí strojového učení SQL

Platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL Managed Instance

V tomto rychlém startu vytvoříte a vytrénujete prediktivní model pomocí jazyka T. Model uložíte do tabulky v instanci SQL Serveru a pak použijete model k predikci hodnot z nových dat pomocí služeb SQL Server Machine Learning Services nebo clusterů s velkými objemy dat.

Vytvoříte a spustíte dvě uložené procedury spuštěné v SQL. První používá datovou sadu mtcars , která je součástí jazyka R, a generuje jednoduchý generalizovaný lineární model (GLM), který předpovídá pravděpodobnost, že vozidlo bylo vybaveno ruční převodovkou. Druhým postupem je bodování – volá model vygenerovaný v prvním postupu k výstupu sady predikcí na základě nových dat. Umístěním kódu R do uložené procedury SQL jsou operace obsaženy v SQL, jsou opakovaně použitelné a mohou být volány jinými uloženými procedurami a klientskými aplikacemi.

Návod

Pokud potřebujete aktualizovatel lineárních modelů, vyzkoušejte tento kurz, který popisuje proces přizpůsobení modelu pomocí rxLinMod: Přizpůsobení lineárních modelů

Po dokončení tohoto rychlého startu se naučíte:

• Jak vložit kód R do uložené procedury
• Jak předat vstupy do kódu prostřednictvím parametrů uložené procedury
• Jak se uložené procedury používají k zprovoznění modelů

Požadavky

Ke spuštění tohoto rychlého startu potřebujete následující požadavky.

• SQL Server Machine Learning Services. Informace o instalaci služby Machine Learning Services najdete v průvodci instalací systému Windows nebo v průvodci instalací pro Linux. Službu Machine Learning Services můžete také povolit v clusterech s velkými objemy dat SQL Serveru.

• Nástroj pro spouštění dotazů SQL, které obsahují skripty jazyka R. V tomto rychlém startu se používá Azure Data Studio.

Vytvoření modelu

Pokud chcete vytvořit model, vytvoříte zdrojová data pro trénování, vytvoříte model a vytrénujete ho pomocí dat a pak ho uložíte do databáze, kde se dají použít k vygenerování předpovědí s novými daty.

Vytvoření zdrojových dat

1. Otevřete Azure Data Studio, připojte se k vaší instanci a otevřete nové okno dotazu.

2. Vytvořte tabulku pro uložení trénovacích dat.

   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. Vložte data z předdefinované datové sady 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';
   

   Návod

   Součástí modulu runtime R je mnoho datových sad, malých a velkých. Pokud chcete získat seznam datových sad nainstalovaných s jazykem R, zadejte library(help="datasets") z příkazového řádku jazyka R.

Vytvoření a trénování modelu

Data o rychlosti auta obsahují dva sloupce, a to jak číselné: koňské síly (hp) a hmotnosti (wt). Z těchto dat vytvoříte generalizovaný lineární model (GLM), který odhaduje pravděpodobnost, že vozidlo bylo vybaveno ruční převodovkou.

Pokud chcete vytvořit model, definujete vzorec uvnitř kódu R a předáte data jako vstupní parametr.

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

• Prvním argumentem glm je parametr vzorce , který definuje am jako závislý na hp + wt.
• Vstupní data jsou uložena v proměnné MTCarsData, která je naplněna dotazem SQL. Pokud vstupním datům nepřiřazujete konkrétní název, bude výchozí název proměnné InputDataSet.

Uložení modelu v databázi

Pak model uložte do databáze, abyste ho mohli použít k predikci nebo ho znovu natrénovat.

1. Vytvořte tabulku pro uložení modelu.

   Výstupem balíčku R, který vytvoří model, je obvykle binární objekt. Proto tabulka, do které model ukládáte, musí obsahovat sloupec typu varbinary(max ).

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

2. Spuštěním následujícího příkazu Transact-SQL volejte uloženou proceduru, vygenerujte model a uložte ho do tabulky, kterou jste vytvořili.

   INSERT INTO GLM_models(model)
   EXEC generate_GLM;
   

   Návod

   Pokud tento kód spustíte podruhé, zobrazí se tato chyba: Porušení omezení PRIMÁRNÍHO KLÍČE... Do objektu dbo.stopping_distance_models nelze vložit duplicitní klíč. Jednou z možností, jak se této chybě vyhnout, je aktualizovat název každého nového modelu. Můžete například změnit název na něco popisnějšího a zahrnout typ modelu, den, kdy jste ho vytvořili, atd.

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

Skórovat nová data pomocí vycvičeného modelu

Bodování je termín používaný v datové vědě, který znamená generování předpovědí, pravděpodobností nebo jiných hodnot založených na nových datech předávaných do vytrénovaného modelu. K určení skóre předpovědí proti novým datům použijete model, který jste vytvořili v předchozí části.

Vytvoření tabulky nových dat

Nejprve vytvořte tabulku s novými daty.

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

Predikce ručního přenosu

Pokud chcete získat predikce na základě modelu, napište skript SQL, který provede následující akce:

1. Získá požadovaný model.
2. Získá nová vstupní data.
3. Volá funkci predikce jazyka R, která je kompatibilní s tímto modelem.

V průběhu času může tabulka obsahovat více modelů R, všechny vytvořené pomocí různých parametrů nebo algoritmů nebo natrénované na různých podmnožinách dat. V tomto příkladu použijeme model s názvem 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)));

Výše uvedený skript provede následující kroky:

• Pomocí příkazu SELECT získáte jeden model z tabulky a předejte ho jako vstupní parametr.

• Po načtení modelu z tabulky zavolejte na model funkci unserialize.

• predict Použijte funkci s příslušnými argumenty pro model a zadejte nová vstupní data.

Poznámka:

V příkladu se str funkce přidá během testovací fáze, aby se zkontrolovalo schéma dat vrácených z jazyka R. Příkaz můžete později odebrat.

Názvy sloupců použité ve skriptu jazyka R nemusí být nutně předány výstupu uložené procedury. Tady se klauzule WITH RESULTS používá k definování některých nových názvů sloupců.

Results

Příkaz PREDICT (Transact-SQL) je také možné použít k vygenerování předpovězené hodnoty nebo skóre na základě uloženého modelu.

Další kroky

Další informace o kurzech jazyka R s využitím strojového učení SQL najdete tady:

• Kurzy jazyka R