Encapsular transformações de dados em um procedimento armazenado

Concluído

Embora seja possível executar uma instrução CREATE EXTERNAL TABLE AS SELECT (CETAS) em um script sempre que você precisar transformar dados, é uma boa prática encapsular a operação de transformação no procedimento armazenado. Essa abordagem pode facilitar a operacionalização das 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 pedidos do 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

Observação

Conforme 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 da execução do procedimento armazenado ou um erro ocorrerá.

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

Reduz o tráfego de rede do cliente para o 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 porque somente 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 se os usuários e programas não tiverem permissões diretas para esses 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 simplificando as camadas de segurança.

Facilita a manutenção

Todas as alterações na lógica ou nos locais do sistema de arquivos envolvidos na transformação de dados só podem ser aplicadas ao procedimento armazenado; sem a necessidade de fazer atualizações de aplicativos cliente ou de outras funções de chamada.

desempenho aprimorado

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