Encapsuler des transformations de données dans une procédure stockée

Effectué

Bien que vous puissiez exécuter une instruction CREATE EXTERNAL TABLE AS SELECT (CETAS) dans un script chaque fois que vous devez transformer des données, il est recommandé d’encapsuler l’opération de transformation dans une procédure stockée. Cette approche peut faciliter l’opérationnalisation des transformations de données en vous permettant de fournir des paramètres, de récupérer des sorties et d’inclure une logique supplémentaire dans un appel de procédure unique.

Par exemple, le code suivant crée une procédure stockée qui supprime la table externe si elle existe, avant de la recréer avec des données de commande pour l’année spécifiée :

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

Notes

Comme indiqué précédemment, la suppression d’une table externe existante n’a pas pour effet de supprimer le dossier contenant ses fichiers de données. Vous devez supprimer explicitement le dossier cible s’il existe, avant d’exécuter la procédure stockée, sans quoi une erreur se produit.

Outre l’encapsulation de la logique Transact-SQL, les procédures stockées offrent également les avantages suivants :

Réduit le trafic réseau entre le client et le serveur

Les commandes d’une procédure sont exécutées en tant que lot unique de code, ce qui peut réduire considérablement le trafic réseau entre le serveur et le client, car seul l’appel à exécuter la procédure est envoyé sur le réseau.

Fournit une limite de sécurité

Plusieurs utilisateurs et programmes clients peuvent effectuer des opérations sur les objets de base de données sous-jacents par le biais d’une procédure, même si les utilisateurs et les programmes n’ont pas d’autorisations directes sur ces objets sous-jacents. La procédure contrôle les processus et activités effectués, et protège les objets de base de données sous-jacents, éliminant ainsi la nécessité d’accorder des autorisations au niveau objet individuel, et simplifiant les couches de sécurité.

Facilite la maintenance

Toutes les modifications apportées aux emplacements de système de fichiers ou logiques impliqués dans la transformation de données ne peuvent être appliquées qu’à la procédure stockée, sans nécessiter de mises à jour d’applications clientes ou d’autres fonctions appelantes.

performances améliorées

Les procédures stockées sont compilées lors de leur première exécution, et le plan d’exécution résultant est conservé dans le cache et réutilisé lors de l’exécution suivante de la même procédure stockée. Par conséquent, le traitement de la procédure prend moins de temps.