Zapouzdření transformací dat v uložené proceduře

Dokončeno

I když ve skriptu můžete spustit CREATE EXTERNAL TABLE AS SELECT příkaz (CETAS), kdykoli potřebujete transformovat data, je vhodné zapouzdřit operaci transformace v uložené proceduře. Tento přístup může usnadnit zprovoznění transformací dat tím, že vám umožní zadat parametry, načíst výstupy a zahrnout do jednoho volání procedury další logiku.

Následující kód například vytvoří uloženou proceduru, která zahodí externí tabulku, pokud již existuje před opětovným vytvořením dat objednávky pro zadaný rok:

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

Poznámka:

Jak bylo popsáno dříve, vyřazení existující externí tabulky neodstraní složku obsahující její datové soubory. Cílovou složku musíte explicitně odstranit, pokud existuje před spuštěním uložené procedury nebo dojde k chybě.

Kromě zapouzdření logiky Transact-SQL poskytují uložené procedury také následující výhody:

Snižuje síťový provoz klienta na server.

Příkazy v postupu se provádějí jako jedna dávka kódu; což může výrazně snížit síťový provoz mezi serverem a klientem, protože se přes síť odesílá pouze volání ke spuštění procedury.

Poskytuje hranici zabezpečení.

Více uživatelů a klientských programů může provádět operace s podkladovými databázovými objekty prostřednictvím postupu, a to i v případě, že uživatelé a programy nemají k těmto podkladovým objektům přímá oprávnění. Postup řídí, jaké procesy a aktivity se provádějí a chrání základní databázové objekty; eliminuje požadavek na udělení oprávnění na úrovni jednotlivých objektů a zjednodušuje vrstvy zabezpečení.

Usnadňuje údržbu.

Jakékoli změny v logice nebo umístěních systému souborů, které jsou součástí transformace dat, lze použít pouze na uloženou proceduru; bez nutnosti aktualizací klientských aplikací nebo jiných volajících funkcí.

Zlepšení výkonu

Uložené procedury se kompilují při prvním spuštění a výsledný plán provádění se uchovává v mezipaměti a znovu se použije při následných spuštěních stejné uložené procedury. V důsledku toho zpracování procedury trvá kratší dobu.