sp_execute_external_script (Transact-SQL)

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

O procedimento armazenado sp_execute_external_script executa um script fornecido como um argumento de entrada para o procedimento e é usado com Machine Learning Serviços e Extensões de Linguagem.

Para Machine Learning Services, o Python e o R têm suporte para idiomas. Para extensões de idioma, o Java tem suporte, mas deve ser definido com CREATE EXTERNAL LANGUAGE.

Para executar sp_execute_external_script, primeiro você deve instalar Machine Learning Services ou Extensões de Idioma. Para obter mais informações, consulte Instalar SQL Server Machine Learning Services (Python e R) em Windows e Linux ou instalar extensões de idioma SQL Server em Windows e Linux.

O procedimento armazenado sp_execute_external_script executa um script fornecido como um argumento de entrada para o procedimento e é usado com Machine Learning Services no SQL Server 2017.

Para Machine Learning Services, o Python e o R têm suporte para idiomas.

Para executar sp_execute_external_script, primeiro você deve instalar o Machine Learning Services. Para obter mais informações, consulte Instalar SQL Server Machine Learning Services (Python e R) no Windows.

O procedimento armazenado sp_execute_external_script executa um script fornecido como um argumento de entrada para o procedimento e é usado com os Serviços do R no SQL Server 2016.

Para r services, R é o idioma com suporte.

Para executar sp_execute_external_script, primeiro você deve instalar o R Services. Para obter mais informações, consulte Instalar SQL Server Machine Learning Services (Python e R) no Windows.

O procedimento armazenado sp_execute_external_script executa um script fornecido como um argumento de entrada para o procedimento e é usado com Machine Learning Services em Instância Gerenciada de SQL do Azure.

Para Machine Learning Services, o Python e o R têm suporte para idiomas.

Para executar sp_execute_external_script, primeiro você deve habilitar Machine Learning Services. Para obter mais informações, consulte os Serviços de Machine Learning na documentação Instância Gerenciada de SQL do Azure.

Topic link iconConvenções de sintaxe do Transact-SQL

Syntax

sp_execute_external_script
    @language = N'language',
    @script = N'script'  
    [ , @input_data_1 = N'input_data_1' ]
    [ , @input_data_1_name = N'input_data_1_name' ]  
    [ , @input_data_1_order_by_columns = N'input_data_1_order_by_columns' ]
    [ , @input_data_1_partition_by_columns = N'input_data_1_partition_by_columns' ]  
    [ , @output_data_1_name = N'output_data_1_name' ]  
    [ , @parallel = 0 | 1 ]  
    [ , @params = N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]' ] 
    [ , @parameter1 = 'value1' [ OUT | OUTPUT ] [ ,...n ] ]

Sintaxe para SQL Server 2017 e anterior

sp_execute_external_script   
    @language = N'language',   
    @script = N'script'  
    [ , @input_data_1 = N'input_data_1' ]   
    [ , @input_data_1_name = N'input_data_1_name' ]  
    [ , @output_data_1_name = N'output_data_1_name' ]  
    [ , @parallel = 0 | 1 ]  
    [ , @params = N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]' ] 
    [ , @parameter1 = 'value1' [ OUT | OUTPUT ] [ ,...n ] ]

Argumentos

@language = N'language'

Indica a linguagem de script. idioma é sysname. Os valores válidos são R, Python e qualquer idioma definido com CREATE EXTERNAL LANGUAGE (por exemplo, Java).

Indica a linguagem de script. idioma é sysname. No SQL Server 2017, os valores válidos são R e Python.

Indica a linguagem de script. idioma é sysname. Em SQL Server 2016, o único valor válido é R.

Indica a linguagem de script. idioma é sysname. Em Instância Gerenciada de SQL do Azure, os valores válidos são R e Python.

@script = N'script' Script de linguagem externa especificado como uma entrada literal ou variável. script é nvarchar(max).

[ @input_data_1 = N'input_data_1' ]Especifica os dados de entrada usados pelo script externo na forma de uma consulta Transact-SQL. O tipo de dados de input_data_1 é nvarchar(max).

[ @input_data_1_name = N'input_data_1_name' ] Especifica o nome da variável usada para representar a consulta definida por @input_data_1. O tipo de dados da variável no script externo depende do idioma. No caso de R, a variável de entrada é um quadro de dados. No caso do Python, a entrada deve ser tabular. input_data_1_name é sysname. O valor padrão é InputDataSet.

[ @input_data_1_order_by_columns = N'input_data_1_order_by_columns' ] Usado para criar modelos por partição. Especifica o nome da coluna usada para ordenar o conjunto de resultados, por exemplo, pelo nome do produto. O tipo de dados da variável no script externo depende do idioma. No caso de R, a variável de entrada é um quadro de dados. No caso do Python, a entrada deve ser tabular.

[ @input_data_1_partition_by_columns = N'input_data_1_partition_by_columns' ] Usado para criar modelos por partição. Especifica o nome da coluna usada para segmentar dados, como região geográfica ou data. O tipo de dados da variável no script externo depende do idioma. No caso de R, a variável de entrada é um quadro de dados. No caso do Python, a entrada deve ser tabular.

[ @output_data_1_name = N'output_data_1_name' ]Especifica o nome da variável no script externo que contém os dados a serem retornados para SQL Server após a conclusão da chamada de procedimento armazenado. O tipo de dados da variável no script externo depende do idioma. Para R, a saída deve ser um quadro de dados. Para Python, a saída deve ser um quadro de dados pandas. output_data_1_name é sysname. O valor padrão é OutputDataSet.

[ @parallel = 0 | 1 ] Habilite a execução paralela de scripts R definindo o @parallel parâmetro como 1. O padrão para esse parâmetro é 0 (sem paralelismo). Se @parallel = 1 e a saída estiver sendo transmitida diretamente para o computador cliente, a WITH RESULT SETS cláusula será necessária e um esquema de saída deverá ser especificado.

  • Para scripts R que não usam funções RevoScaleR, o uso do @parallel parâmetro pode ser benéfico para o processamento de grandes conjuntos de dados, supondo que o script possa ser trivialmente paralelizado. Por exemplo, ao usar a função R predict com um modelo para gerar novas previsões, defina @parallel = 1 como uma dica para o mecanismo de consulta. Se a consulta puder ser paralelizada, as linhas serão distribuídas de acordo com a configuração MAXDOP .

  • Para scripts R que usam funções RevoScaleR, o processamento paralelo é tratado automaticamente e você não deve especificar @parallel = 1 para a chamada sp_execute_external_script .

[ @params = N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]' ] Uma lista de declarações de parâmetro de entrada usadas no script externo.

[ @parameter1 = 'value1' [ OUT | OUTPUT ] [ ,...n ] ] Uma lista de valores para os parâmetros de entrada usados pelo script externo.

Comentários

Importante

A árvore de consulta é controlada por SQL aprendizado de máquina e os usuários não podem executar operações arbitrárias na consulta.

Use sp_execute_external_script para executar scripts escritos em um idioma com suporte. Os idiomas com suporte são Python e R usados com Machine Learning Services e qualquer idioma definido com CREATE EXTERNAL LANGUAGE (por exemplo, Java) usado com extensões de linguagem.

Use sp_execute_external_script para executar scripts escritos em um idioma com suporte. Os idiomas com suporte são Python e R no SQL Server 2017 Machine Learning Services.

Use sp_execute_external_script para executar scripts escritos em um idioma com suporte. O único idioma com suporte é r no SQL Server 2016 R Services.

Use sp_execute_external_script para executar scripts escritos em um idioma com suporte. Os idiomas com suporte são Python e R no Instância Gerenciada de SQL do Azure Machine Learning Services.

Por padrão, os conjuntos de resultados retornados por esse procedimento armazenado são saídas com colunas sem nome. Os nomes de coluna usados em um script são locais para o ambiente de script e não são refletidos no conjunto de resultados gerado. Para nomear colunas de conjunto de resultados, use a WITH RESULT SET cláusula de EXECUTE.

Além de retornar um conjunto de resultados, você pode retornar valores escalares para usar parâmetros OUTPUT.

Você pode controlar os recursos usados por scripts externos configurando um pool de recursos externo. Para obter mais informações, consulte CREATE EXTERNAL RESOURCE POOL (Transact-SQL). Informações sobre a carga de trabalho podem ser obtidas nas exibições do catálogo do administrador de recursos, DMV e contadores. Para obter mais informações, consulte Resource Governor exibições de catálogo (Transact-SQL), Resource Governor exibições de gerenciamento dinâmico relacionadas (Transact-SQL) e SQL Server, objeto Scripts Externos.

Monitorar a execução do script

Monitorar a execução de script usando sys.dm_external_script_requests e sys.dm_external_script_execution_stats.

Parâmetros para modelagem de partição

Você pode definir dois parâmetros adicionais que permitem a modelagem em dados particionados, em que as partições são baseadas em uma ou mais colunas que você fornece que naturalmente segmentam um conjunto de dados em partições lógicas criadas e usadas somente durante a execução do script. Colunas que contêm valores repetidos para idade, sexo, região geográfica, data ou hora, são alguns exemplos que se prestam a conjuntos de dados particionados.

Os dois parâmetros são input_data_1_partition_by_columns e input_data_1_order_by_columns, em que o segundo parâmetro é usado para ordenar o conjunto de resultados. Os parâmetros são passados como entradas para sp_execute_external_script com o script externo em execução uma vez para cada partição. Para obter mais informações e exemplos, consulte Tutorial: Criar modelos baseados em partição.

Você pode executar o script em paralelo especificando @parallel=1. Se a consulta de entrada puder ser paralelizada, você deverá definir @parallel=1 como parte de seus argumentos como sp_execute_external_script. Por padrão, o otimizador de consulta opera em tabelas @parallel=1 com mais de 256 linhas, mas se você quiser lidar com isso explicitamente, esse script inclui o parâmetro como uma demonstração.

Dica

Para treinar cargas de trabalho, use @parallel com qualquer script de treinamento arbitrário, mesmo aqueles que usam algoritmos não Microsoft Rx. Normalmente, somente os algoritmos do RevoScaleR (com o prefixo rx) oferecem paralelismo em cenários de treinamento no SQL Server. Porém, com os novos parâmetros no SQL Server 2019 e posterior, você pode paralelizar um script que chama funções não especificamente projetadas com essa funcionalidade.

Execução de streaming para scripts Python e R

O streaming permite que o script Python ou R funcione com mais dados do que pode caber na memória. Para controlar o número de linhas passadas durante o streaming, especifique um valor inteiro para o parâmetro na @r_rowsPerRead@params coleção. Por exemplo, se você estiver treinando um modelo que usa dados muito amplos, poderá ajustar o valor para ler menos linhas, para garantir que todas as linhas possam ser enviadas em uma parte dos dados. Você também pode usar esse parâmetro para gerenciar o número de linhas que estão sendo lidas e processadas ao mesmo tempo, para atenuar problemas de desempenho do servidor.

@r_rowsPerRead O parâmetro para streaming e o @parallel argumento devem ser considerados dicas. Para que a dica seja aplicada, é possível gerar um plano de consulta SQL que inclua o processamento paralelo. Se isso não for possível, o processamento paralelo não poderá ser habilitado.

Observação

O streaming e o processamento paralelo têm suporte apenas em Edição Enterprise. Você pode incluir os parâmetros em suas consultas em Edição Standard sem gerar um erro, mas os parâmetros não têm efeito e os scripts R são executados em um único processo.

Restrições

Tipos de dados

Os tipos de dados a seguir não têm suporte quando usados na consulta de entrada ou parâmetros de sp_execute_external_script procedimento e retornam um erro de tipo sem suporte.

Como solução alternativa, CAST a coluna ou o valor para um tipo com suporte no Transact-SQL antes de enviá-lo para o script externo.

  • cursor

  • timestamp

  • datetime2, datetimeoffset, time

  • sql_variant

  • texto, imagem

  • xml

  • hierarchyid, geometry, geography

  • Tipos definidos pelo usuário de CLR

Em geral, qualquer conjunto de resultados que não possa ser mapeado para um tipo de dados transact-SQL é saída como NULL.

Restrições específicas do R

Se a entrada incluir valores datetime que não se ajustam ao intervalo permitido de valores em R, os valores serão convertidos em NA. Isso é necessário porque SQL aprendizado de máquina permite um intervalo maior de valores do que há suporte na linguagem R.

Não há suporte para valores float (por exemplo, +Inf, -Inf, NaN) em SQL aprendizado de máquina, embora ambas as linguagens usem IEEE 754. O comportamento atual apenas envia os valores para SQL diretamente; como resultado, o cliente SQL gerará um erro. Portanto, esses valores são convertidos em NULL.

Permissões

Requer permissão de banco de dados EXECUTE ANY EXTERNAL SCRIPT .

Exemplos

Esta seção contém exemplos de como esse procedimento armazenado pode ser usado para executar scripts R ou Python usando o Transact-SQL.

a. Retornar um conjunto de dados R para SQL Server

O exemplo a seguir cria um procedimento armazenado que usa sp_execute_external_script para retornar o conjunto de dados iris incluído no R.

DROP PROC IF EXISTS get_iris_dataset;  
go  
CREATE PROC get_iris_dataset
AS  
BEGIN  
 EXEC   sp_execute_external_script  
       @language = N'R'  
     , @script = N'iris_data <- iris;'
     , @input_data_1 = N''  
     , @output_data_1_name = N'iris_data'
     WITH RESULT SETS (("Sepal.Length" float not null,
           "Sepal.Width" float not null,  
        "Petal.Length" float not null,
        "Petal.Width" float not null, "Species" varchar(100)));  
END;
GO

B. Criar um modelo de Python e gerar pontuações com base nele

Este exemplo ilustra como usar sp_execute_external_script para gerar pontuações em um modelo simples do Python.

CREATE PROCEDURE [dbo].[py_generate_customer_scores]
AS
BEGIN

-- Input query to generate the customer data
DECLARE @input_query NVARCHAR(MAX) = N'SELECT customer, orders, items, cost FROM dbo.Sales.Orders'

EXEC sp_execute_external_script @language = N'Python', @script = N'
import pandas as pd
from sklearn.cluster import KMeans

# Get data from input query
customer_data = my_input_data

# Define the model
n_clusters = 4
est = KMeans(n_clusters=n_clusters, random_state=111).fit(customer_data[["orders","items","cost"]])
clusters = est.labels_
customer_data["cluster"] = clusters

OutputDataSet = customer_data
'
, @input_data_1 = @input_query
, @input_data_1_name = N'my_input_data'
WITH RESULT SETS (("CustomerID" int, "Orders" float,"Items" float,"Cost" float,"ClusterResult" float));
END;
GO

Os títulos de coluna usados no código Python não são saída para SQL Server; portanto, use a instrução WITH RESULT para especificar os nomes de coluna e os tipos de dados para SQL usar.

C. Gerar um modelo R com base em dados de SQL Server

O exemplo a seguir cria um procedimento armazenado que usa sp_execute_external_script para gerar um modelo de íris e retornar o modelo.

Observação

Este exemplo requer a instalação antecipada do pacote e1071. Para obter mais informações, consulte Instalar pacotes adicionais do R em SQL Server.

DROP PROC IF EXISTS generate_iris_model;
GO
CREATE PROC generate_iris_model
AS
BEGIN
 EXEC sp_execute_external_script  
      @language = N'R'  
     , @script = N'  
          library(e1071);  
          irismodel <-naiveBayes(iris_data[,1:4], iris_data[,5]);  
          trained_model <- data.frame(payload = as.raw(serialize(irismodel, connection=NULL)));  
'  
     , @input_data_1 = N'select "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species" from iris_data'  
     , @input_data_1_name = N'iris_data'  
     , @output_data_1_name = N'trained_model'  
    WITH RESULT SETS ((model varbinary(max)));  
END;
GO

Para gerar um modelo semelhante usando Python, altere o identificador de idioma de @language=N'R' para @language = N'Python'e faça as modificações necessárias para o argumento @script. Caso contrário, todos os parâmetros funcionam do mesmo modo que para o R.

Para pontuação, você também pode usar a função nativa PREDICT, que é normalmente mais rápida porque evita que o runtime do Python ou do R seja chamado.

Confira também