tMysqlSCD - 6.1

Composants Talend Guide de référence

EnrichVersion
6.1
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Création et développement
Gouvernance de données
Qualité et préparation de données
EnrichPlatform
Studio Talend

Propriétés du tMysqlSCD

Famille du composant

Databases/Business Intelligence/MySQL

 

Fonction

Le composant tMysqlSCD reflète et traque les modifications d'une table SCD MySQL dédiée.

Objectif

Le tMysqlSCD répond à des besoins en transformation Slowly Changing Dimension, en lisant régulièrement une source de données et en répertoriant les modifications dans une table SCD dédiée.

Basic settings

Property type

Peut être Built-in ou Repository.

Depuis la version 5.6, les modes Built-In et Repository sont disponibles dans toutes les solutions de Talend.

 

 

Built-in : Propriétés utilisées ponctuellement.

 

 

Repository : Sélectionnez le fichier de propriétés du composant. Les champs suivants sont alors pré-remplis à l'aide des données collectées.

 

Use an existing connection

Cochez cette case et sélectionnez le composant de connexion adéquat à partir de la liste Component List pour réutiliser les paramètres d'une connexion que vous avez déjà définie.

Note

Lorsqu'un Job contient un Job parent et un Job enfant, si vous souhaitez utiliser une connexion existant entre les deux niveaux, par exemple pour partager la connexion créée par le Job parent avec le Job enfant, vous devez :

  1. Au niveau parent, enregistrer la connexion à la base de données à partager, dans la vue Basic settings du composant de connexion créant cette connexion.

  2. Au niveau enfant, utiliser un composant dédié à la création de connexion, afin de lire la connexion enregistrée.

Pour plus d'informations concernant le partage d'une connexion à travers différents niveaux de Jobs, consultez le Guide utilisateur du Studio Talend.

 

DB Version

Sélectionnez la version de Mysql que vous utilisez.

 

Host

Adresse IP du serveur de base de données.

 

Port

Numéro du port d'écoute du serveur de base de données.

 

Database

Nom de la base de données.

 

Username et Password

Informations d'authentification de l'utilisateur de la base de données.

Pour saisir le mot de passe, cliquez sur le bouton [...] à côté du champ Password, puis, dans la boîte de dialogue qui s'ouvre, saisissez le mot de passe entre guillemets doubles, puis cliquez sur OK afin de sauvegarder les paramètres.

 

Table

Nom de la table à créer. Vous ne pouvez créer qu'une seule table à la fois.

 

Action on table

Vous pouvez effectuer l'une des opérations suivantes sur les données de la table sélectionnée :

None : n'effectuer aucune opération de table.

Create a table : créer une table qui n'existe pas encore.

Create table if doesn't exist : créer la table si nécessaire.

 

Schema et Edit schema

Un schéma est une description de lignes, il définit le nombre de champs (colonnes) qui sont traités et passés au composant suivant. Le schéma est soit local (Built-In), soit distant dans le Repository.

Depuis la version 5.6, les modes Built-In et Repository sont disponibles dans toutes les solutions de Talend.

Cliquez sur Edit schema pour modifier le schéma. Si le schéma est en mode Repository, trois options sont disponibles :

  • View schema : sélectionnez cette option afin de voir le schéma.

  • Change to Built-In property : sélectionnez cette option pour passer le schéma en mode Built-In et effectuer des modifications locales.

  • Update repository connection : sélectionnez cette option afin de modifier le schéma stocké dans le référentiel et décider de propager ou non les modifications à tous les Jobs. Si vous souhaitez propager les modifications uniquement au Job courant, sélectionnez No et sélectionnez à nouveau la métadonnée du schéma dans la fenêtre [Repository Content].

 

 

Built-in : Le schéma est créé et conservé ponctuellement pour ce composant seulement. Voir également le Guide utilisateur du Studio Talend.

 

 

Repository : Le schéma existe déjà et est stocké dans le Repository. Ainsi, il peut être réutilisé. Voir également le Guide utilisateur du Studio Talend.

 

SCD Editor

L'éditeur SCD Editor permet de construire et de configurer les données du flux de sortie vers la table Slowly Changing Dimension.

Pour plus d'informations, consultez Méthodologie de gestion du SCD.

 

Use memory saving mode

Cochez cette case pour améliorer les performances du système.

 

Source keys include Null

Cochez cette case pour autoriser, dans les colonnes clés source, les valeurs Null.

Avertissement

Lorsque cette case est cochée, assurez-vous que la valeur de(s) clé(s) source est unique.

 

Die on error

Cette case est décochée par défaut, ce qui vous permet de terminer le traitement avec les lignes sans erreur, et ignorer les lignes en erreur.

Advanced settings

Additional JDBC Parameters

Spécifiez des informations supplémentaires de connexion à la base de données créée. Cette option n'est pas disponible lorsque vous utilisez l'option Use an existing connection dans les Basic settings.

 

End date time details

Spécifiez la valeur de temps du paramètre de date et heure de fin du SCD au format HH:mm:ss. La valeur par défaut pour ce champ est 12:00:00.

Ce champ apparaît uniquement lorsqu'un SCD de Type 2 est utilisé et lorsque Fixed year value est sélectionné pour créer la date de fin du SCD. Pour plus d'informations concernant le SCD de Type 2, consultez Méthodologie de gestion du SCD.

 

tStatCatcher Statistics

Cochez cette case pour collecter les données de log au niveau du composant.

 

Debug mode

Cochez cette case pour afficher chaque étape du processus de d'écriture dans la base de données.

Dynamic settings

Cliquez sur le bouton [+] pour ajouter une ligne à la table. Dans le champ Code, saisissez une variable de contexte afin de sélectionner dynamiquement votre connexion à la base de données parmi celles prévues dans votre Job. Cette fonctionnalité est utile si vous devez accéder à plusieurs tables de bases de données ayant la même structure mais se trouvant dans différentes bases de données, en particulier lorsque vous travaillez dans un environnement dans lequel vous ne pouvez pas changer les paramètres de votre Job, par exemple lorsque votre Job doit être déployé et exécuté dans un Studio Talend indépendant.

La table Dynamic settings n'est disponible que si la case Use an existing connection est cochée dans la vue Basic settings. Lorsqu'un paramètre dynamique est configuré, la liste Component List devient inaccessible dans la vue Basic settings.

Pour des exemples sur l'usage des paramètres dynamiques, consultez Scénario 3 : Lire des données dans des bases de données MySQL à l'aide de connexions dynamiques basées sur les variables de contexte et Scénario : Lire des données à partir de différentes bases de données MySQL à l'aide de paramètres de connexion chargés dynamiquement. Pour plus d'informations concernant les Dynamic settings et les variables de contexte, consultez le Guide utilisateur du Studio Talend.

Global Variables

NB_LINE_UPDATED : nombre de lignes mises à jour. Cette variable est une variable After et retourne un entier.

NB_LINE_INSERTED : nombre de lignes insérées. Cette variable est une variable After et retourne un entier.

NB_LINE_REJECTED : nombre de lignes rejetées. Cette variable est une variable After et retourne un entier.

ERROR_MESSAGE : message d'erreur généré par le composant lorsqu'une erreur survient. Cette variable est une variable After et retourne une chaîne de caractères. Cette variable fonctionne uniquement si la case Die on error est décochée, si le composant a cette option.

Une variable Flow fonctionne durant l'exécution d'un composant. Une variable After fonctionne après l'exécution d'un composant.

Pour renseigner un champ ou une expression à l'aide d'une variable, appuyez sur les touches Ctrl+Espace pour accéder à la liste des variables. A partir de cette liste, vous pouvez choisir la variable que vous souhaitez utiliser.

Pour plus d'informations concernant les variables, consultez le Guide utilisateur du Studio Talend.

Utilisation

Ce composant est un composant de sortie. Par conséquent, il requiert un composant et une connexion de type Row > Main en entrée.

Méthodologie de gestion du SCD

Lors de dimensions à évolution lente ou Slowly Changing Dimensions (SCD), les données changent lentement. L'éditeur SCD permet de construire de manière simple le flux de sortie des données SCD. Dans l'éditeur SCD, vous pouvez relier les colonnes, sélectionner la clé de substitution (Surrogate key) et paramétrer les attributs des colonnes modifiées en combinant les différents types de modifications SCD.

La figure ci-dessous représente l'éditeur SCD.

Clés SCD

Vous devez sélectionner une ou plusieurs colonnes qui serviront de clés permettant d'assurer l'unicité des données entrantes.

Vous devez aussi sélectionner une colonne sur laquelle positionner une clé de substitution (surrogate key) dans la table SCD et la relier à une des colonnes d'entrée de la table source. La valeur de la clé de substitution permet de relier les enregistrements de la table source aux enregistrements de la table SCD. L'éditeur utilise ce mapping pour localiser l'enregistrement dans la table SCD et pour déterminer si un enregistrement est nouveau ou s'il a été modifié. La clé de substitution est généralement la clé primaire de la table source, mais elle peut aussi correspondre à une autre clé à partir du moment où elle permet d'identifier de façon unique un enregistrement et où ses valeurs ne changent pas.

Source keys : Glissez une ou plusieurs colonnes du panneau Unused vers le panneau Source keys. Ces colonnes seront utilisées en tant que clé(s) assurant l'unicité des données entrantes.

Surrogate keys : Paramétrez la colonne dans laquelle la clé de substitution générée sera stockée. Une clé de substitution peut être générée en fonction de la méthode sélectionnée dans la liste Creation.

Creation : Sélectionnez une des méthodes suivantes permettant de générer la clé :

  • Auto increment : la clé est auto-incrémentée.

  • Input field : la clé est fournie par un champ d'entrée.

    Lorsque cette méthode est sélectionnée, vous pouvez glisser le champ correspondant du panneau Unused vers le champ complement.

  • Routine : à partir du champ complement, vous pouvez appuyer sur Ctrl+ Espace afin d'afficher la liste d'auto-complétion et de sélectionner la routine appropriée.

  • Table max +1 : la valeur maximum de la table SCD est incrémentée pour créer une clé de substitution.

  • DB Sequence : à partir du champ complement, vous pouvez saisir le nom de la séquence de la base de données (DB Sequence) existante qui va incrémenter automatiquement la colonne indiquée dans le champ name.

Note

L'option DB Sequence est disponible uniquement depuis l'éditeur SCD du composant tOracleSCD.

Types de SCD

Il existe quatre types de dimensions à évolution lente (Slowly Changing Dimensions) : du Type 0 au Type 3. Vous pouvez, d'un simple glisser-déposer, appliquer n'importe quel type de SCD à n'importe quelle colonne de la table source.

  • Type 0 : ce type de SCD n'est pas beaucoup utilisé. Certaines données dimensionnelles peuvent être écrasées et d'autres peuvent rester inchangées au cours du temps. Ce type de SCD convient lorsque aucun effort n'a été mis en place pour gérer les dimensions à caractère évolutif.

  • Type 1 : aucun historique n'est stocké dans la base de données. Les nouvelles données écrase les anciennes. Utilisez ce type si vous n'avez pas besoin de traquer les changements. Ce type de SCD convient lorsque vous corrigez certaines fautes, par exemple l'orthographe des mots.

  • Type2 : l'intégralité de l'historique est stockée dans la base de données. Ce type de SCD traque les données d'historique en enregistrant un nouvel enregistrement dans la table de dimension avec une nouvelle clé à chaque fois qu'un changement est effectué. Ce type de SCD convient lorsque l'on traque les mises à jour, par exemple.

    Le principe du SCD Type 2 réside dans le fait qu'un nouvel enregistrement est ajouté à la table SCD lorsqu'un changement est détecté dans les colonnes sélectionnées. Notez que bien que plusieurs changements peuvent être effectués au même enregistrement sur plusieurs colonnes paramétrées en Type 2, une seule ligne traquant ces changements est ajoutée à la table SCD.

    Le schéma du SCD Type 2 devrait inclure des colonnes spécifiques au SCD contenant les informations de log standard, notamment :

    -start : ajoute une colonne au schéma de la table SCD contenant la date de début d'un enregistrement. Vous pouvez sélectionner une des colonnes du schéma d'entrée comme date de départ pour la table SCD.

    -end : ajoute une colonne au schéma de la table SCD contenant la date de fin d'un enregistrement. Lorsque l'enregistrement est en cours, la date de fin est de valeur NULL , sinon vous pouvez utiliser une année fixe en sélectionnant Fixed Year Value dans la liste et renseignez l'année fictive dans la cellule d'à côté pour éviter d'avoir une valeur nulle dans le champ end.

    -version : ajoute une colonne au schéma de la table SCD contenant le numéro de version de l'enregistrement.

    -active : ajoute une colonne au schéma de la table SCD contenant les statuts true ou false. Cette colonne permet de repérer facilement les enregistrements actifs.

  • Type 3 : seules les informations sur l'ancienne valeur d'une dimension est écrite dans la base de données. Ce type de SCD traque les changements en utilisant des colonnes séparées. Ce type de SCD convient lorsque l'on traque les valeurs précédentes d'une colonne qui change.

Scénario : Traquer des modifications avec les Slowly Changing Dimensions de type 0 à 3

Ce scénario décrit un Job traquant les modifications effectuées dans quatre des colonnes du fichier délimité source, écrit ces modifications ainsi que l'historique des modifications dans une table SCD et affiche les informations d'erreurs dans la console Run.

Le fichier délimité source contient différentes informations personnelles : des prénoms dans la colonne firstname, des noms dans la colonne lastname, des adresses dans la colonne address, des villes dans la colonne city, l'entreprise de la personne dans la colonne company, son âge dans la colonne age et son statut marital dans la colonne status. Une colonne id permet d'assurer l'unicité des données.

Les changements de statut marital doivent écraser les anciens statuts enregistrés. Ce type de changement correspond à un changement SCD Type 1.

Un nouvel enregistrement dans la table dimensionnelle avec une clé différente sera inséré à chaque fois qu'une personne change d'entreprise. Ce type de changement correspond à un changement SCD Type 2.

Vous allez récupérer l'ancienne ville et l'ancienne adresse d'une personne. Ce type de changement correspond à un changement SCD Type 3.

Pour réaliser ce type de scénario, il est préférable de le séparer en trois étapes principales : la définition du flux principal du Job, le paramétrage de l'éditeur SCD et la création de la table de modification dans la base de données.

Définir le flux principal du Job

  1. Cliquez et déposez les composants suivants dans l'éditeur graphique : tMysqlConnection, tFileInputDelimited, tMysqlSCD, tMysqlCommit ainsi que deux composants tLogRow.

  2. Connectez les composants tFileInputDelimited, le premier tLogRow et le tMysqlSCD à l'aide d'un lien Row Main. Ce sous-job correspond au flux principal du Job.

  3. Connectez le tMysqlConnection au tFileInputDelimited et le tMysqlSCD au tMysqlCommit à l'aide de liens OnComponentOk.

  4. Connectez le tMysqlSCD au second tLogRow à l'aide d'un lien Row Reject. Les colonnes errorCode et errorMessage sont ajoutées au schéma. Cette connexion collecte les informations d'erreurs.

Configurer la connexion à la base de données et le composant d'entrée

  1. Dans l'éditeur graphique, double-cliquez sur le composant tMysqlConnection pour afficher ses propriétés de base dans l'onglet Basic settings et renseigner manuellement les informations de connexion. Lorsque plusieurs composants Database sont utilisés, le composant tMysqlConnection devrait être utilisé pour éviter de renseigner plusieurs fois les mêmes informations de connexion à la base de données.

    Note

    Si vous avez déjà stocké les informations de connexion dans le Repository, glissez l'élément Metadata souhaité dans l'éditeur graphique et les informations de connexion à la base de données apparaîtront automatiquement dans les champs correspondants. Pour plus d'informations sur les métadonnées, consultez le Guide utilisateur du Studio Talend.

    Dans ce scénario, l'objectif est de se connecter à la table SCD dans laquelle toutes les modifications apportées au fichier délimité seront traquées.

  2. Dans l'éditeur graphique, double-cliquez sur le composant tFileInputDelimited pour afficher la vue Basic settings.

  3. Cliquez sur le bouton [...] à côté du champ File Name pour sélectionner le chemin d'accès au fichier délimité dataset.csv source contenant les informations personnelles.

  4. Dans les champs Row et Field Separator, renseignez respectivement les séparateurs de lignes et de champs utilisés dans le fichier source.

    Note

    Les champs File Name, Row separator et Field separators sont obligatoires.

  5. Si nécessaire, renseignez les champs Header, Footer et Limit.

    Dans ce scénario, saisissez 1 dans le champ Header puisque le fichier source contient un en-tête. Laissez le champ Footer à 0 puisque le fichier source ne contient pas de pied de page et ne renseignez pas le champ Limit déterminant le nombre de lignes devant être traitées.

  6. Cliquez sur Edit schema pour décrire la structure des données du fichier délimité source.

    Dans ce scénario, le schéma source est composé de huit colonnes : id,firstname, lastname, address, city, company, age et status.

  7. Dans l'onglet Basic settings du premier composant tLogRow, sélectionnez le mode Table afin que le contenu du fichier source, ainsi que les attributs variants apparaissent dans les cellules du tableau dans la console avant d'être traitées par le composant SCD.

Configurer tMysqlSCD et tMysqlCommit

  1. Dans l'éditeur graphique, double-cliquez sur le composant tMysqlSCD pour paramétrer ses propriétés.

  2. Dans l'onglet Basic settings, cochez la case Use an existing connection pour réutiliser les informations de connexion définies dans les propriétés du composant tMysqlConnection.

  3. Dans le champ Table, saisissez le nom de la table SCD dans laquelle les changements seront stockés.

  4. Si nécessaire, cliquez sur Sync columns pour récupérer toutes les colonnes du fichier délimité.

  5. Dans l'éditeur graphique, double-cliquez sur le composant tMysqlCommit pour paramétrer ses propriétés.

  6. Dans la liste Component list, sélectionnez la connexion adéquate s'il y a plusieurs composants de connexion dans le Job.

  7. Dans l'onglet Basic settings du second composant tLogRow, sélectionnez le mode Table afin que le contenu du fichier source, ainsi que les attributs variants apparaissent dans les cellules d'un tableau.

Paramétrer l'éditeur SCD

  1. Dans l'onglet Basic settings du composant tMysqlSCD, cliquez sur le bouton [...] à côté du champ SCD Editor pour ouvrir l'éditeur SCD editor et construire le flux de données en sortie du SCD.

    Toutes les colonnes du fichier délimité source, provenant du composant précédent et n'ayant pas encore été utilisées, apparaissent dans le panneau Unused de l'éditeur SCD. Tous les autres panneaux de l'éditeur sont vides.

  2. A partir de la liste Unused, glissez la colonne id vers le panneau Source keys pour être utilisée comme clé permettant d'assurer l'unicité des données entrantes.

  3. Dans le panneau Surrogate keys, donnez un nom à la clé de substitution (surrogate key) dans le champ Name, SK1 pour ce scénario.

  4. Dans la liste Creation, sélectionnez la méthode à utiliser pour générer la clé de substitution, Auto-increment pour ce scénario.

  5. Dans la liste Unused, glissez les colonnes firstname et lastname vers le panneau Type 0, les changements effectués dans ces colonnes ne sont pas pertinents dans cet exemple.

  6. Glissez la colonne status vers le panneau Type 1. L'ancienne valeur sera écrasée par la nouvelle.

  7. Glissez la colonne company vers le panneau Type 2. A chaque fois qu'une personne change d'entreprise, un nouvel enregistrement sera inséré dans la table dimensionnelle avec une nouvelle clé.

    Dans la zone Versioning :

    -Paramétrez les colonnes start et end de votre table SCD contenant respectivement les dates de début et de fin. La date de fin est nulle pour les enregistrements en cours jusqu'à ce qu'un changement soit détecté. A ce moment là, la date de fin est renseignée et un nouvel enregistrement est ajouté sans date de fin.

    Dans ce scénario, sélectionnez Fixed Year Value dans la colonne end et saisissez une année fictive pour éviter d'avoir une valeur nulle dans le champ de date de fin.

    -cochez le case version pour conserver le numéro de version de l'enregistrement.

    -cochez la case active pour ajouter la colonne Status contenant les statuts True ou False des enregistrements. True correspond à l'enregistrement en cours et False correspond à l'enregistrement modifié.

  8. Glissez les colonnes address et city vers le panneau Type 3 pour ne récupérer que les informations sur les anciennes valeurs des adresses et des villes.

    Pour plus d'informations sur les types de SCD, consultez Méthodologie de gestion du SCD.

  9. Cliquez sur OK pour valider votre configuration et fermer l'éditeur SCD.

Créer la table SCD

  1. Cliquez sur Edit schema pour afficher le flux de données en entrée et en sortie. Le schéma de sortie du SCD devrait inclure les colonnes spécifiques au SCD contenant les informations de log standard définies dans l'éditeur SCD editor.

    Note

    Si vous modifiez la définition du schéma d'entrée, vous devez vérifier et reconfigurer si nécessaire la définition du flux de sortie dans l'éditeur SCD, afin de s'assurer que la structure des données de sortie est correctement actualisée.

  2. Double-cliquez sur le composant tMysqlSCD pour afficher sa vue Basic settings et sélectionnez Create table if not exists à partir de la liste Action on table pour éviter d'avoir à créer et définir manuellement la table SCD.

Exécuter le Job

Enregistrez votre Job et appuyez sur F6 pour l'exécuter.

La console Run affiche le contenu du fichier délimité d'entrée et votre table SCD est créée dans votre base de données, contenant l'ensemble de données initial.

Janet divorce, déménage à Adelanto au 355 Golf Rd et travaille maintenant chez Greenwood.

Adam s'est marié, a déménagé à Belmont au 2505 Alisson ct. et travaille chez Scoop.

Martin travaille désormais chez Phillips and Brothers.

Mettez à jour le fichier délimité avec ces informations et appuyez sur F6 pour exécuter le Job.

La console affiche les changements apportés aux informations personnelles et la table SCD affiche l'historique des changements valides, ainsi que leur statut et leur numéro de version. Le nom de l'entreprise de Martin dépasse la longueur définie pour la colonne company dans le schéma. Ce changement est donc dirigé vers le flux de rejets, et non sauvegardé dans la table SCD.