Stored procedure con Synapse SQL in Azure Synapse Analytics

I pool con provisioning e serverless di Synapse SQL consentono di inserire una logica di elaborazione dati complessa nelle stored procedure SQL. Le stored procedure sono un ottimo modo per incapsulare il codice SQL, archiviandolo vicino i dati nel data warehouse. Le stored procedure consentono agli sviluppatori di rendere modulari le soluzioni incapsulando il codice in unità gestibili, facilitando così il riutilizzo del codice stesso. Ogni stored procedure può anche accettare parametri per essere ancora più flessibile. In questo articolo sono disponibili alcuni suggerimenti per l'implementazione di stored procedure nel pool SQL di Synapse per lo sviluppo di soluzioni.

Risultati previsti

Synapse SQL supporta molte delle funzionalità T-SQL che si usano in SQL Server. Ancora più importanti sono le funzionalità di scale-out specifiche, che si possono usare per migliorare le prestazioni della soluzione. In questo articolo verranno fornite informazioni sulle funzionalità che è possibile inserire nelle stored procedure.

Nota

Nel corpo della stored procedure è possibile usare solo le funzionalità supportate nella superficie di attacco di Synapse SQL. Vedere questo articolo identificare gli oggetti, ovvero le istruzioni che si possono usare nelle stored procedure. Gli esempi in questi articoli usano funzionalità generiche disponibili sia nella superficie di attacco serverless che in quella dedicata. Vedere altre limitazioni nei pool SQL con provisioning e serverless di Synapse alla fine di questo articolo.

Per mantenere la scalabilità e le prestazioni del pool SQL sono disponibili anche funzionalità e caratteristiche con differenze di comportamento e altre che non sono supportate.

Stored procedure in Synapse SQL

Nell'esempio seguente è possibile visualizzare le stored procedure che rilasciano oggetti esterni, se presenti nel database:

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

Queste stored procedure possono essere eseguite usando l'istruzione EXEC in cui è possibile specificare il nome e i parametri della stored procedure:

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

Synapse SQL fornisce un'implementazione semplificata e ottimizzata delle stored procedure. La differenza principale rispetto a SQL Server è che la stored procedure non è codice precompilato. Nei data warehouse il tempo di compilazione è limitato rispetto al tempo necessario per eseguire query su grandi volumi di dati. È più importante assicurarsi che il codice della stored procedure sia correttamente ottimizzato per le query di grandi dimensioni. L'obiettivo consiste nel risparmiare ore, minuti e secondi, non millisecondi. È quindi più utile pensare alle stored procedure come contenitori per la logica di SQL.

Quando Synapse SQL esegue la stored procedure, le istruzioni SQL vengono analizzate, convertite e ottimizzate in fase di esecuzione. Durante questo processo ogni istruzione viene convertita in query distribuite. Il codice SQL eseguito sui dati è diverso dalla query inviata.

Incapsulare le regole di convalida

Le stored procedure consentono di individuare la logica di convalida in un singolo modulo archiviato nel database SQL. Nell'esempio seguente è possibile osservare come convalidare i valori dei parametri e modificarne i valori predefiniti.

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

La logica nella stored procedure SQL convaliderà i parametri di input quando viene chiamata la stored procedure.

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.

Annidamento di stored procedure

Quando le stored procedure chiamano altre stored procedure o eseguono istruzioni SQL dinamiche, la stored procedure o la chiamata di codice interna è detta annidata. Un esempio di stored procedure annidata è illustrato nel codice seguente:

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

Questa stored procedure accetta un parametro che rappresenta un nome e quindi chiama altre stored procedure per eliminare gli oggetti con questo nome. Il pool SQL di Synapse supporta un massimo di otto livelli di annidamento. Questa funzionalità è leggermente diversa da SQL Server. In SQL Server i livelli di annidamento sono 32.

La chiamata di stored procedure di massimo livello equivale al livello di annidamento 1.

EXEC clean_up 'mytest'

Se la stored procedure effettua anche un'altra chiamata EXEC, il livello di annidamento aumenta a due.

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 la seconda routine esegue poi istruzioni in SQL dinamico, il livello di annidamento aumenterà a tre.

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

Synapse SQL attualmente non supporta @@NESTLEVEL. È necessario tenere traccia del livello di annidamento. È improbabile superare il limite di otto livelli di annidamento, ma se lo si fa, è necessario rielaborare il codice per adattare i livelli di annidamento entro tale limite.

INSERT..EXECUTE

Il pool SQL con provisioning di Synapse non consente di usare il set di risultati di una stored procedure con un'istruzione INSERT. Si può tuttavia usare un approccio alternativo. Per un esempio, vedere l'articolo sulle tabelle temporanee per i pool SQL con provisioning di Synapse.

Limiti

Esistono alcuni aspetti delle stored procedure Transact-SQL che non sono implementati in Synapse SQL, ad esempio:

Funzionalità/Opzione	Sottoposto a provisioning	Senza server
Stored procedure temporanee	No	Sì
Stored procedure numerate	No	No
Stored procedure estese	No	No
Stored procedure CLR	No	No
Opzione di crittografia	No	Sì
Opzione di replica	No	No
Parametri con valori di tabella	No	No
Parametri di sola lettura	No	No
Parametri predefiniti	No	Sì
Contesti di esecuzione	No	No
Istruzione return	No	Sì
INSERT INTO... EXEC	No	Sì

Passaggi successivi

Per altri suggerimenti sullo sviluppo, vedere la panoramica dello sviluppo.