Guia de início rápido: criar e pontuar um modelo preditivo em R com aprendizado de máquina SQL

Aplica-se a: SQL Server 2016 (13.x) e posterior Instância Gerenciada SQL do Azure

Você criará e executará dois procedimentos armazenados em execução em SQL. O primeiro usa o conjunto de dados de mtcars incluído com R e gera um modelo linear generalizado simples (GLM) que prevê a probabilidade de um veículo ter sido equipado com uma transmissão manual. O segundo procedimento é para pontuação - chama o modelo gerado no primeiro procedimento para produzir um conjunto de previsões com base em novos dados. Ao colocar o código R em um procedimento armazenado SQL, as operações estão contidas no SQL, são reutilizáveis e podem ser chamadas por outros procedimentos armazenados e aplicativos cliente.

Ao concluir este guia de início rápido, você aprenderá:

Pré-requisitos

Você precisa dos seguintes pré-requisitos para executar este início rápido.

• Uma ferramenta para executar consultas SQL que contêm scripts R. Este procedimento de início rápido usa Azure Data Studio.

Criar o modelo

Para criar o modelo, você criará dados de origem para treinamento, criará o modelo e treiná-lo usando os dados e, em seguida, armazenará o modelo em um banco de dados onde poderá ser usado para gerar previsões com novos dados.

Criar os dados de origem

1. Abra o Azure Data Studio, conecte-se à sua instância e abra uma nova janela de consulta.

2. Crie uma tabela para salvar os dados de treinamento.

   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. Insira os dados do conjunto de dados interno 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';
   

   Dica

   Muitos conjuntos de dados, pequenos e grandes, estão incluídos no tempo de execução do R. Para obter uma lista de conjuntos de dados instalados com R, digite library(help="datasets") em um prompt de comando R.

Criar e treinar o modelo

Os dados de velocidade do carro contêm duas colunas, ambas numéricas: potência (hp) e peso (wt). A partir desses dados, você criará um modelo linear generalizado (GLM) que estima a probabilidade de um veículo ter sido equipado com uma transmissão manual.

Para criar o modelo, defina a fórmula dentro do código R e passe os dados como um parâmetro de entrada.

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

• O primeiro argumento a glm é a fórmula parâmetro, que define am como dependente de hp + wt.
• Os dados de entrada são armazenados na variável MTCarsData, que é preenchida pela consulta SQL. Se você não atribuir um nome específico aos dados de entrada, o nome da variável padrão será InputDataSet.

Armazenar o modelo no banco de dados

Em seguida, armazene o modelo em um banco de dados para que você possa usá-lo para previsão ou retreiná-lo.

1. Crie uma tabela para armazenar o modelo.

   A saída de um pacote R que cria um modelo é geralmente um objeto binário. Portanto, a tabela onde você armazena o modelo deve fornecer uma coluna de tipo de varbinary(max).

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

2. Execute a seguinte instrução Transact-SQL para chamar o procedimento armazenado, gerar o modelo e salvá-lo na tabela que você criou.

   INSERT INTO GLM_models(model)
   EXEC generate_GLM;
   

   Dica

   Se você executar esse código uma segunda vez, você receberá este erro: "Violação da restrição de CHAVE PRIMÁRIA... Não é possível inserir chave duplicada no objeto dbo.stopping_distance_models". Uma opção para evitar esse erro é atualizar o nome de cada novo modelo. Por exemplo, você pode alterar o nome para algo mais descritivo e incluir o tipo de modelo, o dia em que o criou e assim por diante.

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

Pontuar novos dados usando o modelo treinado

Scoring é um termo usado em ciência de dados para significar gerar previsões, probabilidades ou outros valores com base em novos dados alimentados num modelo treinado. Você usará o modelo criado na seção anterior para pontuar previsões em relação a novos dados.

Criar uma tabela de novos dados

Primeiro, crie uma tabela com novos dados.

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

Prever a transmissão manual

Para obter previsões com base no seu modelo, escreva um script SQL que faça o seguinte:

1. Consegue o modelo que quer
2. Obtém os novos dados de entrada
3. Chama uma função de previsão R que é compatível com esse modelo

Com o tempo, a tabela pode conter vários modelos R, todos construídos usando diferentes parâmetros ou algoritmos, ou treinados em diferentes subconjuntos de dados. Neste exemplo, usaremos o modelo chamado 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)));

O script acima executa as seguintes etapas:

• Use uma instrução SELECT para obter um único modelo da tabela e passá-lo como um parâmetro de entrada.

• Depois de recuperar o modelo da tabela, chame a função unserialize no modelo.

• Aplique a função predict com argumentos apropriados ao modelo e forneça os novos dados de entrada.

Resultados

Também é possível usar a instrução PREDICT (Transact-SQL) para gerar um valor ou pontuação prevista com base em um modelo armazenado.

Próximos passos

Para obter mais informações sobre tutoriais para R com aprendizado de máquina SQL, consulte:

• R tutoriais