Looking up customer information from a database - 6.2

Talend Data Services Platform Getting Started Guide

Talend Data Services Platform
Administration and Monitoring
Design and Development
Installation and Upgrade
Talend Administration Center
Talend CommandLine
Talend DQ Portal
Talend ESB
Talend Installer
Talend Runtime
Talend Studio

In this scenario, you will learn:

Setting up input data

The example in this document assumes that the customer data you want to look up is stored in a MySQL database. If you want to replicate the example and use the exact input data, you can download the gettingstarted.sql file of the customer data and then import it in a MySQL database.


  • You have an access to a MySQL database.

  • You have downloaded the gettingstarted.sql file by clicking here and stored it locally.

To import the customer data into a MySQL database:

  1. Open the MySQL Workbench to launch an instance of the database.

  2. From the menu bar, select Server > Data Import to open the import wizard wizard.

  3. Select the Import from Self-Contained File option and browse to where you have stored the gettingstarted .sql file.

  4. Select the schema to which you want to import the data, or click New... to define a new schema.

  5. Click Start Import in the lower right corner.

    The gettingstarted database is imported in the MySQL database.

Building a Customer service provider

This section provides you with step-by-step instructions on building the Customer service provider that will give access to the Web service via a WSDL, send a request and retrieve the response.

Creating a service

In this section, you will create a WSDL to define the Customer service.

  1. In the Integration perspective of Talend Studio, right-click the Services node in the Repository tree view, and select Create Service from the contextual menu.

  2. In the pop-up wizard, enter the name CustomerService, the purpose demo and a description of the service, and then click Next.

  3. Select the Create new WSDL option, and then click Finish.

    The service opens in the design workspace with a basic WSDL skeleton, which contains one service, one binding and one port type of one operation.

  4. Click the arrow icon to the right of the input element, CustomerServiceOperationRequest in the WSDL skeleton. The schema editor opens, allowing you to define the schema of the request message.

  5. Right-click the in element and select Show properties in the context menu.

    In the Properties view, change its name to id in the Name field, as in this use case, the request message will be the customer id.

    Click the icon in the menu bar of the Talend Studio to save the schema and close it.

  6. In the WSDL skeleton, click the arrow icon to the right of the output element, CustomerServiceOperationResponse in the WSDL skeleton to edit the schema of the response message in the schema editor.

  7. In this use case, there will be two rows, email and phone of the customer in the response message.

    Click the out element and change its name to Phone in the Properties view.

  8. In the design workspace, right-click the Phone element and select the Insert Element > After in the context menu.

    Give the name Email to it in the Properties view.

    Click the icon in the menu bar of the Talend Studio to save the schema and close it.

  9. Save the WSDL file. It will be used to build the Web service.

The newly defined Web service with exclamation icon is then shown under the Services node of the Repository view. The exclamation icon means that this defined Web service is not yet used.

Configuring and exposing the service

In this scenario, the data service provider uses the tESBProviderRequest and the tESBProviderResponse components to create the access to the Customer Web service and uses the tXMLMap component to join the Customer data provided by a given MySQL database into the request-response main flow for publication. The database data is loaded by the tMysqlInput component.

  1. Save the service details and WSDL Request / Response data types to the Metadata so that they can be accessible to other components. In Services, right-click CustomerService 0.1 and select Import WSDL Schemas.

    This option imports the WSDL metadata from the service into the Repository, under the Metadata > File xml, which allows you to share the operations details across services and other components.

  2. Expand the elements displayed in CustomerService 0.1, right-click CustomerServiceOperation 0.1 and select Assign Job.

  3. The [Assign Job] wizard opens. Select the Create a new Job and Assign it to this Service Operation and click Next.

  4. In the [New Job] view of the wizard, the Job to be created is already named automatically, so simply click Finish.

  5. A default template of the Job is opened on the workspace. In the template, a tESBProviderRequest and a tESBProviderResponse are already selected and configured. tESBProviderRequest will send a request to the specified Web service and tESBProviderResponse will send back the response corresponding to the request. These two components can be found in the ESB group of the Palette.

    Now add a tXMLMap between the two ESB components by typing its name on the design workspace.

  6. Right-click the tESBProviderRequest_1 and select Row, then Main and drop the end of the line on tXMLMap_1.

  7. Right-click tXMLMap_1, select Row > Main and drop the end of the line on tESBProviderResponse_1. Give it the name response, and click OK. Click the default Yes when asked if you wish to import the schemas.

  8. Add a tMysqlInput below the tXMLMap by typing its name on the design workspace to load the customer data in a MySQL database.

  9. Right-click tMysqlInput, select Row > Main and drop the end of the line on tXMLMap.

Configuring the service operation

In this section, the service operation is customized to match the scenario.

  1. On the workspace, double-click tMysqlInput to open its Basic settings view in the Component tab.

  2. Configure the tMysqlInput to load the customer data in a MySQL database.

    In the DB version field, select the version of your MySQL database. It is Mysql 5 in this example.

    Specify the connection details in the relevant fields, including:

    • the host name or IP address of your database server

    • the listening port number

    • the database name

    • the user name and password for your database authentication

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

    The schema editor opens. Click the [+] button to add three rows of String type and give the name id, Phone, Email to the columns. Click OK to close the schema editor.

  4. In the Table Name field, type in the name of the database table, customers in this case.

  5. In the Query box, enter the query required to retrieve the desired columns from the table, id, Phone, Email in this example.

  6. On the workspace, double-click tXMLMap to open its editor. At this moment, the editor should look like: