PULL API: expose a data source as an API endpoint

PULL API

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.

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 (search for to web service) which was created in Step 1 and the format created in Step 2.

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

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 files and API endpoints

Follow the same steps as you would normally follow when the source is file or API endpoint.

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

Testing using Integrator

Step 1. Create an HTTP connection with the following parameters to call the API endpoint:

• URL - the API endpoint 
• Method - GET
• Authentication - token
• Authentication URL - https://etlworkshost/rest/v1/token-auth/issue
• HTTP Method for token Authentication - POST
• Access token attribute - token
• Access token prefix - Bearer
• Authentication Request Payload - {"login":"{user}","password":"{password}"}
• Authentication Request Content Type - application/json
• User - user id of the user with appropriate permissions to call particular API (refer to the API docs) 
• Password - password of the user with appropriate permissions to call particular API (refer to the API docs) 

Step 2. Use Explorer to browse the API endpoint and view the data.

Testing using Postman

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.