L'historisation de données via la fonction MERGE
par
, 03/05/2016 à 09h47 (2579 Affichages)
Présentation
Ce document présente une technique de gestion des historiques de données assez particulière : le MERGE combiné à la clause OUTPUT. Microsoft a implémenté le MERGE, présente dans les normes SQL depuis 2003 et revue en 2008, à partir de SQL Server 2008.
Cette fonctionnalité regroupe 3 fonctions SQL de base : INSERT, UPDATE, DELETE.
Le MERGE
Cette fonctionnalité permet de manager en une seule instruction SQL l’ensemble des mouvements possibles sur une table, souvent nommée « TARGET » ou « T ». La source de la modification peut être une requête, une vue ou un CTE (Common Table Expression). En fonction des jointures définies entre la TARGET et la SOURCE, le MERGE permet de définir les lignes à insérer, mettre à jour ou supprimer.
Il est à noter qu’il est possible de compléter le critère de jointure de base, (définissant la relation entre la TARGET et la SOURCE) dans la clause (NOT) MATCH d’un filtre, ce qui offre l'avantage d’être très précis sur les actions à entreprendre. Il sera, par exemple, possible de choisir un critère pour mettre à jour une ligne et pas une autre.
Il est aussi envisageable de filtrer les lignes à supprimer ; les autres données, pourront être insérées ou ignorées, au choix (bien qu'ignorer des données relève d'une mauvaise application des règles dans la source).
La syntaxe du MERGE est :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9 MERGE INTO [base].[schema].[table] AS TARGET USING ( SELECT * FROM maSource ) AS SOURCE ON TARGET.Clé = SOURCE.Clé WHEN MATCHED THEN UPDATE SET Champ1 = SOURCE.Champ1 , Champ2 = SOURCE.Champ2 WHEN NOT MATCHED THEN INSERT ( Champ1, Champ2, Champ3 ) VALUES ( SOURCE.Champ1, SOURCE.Champ2, SOURCE.Champ3 );
Cette forme est la construction la plus simple du MERGE. Il est à noter qu’un MATCHED ne fonctionne qu’une seule fois par action effectuée. (UPDATE ou DELETE)
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3 WHEN MATCHED AND Champ3 = 'OK' THEN UPDATE SET Champ1 = SOURCE.Champ1 , Champ2 = SOURCE.Champ2
Ainsi, il est permis de gérer plusieurs règles en une seule fois. Ce gain de temps d'écriture de code ainsi qu’une optimisation du processus maintiennent le principe ensembliste des SGBDR comme SQL Server.
Ce principe de clauses particulières permet aussi de cibler certaines lignes afin de les supprimer.
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 WHEN MATCHED AND Champ3 = 'DEL' THEN DELETE
Avantages du MERGE
Cette fonctionnalité peut rapidement devenir essentielle lors de traitements de grosses volumétries. Les processus respectant les normes SQL sont optimisés et exploitent pleinement les possibilités des moteurs SGBD. Un MERGE bien maitrisé vous ouvrira les portes de procédures multi-tâches et efficaces !
Cas Pratique
Observons comment se déroule une requête MERGE. Commençons par une structure de base :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 CREATE DATABASE TrainningMERGE; GO USE TrainningMERGE; GO CREATE TABLE dbo.TableMERGE( Id Int IDENTITY( 1, 1 ) , Nom VarChar( 50 ) , Prenom VarChar( 50 ) , TypeAction CHAR( 3 ) ); GO INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('jean' , 'valjean', 'MAJ' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('pierre' , 'dupont' , 'MAJ' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('capitain', 'haddock', 'MAJ' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('hamza' , 'hamzawi', 'MAJ' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('sarra' , 'alfane' , 'MAJ' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('jean' , 'valjean', 'DEL' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('pierre' , 'dupont' , 'DEL' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('capitain', 'haddock', 'DEL' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('hamza' , 'hamzawi', 'DEL' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('sarra' , 'alfane' , 'DEL' ); GO
Ensuite, le MERGE.
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 --La requête de la source met la première lettre du Nom et du Prénom en capitale. MERGE INTO dbo.Table MERGE AS Target USING ( SELECT Id, UPPER( LEFT( Nom, 1 ) ) + RIGHT( Nom, LEN( Nom ) -1 ) AS Nom , UPPER( LEFT( Prenom, 1 ) ) + RIGHT( Prenom, LEN( Prenom ) -1 ) AS Prenom FROM dbo.TableMERGE UNION SELECT NULL, 'Isabelle', 'Hupert' UNION SELECT NULL, 'Hind' , 'Affane' UNION SELECT NULL, 'Baptiste', 'Durand' UNION SELECT NULL, 'Joseph' , 'Bardin' UNION SELECT NULL, 'Clément' , 'Grenier') AS Source ON Target.Id = Source.ID WHEN MATCHED AND Target.TypeAction = 'MAJ' THEN UPDATE SET Nom = Source.Nom , Prenom = Source.Prenom , TypeAction = 'TER' --Comme Terminé WHEN MATCHED AND Target.TypeAction = 'DEL' THEN DELETE WHEN NOT MATCHED THEN INSERT ( Nom, Prenom, TypeAction ) VALUES ( Source.Nom, Source.Prenom, 'INS' ) ;
Nb : Un MERGE doit impérativement se terminer par un ";" sinon SQL Server déclare une erreur.
Ici, notre but est de mettre la première lettre des Noms/Prénom en Majuscule, de supprimer les lignes en double et d’insérer de nouvelles personnes dans la table !
Pour faciliter la lecture, les marqueurs sont volontairement simples. ‘DEL’ pour des données à supprimer ‘MAJ’ pour les données à mettre à jour, les UNION dans la requête source ajoutent les données non-existantes dans la table.
Vous pouvez constater avec un SELECT de la table qu’il y a bien 10 lignes, les 5 premières avec des majuscules en première lettre, une action = ‘TER’ et de nouvelles lignes avec une action à ‘INS’.
Les autres lignes, celles avec l’action ‘DEL’, ont été supprimées !
La Clause OUTPUT
Nous venons de voir les fonctionnalités de base du MERGE, à savoir représenter en une seule grande fonction, le principe de base des SGBDR : manager les données dans les tables. Cependant, certains projets nécessitent un peu plus que de simples INSERT, DELETE et UPDATE. Il faut parfois faire des historisations des données en conservant toutes les traces d’évolution de celles-ci. Chaque modification ou suppression doit être enregistrée, datée et caractérisée. Afin de simplifier ce genre de processus, les triggers, bien que consommateurs de ressources, sont utiles. Ils permettent de travailler via des tables de session (INSERTED, DELETED). Cependant, SQL Server a implanté une fonctionnalité très pratique, la clause OUTPUT. Elle permet d’extraire d’une requête les données qui ont été calculées ou mises en forme. Qu’est-ce que ça signifie ? Simplement qu’une requête, à laquelle on ajoute cette clause, présente les données qu’elle vient de traiter pour les envoyer dans une autre table.
Voici l’exemple d’un SELECT INSERT qui déverse les données traitées dans une table clone qui permet, par exemple, de traiter en arrière-plan de grosses volumétries, tout en laissant la table courante libre de tout verrouillage.
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5 INSERT INTO [schema].[maTable] SELECT * OUTPUT INSERTED.* INTO [schema].[maTable3] FROM [schema].[maTable2];
Cette clause peut aussi être placée dans un UPDATE.
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7 UPDATE [schema].[maTable] SET Champ1 = [s].[Champ1] , Champ2 = [s].[Champ2] OUTPUT DELETED.*, GETDATE INTO [schema].[maTable_HST] FROM [schema].[maTable2] AS [s] WHERE [schema].[maTable].Clé = [s].Clé;
Ici, la clause OUTPUT extrait les données de [schema].[maTable] avant modification et les exporte dans une table d’historique, clone de la table mise à jour et possédant une colonne de date d’historisation. Cette façon d’historiser permet de manager de façon efficace, l’ensemble des données impactées par un traitement. Évidemment tout ceci a un coût, mais reste moindre par rapport à l'utilisation de 2 requêtes 1 première qui va sauvegarder les données et une 2ème pour traiter les nouvelles.
Ceci peut aussi s’appliquer à une clause DELETE.
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4 DELETE OUTPUT DELETED.*, GETDATE() INTO [schema].[maTable_HST] FROM [schema].[maTable];
Dans ce cas, les données supprimées de la table sont aussi historisées.
Clause OUTPUT et MERGE
Cette fonctionnalité, très utile, a aussi été implémentée avec un MERGE. L’avantage réside dans le fait de pouvoir manager chacune des actions entreprises sur la table en précisant l'action appliquée.
Cependant, la forme diffère un peu. La structure d’un MERGE étant totalement différente des 3 exemples ci-dessus, il est important de noter, qu’une clause OUTPUT dans un MERGE, n’est pas simplement composée de 2 lignes, mais permet cependant plus de souplesse quant aux données historisées.
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 INSERT INTO [schema].[table_HST] SELECT * FROM ( MERGE INTO [schema].[table] AS TARGET USING ( SELECT * FROM maSource ) AS SOURCE ON TARGET.Clé = SOURCE.Clé WHEN MATCHED THEN UPDATE SET Champ1 = SOURCE.Champ1 , Champ2 = SOURCE.Champ2 WHEN NOT MATCHED THEN INSERT ( Champ1, Champ2, Champ3 ) VALUES ( SOURCE.Champ1, SOURCE.Champ2, SOURCE.Champ3 ) OUTPUT DELETED.*, GETDATE(), $Action ) MERGE_Out(Champ1, Champ2, Champ3, Date_Traitement, Actions);
Comme vous pouvez le constater, l’OUTPUT du MERGE est en fait une encapsulation de ce dernier dans un INSERT SELECT. On va considérer le jeu de données ressorties par le MERGE comme une requête source (ou table dérivée) et s’en servir dans un SELECT. Il est alors possible d’effectuer divers traitements, comme des filtres sur les actions effectuées.
Nb : La fonction $Action liée à une clause OUTPUT indique l'action effectuée par le MERGE sur la ligne en question.
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part WHERE Actions IN ( 'UPDATE', 'DELETE' )
Dans ce cas, seules les données mises à jour et supprimées sont historiées. Cependant, il serait tout aussi possible d’utiliser la table INSERTED, ou SOURCE dans le OUTPUT pour ressortir les nouvelles données et les historiser. Tout dépend de votre projet.
Cas Pratique
Nous avons vu un peu plus haut un MERGE classique. Maintenant, mettons en application ce que nous venons d’aborder.
Avant tout, créons une structure capable de recevoir nos données ! Puis, supprimons les données de notre table d’origine et réinsérons-les.
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 CREATE TABLE dbo.TableMERGE_HISTO( Id Int , Nom VarChar( 50 ) , Prenom VarChar( 50 ) , TypeAction CHAR( 3 ) , DateHisto DateTimeOffset( 5 ) , ActionHisto VarChar( 10 ) ); --La requête de la source met la première lettre du Nom et du Prenom en capital. TRUNCATE TABLE dbo.TableMERGE; --Pour repartir sur un jeu de données vierge, un truncate de la table puis une insertion des données est a faire GO INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('jean' , 'valjean', 'MAJ' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('pierre' , 'dupont' , 'MAJ' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('capitain', 'haddock', 'MAJ' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('hamza' , 'hamzawi', 'MAJ' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('sarra' , 'alfane' , 'MAJ' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('jean' , 'valjean', 'DEL' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('pierre' , 'dupont' , 'DEL' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('capitain', 'haddock', 'DEL' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('hamza' , 'hamzawi', 'DEL' ); INSERT INTO dbo.TableMERGE( Nom, Prenom, TypeAction )VALUES ('sarra' , 'alfane' , 'DEL' ); GO
Maintenant, voyons comment, à partir de la requête MERGE du dessus, nous pouvons supprimer, et mettre à jour, tout en conservant l’information des données que nous avons touchées dans une table. Le tout, en une seule et unique requête ! (après tout, nous sommes là pour ça !)
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 INSERT INTO dbo.TableMERGE_HISTO( Id, Nom, Prenom, TypeAction, DateHisto, ActionHisto ) SELECT * FROM ( MERGE INTO dbo.TableMERGE AS Target USING ( SELECT Id, UPPER( LEFT( Nom, 1 ) ) + RIGHT( Nom, LEN( Nom ) -1 ) AS Nom , UPPER( LEFT( Prenom, 1 ) ) + RIGHT( Prenom, LEN( Prenom ) -1 ) AS Prenom FROM dbo.TableMERGE UNION SELECT NULL, 'Isabelle', 'Hupert' UNION SELECT NULL, 'Hind' , 'Affane' UNION SELECT NULL, 'Baptiste', 'Durand' UNION SELECT NULL, 'Joseph' , 'Bardin' UNION SELECT NULL, 'Clément' , 'Grenier') AS Source ON Target.Id = Source.ID WHEN MATCHED AND Target.TypeAction = 'MAJ' THEN UPDATE SET Nom = Source.Nom , Prenom = Source.Prenom , TypeAction = 'TER' --Comme Terminé WHEN MATCHED AND Target.TypeAction = 'DEL' THEN DELETE WHEN NOT MATCHED THEN INSERT ( Nom, Prenom, TypeAction ) VALUES ( Source.Nom, Source.Prenom, 'INS' ) OUTPUT DELETED.*, GETUTCDATE(), $Action ) AS Output_MERGE ( Id, Nom, Prenom, TypeAction, DateHisto, Actions ) WHERE Actions IN ( 'DELETE', 'UPDATE' ) ;
Faites maintenant, un SELECT sur votre table d’historique ainsi que sur votre table mise à jour. Le résultat est le même pour la table d’origine, mais vous pourrez constater dans la table d’historique que les lignes supprimées sont là, et que les lignes mises à jour sont là avec la valeur présente dans la table AVANT la mise à jour.
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 SELECT * FROM dbo.TableMERGE_HISTO
Ainsi, voici comment gérer une table tout en conservant les données que nous avons modifiées.
Conclusion
SQL est un moyen de traiter de façon efficace de grosses volumétries de données sans systématiquement nécessiter d'énormes capacités de calcul.
Toutefois, l’utilisation d’un OUTPUT nécessite une vérification des volumétries des bases. En effet, gérer un historique complet des mouvements d’une table peut poser des problèmes de stockage, pouvant entraîner des coûts considérables.
Dans un projet BI, cette problématique est récurrente et il est essentiel de bien réfléchir à la façon dont l’historisation des tables sera gérée : TRIGGER, ETL ou fonction MERGE.
Bons projets à vous.