Scenario: Inserting data into a database table and extracting useful information from it - 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 scenario describes a Job that reads the employee data from a text file, inserts the data into a table of an MSSQL database, then extracts useful data from the table, and displays the information on the console.

This scenario involves the following components:

  • tMSSqlConnection: establishes a connection to the MSSQL server.

  • tFileInputDelimited: reads the input file, defines the data structure and sends it to the next component.

  • tMSSqlOutput: writes data it receives from the preceding component into a table of an MSSQL database.

  • tMSSqlInput: extracts data from the table based on an SQL query.

  • tLogRow: displays the information it receives from the preceding component on the console.

  • tMSSqlCommit: commits the transaction in the connected MSSQL server.

Setting up the Job

  1. Drop the following components from the Palette onto the design workspace: tMSSqlConnection, tFileInputDelimited, tMSSqlOutput, tMSSqlInput, tLogRow, and tMSSqlCommit.

  2. Connect tMSSqlConnection to tFileInputDelimited using a Trigger > OnSubjobOk link.

  3. Do the same to connect tFileInputDelimited to tMSSqlInput and tMSSqlInput to tMSSqlCommit.

  4. Connect tFileInputDelimited to tMSSqlOutput using a Row > Main link.

  5. Do the same to connect tMSSqlInput to tLogRow.

Configuring the components

Opening a connection to the MSSQL server

  1. Double-click the tMSSqlConnection component to open its Basic settings view in theComponent tab.

  2. In the Host field, type in the IP address or hostname of the MSSQL server, 192.168.30.47 in this example.

  3. In the Port field, type in the port number of the database server, 1433 in this example.

  4. In the Schema field, type in the schema name, dbo in this example.

  5. In the Database field, type in the database name, talend in this example.

  6. In the Username and Password fields, enter the credentials for the MSSQL connection.

Reading the input data

  1. Double-click the tFileInputDelimited component to open its Component view.

  2. Click the [...] button next to the File Name/Stream field to browse to the input file. In this example, it is D:/Input/Employee_Wage.txt. This text file holds three columns: id, name and wage.

    id;name;wage
    51;Harry;2300
    40;Ronald;3796
    17;Theodore;2174
    21;James;1986
    2;George;2591
    89;Calvin;2362
    84;Ulysses;3383
    4;Lyndon;2264
    17;Franklin;1780
    86;Lyndon;3999
  3. In the Header field, type in 1 to skip the first row of the input file.

  4. Click Edit schema to define the data to pass on to the tMSSqlOutput component. In this example, we define id as the key, and specify the length and precision for each column respectively.

    Click OK to close the schema editor. A dialog box opens, and you can choose to propagate the schema to the next component.

    Related topic: tFileInputDelimited.

Writing the data into the database table

  1. Double-click the tMSSqlOutput component to open its Basic settings view in the Component tab.

  2. Type in required information for the connection or use the existing connection you have configured before. In this example, we select the Use an existing connection check box. If multiple connections are available, select the connection you want to use from the Component List drop-down list.

  3. In the Table field, type in the name of the table you want to write the data to: Wage_Info in this example. You can also click the [...] button next to the Table field to open a dialog box and select a proper table.

  4. Select Create table if not exists from the Action on table drop-down list.

  5. Select Insert if not exists from the Action on data drop-down list.

  6. Click Sync columns to retrieve the schema from the preceding component.

Extracting useful information from the table

  1. Double-click the tMSSqlInput component to open its Basic settings view in the Component tab.

  2. Select the Use an existing connection check box. If multiple connections are available, select the connection you want to use from the Component List drop-down list.

  3. Click Edit schema to define the data structure to be read from the table. In this example, we need to read all three columns from the table.

  4. In the Table Name field, type in the name of the table you want to read the data from: Wage_Info in this example.

  5. In the Query field, fill in the SQL query to be executed on the table specified. To obtain the data of employees whose wages are above the average value and order them by id, enter the SQL query as follows:

    SELECT    * 
    FROM      Wage_Info
    WHERE     wage >
    (SELECT   avg(wage)
    FROM      Wage_Info)
    ORDER BY  id
    

Displaying information on the console

  1. Double-click the tLogRow component to open its Basic settings view.

  2. In the Mode area, select Table (print values in cells of a table).

Committing the transaction and closing the connection

  1. Double-click the tMSSqlCommit component to open its Basic settings view.

  2. Select the Close Connection check box.

Saving and executing the Job

  1. Press Ctrl+S to save your Job.

  2. Execute the Job by pressing F6 or clicking Run on the Run tab.

    The information of employees whose wages are above the average value ordered by id is displayed on the console.