Gegevenstransformaties inkapselen in een opgeslagen procedure

Voltooid

Hoewel u een CREATE EXTERNAL TABLE AS SELECT instructie (CETAS) in een script kunt uitvoeren wanneer u gegevens wilt transformeren, is het raadzaam om de transformatiebewerking in de opgeslagen procedure in te kapselen. Deze aanpak kan het eenvoudiger maken om gegevenstransformaties operationeel te maken door u in staat te stellen parameters op te geven, uitvoer op te halen en extra logica op te nemen in één procedureaanroep.

Met de volgende code wordt bijvoorbeeld een opgeslagen procedure gemaakt waarmee de externe tabel wordt verwijderd als deze al bestaat voordat deze opnieuw wordt gemaakt met ordergegevens voor het opgegeven jaar:

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

Notitie

Zoals eerder is besproken, wordt de map met de bijbehorende gegevensbestanden niet verwijderd door een bestaande externe tabel te verwijderen. U moet de doelmap expliciet verwijderen als deze bestaat voordat u de opgeslagen procedure uitvoert, anders treedt er een fout op.

Naast het inkapselen van Transact-SQL-logica bieden opgeslagen procedures ook de volgende voordelen:

Vermindert het netwerkverkeer van de client naar de server

De opdrachten in een procedure worden uitgevoerd als één batch code; wat het netwerkverkeer tussen de server en de client aanzienlijk kan verminderen, omdat alleen de aanroep voor het uitvoeren van de procedure via het netwerk wordt verzonden.

Biedt een beveiligingsgrens

Meerdere gebruikers en clientprogramma's kunnen bewerkingen uitvoeren op onderliggende databaseobjecten via een procedure, zelfs als de gebruikers en programma's geen directe machtigingen hebben voor deze onderliggende objecten. De procedure bepaalt welke processen en activiteiten worden uitgevoerd en beveiligt de onderliggende databaseobjecten; het elimineren van de vereiste voor het verlenen van machtigingen op het niveau van het afzonderlijke object en vereenvoudigt de beveiligingslagen.

Vereenvoudigt onderhoud

Wijzigingen in de logische of bestandssysteemlocaties die bij de gegevenstransformatie betrokken zijn, kunnen alleen worden toegepast op de opgeslagen procedure; zonder dat er updates nodig zijn voor clienttoepassingen of andere aanroepende functies.

Betere prestaties

Opgeslagen procedures worden gecompileerd wanneer ze voor het eerst worden uitgevoerd en het resulterende uitvoeringsplan wordt bewaard in de cache en opnieuw gebruikt bij volgende uitvoeringen van dezelfde opgeslagen procedure. Hierdoor duurt het minder tijd om de procedure te verwerken.