Integrations and APIs

Whether you want to build event-driven data integration flows or incorporate Integrator into your own automation workflow, you can use the developer features below to achieve your goals. All the APIs in Integrator are REST-based.

Calling Integrator API endpoints with JWT auth

Step 1. Create an API user. Note: you can actually use any user, as long as they have a role that can execute flows.

Step 2. Use the API user to call an Integrator authentication endpoint and receive an access token.

Step 3. Use the access token, received in Step 2, to call Integrator API endpoints. The access token must be submitted as a header parameter, as in: Authorization:Bearer access-token, or query parameter, as in: ?Authorization=Bearer%20access-token.

Important: access tokens in Integrator are short-lived and self-expiring. An access token gives you access to the APIs for approximately 10 minutes. It is recommended that you refresh the access token before each call to the API.

Calling Integrator API endpoints with basic auth

Step 1. Create an API user. Note: you can actually use any user, as long as they have a role that can execute flows.

Step 2. Use the user name and password for the user created in Step 1, to call an API endpoint.

Read more about basic authentication.

APIs

Authentication

This API must be used to receive an access token, giving the third-party application temporary access to Integrator's APIs.

PATH: /rest/v1/token-auth/issue

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

METHOD: POST

REQUEST BODY: {"login":"user","password":"password"}, where user is the user id of the API user and password is the password for the API user.

REQUEST CONTENT TYPE: application/json

RESPONSE: {"token": "access token"}, where "access token" is an actual access token which will be used for all authenticated requests to the API.

RESPONSE CODES: 200 for success, 401 and 403 for not authorized, and 500 for an internal error.

Run flow by name

You can incorporate Integrator into your own automation workflow by delegating heavy-duty integration tasks and executing flows on demand.

Flow can be executed asynchronously (the response is returned to the client immediately) and synchronously (the response is returned to the client once flow is executed).

To execute flow by name asynchronously:

PATH: /plugins/flows/rest/v1/flows/flow-name/{flowName}/run/?optional_parameters

To execute flow by name synchronously:

PATH: /plugins/flows/rest/v1/flows/flow-name/{flowName}/run/?sync=true&optional_parameters

EXAMPLE (with no parameters): https://app.etlworks.com/plugins/flows/rest/v1/flows/flow-name/my-flow/run, where my-flow is the name of the flow to execute.

EXAMPLE (with a parameter): https://app.etlworks.com/plugins/flows/rest/v1/flows/flow-name/my-flow/run/?patientid=123, where my-flow is the name of the flow to execute, patientid is a parameter and 123 is the value of the parameter. There can be multiple & separated parameters, following this pattern: ?parameter1=value&parameter2=value.

Important: make sure that the URL is properly encoded.

Parameters can be referenced in the Source SQL, FROM and TO fields and connection parameters as {parameter_name}. They can be also accessed programmatically.

An example of an endpoint with parameters:

https://localhost:8080/plugins/flows/rest/v1/flows/flow-name/flow_with_parameters/run?roleid=2

An example of SQL with parameters:

select * from role where role = {roleid}

METHOD: POST

HEADER: Authorization:Bearer access-token

REQUEST BODY: none.

REQUEST CONTENT TYPE: none.

RESPONSE:

{"flowId":flodId,
 "messageUuid":"UUID of the message if flow was executed by sending payload to the custom endpoint", 
 "auditId":"ID of the record in audit table if flow was executed by name or by ID", 
 "status":status-code,
 "exception":"exception-stack-trace"
} 

Possible status codes: 0 - success, 1 - error.

RESPONSE CODES: 200 for success, 401 and 403 for not authorized, and 500 for an internal error.

Get flow status

This API endpoint is used to retrieve current status of the flow, even when flow is currently running.

PATH: /plugins/dashboard/rest/v1/flows{flowid}?optional_parameters

EXAMPLE: https://app.etlworks.com/plugins/dashboard/rest/v1/flows/1234

flow id

METHOD: GET

HEADER: Authorization:Bearer access-token

PARAMETERS:

REQUEST BODY: none

REQUEST CONTENT TYPE: none

RESPONSE: JSON document in the following format:

{
    "auditId": long, // unique audit ID
    "parentAuditId": long, // parent audit ID if there were auto-retry attempts when executing the flow. Null if there were no auto-retry attempts
    "retries": integer,// number of retries or null
    "flowId": long, // flow ID
    "flowName": "string", // flow name
    "eventId": long, // scheduled event ID
    "eventName": "string", // name of the scheduled event or null if flow has not been scheduled
    "messageId": "UUID", // message ID when flow has been executed by sending payload to the custom endpoint, otherwise null
    "tenantId": long, // tenant id if flow was executed by super-admin user, otherwise null
    "status": "string", // status of the flow execution: success, warning, error, running
    "triggerType": "string", // flow.execution.scheduler or flow.execution.on.demand or null
    "startedBy": "string", // who triggered the flow: scheduler, or user login name
    "started": long, // start time in milliseconds 
    "ended": 1531158790495, // end time in milliseconds. Only available if flow has been already executed
    "duration": "string", // human readable duration of the flow. Only available if flow has been already executed
    "code": "string", // last executed SQL statement in case of error, or null. Only available if flow has been already executed
    "exception": "string", // exception stack-trace or/end messages logged by the user using etlConfig.log(message). Only available if flow has been already executed
    "warnings": "string", // warnings or null. Only available if flow has been already executed
    "metrics": [metrics], // flow metrics or null. Only available if flow has been already executed
    "fileMetrics": [file metrics], // file metrics or null. Only available if flow has been already executed
    "errorLocationInSql": integer, // line number in last executed SQL in case of error, or null. Only available if flow has been already executed
    "fileName": null, // currently not used
    "records": 1 // currently not used
}

RESPONSE CODES: 200 for success, 401 and 403 for not authorized, and 500 for an internal error.

Audit-trail

Each event, such as a login, flow execution, etc. is captured in Etlworks Integrator's audit-trail log.

The last 1,000 events

To retrieve the last 1,000 events, use the following endpoint.

Important: to access this API, an admin user must make the authorization request.

PATH: /plugins/audit/rest/v1/audit?optional_parameters

EXAMPLE: https://app.etlworks.com/plugins/audit/rest/v1/audit

METHOD: GET

HEADER: Authorization:Bearer access-token

PARAMETERS: exception=false|true. Example: https://app.etlworks.com/plugins/audit/rest/v1/audit?exception=true. If the value of the optional parameter exception is true, the exception stack trace will be included in the response.

REQUEST BODY: none

REQUEST CONTENT TYPE: none

RESPONSE: JSON document in the following format:

[
    {
        "id": number, the audit record id
        "an": string, the application name
        "md": string, the event name
        "ur": string, the user name
        "tn": string, the tenant name
        "rt": boolean, if true the event was successful
        "in": boolean, if true the record has additional information
        "ex": string, the exception stack trace
        "hip": string, the host IP
        "cip": string, the client IP
        "dn": long, the duration in milliseconds
        "dt": string, the start date+time in milliseconds
    },
] 

Example:

[
    {
        "id": 12177,
        "an": "connections",
        "md": "editConnection",
        "ur": "admin",
        "tn": null,
        "rt": true,
        "in": false,
        "ex": null,
        "hip": "localhost:8080",
        "cip": "0:0:0:0:0:0:0:1",
        "dn": 7,
        "dt": "1522160520345"
    },
    {
        "id": 12176,
        "an": "flows",
        "md": "runFlow",
        "ur": "admin",
        "tn": null,
        "rt": false,
        "in": true,
        "ex": "com.toolsverse.etl.exception.EtlException: Attempted read from closed stream.",
        "hip": "localhost:8080",
        "cip": "0:0:0:0:0:0:0:1",
        "dn": 215086,
        "dt": "1522160093479"
    }    
]    

RESPONSE CODES: 200 for success, 401 and 403 for not authorized, and 500 for an internal error.

Filter audit-trail events

To filter audit-trail events use the following endpoint. Note: the endpoint can return up to 1,000 events.

Important: to access this API, an admin user must make the authorization request.

PATH: /plugins/audit/rest/v1/audit?optional_parameters

EXAMPLE: https://app.etlworks.com/plugins/audit/rest/v1/audit

METHOD: POST

HEADER: Authorization:Bearer access-token

PARAMETERS: exception=false|true. Example: https://app.etlworks.com/plugins/audit/rest/v1/audit?exception=true. If the value of the optional parameter exception is true, the exception stack trace will be included in the response.

REQUEST BODY: JSON document in the following format:

{
    "app": string, the application name
    "method": string, the event name
    "login": string, the user name
    "fromDate": string, the start date for the filter in Unix epoch format 
    "toDate": string, the end date for the filter in Unix epoch format 
    "duration": long, the duration in milliseconds
    "clientIp": string, the client IP
}

REQUEST CONTENT TYPE: application/json.

RESPONSE: JSON document in the following format:

[
    {
        "id": number, the audit record id
        "an": string, the application name
        "md": string, the event name
        "ur": string, the user name
        "tn": string, the tenant name
        "rt": boolean, if true the event was successful
        "in": boolean, if true the record has additional information
        "ex": string, the exception stack trace
        "hip": string, the host IP
        "cip": string, the client IP
        "dn": long, the duration in milliseconds
        "dt": string, the start date+time in milliseconds
    },
] 

Example:

[
    {
        "id": 12177,
        "an": "connections",
        "md": "editConnection",
        "ur": "admin",
        "tn": null,
        "rt": true,
        "in": false,
        "ex": null,
        "hip": "localhost:8080",
        "cip": "0:0:0:0:0:0:0:1",
        "dn": 7,
        "dt": "1522160520345"
    },
    {
        "id": 12176,
        "an": "flows",
        "md": "runFlow",
        "ur": "admin",
        "tn": null,
        "rt": false,
        "in": true,
        "ex": "com.toolsverse.etl.exception.EtlException: Attempted read from closed stream.",
        "hip": "localhost:8080",
        "cip": "0:0:0:0:0:0:0:1",
        "dn": 215086,
        "dt": "1522160093479"
    }    
]    

RESPONSE CODES: 200 for success, 401 and 403 for not authorized, and 500 for an internal error.

Download the log file for a given flow ID and audit ID

Etlworks Integrator creates a unique log file for each executed flow. Use this API endpoint to download the log file.

PATH: /plugins/dashboard/rest/v1/flows/console/download?flowId={flowId}&auditId={auditId}

EXAMPLE: https://app.etlworks.com//plugins/dashboard/rest/v1/flows/console/download?flowId=123&auditId=45

METHOD: GET

HEADER: Authorization:Bearer access-token

flow id

audit id

REQUEST BODY: none

RESPONSE: file - Content-Disposition: attachment

RESPONSE CODES: 200 for success, 401 and 403 for not authorized, and 500 for an internal error.

User-defined APIs

Integrator can be used as an Enterprise Service Bus, serving requests from third-party applications. Basically you can build an API, without writing a single line of code, which can be used by other applications to send payload to Integrator's API endpoints.

Below is a typical example of an event-driven flow:

  1. The third-party application updates its own database.
  2. The update triggers an event, which sends a notification to Integrator and includes updated record(s) as part of the payload.
  3. There is an HTTP listener, configured in Integrator, which listens to the incoming HTTP requests.
  4. There is also a flow, which has the listener as the source (FROM) connection. The flow is scheduled and ready to receive a payload.
  5. Once the payload is received, Integrator automatically executes the flow and passes the payload as a source.
  6. The source data is transformed and placed into the destination. For example, information about an updated record(s) is sent to a data warehouse.

Read more about event-driven flows.

A user-defined API allows the developer to create custom endpoints, which can receive payloads from third-party applications, triggering execution of Integrator's flows.

PATH: /plugins/schedules/rest/v1/httplistener/{user-defined-path}.

EXAMPLE: https://app.etlworks.com/plugins/schedules/rest/v1/httplistener/hl7/patient, where /hl7/patient is a user-defined-path, defined when creating http listener.

METHOD: POST.

HEADER: Authorization:Bearer access-token.

REQUEST BODY: a payload in any of the supported formats, such as CSV, XML or JSON.

REQUEST CONTENT TYPE: not required.

RESPONSE: a response defined by the developer, typically a JSON document.

RESPONSE CODES: 200 for success, 401 and 403 for not authorized, and 500 for an internal error.

Expose any data source as an API endpoint

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 (From) is any supported data source and the Destination (To) is the HTTP listener created in Step 1, with 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 the event-driven flow.