Access GET method using InvokeHTTP processor with dynamic parameter

Introduction

This document explains the steps to get response from HTTP GET method using the InvokeHTTP processor by dynamically changing the parameter value.

Objective

To create workflow to get response from HTTP GET method using the InvokeHTTP processor and store the parsed output data into SQL server.

Processors used in workflow:

Workflow overview

Prerequisites

Before starting the workflow, create a table in SQL server. Use the following steps to create a table:

1. Right click the generate new table process group.
2. In the context menu, click Start option.

After the workflow inside the process group is started, the required table will be generated in SQL server.

Note: For configuring and enabling controller service, please refer to https://www.syncfusion.com/kb/7678/how-to-create-dbcpconnectionpool-in-syncfusion-data-integration-platform

Steps involved in workflow:

Step 1

• Drag and drop the GetFile processor into canvas area. In Scheduling tab, Select Scheduling Strategy and enter Run schedule as needed. Refer to the following screenshot.

• In properties tab, enter value for Input Directory, File Filter, and Keep Source File properties as mentioned in the following screenshot.

Note: IncomeLevelId.txt file will be available in shared folder.

Step 2

Drag and drop the SplitText processor into canvas area. Make connection of GetFile processor with SplitText processor through Success relationship. To get each line as separate FlowFile, enter value as 1 in the Line Split Count property as mentioned in the following screenshot.

Note: To ignore the failure case, please select the auto-terminate option for corresponding relationships. Refer to the following screenshot.

Step 3

Drag and drop the ExtractText processor into canvas area. Make connection of SplitText processor with ExtractText processor through splits relationship. In properties tab, add the new field using add button and update the value for newly created property (IncomeLevel) as in the following screenshot.

Step 4

• Drag and drop the InvokeHTTP processor into canvas area. Make connection of ExtractText processor with InvokeHTTP processor through matched relationship. In properties tab, enter value for HTTP method and Remote URL property as in the following screenshot.

The API used is http://api.worldbank.org/v2/countries?incomeLevel=${IncomeLevel}&format=json

• The rest API is referred from https://datahelpdesk.worldbank.org/knowledgebase/articles/898590-api-country-queries

• The output rest API response is specified as JSON format (used format=json in URL) in Remote URL. By default, it is XML format.

Step 5

Drag and drop the SplitJson processor into canvas area. Make connection to InvokeHTTP processor with SplitJson processor with “Response” relationship. To get JSON array value from another array, enter $.[1].* in JsonPath Expression field as in the following screenshot.

Step 6

• Drag and drop the EvaluateJsonPath processor into canvas area. Make connection of SplitJson processor with EvaluateJsonPath processor with “split” relationship. In properties tab, choose flowfile-attribute for Destination field to update the new property values in FlowFile attributes. To update required values into FlowFile attributes, add new required properties by using JSON path expression.

• In the sample, the new properties (id, iso2Code, name, region, adminregion, incomeLevel, lendingType, capitalCity, longitude, latitude) are added that to be updated in SQL database.

Step 7

• Drag and drop the UpdateAttribute processor into canvas area. Make connection of EvaluateJsonPath processor with UpdateAttribute processor through matched relationship. In properties tab, enter the updated value for FlowFile attributes (capitalCity and name) as in the following screenshot.

• In this sample, some data contains single slash. It will throw error while executing the insert statement using the PutSQL processor. For this case, the single slash can be replaced with white space for capitalCity and Name attribute property.

Step 8

• Drag and drop the ReplaceText processor into canvas area. Make connection of UpdateAttribute processor with ReplaceText processor through success relationship. In properties tab, enter the SQL insert query in replacement value field as in the following screenshot.

• Please refer to the following insert queries used in the sample.

insert into worldbanksample (id, iso2Code, name, region, adminregion, incomeLevel, lendingType, capitalCity, longitude, latitude) values ('${id}', '${iso2Code}', '${name}', '${region}', '${adminregion}', '${incomeLevel}', '${lendingType}', '${capitalCity}', '${longitude}', '${latitude}')

Step 9

Drag and drop the PutSQL processor into canvas area. Make connection of ReplaceText processor with PutSQL processor through success relationship. In properties tab, configure the controller service for JDBC Connection Pool field and enter value for support Fragmented Transactions as false. Refer to the following screenshot.

Step 10

After the previous configurations are done, start the workflow. It will store the parsed data into SQL server.

Output data