Procedimentos armazenados com o SQL do Synapse no Azure Synapse Analytics

Os conjuntos aprovisionados e sem servidor do Synapse SQL permitem-lhe colocar lógica de processamento de dados complexa nos procedimentos armazenados do SQL. Os procedimentos armazenados são uma ótima forma de encapsular o código SQL e armazená-lo perto dos seus dados no armazém de dados. Os procedimentos armazenados ajudam os programadores a modularizar as suas soluções ao encapsularem o código em unidades geríveis e facilitarem 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, encontrará algumas sugestões para implementar procedimentos armazenados no conjunto de SQL do Synapse para desenvolver soluções.

O que esperar

O Synapse SQL suporta muitas das funcionalidades T-SQL utilizadas no SQL Server. Mais importante ainda, existem funcionalidades específicas de aumento horizontal que pode utilizar para maximizar o desempenho da sua solução. Neste artigo, irá saber mais sobre as funcionalidades que pode colocar nos procedimentos armazenados.

Para manter a escala e o desempenho do conjunto de SQL, existem também algumas funcionalidades que têm diferenças comportamentais e outras que não são suportadas.

Procedimentos armazenados no SQL do Synapse

No exemplo seguinte, pode ver os procedimentos que deixam cair objetos externos se existirem na base 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

Estes procedimentos podem ser executados com a EXEC instrução onde pode especificar o nome e os parâmetros do procedimento:

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

O Synapse SQL fornece uma implementação simplificada e simplificada do procedimento armazenado. A maior diferença em comparação com SQL Server é que o procedimento armazenado não é código pré-compilado. Nos armazéns de dados, o tempo de compilação é pequeno em comparação com o tempo que demora a executar consultas em grandes volumes de dados. É mais importante garantir que o código do procedimento armazenado está corretamente otimizado para consultas grandes. O objetivo é poupar horas, minutos e segundos, não milissegundos. Por conseguinte, é mais útil pensar nos procedimentos armazenados como contentores para a lógica do SQL.

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

Encapsular regras de validação

Os procedimentos armazenados permitem-lhe localizar a lógica de validação num único módulo armazenado na base de dados SQL. No exemplo seguinte, pode ver como validar os valores dos parâmetros e alterar os respetivos valores predefinidos.

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.

Aninhar procedimentos armazenados

Quando os procedimentos armazenados chamam outros procedimentos armazenados ou executam SQL dinâmico, o procedimento armazenado interno ou invocação de código é considerado aninhado. É apresentado um exemplo de procedimento aninhado 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

Este procedimento aceita um parâmetro que representa algum nome e, em seguida, chama outros procedimentos para remover os objetos com este nome. O conjunto de SQL do Synapse suporta um máximo de oito níveis de aninhamento. Esta capacidade é ligeiramente diferente da SQL Server. O nível de ninho no SQL Server é 32.

A chamada de procedimento armazenado de nível superior equivale ao nível 1 do aninhamento.

EXEC clean_up 'mytest'

Se o procedimento armazenado também fizer outra chamada EXEC, o nível de ninho aumenta 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 ninho aumenta 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

INSERT.. EXECUTAR

O conjunto de SQL do Synapse aprovisionado não lhe permite consumir o conjunto de resultados de um procedimento armazenado com uma instrução INSERT. Existe uma abordagem alternativa que pode utilizar. Por exemplo, veja o artigo sobre tabelas temporárias para o conjunto de SQL do Synapse aprovisionado.

Limitações

Existem alguns aspetos dos procedimentos armazenados transact-SQL que não são implementados no Synapse SQL, tais como:

Próximos passos

Para obter mais sugestões de desenvolvimento, veja Descrição geral do desenvolvimento.