Mise en œuvre de PDI

1. Présentation générale des transformations

Dans ce chapitre, 24 transformations sont présentées. Ces traitements font partie des opérations effectuées classiquement en matière d’ETL.

Pour chacune de ces transformations ETL de nombreuses indications techniques sont données (y compris sous forme de recopies d’écran). Le lecteur peut, s’il le souhaite, les reconstituer. Un nom est également préconisé pour la sauvegarde de chacune d’entre elles.

2. Présentation des données

Le jeu de données utilisé dans le cadre de ce chapitre est volontairement simple (peu de tables et une volumétrie réduite). Les données sont principalement stockées dans des bases de données gérées par le SGBD Open Source MySQL. Pour certaines transformations, un recours à d’autres SGBD (Microsoft Access, Microsoft SQL Server) est effectué. Des classeurs Microsoft Excel, des fichiers texte de différents formats (fichiers plats, XML) sont aussi utilisés.

La base de données principale (de nom TDF2003-1), de laquelle seront en général extraites les données, est au format MySQL. Pour déployer simplement cette base de données sous MySQL, les utilisateurs sous Windows ont le choix entre plusieurs solutions Web packagées (intégrant le serveur Web Apache, le serveur de gestion de bases de données MySQL et le logiciel phpMyAdmin pour l’administration aisée des bases de données) :

Une suite Web intégrant ces mêmes composants est aussi disponible pour MacOS (Mamp). La majorité des distributions Linux intègrent ces composants de manière native.

La base de données MySQL TDF2003-1 contient cinq tables dans lesquelles sont entreposées des données liées au Tour De France cycliste édition 2003. Les scripts SQL permettant de générer les structures des tables de cette base et d’y stocker des enregistrements...

1. Objectif de la transformation

Le traitement réalisé par cette seconde transformation est quasi-identique à celui de la transformation précédente. La seule différence est le fait que la source de données en entrée est une base de données Microsoft SQL Server nommée TDF2003. Pour constituer cette base de données il faut disposer d’une version de ce SGBD (une version Express gratuite peut suffire pour réaliser l’opération).

La base de données Microsoft SQL Server TDF2003 contient cinq tables identiques en terme de structure et de contenu à celles de la version MySQL (TDF2003-1). Les scripts SQL permettant de générer les structures des tables de cette base et d’y stocker des enregistrements sont disponibles sur le site des Editions ENI.

La copie de l’intégralité des données de la table Microsoft SQL Server COUREUR dans la table MySQL de nom COUREUR2 stockée dans la seconde base de données MySQL de nom TDF2003-2 est prévue. La structure de la table COUREUR2 doit exister pour cette transformation. Il sera vu ultérieurement la possibilité de créer la structure de la table cible à la volée par un script SQL.

La transformation souhaitée comprend les étapes suivantes :

1. Objectif de la transformation

Dans le cadre de cette transformation, trois étapes sont à prévoir :

Le traitement réalisé par cette transformation est quasi-identique à celui de la transformation n°1.

Sur la nouvelle transformation, il faudra toutefois :

Pour gagner un temps précieux, une sauvegarde de la transformation n°1 pourra être réalisée par la commande de menu Fichier - Enregistrer sous.

Nom de la transformation : PDI_03 - Transfert global table d’une BDD MySQL dans autre BDD MySQL (avec tri).

2. Tri

Position de l’étape dans...

1. Objectif de la transformation

Dans le cadre de cette transformation, quatre étapes sont à prévoir :

Le traitement réalisé par cette transformation est quasi-identique à celui de la transformation n°3.

Sur la nouvelle transformation, il faudra toutefois :

La transformation n°3 pourra servir de base à celle à construire ici.

Nom de la transformation : PDI_04 - Transfert global table d’une BDD MySQL dans autre BDD MySQL (avec sélection et tri).

2. Sélection

Position de l’étape dans la palette : Contrôle de flux - Filtrage lignes.

Le paramétrage...

1. Objectif de la transformation

Cette transformation va montrer qu’il est tout à fait envisageable de mettre en œuvre deux flux s’exécutant de manière parallèle.

Dans la transformation précédente, les coureurs français ont été copiés à partir d’une table MySQL (COUREUR) d’une première base de données (TDF2003-1) vers une table MySQL (COUREUR4) d’une seconde base de données (TDF2003-2).

Dans le traitement présenté ci-après, un second flux sensiblement identique au premier sera ajouté et la transformation comportera les étapes suivantes :

1. Objectif de la transformation

Dans les traitements ETL, il est très fréquent que l’on ait recours à des données stockées dans des fichiers texte (on dit aussi souvent fichiers plats ou ASCII). Plusieurs présentations des données peuvent être rencontrées :

Le contenu de ce type de fichiers est visualisable directement par un éditeur de textes. Ces fichiers sont généralement importés à partir de systèmes de gestion de base de données, d’applications de gestion (comptabilité, facturation, gestion commerciale...). Les composants logiciels...

1. Objectif de la transformation

Au niveau des SGBD, il est fréquent que l’on ait recours à des requêtes SQL pour réaliser des traitements sur les données. De nombreuses étapes de PDI, comme l’extraction des données depuis une table vue précédemment, sont basées sur des séquences de code SQL.

Pentaho Data Integration dispose d’une étape spécifique dont le rôle unique est d’embarquer du code SQL. Cette étape permet de réaliser de multiples types de transactions sur les données d’un SGBD (création de structures des tables, suppression de tables, suppression de données, mise à jour de données, sélections y compris les plus complexes...).

Dans les transformations vues jusqu’à présent, une importante contrainte existait, l’existence de la table cible dans la base de données MySQL TDF2003-2.

La transformation n°7 va exécuter un code SQL de création d’une structure d’une table MySQL de nom TEST.

Pour paramétrer ce type d’étape, il est préférable de tester le code SQL auparavant directement dans le SGBD concerné, sous phpMyAdmin pour MySQL par exemple.

Le code de la requête SQL prévue est :

CREATE TABLE `TEST` (
`NOM_PRENOM` CHAR( 40 ) NOT NULL ,
`AGE`...

1. Objectif de la transformation

Dans cette transformation, quatre étapes sont à prévoir :

Dans cette transformation deux nouvelles étapes sont utilisées : la lecture d’un fichier ASCII de largeur fixe et l’affichage des enregistrements du flux sur l’onglet Trace. L’étape Trace est fréquemment utilisée en phase de test.

Nom de la transformation : PDI_09 - Lecture fichier ASCII de longueur fixe et écriture dans table d’une BDD MySQL.

2. Étape de lecture d’un fichier texte de longueur fixe

Position de l’étape dans la palette : Extraction - Extraction depuis fichier longueur fixe.

Le nombre de paramètres à fournir pour ce type d’étape est là encore important.

Les paramétrages initiaux de ce type d’étape sont :

Certaines propriétés doivent être modifiées :

1. Objectif de la transformation

Dans cette transformation, quatre étapes sont à prévoir :

Cette transformation présente deux nouvelles étapes, l’agrégation de valeurs et l’écriture dans un fichier texte.

La structure de la table EQUIPE_BUDGET de la base de données Microsoft Access de nom TDF2003-1.mdb est :

Nom de la transformation : PDI_10 - Lecture table d’une BDD Access et écriture dans un fichier ASCII d’un cumul de champ.

2. Étape d’accès à une base de données Microsoft Access

Position de l’étape dans la palette : Extraction - Extraction depuis une base Access.

Contrairement à ce qui a été vu pour une base de données MySQL, il n’est pas ici nécessaire de paramétrer une connexion (cf. Navigateur - Connexions). Il suffit dans les propriétés de l’étape...

1. Objectif de la transformation

Dans cette transformation, trois étapes sont à prévoir :

Nom de la transformation : PDI_13 - Intégration feuille d’un classeur Excel dans table d’une BDD MySQL.

2. Étape de lecture d’un classeur Microsoft Excel

Position de l’étape dans la palette : Extraction - Extraction depuis fichier Excel.

Sur l’onglet Fichiers de l’étape on signale le nom du fichier et son chemin :

Sur l’onglet Feuilles, le nom de la feuille (Sheet1 par défaut) à partir de laquelle l’extraction est réalisée, est signalé. Sur l’onglet Contenu, il est précisé si la feuille de calcul comporte une ligne de titre ou pas (cette ligne de titre peut servir ensuite au mappage).

Enfin sur l’onglet Champs, le mappage est réalisé. La récupération des champs depuis la ligne d’en-tête de la feuille de calcul Excel est possible et donne donc :

3. Étapes et liens de la transformation...

1. Objectif de la transformation

Dans cette transformation, trois étapes sont à prévoir :

Nom de la transformation : PDI_14 - Lecture d’un fichier XML et intégration dans une table d’une BDD MySQL.

2. Fichier XML en lecture

Position de l’étape dans la palette : Alimentation - Alimentation fichier XML.

Dans un fichier Etape.xml, les données sont stockées dans une structure en arbre :

<BDD>
    <ETAPE>
        <NUM_ETAPE>0</NUM_ETAPE>
        <VILLE_DEPART>Paris</VILLE_DEPART>
        <VILLE_ARRIVEE>Paris</VILLE_ARRIVEE>
    </ETAPE>
    <ETAPE>
        <NUM_ETAPE>1</NUM_ETAPE>
        <VILLE_DEPART>Saint-Denis</VILLE_DEPART>
        <VILLE_ARRIVEE>Meaux</VILLE_ARRIVEE>
    </ETAPE>

...

    <ETAPE>
        <NUM_ETAPE>20</NUM_ETAPE>
        <VILLE_DEPART>Ville d&apos;Avray</VILLE_DEPART>
        <VILLE_ARRIVEE>Paris</VILLE_ARRIVEE>
    </ETAPE>
</BDD> 

Dans la transformation exposée ci-après, seul le fichier XML (Etape.xml) est utilisé....

1. Objectif de la transformation

Dans cette transformation, quatre étapes sont à prévoir :

Nom de la transformation : PDI_15 - Recherche de données manquantes dans une table MySQL.

2. Recherche

Position de l’étape dans la palette : Recherche - Recherche dans Base de données.

Dans ce type d’étape, il faut renseigner a minima :

1. Objectif de la tâche

Il est fréquent que l’on ait à publier sur un site Web (sur un compte FTP) des fichiers de données (souvent en format texte).

Des étapes de Pentaho Data Integration permettent de publier (disque local -> compte FTP) ou de rapatrier (compte FTP -> disque local) des fichiers.

Par contre ces étapes ne sont pas intégrables dans une transformation PDI. Il faudra passer par une tâche PDI. La construction d’une tâche est assez similaire à celle d’une transformation. Il y a quelques différences tout de même :

Il s’agit ici d’assurer la récupération, depuis un compte FTP (compte installé chez l’hébergeur Free), d’un fichier texte Fichier3.txt pour l’entreposer localement.

Nom de la tâche : PDI_19 - Récupération fichier texte depuis compte FTP.

2. Récupération depuis un serveur FTP (download)

Position de l’étape dans la palette : Transfert de fichiers (FTP) - Récupération...

1. Objectif de la transformation

Il est fréquent que l’on rencontre des enregistrements (totalement identiques) en multiples exemplaires dans un flux de données. Il existe une étape spécifique pour supprimer ces enregistrements intempestifs du flux (on parle de dédoublonnage).

La transformation présentée ci-après a au contraire pour objectif de créer des doublons. Elle doit comporter les étapes suivantes :

Nom de la transformation : PDI_21 - Clonage d’enregistrements d’une table d’une BDD MySQL.

2. Clonage

Position de l’étape dans la palette : Divers - Clonage ligne.

Cette étape se contente de réaliser des doublons (clones) pour chacun des enregistrements présents dans le flux. Un nombre de clones à 2 par exemple signifie que l’on souhaite dans le flux en sortie l’enregistrement original...

1. Objectif de la transformation

La transformation présentée ci-après a pour objectif de supprimer les doublons dans un flux. Les étapes seront :

Nom de la transformation : PDI_22 - Dédoublonnage sur une table d’une BDD MySQL.

2. Dédoublonnage

Position de l’étape dans la palette : Transformation - Dédoublonnage.

Il n’est pas toujours obligatoire de signaler les champs de comparaison dans le paramétrage de cette étape.

Dans notre cas, on cherche à éliminer de véritables doublons (des enregistrements clonés lors de la transformation précédente) avec des valeurs identiques pour tous les champs. La grille est donc laissée vide.

Il faut noter aussi que cette étape doit être impérativement précédée d’un...

1. Objectif de la transformation

La transformation présentée ci-après va montrer comment générer des champs calculés sous PDI.

Les étapes de la transformation seront :

Nom de la transformation : PDI_23 - Champ calculé sur table d’une BDD MySQL.

2. Script SQL de création de la table EQUIPE_BUDGET23

Le code SQL à prévoir est :

CREATE TABLE IF NOT EXISTS `equipe_budget23` (
  `CODE_EQUIPE` char(3) NOT NULL DEFAULT '',
  `NOM_EQUIPE` char(20) DEFAULT NULL,
  `BUDGET_EQUIPE` double NOT NULL,
  `NB_COUREURS_EQUIPE` int(11) NOT NULL,
  `BUDGET_COUREUR_EQUIPE` double,
  PRIMARY KEY (`CODE_EQUIPE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

3. Champ calculé

Position de l’étape dans la palette : Transformation - Calcul.

Le paramétrage de cette étape requiert le renseignement des colonnes suivantes :

1. Objectif de la transformation

La transformation présentée ci-après montre une alternative pour le filtrage de lignes.

Les étapes de la transformation seront :

Avec Filtrages lignes (cf. "Transformation PDI n°4"), il aurait été facile de répartir les coureurs comme suit :

Ici la problématique est un peu différente. On veut pouvoir à la limite ventiler les coureurs en plus de deux sous-ensembles, l’étape Branchement conditionnel va être utilisée.

Nom de la transformation : PDI_24 - Eclatement d’une table d’une BDD MySQL en deux...