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.

Nota

No corpo do procedimento, só pode utilizar as funcionalidades suportadas na área da superfície SQL do Synapse. Reveja este artigo para identificar objetos, instrução que pode ser utilizada em procedimentos armazenados. Os exemplos nestes artigos utilizam funcionalidades genéricas que estão disponíveis na área de superfície sem servidor e dedicada. Veja as limitações adicionais nos conjuntos de SQL do Synapse aprovisionados e sem servidor no final deste artigo.

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

Nota

Atualmente, o Synapse SQL não suporta @@NESTLEVEL. Tem de controlar o nível do ninho. É pouco provável que exceda o limite de oito níveis de ninho, mas se o fizer, terá de reformular o código para se ajustar aos níveis de aninhamento dentro deste limite.

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:

Funcionalidade/opção	Aprovisionado	Sem servidor
Procedimentos armazenados temporários	No	Yes
Procedimentos armazenados numerados	No	No
Procedimentos armazenados expandidos	No	No
Procedimentos armazenados clR	No	No
Opção de encriptação	No	Yes
Opção de replicação	No	No
Parâmetros de valor de tabela	No	No
Parâmetros só de leitura	No	No
Parâmetros predefinidos	No	Yes
Contextos de execução	No	No
Declaração de devolução	No	Yes
INSERIR EM .. EXEC	No	Sim

Próximos passos

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