Opgeslagen procedures met Synapse SQL in Azure Synapse Analytics

Met Synapse SQL ingerichte en serverloze pools kunt u complexe logica voor gegevensverwerking in opgeslagen SQL-procedures plaatsen. Opgeslagen procedures zijn een uitstekende manier om uw SQL-code in te kapselen en dicht bij uw gegevens in het datawarehouse op te slaan. Met opgeslagen procedures kunnen ontwikkelaars hun oplossingen modulariseren door de code in te kapselen in beheerbare eenheden en de herbruikbaarheid van code te vergroten. Elke opgeslagen procedure kan ook parameters accepteren om ze nog flexibeler te maken. In dit artikel vindt u enkele tips voor het implementeren van opgeslagen procedures in Synapse SQL-pool voor het ontwikkelen van oplossingen.

Wat u kunt verwachten

Synapse SQL ondersteunt veel van de T-SQL-functies die worden gebruikt in SQL Server. Nog belangrijker is dat er specifieke functies voor uitschalen zijn die u kunt gebruiken om de prestaties van uw oplossing te maximaliseren. In dit artikel vindt u informatie over de functies die u in opgeslagen procedures kunt plaatsen.

Notitie

In de hoofdtekst van de procedure kunt u alleen de functies gebruiken die worden ondersteund in synapse SQL surface area. Raadpleeg dit artikel om objecten te identificeren, een instructie die kan worden gebruikt in opgeslagen procedures. In de voorbeelden in deze artikelen worden algemene functies gebruikt die beschikbaar zijn in zowel serverloze als toegewezen surface area. Zie aanvullende beperkingen in ingerichte en serverloze Synapse SQL-pools aan het einde van dit artikel.

Om de schaal en prestaties van de SQL-pool te behouden, zijn er ook enkele functies en functionaliteit met gedragsverschillen en andere die niet worden ondersteund.

Opgeslagen procedures in Synapse SQL

In het volgende voorbeeld ziet u de procedures waarmee externe objecten worden verwijderd als deze aanwezig zijn in de 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

Deze procedures kunnen worden uitgevoerd met behulp van de EXEC instructie, waarbij u de naam en parameters van de procedure kunt opgeven:

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 biedt een vereenvoudigde en gestroomlijnde implementatie van opgeslagen procedures. Het grootste verschil met SQL Server is dat de opgeslagen procedure geen vooraf gecompileerde code is. In datawarehouses is de compilatietijd klein in vergelijking met de tijd die nodig is om query's uit te voeren op grote gegevensvolumes. Het is belangrijker om ervoor te zorgen dat de opgeslagen procedurecode correct is geoptimaliseerd voor grote query's. Het doel is om uren, minuten en seconden op te slaan, niet milliseconden. Het is daarom handiger om opgeslagen procedures te beschouwen als containers voor SQL-logica.

Wanneer Synapse SQL uw opgeslagen procedure uitvoert, worden de SQL-instructies tijdens runtime geparseerd, vertaald en geoptimaliseerd. Tijdens dit proces wordt elke instructie geconverteerd naar gedistribueerde query's. De SQL-code die wordt uitgevoerd op basis van de gegevens, is anders dan de query die is verzonden.

Validatieregels inkapselen

Met opgeslagen procedures kunt u validatielogica vinden in één module die is opgeslagen in de SQL-database. In het volgende voorbeeld ziet u hoe u de waarden van parameters kunt valideren en de standaardwaarden ervan kunt wijzigen.

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

De logica in de SQL-procedure valideert de invoerparameters wanneer de procedure wordt aangeroepen.

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.

Opgeslagen procedures nesten

Wanneer opgeslagen procedures andere opgeslagen procedures aanroepen of dynamische SQL uitvoeren, wordt de interne opgeslagen procedure of codeaanroep genest. Een voorbeeld van geneste procedure wordt weergegeven in de volgende code:

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

Deze procedure accepteert een parameter die een bepaalde naam vertegenwoordigt en roept vervolgens andere procedures aan om de objecten met deze naam te verwijderen. Synapse SQL-pool ondersteunt maximaal acht geneste niveaus. Deze mogelijkheid is iets anders dan SQL Server. Het nestniveau in SQL Server is 32.

De aanroep van de opgeslagen procedure op het hoogste niveau is gelijk aan nestniveau 1.

EXEC clean_up 'mytest'

Als de opgeslagen procedure ook nog een EXEC-aanroep doet, wordt het nestniveau verhoogd naar twee.

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

Als met de tweede procedure vervolgens een dynamische SQL wordt uitgevoerd, wordt het nestniveau verhoogd naar drie.

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

Notitie

Synapse SQL biedt momenteel geen ondersteuning voor @@NESTLEVEL. U moet het nestniveau bijhouden. Het is onwaarschijnlijk dat u de limiet van acht nestniveaus overschrijdt, maar als u dat wel doet, moet u de code aanpassen aan de nestniveaus binnen deze limiet.

INVOEGEN.. UITVOEREN

Met de ingerichte Synapse SQL-pool kunt u de resultatenset van een opgeslagen procedure met een INSERT-instructie niet gebruiken. Er is een alternatieve methode die u kunt gebruiken. Zie voor een voorbeeld het artikel over tijdelijke tabellen voor ingerichte Synapse SQL-pool.

Beperkingen

Er zijn enkele aspecten van opgeslagen Transact-SQL-procedures die niet zijn geïmplementeerd in Synapse SQL, zoals:

Functie/optie	Ingericht	Serverloos
Tijdelijke opgeslagen procedures	Nee	Ja
Genummerde opgeslagen procedures	Nee	Nee
Uitgebreide opgeslagen procedures	Nee	Nee
Opgeslagen CLR-procedures	Nee	Nee
Versleutelingsoptie	Nee	Ja
Replicatie-optie	Nee	Nee
Tabelwaardeparameters	Nee	Nee
Alleen-lezenparameters	Nee	Nee
Standaardparameters	Nee	Ja
Uitvoeringscontexten	Nee	Nee
Retourinstructie	Nee	Ja
INVOEGEN IN .. UITVOEREND	Nee	Ja

Volgende stappen

Zie Overzicht van ontwikkeling voor meer tips voor ontwikkeling.