Tárolt eljárások a Synapse SQL használatával a Azure Synapse Analyticsben

A Synapse SQL kiépített és kiszolgáló nélküli készletei lehetővé teszik összetett adatfeldolgozási logika SQL-ben tárolt eljárásokba helyezését. A tárolt eljárások kiválóan alkalmasak az SQL-kód beágyazására és az adattárház adataihoz közel történő tárolására. A tárolt eljárások segítenek a fejlesztőknek abban, hogy a kód kezelhető egységekbe való beágyazásával és a kód nagyobb újrafelhasználhatóságával modulárissá alakítsák megoldásaikat. Minden tárolt eljárás paramétereket is elfogad, hogy még rugalmasabbá tegye őket. Ebben a cikkben talál néhány tippet a tárolt eljárások Synapse SQL-készletben való implementálásához megoldások fejlesztéséhez.

Amire számíthat

A Synapse SQL számos T-SQL-funkciót támogat, amelyeket a SQL Server használ. Ennél is fontosabb, hogy a megoldás teljesítményének maximalizálása érdekében felskálázási funkciók érhetők el. Ebben a cikkben megismerheti a tárolt eljárásokban elhelyezhető funkciókat.

Megjegyzés

Az eljárás törzsében csak a Synapse SQL felületi területén támogatott funkciókat használhatja. Tekintse át ezt a cikket a tárolt eljárásokban használható objektumok és utasítások azonosításához. Az ezekben a cikkekben szereplő példák általános funkciókat használnak, amelyek kiszolgáló nélküli és dedikált felületen is elérhetők. A cikk végén további korlátozásokat talál a kiépített és kiszolgáló nélküli Synapse SQL-készletekben .

Az SQL-készlet skálájának és teljesítményének fenntartása érdekében vannak olyan funkciók és funkciók is, amelyek viselkedési eltérésekkel rendelkeznek, és mások, amelyek nem támogatottak.

Tárolt eljárások a Synapse SQL-ben

Az alábbi példában láthatja azokat az eljárásokat, amelyek elvetik a külső objektumokat, ha azok az adatbázisban léteznek:

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

Ezeket az eljárásokat utasítással EXEC lehet végrehajtani, ahol megadhatja az eljárás nevét és paramétereit:

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

A Synapse SQL egyszerűsített és egyszerűsített tárolt eljárás implementálást biztosít. A legnagyobb különbség az SQL Server képest, hogy a tárolt eljárás nem előre lefordított kód. Az adattárházakban a fordítási idő kicsi a nagy adatmennyiségeken végzett lekérdezések futtatásához szükséges időhöz képest. Sokkal fontosabb, hogy a tárolt eljáráskód megfelelően legyen optimalizálva a nagy lekérdezésekhez. A cél órák, percek és másodpercek mentése, nem ezredmásodperc. Ezért hasznosabb, ha a tárolt eljárásokat az SQL-logika tárolóinak tekintik.

Amikor a Synapse SQL végrehajtja a tárolt eljárást, a rendszer futtatáskor elemzi, lefordítja és optimalizálja az SQL-utasításokat. A folyamat során minden utasítás elosztott lekérdezésekké lesz konvertálva. Az adatokon végrehajtott SQL-kód eltér az elküldött lekérdezésétől.

Érvényesítési szabályok beágyazása

A tárolt eljárások lehetővé teszik az érvényesítési logika megkeresését egyetlen, AZ SQL-adatbázisban tárolt modulban. Az alábbi példában megtudhatja, hogyan érvényesítheti a paraméterek értékeit, és hogyan módosíthatja az alapértelmezett értékeket.

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

Az sql-eljárás logikája ellenőrzi a bemeneti paramétereket az eljárás meghívásakor.

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.

Tárolt eljárások beágyazása

Ha a tárolt eljárások más tárolt eljárásokat hívnak meg, vagy dinamikus SQL-t hajtanak végre, akkor a rendszer azt mondja, hogy a belső tárolt eljárás vagy kódhívás beágyazott. A beágyazott eljárásra egy példa látható a következő kódban:

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

Ez az eljárás elfogad egy nevet képviselő paramétert, majd meghív más eljárásokat az ilyen nevű objektumok elvetésére. A Synapse SQL-készlet legfeljebb nyolc beágyazási szintet támogat. Ez a képesség kissé eltér a SQL Server. A SQL Server fészekszintje 32.

A legfelső szintű tárolt eljáráshívás az 1. beágyazott szintnek felel meg.

EXEC clean_up 'mytest'

Ha a tárolt eljárás egy másik EXEC-hívást is kezdeményez, a beágyazási szint kettőre nő.

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

Ha a második eljárás dinamikus SQL-t hajt végre, a beágyazás szintje háromra nő.

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

Megjegyzés

A Synapse SQL jelenleg nem támogatja a @@NESTLEVEL. Nyomon kell követnie a beágyazás szintjét. Nem valószínű, hogy túllépi a nyolc beágyazási szint korlátját, de ha igen, át kell dolgoznia a kódot, hogy megfeleljen a beágyazási szinteknek ezen a korláton belül.

BESZÚRÁSA.. VÉGRE

A kiépített Synapse SQL-készlet nem teszi lehetővé egy tárolt eljárás eredményhalmazának az INSERT utasítással való felhasználását. Van egy alternatív módszer, amelyet használhat. Például tekintse meg a kiépített Synapse SQL-készlet ideiglenes tábláiról szóló cikket.

Korlátozások

A Transact-SQL tárolt eljárásainak néhány olyan aspektusa van, amelyek nincsenek implementálva a Synapse SQL-ben, például:

Funkció/lehetőség	Kiépítve	Kiszolgáló nélküli
Ideiglenesen tárolt eljárások	Nem	Igen
Számozott tárolt eljárások	Nem	Nem
Bővített tárolt eljárások	Nem	Nem
CLR tárolt eljárások	Nem	Nem
Titkosítási lehetőség	Nem	Igen
Replikációs beállítás	Nem	Nem
Ideiglenes értékű paraméterek	Nem	Nem
Írásvédett paraméterek	Nem	Nem
Alapértelmezett paraméterek	Nem	Igen
Végrehajtási környezetek	Nem	Nem
Return utasítás	Nem	Igen
SZÚRJA BE A ..-BE. EXEC	Nem	Igen

Következő lépések

További fejlesztési tippekért tekintse meg a fejlesztés áttekintését ismertető cikket.