Chargement incrémental avec des QVD dans Qlik Sense : les trois cas

Le problème

Interroger une base de production depuis Qlik coûte cher. La requête est lente, elle charge le serveur source et sa durée grandit avec la table. Pourtant, d’un rechargement à l’autre, la plupart des lignes sont identiques.

Le fichier QVD offre l’inverse : il se lit très vite, même avec des millions de lignes. L’idée du chargement incrémental (on parle aussi de chargement différentiel ou delta) est de combiner les deux :

1. lire dans la base uniquement les lignes nouvelles ou modifiées ;
2. lire l’historique dans le QVD ;
3. enregistrer le résultat dans un nouveau QVD, qui servira d’historique la fois suivante.

Le neuf vient de la base, l’ancien du QVD, et le tout repart dans un QVD.

Cette mécanique se construit table par table. Il n’existe pas d’interrupteur global : chaque table volumineuse reçoit son propre bloc de script.

Trois cas selon la source

La bonne technique dépend des changements que la source subit et de ce qu’elle expose pour les repérer. Avec une base de données, trois situations se présentent.

Plus la source autorise de changements, plus le script doit en faire.

Cas 1 : insertion seule

Ici, la base reçoit de nouvelles lignes mais n’en modifie ni n’en supprime aucune. Toute base convient, à une condition : disposer d’un champ d’horodatage, ici ModificationTime.

Le script lit les lignes apparues depuis la dernière exécution, ajoute l’historique du QVD, puis réenregistre le tout :

LIB CONNECT TO 'Sales_DB';

Orders:
SQL SELECT PrimaryKey, X, Y
FROM dbo.Orders
WHERE ModificationTime >= '$(vLastExecTime)'
  AND ModificationTime <  '$(vThisExecTime)';

Concatenate (Orders)
LOAD PrimaryKey, X, Y
FROM [lib://QVD_Store/Orders.qvd] (qvd);

STORE Orders INTO [lib://QVD_Store/Orders.qvd] (qvd);

La borne haute vThisExecTime mérite attention. Une ligne écrite en base pendant le rechargement serait sinon prise deux fois, ou pas du tout. En fermant la fenêtre sur l’instant de démarrage, chaque ligne tombe dans exactement un intervalle.

Côté Qlik Sense, les chemins passent par des connexions de données lib://, jamais par des chemins absolus. QVD_Store désigne ici une connexion de dossier en Client-Managed. Dans Qlik Cloud, le QVD vit dans un espace : le chemin prend alors la forme lib://NomEspace:DataFiles/Orders.qvd. Enfin, le format du littéral de date dépend du SGBD interrogé : nous y revenons plus bas.

Cas 2 : insertion et mise à jour

Dès que des lignes existantes peuvent être modifiées, le QVD contient d’anciennes versions. Il faut une clé primaire pour les écarter. La correction tient en une clause :

Concatenate (Orders)
LOAD PrimaryKey, X, Y
FROM [lib://QVD_Store/Orders.qvd] (qvd)
WHERE NOT Exists(PrimaryKey);

L’ordre est décisif. Les lignes récentes sont chargées d’abord depuis la base. Quand le QVD est lu ensuite, Exists(PrimaryKey) repère les clés déjà présentes en mémoire et ignore leur ancienne version. Une ligne modifiée n’apparaît donc qu’une fois, dans son état le plus récent.

Cas 3 : insertion, mise à jour et suppression

C’est le cas le plus délicat, et celui que la présentation signale comme tel. Une ligne supprimée en base ne remonte dans aucune requête incrémentale : rien ne permet de la voir disparaître. Il faut donc comparer le résultat aux clés encore présentes dans la source.

Une jointure interne s’en charge. Elle ne lit que la colonne de clé, sur toute la table :

Inner Join (Orders)
SQL SELECT PrimaryKey FROM dbo.Orders;

Sans correspondance dans la source, la ligne supprimée sort de la table.

La présentation donne aussi une variante pour les bases qui marquent les suppressions par un indicateur au lieu d’effacer la ligne. On charge d’abord la liste des clés supprimées sous un autre nom de champ, puis on les exclut à la lecture du QVD :

DeletedKeys:
SQL SELECT PrimaryKey AS Deleted
FROM dbo.Orders
WHERE DeletionFlag = 1;

// ... incremental SELECT into Orders, as in case 1 ...

Concatenate (Orders)
LOAD PrimaryKey, X, Y
FROM [lib://QVD_Store/Orders.qvd] (qvd)
WHERE NOT Exists(PrimaryKey)
  AND NOT Exists(Deleted, PrimaryKey);

DROP TABLE DeletedKeys;

Mémoriser la dernière exécution

Tout repose sur un horodatage fiable de la dernière exécution réussie. La présentation le capture au début du script avec ReloadTime() et ne le valide qu’en l’absence d’erreur, via ScriptErrorCount. En cas d’échec, la fenêtre suivante repart donc du même point et rien n’est perdu.

Dans Qlik Sense, nous stockons cette valeur dans un petit QVD, relu au rechargement suivant. Le script complet devient :

LIB CONNECT TO 'Sales_DB';

SET vQvd     = lib://QVD_Store/Orders.qvd;
SET vQvdExec = lib://QVD_Store/Orders_LastExec.qvd;

LET vThisExecTime = Timestamp(ReloadTime(), 'YYYY-MM-DD hh:mm:ss');

// Incremental load only if the history QVD and the last execution time both exist
IF Not IsNull(QvdCreateTime('$(vQvd)')) AND Not IsNull(QvdCreateTime('$(vQvdExec)')) THEN

  LastExec:
  LOAD LastExecTime
  FROM [$(vQvdExec)] (qvd);
  LET vLastExecTime = Peek('LastExecTime', 0, 'LastExec');
  DROP TABLE LastExec;

  Orders:
  SQL SELECT PrimaryKey, X, Y
  FROM dbo.Orders
  WHERE ModificationTime >= '$(vLastExecTime)'
    AND ModificationTime <  '$(vThisExecTime)';

  Concatenate (Orders)
  LOAD PrimaryKey, X, Y
  FROM [$(vQvd)] (qvd)
  WHERE NOT Exists(PrimaryKey);

  Inner Join (Orders)
  SQL SELECT PrimaryKey FROM dbo.Orders;

ELSE

  // First run, or QVD lost: full extraction from the source
  Orders:
  SQL SELECT PrimaryKey, X, Y
  FROM dbo.Orders;

END IF

IF ScriptErrorCount = 0 THEN
  STORE Orders INTO [$(vQvd)] (qvd);

  LastExec:
  LOAD '$(vThisExecTime)' AS LastExecTime
  AUTOGENERATE 1;
  STORE LastExec INTO [$(vQvdExec)] (qvd);
  DROP TABLE LastExec;
END IF

Deux remarques pour Qlik Sense. Par défaut, une erreur de script interrompt déjà le rechargement ; le test sur ScriptErrorCount protège surtout les scripts qui assouplissent ce comportement avec SET ErrorMode = 0;. Ensuite, le script gère lui-même le premier chargement. La présentation ne le traitait pas : nous l’ajoutons. QvdCreateTime() renvoie NULL quand le fichier n’existe pas. Si l’historique ou l’horodatage manque (première exécution, QVD supprimé ou déplacé), le script lit toute la table source, sans WHERE, puis crée les deux QVD. Les exécutions suivantes passent en incrémental. Ce chargement complet n’a besoin ni de Concatenate ni d’Inner Join : la lecture intégrale reflète déjà les suppressions. Il n’est pas non plus borné par ModificationTime, pour ne pas perdre les lignes où ce champ est vide. Une ligne modifiée pendant le rechargement sera simplement relue au suivant, sans doublon grâce à NOT Exists(PrimaryKey). Enfin, les chemins sont regroupés dans deux variables : un seul endroit à adapter, par exemple en lib://Espace:DataFiles/… dans Qlik Cloud.

Le rechargement s’exécute ensuite à intervalle régulier : par une tâche dans la QMC en Client-Managed, par une planification de l’application dans Qlik Cloud.

Passer la date à la base sans ambiguïté

La requête incrémentale compare ModificationTime à une date fabriquée par Qlik. Écrite comme une simple chaîne, '2026-10-09 14:30:00', cette date est interprétée par la base selon ses réglages de session : langue, DATEFORMAT, NLS_DATE_FORMAT, DateStyle. Le même script peut alors fonctionner sur un serveur et échouer, ou pire filtrer de travers, sur un autre.

La règle tient en deux temps.

1. Côté Qlik, la variable est toujours formatée de la même façon : Timestamp(…, 'YYYY-MM-DD hh:mm:ss'), avec des heures sur 24 (hh en Qlik). C’est déjà le cas dans le script ci-dessus.
2. Côté base, on ne laisse pas deviner le format : on le déclare dans la requête, avec la syntaxe propre au SGBD.

// PostgreSQL
WHERE ModificationTime >= TIMESTAMP '$(vLastExecTime)'

// MySQL / MariaDB
WHERE ModificationTime >= STR_TO_DATE('$(vLastExecTime)', '%Y-%m-%d %H:%i:%s')

// SQL Server
WHERE ModificationTime >= CONVERT(datetime2, '$(vLastExecTime)', 120)

// Oracle
WHERE ModificationTime >= TO_TIMESTAMP('$(vLastExecTime)', 'YYYY-MM-DD HH24:MI:SS')

Quelques précisions :

• PostgreSQL et Oracle acceptent tous deux le littéral standard TIMESTAMP '2026-10-09 14:30:00', indépendant des réglages de session. Avec Oracle, TO_TIMESTAMP avec son masque explicite reste le choix le plus courant, et il ne dépend pas de NLS_DATE_FORMAT.
• MySQL et MariaDB lisent nativement les chaînes 'YYYY-MM-DD hh:mm:ss' ; STR_TO_DATE rend simplement le format explicite.
• Avec SQL Server, une chaîne 'YYYY-MM-DD hh:mm:ss' comparée à une colonne datetime peut être lue jour et mois inversés quand la langue de la session est le français. Le style 120 de CONVERT fixe le format yyyy-mm-dd hh:mi:ss sur 24 heures, quelle que soit la langue.

La borne haute vThisExecTime s’écrit de la même façon. Pour SQL Server, la requête du script complet devient par exemple :

Orders:
SQL SELECT PrimaryKey, X, Y
FROM dbo.Orders
WHERE ModificationTime >= CONVERT(datetime2, '$(vLastExecTime)', 120)
  AND ModificationTime <  CONVERT(datetime2, '$(vThisExecTime)', 120);

Dernier point à vérifier : le fuseau horaire. ReloadTime() donne l’heure du moteur Qlik, et ModificationTime celle de la base. Si les deux horloges ne sont pas dans le même fuseau, la fenêtre de lecture est décalée d’autant. Mieux vaut le contrôler une fois pour toutes au moment de la mise en place.

À retenir

• Lire peu, lire vite. La base ne fournit que les lignes nouvelles ou modifiées ; le QVD fournit l’historique.
• Partir de la source. Ce que la base permet de détecter (ajout, mise à jour, suppression) décide du script.
• Deux champs indispensables. ModificationTime pour borner la lecture, PrimaryKey dès que des lignes changent.
• L’ordre compte. Charger la base d’abord, puis le QVD avec WHERE NOT Exists(PrimaryKey).
• Les suppressions se rattrapent par jointure. Un Inner Join sur les clés encore en base élimine les lignes disparues.
• Pas de STORE sur un échec. QVD et horodatage ne sont réécrits qu’après un rechargement sans erreur.
• Prévoir le premier chargement. Sans QVD ni horodatage, le script lit toute la source puis crée l’historique.
• Déclarer le format des dates. CONVERT, TO_TIMESTAMP, STR_TO_DATE ou TIMESTAMP : la base ne devine rien.

Source

« Incremental Load using QVD files », présentation Qlik, auteur non indiqué, 2017.

Tout le mérite du contenu original revient à ses auteurs ; l’adaptation à Qlik Sense et ses éventuelles erreurs sont de HeyData. Cet article fait partie de nos synthèses techniques : découvrir la série et ses autres sujets.