Scenario 2: Using context parameters when reading a table from a MySQL database - 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

In this scenario, we will read a table from a MySQL database, using a context parameter to refer to the table name.

Dragging and dropping components and linking them together

  1. Drop tMysqlInput and tLogRow from the Palette onto the workspace.

  2. Link tMysqlInput to tLogRow using a Row > Main connection.

Configuring the components

  1. Double-click tMysqlInput to open its Basic Settings view in the Component tab.

  2. From the Property Type list, select Repository if you have already stored the connection to database in the Metadata node of the Repository tree view. The property fields that follow are automatically filled in.

    For more information about how to store a database connection, see Talend Studio User Guide.

    If you have not defined the database connection in the Repository, fill in the details manually after selecting Built-in from the Property Type list.

  3. Set the Schema as Built-In and click Edit schema to define the desired schema.

    The schema editor opens:

  4. Click the [+] button to add the rows that you will use to define the schema, seven columns in this example: id, first_name, last_name, city, state, date_of_birth and salary.

    Under Column, click the fields to enter the corresponding column names.

    Click the fields under Type to define the type of data.

    Click OK to close the schema editor.

  5. Put the cursor in the Table Name field and press F5 for context parameter setting.

    For more information about context settings, see Talend Studio User Guide.

  6. Keep the default setting in the Name field and type in the name of the database table in the Default value field, employees in this case.

  7. Click Finish to validate the setting.

    The context parameter context.TABLE automatically appears in the Table Name field.

  8. In the Query type list, select Built-In. Then, click Guess Query to get the query statement.

    In this use case, we want to read the records with the salary above 8000. Therefore, we add a Where clause and the final query statement is as follows:

    "SELECT 
      "+context.TABLE+".`id`, 
      "+context.TABLE+".`first_name`, 
      "+context.TABLE+".`last_name`, 
      "+context.TABLE+".`city`, 
      "+context.TABLE+".`state`, 
      "+context.TABLE+".`date_of_birth`, 
      "+context.TABLE+".`salary`
    FROM "+context.TABLE+"
    WHERE
      "+context.TABLE+".`salary` > 8000"
  9. Double-click tLogRow to set its Basic Settings in the Component tab.

  10. In the Mode area, select Table (print values in cells of a table) for a better display of the results.

  11. Save the Job.

Executing the Job

The results below can be found after F6 is pressed to run the Job.

As shown above, the records with the salary greater than 8000 are retrieved.