Mettre à jour un QVD ligne par ligne avec un véritable MERGE

1. Le MERGE n’est pas un DELTA « plus performant »

Le DELTA et le MERGE répondent à deux logiques différentes. Un DELTA par objet remplace la version complète d’un objet métier dès qu’une évolution est détectée. Le MERGE descend au niveau de la ligne : il doit savoir précisément quelle ligne a été insérée, modifiée ou supprimée.

Cette granularité ne signifie pas que le MERGE sera automatiquement plus rapide. Les performances dépendent du contexte. Dans mes propres tests, le DELTA s’est souvent montré plus rapide, mais ce résultat reste subjectif et dépend notamment du volume, du nombre de mouvements et du coût des comparaisons en base.

En revanche, le MERGE est clairement plus contraignant à mettre en œuvre : il faut construire les mouvements I/U/D côté base, préserver leur ordre avec une séquence et exécuter la partie Qlik dans le cadre d’un Partial Reload.

2. Le snapshot N-1 est au cœur du MERGE

Le MERGE commence, comme le DELTA, par la comparaison de deux états de la même table. Prenons LIGNES_FACTURE :

La comparaison est effectuée ligne à ligne grâce à ID_LIGNE_FACTURE, qui doit identifier de manière unique et stable la même ligne entre l’état courant, le snapshot et le QVD.

Le snapshot doit rester inchangé pendant la détection des trois familles de mouvements. Une fois INSERT, UPDATE et DELETE identifiés, il est reconstruit depuis LIGNES_FACTURE afin de devenir la référence du traitement suivant.

3. Construire les mouvements INSERT, UPDATE et DELETE

La table LIGNES_FACTURE_MERGE contient les opérations à appliquer au QVD. Chaque mouvement porte au minimum la clé de la ligne, un TYPE_OPERATION et un ID_DELTA. Pour un INSERT ou un UPDATE, elle contient également les valeurs courantes nécessaires au QVD.

3.1 INSERT : présent en N, absent du snapshot

INSERT INTO LIGNES_FACTURE_MERGE
    (TYPE_OPERATION, ID_DELTA, ID_LIGNE_FACTURE,
     PRODUIT, QUANTITE, CA_HT, MARGE)
SELECT 'I',
       SEQ_LIGNES_FACTURE_MERGE.NEXTVAL,
       f.ID_LIGNE_FACTURE,
       f.PRODUIT, f.QUANTITE, f.CA_HT, f.MARGE
FROM LIGNES_FACTURE f
WHERE f.ID_LIGNE_FACTURE IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM LIGNES_FACTURE_SNAP_N_1 s
      WHERE s.ID_LIGNE_FACTURE = f.ID_LIGNE_FACTURE
  );

3.2 UPDATE : même clé, contenu différent

Pour les lignes présentes dans les deux états, il faut comparer les colonnes métier. L’exemple ci-dessous utilise INTERSECT : si les deux projections sont identiques, l’intersection retourne une ligne. Si une valeur diffère, le NOT EXISTS devient vrai et le mouvement est enregistré comme UPDATE.

INSERT INTO LIGNES_FACTURE_MERGE
    (TYPE_OPERATION, ID_DELTA, ID_LIGNE_FACTURE,
     PRODUIT, QUANTITE, CA_HT, MARGE)
SELECT 'U',
       SEQ_LIGNES_FACTURE_MERGE.NEXTVAL,
       f.ID_LIGNE_FACTURE,
       f.PRODUIT, f.QUANTITE, f.CA_HT, f.MARGE
FROM LIGNES_FACTURE f
JOIN LIGNES_FACTURE_SNAP_N_1 s
  ON s.ID_LIGNE_FACTURE = f.ID_LIGNE_FACTURE
WHERE NOT EXISTS (
    SELECT f.PRODUIT, f.QUANTITE, f.CA_HT, f.MARGE FROM dual
    INTERSECT
    SELECT s.PRODUIT, s.QUANTITE, s.CA_HT, s.MARGE FROM dual
);

3.3 DELETE : présent dans le snapshot, absent en N

Pour une suppression, le point de départ est nécessairement le snapshot puisque la ligne n’existe plus dans la table courante.

INSERT INTO LIGNES_FACTURE_MERGE
    (TYPE_OPERATION, ID_DELTA, ID_LIGNE_FACTURE,
     PRODUIT, QUANTITE, CA_HT, MARGE)
SELECT 'D',
       SEQ_LIGNES_FACTURE_MERGE.NEXTVAL,
       s.ID_LIGNE_FACTURE,
       s.PRODUIT, s.QUANTITE, s.CA_HT, s.MARGE
FROM LIGNES_FACTURE_SNAP_N_1 s
WHERE NOT EXISTS (
    SELECT 1
    FROM LIGNES_FACTURE f
    WHERE f.ID_LIGNE_FACTURE = s.ID_LIGNE_FACTURE
);

4. Pourquoi une séquence ID_DELTA ?

Les mouvements sont appliqués dans un ordre déterminé. Chaque ligne de LIGNES_FACTURE_MERGE reçoit donc un identifiant séquentiel. Dans cet exemple, les familles sont produites successivement dans l’ordre INSERT, UPDATE puis DELETE.

CREATE SEQUENCE SEQ_LIGNES_FACTURE_MERGE
INCREMENT BY 1
MINVALUE 1
NOCACHE;

ID_DELTA sert ensuite de champ de séquence au MERGE ONLY Qlik. Il ne s’agit donc pas d’un simple identifiant technique : il permet de préserver l’ordre des mouvements transmis par la base.

5. Reconstruire le snapshot pour le traitement suivant

La reconstruction du snapshot intervient uniquement après la constitution complète de LIGNES_FACTURE_MERGE. À ce moment, les écarts entre N et N-1 sont mémorisés dans la table de mouvements et l’ancien snapshot peut être remplacé.

DROP TABLE LIGNES_FACTURE_SNAP_N_1 PURGE;

CREATE TABLE LIGNES_FACTURE_SNAP_N_1 NOLOGGING AS
SELECT *
FROM LIGNES_FACTURE
WHERE ID_LIGNE_FACTURE IS NOT NULL;

Sur une table importante, la préparation du snapshot peut également comprendre la création d’un index sur ID_LIGNE_FACTURE et le calcul des statistiques du SGBD. Ce coût fait partie du traitement MERGE côté base et doit être pris en compte lorsque l’on compare cette approche à un DELTA plus simple.

6. Côté Qlik : le MERGE s’inscrit dans un Partial Reload

Le QVD existant représente l’état issu du traitement précédent. Il est chargé en mémoire puis les mouvements contenus dans LIGNES_FACTURE_MERGE sont appliqués à cette table. Cette mécanique s’appuie sur le Partial Reload et sur les préfixes adaptés aux chargements concernés.

[LIGNES_FACTURE]:
REPLACE LOAD *
FROM [lib://QVD_RAW/LIGNES_FACTURE.qvd] (qvd);

Le REPLACE indique que ce LOAD participe au Partial Reload. Cette contrainte est importante : un script MERGE doit être conçu et exploité en tenant compte de ce mode d’exécution, contrairement au DELTA par objet qui peut rester dans une mécanique de chargement plus classique.

7. Comprendre MERGE ONLY

Le cœur du traitement est le préfixe MERGE ONLY. Il reçoit les mouvements et les applique à la table cible en fonction de la clé indiquée après ON.

MERGE ONLY (ID_DELTA)
ON [ID_LIGNE_FACTURE_Id]
CONCATENATE ([LIGNES_FACTURE])
LOAD
    If(TYPE_OPERATION = 'I', 'Insert',
    If(TYPE_OPERATION = 'U', 'Update',
    If(TYPE_OPERATION = 'D', 'Delete', TYPE_OPERATION))) AS TYPE_OPERATION,
    ID_DELTA,
    ID_LIGNE_FACTURE AS ID_LIGNE_FACTURE_Id,
    PRODUIT,
    QUANTITE,
    CA_HT,
    MARGE;

SQL SELECT
    TYPE_OPERATION,
    ID_DELTA,
    ID_LIGNE_FACTURE,
    PRODUIT,
    QUANTITE,
    CA_HT,
    MARGE
FROM LIGNES_FACTURE_MERGE;

Que signifie chaque partie ?

Pour un INSERT, une nouvelle ligne est ajoutée. Pour un UPDATE, la ligne correspondant à la clé est mise à jour avec les valeurs du mouvement. Pour un DELETE, la ligne correspondant à la clé est supprimée de la table en mémoire.

8. Mise en application traditionnelle complète

Une mise en œuvre minimale peut rester entièrement explicite. Il n’est pas nécessaire de disposer d’une SUB générique pour comprendre ou utiliser le mécanisme.

// 1 - Charger le QVD issu du traitement précédent
[LIGNES_FACTURE]:
REPLACE LOAD *
FROM [lib://QVD_RAW/LIGNES_FACTURE.qvd] (qvd);

// 2 - Appliquer les mouvements détectés côté base
MERGE ONLY (ID_DELTA)
ON [ID_LIGNE_FACTURE_Id]
CONCATENATE ([LIGNES_FACTURE])
LOAD
    If(TYPE_OPERATION = 'I', 'Insert',
    If(TYPE_OPERATION = 'U', 'Update',
    If(TYPE_OPERATION = 'D', 'Delete', TYPE_OPERATION))) AS TYPE_OPERATION,
    ID_DELTA,
    ID_LIGNE_FACTURE AS ID_LIGNE_FACTURE_Id,
    PRODUIT,
    QUANTITE,
    CA_HT,
    MARGE;

SQL SELECT
    TYPE_OPERATION,
    ID_DELTA,
    ID_LIGNE_FACTURE,
    PRODUIT,
    QUANTITE,
    CA_HT,
    MARGE
FROM LIGNES_FACTURE_MERGE;

// 3 - Stocker le nouvel état complet
STORE LIGNES_FACTURE
INTO [lib://QVD_RAW/LIGNES_FACTURE.qvd] (qvd);

Le résultat est un nouvel état complet de LIGNES_FACTURE.qvd : les lignes non modifiées proviennent du QVD précédent, tandis que les mouvements I/U/D ont été appliqués uniquement aux lignes concernées.

9. Les contraintes à garder en tête

Le MERGE est séduisant par sa précision, mais cette précision augmente le nombre de points à maîtriser. La base doit comparer les lignes avec le snapshot, déterminer exactement les I/U/D, alimenter la table de mouvements, gérer la séquence puis reconstruire le snapshot. Côté Qlik, le script dépend du Partial Reload et la clé utilisée par ON doit être parfaitement cohérente avec la clé des mouvements.

Le choix doit donc être fonctionnel et technique. Si remplacer un objet métier complet est peu coûteux, un DELTA peut être plus simple et très performant. Si l’objet est trop volumineux ou si l’on a réellement besoin d’une granularité ligne à ligne, le MERGE devient intéressant malgré ses contraintes supplémentaires.