Kapsla in datatransformeringar i en lagrad procedur

Slutförd

Även om du kan köra en CREATE EXTERNAL TABLE AS SELECT (CETAS)-instruktion i ett skript när du behöver transformera data, är det bra att kapsla in transformeringsåtgärden i den lagrade proceduren. Den här metoden kan göra det enklare att operationalisera datatransformeringar genom att du kan ange parametrar, hämta utdata och inkludera ytterligare logik i ett enda proceduranrop.

Följande kod skapar till exempel en lagrad procedur som släpper den externa tabellen om den redan finns innan den återskapas med orderdata för det angivna året:

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

Kommentar

Som tidigare nämnts tar det inte bort mappen som innehåller dess datafiler om du tar bort en befintlig extern tabell. Du måste uttryckligen ta bort målmappen om den finns innan du kör den lagrade proceduren, annars uppstår ett fel.

Förutom att kapsla in Transact-SQL-logik ger lagrade procedurer också följande fördelar:

Minskar klient-till-server-nätverkstrafik

Kommandona i en procedur körs som en enda kodbatch. vilket avsevärt kan minska nätverkstrafiken mellan servern och klienten eftersom endast anropet för att köra proceduren skickas över nätverket.

Tillhandahåller en säkerhetsgräns

Flera användare och klientprogram kan utföra åtgärder på underliggande databasobjekt genom en procedur, även om användarna och programmen inte har direkt behörighet för dessa underliggande objekt. Proceduren styr vilka processer och aktiviteter som utförs och skyddar de underliggande databasobjekten. eliminerar kravet på att bevilja behörigheter på enskild objektnivå och förenklar säkerhetsskikten.

Underlättar underhåll

Ändringar i logik- eller filsystemplatser som ingår i datatransformeringen kan endast tillämpas på den lagrade proceduren. utan att kräva uppdateringar av klientprogram eller andra anropande funktioner.

Förbättrad prestanda

Lagrade procedurer kompileras första gången de körs och den resulterande körningsplanen lagras i cacheminnet och återanvänds vid efterföljande körningar av samma lagrade procedur. Därför tar det mindre tid att bearbeta proceduren.