Automation and Building REST APIs

In Etlworks Integrator, it is possible to build REST APIs with zero coding, using our drag-and-drop interface. The following integration scenarios are supported:

Basic ideas

The core element of the infrastructure, which allows one to build REST APIs in Integrator, is the HTTP listener. The HTTP listener is a special type of connector that is constantly listening to incoming HTTP requests, using a configurable PATH and METHOD.

Depending upon which side of the transformation (source or destination) the HTTP listener sits, it can either push payload to the flow or return a response to the client.

Authentication

Any API built in Integrator requires authentication, using either a JWT token, received from the authentication API, or a user name and password.

JWT Authentication

To configure an API endpoint with JWT authentication, select JWT when defining the Auth Type parameter for the HTTP listener. Select either Header (recommended and more secure) or Query Parameter (less secure) for the Auth Location.

Learn about the Authentication API.

Read how to call Integrator's REST API endpoints, when using JWT authentication.

Basic Authentication

Important: generally speaking, basic authentication is less secure than JWT. Use it only if it's impossible to use a two-step authentication. For example, this would be used when a third-party application has a simple web hook that sends a payload to the API.

To configure an API endpoint with Basic authentication, select Basic when defining the Auth Type parameter for the HTTP listener. Select either Header (recommended and more secure), or Query Parameter (less secure) for the Auth Location.

Read how to call Integrator's REST API endpoints when using basic authentication.

Defining endpoints

In Integrator, you define an endpoint by specifying the URL Pattern parameter, when creating an HTTP listener.

Important: only one HTTP listener with that particular URL Pattern can exist at the same time. For example, you cannot create multiple HTTP listeners with the URL Pattern /patients. You can, however, create a listener with the URL Pattern /patients and another listener with the URL Pattern /patient.

Within URL Pattern, you can specify path parameters: /patients/{patientid} and query parameters: /patients?patientid={patientid}. They will be automatically converted to variables, which can then be referenced from the source SQL. From a functional standpoint, the path parameters = query parameters.

An example of an endpoint with path parameters:

/patients/{patientid} --> https://app.etlworks.com/plugins/schedules/rest/v1/httplistener/hl7/patients/123

An example of an endpoint with query parameters:

/?patientid={patientid} --> https://app.etlworks.com/plugins/schedules/rest/v1/httplistener/hl7/patients/?patientid=123

An example of SQL with parameters:

select * from patient where patientid = {patientid}

Pull

Pull means to expose a data source as a REST API endpoint.

Problem: we have a database which sits behind a firewall and is not accessible from the Internet. We would like to build a REST API that can be used to access a specific, controlled portion of the data from the other side of the firewall.

Solution: any data source which Integrator can read can be exposed as a REST API endpoint. Read more about available data sources.

Steps to build a REST API

Step 1. Create a new HTTP listener. Select GET as the HTTP method.

User's Listener

Step 2. Create a format for the response. The following formats are supported: JSON, JSON Dataset, XML, XML Dataset, CSV and Fixed Length Text Format.

Step 3. Create a flow, where the Source is any supported data source and the Destination is the HTTP listener which was created in Step 1 and the format created in Step 2.

User's Flow

Step 4. Define the source query, mapping and parameters, if needed.

Source Query

Step 5. Schedule an event-driven flow.

Working with databases

Follow the same steps as you would normally follow when the source is a database.

Working with data objects

Follow the same steps as you would normally follow when the source is a data object.

Important: you can define an SQL query which will be executed on the source data object, even if it is a file, web service or data in key-value storage.

Testing the GET API endpoint

The simplest way to test the GET API endpoint, right from the browser, is to use the Postman plugin, for example, within Chrome.

Step 1. Create a POST authentication request in Postman:

URL: https://app.etlworks.com/rest/v1/token-auth/issue

Body: {"login":"user","password":"password"}. The user and password must be an existing Integrator user and password.

Step 2. Click the Send button.

Step 3. Copy the token body from the response.

Response example:

{
  "token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzUxMiJ9.eyJpc3MiOiJldGx3b3Jrcy5jb20iLCJpYXQiOjE0OTMyNDMwNDQsImV4cCI6MTQ5MzI0MzY0NCwianRpIjoiMjJiMzQ5NjktOTU5MS00NGRlLTg5OTEtOWExMjZiMmNmNDIzIiwic3ViIjoiYWRtaW4ifQ.fgjJRqjPfVSXpgB7o7A6Q7ohYyoWovKZwdvatojx5xBXi1NLJapMVaPyIaJGg3jKdiQDaKPgwYk6daH_nut5vg"
}

Step 4. Create a GET request in Postman using configure in the Integrator endpoint.

URL: https://app.etlworks.com/plugins/schedules/rest/v1/httplistener/patients/123.

Step 5. Specify that the Authorization header parameter will have a Bearer prefix, and pass it the value received from the response in Step 3.

Example: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzUxMiJ9.eyJpc3MiOiJldGx3b3Jrcy5jb20iLCJpYXQiOjE0OTMxNjQwNjAsImV4cCI6MTQ5MzE2NDY2MCwianRpIjoiNjc2Nzk5NTgtODQ3OC00Y2MzLWFiOWMtYmJmNjYyYjQxOGNlIiwic3ViIjoiYWRtaW4ifQ.Loxfquv-qjuQRT15eRiunFCFWuWwtZTosrn86ZcWguoPbiiC00toXmFTJ4QqYYgdAHO7QFIEeqqNdgAXwED2lg.

Step 6. Click the Send button.

Push

In Integrator, you can configure an API endpoint so that when it receives a payload from a third-party application, it will trigger execution of a data integration flow.

Steps to building a REST API

Read how to build event-driven flows.

Testing an API endpoint

For a flow which is configured to be executed on-demand, you can create another flow, which will send an HTTP request, right from Integrator.

Let's assume that there is a scheduled on-demand flow which is triggered by sending a POST request to the https://app.etlworks.com/plugins/schedules/rest/v1/httplistener/patient endpoint, with a payload that contains a JSON document representing the patient.

For testing purposes, let's put patient.json in the folder on the server or cloud storage.

Step 1. Create an HTTP connection with the following parameters:

URL: https://app.etlworks.com/plugins/schedules/rest/v1/httplistener/patient.

Authentication: token.

Authentication URL: https://app.etlworks.com/rest/v1/token-auth/issue.

HTTP Method for Token Authentication: POST.

Authentication Request Payload: {"login":"{user}","password":"{password}"}

Authentication Request Content Type: application/json.

User: any existing Integrator user who has permission to run flows.

Password: password for the user.

Method: POST.

Step 2. Create a flow where the source connection points to patient.json, the source format is JSON, the destination connection is the connection created in Step 1, and the destination format is the same as the source format.

Step 3. Manually execute the flow created in Step 2 and watch it trigger execution of an on-demand flow.

CRUD

In Integrator, you can create REST APIs for CRUD operations by creating event-driven flows and selecting the appropriate method, when defining the HTTP listener parameters.

The following methods are supported: