ETL avec Pentaho Data Integration

Une transformation

Vous travaillez chez un commissionnaire de transport. Vous recevez donc des commandes de transport de la part de vos clients et votre rôle est d'attribuer ces commandes à des transporteurs.

Un nouveau logiciel a été implanté dans la société pour suivre les commandes en temps réel et on souhaite donc pouvoir comparer les horaires de chargement et de livraison demandés par le client et les horaires réels obtenus grâce au nouveau logiciel. Ceci permettra d'évaluer les différents retards au niveau des usines (retard pour la préparation de la commande par exemple) mais également ceux qui concernent le transporteur (retard à la livraison par exemple).

Vous avez deux fichiers à disposition :

  • orders.csv contient l'ensemble des commandes après affrètement, c'est-à-dire qu'il contient l'ensemble des commandes envoyées par le client ainsi que le transporteur que vous avez attribué à chaque commande.

    Les champs sont les suivants :

    1. OrderNumber : numéro de commande

    2. LoadingPlace : ville de chargement

    3. DeliveryPlace : ville de livraison

    4. Carrier : le transporteur chargé d'effectuer le transport

    5. LoadingDate : date de chargement demandée par le client

    6. ETL (Estimated Time of Loading) : heure de chargement demandée par le client

    7. DeliveryDate : date de livraison demandée par le client

    8. ETA (Estimated Time of Arrival) : heure de livraison demandée par le client

  • realtime.csv contient les commandes exploitées dans le logiciel de suivi. On y retrouve donc certaines commandes avec des horaires réels (pas toujours complets).

    Les champs sont les suivants :

    1. OrderNumber : numéro de commande

    2. RealLoadingDate : date réelle de chargement

    3. RealArrivalAtPlaceOfLoading : heure réelle de chargement

    4. RealDeliveryDate : date réelle de livraison

    5. RealArrivalAtPlaceOfDelivery : heure réelle de livraison

orders.csv.zip

1
OrderNumber;LoadingPlace;DeliveryPlace;Carrier;LoadingDate;ETL;DeliveryDate;ETA
2
60055412;Thourotte;Dabrowa;Schade Polska;22-10-2014;10:00;25-10-2014;16:00
3
60087412;Torgau;Mannheim;Offergeld;10-05-2014;08:00;10-05-2014;18:00
4
60148965;Pise;Aniche;Lannutti;16-07-2014;06:00;18-07-2014;15:00
5
60058999;Low Eggborough;Aniche;Campeys of Selby;02-01-2014;16:00;03-01-2014;14:00
6
60016071;Salaise sur Sanne;Carentan;Transports Elero;03-03-2014;08:00;04-03-2014;10:00
7
60023562;Dabrowa;Jarozowiesc;ESA Poland;05-08-2014;06:00;06-08-2014;05:00
8
60056452;Aviles;Arbos;Carcaba;23-09-2014;12:00;23-09-2014;19:00
9
60023568;Aviles;Thourotte;Glasspartners;15-10-2014;15:00;17-10-2014;13:00
10
60098745;Aniche;Mannheim;Elbe Torgau Transporte;18-11-2014;09:00;18-11-2014;17:00
11
60032568;Carentan;Low Eggborough;Avitrans;10-02-2014;11:00;11-02-2014;10:00
12
60145963;Salaise sur Sanne;Szecin;Offergeld;16-03-2014;09:00;19-03-2014;18:00
13
60023231;Torgau;Dabrowa;Polonia Logistyka;20-06-2014;10:00;22-06-2014;12:00
14
60014569;Arbos;Lisbonne;Lannutti; 31-03-2014;06:00;01-04-2014;14:00
15
60023548;Thourotte;Pise;PKM Logistique;28-02-2014;16:00;02-03-2014;11:00
16
60025897;Mannheim;Low Eggborough;Campeys of Selby;25-07-2014;11:00;28-07-2014;09:00

realtime.csv.zip

1
OrderNumber;RealLoadingDate;RealArrivalAtPlaceOfLoading;RealDeliveryDate;RealArrivalAtPlaceOfDelivery
2
60055412;22-10-2014;10:45;25-10-2014;
3
60087412;10-05-2014;07:05;10-05-2014;18:30
4
60148965;;06:02;18-07-2014;14:00
5
60016071;04-03-2014;06:00;05-03-2014;11:25
6
60023562;05-08-2014;14:00;06-08-2014;11:30
7
60056452;;11:55;23-09-2014;19:05
8
60023568;15-10-2014;15:00;17-10-2014;16:00
9
60098745;18-11-2014;;18-11-2014;16:42
10
60032568;;;;
11
60145963;15-03-2014;19:50;19-03-2014;16:20
12
60023231;20-06-2014;09:00;;12:03
13
60014569;;;;
14
60023548;28-02-2014;16:00;02-03-2014;11:00

On veut procéder à la jointure des deux fichiers CSV. Pour cela, nous devons procéder en deux étapes car une jointure nécessite d'avoir trié les données au préalable.

Question

Créez une nouvelle transformation :

  • Fichier > Nouveau > Transformation

    La transformation, nommée par défaut Transformation 1 apparaît alors dans l'onglet Navigateur

  • Enregistrer

  • Sélectionner le répertoire dans lequel vous souhaitez enregistrer votre fichier et renommer la transformation si vous le souhaitez

  • Un fichier nomTransformation.ktr a été créé dans le répertoire que vous avez désigné.

Solution

Complément

Pour des transformations plus complexes que celle que nous allons réaliser, il est possible de changer le paramétrage en sélectionnant Paramètres après un clic droit sur votre transformation.

Vous remarquez qu'il était également possible de renommer la transformation et d'indiquer le répertoire de sauvegarde directement dans cette fenêtre.

Question

Ajoutez les deux fichiers sources.

Indice

Pour chaque fichier CSV, choisir dans la palette une Extraction depuis fichier CSV.

Indice

  • Aller dans l'onglet Palette de création.

  • Extraction > Extraction depuis fichier CSV.

  • Glisser-déposer deux de ces éléments vers la partie droite.

  • Configurer chaque élément.

Solution

Pour chaque élément, suivre la procédure suivante :

  • Double-cliquer sur l'élément

  • Indiquer un nom d'étape (par exemple, le nom du fichier CSV)

  • Importer le fichier en cliquant sur Parcourir

  • Fixer les séparateurs de champs à ;

  • Cliquer sur Récupérer Champs

  • Cliquer sur OK

Remarque

A ce stade, tous les champs de votre fichier ont été récupérés et leur type a été automatiquement déduit de l'analyse qui a été faite du fichier par le logiciel.

Complément

En cliquant sur Prévisualiser, vous pouvez vérifier que toutes vos données sont importées correctement.

Question

Faire un tri sur les deux fichiers (le tri est un préalable indispensable à la jointure).

Indice

  • Insérer deux étapes Transformation > Tri lignes

  • Relier chaque étape d'extraction avec une étape de tri

    1. Sélectionner l'étape source et maintenir Shift enfoncée tout en glissant vers l'étape destination

    2. Choisir Sortie principale de l'étape

  • Configurer le tri pour qu'il se fasse sur le champ OrderNumber

Solution

Pour chaque étape de tri, suivre la procédure suivante :

  • Double-cliquer sur l'élément

  • Cliquer sur Récupérer champs

  • mettre Ascendant à N pour tous les champs sauf OrderNumber

Remarque

On ordonne nos données en fonction du numéro de commande car c'est l'élément commun de nos deux sources de données et celui sur lequel nous baserons notre jointure.

Question

Faire la jointure.

Indice

  • Insérer une étape Jointure lignes > Jointure comparaison de lignes

  • Relier les deux étapes de tri à cette étape de jointure

  • Configurer une jointure externe gauche sur le champ OrderNumber

Solution

  • Double-cliquer sur l'élément

  • Dans Première étape sélectionner le tri correspondant au fichier Orders et sélectionner le second tri pour Seconde étape

  • Fixer Type Jointure à Left Outer

  • Récupérer les champs clés pour les deux étapes

  • Ne conserver que le champ OrderNumber dans les deux cas

  • Cliquer sur OK

Complément

En cliquant droit sur votre étape de jointure, vous pouvez choisir de prévisualiser votre flux de sortie.

Vous remarquez ainsi qu'on a bien toutes les commandes avec les horaires estimés et réels. On retrouve également les commandes qui ne figuraient pas dans le fichier realtime.csv (ex 60058999) avec des valeurs nulles dans les champs non renseignés, d'où le choix d'une jointure externe gauche, pour ne pas perdre les informations du fichier de référence orders.csv.

Pour l'analyse future de nos données, l'entreprise a souhaité que les horaires manquants soient considérés comme une "vraie" information.

Cela veut dire qu'on ne veut pas de valeurs nulles dans notre table mais plutôt une information du type "données inconnues" lorsqu'il manque une date ou un horaire.

Question

Remplacez les valeurs nulles qui concernent les dates et les horaires réels avec une information de type String.

Indice

Mettre les champs de type Date au type String.

Indice

  • Insérer une étape Divers > Remplacer valeur nulle

  • Relier la jointure à cette étape

  • Configurer l'élément

Solution

  • Double-cliquer sur l'élément

  • Cocher Sélection par champs

  • Récupérer les champs

  • Ne conserver que les champs nommés Realxxxxxxxx dans la liste

  • Dans la colonne Remplacer par mettre la chaîne de caractères que vous voulez faire apparaître

  • cliquer sur OK

Conseil

Prévisualisez votre flux de sortie pour être sûr que les valeurs nulles ont bien été remplacées.

Question

Supprimez la colonne OrderNumber_1 de votre flux de données.

Indice

  • Insérer une étape Transformation -> Altération structure de flux

  • Relier la dernière étape avec la nouvelle

  • Configurer l'élément pour obtenir le résultat demandé

Solution

  • Double-cliquer sur l'élément

  • Aller dans l'onglet Retirer

  • Récupérer champs

  • Retirer tous les champs de la liste sauf orderNumber_1

  • Cliquer sur OK

Question

Exportez votre table dans un fichier Excel.

Indice

  • Insérer une étape Alimentation -> Alimentation fichier MS Excel

  • Relier cette étape avec la précédente

  • Indiquer l'emplacement et le nom du fichier à sauvegarder

Solution

  • Double-cliquer sur l'élément

  • Parcourir pour choisir le répertoire de sauvegarde et donner un nom à votre fichier

  • Dans l'onglet Champs, cliquer sur Récupérer champs

  • Cliquer sur OK

La modélisation de votre transformation est maintenant terminée et vous devriez avoir le résultat suivant :

Question

Exécutez votre transformation.

Solution

Dans le menu principal Actions -> Exécuter puis cliquer sur Démarrer dans la fenêtre qui s'affiche.

Remarque

Dans la partie basse de votre écran, vous trouvez les résultats d'exécution avec pour chaque étape un détail sur le nombre de lignes lues, écrites, etc...

Attention

Si vous voyez une ligne rouge apparaître dans la partie basse de votre espace de travail comme dans la figure ci-dessous, c'est qu'une erreur a été détectée sur l'étape surlignée.

Remarque

Si vous n'avez pas d'erreur dans l'exécution de votre transformation, vous devriez avoir un fichier Excel contenant toutes vos commandes avec les différents horaires dans le répertoire que vous aviez choisi.

Solution

Transformation correctement configurée : exercice1.ktr

PrécédentPrécédentSuivantSuivant
AccueilAccueilImprimerImprimer Morgane Becret, Stéphane Crozat, 2014-2015 Paternité - Partage des Conditions Initiales à l'IdentiqueRéalisé avec Scenari (nouvelle fenêtre)