Инкапсуляция преобразования данных в хранимой процедуре

Завершено

Хотя оператор CETAS можно запускать CREATE EXTERNAL TABLE AS SELECT в скрипте всякий раз, когда необходимо преобразовать данные, рекомендуется инкапсулировать операцию преобразования в хранимой процедуре. Такой подход упрощает ввод в эксплуатацию преобразований данных, позволяя предоставлять параметры, извлекать выходные данные и включать дополнительную логику в один вызов процедуры.

Например, следующий код создает хранимую процедуру, которая удаляет внешнюю таблицу, если она уже существует, прежде чем повторно создать ее с данными заказа за указанный год:

CREATE PROCEDURE usp_special_orders_by_year @order_year INT
AS
BEGIN

	-- Drop the table if it already exists
	IF EXISTS (
                SELECT * FROM sys.external_tables
                WHERE name = 'SpecialOrders'
            )
        DROP EXTERNAL TABLE SpecialOrders

	-- Create external table with special orders
	-- from the specified year
	CREATE EXTERNAL TABLE SpecialOrders
		WITH (
			LOCATION = 'special_orders/',
			DATA_SOURCE = files,
			FILE_FORMAT = ParquetFormat
		)
	AS
	SELECT OrderID, CustomerName, OrderTotal
	FROM
		OPENROWSET(
			BULK 'sales_orders/*.csv',
			DATA_SOURCE = 'files',
			FORMAT = 'CSV',
			PARSER_VERSION = '2.0',
			HEADER_ROW = TRUE
		) AS source_data
	WHERE OrderType = 'Special Order'
	AND YEAR(OrderDate) = @order_year
END

Примечание.

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

Помимо инкапсуляции логики Transact-SQL, хранимые процедуры также предоставляют следующие преимущества.

Сокращение сетевого трафика между клиентом и сервером

Команды в процедуре выполняются как один пакет кода, что может значительно уменьшить сетевой трафик между сервером и клиентом, так как по сети отправляется только вызов для выполнения процедуры.

Предоставление границы безопасности

Многие пользователи и клиентские программы могут выполнять операции с базовыми объектами базы данных посредством процедур, даже если у них нет прямых разрешений на доступ к базовым объектам. Процедура определяет, какие именно процессы и действия будут выполняться, и защищает базовые объекты базы данных, устраняя необходимость предоставлять разрешения на уровне отдельного объекта, а также упрощает уровни безопасности.

Простота обслуживания

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

Улучшение производительности

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