Modificar o código R/Python para ser executado em instâncias do SQL Server (In-Database)

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

Este artigo fornece orientação de alto nível sobre como modificar o código R ou Python para ser executado como um procedimento armazenado do SQL Server para melhorar o desempenho ao acessar dados SQL.

Quando você move o código R/Python de um IDE local ou outro ambiente para o SQL Server, o código geralmente funciona sem modificações adicionais. Isso é especialmente verdadeiro para código simples, como uma função que usa algumas entradas e retorna um valor. Também é mais fácil portar soluções que usam os pacotesrevoscalepy/, que suportam a execução em diferentes contextos de execução com alterações mínimas. Observe que o MicrosoftML se aplica ao SQL Server 2016 (13.x), SQL Server 2017 (14.x) e SQL Server 2019 (15.x) e não aparece no SQL Server 2022 (16.x).

No entanto, seu código pode exigir alterações substanciais se alguma das seguintes situações se aplicar:

• Você usa bibliotecas que acessam a rede ou que não podem ser instaladas no SQL Server.
• O código faz chamadas separadas para fontes de dados fora do SQL Server, como planilhas do Excel, arquivos em compartilhamentos e outros bancos de dados.
• Você deseja parametrizar o procedimento armazenado e executar o código no parâmetro @script de sp_execute_external_script.
• Sua solução original inclui várias etapas que podem ser mais eficientes em um ambiente de produção se executadas de forma independente, como preparação de dados ou engenharia de recursos versus treinamento de modelo, pontuação ou emissão de relatórios.
• Você deseja otimizar o desempenho alterando bibliotecas, usando execução paralela ou descarregando algum processamento para o SQL Server.

Passo 1. Planejar requisitos e recursos

Packages

• Determine quais pacotes são necessários e verifique se eles funcionam no SQL Server.

• Instale pacotes com antecedência, na biblioteca de pacotes padrão usada pelos Serviços de Aprendizado de Máquina. Não há suporte para bibliotecas de usuários.

Fontes de dados

• Se você pretende incorporar seu código no sp_execute_external_script, identifique fontes de dados primárias e secundárias.

  • As fontes de dados primárias são grandes conjuntos de dados, como dados de treinamento de modelo ou dados de entrada para previsões. Planeie mapear o seu maior conjunto de dados para o parâmetro de entrada do sp_execute_external_script.

  • As fontes de dados secundárias geralmente são conjuntos de dados menores, como listas de fatores ou variáveis de agrupamento adicionais.

  Atualmente, o sp_execute_external_script suporta apenas um único conjunto de dados como entrada para o procedimento armazenado. No entanto, você pode adicionar várias entradas escalares ou binárias.

  As chamadas de procedimento armazenado precedidas por EXECUTE não podem ser usadas como entrada para sp_execute_external_script. Você pode usar consultas, vistas ou qualquer outra instrução SELECT válida.

• Determine as saídas de que você precisa. Se você executar o código usando sp_execute_external_script, o procedimento armazenado poderá gerar apenas um quadro de dados como resultado. No entanto, você também pode gerar várias saídas escalares, incluindo gráficos e modelos em formato binário, bem como outros valores escalares derivados de parâmetros SQL ou de código.

Tipos de dados

Para obter uma visão detalhada dos mapeamentos de tipo de dados entre o R/Python e o SQL Server, consulte estes artigos:

• Mapeamentos de tipo de dados entre R e SQL Server
• Mapeamentos de tipo de dados entre Python e SQL Server

Dê uma olhada nos tipos de dados usados em seu código R/Python e faça o seguinte:

• Faça uma lista de verificação de possíveis problemas de tipo de dados.

  Todos os tipos de dados R/Python são suportados pelos Serviços de Aprendizado de Máquina do SQL Server. No entanto, o SQL Server oferece suporte a uma variedade maior de tipos de dados do que o R ou o Python. Portanto, algumas conversões implícitas de tipo de dados são executadas ao mover dados do SQL Server de e para seu código. Talvez seja necessário transmitir ou converter explicitamente alguns dados.

  Valores NULL são suportados. No entanto, R usa a na construção de dados para representar um valor ausente, que é semelhante a um null.

• Considere eliminar a dependência de dados que não podem ser usados por R: por exemplo, os tipos de dados rowid e GUID do SQL Server não podem ser consumidos pelo R e gerarão erros.

Passo 2. Converter ou reempacotar código

O quanto você altera seu código depende se você pretende enviar o código de um cliente remoto para ser executado no contexto de computação do SQL Server ou se pretende implantar o código como parte de um procedimento armazenado. Este último pode proporcionar um melhor desempenho e segurança de dados, embora imponha alguns requisitos adicionais.

• Defina seus dados de entrada primários como uma consulta SQL sempre que possível para evitar a movimentação de dados.

• Ao executar código em um procedimento armazenado, você pode passar por várias entradas escalares . Para quaisquer parâmetros que você deseja usar na saída, adicione a palavra-chave OUTPUT .

  Por exemplo, a seguinte entrada escalar @model_name contém o nome do modelo, que é posteriormente modificado pelo script R, e esse é exibido em sua própria coluna nos resultados.

  -- declare a local scalar variable which will be passed into the R script
  DECLARE @local_model_name AS NVARCHAR (50) = 'DefaultModel';
  
  -- The below defines an OUTPUT variable in the scope of the R script, called model_name
  -- Syntactically, it is defined by using the @model_name name. Be aware that the sequence
  -- of these parameters is very important. Mandatory parameters to sp_execute_external_script
  -- must appear first, followed by the additional parameter definitions like @params, etc.
  EXECUTE sp_execute_external_script @language = N'R', @script = N'
    model_name <- "Model name from R script"
    OutputDataSet <- data.frame(InputDataSet$c1, model_name)'
    , @input_data_1 = N'SELECT 1 AS c1'
    , @params = N'@model_name nvarchar(50) OUTPUT'
    , @model_name = @local_model_name OUTPUT;
  
  -- optionally, examine the new value for the local variable:
  SELECT @local_model_name;
  

• Todas as variáveis que passar como parâmetros do procedimento armazenado sp_execute_external_script devem ser mapeadas para variáveis no código. Por padrão, as variáveis são mapeadas por nome. Todas as colunas no conjunto de dados de entrada também devem ser mapeadas para variáveis no script.

  Por exemplo, suponha que o script R contenha uma fórmula como esta:

  formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek
  

  Um erro será gerado se o conjunto de dados de entrada não contiver colunas com os nomes correspondentes ArrDelay, CRSDepTime, DayOfWeek, CRSDepHour e DayOfWeek.

• Em alguns casos, um esquema de saída deve ser definido com antecedência para os resultados.

  Por exemplo, para inserir os dados em uma tabela, você deve usar a cláusula WITH RESULT SET para especificar o esquema.

  O esquema de saída também é necessário se o script usar o argumento @parallel=1. O motivo é que vários processos podem ser criados pelo SQL Server para executar a consulta em paralelo, com os resultados coletados no final. Portanto, o esquema de saída deve ser preparado antes que os processos paralelos possam ser criados.

  Em outros casos, você pode omitir o esquema de resultados usando a opção WITH RESULT SETS UNDEFINED. Esta instrução retorna o conjunto de dados do script sem nomear as colunas ou especificar os tipos de dados SQL.

• Considere gerar dados de tempo ou rastreamento usando T-SQL em vez de R/Python.

  Por exemplo, você pode passar o tempo do sistema ou outras informações usadas para auditoria e armazenamento adicionando uma chamada T-SQL que é passada para os resultados, em vez de gerar dados semelhantes no script.

Melhorar o desempenho e a segurança

• Execute todas as consultas com antecedência e revise os planos de consulta do SQL Server para identificar tarefas que podem ser executadas em paralelo.

  Se a consulta de entrada puder ser paralelizada, inclua @parallel=1 como parte dos seus argumentos para sp_execute_external_script.

  O processamento paralelo com esse sinalizador normalmente é possível sempre que o SQL Server pode trabalhar com tabelas particionadas ou distribuir uma consulta entre vários processos e agregar os resultados no final. O processamento paralelo com esse sinalizador normalmente não é possível se você estiver treinando modelos usando algoritmos que exigem que todos os dados sejam lidos ou se precisar criar agregações.

• Revise seu código para determinar se há etapas que podem ser executadas de forma independente ou mais eficiente usando uma chamada de procedimento armazenado separada. Por exemplo, você pode obter um melhor desempenho fazendo engenharia de recursos ou extração de recursos separadamente e salvando os valores em uma tabela.

• Procure maneiras de usar T-SQL em vez de código R/Python para cálculos baseados em conjuntos.

  Por exemplo, esta solução R mostra como as funções T-SQL definidas pelo utilizador e o R podem executar a mesma tarefa de engenharia de características: Guia completo de ciência de dados.

• Consulte um desenvolvedor de banco de dados para determinar maneiras de melhorar o desempenho usando recursos do SQL Server, como tabelas com otimização de memória ou, se você tiver o Enterprise Edition, o Administrador de Recursos.

• Se você estiver usando R, substitua, se possível, as funções R convencionais por funções RevoScaleR que suportam execução distribuída. Para obter mais informações, consulte Comparação de funções Base R e RevoScaleR.

Passo 3. Prepare-se para a implantação

• Notifique o administrador para que os pacotes possam ser instalados e testados antes da implantação do código.

  Em um ambiente de desenvolvimento, pode ser aceitável instalar pacotes como parte do seu código, mas essa é uma prática incorreta em um ambiente de produção.

  Não há suporte para bibliotecas de usuário, independentemente de você estar usando um procedimento armazenado ou executando código R/Python no contexto de computação do SQL Server.

Empacote seu código R/Python em um procedimento armazenado

• Crie uma função T-SQL definida pelo usuário, incorporando seu código usando a instrução sp-execute-external-script .

• Se você tiver um código R complexo, use o pacote R sqlrutils para converter seu código. Este pacote foi projetado para ajudar usuários experientes do R a escrever um bom código de procedimento armazenado. Você reescreve seu código R como uma única função com entradas e saídas claramente definidas e, em seguida, usa o pacote sqlrutils para gerar as entradas e saídas no formato correto. O pacote sqlrutils gera o código completo do procedimento armazenado para você e também pode registrar o procedimento armazenado no banco de dados.

  Para obter mais informações e exemplos, consulte sqlrutils (SQL).

Integração com outros fluxos de trabalho

• Aproveite as ferramentas T-SQL e os processos ETL. Execute engenharia de características, extração de características e limpeza de dados com antecedência como parte de fluxos de trabalho de dados.

  Quando você está trabalhando em um ambiente de desenvolvimento dedicado, você pode extrair dados para o seu computador, analisar os dados iterativamente e, em seguida, escrever ou exibir os resultados. No entanto, quando o código autônomo é migrado para o SQL Server, grande parte desse processo pode ser simplificada ou delegada a outras ferramentas do SQL Server.

• Use estratégias de visualização seguras e assíncronas.

  Os usuários do SQL Server geralmente não podem acessar arquivos no servidor, e as ferramentas de cliente SQL normalmente não suportam os dispositivos gráficos R/Python. Se você gerar gráficos ou outros gráficos como parte da solução, considere exportar os gráficos como dados binários e salvar em uma tabela ou gravar.

• Envolva funções de previsão e pontuação em procedimentos armazenados para acesso direto por aplicativos.

Próximos passos

Para exibir exemplos de como as soluções R e Python podem ser implantadas no SQL Server, consulte estes tutoriais:

Tutoriais sobre a linguagem R

• Desenvolva um modelo preditivo em R com aprendizado de máquina SQL

• Preveja as tarifas de táxi de Nova York com classificação binária

Tutoriais em Python

• Preveja o aluguel de esquis com regressão linear com aprendizado de máquina SQL

• Preveja as tarifas de táxi de Nova York com classificação binária