Lagrade procedurer med Synapse SQL i Azure Synapse Analytics

Med synapse SQL-etablerade och serverlösa pooler kan du placera komplex databearbetningslogik i SQL-lagrade procedurer. Lagrade procedurer är ett bra sätt att kapsla in SQL-koden och lagra den nära dina data i informationslagret. Lagrade procedurer hjälper utvecklare att modularisera sina lösningar genom att kapsla in koden i hanterbara enheter och underlätta bättre återanvändning av kod. Varje lagrad procedur kan också acceptera parametrar för att göra dem ännu mer flexibla. I den här artikeln hittar du några tips för att implementera lagrade procedurer i Synapse SQL-poolen för att utveckla lösningar.

Vad du kan förvänta dig

Synapse SQL stöder många av de T-SQL-funktioner som används i SQL Server. Ännu viktigare är att det finns utskalningsspecifika funktioner som du kan använda för att maximera lösningens prestanda. I den här artikeln får du lära dig mer om de funktioner som du kan placera i lagrade procedurer.

Anteckning

I procedurtexten kan du bara använda de funktioner som stöds i Synapse SQL-ytan. Läs den här artikeln om du vill identifiera objekt, instruktioner som kan användas i lagrade procedurer. Exemplen i dessa artiklar använder allmänna funktioner som är tillgängliga både i serverlös och dedikerad yta. Se ytterligare begränsningar i etablerade och serverlösa Synapse SQL-pooler i slutet av den här artikeln.

För att upprätthålla skalning och prestanda för SQL-pool finns det även vissa funktioner som har beteendeskillnader och andra som inte stöds.

Lagrade procedurer i Synapse SQL

I följande exempel kan du se procedurerna som släpper externa objekt om de finns i databasen:

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

Dessa procedurer kan köras med instruktionen EXEC där du kan ange procedurens namn och parametrar:

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 tillhandahåller en förenklad och strömlinjeformad implementering av lagrade procedurer. Den största skillnaden jämfört med SQL Server är att den lagrade proceduren inte är förkompilerad kod. I informationslager är kompileringstiden liten jämfört med den tid det tar att köra frågor mot stora datavolymer. Det är viktigare att se till att koden för lagrad procedur är korrekt optimerad för stora frågor. Målet är att spara timmar, minuter och sekunder, inte millisekunder. Det är därför mer användbart att tänka på lagrade procedurer som containrar för SQL-logik.

När Synapse SQL kör den lagrade proceduren parsas, översätts och optimeras SQL-uttrycken vid körning. Under den här processen konverteras varje instruktion till distribuerade frågor. SQL-koden som körs mot data skiljer sig från den fråga som skickas.

Kapsla in verifieringsregler

Med lagrade procedurer kan du hitta valideringslogik i en enda modul som lagras i SQL Database. I följande exempel kan du se hur du verifierar parametrarnas värden och ändrar deras standardvärden.

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

Logiken i SQL-proceduren validerar indataparametrarna när proceduren anropas.

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.

Kapsling av lagrade procedurer

När lagrade procedurer anropar andra lagrade procedurer, eller kör dynamisk SQL, sägs den inre lagrade proceduren eller kodanropet vara kapslat. Ett exempel på kapslad procedur visas i följande kod:

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

Den här proceduren accepterar en parameter som representerar ett visst namn och anropar sedan andra procedurer för att släppa objekten med det här namnet. Synapse SQL-poolen stöder högst åtta kapslingsnivåer. Den här funktionen skiljer sig något från SQL Server. Kapslingsnivån i SQL Server är 32.

Anropet för den lagrade proceduren på den översta nivån motsvarar kapslingsnivå 1.

EXEC clean_up 'mytest'

Om den lagrade proceduren också gör ytterligare ett EXEC-anrop ökar kapslingsnivån till två.

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

Om den andra proceduren sedan kör en dynamisk SQL ökar kapslingsnivån till 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

Anteckning

Synapse SQL stöder för närvarande inte @@NESTLEVEL. Du måste spåra kapslingsnivån. Det är osannolikt att du överskrider gränsen på åtta kapslingsnivåer, men om du gör det måste du omarbeta koden så att den passar kapslingsnivåerna inom den här gränsen.

INFOGA.. UTFÖRA

Med den etablerade Synapse SQL-poolen kan du inte använda resultatuppsättningen för en lagrad procedur med en INSERT-instruktion. Det finns en alternativ metod som du kan använda. Ett exempel finns i artikeln om temporära tabeller för etablerad Synapse SQL-pool.

Begränsningar

Det finns vissa aspekter av transact-SQL-lagrade procedurer som inte implementeras i Synapse SQL, till exempel:

Funktion/alternativ	Etablerad	Utan server
Tillfälligt lagrade procedurer	Inga	Ja
Numrerade lagrade procedurer	Inga	Inga
Utökade lagrade procedurer	Inga	Inga
LAGRADE CLR-procedurer	Inga	Inga
Krypteringsalternativ	Inga	Ja
Replikeringsalternativ	Inga	Inga
Tabellvärdesparametrar	Inga	Inga
Skrivskyddade parametrar	Inga	Inga
Standardparametrar	Inga	Ja
Körningskontexter	Inga	Inga
Returuttryck	Inga	Ja
INFOGA I .. EXEC	Inga	Ja

Nästa steg

Fler utvecklingstips finns i Utvecklingsöversikt.