Kapseln von Datentransformationen in einer gespeicherten Prozedur

Abgeschlossen

Wenn Sie Daten transformieren müssen, können Sie zwar eine CETAS-Anweisung (CREATE EXTERNAL TABLE AS SELECT) in einem Skript ausführen, aber es empfiehlt sich, den Transformationsvorgang in einer gespeicherten Prozedur zu kapseln. Dieser Ansatz kann das Operationalisieren von Datentransformationen erleichtern, da Sie in einem einzelnen Prozeduraufruf Parameter bereitstellen, Ausgaben abrufen und zusätzliche Logik einschließen können.

Der folgende Code erstellt beispielsweise eine gespeicherte Prozedur, die die externe Tabelle löscht, falls sie bereits vorhanden ist, bevor sie mit Auftragsdaten für das angegebene Jahr neu erstellt wird:

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

Hinweis

Wie bereits erwähnt, wird durch das Löschen einer vorhandenen externen Tabelle nicht der Ordner mit den Datendateien gelöscht. Sie müssen den Zielordner explizit löschen, falls er vorhanden ist, bevor Sie die gespeicherte Prozedur ausführen. Andernfalls tritt ein Fehler auf.

Zusätzlich zum Kapseln der Transact-SQL-Logik bieten gespeicherte Prozeduren auch die folgenden Vorteile:

Verringerung des Datenverkehrs zwischen Client und Server

Die Befehle in einer Prozedur werden als einzelner Codebatch ausgeführt; was den Netzwerkdatenverkehr zwischen dem Server und dem Client erheblich reduzieren kann, da nur der Aufruf zum Ausführen der Prozedur über das Netzwerk gesendet wird.

Sicherheitsgrenze

Mithilfe einer Prozedur können mehrere Benutzer und Clientprogramme Vorgänge für zugrunde liegende Datenbankobjekte ausführen, selbst wenn die Benutzer und Programme keine direkten Berechtigungen für diese zugrunde liegenden Objekte aufweisen. Die Prozedur steuert, welche Prozesse und Aktivitäten ausgeführt werden, und schützt die zugrunde liegenden Datenbankobjekte. Dadurch müssen keine Berechtigungen mehr auf Ebene einzelner Objekte erteilt werden, und die Sicherheitsebenen werden vereinfacht.

Vereinfachte Wartung

Alle Änderungen an der Logik oder den Dateisystemspeicherorten, die an der Datentransformation beteiligt sind, können nur auf die gespeicherte Prozedur angewendet werden, ohne dass Aktualisierungen der Clientanwendungen oder anderer aufrufender Funktionen erforderlich sind.

Verbesserte Leistung

Gespeicherte Prozeduren werden bei der erstmaligen Ausführung kompiliert. Der resultierende Ausführungsplan wird im Cache gespeichert und bei Ausführung derselben gespeicherten Prozedur wieder verwendet. Dies führt zu einer kürzeren Verarbeitungszeit für die Prozedur.