Scenario 2: Using PreparedStatement objects to query data - 6.3

Talend Open Studio for Big Data Components Reference Guide

Talend Open Studio for Big Data
Data Governance
Data Quality and Preparation
Design and Development
Talend Studio

This scenario describes a four component job which allows you to link a table column with a client file. The MySQL table contains a list of all the American States along with the State ID, while the file contains the customer information including the ID of the State in which they live. We want to retrieve the name of the State for each client, using an SQL query. In order to process a large volume of data quickly, we use a PreparedStatement object which means that the query is executed only once rather than against each row in turn. Then each row is sentas a parameter.

For this scenario, we use a file and a database for which we have already stored the connection and properties in the Rerpository metadata. For further information concerning the creation of metadata in delimited files, the creation of database connection metadata and the usage of metadata, see Talend Studio User Guide.

Configuring the input component

  1. In the Repository, expand the Metadata and File delimited nodes. Select the metadata which corresponds to the client file you want to use in the Job.

    Here, we are using the customers metadata.

  2. Slide the metadata onto the workspace and double-click tFileInputDelimited in the [Components] dialog box so that the tFileInputDelimited component is created with the parameters already set.

  3. 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.

  4. 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.

  5. From the Palette, select the tAmazonMysqlRow, tParseRecordSet and tFileOutputDelimited components and drop them onto the workspace. Connect the four components using Row > Main type links.

Setting up the DB connection

  1. Double-click tAmazonMysqlRow to set its properties in the Basic settings tab of the Component view.

  2. 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.

  3. 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.

  4. 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.

Configuring the Advanced settings of tAmazonMysqlRow

  1. Click Advanced settings to set the component's advanced properties.

  2. 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.

  3. Select the Use PreparedStatement check box and define the parameter used in the query in the Set PreparedStatement Parameters table. Click on the [+] button to add a parameter.

  4. 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.

  5. In the Parameter Type cell, enter the type of parameter. Here, the parameter is a whole number, hence, select Int from the list.

  6. 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".

Configuring the tParseRecordSet component

  1. Double-click tParseRecordSet to set its properties in the Basic settings tab of the Component view.

  2. From the Prev. Comp. Column list, select the preceding components column for analysis. In this example, select LabelStateRecordSet.

  3. Click on the Sync columns button to retrieve the schema from the preceding component. The Attribute table is automatically completed with the schema columns.

  4. 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".

Configuring the output component

  1. Double-click tFileOutputDelimited to set its properties in the Basic settings tab of the Component view.

  2. In the File Name field, enter the access path and name of the output file. Click Sync columns to retrieve the schema from the preceding component.

Job execution

Save your Job and press F6 to run it.

A column containing the name of the American State corrresponding to each client is added to the file.