Ajout de transformations SQL

Vous pouvez inclure des tables basées sur SQL dans des tâches de transformation. Une transformation SQL vous permet de saisir une requête SQL SELECT dans un pipeline pour définir des transformations simples ou complexes. Vous pouvez également utiliser des macros pour adapter la requête à exécuter si un chargement initial ou un chargement incrémentiel est effectué.

Pour plus d'informations sur la création d'une tâche de transformation, voir Transformation des données.

Ajout d'une transformation SQL

Pour ajouter une transformation SQL dans une tâche de données Transformer :

Dans Transformer, sélectionnez les ensembles de données à inclure dans la requête, puis cliquez sur Ajouter une transformation SQL.

Ajouter une transformation SQL apparaît, vous permettant de fournir les paramètres de la transformation.
Saisissez un nom pour l'ensemble de données cible dans Nom.

Vous pouvez également ajouter une description plus longue dans Description.
Dans Matérialisation, sélectionnez si la sortie transformée doit être matérialisée ou non. Vous pouvez choisir d'hériter du paramètre des paramètres de la tâche de données.
- La sélection d'Activé créera des tables et permettra de gérer le traitement ELT associé.
- Si cette option est Désactivée, des vues effectuant des transformations à la volée seront créées.
L'option Chargement incrémentiel vous permet d'ajuster la requête pour un chargement de données incrémentiel en appliquant des filtres ou d'autres conditions afin de réduire l'ensemble des données traitées via des macros. L'option Chargement incrémentiel est disponible uniquement si les données sont matérialisées sous forme de tables.
- Lorsque l'option Chargement incrémentiel est Activée
  
  La première exécution de la tâche effectuera un chargement initial, insérant l'ensemble des résultats de la requête dans votre table cible. Les exécutions suivantes effectueront des chargements incrémentiels en fonction des filtres ou conditions spécifiques que vous avez définis pour le traitement incrémentiel. Lors du chargement incrémentiel, la tâche traitera uniquement les données sous forme de mise à jour ou d'insertion ; les suppressions ne sont pas prises en charge.
- Lorsque l'option Chargement incrémentiel est Désactivée
  
  La première exécution de la tâche effectuera un chargement initial, insérant l'ensemble des résultats de la requête dans votre table cible. Les exécutions suivantes traiteront l'ensemble des résultats de la requête en les comparant à votre table cible et en traitant les enregistrements nouveaux, modifiés ou supprimés.
Note InformationsSi la requête sélectionne tous les enregistrements qui devraient exister dans la cible, désactivez Chargement incrémentiel. Les enregistrements non sélectionnés seront supprimés de la cible.
Cliquez sur Ajouter lorsque vous êtes prêt à créer la transformation SQL.

Éditer la transformation SQL apparaît.
Saisissez la requête pour renvoyer la table basée sur SQL dans Requête SELECT.

La requête peut lire uniquement les ensembles de données sources que vous avez sélectionnés, mais vous pouvez ajouter davantage d'ensembles de données sources via Ajouter.

Vous pouvez effectuer une sélection parmi les Ensembles de données et les Colonnes.
Si l'option Chargement incrémentiel est utilisée, vous pouvez utiliser des Macros pour adapter la requête à exécuter si un chargement initial ou un chargement incrémentiel est effectué.

Pour plus d'informations, voir Utilisation de macros pour adapter la requête au type de chargement.
Cliquez sur Extraire les paramètres pour analyser les paramètres de la requête SELECT.

Les paramètres doivent être mappés vers les objets sources ou cibles. Vous pouvez mapper vers
- Structures historiques de type 2 (_history)
- Structures existantes
- Objets cibles
  
  Vous ne pouvez mapper que des objets cibles lorsqu'un paramètre est défini à l'intérieur d'une macro incrémentielle.
Note ConseilVous pouvez remplacer un paramètre par une référence pour obtenir une requête plus facile à lire. Modifiez la valeur de Paramètre et la référence dans Requête SELECT changera elle aussi.
Cliquez sur Décrire la table pour obtenir un aperçu des métadonnées de sortie de la requête.

Vous pouvez également afficher un aperçu des résultats via un échantillon des données.

Affichage d'un aperçu des données
Assurez-vous que la table comporte une clé primaire.

Vous pouvez également éditer les paramètres de métadonnées.
- Définissez des clés primaires dans la colonne Clé.
- Déterminez si la colonne peut être nulle dans la colonne Peut être nul.
- Sélectionnez une colonne et cliquez sur Éditer pour définir le nom de colonne, la clé, indiquer si elle peut être nulle et définir le type de données.
Une fois que vous êtes satisfait des résultats de la requête, cliquez sur OK. L'ensemble de données est maintenant ajouté à la liste de cibles et vous pouvez en afficher un aperçu.

Affichage d'un aperçu des données

Vous pouvez afficher un aperçu des résultats via un échantillon des données.

L'aperçu des données doit être activé au niveau du client dans Console de gestion Qlik. Vous trouverez l'option Affichage des données dans Intégration de données dans Paramètres, sous Contrôle de fonction.

Pour pouvoir afficher un aperçu des données, vous devez recevoir les rôles suivants :

Peut afficher des données dans l'espace dans lequel réside la connexion de données.
Accès en lecture dans l'espace dans lequel réside le projet de données.

Pour afficher un aperçu des résultats :

Cliquez sur Afficher les données.

Les résultats via un échantillon des données apparaissent. Vous pouvez définir le nombre de lignes de données à inclure dans l'échantillon dans Nombre de lignes.

Vous pouvez filtrer l'échantillon de données de deux manières différentes :

Utilisez pour filtrer l'échantillon de données à récupérer.

Par exemple, si vous appliquez le filtre ${OrderYear}>2023 et si Nombre de lignes est défini sur 10, vous obtiendrez un échantillon de 10 commandes en 2024.
Filtrez l'échantillon de données en fonction d'une colonne spécifique.

Cela affectera uniquement l'échantillon de données existant. Si vous avez utilisé pour inclure uniquement des commandes de 2024 et si vous avez défini le filtre de colonne pour afficher des commandes de 2022, vous obtiendrez un échantillon vide.

Vous pouvez trier l'échantillon de données en fonction d'une colonne spécifique. Le tri affectera uniquement l'échantillon de données existant. Si vous avez utilisé filtre pour inclure uniquement des commandes de 2024 et si vous inversez l'ordre de tri, l'échantillon de données continuera à ne contenir que des commandes de 2024.

Utilisation de macros pour adapter la requête au type de chargement

Si l'option Chargement incrémentiel est utilisée, vous pouvez utiliser des Macros pour adapter la requête à exécuter si un chargement initial ou un chargement incrémentiel est effectué.

Sélectionnez la macro et ajoutez la requête à l'intérieur de la clause correspondante.

Q_RUN_INCREMENTAL ajoute une clause dans laquelle vous pouvez placer une requête exécutée uniquement en cas de chargement incrémentiel.
Q_RUN_INITIAL_LOAD ajoute une clause dans laquelle vous pouvez placer une requête exécutée uniquement en cas de chargement initial.

Vous n'avez pas besoin de spécifier la requête complète avec chaque macro. Vous pouvez ajuster le code ou en ajouter en fonction de votre cas d'utilisation.

Cas d'utilisation : filtre incrémentiel utilisant une plage de dates :

Dans ce cas d'utilisation, un ensemble de données provenant de commandes doit être transformé via une requête SQL personnalisée. Étant donné qu'il s'agit d'un ensemble de données volumineux, vous voulez inclure un filtrage incrémentiel pour vous assurer que le chargement incrémentiel tient compte uniquement des commandes des 12 dernières heures.

Voici la requête de base :

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM “mydb”.”myStorageTask”.”ORDER” O JOIN “mydb”.”myStorageTask”.”ORDER_DETAIL” OD ON O.ORDER_ID = OD.ORDER_ID

Voici le filtre à appliquer lors de l'exécution incrémentielle, où vous tenez compte uniquement des commandes ajoutées au cours des 12 dernières heures :

WHERE OD.UPDATE_DTM > DATEADD(hour, -12, CURRENT_TIMESTAMP())

Lors de la mise en œuvre d'une table basée sur SQL, il est recommandé de remplacer les noms de table par la notation ${TABLE}. Cela empêche les noms de schéma et de base de données codés en dur susceptibles de changer d'un environnement à un autre, par exemple, lors du déplacement d'un environnement de développement vers un environnement de production.

Voici la requête complète, qui utilise la macro de filtre et des noms de table remplacés par la notation ${TABLE} :

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID {{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}} WHERE OD.UPDATE_DTM > DATEADD(hour, -12, CURRENT_TIMESTAMP() ) {{/QLIK_FUNC_IF}}

On obtient la requête de chargement initial :

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID

Et la requête de chargement incrémentiel :

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT , OD.LAST_UPDATE_DTM FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID WHERE OD.UPDATE_DTM > DATEADD(hour, -12, CURRENT_TIMESTAMP() )

Cas d'utilisation : filtre incrémentiel utilisant des données dans la cible :

Dans ce cas d'utilisation, un ensemble de données provenant de commandes doit être transformé via une requête SQL personnalisée. Étant donné que les commandes représentent un ensemble de données volumineux, vous voulez inclure un filtrage incrémentiel pour vous assurer que le chargement incrémentiel tient compte uniquement des enregistrements des détails des commandes plus récents que les données de la table cible. Vous souhaitez également indiquer si une ligne a été traitée par le chargement initial ou un chargement incrémentiel.

Comme vous pouvez le voir dans cet exemple, il est possible d'utiliser les macros pour ajuster toute partie de la requête afin d'effectuer un chargement initial ou un chargement incrémentiel.

Voici la requête de base :

Il s'agit du filtre à appliquer lors de l'exécution incrémentielle. “ORDERS_DERIVED” est le nom de l'ensemble de données cible.

WHERE OD.UPDATE_DTM > = (SELECT MAX(LAST_UPDATE_DTM) FROM “mydb”,”myTrasformTask”.”ORDERS_DERIVED” )

Un chargement incrémentiel peut également mapper une notation ${TABLE} vers les objets cibles créés par la tâche de transformation.

Voici la requête complète, qui utilise la macro de filtre et des noms de table remplacés par la notation {TABLE}. LOADED_BY est défini sur INIT si le chargement est initial et sur INCR si le chargement est incrémentiel :

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT, OD.LAST_UPDATE_DTM, {{#QLIK_FUNC_IF Q_RUN_INITIAL_LOAD}} ‘INIT’ as LOADED_BY {{/QLIK_FUNC_IF}} {{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}} ‘INCR’ as LOADED_BY {{/QLIK_FUNC_IF}} FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID {{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}} WHERE OD.UPDATE_DTM >= (SELECT MAX(LAST_UPDATE_DTM) FROM ${TARGET_TABLE} ) {{/QLIK_FUNC_IF}}

On obtient la requête de chargement initial :

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT, OD.LAST_UPDATE_DTM, ‘INIT’ as LOADED_BY FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID

Et la requête de chargement incrémentiel :

SELECT O.ORDER_ID, O.CUSTOMER_ID, OD.PRODUCT_ID, OD.QUANTITY, O.AMOUNT, OD.LAST_UPDATE_DTM, ‘INCR’ as LOADED_BY FROM ${ORDER} O JOIN ${ORDER_DETAIL} OD ON O.ORDER_ID = OD.ORDER_ID WHERE OD.UPDATE_DTM >= (SELECT MAX(LAST_UPDATE_DTM) FROM ${TARGET_TABLE} )

Bonnes pratiques

Lorsque vous ajoutez une transformation SQL, une clé primaire n'est pas automatiquement définie. Ajoutez une clé à l'ensemble de données dans l'onglet Ensemble de données.
Ne créez pas d'en-têtes de colonne dans la sortie transformée manuellement.
Évitez d'utiliser SELECT avec *, car cela risque de renvoyer des colonnes différentes à chaque exécution de la requête. En cas de changement dans le schéma, modifiez la requête en conséquence et exécutez de nouveau Décrire la table. Vous pouvez ensuite éditer les colonnes et ajuster les tables.

Cette page vous a-t-elle aidé ?

Si vous rencontrez des problèmes sur cette page ou dans son contenu – une faute de frappe, une étape manquante ou une erreur technique – dites-nous comment nous améliorer !

Laissez vos commentaires ici