Uložené procedury využívající Synapse SQL v Azure Synapse Analytics

Článek
06/01/2023

Zřízené fondy Synapse SQL a bezserverové fondy umožňují umístit do uložených procedur SQL složitou logiku zpracování dat. Uložené procedury představují skvělý způsob zapouzdření kódu SQL a jeho uložení v blízkosti dat v datovém skladu. Uložené procedury pomáhají vývojářům modularizovat jejich řešení tím, že kód zapouzdřují do spravovatelných jednotek a usnadňují větší opakované použití kódu. Každá uložená procedura může také přijímat parametry, aby byly ještě flexibilnější. V tomto článku najdete několik tipů pro implementaci uložených procedur ve fondu Synapse SQL pro vývoj řešení.

Co očekávat

Synapse SQL podporuje mnoho funkcí T-SQL, které se používají v SQL Server. Ještě důležitější je, že existují funkce specifické pro horizontální navýšení kapacity, které můžete použít k maximalizaci výkonu vašeho řešení. V tomto článku se dozvíte o funkcích, které můžete umístit do uložených procedur.

Poznámka

V těle procedury můžete použít pouze funkce, které jsou podporovány v oblasti plochy Synapse SQL. Projděte si tento článek a identifikujte objekty, příkaz, který lze použít v uložených procedurách. Příklady v těchto článcích používají obecné funkce, které jsou k dispozici v bezserverové i vyhrazené oblasti surface. Další omezení ve zřízených a bezserverových fondech Synapse SQL najdete na konci tohoto článku.

Kvůli zachování škálování a výkonu fondu SQL existují také některé funkce, které mají rozdíly v chování, a jiné, které nejsou podporované.

Uložené procedury v Synapse SQL

V následujícím příkladu můžete vidět postupy, které odstraňují externí objekty, pokud existují v databázi:

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

Tyto postupy lze provést pomocí EXEC příkazu, kde můžete zadat název a parametry procedury:

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 poskytuje zjednodušenou a zjednodušenou implementaci uložených procedur. Největší rozdíl oproti SQL Server spočívá v tom, že uložená procedura není předkompilovaný kód. V datových skladech je doba kompilace malá v porovnání s dobou potřebnou ke spouštění dotazů na velké objemy dat. Důležitější je zajistit, aby byl kód uložené procedury správně optimalizovaný pro velké dotazy. Cílem je ušetřit hodiny, minuty a sekundy, ne milisekundy. Proto je užitečnější uvažovat o uložených procedurách jako o kontejnerech pro logiku SQL.

Když Synapse SQL spustí uloženou proceduru, příkazy SQL se analyzují, překládají a optimalizují za běhu. Během tohoto procesu se každý příkaz převede na distribuované dotazy. Kód SQL, který je spuštěn proti datům, se liší od odeslaného dotazu.

Zapouzdření ověřovacích pravidel

Uložené procedury umožňují najít logiku ověřování v jednom modulu uloženém v databázi SQL. V následujícím příkladu se můžete podívat, jak ověřit hodnoty parametrů a změnit jejich výchozí hodnoty.

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

Logika v proceduře SQL ověří vstupní parametry při volání procedury.


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.

Vnoření uložených procedur

Když uložené procedury volají jiné uložené procedury nebo spouštějí dynamické SQL, znamená to, že vnitřní uložená procedura nebo vyvolání kódu je vnořené. Příklad vnořené procedury je znázorněn v následujícím kódu:

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

Tato procedura přijímá parametr, který představuje nějaký název, a pak volá další procedury, aby se objekty s tímto názvem vyřadily. Fond Synapse SQL podporuje maximálně osm úrovní vnoření. Tato funkce se mírně liší od SQL Server. Úroveň vnoření v SQL Server je 32.

Volání uložené procedury nejvyšší úrovně odpovídá vnoření úrovně 1.

EXEC clean_up 'mytest'

Pokud uložená procedura také provede další volání EXEC, úroveň vnoření se zvýší na dvě.

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

Pokud druhá procedura pak spustí nějaký dynamický SQL, úroveň vnoření se zvýší na tři.

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

Poznámka

Synapse SQL v současné době nepodporuje @@NESTLEVEL. Musíte sledovat úroveň hnízda. Je nepravděpodobné, že byste překročili limit úrovně osm vnoření, ale pokud ano, budete muset přepracovat kód tak, aby odpovídal úrovním vnořování v rámci tohoto limitu.

VLOŽIT.. PROVÉST

Zřízený fond Synapse SQL neumožňuje využívat sadu výsledků uložené procedury pomocí příkazu INSERT. Existuje alternativní přístup, který můžete použít. Příklad najdete v článku o dočasných tabulkách pro zřízený fond Synapse SQL.

Omezení

Existují některé aspekty uložených procedur Jazyka Transact-SQL, které nejsou implementované v Synapse SQL, například:

Funkce/možnost	Zřízené	Bez serveru
Dočasné uložené procedury	No	Yes
Číslování uložených procedur	No	No
Rozšířené uložené procedury	No	No
Uložené procedury CLR	No	No
Možnost šifrování	No	Yes
Možnost replikace	No	No
Parametry vracející tabulku	No	No
Parametry jen pro čtení	No	No
Výchozí parametry	No	Yes
Kontexty spuštění	No	No
Return – příkaz	No	Yes
VLOŽIT DO .. EXEC	No	Ano

Další kroky

Další tipy pro vývoj najdete v přehledu vývoje.

Sdílet prostřednictvím