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

Tip

Microsoft Fabric Data Warehouse — это реляционное хранилище корпоративного масштаба на основе озера данных, с архитектурой, готовой к будущему, встроенной ИИ и новыми функциями. Если вы не знакомы с хранилищем данных, начните с Fabric Data Warehouse. Существующие рабочие нагрузки выделенного пула SQL могут обновляться до Fabric для доступа к новым возможностям в области науки о данных, аналитики в реальном времени и отчетности.

• Start a Fabric бесплатная пробная версия.
• Ассистент по миграции для Fabric Data Warehouse.

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

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

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

Примечание.

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

Для поддержания масштаба и производительности пула 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, уровень вложения увеличивается до двух.

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, уровень вложенности увеличивается до трех.

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. Существует альтернативный подход, который можно использовать. См. пример в статье о временных таблицах для выделенного пула SQL Synapse.

Ограничения

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

Компонент или параметр	Provisioned	Бессерверные системы
Временные хранимые процедуры	нет	Да
Нумерованные хранимые процедуры	нет	нет
Расширенные хранимые процедуры	нет	нет
Хранимые процедуры CLR	нет	нет
Параметр шифрования	нет	Да
Параметр репликации	нет	нет
Параметры с табличным значением	нет	нет
Параметры только для чтения	нет	нет
Параметры по умолчанию	нет	Да
Контексты выполнения	нет	нет
Оператор возврата	нет	Да
ВСТАВЬТЕ В .. EXEC	нет	Да

Связанный контент

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