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

Tip

Microsoft Fabric Data Warehouse egy nagyvállalati szintű relációs raktár egy Data Lake-alaprendszeren, jövőre kész architektúrával, beépített AI-vel és új funkciókkal. Ha még nem ismerkedik adattárházzal, kezdje a Fabric Data Warehouse. A meglévő dedikált SQL-készlet számítási feladatai frissíthetők Fabric az adatelemzés, a valós idejű elemzés és a jelentéskészítés új képességeinek eléréséhez.

• A Fabric ingyenes próbaverzió indítása.
• Migration Assistant Fabric Data Warehouse.

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 való elhelyezé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özeli tárolására. A tárolt eljárások segítségével a fejlesztők a kód kezelhető egységekbe való beágyazásával és a kód nagyobb újrafelhasználhatóságával segítik a fejlesztőket a megoldások modularizálásában. Az egyes tárolt eljárások paramétereket is elfogadnak, hogy még rugalmasabbá tegyék ő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.

Mire számítsunk?

A Synapse SQL támogatja a SQL Server számos T-SQL-funkcióját. Ennél is fontosabb, hogy vannak vertikális felskálázási funkciók, amelyekkel maximalizálhatja a megoldás teljesítményét. 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ületének támogatott funkciói használhatók. 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észletekre vonatkozóan.

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 és más, nem támogatott funkciókkal rendelkeznek.

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

Az alábbi példában láthatja azokat az eljárásokat, amelyek külső objektumokat ejtenek, 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

Ezek az eljárások utasítással EXEC végrehajthatók, 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 tárolt eljárás megvalósítást biztosít. A legnagyobb különbség a SQL Server képest az, 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 nagy lekérdezésekhez. A cél az, hogy órákat, perceket és másodperceket takarítson meg, ne ezredmásodperceket. Ezért hasznosabb, ha a tárolt eljárásokat az SQL-logika tárolójaként tekinti.

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 a rendszer minden utasítást elosztott lekérdezésekké konvertál. 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 meghívnak más tárolt eljárásokat, 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 az alábbi kódban látható példa:

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 bizonyos 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 a beágyazási szint 1-nek 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ágyazottsági szint kettőre emelkedik.

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 ezt követően 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 fészek szintjét. Nem valószínű, hogy túllépi a nyolc fészekszintű korlátot, de ha mégis, át kell dolgoznia a kódot, hogy megfeleljen a korláton belüli beágyazási szinteknek.

INSERT..EXECUTE

A kiépített Synapse SQL-készlet nem teszi lehetővé, hogy insert utasítással használja fel a tárolt eljárás eredményhalmazá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

Az Transact-SQL tárolt eljárások bizonyos aspektusai nem implementálódnak a Synapse SQL-ben, például:

Funkció/opció	Provisioned	Kiszolgáló nélküli
Ideiglenes tárolt eljárások	Nem	Igen
Számozott tárolt eljárások	Nem	Nem
Kiterjesztett tárolt eljárások	Nem	Nem
CLR tárolt eljárások	Nem	Nem
Titkosítási beállítás	Nem	Igen
Replikációs lehetőség	Nem	Nem
Táblaértékkel megadott paraméterek	Nem	Nem
Írásvédett paraméterek	Nem	Nem
Alapértelmezett paraméterek	Nem	Igen
Végrehajtási környezetek	Nem	Nem
Visszatérési utasítás	Nem	Igen
INSERT INTO .. EXEC	Nem	Igen

Kapcsolódó tartalom

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