À la découverte de Pentaho, un programme ETL

Pentaho Data Integration est un outil ETL (Extract, Transform and Load) utilisé pour l'intégration de données. Imaginons que vous ayez un fichier Excel et que vous vouliez en extraire des enregistrements (uniquement ceux qui correspondent à une règle précise), puis appliquer une transformation (comme ajouter de nouvelles colonnes basées sur les données existantes) et les charger dans une base PostgreSQL.

Pentaho (également appelé PDI ou Kettle) va vous permettre de dessiner un flux de données (charger des fichiers, faire des merges, appliquer une transformation, exécuter du code (Shell ou JavaScript), ... et stocker le résultat quelque part (une base de données ou un fichier) ou même envoyer une information par email en cas d'échec).

Découvrons rapidement Pentaho et, pour un premier exemple, chargeons un fichier Excel dans une base PostgreSQL.

Voici exactement ce que nous allons construire : un flux qui lit un fichier Excel, puis charge ses lignes dans une table PostgreSQL.

http://localhost:8080

Pourquoi ça fonctionne​

• Spoon, l'interface graphique de Pentaho, est un canevas en glisser-déposer : aucun code à écrire, juste des étapes (entrée Excel, sortie base de données, ...) reliées par des flèches.
• Chaque étape peut être testée isolément — prévisualiser les lignes Excel, tester la connexion à la base — avant même de lancer le flux complet.
• Pentaho génère pour vous le SQL CREATE TABLE à partir du flux lui-même, donc le schéma de la base et le flux ne divergent jamais.

Installation sous Linux​

Pentaho peut être installé sous Windows comme sous Linux puisqu'il s'agit d'une application Java.

Mon expérience personnelle : même avec un ordinateur puissant et beaucoup de RAM, les interfaces Java sous Windows sont tout simplement impossibles tant elles manquent de réactivité. En plus, avant même de pouvoir lancer l'application, il faut se donner la peine d'installer Java et ... beurk.

Passons à la suite et installons-le sous Linux ; c'est vraiment facile à faire.

https://pentaho.com/pentaho-developer-edition/

Ajouter Pentaho au PATH​

Pour finaliser l'installation, vous devez aussi mettre à jour la variable Linux PATH.

Lancez code ~/.bashrc (ou code ~/.zshrc si vous utilisez Oh-my-ZSH) ; cherchez dans le fichier si vous avez déjà une ligne avec PATH=. Si oui, mettez-la à jour et ajoutez-y $HOME/tools/pentaho/data-integration. Sinon, allez à la fin du fichier et ajoutez cette ligne :

PATH="$HOME/tools/pentaho/data-integration:$PATH"?

Sauvegardez et quittez.

Pour appliquer aussi le changement à votre console actuelle, lancez export PATH="$HOME/tools/pentaho/data-integration:$PATH" (ou ouvrez une nouvelle console).

À partir de maintenant, vous pourrez lancer Pentaho depuis n'importe quel dossier.

Préparons notre environnement​

Nous allons créer un nouveau projet ; créez un dossier temporaire et entrez-y :

$ mkdir -p /tmp/pentaho && cd $_

Nous allons créer un fichier Excel bidon puis le charger dans une base PostgreSQL. La table cible s'appellera people et nous la créerons manuellement (pas automatiquement).

D'abord, il nous faut un fichier Excel à charger​

Pour ce tutoriel, nous allons charger un fichier Excel dans PostgreSQL, il nous faut donc un fichier Excel.

Copiez le tableau ci-dessous dans Excel et sauvegardez le nouveau fichier sous le nom people.xlsx (sinon, cliquez ici pour le télécharger). Sauvegardez le fichier dans votre dossier Linux /tmp/pentaho.

Ensuite, il nous faut une base PostgreSQL​

Pour cela, nous allons utiliser Docker. Créez un fichier appelé compose.yaml dans votre dossier /tmp/pentaho avec ce contenu :

name: pentaho

services:
  postgres:
    image: postgres:9-alpine
    hostname: postgresql
    environment:
      - DATABASE_HOST=postgresql
      - POSTGRES_DB=people
      - POSTGRES_HOST_AUTH_METHOD=trust
      - POSTGRES_PASSWORD=admin
      - POSTGRES_USER=admin
    ports:
      - "5432:5432"?
    expose:
      - "5432"
    networks:
      - pentaho

  pgadmin:
    image: dpage/pgadmin4:6.12
    hostname: pgadmin
    ports:
      - "8080:80"?
    depends_on:?
      - postgres
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@yopmail.com
      - PGADMIN_DEFAULT_PASSWORD=admin
    networks:
      - pentaho

networks:
  pentaho:
    external: false?

Et, pour nous faciliter la vie, créons un makefile​

.PHONY: start?
start:
	SWT_GTK3=1 SKIP_WEBKITGTK_CHECK=1 KETTLE_HOME=${PWD} spoon.sh?

.PHONY: up?
up:
	docker compose up

Lançons notre container Docker​

Lancez docker compose up --detach pour démarrer le container PostgreSQL et celui de pgAdmin.

Comme vous l'avez vu, nous avons déjà mis en place une interface pgAdmin ; nous pouvons l'ouvrir en allant sur http://localhost:8080. pgAdmin n'est pas votre seule option ici ; voir Utiliser Adminer, pgadmin ou phpmyadmin pour accéder à votre container de base de données Docker.

http://localhost:8080

Utilisez admin@yopmail.com et admin comme identifiants.

Une fois dans l'interface de pgAdmin, nous devons créer un nouveau serveur en cliquant sur le bouton Add new server.

Donnez un nom au serveur puis, dans l'onglet Connection, remplissez comme ci-dessous :

http://localhost:8080

Comme vous pouvez le voir sur l'image ci-dessous, lors de la création de notre container PostgreSQL, une nouvelle base appelée people a été créée. Cette base a un schéma public et, pour l'instant, aucune table.

http://localhost:8080

Les pièces du puzzle sont en place, créons notre flux​

Maintenant, lancez spoon.sh pour démarrer l'interface graphique de Pentaho. Cette interface s'appelle Spoon.

Nous devons créer un nouveau fichier de transformation​

Cliquez sur le menu File, puis New et sélectionnez Transformation.

Charger depuis Excel​

Dans notre scénario, nous voulons charger un fichier Excel : cliquez donc sur la catégorie Input et descendez jusqu'à voir Microsoft Excel input, puis déposez-le sur le canevas principal.

Double-cliquez sur l'étape que vous venez d'ajouter pour la configurer :

• Donnez-lui un nom clair (comme Load people from Excel),
• Cliquez sur le bouton Browse... pour retrouver votre fichier (par exemple /tmp/pentaho/people.xlsx) puis cliquez sur le bouton Add pour ajouter ce fichier à la liste des Selected files
• Dans l'onglet Sheets, cliquez sur le bouton Get sheetname(s)..., récupérez le nom de la feuille et ajoutez-le.
• Dans l'onglet Content, il n'y a rien à changer (puisque notre fichier Excel a une ligne d'en-tête puis les données sans ligne vide entre elles)
• Dans l'onglet Fields, cliquez sur Get fields from header row... pour charger les noms puis ajustez quelques propriétés comme illustré ci-dessous :
• Cliquez sur le bouton Preview rows si vous voulez vérifier que tout est OK

C'est fait ; cliquez sur le bouton OK.

Définir notre connexion à la base de données​

Maintenant, cliquez sur l'onglet View comme illustré ci-dessous et double-cliquez sur l'élément Database connections.

Souvenez-vous de notre fichier compose.yaml et remplissez l'écran comme ceci :

Cliquez sur le bouton Test pour vous assurer que la connexion est correctement configurée.

Cliquez sur le bouton Ok pour sauvegarder votre connexion.

Préparer notre sortie vers une table​

Cliquez sur l'onglet Design puis sur la catégorie Output et trouvez Table output. Faites un glisser-déposer vers le canevas.

Cliquez sur la boîte Load people from Excel et attendez que les petites icônes ci-dessous s'affichent :

Cliquez sur le bouton avec le connecteur de sortie (celui avec la flèche vers la droite) et déposez la ligne sur la boîte Table output :

Cela indique à Pentaho que, une fois l'étape Load people from Excel terminée avec succès, il faut continuer avec Table output.

Maintenant, double-cliquez sur Table output et faisons un peu de configuration :

• Donnez-lui un nom d'étape clair (comme Load to PostgreSQL),
• Le nom de la Connection à utiliser doit être People (celle que nous venons de créer),
• Le Target schema doit être public (celui de notre base People),
• La Target table est people.

Maintenant, cliquez sur le bouton SQL : Pentaho affiche une popup (pensez à redimensionner la fenêtre) avec une instruction CREATE TABLE :

Puisque notre table n'existait pas encore, cliquez sur le bouton Execute :

Vérifions grâce à pgAdmin :

http://localhost:8080

Parfait. Nous pouvons maintenant fermer les deux fenêtres popup et revenir à la configuration de notre table. Cliquez sur le bouton OK puisque cette étape est terminée.

Il est temps de sauvegarder notre transformation​

Cliquez sur le menu File puis Save.

Sauvegardez le fichier dans /tmp/pentaho sous le nom load_people_from_excel.ktr.

Lançons-la​

Repérez l'icône Run (la même que celle déjà montrée en haut de cet article). Cliquez dessus. Vous obtiendrez une nouvelle fenêtre avec un bouton Run en bas à droite ; cliquez dessus.

Si tout était correctement configuré et lancé, vous obtiendrez le même écran « Successfully executed » montré plus haut.

De retour dans pgAdmin, affichez la liste des enregistrements de la table people pour vérifier que le fichier Excel a bien été chargé — le même résultat que celui déjà montré en haut de cet article.

Télécharger le fichier de transformation​

Si vous avez des soucis pour créer votre fichier de transformation, voici celui que j'ai utilisé pour cet article : load_people_from_excel.ktr.

Si vous voulez le fichier Excel utilisé dans cet article, voici à nouveau le lien.

Conclusion​

Au-delà de Docker, PostgreSQL et pgAdmin, nous avons vu comment charger un fichier Excel sous Linux et stocker les enregistrements dans une table PostgreSQL.

Les possibilités offertes par Pentaho sont tout simplement monstrueuses.