tSQLTemplateMerge - 6.1

Composants Talend Guide de référence

EnrichVersion
6.1
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Création et développement
Gouvernance de données
Qualité et préparation de données
EnrichPlatform
Studio Talend

Propriétés du tSQLTemplateMerge

Famille de composant

ELT/SQLTemplate

 

Fonction

Le composant tSQLTemplateMerge crée une instruction SQL MERGE sur les données dans une table de la base de données.

Objectif

Ce composant est utilisé pour effectuer des opérations de MERGE sur les données (insertion, mise à jour ou suppression) directement dans une table du SGBD. Pour cela, il crée et exécute une instruction MERGE.

Basic settings

Database Type

Sélectionnez dans le menu déroulant le type de base de données sur lequel vous voulez travailler.

 

Component list

Sélectionnez le composant ELT de connexion le plus pertinent dans la liste si vous prévoyez d'ajouter plus d'une connexion à votre Job en cours.

 

Source table name

Nom de la table contenant les données sur lesquelles vous souhaitez effectuer les opérations d'insertion ou de mise à jour dans la table cible.

 

Target table name

Nom de la table dans laquelle vous souhaitez effectuer les opérations d'insertion ou de mise à jour.

 

Schema et Edit schema

Un schéma est une description de lignes, il définit le nombre de champs qui sont traités et passés au composant suivant. Le schéma est soit local (built-in) soit distant dans le Repository.

Depuis la version 5.6, les modes Built-In et Repository sont disponibles dans toutes les solutions de Talend.

Cliquez sur Edit schema pour modifier le schéma. Si le schéma est en mode Repository, trois options sont disponibles :

  • View schema : sélectionnez cette option afin de voir le schéma.

  • Change to Built-In property : sélectionnez cette option pour passer le schéma en mode Built-In et effectuer des modifications locales.

  • Update repository connection : sélectionnez cette option afin de modifier le schéma stocké dans le référentiel et décider de propager ou non les modifications à tous les Jobs. Si vous souhaitez propager les modifications uniquement au Job courant, sélectionnez No et sélectionnez à nouveau la métadonnée du schéma dans la fenêtre [Repository Content].

 

 

Built-in : Le schéma sera créé et conservé pour ce composant seulement. Voir également le Guide utilisateur du Studio Talend.

 

 

Repository : Le schéma existe déjà et est stocké dans le Repository. Ainsi, il peut être réutilisé dans divers projets et Jobs. Voir également le Guide utilisateur du Studio Talend.

 

Merge ON

Spécifiez les colonnes source et cible que vous souhaitez utiliser comme clés primaires.

 

Use UPDATE (WHEN MATCHED)

Cochez cette case pour mettre à jour les enregistrements existants. Une fois cette case cochée, la table UPDATE Columns apparaît, vous permettant de définir les colonnes dans lesquelles les enregistrements seront mis à jour.

 

Specify additional output columns

Cochez cette case pour mettre à jour les enregistrements dans des colonnes autres que celles listées dans la table UPDATE Columns. Une fois cette case cochée, la table Additional UPDATE Columns apparaît, vous permettant de définir des colonnes supplémentaires.

 

Specify UPDATE WHERE clause

Cochez cette case et saisissez une clause de type WHERE dans le champ WHERE clause afin de filtrer les données lors de l'opération de mise à jour.

Note

Il est probable que cette option ne fonctionne pas avec certaines versions des bases de données, notamment Oracle 9i.

 

Use INSERT (WHEN MATCHED)

Cochez cette case pour insérer de nouveaux enregistrements. Une fois cette case cochée, la table INSERT Columns apparaît, vous permettant de définir des colonnes à impliquer dans l'opération d'insertion.

 

Specify additional output columns

Cochez cette case pour insérer des enregistrements dans les colonnes des colonnes autres que celles listées dans la table INSERT Columns. Une fois cette case cochée, la table Additional INSERT Columns apparaît, vous permettant de définir des colonnes supplémentaires.

 

Specify INSERT WHERE clause

Cochez cette case et saisissez une clause de type WHERE dans le champ WHERE clause fin de filtrer les données lors de l'opération d'insertion.

Note

Il est probable que cette option ne fonctionne pas avec certaines versions des bases de données, notamment Oracle 9i.

Advanced settings

tStatCatcher Statistics

Cochez cette case pour collecter les données de log aussi bien au niveau du Job qu'au niveau du composant.

SQL Template

SQL Template List

Cliquez sur le bouton Add pour ajouter un ou plusieurs modèle SQL du système à la liste SQLTemplateList. Cliquez dans la cellule de la colonne SQLTemplate List que vous venez d'ajouter et sélectionnez le modèle SQL du système adéquat dans la liste de modèles fournis par Talend.

Note : Vous pouvez créer vos propres modèles SQL et les ajouter à la liste SQLTemplate List.

Pour créer un nouveau modèle SQL :

1- Sélectionnez un modèle déjà existant dans la liste SQLTemplate list et cliquez dans son code dans le champ de code. Une boîte de dialogue s'ouvrira vous demandant de créer un nouveau modèle.

2- Cliquez sur Yes pour ouvrir la boîte de dialogue [New SQLTemplate].

3- Renseignez les informations de votre modèle SQL et cliquez sur Finish pour fermer la boîte de dialogue. Un éditeur de modèle SQL apparaît dans lequel vous pouvez saisir votre code.

4- Cliquez sur le bouton Add pour ajouter le nouveau modèle dans la liste SQLTemplate list.

Pour plus d'informations, consultez le Guide utilisateur du Studio Talend.

Global Variables

NB_LINE : nombre de lignes lues par un composant d'entrée ou passées à un composant de sortie. Cette variable est une variable After et retourne un entier.

NB_LINE_MERGED : nombre de lignes fusionnées. Cette variable est une variable After et retourne un nombre entier.

QUERY : requête SQL traitée. Cette variable est une variable Flow et retourne une chaîne de caractères.

ERROR_MESSAGE : message d'erreur généré par le composant lorsqu'une erreur survient. Cette variable est une variable After et retourne une chaîne de caractères. Cette variable fonctionne uniquement si la case Die on error est décochée, si le composant a cette option.

Une variable Flow fonctionne durant l'exécution d'un composant. Une variable After fonctionne après l'exécution d'un composant.

Pour renseigner un champ ou une expression à l'aide d'une variable, appuyez sur les touches Ctrl+Espace pour accéder à la liste des variables. A partir de cette liste, vous pouvez choisir la variable que vous souhaitez utiliser.

Pour plus d'informations concernant les variables, consultez le Guide utilisateur du Studio Talend.

Utilisation

Ce composant est un composant intermédiaire. Vous pouvez l'utiliser avec d'autres composants de base de données, notamment les composants de connexion et de commit.

Log4j

Si vous utilisez une solution Talend soumise à souscription, l'activité de ce composant peut être journalisée avec la fonctionnalité log4j. Pour plus d'informations sur cette fonctionnalité, consultez le Guide utilisateur du Studio Talend.

Pour plus d'informations sur les niveaux de logs du log4j, consultez la documentation d'Apache : http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/Level.html (en anglais).

Scénario : Effectuer des opérations de MERGE directement dans le SGBD

Ce scénario décrit un Job simple permettant d'ouvrir une connexion à une base de données MySQL, d'effectuer des opérations d'insertion ou de mise à jour sur les données d'une table source vers une table cible en fonction de l'identifiant de l'utilisateur et d'afficher le contenu de la table cible avant et après les opérations de MERGE. Une clause de type WHERE est utilisée pour filtrer les données lors de ces opérations.

  • A partir de la Palette, déposez les composants suivants dans l'espace de modélisation graphique : un tMysqlConnection, un tSQLTemplateMerge, deux tMysqlInput et deux tLogRow.

  • Connectez le composant tMysqlConnection au premier tMysqlInput à l'aide d'un lien Trigger > OnSubjobOk.

  • Connectez le premier tMysqlInput au premier tLogRow à l'aide d'un lien Row > Main. Cette ligne affichera le contenu initial de la table cible dans la console de la vue Run.

  • Connectez le premier tMysqlInput au composant tSQLTemplateMerge, et le tSQLTemplateMerge au second composant tMysqlInput à l'aide de connexions Trigger > OnSubjobOk.

  • Connectez le second tMysqlInput au second tLogRow à l'aide d'un lien Row > Main. Cette ligne affichera le résultat des opérations de MERGE dans la console de la vue Run.

  • Double-cliquez sur le composant tMysqlConnection pour afficher l'onglet Basic settings de sa vue Component.

  • Paramétrez manuellement les détails de la connexion à la base de données ou sélectionnez Repository dans la liste Property Type et sélectionnez votre connexion à une base de données si celle-ci a déjà été définie et stockée dans la zone Metadata de la vue Repository.

    Pour plus d'informations concernant les Métadonnées, consultez le Guide utilisateur du Studio Talend.

  • Double-cliquez sur le premier composant tMysqlInput pour afficher l'onglet Basic settings de sa vue Component.

  • Cochez la case Use an existing connection. Si vous souhaitez utiliser plus d'un composant de connexion dans votre Job, sélectionnez le composant que vous souhaitez utiliser à partir de la liste Component List.

  • Cliquez sur le bouton [...] à côté de Edit schema et définissez la structure des données de la table cible, ou sélectionnez Repository à partir de la liste Schema puis sélectionnez la table cible si le schéma a déjà été défini et stocké dans la zone Metadata de la vue Repository.

    Pour ce scénario, choisissez Built-in.

  • Définissez les colonnes comme indiqué ci-dessus, puis cliquez sur OK pour propager la structure du schéma au composant de sortie et fermer la boîte de dialogue.

  • Renseignez le champ Table Name avec le nom de la table cible, soit customer_info_merge pour ce scénario.

  • Cliquez sur le bouton Guess Query, ou saisissez "SELECT * FROM customer_info_merge" dans la zone Query afin de récupérer toutes les colonnes des tables.

  • Définissez les propriétés du second composant tMysqlInput en utilisant exactement les mêmes paramètres que dans le premier composant tMysqlInput.

  • Dans l'onglet Basic settings de la vue Component de chaque composant tLogRow, sélectionnez l'option Table dans la zone Mode afin d'afficher le résultat sous forme de tableau dans la console de la vue Run.

  • Double-cliquez sur le composant tSQLTemplateMerge pour afficher l'onglet Basic settings de sa vue Component.

  • Saisissez les noms de la table source et de la table cible dans les champs correspondants.

    Pour ce scénario, la table source, new_customer_info, contient huit enregistrements ; la table cible, customer_info_merge, contient cinq enregistrements et les deux tables ont la même structure.

Note

Il est possible que la table source et la table cible aient des schémas différents. Dans ce cas, cependant, assurez-vous que la colonne source et la colonne cible spécifiées dans chaque ligne des tables Merge ON, UPDATE Columns et INSERT Columns aient un type de données identique et que la longueur de la colonne cible permette l'insertion de données à partir de la colonne source correspondante.

  • Définissez le schéma source manuellement, ou sélectionnez Repository à partir de la liste Schema et sélectionnez la table souhaitée si le schéma a déjà été défini et stocké dans la zone Metadata de la vue Repository.

    Pour ce scénario, des schémas en mode Built-in sont utilisés.

  • Définissez les colonnes comme indiqué ci-dessus puis cliquez sur OK pour fermer la boîte de dialogue. Faites de même pour le schéma cible.

  • Cliquez sur le bouton [+] sous la table Merge ON pour ajouter une ligne et sélectionnez la colonne ID comme clé primaire.

  • Cochez la case Use UPDATE (WHEN MATCHED) pour mettre à jour les données existantes lors des opérations de MERGE, définissez les colonnes à mettre à jour en cliquant sur le bouton [+] et sélectionnez les colonnes souhaitées.

    Le but de ce scénario est de mettre à jour toutes les colonnes selon l'identifiant (ID) des utilisateurs. Pour cela, sélectionnez toutes les colonnes sauf la colonne ID.

Avertissement

Les colonnes définies comme clés primaires ne peuvent pas et ne doivent pas être mises à jour.

  • Cochez la case Specify UPDATE WHERE clause et saisissez customer_info_merge.ID >= 4 entre guillemets doubles, dans le champ WHERE clause afin que seuls les enregistrements existants ayant un ID égal ou supérieur à 4 soient mis à jour.

  • Cochez la case Use INSERT puis définissez dans la table INSERT Columns les colonnes où les données seront prises et insérées.

    Pour ce scénario, tous les enregistrements qui n'existent pas dans la table cible sont insérés.

  • Sélectionnez l'onglet SQL Template pour afficher et ajouter les modèles SQL à utiliser.

    Par défaut, le composant SQLTemplateMerge utilise deux modèles SQL système : MergeUpdate et MergeInsert.

Note

Dans l'onglet SQL Template, vous pouvez ajouter des modèles SQL système ou créer vos propres modèles et les utiliser dans votre Job pour effectuer des opérations codées. Pour plus d'informations, consultez Propriétés du tSQLTemplateFilterColumns.

  • Cliquez sur le bouton Add pour ajouter une ligne puis sélectionnez Commit dans la liste des modèles afin de commiter les résultats des opérations de MERGE dans votre base de données.

    Autrement, vous pouvez connecter le composant tSQLTemplateMerge à un tSQLTemplateCommit ou un tMysqlCommit à l'aide d'un lien Trigger > OnSubjobOk afin de commiter les résultats des opérations dans votre base de données.

  • Sauvegardez votre Job et appuyez sur F6 pour l'exécuter.

    La console de la vue Run affiche à la fois le contenu d'origine de la table cible ainsi que les résultats des opérations de MERGE. Dans la table cible, les enregistrements No. 4 et No. 5 contiennent les informations mises à jour, tandis que les enregistrements No .6, No. 7 et No. 8 contiennent les informations insérées dans la table.