Configuring the components - 6.5

MySQL

author
Talend Documentation Team
EnrichVersion
6.5
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 ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Data Governance > Third-party systems > Database components > MySQL components
Data Quality and Preparation > Third-party systems > Database components > MySQL components
Design and Development > Third-party systems > Database components > MySQL components
EnrichPlatform
Talend Studio

Procedure

  1. Double-click tFileInputDelimited to open its Basic settings view.
  2. In the Schema list, select Built-in so that you can modify the component's schema. Then click on [...] next to the Edit schema field to add a column into which the name of the State will be inserted.
  3. Click on the [+] button to add a column to the schema. Rename this column LabelStateRecordSet and select Object from the Type list. Click OK to save your modifications.
  4. From the Palette, select the tMysqlRow, tParseRecordSet and tFileOutputDelimited components and drop them onto the workspace.
  5. Double click tMysqlRow to set its properties in the Basic settings tab of the Component view.
  6. In the Property Type list, select Repository and click on the [...] button to select a database connection from the metadata in the Repository. The DB Version, Host, Port, Database, Username and Password fields are completed automatically. If you are using the Built-in mode, complete these fields manually.
  7. From the Schema list, select Built-in to set the schema properties manually and add the LabelStateRecordSet column, or click directly on the Sync columns button to retrieve the schemma from the preceding component.
  8. In the Query field, enter the SQL query you want to use. Here, we want to retrieve the names of the American States from the LabelState column of the MySQL table, us_state: "SELECT LabelState FROM us_state WHERE idState=?". The question mark, "?", represents the parameter to be set in the Advanced settings tab.
  9. Click Advanced settings to set the components advanced properties.
  10. Select the Propagate QUERY's recordset check box and select the LabelStateRecordSet column from the use column list to insert the query results in that column.
  11. Select the Use PreparedStatement check box and define the parameter used in the query in the Set PreparedStatement Parameters table.
  12. Click on the [+] button to add a parameter.
    1. In the Parameter Index cell, enter the parameter position in the SQL instruction. Enter "1" as we are only using one parameter in this example.
    2. In the Parameter Type cell, enter the type of parameter. Here, the parameter is a whole number, hence, select Int from the list.
    3. In the Parameter Value cell, enter the parameter value. Here, we want to retrieve the name of the State based on the State ID for every client in the input file. Hence, enter " row1.idState".
  13. Double click tParseRecordSet to set its properties in the Basic settings tab of the Component view.
    1. From the Prev. Comp. Column list, select the preceding components column for analysis. In this example, select LabelStateRecordSet.
    2. Click on the Sync columns button to retrieve the schema from the preceding component. The Attribute table is automatically completed with the schema columns.
    3. In the Attribute table, in the Value field which corresponds to the LabelStateRecordSet, enter the name of the column containing the State names to be retrieved and matched with each client, within double quotation marks. In this example, enter "LabelState".
  14. Double click tFileOutputDelimited to set its properties in the Basic settings tab of the Component view.
    1. In the File Name field, enter the access path and name of the output file.
    2. Click Sync columns to retrieve the schema from the preceding component.