Procedimentos armazenados usando o SQL do Synapse no Azure Synapse Analytics

Os pools do SQL do Synapse provisionados e sem servidor permitem que você coloque uma lógica complexa de processamento de dados em procedimentos armazenados do SQL. Os procedimentos armazenados são uma ótima maneira de encapsular seu código SQL e armazená-lo perto de seus dados no data warehouse. Os procedimentos armazenados ajudam os desenvolvedores a modularizarem suas soluções encapsulando o código em unidades gerenciáveis; facilitando a maior reutilização do código. Cada procedimento armazenado também pode aceitar parâmetros para torná-lo ainda mais flexível. Neste artigo, você encontrará algumas dicas para implementar procedimentos armazenados no pool de SQL do Synapse para desenvolver soluções.

O que esperar

O SQL do Synapse dá suporte a muitos dos recursos do T-SQL que são usados no SQL Server. Mais importante, há recursos específicos de expansão que você pode usar para maximizar o desempenho da sua solução. Neste artigo, você aprenderá sobre os recursos que podem ser colocados em procedimentos armazenados.

Observação

No corpo do procedimento, você pode usar apenas os recursos que têm suporte na área de superfície do SQL do Synapse. Examine este artigo para identificar objetos e instruções que podem ser usados em procedimentos armazenados. Os exemplos nestes artigos usam recursos genéricos que estão disponíveis na área de superfície dedicada e sem servidor. Confira limitações adicionais em pools do SQL do Synapse provisionados e sem servidor no final deste artigo.

Para manter a escala e o desempenho do pool de SQL também há alguns recursos e funcionalidades que apresentam diferenças de comportamento e outros que não têm suporte.

Procedimentos armazenados no SQL do Synapse

No exemplo a seguir, você pode ver os procedimentos que removem objetos externos, se existirem no banco de dados:

CREATE PROCEDURE drop_external_table_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_tables WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL TABLE ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END
GO
CREATE PROCEDURE drop_external_file_format_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_file_formats WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL FILE FORMAT ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END
GO
CREATE PROCEDURE drop_external_data_source_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_data_sources WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL DATA SOURCE ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END

Esses procedimentos podem ser executados usando a instrução EXEC em que você pode especificar o nome do procedimento e os parâmetros:

EXEC drop_external_table_if_exists 'mytest';
EXEC drop_external_file_format_if_exists 'mytest';
EXEC drop_external_data_source_if_exists 'mytest';

O SQL do Synapse fornece uma implementação simplificada e otimizada de procedimentos armazenados. A maior diferença em comparação ao SQL Server é que o procedimento armazenado não é um código pré-compilado. Em data warehouses, o tempo de compilação é pequeno em comparação com o tempo necessário para executar consultas em grandes volumes de dados. É mais importante garantir que o código do procedimento armazenado seja otimizado corretamente para grandes consultas. A meta é poupar horas, minutos e segundos, não milissegundos. Portanto, é mais útil pensar em procedimentos armazenados como contêineres para a lógica SQL.

Quando o SQL do Synapse executa o procedimento armazenado, as instruções SQL são analisadas, traduzidas e otimizadas em tempo de execução. Durante esse processo, cada instrução é convertida em consultas distribuídas. O código SQL executado nos dados é diferente da consulta enviada.

Encapsular regras de validação

Os procedimentos armazenados permitem que você localize a lógica de validação em um único módulo armazenado no banco de dados SQL. No exemplo a seguir, você pode ver como validar os valores dos parâmetros e alterar seus valores padrão.

CREATE PROCEDURE count_objects_by_date_created 
                            @start_date DATETIME2,
                            @end_date DATETIME2
AS BEGIN 

    IF( @start_date >= GETUTCDATE() )
    BEGIN
        THROW 51000, 'Invalid argument @start_date. Value should be in past.', 1;  
    END

    IF( @end_date IS NULL )
    BEGIN
        SET @end_date = GETUTCDATE();
    END

    IF( @start_date >= @end_date )
    BEGIN
        THROW 51000, 'Invalid argument @end_date. Value should be greater than @start_date.', 2;  
    END

    SELECT
         year = YEAR(create_date),
         month = MONTH(create_date),
         objects_created = COUNT(*)
    FROM
        sys.objects
    WHERE
        create_date BETWEEN @start_date AND @end_date
    GROUP BY
        YEAR(create_date), MONTH(create_date);
END

A lógica no procedimento SQL validará os parâmetros de entrada quando o procedimento for chamado.

EXEC count_objects_by_date_created '2020-08-01', '2020-09-01'

EXEC count_objects_by_date_created '2020-08-01', NULL

EXEC count_objects_by_date_created '2020-09-01', '2020-08-01'
-- Error
-- Invalid argument @end_date. Value should be greater than @start_date.

EXEC count_objects_by_date_created '2120-09-01', NULL
-- Error
-- Invalid argument @start_date. Value should be in past.

Aninhamento de procedimentos armazenados

Quando os procedimentos armazenados chamam outros procedimentos armazenados ou executam SQL dinâmico, a invocação interna de código ou procedimento armazenado é considerada aninhada. Um exemplo de procedimento aninhado é mostrado no código a seguir:

CREATE PROCEDURE clean_up @name SYSNAME
AS BEGIN
    EXEC drop_external_table_if_exists @name;
    EXEC drop_external_file_format_if_exists @name;
    EXEC drop_external_data_source_if_exists @name;
END

Esse procedimento aceita um parâmetro que representa um nome e, em seguida, chama outros procedimentos para descartar os objetos com esse nome. O pool de SQL do Synapse é compatível com até oito níveis de aninhamento. Esse recurso é um pouco diferente do SQL Server. O nível de aninhamento no SQL Server é de 32.

A chamada de procedimento armazenado de nível superior é igual ao nível 1 de aninhamento.

EXEC clean_up 'mytest'

Se o procedimento armazenado também criar outra chamada EXEC, o nível de aninhamento aumentará para dois.

CREATE PROCEDURE clean_up @name SYSNAME
AS
    EXEC drop_external_table_if_exists @name  -- This call is nest level 2
GO
EXEC clean_up 'mytest'  -- This call is nest level 1

Se o segundo procedimento executar algum SQL dinâmico, o nível de aninhamento aumentará para três.

CREATE PROCEDURE drop_external_table_if_exists @name SYSNAME
AS BEGIN
    /* See full code in the previous example */
    EXEC sp_executesql @tsql = @drop_stmt;  -- This call is nest level 3
END
GO
CREATE PROCEDURE clean_up @name SYSNAME
AS
    EXEC drop_external_table_if_exists @name  -- This call is nest level 2
GO
EXEC clean_up 'mytest'  -- This call is nest level 1

Observação

Atualmente, o SQL do Synapse não dá suporte a @@NESTLEVEL. É necessário rastrear o nível de aninhamento. É improvável que você exceda o limite de oito níveis de aninhamento, mas, se você fizer isso, será necessário trabalhar novamente em seu código para ajustar os níveis de aninhamento dentro desse limite.

INSERT..EXECUTE

O pool de SQL do Synapse provisionado não permite que você consuma o conjunto de resultados de um procedimento armazenado com uma instrução INSERT. Há uma abordagem alternativa que você pode usar. Para obter um exemplo, confira o artigo em tabelas temporárias para o pool de SQL do Synapse provisionado.

Limitações

Há alguns aspectos de procedimentos armazenados Transact-SQL que não são implementados no SQL do Synapse, como:

Recurso/opção	Provisionado	Sem servidor
Procedimentos armazenados temporariamente	Não	Sim
Procedimentos armazenados numerados	Não	Não
Procedimentos armazenados estendidos	Não	Não
procedimentos armazenados de CLR	Não	Não
Opção de criptografia	Não	Sim
Opção de replicação	Não	Não
Parâmetros com valor de tabela	Não	Não
Parâmetros somente leitura	Não	Não
Parâmetros padrão	Não	Sim
Contextos de execução	Não	Não
instrução Return	Não	Sim
INSERT INTO .. EXEC	Não	Sim

Próximas etapas

Para obter mais dicas de desenvolvimento, confira visão geral de desenvolvimento.