Хранимые процедуры с использованием Synapse SQL в Azure Synapse Analytics

Мақала
06/01/2023

Подготовленные и бессерверные пулы Synapse SQL позволяют размещать сложную логику обработки данных в хранимых процедурах SQL. Хранимые процедуры — это отличный метод инкапсуляции кода SQL, при котором он хранится близко к данным в хранилище данных. Хранимые процедуры помогают разработчикам добиться модульности своих решений, инкапсулируя код в управляемые единицы и обеспечивая большую возможность повторного использования кода. Каждая хранимая процедура может также принимать параметры, что делает их еще более гибкими. В этой статье вы найдете советы по реализации хранимых процедур в пуле Synapse SQL для разработки решений.

Чего следует ожидать

Synapse SQL поддерживает многие функции T-SQL, которые используются в SQL Server. Что более важно, в нем есть специальные функции горизонтального масштабирования, с помощью которых можно максимально увеличить производительность решения. В этой статье вы узнаете о функциях, которые можно разместить в хранимых процедурах.

Примечание

В теле процедуры можно использовать только те функции, которые поддерживаются в контактной зоне Synapse SQL. Ознакомьтесь с этой статьей, чтобы узнать, какие объекты можно использовать в хранимых процедурах. В этих статьях показаны примеры использования универсальных функций, доступных как на бессерверной, так и в выделенной контактной зоне. Дополнительные ограничения в подготовленных и бессерверных пулах Synapse SQL см. в конце этой статьи.

Для обеспечения масштаба и производительности пула SQL существуют компоненты и функции, которые имеют отличия в реакциях на события, а также те, которые не поддерживаются.

Хранимые процедуры в Synapse SQL

В следующем примере представлены процедуры, которые удаляют внешние объекты, если они существуют в базе данных:

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

Эти процедуры можно выполнить с помощью EXEC инструкции, в которой вы указываете имя и параметры процедуры:

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 предоставляет упрощенную и оптимизированную реализацию хранимых процедур. Главное отличие от SQL Server — хранимая процедура не является предварительно скомпилированным кодом. В хранилищах данных время компиляции мало по сравнению со временем, которое требуется для выполнения запросов к большим объемам данных. Более важно убедиться, что код хранимой процедуры правильно оптимизирован для больших запросов. Цель — сэкономить часы, минуты и секунды, но не миллисекунды. Поэтому удобнее считать хранимые процедуры контейнерами для логики SQL.

Когда Synapse SQL выполняет хранимую процедуру, инструкции SQL анализируются, транслируются и оптимизируются. Во время этого процесса каждая инструкция преобразуется в распределенные запросы. Код SQL, который выполняется с данными, отличается от отправляемого запроса.

Инкапсуляция правил проверки

Хранимые процедуры позволяют расположить логику проверки в одном модуле, хранящемся в базе данных SQL. В следующем примере представлена проверка значения параметров и изменение их значения по умолчанию.

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

Логика в процедуре SQL проверит входные параметры при вызове процедуры.


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.

Вложенные хранимые процедуры

Когда хранимые процедуры вызывают другие хранимые процедуры или выполняют динамический код SQL, то внутреннюю хранимую процедуру или код вызова называют вложенными. Пример вложенной процедуры показан в коде:

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

Эта процедура принимает параметр, представляющий некоторое имя, а затем вызывает другие процедуры для удаления объектов с таким именем. Пул Synapse SQL поддерживает до восьми уровней вложенности. Это одно из отличий от SQL Server. Уровень вложенности в SQL Server — 32.

Вызов хранимой процедуры верхнего уровня считается 1 уровнем вложенности.

EXEC clean_up 'mytest'

Если хранимая процедура также выполняет еще один вызов EXEC, уровень вложенности увеличится до 2.

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

Если вторая процедура затем выполняет какой-либо динамический SQL-запрос, уровень вложенности увеличится до 3.

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

Примечание

В текущей версии Synapse SQL не поддерживает @@NESTLEVEL. Необходимо отслеживать уровень вложенности. Маловероятно, что вы превысите восемь уровней вложенности, но если вы это сделаете, вам нужно переписать свой код, чтобы он соответствовал уровням вложенности в этих пределах.

INSERT..EXECUTE

Подготовленный пул Synapse SQL не позволяет использовать результирующий набор хранимой процедуры в инструкции INSERT. Существует другой подход, которым вы можете воспользоваться. Пример см. в статье по временным таблицам для подготовленного пула Synapse SQL.

Ограничения

Некоторые аспекты хранимых процедур Transact-SQL не реализованы в Synapse SQL. Среди них:

Функция/возможность	Подготовлено	Бессерверные приложения
Временные хранимые процедуры	Нет	Да
Нумерованные хранимые процедуры	Нет	Нет
Расширенные хранимые процедуры	Нет	Нет
хранимые процедуры CLR;	Нет	Нет
Вариант шифрования	Нет	Да
Параметр репликации	Нет	Нет
Возвращающие табличные значения параметры	Нет	Нет
Параметры только для чтения	Нет	Нет
Параметры по умолчанию	Нет	Да
Контекст выполнения	Нет	Нет
Return - оператор	Нет	Да
INSERT INTO EXEC	Нет	Да

Дальнейшие действия

Дополнительные советы по разработке приведены в обзоре разработки.

Бөлісу құралы: