Modificar o código R/Python para ser executado em instâncias do SQL Server (dentro do banco de dados)

Artigo
05/30/2023

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

Este artigo fornece diretrizes de alto nível sobre como modificar o código R ou Python para ser executado como um procedimento armazenado do SQL Server a fim de aprimorar o desempenho ao acessar os dados do SQL.

Quando você move o código R/Python de um IDE local ou de outro ambiente para o SQL Server, o código geralmente funciona sem modificações adicionais. Isso é especialmente verdadeiro para códigos simples, como uma função que usa algumas entradas e retorna um valor. Também é mais fácil portar soluções que usam os pacotes RevoScaleR/revoscalepy, que dão suporte à 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), ao SQL Server 2017 (14.x) e ao SQL Server 2019 (15.x), mas não é exibido no SQL Server 2022 (16.x).

No entanto, seu código poderá exigir alterações substanciais em qualquer um dos seguintes casos:

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, tais 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 do sp_execute_external_script.
Sua solução original inclui várias etapas que, caso executadas de modo independente, podem ser mais eficientes em um ambiente de produção, assim como preparação de dados ou engenharia de recursos versus treinamento de modelo, pontuação ou relatório.
Você deseja otimizar o desempenho alterando as bibliotecas, usando a execução paralela ou descarregando algum processamento para o SQL Server.

Etapa 1. Recursos e requisitos do plano

Pacotes

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 Machine Learning. Bibliotecas de usuário não são compatíveis.

Fontes de dados

Se você pretende inserir o código no sp_execute_external_script, identifique as fontes de dados primária e secundária.
- As fontes de dados primárias são grandes conjuntos de dados, tais como os de treinamento de modelo ou dados de entrada para previsões. Planeje o mapeamento do seu maior conjunto de dados para o parâmetro de entrada sp_execute_external_script.
- As fontes de dados secundárias normalmente são conjuntos de dados menores, tais como listas de fatores ou variáveis de agrupamento adicionais.
Atualmente, sp_execute_external_script dá suporte a 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 uma entrada para sp_execute_external_script. Você pode usar consultas, exibições ou qualquer outra instrução SELECT válida.
Determine as saídas que você precisa. Se você executar o código usando o sp_execute_external_script, o procedimento armazenado poderá gerar como resultado apenas uma estrutura de dados. 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 código ou de parâmetros do SQL.

Tipos de dados

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

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 dos possíveis problemas de tipo de dados.

Todos os tipos de dados R/Python são compatíveis com os Serviços de Machine Learning do SQL Server. No entanto, SQL Server dá suporte a um maior intervalo de tipos de dados do que R ou Python. Portanto, algumas conversões de tipo de dados implícitas são executadas ao mover dados SQL Server de/para o seu código. Talvez seja necessário converter explicitamente alguns dados.

Há suporte para valors NULL. No entanto, o R usa o constructo de dados na para representar um valor ausente, semelhante um nulo.
Considere a possibilidade de eliminar as dependências sobre os 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 por R e vão gerar erros.

Etapa 2. Converter ou reempacotar código

A quantidade de alterações do código depende do que você pretende: se enviá-lo por meio de um cliente remoto para ser executado no contexto da computação do SQL Server ou implantar o código como parte de um procedimento armazenado. A implantação do código pode fornecer melhor desempenho e segurança de dados, mas isso impõe alguns requisitos adicionais.

Defina os dados de entrada principais como uma consulta SQL sempre que possível para evitar a movimentação de dados.

Ao executar o código em um procedimento armazenado, você pode realizar a passagem de várias entradas escalares. Para todos os parâmetros que você deseja usar na saída, adicione a palavra-chave OUTPUT.

Por exemplo, o seguinte @model_name de entrada escalar contém o nome do modelo, que também será modificado posteriormente pelo script R, e o apresenta na 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;

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

Por exemplo, digamos que seu script R contenha uma fórmula como esta:
```
formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek
```
Será gerado um erro se o conjunto de dados de entrada não contiver colunas com nomes correspondentes, ArrDelay, CRSDepTime, DayOfWeek, CRSDepHour e DayOfWeek.
Em alguns casos, um esquema de saída deverá 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 será 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 precisa ser preparado, para que então seja possível criar processos paralelos.

Em outros casos, você pode omitir o esquema de resultados usando a opção WITH RESULT SETS UNDEFINED. Essa instrução retorna o conjunto de dados do script sem dar nome às colunas e nem especificar os tipos de dados do SQL.
Considere a possibilidade de gerar dados de tempo ou de acompanhamento usando T-SQL em vez de R/Python.

Por exemplo, você pode passar a hora 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

Evite gravar previsões ou resultados intermediários em um arquivo. Em vez disso, grave as previsões em uma tabela para evitar a movimentação de dados.

Execute todas as consultas antecipadamente e examine os planos de consulta do SQL Server para identificar as tarefas que podem ser realizadas em paralelo.

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

Normalmente, o processamento paralelo com esse sinalizador será possível sempre que o SQL Server puder trabalhar com tabelas particionadas ou distribuir uma consulta entre vários processos e agregar os resultados no final. Normalmente, o processamento paralelo com esse sinalizador não será possível se você estiver treinando modelos usando algoritmos que exigem que todos os dados sejam lidos ou se for necessário criar agregações.
Examine seu código para determinar se há etapas que podem ser executadas de maneira independente ou executadas com mais eficiência usando uma chamada de procedimento armazenado separado. Por exemplo, você pode obter um melhor desempenho realizando a engenharia de recursos ou a extração de recursos separadamente e salvando os valores em uma tabela.
Procure meios de usar o T-SQL em vez de código R/Python para computações baseada em conjunto.

Por exemplo, esta solução de R mostra como as funções T-SQL definidas pelo usuário e o R podem executar a mesma tarefa de engenharia de recursos: Passo a passo de ponta a ponta sobre a ciência de dados.
Consulte um desenvolvedor de banco de dados para determinar maneiras de melhorar o desempenho usando recursos do SQL Server como as tabelas com otimização de memória ou, se você tiver a Edição Enterprise, o Resource Governor.
Se estiver usando R, substitua, se possível, as funções R convencionais por funções ScaleR que ofereçam suporte à execução distribuída. Para obter mais informações, confira Comparação das funções do Base R e do RevoScaleR.

Etapa 3. Preparar para a implantação

Notifique o administrador para que os pacotes possam ser instalados e testados antes de implantar seu código.

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

As bibliotecas de usuário não são compatíveis, independentemente de você estar usando um procedimento armazenado ou executando código R/Python no contexto de computação do SQL Server.

Empacotar o código R/Python em um procedimento armazenado

Crie uma função do T-SQL definida pelo usuário ao inserir seu código usando a instrução sp-execute-external-script.
Se você tiver código R complexo, use o pacote do R sqlrutils para converter o código. Este pacote foi criado para ajudar os usuários de R experientes a escreverem um código de procedimento armazenado de boa qualidade. Você reescreve o código R como apenas uma função com entradas e saídas claramente definidas e, em seguida, usa o pacote sqlrutils para gerar a entrada e as saídas no formato correto. O pacote sqlrutils gera o código de procedimento armazenado completo para você. Você também pode registrar o procedimento armazenado no banco de dados.

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

Integrar com outros fluxos de trabalho

Aproveite as ferramentas de T-SQL e os processos de ETL. Realize engenharia de recursos, extração de recursos e limpeza de dados com antecedência como parte dos fluxos de trabalho de dados.

Quando estiver trabalhando em um ambiente de desenvolvimento dedicado, você poderá efetuar pull dos dados para o seu computador, analisá-los interativamente e então gravar ou exibir os resultados. No entanto, quando código independente é 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 assíncronas e seguras.

Os usuários do SQL Server geralmente não podem acessar arquivos no servidor e as ferramentas de cliente do SQL normalmente não dão suporte aos dispositivos gráficos do R/Python. Se você gerar plotagens ou outros elementos gráficos como parte da solução, considere exportar os gráficos como dados binários e salvá-los em uma tabela ou registrá-los.
Encapsule funções de previsão e pontuação em procedimentos armazenados para que aplicativos tenham acesso direto a elas.

Próximas etapas

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

Tutoriais do R

Desenvolvimento de SQL para cientistas de dados do R

Compartilhar via