Dans cet article vous verrez comment « parser » des données dans Talend grâce à l’objet java « PreparedStatement », plus simplement des requêtes munies de paramètres seront construites dynamiquement et exécutées une à une.
Problématique
Sur de nombreux forum, une question revient souvent dans le monde java « Comment passer un nombre dynamique de paramètres dans un objet PreparedStatement ? ».
Pour rappel, la fonctionnalité du « PreparedStatement » construit une requête lacunaire au niveau de la base de données, lacunaire dans le sens où il nous reste ensuite à lui passer des paramètres ainsi que les positions de ceux-ci, les performances s’en trouvent améliorées car la base de données va mettre la requête construite dans son cache (pré-compilation) et réutiliser celle-ci à chaque appel.
Voici un exemple complet en java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestJDBC
{
public static void main(String[] args) throws ClassNotFoundException {
Connection connect = null;
PreparedStatement ps = null;
ResultSet rs = null;
try
{
Class.forName("com.mysql.jdbc.Driver");
connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","tempo");
ps = connect.prepareStatement("select * from tiers where id = ?");
for (int i=0;i<11;i++)
{
ps.setInt(1, i);
rs = ps.executeQuery();
while (rs.next())
System.out.println(rs.getString("nom") + " " + rs.getString("prenom"));
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally
{
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
On remarque que le paramètre dynamique est symbolisé par un point d’interrogation.
Démo avec Talend
Avant toute chose, il est tout à fait possible d’utiliser la syntaxe « IN ( ?) » avec un « PreparedStatement » et la méthode « setObject() », malheureusement suite à mes propres tests et sauf erreur de ma part, il est impossible de construire un job pour récupérer le contenu des résultats de type objet dans un flux Talend.
Dans l’image ci-dessus, le résultat attendu est de 35 lignes d’adresses pour 10 tiers listés, le job n’affiche que les 5 lignes d’adresse du premier tiers, ceci étant surement dû au composant tMysqlRow qui est adapté pour retourner qu’un seul paquet de résultats à la fois.
J’ajouterai que je trouve dommage que le tMysqlInput ne soit pas pourvu des options de parsing dans ses propriétés avancées, peut-être que cela aurait résolu ce problème.
Paramètres du tRowMysql pour les tiers
1ère étape : Générer les données
Dans ce scénario, il s’agit de générer des données de clients ainsi que leurs adresses avec l’aide du composant tRowGenerator (relation 1 à plusieurs), j’utiliserai pour se faire, une base de données MySQL pour accueillir les deux tables.
Paramètres du tRowGenerator pour les tiers
Remarque : J’ai paramétré volontairement le nombre de lignes à générer sur 5000 pour comparer les performances de la seconde méthode.
Paramètres du tRowGenerator pour les adresses
Utilisez la fonction random pour générer une à plusieurs adresses de façon aléatoire.
2ème étape: Job de test avec tParseRecordSet
La première étape est de sélectionner toutes les lignes de tiers puis de remplacer le point d’interrogation par chacun des ID de ces tiers, la requête construite sera mise dans le pool de la base de données puis exécutée de façon multiple afin d’améliorer les performances. Idéale sur le papier, cette méthode demande quand même de nombreux allers-retours vers la base de données et autant de transits sur le réseau.
Paramètrage du tMysqlInput
Paramètres de base du tMysqlRow
Paramètres avancés du tMysqlRow
Il faut indiquer la position (index) du paramètre dans la requête ainsi que son type.
Paramètres avancés du tParseRecordset
Le champ « adresse » sera récupéré des requêtes lancées à partir des id tiers, les données originales de la première requête passent aussi par ce composant.
Une fois le job lancé, voici le résultat dans la base de données :
Remarque : Pour activer les logs de requête sous MySQL, il faut paramétrer le fichier my.ini situé dans le répertoire /bin du serveur. (XAMPP sous Windows pour ma part)
3ème étape: Job de test avec « dénormalisation » des paramètres
C’est dans ce deuxième job que nous allons dénormaliser les lignes d’entrées pour les inclure dans un paramètre d’entrée « IN », au lieu d’utiliser un point d’interrogation nous allons concaténer une chaine de caractère, notez qu’il s’agit plutôt de l’utilisation de l’objet java « Statement » .
Paramétrage du tMysqlInput
Idem que dans le premier job de test, on sélectionne les champs de la table tiers.
Paramètrage du tDenormalize
Ce composant va en fait créer une liste de valeurs scindées par un séparateur défini par l’utilisateur, dans notre cas, ce sera la virgule.
Paramètrage du tMysqlInput
Cette étape est nécessaire pour pour la concaténation de la requête
Résultats du log de requêtes en base de données
Notez qu’une seule requête a été lancée, toutes les valeurs étant passées en même temps dans la clause « IN »
Benchmark
Quelle solution est la plus rapide ? voici un petit test avec 5000 valeurs
Avec un quart de secondes au lieu de 34.07secondes pour le PreparedStatement, il est évident qu’avec des milliers d’allers-retours et même si la mise en cache de la requête est censée améliorer les performances, le simple « statement » avec une pléthore de paramètres bat le « PreparedStatement » à plate couture.
Une question se pose alors : « Combien de paramètres puis-je passer dans une clause IN ? »
Je n’ai pas la réponse exacte pour MySQL mais sachez que 100.000 paramètres passent sans problème (cfr copie d’écran), la requête prend même le luxe de s’exécuter en 1.14s pour retourner les 350.000 valeurs dans le fichier.
Avantages et inconvénients
|
Preparestatement |
|
|
+ |
– |
| Pré-compile les requêtes répétitives | Trop lourd pour un one shot |
| Evite les injections SQL | |
| Plus de lisibilité | |
| Talend permet de récupérer les autres champs | |
|
Statement (concaténation de paramètres) |
|
|
+ |
– |
| Idéal pour un one shot | Plus difficile à construire |
| Moins d’allers-retours avec la DB | Lisibilité |
| Pas propre en terme de bonne pratique | |
