Scenario 2: Using URI Query parameters to explore the data of a database - 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

This scenario describes how to use URI query parameters in tRESTRequest to explore data of a database, and send the response via the tRESTResponse.

To do so, you can create two subjobs linked together by an OnSubjobOk connection; this way the two subjobs will be executed sequentially. For more information on Trigger connection, see the Talend Studio User Guide. The first subjob will create and populate the database and the second one will allow to explore the database through the REST service.

Creating the first subjob

To do this, proceed as follows:

  1. Drop the following components from the Palette onto the design workspace: tFixedFlowInput from the Misc family and tMysqlOutput from the Databases > Mysql family.

  2. Link tFixedFlowInput to tMysqlOutput using a Row > Main connection.

  3. Double-click tFixedFlowInput to display its Basic settings view:

  4. Click the [...] button next to Edit schema to open the schema editor.

  5. In the schema editor, click the [+] button three times to add three lines and set them as displayed in the above screenshot.

  6. Click Ok.

  7. Back to tFixedFlowInput Basic settings view, in the Mode area, select the Use inline table option.

  8. Under the inline table, click the [+] button three times to add three rows in the table.

  9. In the inline table, click the id field of the first row and type in 1.

  10. Click the firstname field of the first row, press Ctrl+Space to display the autocompletion list and select the TalendDataGenerator.getFirstName() variable in the list.

  11. Click the lastname field of the first row, press Ctrl+Space to display the autocompletion list and select the TalendDataGenerator.getLastName() variable in the list.

  12. Do the same for the two following rows to obtain the settings displayed in the screenshot.

  13. Double-click tMysqlOutput to display its Basic settings view:

  14. From the Property Type list, leave Built-in and fill in the Host, Port, Database, Username and Password fields manually. If you centralized your connection information to the database in the Metadata > DB Connections node of the Repository, you can select Repository from the list and the fields will be automatically filled in.

    For more information about storing metadata, see Talend Studio User Guide.

  15. In the Table field, type in the name of the table in which the data will be loaded, for example: users.

  16. From the Action on table list, select Drop table if exists and create, select Insert from the Action on data list.

  17. Click Sync columns to retrieve the schema coming from the previous component.

Creating the second subjob

To do this, proceed as follows:

  • Drop and place the following components as displayed in the first screenshot:

    • tRESTRequest and tRESTResponse from the ESB > REST family,

    • tFlowToIterate from the Orchestration family,

    • tMysqlInput from the Databases > Mysql family,

    • tXMLMap from the Processing family.

Configuring the tRESTRequest component

To do this, proceed as follows:

  1. Double-click tRESTRequest in the design workspace to display its Basic settings view:

  2. Fill the REST Endpoint field with the URI location where the REST-ful web service will be accessible for requests. For example, "http://localhost:8088/users".

  3. Click the [+] button to add one line in the REST API Mapping table.

  4. Select the newly-added line and click the [...] button in the Output Flow column to add a schema for the output flow.

  5. In the dialog box, name the output flow getUsers. A schema editor dialog box appears.

  6. In the schema editor, click the [+] button twice to add two lines and set them as displayed in the above screenshot.

  7. Click OK.

  8. Back to tRESTRequest Basic settings view, select GET from the list in the HTTP Verb column.

  9. Leave the URI Pattern column as is.

Now that you created the tRESTRequest output flow, you can use the corresponding link to connect to the following component:

  1. Connect tRESTRequest to tFlowToIterate using Row > getUsers connection.

  2. Leave the tFlowToIterate settings as is.

  3. Connect tFlowToIterate to tMysqlInput using Row > Iterate connection.

Configuring the tMysqlInput component

To do this, proceed as follows:

  1. Double-click tMysqlInput to display its Basic settings view:

  2. From the Property Type list, leave Built-in and fill in the Host, Port, Database, Username and Password fields manually. If you centralized your connection information to the database in the Metadata > DB Connections node of the Repository, you can select Repository from the list and the fields will be automatically filled in.

    For more information about storing metadata, see Talend Studio User Guide.

  3. Leave the Schema list as Built-in and click the [...] button next to the Edit schema field.

  4. In the schema editor, define the schema exactly like the one of the tFixedFlowInput.

  5. In the Table Name field, fill in the name of the table in which the data are stored: users.

  6. Leave the Query Type list as Built-in and fill in the Query field with the following SQL query allowing to explore the database data with the URI query set in the tRESTRequest component:

    "select * from users where id >= " + globalMap.get("getUsers.from") + " and  id <= " + globalMap.get("getUsers.to")
Configuring the tXMLMap component
  1. Right-click tMysqlInput, hold and drag to tXMLMap to connect the two components together.

  2. Double-click tXMLMap in the design workspace to open the Map Editor.

  3. Click the [+] button on the top right to add an output and name it as ResponseUsers.

  4. Click the [+] button on the bottom right to add two columns for the output.

    Name the first column as body and set the Type to Document.

    Name the second column as string and set the Type to String.

  5. Right-click on the root node, select Rename in the list and rename it users

  6. Right-click on the users node and select Create Sub-Element to create a sub-element. Name the sub-element user in the popup dialog box.

  7. Right-click on the user node created in the previous step and select As loop element.

  8. Select the id column of the row2 table to the left and drop it onto the user node of the ResponseUsers table to the right.

  9. In the [Selection] dialog box, select the Create as attribute of target node option and click OK.

  10. Select the firstname and lastname columns of the row2 table to the left and drop it onto the user node of the ResponseUsers table to the right.

  11. In the [Selection] dialog box, select the Create as sub-element of target node option and click OK.

  12. Click the wrench icon on the top of the ResponseUsers table to open the setting panel.

  13. Set the All in one feature as true, this way all XML data is outputted in one single flow.

  14. Click OK to save the settings.

Configuring the tRESTResponse component
  1. Connect tXMLMap to tRESTResponse using Row > ResponseUsers connection.

  2. The schema defined in tXMLMap is retrieved in tRESTResponse automatically. Leave the other settings as they are.

Connecting the two subjobs

Now that the two subjobs are created, you can connect them together:

  1. Right-click the tFixedFlowInput component of the first subjob.

  2. Select Trigger > OnSubjobOk on the list.

  3. Click the tRESTRequest component of the second subjob.

This way, when executing the job, the second subjob will be executed only if the first one's execution succeeded.

Saving and executing the Job

  1. Save the Job and press F6 to execute it.

  2. Go to your browser if you want to test the service.

    For example, use the URI query ?to=2 to retrieve the data of the two first users.

    The HTTP request for a user id is accepted by the REST service and the HTTP response is sent back to the server.

    For a use case that calls this REST service using Talend ESB components, see Scenario 1: Getting user information by interacting with a RESTful service.