Procedimentos armazenados usando o SQL do Synapse no Azure Synapse Analytics

Os pools provisionados e sem servidor do SQL do Synapse permitem colocar lógica de processamento de dados complexa 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 modularizar suas soluções encapsulando o código em unidades gerenciáveis e facilitando uma maior reutilização do código. Cada procedimento armazenado também pode aceitar parâmetros para torná-los ainda mais flexíveis. 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 T-SQL usados no SQL Server. Mais importante, há recursos específicos de expansão que você pode usar para maximizar o desempenho da solução. Neste artigo, você aprenderá sobre os recursos que pode colocar em procedimentos armazenados.

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

Procedimentos armazenados no SQL do Synapse

No exemplo a seguir, você poderá ver os procedimentos que descartam objetos externos se eles 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 EXEC a instrução 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 unificada de procedimentos armazenados. A maior diferença em relação ao SQL Server é que o procedimento armazenado não é o 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 de procedimento armazenado seja otimizado corretamente para consultas grandes. A meta é economizar horas, minutos e segundos, não milissegundos. Portanto, é mais útil pensar em procedimentos armazenados como contêineres para a lógica do 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 declaraçã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 localizar 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.

Aninhando procedimentos armazenados

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

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 algum nome e, em seguida, chama outros procedimentos para remover os objetos com esse nome. O pool de SQL do Synapse dá suporte a um máximo de oito níveis de aninhamento. Essa funcionalidade é ligeiramente diferente de 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

INSERIR.. EXECUTAR

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, consulte o artigo sobre tabelas temporárias para o pool de SQL do Synapse provisionado.

Limitations

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

Conteúdo relacionado

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