Une problématique récurrente lors du chargement d’un DWH consiste à savoir quelles lignes seront insérées, updatées ou ignorées. Deux approches existent:

• Full SSIS (Utilisation du LookUp et Conditonal Split)
• Full SQL (Commande MERGE).

L’utilisation de la commande MERGE est pour quelques uns complexe à mettre en place, difficile à maintenir et lente au niveau des performances à cause de la comparaison des valeurs une par une des colonnes susceptibles d’être modifiées.

En effet, imaginons une table avec une dizaine de colonnes de type chaîne de caractères, cela reviens à avoir une clause WHEN dans la commande MERGE qui ressemble à:

.
.
.
Source.Colonne 1 != Target.Colonne 1  
AND Source.Colonne 2 != Target.Colonne 2  
AND Source.Colonne 3 != Target.Colonne 3
...

On se retrouve avec un code illisible et difficile à maintenir.

Afin d’éviter cela, il existe une méthode pour suivre les modifications de données qui consiste à utiliser la fonction HASHBYTES.

Cette fonction retourne le hachage des données d’entrée dans SQL Server. Le hachage est une opération consistant à transformer une chaîne de taille variable en un code de taille fixe en appliquant un algorithme dans le but d’authentifier ou de stocker ce message.

La syntaxe est la suivante: HASHBYTES (Algorithme de hachage, Colonnes d’entrée)

• • Algorithme : Identifie l’algorithme de hachage MD2, MD4, MD5, SHA, SHA1, SHA2_256 ou SHA2_512 à utiliser pour les données d’entrée. Ces algorithmes sont utilisés dans le but de réduire la taille des données à traiter par la fonction de cryptage. Ce condensé est de taille fixe, dont la valeur diffère suivant la fonction utilisée :
    • MD2 produit un résultat de 128 bits (16 octets)
    • MD4 produit un résultat de 128 bits (16 octets)
    • MD5 produit un résultat de 128 bits (16 octets)
    • SHA produit un résultat 160 bits (20 octets)
    • SHA1 produit un résultat de 160 bits (20 octets)

    SQL Server 2012 Introduit deux algorithmes additionels

    • SHA2_256 produit un résultat de 256 bits (32 octets)
    • SHA2_512 produit un résultat de 512 bits (64 octets)
  • Colonnes d’entrée: Spécifie une expression qui correspond à une chaîne de type caractère ou binaire à hacher. Les valeurs d’entrée autorisées sont limitées à 8 000 octets.

    La valeur de retour de la fonction HASHBYTES est un Varbinary.

    Nous allons maintenant nous pencher sur la façon d’utiliser cette fonction pour le chargement d’un entrepôt de données.

    Etape 1 : Création et chargement Table source

    CREATE TABLE [dbo].[personne]( 
    [matricule] [int] NULL, 
    [prenom] [nvarchar](100) NULL, 
    [nom] [nvarchar](100) NULL, 
    [date_naissance] date NULL, 
    [adresse] [nvarchar](100) NULL, 
    [zip_code] [int] NULL, 
    [ville] [nvarchar](100) NULL, 
    [tel] [nvarchar](100) NULL, 
    [email] [nvarchar](100) NULL 
    ) 
     
    INSERT INTO [dbo].[personne] VALUES (101,'Alain','Dupond','1985/01/11','Rue Vaugirard',75015,'Paris','01 01 01 01','alain.dupond@mail.com') 
    INSERT INTO [dbo].[personne] VALUES (102,'Bertrand','Durand','1984/02/22','Rue Lecourbe',75015,'Paris','02 02 02 02','bertrand.durand@mail.com') 
    INSERT INTO [dbo].[personne] VALUES (103,'André','Duval','1985/06/01','Rue Bichat',75010,'Paris','03 03 03 03','andre.duval@mail.com') 
    INSERT INTO [dbo].[personne] VALUES (104,'Marcel','Dujardin','1986/06/07','Rue des boulets',75011,'Paris','04 04 04 04','marcel.dujardin@mail.com') 
    

    Etape 2 : Création de la Dimension

    CREATE TABLE [dbo].[DimPersonne]( 
    [IdPersonne] [int] Identity(1,1) NOT NULL, 
    [MatriculePersonne] [int] NULL, 
    [PrenomPersonne] [nvarchar](100) NULL, 
    [NomPersonne] [nvarchar](100) NULL, 
    [DateNaissance] [date] NULL, 
    [AdressePersonne] [nvarchar](100) NULL, 
    [ZipCode] [int] NULL, 
    [Ville] [nvarchar](100) NULL, 
    [Telephone] [nvarchar](100) NULL, 
    [Email] [nvarchar](100) NULL, 
    [HashBytesPersonne] [varbinary](200) NULL 
    )  
    

    A noter: La colonne HashBytesPersonne permettra de stocker les valeurs de hachage des données.

    Etape 3 : Chargement de la Dimension à partir de la source

    MERGE dbo.DimPersonne AS Dimension
    USING (SELECT 
       matricule 
      ,prenom 
      ,nom 
      ,date_naissance 
      ,adresse 
      ,zip_code 
      ,ville 
      ,tel 
      ,email 
    
      -- Fonction de Hachage des colonnes modifiables
    
      ,HASHBYTES('MD5',CONCAT(prenom ,nom ,adresse ,zip_code ,ville ,tel ,email)) AS hashPersonne 
    
    FROM dbo.personne) AS Source ON source.matricule = Dimension.MatriculePersonne 
     
    WHEN MATCHED AND source.hashPersonne != Dimension.HashBytesPersonne -- La ligne existe dans la destination, on compare la valeur de Hachage de la source et de la destination
    
    -- Mise à jour si le hachage n'est pas le même
    
    THEN UPDATE SET  
     Dimension.MatriculePersonne=source.matricule 
    ,Dimension.PrenomPersonne=source.prenom 
    ,Dimension.NomPersonne=source.nom 
    ,Dimension.DateNaissance=source.date_naissance 
    ,Dimension.AdressePersonne=source.adresse 
    ,Dimension.ZipCode=source.zip_code 
    ,Dimension.Ville=source.ville 
    ,Dimension.Telephone=source.tel 
    ,Dimension.Email=source.email 
    ,Dimension.HashBytesPersonne=source.hashPersonne 
     
     -- Insertion si la ligne n'existe pas
    
    WHEN NOT MATCHED THEN INSERT( 
     MatriculePersonne 
    ,PrenomPersonne 
    ,NomPersonne 
    ,DateNaissance 
    ,AdressePersonne 
    ,ZipCode 
    ,Ville 
    ,Telephone 
    ,Email 
    ,HashBytesPersonne 
    )  
     
    VALUES( 
     
     source.matricule  
    ,source.prenom  
    ,source.nom  
    ,source.date_naissance  
    ,source.adresse  
    ,source.zip_code  
    ,source.ville  
    ,source.tel  
    ,source.email 
    ,source.hashPersonne 
    ); 
    

    La fonction HASHBYTES(‘MD5’,CONCAT(prenom ,nom ,adresse ,zip_code ,ville ,tel ,email) renvoie le hachage de la concaténation des colonnes prenom, nom, adresse, zip_code, ville, tel, email suivant l’algorithme de hachage MD5.

    Le résultat retourné est unique et identique pour une même combinaison de valeurs. Ce qui permet d’effectuer une comparaison sur une seule colonne et non sur toutes les colonnes sources séparément.

Attention: La fonction HASHBYTES ne peut être utilisée que sur des données de type chaîne de type caractère ou binaire, d’où l’utilisation de la fonction CONCAT qui permet de concaténer toutes les colonnes, tout en transformant les données en type chaîne permettant ainsi d’éviter le CAST des données.

Afin de comprendre le gain réel que peut procurer l’utilisation de cette fonction, ci-dessous le résultat de SET STATISTICS IO ON (nombre de page lues) et le SET STATISTICS TIME ON (temps d’exécution) respectifs du MERGE sans et avec HASHBYTES pour un jeu de données de 8 millions de lignes:

Sans HASHBYTESTemps d’exécution: 177885 ms

Avec HASHBYTESTemps d’exécution: 104449 ms

Pour conclure, la fonction HASHBYTES est très intéressante à utiliser dans un MERGE de chargement d’une table et permet un gain de temps et de faciliter la maintenance, néanmoins la limitation de 8000 octets est contraignante. Bien que très rare, on peut se retrouver avec des collisions au niveau des données hachées.