Scenario: Reading data from different MySQL databases using dynamically loaded connection parameters - 6.1

Talend Components Reference Guide

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
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

The Job in this scenario is made of two subjobs. The first subjob aims at dynamically loading the context parameters from two text files, and the second subjob uses the loaded context parameters to connect to two different databases and to display the content of an existing database table of each of them. With the context settings in the Job, we can decide which database to connect to and choose whether to display the set context parameters on the console dynamically at runtime.

Dropping and linking the components

  1. Drop a tFileInputDelimited component and a tContextLoad component from the Palette onto the design workspace, and link them using a Row > Main connection to form the first subjob.

  2. Drop a tMysqlInput component and a tLogRow component onto the design workspace, and link them using a Row > Main connection to form the second subjob.

  3. Link the two subjobs using a Trigger > On Subjob Ok connection.

Preparing the contexts and context variables

  1. Create two delimited files corresponding to the two contexts in this scenario, namely two databases we will access, and name them test_connection.txt and prod_connection.txt, which contain the database connection details for testing and actual production purposes respectively. Each file is made of two columns, containing the parameter names and the corresponding values respectively. Below is an example:

    host;localhost
    port;3306
    database;test
    username;root
    password;talend
  2. Select the Contexts view of the Job, and click the [+] button at the bottom of the view to add seven rows in the table to define the following parameters:

    • host, String type

    • port, String type

    • database, String type

    • username, String type

    • password, Password type

    • filename, File type

    • printOperations, Boolean type

    Note that the host, port, database, username and password parameters correspond to the parameter names in the delimited files and are used to set up the desired database connection, the filename parameter is used to define the delimited file to read at Job execution, the printOperations parameter is used to decide whether to print the context parameters set by the tContextLoad component on the console.

  3. Click the Contexts tab and click the [+] button at the upper right corner of the panel to open the [Configure Contexts] dialog box.

  4. Select the default context, click the Edit button and rename the context to Test.

  5. Click New to add a new context named Production. Then click OK to close the dialog box.

  6. Back in the Contexts tab view, define the value of the filename variable under each context by clicking in the respective Value field and browse to the corresponding delimited file.

  7. Select the Prompt check box next to the Value field of the filename variable for both contexts to show the Prompt fields and enter the prompt message to be displayed at the execution time.

  8. For the printOperations variable, click in the Value field under the Production context and select false from the list; click in the Value field under the Test context and select true from the list. Then select the Prompt check box under both contexts and enter the prompt message to be displayed at the execution time.

Configuring the components

  1. In the tFileInputDelimited component Basic settings panel, fill the File name/Stream field with the relevant context variable we just defined: context.filename.

  2. Define the file schema manually (Built-in). It contains two columns defined as: key and value.

  3. Accept the defined schema to be propagated to the next component (tContextLoad).

  4. In the Dynamic settings view of the tContextLoad component, click the [+] button to add a row in the table, and fill the Code field with context.printOperations to use context variable printOperations we just defined. Note that the Print operations check box in the Basic settings view now becomes highlighted and unusable.

  5. Then double-click to open the tMysqlInput component Basic settings view.

  6. Fill the Host, Port, Database, Username, and Password fields with the relevant variables stored in the delimited files and defined in the Contexts tab view: context.host, context.port, context.database, context.username, and context.password respectively in this example, and fill the Table Name field with the actual database table name to read data from, customers for both databases in this example.

  7. Then fill in the Schema information. If you stored the schema in the Repository Metadata, then you can retrieve it by selecting Repository and the relevant entry in the list.

    In this example, the schema of both database tables is made of four columns: id (INT, 2 characters long), firstName (VARCHAR, 15 characters long), lastName (VARCHAR, 15 characters long), and city (VARCHAR, 15 characters long).

  8. In the Query field, type in the SQL query to be executed on the DB table specified. In this example, simply click Guess Query to retrieve all the columns of the table, which will be displayed on the Run tab, through the tLogRow component.

  9. In the Basic settings view of the tLogRow component, select the Table option to display data records in the form of a table.

Executing the Job

  1. Press Ctrl+S to save the Job, and press F6 to run the Job using the default context, which is Test in this use case.

    A dialog box appears to prompt you to specify the delimited file to read and decide whether to display the set context parameters on the console.

    You can specify a file other than the default one if needed, and clear the Show loaded variables check box if you do not want to see the set context variables on the console. To run the Job using the default settings, click OK.

    The context parameters and content of the database table in the Test context are all displayed on the Run console.

  2. Now select the Production context and press F6 to launch the Job again. When the prompt dialog box appears, simply click OK to run the Job using the default settings.

    The content of the database table in the Production context is displayed on the Run console. Because the printOperations variable is set to false, the set context parameters are not displayed on the console this time.