Hermetyzowanie przekształceń danych w procedurze składowanej

  • 4 min

Chociaż można uruchomić instrukcję CREATE EXTERNAL TABLE AS SELECT (CETAS) w skrypcie za każdym razem, gdy trzeba przekształcić dane, dobrym rozwiązaniem jest hermetyzowanie operacji przekształcania w procedurze składowanej. Takie podejście może ułatwić operacjonalizacja przekształceń danych, umożliwiając dostarczanie parametrów, pobieranie danych wyjściowych i uwzględnianie dodatkowej logiki w jednym wywołaniu procedury.

Na przykład poniższy kod tworzy procedurę składowaną, która pominie tabelę zewnętrzną, jeśli już istnieje, zanim utworzy ją ponownie przy użyciu danych zamówienia dla określonego roku:

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

Uwaga

Jak wspomniano wcześniej, usunięcie istniejącej tabeli zewnętrznej nie powoduje usunięcia folderu zawierającego jego pliki danych. Należy jawnie usunąć folder docelowy, jeśli istnieje przed uruchomieniem procedury składowanej lub wystąpi błąd.

Oprócz hermetyzacji logiki Transact-SQL procedury składowane zapewniają również następujące korzyści:

Zmniejsza ruch sieciowy klienta do serwera

Polecenia w procedurze są wykonywane jako pojedyncza partia kodu; co może znacznie zmniejszyć ruch sieciowy między serwerem a klientem, ponieważ tylko wywołanie do wykonania procedury jest wysyłane przez sieć.

Zapewnia granicę zabezpieczeń

Wielu użytkowników i programów klienckich może wykonywać operacje na obiektach bazowych baz danych za pomocą procedury, nawet jeśli użytkownicy i programy nie mają bezpośrednich uprawnień do tych obiektów bazowych. Procedura kontroluje, jakie procesy i działania są wykonywane i chronią bazowe obiekty bazy danych; wyeliminowanie wymagania udzielenia uprawnień na poziomie pojedynczego obiektu i upraszcza warstwy zabezpieczeń.

Ułatwia konserwację

Wszelkie zmiany w lokalizacjach logiki lub systemu plików biorących udział w przekształcaniu danych mogą być stosowane tylko do procedury składowanej; bez konieczności aktualizowania aplikacji klienckich lub innych funkcji wywołujących.

Zwiększona wydajność

Procedury składowane są kompilowane po raz pierwszy, a wynikowy plan wykonywania jest przechowywany w pamięci podręcznej i ponownie używany w kolejnych uruchomieniach tej samej procedury składowanej. W związku z tym przetwarzanie procedury zajmuje mniej czasu.