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 Aprendizado de Máquina 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 Aprendizado de Máquina ou as Extensões de Idioma. Para obter mais informações, consulte Instalar os Serviços de Aprendizado de Máquina do SQL Server (Python e R) no Windows e no Linux ou Instalar extensões de idioma 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'língua'

Indica a linguagem de 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 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 pelo @input_data_1. O tipo de dados da variável no script externo depende da linguagem. 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 da linguagem. 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 da linguagem. 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 da linguagem. 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 a máquina 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 parâmetro pode ser benéfico para processar grandes conjuntos de dados, supondo que o @parallel 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 é manipulado automaticamente e você não deve especificar @parallel = 1 a sp_execute_external_script chamada.

@params [ = ] N'@parameter_name data_type' [ SAÍDA | 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 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 com suporte. As linguagens suportadas são Python e R usadas com Serviços de Aprendizado de Máquina, e qualquer linguagem definida com CREATE EXTERNAL LANGUAGE (por exemplo, Java) usada com Language Extensions.

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 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 externos. Para obter mais informações, consulte CREATE EXTERNAL RESOURCE POOL (Transact-SQL). As informações sobre a carga de trabalho podem ser obtidas nas exibições do catálogo do administrador de recursos, no Detran e nos contadores. Para obter mais informações, consulte Exibições de catálogo do Administrador de Recursos (Transact-SQL), Exibições de gerenciamento dinâmico relacionadas ao Administrador de Recursos (Transact-SQL) e SQL Server, Objeto de scripts externos.

Monitorar a execução do script

Monitore a execução de scripts 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 com o script externo sendo executado uma vez para sp_execute_external_script 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 @parallel = 1 tabelas com mais de 256 linhas, mas se você quiser manipular 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 esse recurso.

Execução de streaming para scripts Python e R

O streaming permite que o script Python ou R trabalhe com mais dados do que cabem na memória. Para controlar o número de linhas passadas durante o streaming, especifique um valor inteiro para o parâmetro, @r_rowsPerRead na @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 um bloco de dados. Você também pode usar esse parâmetro para gerenciar o número de linhas sendo lidas e processadas ao mesmo tempo, para atenuar os problemas de desempenho do servidor.

Tanto o parâmetro para streaming quanto o @r_rowsPerRead@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 no Standard Edition sem gerar um erro, mas os parâmetros não têm efeito e os scripts R são executados em um único processo.

Limitações

Tipos de dados

Os seguintes tipos de dados não são suportados 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
• texto, imagem
• xml
• hierárquico, 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 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 o aprendizado de máquina do SQL permite um intervalo maior de valores do que o suportado na linguagem R.

Valores flutuantes (por exemplo, , , ) não têm suporte no aprendizado de máquina do SQL, +Inf-InfNaNmesmo que ambas as linguagens usem IEEE 754. O comportamento atual apenas envia os valores para o SQL diretamente; como resultado, o cliente SQL lança 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 Transact-SQL.

R. Retornar um conjunto de dados 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 com 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 o 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 R com base nos 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 a instalação antecipada do pacote e1071 . Para obter mais informações, consulte Instalar pacotes R adicionais no SQL Server.

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
• Extensões de idioma 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 External Scripts
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats