Encapsular transformações de dados em um procedimento armazenado

Concluído

Embora você possa executar uma CREATE EXTERNAL TABLE AS SELECT instrução (CETAS) em um script sempre que precisar transformar dados, é uma boa prática encapsular a operação de transformação no procedimento armazenado. Essa abordagem pode facilitar a operacionalização de transformações de dados, permitindo que você forneça parâmetros, recupere saídas e inclua lógica adicional em uma única chamada de procedimento.

Por exemplo, o código a seguir cria um procedimento armazenado que descarta a tabela externa se ela já existir antes de recriá-la com dados de ordem para o ano especificado:

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

Nota

Como discutido anteriormente, descartar uma tabela externa existente não exclui a pasta que contém seus arquivos de dados. Você deve excluir explicitamente a pasta de destino, se ela existir, antes de executar o procedimento armazenado, ou ocorrerá um erro.

Além de encapsular a lógica Transact-SQL, os procedimentos armazenados também oferecem os seguintes benefícios:

Reduz o tráfego de rede de cliente para servidor

Os comandos em um procedimento são executados como um único lote de código; o que pode reduzir significativamente o tráfego de rede entre o servidor e o cliente, pois apenas a chamada para executar o procedimento é enviada pela rede.

Fornece um limite de segurança

Vários usuários e programas cliente podem executar operações em objetos de banco de dados subjacentes por meio de um procedimento, mesmo que os usuários e programas não tenham permissões diretas nesses objetos subjacentes. O procedimento controla quais processos e atividades são executados e protege os objetos de banco de dados subjacentes; eliminando a necessidade de conceder permissões no nível de objeto individual e simplifica as camadas de segurança.

Facilita a manutenção

Quaisquer alterações na lógica ou nos locais do sistema de arquivos envolvidos na transformação de dados podem ser aplicadas apenas ao procedimento armazenado; sem exigir atualizações para aplicativos cliente ou outras funções de chamada.

Desempenho melhorado

Os procedimentos armazenados são compilados na primeira vez que são executados, e o plano de execução resultante é mantido no cache e reutilizado em execuções subsequentes do mesmo procedimento armazenado. Como resultado, leva menos tempo para processar o procedimento.