sp_execute_external_script (Transact-SQL)

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

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

Para Serviços de Machine Learning, Python e R são linguagens suportadas. Para Extensões de Linguagem, Java é suportado, mas deve ser definido com CREATE EXTERNAL LANGUAGE.

Para executar sp_execute_external_scripto , você deve primeiro instalar os Serviços de Machine Learning ou as Extensões de Linguagem. Para obter mais informações, consulte Instalar os Serviços de Machine Learning do SQL Server (Python e R) no Windows e no Linux ou Instalar extensões de linguagem do SQL Server no Windows e no Linux.

Convenções de sintaxe de Transact-SQL

Sintaxe

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 ] ]
[ ; ]

Argumentos

@language [ = ] N'linguagem'

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

@script [ = ] N'script'

Script de idioma externo 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 pelo @input_data_1. O tipo de dados da variável no script externo depende do idioma. Para R, a variável de entrada é um quadro de dados. Para 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, por nome do produto. O tipo de dados da variável no script externo depende do idioma. Para R, a variável de entrada é um quadro de dados. Para 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. Para R, a variável de entrada é um quadro de dados. Para 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 ao 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 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 do 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 paralelizado trivialmente. 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 do R que usam funções RevoScaleR, o sp_execute_external_script processamento paralelo é tratado automaticamente e você não deve especificar @parallel = 1 a chamada.

@params [ = ] N'@parameter_name data_type' [ OUT | SAÍDA ] [ , ... n ]

Uma lista de declarações de parâmetro de entrada que são usadas no script externo.

@parameter1 [ = ] 'valor1' [ SAÍDA | SAÍDA ] [ , ... n ]

Uma lista de valores para os parâmetros de entrada usados pelo script externo.

Comentários

Importante

A árvore de consulta é controlada pelo aprendizado de máquina do SQL 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 compatível. As linguagens com suporte são Python e R usadas com os Serviços de Machine Learning e qualquer linguagem definida com CREATE EXTERNAL LANGUAGE (por exemplo, Java) usada com Extensões de Linguagem.

Por padrão, os conjuntos de resultados retornados por esse procedimento armazenado são gerados 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 de saída. Para nomear colunas do 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 externos. Para obter mais informações, consulte CRIAR POOL DE RECURSOS EXTERNOS. As informações sobre a carga de trabalho podem ser obtidas nas exibições do catálogo do administrador de recursos, DMVs e contadores. Para obter mais informações, consulte Exibições do Catálogo do Administrador de Recursos, Exibições de Gerenciamento Dinâmico Relacionadas ao Administrador de Recursos e SQL Server, objeto Scripts Externos.

Monitorar a execução do script

Monitore a execução do 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 fornecidas que segmentam naturalmente um conjunto de dados em partições lógicas, criadas e usadas somente durante a execução do script. Colunas contendo 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, onde 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 no R no SQL Server.

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 @parallel = 1 tabelas com mais de 256 linhas, mas se você quiser lidar com isso explicitamente, esse script incluirá 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. Mas com os novos parâmetros no SQL Server 2019 (15.x) e versões posteriores, 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 @params streaming, especifique um valor inteiro para o parâmetro, @r_rowsPerRead na 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 de dados. Você também pode usar esse parâmetro para gerenciar o número de linhas que estão sendo lidas e processadas de uma só vez, para atenuar os problemas de desempenho do servidor.

Tanto o @r_rowsPerRead parâmetro para streaming quanto o @parallel argumento devem ser considerados dicas. Para que a dica seja aplicada, deve ser possível gerar um plano de consulta SQL que inclua 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 são suportados apenas na Enterprise Edition. Você pode incluir os parâmetros em suas consultas na Standard Edition sem gerar um erro, mas os parâmetros não têm efeito e os scripts do R são executados em um único processo.

Limitações

Tipos de dados

Os tipos de dados a seguir não têm suporte quando usados na consulta de entrada ou nos parâmetros do procedimento e retornam um erro de sp_execute_external_script 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, hora
• sql_variant
• de texto, de imagem
• xml
• hierarchyid, geometria, geografia
• 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 é gerado como NULL.

Restrições específicas para R

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

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

Permissões

Requer a 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.

R. Retornar um conjunto de dados do R para o 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 PROCEDURE IF EXISTS get_iris_dataset;
GO
CREATE PROCEDURE 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 Python simples.

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 enviados para SQL Server; portanto, use a instrução WITH RESULT para especificar os nomes de coluna e os tipos de dados a serem usados pelo SQL.

C. Gerar um modelo do R com base em dados do 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 instalação antecipada do pacote e1071 . Para obter mais informações, consulte Instalar pacotes do R com sqlmlutils.

DROP PROCEDURE IF EXISTS generate_iris_model;
GO
CREATE PROCEDURE 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.

Conteúdo relacionado

• Machine learning do SQL
• O que são Extensões de Linguagem do SQL Server?
• Procedimentos armazenados do sistema (Transact-SQL)
• CREATE EXTERNAL LIBRARY (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• Opção de Configuração do servidor external scripts enabled
• SERVERPROPERTY (Transact-SQL)
• SQL Server, objeto Scripts Externos
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats