Partilhar via

Tutorial: Criar modelos baseados em partições no R no SQL Server

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores

No SQL Server 2019, a modelação baseada em partições é a capacidade de criar e treinar modelos sobre dados particionados. Para dados estratificados que naturalmente se segmentam num dado esquema de classificação – como regiões geográficas, data e hora, idade ou género – pode executar scripts sobre todo o conjunto de dados, com a capacidade de modelar, treinar e pontuar sobre partições que permanecem intactas em todas estas operações.

A modelação baseada em partições é ativada através de dois novos parâmetros em sp_execute_external_script:

  • input_data_1_partition_by_columns, que especifica uma coluna pela qual particionar.
  • input_data_1_order_by_columns especifica por que colunas ordenar.

Neste tutorial, aprende modelação baseada em partições usando os clássicos dados de amostra de táxi de Nova Iorque e o script R. A coluna de partição é o método de pagamento.

  • As partições baseiam-se nos tipos de pagamento (5).
  • Crie e treine modelos em cada partição e armazene os objetos na base de dados.
  • Preveja a probabilidade dos resultados das dicas em cada modelo de partição, usando dados amostrais reservados para esse fim.

Pré-requisitos

Para completar este tutorial, deve ter o seguinte:

  • Recursos suficientes do sistema. O conjunto de dados é grande e as operações de treino são intensivas em recursos. Se possível, use um sistema com pelo menos 8 GB de RAM. Em alternativa, pode usar conjuntos de dados mais pequenos para contornar as restrições de recursos. As instruções para reduzir o conjunto de dados estão inseridas no texto.

  • Uma ferramenta para execução de consultas T-SQL, como o SQL Server Management Studio (SSMS).

  • NYCTaxi_Sample.bak, que pode descarregar e restaurar para a sua instância local do SQL Server. O tamanho do ficheiro é de aproximadamente 90 MB.

  • Instância do motor de base de dados SQL Server 2019, com Serviços de Aprendizagem Automática e integração R.

  • O tutorial utiliza uma ligação loopback ao SQL Server a partir de um script R sobre ODBC. Por isso, precisa de criar um login para o SQLRUserGroup.

  • Verifique a disponibilidade dos pacotes R devolvendo uma lista bem formatada de todos os pacotes R atualmente instalados na sua instância do motor de base de dados:

EXECUTE sp_execute_external_script
  @language=N'R',
  @script = N'str(OutputDataSet);
  packagematrix <- installed.packages();
  Name <- packagematrix[,1];
  Version <- packagematrix[,3];
  OutputDataSet <- data.frame(Name, Version);',
  @input_data_1 = N''
WITH RESULT SETS ((PackageName nvarchar(250), PackageVersion nvarchar(max) ))

Conectar-se ao banco de dados

Inicia o SSMS e liga-te à instância do motor da base de dados. No Object Explorer, verifique a existência da base de dados NYCTaxi_Sample.

Criar a função CalculateDistance

A base de dados de demonstração vem com uma função escalar para calcular distâncias, mas o nosso procedimento armazenado funciona melhor com uma função de valores de tabela. Execute o script seguinte para criar a CalculateDistance função usada na etapa de treino mais tarde.

Para confirmar que a função foi criada, no Explorador de Objetos, verifique o \Programmability\Functions\Table-valued Functions na base de dados NYCTaxi_Sample.

USE NYCTaxi_sample
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CalculateDistance] (
    @Lat1 FLOAT
    ,@Long1 FLOAT
    ,@Lat2 FLOAT
    ,@Long2 FLOAT
    )
    -- User-defined function calculates the direct distance between two geographical coordinates.
RETURNS TABLE
AS
RETURN

SELECT COALESCE(3958.75 * ATAN(SQRT(1 - POWER(t.distance, 2)) / nullif(t.distance, 0)), 0) AS direct_distance
FROM (
    VALUES (CAST((SIN(@Lat1 / 57.2958) * SIN(@Lat2 / 57.2958)) + (COS(@Lat1 / 57.2958) * COS(@Lat2 / 57.2958) * COS((@Long2 / 57.2958) - (@Long1 / 57.2958))) AS DECIMAL(28, 10)))
    ) AS t(distance)
GO

Defina um procedimento para criar e treinar modelos por partição

Este tutorial envolve o script R num procedimento armazenado. Neste passo, cria-se um procedimento armazenado que utiliza R para criar um conjunto de dados de entrada, constrói um modelo de classificação para prever resultados de dicas e depois armazena o modelo na base de dados.

Entre as entradas de parâmetros usadas por este script, verá input_data_1_partition_by_columns e input_data_1_order_by_columns. Recorde-se que estes parâmetros são o mecanismo pelo qual ocorre a modelação particionada. Os parâmetros são passados como entradas para sp_execute_external_script para processar partições, com o script externo a executar-se uma vez para cada partição.

Para este procedimento armazenado, use paralelismo para um tempo de conclusão mais rápido.

Depois de executares este script, no Explorador de Objetos, deves ver train_rxLogIt_per_partition em \Programmability\Stored Procedures na base de dados NYCTaxi_Sample. Deverá também ver uma nova tabela usada para armazenar modelos: dbo.nyctaxi_models.

USE NYCTaxi_Sample
GO

CREATE
    OR

ALTER PROCEDURE [dbo].[train_rxLogIt_per_partition] (@input_query NVARCHAR(max))
AS
BEGIN
    DECLARE @start DATETIME2 = SYSDATETIME()
        ,@model_generation_duration FLOAT
        ,@model VARBINARY(max)
        ,@instance_name NVARCHAR(100) = @@SERVERNAME
        ,@database_name NVARCHAR(128) = db_name();

    EXEC sp_execute_external_script @language = N'R'
        ,@script =
        N'
    
    # Make sure InputDataSet is not empty. In parallel mode, if one thread gets zero data, an error occurs
    if (nrow(InputDataSet) > 0) {
    # Define the connection string
    connStr <- paste("Driver=SQL Server;Server=", instance_name, ";Database=", database_name, ";Trusted_Connection=true;", sep="");
    
    # build classification model to predict a tip outcome
    duration <- system.time(logitObj <- rxLogit(tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance, data = InputDataSet))[3];

    # First, serialize a model to and put it into a database table
    modelbin <- as.raw(serialize(logitObj, NULL));

    # Create the data source. To reduce data size, add rowsPerRead=500000 to cut the dataset by half.
    ds <- RxOdbcData(table="ml_models", connectionString=connStr);

    # Store the model in the database
    model_name <- paste0("nyctaxi.", InputDataSet[1,]$payment_type);
    
    rxWriteObject(ds, model_name, modelbin, version = "v1",
    keyName = "model_name", valueName = "model_object", versionName = "model_version", overwrite = TRUE, serialize = FALSE);
    }
    
    '
        ,@input_data_1 = @input_query
        ,@input_data_1_partition_by_columns = N'payment_type'
        ,@input_data_1_order_by_columns = N'passenger_count'
        ,@parallel = 1
        ,@params = N'@instance_name nvarchar(100), @database_name nvarchar(128)'
        ,@instance_name = @instance_name
        ,@database_name = @database_name
    WITH RESULT SETS NONE
END;
GO

Execução paralela

Note que as entradas de sp_execute_external_script incluem @parallel=1, usado para permitir o processamento paralelo. Em contraste com versões anteriores, a partir do SQL Server 2019, a definição @parallel=1 fornece uma dica mais forte para o otimizador de consultas, tornando a execução paralela um resultado muito mais provável.

Por defeito, o otimizador de consultas tende a operar em @parallel=1 tabelas com mais de 256 linhas, mas se conseguires tratar disto explicitamente definindo @parallel=1 como mostrado neste script.

Sugestão

Para cargas de trabalho de treinamento, você pode usar @parallel com qualquer script de treinamento arbitrário, mesmo aqueles que usam algoritmos não-Microsoft-rx. Normalmente, apenas algoritmos RevoScaleR (com o prefixo rx) oferecem paralelismo em cenários de treinamento no SQL Server. Mas com o novo parâmetro, podes paralelizar um script que chama funções, incluindo funções R open-source, não especificamente concebidas com essa capacidade. Isto funciona porque as partições têm afinidade com threads específicos, pelo que todas as operações chamadas num script executam-se por partição, no thread dado.

Executa o procedimento e treina o modelo

Nesta secção, o script treina o modelo que criou e guardou na etapa anterior. Os exemplos abaixo demonstram duas abordagens para treinar o seu modelo: usar um conjunto de dados completo ou um dado parcial.

Espere que este passo demore algum tempo. O treino é computacionalmente intensivo, demorando muitos minutos a ser concluído. Se os recursos do sistema, especialmente a memória, forem insuficientes para a carga, use um subconjunto dos dados. O segundo exemplo fornece a sintaxe.

--Example 1: train on entire dataset
EXEC train_rxLogIt_per_partition N'
SELECT payment_type, tipped, passenger_count, trip_time_in_secs, trip_distance, d.direct_distance
  FROM dbo.nyctaxi_sample CROSS APPLY [CalculateDistance](pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as d
';
GO

--Example 2: Train on 20 percent of the dataset to expedite processing.
EXEC train_rxLogIt_per_partition N'
  SELECT tipped, payment_type, passenger_count, trip_time_in_secs, trip_distance, d.direct_distance
  FROM dbo.nyctaxi_sample TABLESAMPLE (20 PERCENT) REPEATABLE (98074)
  CROSS APPLY [CalculateDistance](pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as d
';
GO

Observação

Se estiveres a correr outras cargas de trabalho, podes acrescentar OPTION(MAXDOP 2) à instrução SELECT se quiseres limitar o processamento de consultas a apenas 2 núcleos.

Ver resultados

O resultado na tabela de modelos deve ser cinco modelos diferentes, baseados em cinco partições segmentadas pelos cinco tipos de pagamento. Os modelos estão na fonte de dados ml_models.

SELECT *
FROM ml_models

Defina um procedimento para prever resultados

Podes usar os mesmos parâmetros para pontuar. O exemplo seguinte contém um script R que irá pontuar usando o modelo correto para a partição que está atualmente a processar.

Como antes, crie um procedimento armazenado para envolver o seu código R.

USE NYCTaxi_Sample
GO

-- Stored procedure that scores per partition.
-- Depending on the partition being processed, a model specific to that partition will be used
CREATE
    OR

ALTER PROCEDURE [dbo].[predict_per_partition]
AS
BEGIN
    DECLARE @predict_duration FLOAT
        ,@instance_name NVARCHAR(100) = @@SERVERNAME
        ,@database_name NVARCHAR(128) = db_name()
        ,@input_query NVARCHAR(max);

    SET @input_query = 'SELECT tipped, passenger_count, trip_time_in_secs, trip_distance, d.direct_distance, payment_type
                          FROM dbo.nyctaxi_sample TABLESAMPLE (1 PERCENT) REPEATABLE (98074)
                          CROSS APPLY [CalculateDistance](pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as d'

    EXEC sp_execute_external_script @language = N'R'
        ,@script =
        N'
    
    if (nrow(InputDataSet) > 0) {

    #Get the partition that is currently being processed
    current_partition <- InputDataSet[1,]$payment_type;

    #Create the SQL query to select the right model
    query_getModel <- paste0("select model_object from ml_models where model_name = ", "''", "nyctaxi.",InputDataSet[1,]$payment_type,"''", ";")
    

    # Define the connection string
    connStr <- paste("Driver=SQL Server;Server=", instance_name, ";Database=", database_name, ";Trusted_Connection=true;", sep="");
        
    #Define data source to use for getting the model
    ds <- RxOdbcData(sqlQuery = query_getModel, connectionString = connStr)

    # Load the model
    modelbin <- rxReadObject(ds, deserialize = FALSE)
    # unserialize model
    logitObj <- unserialize(modelbin);

    # predict tipped or not based on model
    predictions <- rxPredict(logitObj, data = InputDataSet, overwrite = TRUE, type = "response", writeModelVars = TRUE
        , extraVarsToWrite = c("payment_type"));
    OutputDataSet <- predictions
    
    } else {
        OutputDataSet <- data.frame(integer(), InputDataSet[,]);
    }
    '
        ,@input_data_1 = @input_query
        ,@parallel = 1
        ,@input_data_1_partition_by_columns = N'payment_type'
        ,@params = N'@instance_name nvarchar(100), @database_name nvarchar(128)'
        ,@instance_name = @instance_name
        ,@database_name = @database_name
    WITH RESULT SETS((
                tipped_Pred INT
                ,payment_type VARCHAR(5)
                ,tipped INT
                ,passenger_count INT
                ,trip_distance FLOAT
                ,trip_time_in_secs INT
                ,direct_distance FLOAT
                ));
END;
GO

Crie uma tabela para armazenar previsões

CREATE TABLE prediction_results (
    tipped_Pred INT
    ,payment_type VARCHAR(5)
    ,tipped INT
    ,passenger_count INT
    ,trip_distance FLOAT
    ,trip_time_in_secs INT
    ,direct_distance FLOAT
    );

TRUNCATE TABLE prediction_results
GO

Execute o procedimento e guarde as previsões

INSERT INTO prediction_results (
    tipped_Pred
    ,payment_type
    ,tipped
    ,passenger_count
    ,trip_distance
    ,trip_time_in_secs
    ,direct_distance
    )
EXECUTE [predict_per_partition]
GO

Ver previsões

Como as previsões são armazenadas, pode executar uma consulta simples para devolver um conjunto de resultados.

SELECT *
FROM prediction_results;

Próximos passos

  • Neste tutorial, usaste sp_execute_external_script para iterar operações sobre dados particionados. Para uma análise mais detalhada da chamada de scripts externos em procedimentos armazenados e da utilização de funções RevoScaleR, continue com o tutorial seguinte.

Guia para R e SQL Server

  • Last updated on