Procedury składowane korzystające z usługi Synapse SQL w usłudze Azure Synapse Analytics

Artykuł
06/01/2023

Pule aprowizowane i bezserwerowe usługi Synapse SQL umożliwiają umieszczenie złożonej logiki przetwarzania danych w procedurach składowanych SQL. Procedury składowane to doskonały sposób na hermetyzowanie kodu SQL i przechowywanie go blisko danych w magazynie danych. Procedury składowane pomagają deweloperom modularyzować swoje rozwiązania, hermetyzując kod w możliwe do zarządzania jednostki i ułatwiając ponowne zastosowanie kodu. Każda procedura składowana może również akceptować parametry, aby uczynić je jeszcze bardziej elastycznymi. W tym artykule znajdziesz kilka wskazówek dotyczących implementowania procedur składowanych w puli Synapse SQL na potrzeby opracowywania rozwiązań.

Czego oczekiwać

Usługa Synapse SQL obsługuje wiele funkcji języka T-SQL, które są używane w SQL Server. Co ważniejsze, istnieją funkcje specyficzne dla skalowania w poziomie, których można użyć, aby zmaksymalizować wydajność rozwiązania. W tym artykule poznasz funkcje, które można umieścić w procedurach składowanych.

Uwaga

W treści procedury można używać tylko tych funkcji, które są obsługiwane w obszarze powierzchni synapse SQL. Zapoznaj się z tym artykułem , aby zidentyfikować obiekty, instrukcję, która może być używana w procedurach składowanych. W przykładach w tych artykułach są używane funkcje ogólne, które są dostępne zarówno w obszarze powierzchni bezserwerowej, jak i dedykowanej. Zobacz dodatkowe ograniczenia dotyczące aprowizowania i bezserwerowych pul SQL usługi Synapse na końcu tego artykułu.

Aby zachować skalę i wydajność puli SQL, istnieją również pewne funkcje, które mają różnice behawioralne i inne, które nie są obsługiwane.

Procedury składowane w usłudze Synapse SQL

W poniższym przykładzie przedstawiono procedury, które porzucają obiekty zewnętrzne, jeśli istnieją w bazie danych:

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

Te procedury można wykonać przy użyciu EXEC instrukcji , w której można określić nazwę i 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';

Usługa Synapse SQL zapewnia uproszczoną i usprawnianą implementację procedur składowanych. Największą różnicą w porównaniu z SQL Server jest to, że procedura składowana nie jest wstępnie skompilowanym kodem. W magazynach danych czas kompilacji jest niewielki w porównaniu z czasem potrzebnym na uruchamianie zapytań względem dużych woluminów danych. Ważniejsze jest upewnienie się, że kod procedury składowanej jest poprawnie zoptymalizowany pod kątem dużych zapytań. Celem jest zapisanie godzin, minut i sekund, a nie milisekund. W związku z tym bardziej pomocne jest myślenie o procedurach składowanych jako kontenerach dla logiki SQL.

Gdy usługa Synapse SQL wykonuje procedurę składowaną, instrukcje SQL są analizowane, tłumaczone i optymalizowane w czasie wykonywania. Podczas tego procesu każda instrukcja jest konwertowana na zapytania rozproszone. Kod SQL wykonywany względem danych różni się od przesłanego zapytania.

Hermetyzowanie reguł weryfikacji

Procedury składowane umożliwiają lokalizowanie logiki walidacji w jednym module przechowywanym w bazie danych SQL. W poniższym przykładzie można zobaczyć, jak zweryfikować wartości parametrów i zmienić ich wartości domyślne.

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 w procedurze SQL zweryfikuje parametry wejściowe po wywołaniu 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.

Zagnieżdżanie procedur składowanych

Gdy procedury składowane wywołają inne procedury składowane lub wykonują dynamiczną metodę SQL, mówi się, że wewnętrzna procedura składowana lub wywołanie kodu są zagnieżdżone. Przykład procedury zagnieżdżonej przedstawiono w następującym kodzie:

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

Ta procedura akceptuje parametr reprezentujący nazwę, a następnie wywołuje inne procedury, aby usunąć obiekty o tej nazwie. Pula SQL usługi Synapse obsługuje maksymalnie osiem poziomów zagnieżdżania. Ta funkcja jest nieco inna niż SQL Server. Poziom zagnieżdżenia w SQL Server wynosi 32.

Wywołanie procedury składowanej najwyższego poziomu odpowiada poziomowi zagnieżdżenia 1.

EXEC clean_up 'mytest'

Jeśli procedura składowana wykonuje również inne wywołanie EXEC, poziom zagnieżdżenia zwiększa się do dwóch.

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

Jeśli druga procedura wykonuje dynamiczny kod SQL, poziom zagnieżdżenia wzrasta do trzech.

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

Uwaga

Usługa Synapse SQL nie obsługuje obecnie @@NESTLEVEL. Musisz śledzić poziom zagnieżdżania. Jest mało prawdopodobne, aby przekroczyć limit poziomu ośmiu zagnieżdżeń, ale jeśli to zrobisz, musisz przerobić kod, aby dopasować poziomy zagnieżdżenia w ramach tego limitu.

WSTAWIĆ.. WYKONAĆ

Aprowizowana pula SQL usługi Synapse nie zezwala na korzystanie z zestawu wyników procedury składowanej za pomocą instrukcji INSERT. Istnieje alternatywne podejście, którego można użyć. Aby zapoznać się z przykładem, zobacz artykuł dotyczący tabel tymczasowych dla aprowizowanej puli SQL usługi Synapse.

Ograniczenia

Istnieją pewne aspekty procedur składowanych języka Transact-SQL, które nie są implementowane w usłudze Synapse SQL, takie jak:

Funkcja/opcja	Zaaprowizowane	Praca bezserwerowa
Tymczasowe procedury składowane	Nie	Tak
Ponumerowane procedury składowane	Nie	Nie
Rozszerzone procedury składowane	Nie	Nie
Procedury składowane CLR	Nie	Nie
Opcja szyfrowania	Nie	Tak
Opcja replikacji	Nie	Nie
Parametry z wartościami przechowywanymi w tabeli	Nie	Nie
Parametry tylko do odczytu	Nie	Nie
Parametry domyślne	Nie	Tak
Konteksty wykonywania	Nie	Nie
Return, instrukcja	Nie	Tak
WSTAW DO PLIKU .. EXEC	Nie	Tak

Następne kroki

Aby uzyskać więcej porad dotyczących programowania, zobacz Omówienie programowania.

Udostępnij za pośrednictwem