Génération de contenu dans une base de données, extraction, traitement des données et jointures

Dans ce tutoriel, nous experts Talend vont vous expliquer comment générer et lire du contenu dans une base de données dans le studio Talend Data Integration. Vous apprendrez ensuite à traiter ses données en filtrant les lignes et les colonnes mais également en transformant et en agrégeant les données. Enfin, ce tutoriel Talend vous guidera dans la création de jointures simples avec le composant tJoin. 

Génération des données dans la base

Pour commencer, nous allons vous expliquer comment créer un fichier de correspondance entre plusieurs valeurs (dans ce tutoriel des personnes et des voitures) et insérer les données des fichiers dans une base de données.

Reprenez le Job « Générateur » que vous avez créé grâce au tutoriel précédant.

De même que pour les 2 autres fichiers, créez un sous-job générant un fichier personnes_voitures.csv. Il permettra d’associer aléatoirement une personne à une voiture.

Donnez à ce fichier le schéma suivant :

- Id : Integer

- Immat : Integer

Puis, dans le tRowGenerator, appliquez les fonctions suivantes :

- Id : Numeric.Random

Paramètres :

• Min value : 1
• Max value : 105

- Immat : Numeric.Random

Paramètres :

• Min value : 1000
• Max value : 1104

Dans ce tutoriel, les personnes ont un id allant de 1 à 100 et les voitures une immatriculation allant de 1000 à 1099.

Cela nous assure donc (pas à 100% car il s’agit de hasard) d’avoir des lignes associant une personne existante avec une voiture existante mais aussi d’avoir des lignes ne correspondant ni à une personne, ni à une voiture existante.

Exécutez le job Talend et assurez-vous que votre fichier est bien créé et que les données sont cohérentes.

Vous devriez avoir un job comme ceci :

Nous allons maintenant insérer ces données dans la base de données.

Pour cela, rajoutez un composant tDBOutput à droite de chaque fichier. Dans l’onglet « Composant » du TDBOutput, choisissez le type de base de données et cliquez sur « Apply ».

Il est également possible de faire apparaître le composant tDBOuput avec le bon type de base de données présélectionné en cherchant le composant correspondant tel qu’il existait dans les versions précédentes de Talend (par exemple : tMysqlOutput).

Configurez chaque propriété de connexion selon votre base de données et nommez vos tables respectivement :

- Personne

- Voiture

- Personne_voiture

Dans le champ « Action », choisissez « Supprimer la table si elle existe et la créer » et dans le champ « Action sur les données », choisissez « Insert ».

Dans les paramètres avancés, remplissez le champ « Paramètres JDBC supplémentaires » avec « &serverTimezone=Europe/Paris », cela permettra d’éviter les erreurs dues à la Timezone de la base de données.

Ajoutez également le paramètre « &useSSL=false » afin d’éviter les erreurs dues à une connexion locale non SSL à la base de données.

Créez ensuite un flux Main pour chaque composant.

Si Talend vous propose de « récupérer le schéma » cliquez sur Oui. Si ce n’est pas le cas et que le schéma de votre composant reste vide, cliquez sur « Sync colonnes » dans ses propriétés.

Les conversions de type des colonnes entre celles du fichier et celles à insérer dans la base devraient correctement s’effectuer.

Vérifiez tout de même pour la table personne que la date de naissance est en type DATETIME. En fonction de la version de MySQL il vous faudra peut-être modifier le type du champ « actif » en TINYINT à la place de BIT et inversement.

Exécutez votre Job et assurez-vous d’avoir vos 3 tables dans la base de données.

Ouvrez vos tables et vérifiez que vous avez toutes vos colonnes dans chaque table.

Exemple pour la table 'Personne' :

Pour de multiples raisons, les insertions peuvent poser des soucis.

Vous pouvez par exemple modifier le schéma du tDBOutput de votre table personne et changer la taille de la colonne nom à 1 (ce qui est moins que la taille minimale que l’on aura pour les prénoms) et lancer à nouveau votre Job.

Vous observerez qu’il y a une erreur d’insertion sur cette table dans la console, logique car les valeurs dépassent la taille définie.

Pour capturer les lignes rejetées lors de l’insertion, allez dans les paramètres avancés du composant tDBOutput et décochez la case « Insertions étendues ». Cela permet d’utiliser la sortie de rejets.

Ajoutez un tLogRow à droite du composant et reliez le tDBOutput au tLogRow avec un flux de type « Rejects ».

Relancez votre job. Vous pouvez voir que les lignes rejetées s’affichent dans la console.

Remettez la valeur d’origine pour la longueur du champ nom dans le schéma.

Ajoutez l’affichage des rejets pour vos sous-job voitures et personnes_voitures.

Versionnez votre Job.

Lecture des données depuis la base 

Maintenant que vous avez avez appris à créer des tables dans une base de données, nous allons voir comment afficher les données des 3 tables précédentes dans la console.

Créez un nouveau Job nommé « LectureBase ».

Cette fois-ci nous ne voulons pas écrire dans la base mais en lire les données. Nous n’avons donc pas besoin d’un composant de sortie mais d’un composant d’entrée.

Ajoutez donc un composant tDBInput et configurer ses propriétés de connexion comme pour le job précédent et saisissez le nom de la table contenant les personnes.

Cliquez ensuite sur « Modifier le schéma » et faites-le correspondre au schéma de votre table, qui pour rappel est :

- Id : Integer

- Nom : String

- Prenom : String

- Ville : String

- Date_de_naissance : Date

- Actif : boolean

Une astuce consiste à modifier directement la requête dans la fenêtre dédiée en lui demandant de faire un « Select * » sur la table puis en cliquant sur « Guess Schema ». Le schéma devrait se charger automatiquement.

Cliquez sur le bouton « Guess Query » pour faire correspondre la requête au schéma défini.

Exécutez votre Job et vérifiez que vos données apparaissent dans la console.

Pour rappel, en cas d’erreur de TimeZone lors de l’exécution, allez dans les paramètres avancés et remplissez le champ « Paramètres JDBC supplémentaires » avec « &serverTimezone=Europe/Paris », même chose pour le paramètre « &useSSL=false ».

Versionnez votre Job.

Filtrage des lignes et des colonnes

Nos experts Talend vont maintenant vous montrer comment appliquer des filtres sur les lignes et les colonnes récupérées avec des composants simples.

Reprenez le Job « LectureBase ».

On souhaite n’afficher dans la console que les colonnes nom et prénom de la table personne.

Pour cela nous avons besoin de filtrer les colonnes avec l’aide d’un composant intermédiaire nommé tFilterColumn.

Ajoutez le tFilterColumns entre le tDBInput et le tLogRow. Il est possible de faire glisser depuis la palette le composant directement sur le lien reliant les deux autres composants pour que celui-ci s’insère entre eux sans avoir à supprimer puis recréer les liaisons.

Lorsqu’on vous le propose, récupérez le schéma du composant. Dans le cas contraire, il est toujours possible de le récupérer par la suite en cliquant sur le bouton « Sync colonnes » de ses propriétés.

Après avoir sélectionné le composant, cliquez sur « Modifier le schéma ».

Pour la première fois vous obtenez une fenêtre avec 2 schémas côte à côte. A gauche, il s’agit du schéma en sortie du composant précédent, donc en entrée de ce composant, et à droite le schéma en sortie de ce composant.

Sachant que nous ne souhaitons avoir en sortie que les colonnes nom et prénom, il suffit de supprimer dans le schéma de droite les colonnes non voulues.

Cliquez sur « OK » puis acceptez de « propager les modifications ». Cela aura pour conséquence de modifier le schéma des éléments suivants (ici le tLogRow) en fonction de ce que vous avez défini.

Exécutez votre Job Talend et vérifier dans la console que vous n’avez plus que les noms et prénoms des personnes.

Versionnez votre Job.

Nous souhaitons ensuite filtrer les lignes en fonctions de certaines valeurs. Pour cela nous utiliserons le composant tFilterRow.

Supprimez votre tFilterColumns pour le remplacer par un tFilterRow et refaite vos liaisons entre les composants.

N’oubliez pas de cliquer sur « Sync colonnes » dans le tLogRow pour avoir à nouveau toutes les colonnes.

Ouvrez l’onglet de configuration du tFilterRow. Vous pouvez dès à présent ajouter des conditions dans la case prévue à cet effet en cliquant sur le « + ».

Par exemple nous voulons toutes les personnes actives dont le nom est « Jefferson ». Ajoutez donc 2 conditions en disant que la colonne « actif » vaut true (vrai) pour la première, et que la colonne « nom » vaut « Jefferson » pour la seconde.

Lancez votre Job. Il est possible qu’aucun résultat ne ressorte, tout dépend des données générées en amont.

Si aucune ligne ne correspond, regardez dans votre base de données et essayez avec un autre nom que vous êtes sûr d’avoir.

Transformation et agrégation des données

Dans cette étape, nous allons voir comment transformer et agréger des données avant affichage.

Toujours dans le Job « LectureBase », créez un sous-job qui va lire les données depuis la table voiture et les afficher dans la console.

Comme effectué précédemment, remplissez les paramètres de connexion de votre tDBInput et modifier le schéma en conséquence.

Exécutez votre Job pour vous assurer du bon affichage de vos données.

Si vous voulez y voir plus clair et donc ne plus afficher les données de la table personne de votre sous-job précédent, il est possible de sélectionner le premier composant du sous-job, de faire un clic droit puis « désactiver le Sous-job courant ».

Nous souhaitons ensuite afficher dans la console des voitures en remplaçant toutes les voitures de couleur rouge par la couleur rose.

Pour cela, insérez le composant tReplace entre les deux autres composants.

Dans ses propriétés vous pouvez alors ajouter des lignes à chercher/remplacer. Choisissez donc « couleur » comme colonne d’entrée, « rouge » comme valeur à rechercher et « rose » comme valeur à remplacer.

Vérifiez que « Tout le mot » est coché, et que « Sensible à la casse » est décoché (car potentiellement dans la base nos couleurs pourraient avoir par exemple la première lettre en majuscule).

Exécutez votre Job et vérifiez que vos données apparaissent dans la console.

Pour rappel, en cas d’erreur de TimeZone lors de l’exécution, allez dans les paramètres avancés et remplissez le champ « Paramètres JDBC supplémentaires » avec « &serverTimezone=Europe/Paris », même chose pour le paramètre « &useSSL=false ».

Versionnez votre Job.

Imaginons maintenant que vous souhaitez utiliser un fichier de configuration pour choisir les éléments à remplacer et par quelles valeurs les remplacer. Pour cela, créez un fichier CSV nommé « correspondance_voitures.csv » qui nous servira à remplacer des marques par d’autres.

A l’intérieur de ce fichier, créez une ligne d’en tête avec 2 colonnes : « ancienne » et « nouvelle », puis saisissez deux lignes avec en première colonne la marque à remplacer et en deuxième colonne la nouvelle marque.

Par exemple :

Ajoutez sur votre Job un composant tFileInputDelimited qui permettra de lire le contenu du fichier CSV et remplissez ses propriétés ainsi que son schéma.

Remplacez ensuite votre tReplace par un tReplaceList et récréez vos liaisons.

Ajouter ensuite un flux Main allant du tFileInputDelimited vers le tReplaceList. Une fois validé, observez que le flux est devenu un « lookup », c’est-à-dire un flux de correspondance.

Vous pouvez alors configurer le tFileReplaceList en lui indiquant quelle colonne du fichier sert de recherche et quelle colonne du fichier sert de nouvelle valeur, puis cocher la case correspondant à la colonne de la base à remplacer.

Lancez votre Job et vérifiez dans la console que vos marques ont été remplacés par les nouvelles.

Versionnez votre Job, puis supprimez le tReplaceList et le tFileInputDelimited.

Nous allons maintenant chercher à agréger les données pour compter un nombre de lignes. Par exemple nous souhaitons afficher dans la console le nombre de voitures par marque.

Pour pouvoir agréger les données, ajoutez le composant tAggregateRow entre le tDBInput et le tLogRow puis recréez les liaisons.

Modifier le schéma du tAggregateRow pour ne renvoyer que la marque ainsi qu’une nouvelle colonne de type Integer que l’on nommera compteur.

Modifiez ensuite la propriété « Group By » pour indiquer que nous groupons les données par marque, puis la propriété « Opérations » pour indiquer que nous souhaitons compter les marques dans la colonne compteur.

Lancer votre job. Vous devriez obtenir dans la console chaque marque avec son nombre de voitures.

Effectuer des jointures avec tJoin

Dans cette dernière partie du tutoriel, nos expert Talend vous expliquent comment effectuer des jointures simples de données et comprendre la limite du composant tJoin. 

Pour commencer, créez un nouveau job que l’on nommera « tJoin ».

Le but de ce job sera de joindre les personnes et les voitures pour obtenir la liste de toutes les personnes et de leur(s) voiture(s) associée(s) s’ils en ont une.

Nous allons d'abord créer un premier sous-job pour obtenir la liste des personnes propriétaires d’une voiture.

Pour cela, utilisez à nouveau un tDBInput pour lire la table personne ainsi qu’un second pour lire la table personne_voiture.

Ajouter un composant tJoin qui nous permettra de faire la jointure entre ces deux tables et liez les deux composants précédents à celui-ci avec la table personne en tant que Main et la table personne_voiture en tant que Lookup. Pensez à récupérer le schéma ou à le recréer si besoin dans ce nouveau composant.

Dans ses propriétés, définissez la clé de jointure, à savoir « id » que ce soit pour la clé d’entrée ou la clé Lookup et cochez la case « Inner join » pour n’obtenir que les personnes ayant réellement une voiture et pouvoir avoir les autres personnes en rejet.

Ajoutez ensuite 2 tLogRow.

Reliez le tJoin au premier tLogRow avec un flux Main puis reliez le tJoin au second tLogRow avec un flux Inner Join Rejects.

Lancez votre job.

Nous souhaitons maintenant lier la table des voitures pour obtenir le détail des voitures par personne, vous remarquerez cependant qu’il n’est pas possible d’ajouter une autre entrée sur le composant tJoin, nous allons donc devoir enchaîner un second tJoin avec les voitures.

Commencez par modifier le schéma de sortie du tJoin afin de renvoyer toutes les informations des personnes avec en plus l’immatriculation de la voiture.

Cochez également la case « Inclure la colonne Lookup dans la sortie » qui permettra de renvoyer la valeur de l’immatriculation.

Ajoutez ensuite un second tJoin entre le premier et le tLogRow du flux Main et récupérez le schéma.

Ajoutez un tDBInput lisant la table voiture et connectez-le au nouveau tJoin en tant que Lookup.

Faîtes correspondre les clés « immat » dans ses propriétés et cochez la case « Inner Join ».

Comme pour le tJoin précédent, modifier le schéma et cochez la case « Inclure la colonne Lookup dans la sortie » pour renvoyer les informations de la voiture.

Lancez le job et observez la console. Vous avez obtenu la liste des personnes avec une voiture associée.

Vérifiez la table personne_voiture dans votre base de données et remarquez que certaines personnes ont plusieurs voitures, information que nous n’avons pas ici.

Nous en venons aux limites de ce composant pour effectuer des jointures. En effet tel que nous avons créé ce job il est impossible d’obtenir une liste avec une ligne pour chaque couple personne - voiture.

Nous pourrions prendre le problème dans le sens inverse et partir de la table personne_voiture pour avoir réellement une ligne pour chaque couple personne - voiture, cependant il serait alors impossible d’obtenir les personnes sans voiture et rappelons-nous que notre fichier contient potentiellement des couples personne – voiture qui n’existent pas, la jointure sera donc vouée à l’échec.

Le composant tJoin est donc utile pour réaliser des jointures simples et notamment des Inner Join mais il sera vite limité contrairement au composant tMap que nous verrons par la suite dans un autre tutoriel Talend.