Scenario: Writing data to and reading data from a MemSQL database table - 6.3

Talend Open Studio for Big Data Components Reference Guide

EnrichVersion
6.3
EnrichProdName
Talend Open Studio for Big Data
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

In this scenario a Job is built to write a table to a MemSQL database, and then retrieve the data from the table.

Dragging and dropping components and linking them together

  1. Drop a tMemSQLConnection, a tRowGenerator, a tMemSQLInput, a tMemSQLOutput, a tMemSQLClose, and a tLogRow from the Palette onto the workspace.

  2. Link the tMemSQLConnection to the tRowGenerator using a Row > OnSubjobOk connection.

  3. Link the tRowGenerator to the tMemSQLOutput using a Row > Main connection.

  4. Link the tRowGenerator to the tMemSQLInput using a Row > OnSubjobOk connection.

  5. Link the tMemSQLInput to the tLogRow using a Row > Main connection.

  6. Link the tMemSQLInput to the tMemSQLClose using a Row > OnSubjobOk connection.

Configuring the components

Opening and closing connection to the database

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

  2. In the Property Type list, select Built-In. Specify the connection details in the relevant fields, including the host name and listening port number of the database server, the user name and password for your database authentication, and the database name.

  3. Double-click tMemSQLClose to set its Basic settings in the Component tab.

  4. The Component List is filled in automatically with tMemSQLConnection_1.

Writing data in a database table

  1. Double-click the tRowGenerator component to open the Row Generator editor.

  2. Add three columns as shown below to generate 10 data rows:

    • id, Integer type, using Numeric.sequence from the Function list to generate sequence numbers.

    • firstName, String type, using TalendDataGenerator.getFirstName from the Function list to generate random first names.

    • lastName, String type, using TalendDataGenerator.getLastName from the Function list to generate random family names.

  3. In the Basic settings view of tRowGenerator in the Component tab, click the Edit Schema button to open the schema editor. Set the length of the id, firstName, lastName column to 4, 10, and 10.

    When done, click OK to close the editor and click Yes when prompted to propagate the schema to the next component.

    For more information about how to configure the tRowGenerator component, see tRowGenerator.

  4. Double-click tMemSQLOutput to open its Basic Settings view in the Component tab.

  5. Select the Use Existing Connection check box. The Component List is filled in automatically with tMemSQLConnection_1.

    Fill the Table field with the database table, customers in this example.

    Select Drop table if exists and create in the Action on table list, and Insert in the Action on table list.

Reading data from a database table

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

  2. Select the Use Existing Connection check box. The Component List is filled in automatically with tMemSQLConnection_1.

  3. Set the Schema as Built-in and click Edit schema to define the data structure of the database table to read data from.

    Click the [+] button to add the rows that you will use to define the schema, three columns in this example id, firstName, and lastName.

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

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

    Click OK to close the schema editor.

  4. Fill the Table field with the database table, customers in this example.

    Alternatively, click the [...] button next to the Table Name field to select the database table of interest. A dialog box opens showing a tree diagram of all the tables in the selected database. Select customers and click OK to close the dialog box.

  5. Set the Query Type as Built-In. Click the Guess Query button. The Query box is filled in automatically to retrieve all columns from the selected table.

  6. Double-click tLogRow to set its Basic settings in the Component tab.

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

  8. Save the Job.

Executing the Job

Press F6 to run the Job and see the results in the console.

As shown above, the data with the desired column names is retrieved from the database table.