Procedure memorizzate che usano Synapse SQL in Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse è un data warehouse relazionale su scala aziendale su una base data lake, con un'architettura futura, un'intelligenza artificiale predefinita e nuove funzionalità. Se non si ha familiarità con il data warehousing, iniziare con Fabric Data Warehouse. I carichi di lavoro esistenti del pool SQL dedicated possono eseguire l'aggiornamento a Fabric per accedere a nuove funzionalità tra data science, analisi in tempo reale e creazione di report.

• Inizia una prova gratuita di Fabric.
• Assistente alla migrazione per Fabric Data Warehouse.

I pool provisionati e serverless di Synapse SQL consentono di inserire logiche complesse di elaborazione dati nelle procedure memorizzate SQL. Le stored procedure sono un ottimo modo per incapsulare il codice SQL e archiviarlo vicino ai dati nel data warehouse. Le stored procedure consentono agli sviluppatori di modularizzare le proprie soluzioni incapsulando il codice in unità gestibili e semplificando una maggiore riutilizzabilità del codice. Ogni stored procedure può anche accettare parametri per renderli ancora più flessibili. In questo articolo sono disponibili alcuni suggerimenti per l'implementazione di stored procedure nel pool Synapse SQL per lo sviluppo di soluzioni.

Cosa aspettarsi

Synapse SQL supporta molte delle funzionalità T-SQL usate in SQL Server. Più importante, sono disponibili funzionalità specifiche di scalabilità orizzontale che è possibile usare per ottimizzare le prestazioni della soluzione. In questo articolo verranno fornite informazioni sulle funzionalità che è possibile inserire nelle stored procedure.

Annotazioni

Nel corpo della procedura è possibile usare solo le funzionalità supportate nell'area di superficie di Synapse SQL. Esaminare questo articolo per identificare oggetti e istruzioni che possono essere usate nelle procedure memorizzate. Gli esempi in questi articoli utilizzano funzionalità generiche disponibili sia nell'area serverless che in quella dedicata. Consultare altre limitazioni nei pool SQL di Synapse provisioned e serverless alla fine di questo articolo.

Per mantenere la scalabilità e le prestazioni del pool SQL, esistono anche alcune funzionalità e funzionalità che presentano differenze comportamentali e altre che non sono supportate.

Stored procedures in Synapse SQL

Nell'esempio seguente è possibile visualizzare le 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

È possibile eseguire queste procedure usando l'istruzione EXEC in cui è possibile specificare il nome e i parametri della routine:

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 offre un'implementazione semplificata della stored procedure. La differenza principale rispetto a SQL Server è che la stored procedure non è codice precompilato. Nei data warehouse, il tempo di compilazione è ridotto rispetto al tempo necessario per eseguire query su volumi di dati di grandi dimensioni. È più importante assicurarsi che il codice della stored procedure sia ottimizzato correttamente per le query di grandi dimensioni. L'obiettivo è risparmiare ore, minuti e secondi, non millisecondi. È quindi più utile considerare le stored procedure come contenitori per la logica SQL.

Quando Synapse SQL esegue la stored procedure, le istruzioni SQL vengono analizzate, tradotte 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 vedere come convalidare i valori dei parametri e modificare 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 routine sql convaliderà i parametri di input quando viene chiamata la routine.

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 procedure memorizzate

Quando le procedure memorizzate chiamano altre procedure memorizzate o eseguono SQL dinamico, la procedura memorizzata interna o l'invocazione del codice viene definita come annidata. Un esempio di procedura nidificata è 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 procedura accetta un parametro che rappresenta un nome e quindi chiama altre procedure per eliminare gli oggetti con questo nome. Il pool Synapse SQL supporta un massimo di otto livelli di annidamento. Questa funzionalità è leggermente diversa da SQL Server. Il livello di annidamento in SQL Server è 32.

La chiamata di stored procedure al livello superiore corrisponde al livello di annidamento 1.

EXEC clean_up 'mytest'

Se la procedura memorizzata esegue anche un'altra chiamata EXEC, il livello di annidamento raggiunge il valore di 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 procedura esegue un codice SQL dinamico, il livello di annidamento aumenta 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

Annotazioni

Synapse SQL attualmente non supporta @@NESTLEVEL. È necessario tenere traccia del livello di annidamento. È improbabile che si superi il limite di otto livelli di annidamento, ma in caso affermativo, è necessario rielaborare il codice per adattare i livelli di annidamento entro questo limite.

INSERIRE.. ESEGUIRE

Il pool Synapse SQL con provisioning non permette di utilizzare il set di risultati di una stored procedure con un'istruzione INSERT. È possibile usare un approccio alternativo. Per un esempio, vedere l'articolo sulle tabelle temporanee per il pool Synapse SQL di cui è stato effettuato il provisioning.

Limitazioni

Esistono alcuni aspetti di Transact-SQL stored procedure non implementate in Synapse SQL, ad esempio:

Funzionalità/opzione	Sottoposto a provisioning	Serverless
Procedure memorizzate temporanee	No	Yes
Stored procedure numerate	No	No
Procedure memorizzate estese	No	No
Procedure memorizzate CLR	No	No
Opzione di crittografia	No	Yes
Opzione di replica	No	No
Parametri con valori di tabella	No	No
Parametri di sola lettura	No	No
Parametri predefiniti	No	Yes
Contesti di esecuzione	No	No
Istruzione Return	No	Yes
INSERT INTO .. EXEC	No	Yes

Contenuti correlati

Per altri suggerimenti sullo sviluppo, vedere Panoramica dello sviluppo.