sp_execute_external_script (Transact-SQL)

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

Transact-SQL convenções de 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 ] ]
[ ; ]

EXEC 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'língua'

[ @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 do 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 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.

[ @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 parâmetro @parallel como 1. O padrão para esse parâmetro é 0 (sem paralelismo). Se @parallel = 1 e a saída estão sendo transmitidas diretamente para a máquina cliente, a cláusula WITH RESULT SETS é necessária e um esquema de saída deve ser especificado.

• Para scripts R que não usam funções RevoScaleR, usar o parâmetro @parallel 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' [ SAÍDAS | SAÍDA ] [ , ... n ]

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

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

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

Comentários

Por padrão, os conjuntos de resultados retornados por este 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 produzido. Para nomear colunas do conjunto de resultados, use a cláusula WITH RESULT SET de EXECUTE.

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

Monitorar a execução de scripts

Monitore a execução de scripts usando sys.dm_external_script_requests e sys.dm_external_script_execution_stats.

Execução de streaming para scripts Python e R

O streaming permite que o script Python ou R trabalhe com mais dados do que cabe 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 coleção @params. 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 que estão sendo lidas e processadas ao mesmo tempo, para mitigar problemas de desempenho do servidor.

Tanto o parâmetro @r_rowsPerRead para streaming quanto o argumento @parallel 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.

Limitações

Tipos de dados

Os seguintes tipos de dados não são suportados quando usados na consulta de entrada ou parâmetros de sp_execute_external_script procedimento e retornam um erro de tipo não suportado.

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

• cursor
• de carimbo de data/hora
• datetime2, datetimeoffset, hora
• sql_variant
• texto, imagem
• xml
• hierarchyid, geometria, geografia
• Tipos definidos pelo usuário 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 de R

Se a entrada incluir data/hora valores que não se ajustam ao intervalo de valores permitido 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.

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

Um. 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. Crie um modelo Python e gere pontuações a partir dele

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 cabeçalhos 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 para SQL usar.

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 pontuação, você também pode usar a função nativa PREDICT, que normalmente é mais rápida porque evita chamar o tempo de execução Python ou R.

Conteúdo relacionado

• de aprendizado de máquina SQL
• O que é SQL Server Language Extensions?
• Procedimentos armazenados do sistema (Transact-SQL)
• CRIAR BIBLIOTECA EXTERNA (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• scripts externos habilitados Opção de configuração do servidor
• SERVERPROPERTY (Transact-SQL)
• SQL Server, objeto Scripts Externos
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats