HTTP (web services) connector

When to use this connector

In Etlworks, you can create a connector to practically any HTTP-based web service (API). It is typically assumed that the web service is REST-based, but you can also connect to SOAP, GraphQL and proprietary web services.

Read how to work with web services in Etlworks.

Create a Connection

Start creating a connector to the web service by selecting the APIs group in the gallery, then selecting one of the following Connection Types: HTTP, Google APIs, Microsoft APIs, Etlworks APIs.

Available connectors

HTTP

A generic HTTP connector. This connector can be used to access any HTTP-based API. 

Google APIs

A version of the generic HTTP connector, which supports OAuth2 authentication for Google services and APIs. Otherwise, it is identical to the generic HTTP connector. This connector can be used to access any API that supports or requires interactive OAuth2 authentication with Google authentication infrastructure. Read how to connect to Google APIs.

Microsoft APIs

A version of the generic HTTP connector, which supports OAuth2 authentication for Microsoft services and APIs, also custom APIs deployed to Azure cloud. Otherwise, it is identical to the generic HTTP connector. This connector can be used to access any API that supports or requires interactive OAuth2 authentication with Microsoft authentication infrastructure available through the Azure cloud. Read how to connect to Microsoft APIs. 

Etlworks APIs

A version of the generic HTTP connector, which supports authentication for Etlworks build-in and user-defined APIs. Otherwise, it is identical to the generic HTTP connector. Read how to connect to Etlworks APIs.

Connection parameters

Common parameters

Here's a detailed description of each Connection parameter available:

• URL: the endpoint URL. You can use {tokens} as part of the URL. The tokens will be automatically substituted during runtime on global variable values. Example:

• https://host/rest/v1/lead/36938.json?access_token={access_token}
  

• Method: the HTTP method which will be used for that particular request. If Default is selected, the following methods will be used: GET for getting data from the web service, POST for pushing data to the web service, and DELETE for deleting data.
• Content Type Header: HTTP request content-type header.
• Headers: optional HTTP headers.
• Payload: actual payload for the POST or PUT request. Typically, the payload is automatically provided when Flow transforms it from the source to the destination, but you can also hard-code the payload. You can use {tokens} as a part of the payload. The tokens will be automatically substituted during runtime on global variable values.
• File Name: an optional file name, which contains a payload for the POST or PUT request. Typically, the payload is automatically provided when the Flow transforms the source to the destination, but you can also specify a name for the file which contains the payload.
• Maximum Asynchronous HTTP Calls: the maximum number of asynchronous HTTP calls for this connection. By default, HTTP calls are performed synchronously, but you can enable async calls by setting this property to a value greater than zero. Only the POST, PUT, and DELETE methods are supported. The global maximum number of asynchronous calls is 100.
• Stream payload to POST and PUT endpoints: if this option is selected and the POST or PUT HTTP request contains a payload, the payload will be streamed to the endpoint. Streaming reduces memory footprint when submitting data and is overall much faster, so it is enabled by default. In some rare cases, streaming can cause an HTTP connection to hang, hence the option to disable streaming.
• Timeout: the request timeout in milliseconds. The default value is 1 hour. if the request is taking longer than configured or the default value, it will be automatically aborted.
• Success Codes: a comma-separated list of HTTP codes which, when returned, are considered to be "success" codes. For example: 200,201. If this field is blank, the default "success" codes will be used for each particular request type (GET, POST, PUT, DELETE, HEADER).
• Exceptions from Error Codes: a comma-separated list of the HTTP codes which, when returned, are considered to be exceptions from the standard error codes. Use it when you still want to parse a response from the service even after it has already returned one of the standard error codes (4xxx, 5xx, etc.).
• Response File Name: the name of the file to dump the response from the request. If empty, the system will not create a response file.
• Save Response only on Error: if this option is selected, the response file will be created only if the HTTP request is executed with an error.
• Add Response to Exception Log: if this option is selected, and the HTTP request is executed with an exception, the response content will be added to the exception log.
• Add URL to Debug Log: if this option is selected, the actual URL of the request will be added to the debug log.  
• Read how to debug HTTP requests.
• Save HTTP Code into Global Variable: If this option is enabled, the HTTP code returned by the endpoint will be saved to the global variable http_processor_last_http_code. Read how to get access to this variable from JavaScript.
• Save Response Headers into Global Variable: if this option is enabled, the headers in HTTP response returned by the endpoint will be saved to the global variable http_processor_last_response_headers, which can be accessed using com.toolsverse.config.SystemConfig.instance().getContextProperties().get('http_processor_last_response_headers').
• Request Body Encoding: optional character encoding for the payload (request). Use it when you wish to re-encode the payload (for example, to UT8). No encoding means there will be no additional encoding.
• Encoding: optional character encoding for the response. Use it when you wish to re-encode the response (for example, to UT8). No encoding means there will be no additional encoding.
• Number of Retries: number of retries if the HTTP call was not successful. The maximum allowed number of retries is 10. The default is 0.
• Delay between retries: delay in milliseconds between retries if the HTTP call was not successful. The maximum allowed delay is 5 minutes (300000 milliseconds). The default is 0.

Parameters used for authentication (generic HTTP connector)

• If authentication is not required you can skip this section.
• Authentication: select basic or basic preemptive for username/password authentication, token or oauth2 for OAUTH2 two-step authentication, oauth1 for one-step OAUTH1 authentication, aws for AWS Signature authentication, header for authentication when the Authorization header parameter is directly provided, and none when there is no authentication at all. Learn more about configuring authentication for HTTP Connection.
• User or AccessKey: the username or access key.
• Password or SecretKey: the password or secret key.
• Authentication URL: the endpoint URL for the authentication request. The endpoint must return an access token. Use it when the token or oauth2 Authentication type is selected. You can use {refresh_token} as part of the URL. Example:

• https://host/oauth2/token?grant_type=refresh_token&refresh_token={refresh_token}  
  

• HTTP Method for Token and OAuth2 Authentication: the HTTP method is used for obtaining an access token. Use it when thetoken or oauth2 Authentication type is selected.
• Access Token Attribute: when the authentication endpoint is called, it typically returns an access token as a part of the response in the JSON Format. Use this field to specify the name of the attribute in JSON, which contains an access token. For example: access_token. Use it when the token or oauth2 Authentication type is selected.
• Access Token Prefix: prefix used together with the authentication token header parameter. The default is Bearer.
• Authentication Request Payload: use this field to define a payload for the POST or PUT authentication request. Use it when the token or oauth2 Authentication type is selected and the HTTP Method for Token Authentication is POST. The following tokens are accessible as a part of the payload: {refresh_token}, {user}, {password}. Example:

• {"login":"{user}","password":"{password}"}
  

• Authentication Request Content Type: the default is application/json. Use it when the token or oauth2 Authentication type is selected and the HTTP Method for Token Authentication is POST.
• Authentication Request Headers: HTTP headers for the authentication request.
• Refresh Token: an optional refresh token that can be used to obtain an access token. See the usage example above. Use it when the token or oauth2 Authentication type is selected. You can use tokens such as {user}, {password}, and others. Use single quotes: 'value' instead of double quotes: "value" (all single quotes will be substituted on double quotes).
• AWS Region: an optional AWS Region used together with the aws authentication. The default value is us-east-1.
• Service Name: an optional AWS Service Name used together with the aws authentication.
• Access Token: an optional access token used together with the oauth1 authentication.
• Access Secret: an optional access secret used together with the oauth1 authentication.
• Authentication Header Name: the name of the authentication header parameter. The default is Authorization. it is used only when the token, oauth2, or header authentication type is selected.
• Content of the Authorization header: this parameter is required when the header authentication type is selected.

Configure authentication for HTTP Connection

The HTTP connector supports the following types of authentication:

• OAuth2 authentication for Microsoft services and APIs
• OAuth2 authentication for Google services and APIs
• OAuth2 authentication for Etlworks built-in and user-defined APIs 
• Basic Authentication
• Basic Preemptive Authentication
• Basic Authentication with additional parameters
• API Key Authentication
• Two-step token-based authentication (OAuth2)
• One-step oauth authentication (OAuth1)
• AWS authentication (AWS Signature)
• Authentication when the authorization header parameter is user-defined
• SOAP-style Authentication

OAuth2 authentication for Microsoft services and APIs

1. Select Microsoft APIs connector.

2. Select or enter the required Permissions. You can select or enter more than one permission. If you cannot find the required permission in a provided list, you can update the list by entering new permissions. 

3. Click Sign in with Microsoft to generate an OAuth token. The connector will retrieve the refresh token from the Microsoft authentication endpoint, which will be used at runtime to generate the access token. 

OAuth2 authentication for Google services and APIs

1. Select Google APIs connector.

2. Enter required OAuth2 Scopes. You can enter more than one scope as a space or column delimited list. 

3. Click Sign in with Googleto generate an OAuth token. The connector will retrieve the refresh token from the Google authentication endpoint which will be used at runtime to generate the access token. 

OAuth2 authentication for Etlworks built-in and user-defined APIs

1. Select Etlworks APIs connector.

2. Select or enter Endpoint path, optional Base URL, and Method:

If you selected Endpoint path from the dropdown keep the defaultMethod. The connector automatically sets the method for standard Etlworks APIs.

Read more about Etlwork's built-in and user-defined APIs.  Etlworks APIs have a Base URL to which the endpoint paths are appended, for example, https://app.etlworks.com. If Base URLis not entered, the connector will use the default URL defined under Settings->General->Home URL.

3. Enter the User and Password for the Etlworks account used for authentication. Note that different built-in APIs require different permissions. 

4. If Two-Factor Authentication is enabled for the Etlworks account, enter 2FA Secret. To get the secret, if you have already enabled the 2FA for your account, go to About->Two-Factor Authentication->Add new device->Enter password->view secret. To get the secret, if you are in the process of enabling the 2FA for the account, go to About->Two-Factor Authentication->Enable->view secret.

5. Enter the optional Tenant ID in the Tenant field when you need to use a super admin account to execute the API endpoint on behalf of the tenant. For example, when a flow exists under the tenant, and you want to trigger this flow by a super admin user. To get the Tenant ID switch to the main account,  navigate to Users->Tenants, select the tenant to view the tenant ID in the address bar.

Using API Key with Etlworks API connector

When using API Key authentication, the username, password, 2FA Secret, and tenant settings are disregarded. Please note, there is no automatic tenant switch. To execute the API for a specific tenant, ensure that the user associated with the API Key is explicitly assigned to that tenant.

Basic Authentication

1. When configuring an HTTP Connection, select basic as the value for the Authentication field.
2. Enter the username and password into the User and Password fields.

Basic Preemptive Authentication

1. When configuring an HTTP Connection, select basic preemptive as the value for the Authentication field.
2. Enter the username and password into the User and Password fields.

Basic Authentication with additional parameters

In some cases, additional parameters (besides username and password) are required to connect to a web service.

1. When configuring an HTTP Connection, select none as the value for the Authentication field.
2. Make sure that POST or PUT is selected as the value for the Method field.
3. Enter additional parameters in the JSON Format into the Payload field. Use the tokens {user} and {password} as substitutes for the username and password. For example: {"username":"{user}","password":"{password}","labName":"Demo"}.

API Key Authentication

1. When configuring an HTTP Connection, select none as the value for the Authentication field.
2. When configuring the URL, use the token {password} as a substitute for the actual API key. For example: https://api.com/v1/metrics/timeseries?apiKey={password}.
3. Enter an API key into the Password field.

Two-step token-based authentication (OAuth2)

1. Select token or oauth2 as the value for the Authentication field.
2. Enter the API endpoint used to receive an access token in the Authentication URL field. For example: https://777-XWC-041.mktorest.com/identity/oauth/token?grant_type=client_credentials&client_id={user}&client_secret={password}
3. Select a value for the field HTTP Method for Token Authentication. Typically, it is POST.
4. Optionally define Authentication Request Headers.
5. Enter a value for the Access Token Attribute field. When the authentication endpoint is called, it typically returns an access token as part of a response in the JSON Format. Use this field to specify the name of the attribute in JSON which contains an access token. For  example: access_token. If the response for the authentication (token) endpoint is a nested JSON and the access token is a nested JSON node, for example, {"success":true, "result":{"accessToken":"AA-BB-CC-DD", "expires":1234567}} use the owner.node notation to get the value of the token. Example: result.accessToken.
6. Enter a value for the Access Token Prefix field. A prefix is used for the authentication token header parameter. The default is Bearer. If none (actual word none) is entered, the system assumes that there is no prefix.
7. Enter an optional authentication payload into the Authentication Request Payload field. The following tokens are accessible as part of the payload: {refresh_token}, {user}, {password}. For example: {"login":"{user}","password":"{password}"}
8. Select a value for the Authentication Request Content Type field. The default is application/json.
9. Optionally enter the value in the Authentication Header Name field. The default value is Authorization.
10. Enter client credentials into User and Password fields.
11. You can user optional parameters Private Key and Password for Private Key when configuring OAuth2 authentication using pre-processor. The following connection properties can be accessed by name:

var keyFileName properties.getProperty('any.keyfilename');

var keyPassword properties.getProperty('any.password');

One-step oauth authentication (OAuth1)

1. Select oauth1 as the value for the Authentication field.
2. Enter consumer key into the User field.
3. Enterconsumer secret into the Password field.
4. Enter access token into the Access Token field.
5. Enter access secret into the Access Secret field.

AWS authentication (AWS Signature)

1. Select aws as the value for the Authentication field.
2. Enter the AccessKey and AccessSecret into the User and Password fields.
3. Enter optional AWS Region into the AWS region field. The default region is us-east-1.
4. Enter optional Service Name into the Service Name field. If Service Name is not entered it will be extracted from the URL. For example, for the URL https://iam.amazonaws.com/?Action=GetUser the service name is iam.

Authentication when the authorization header parameter is user-defined

1. Select header as the value for the Authentication field.
2. Change the value for the optional Authentication Header Name field. The default value is Authorization.
3. Enter the required authorization header parameter into the Content of the Authorization header field. You can use tokens such as {user}, {password} and others. Use single quotes: 'value' instead of double quotes: "value" (always use single quotes in place of double quotes).
4. Enter an optional username into the User field.
5. Enter an optional password into the Password field.

SOAP-style Authentication

When working with a SOAP web service, you are typically dealing with a payload that must be electronically signed in some fashion. There is no single standard way of doing this, so we will merely explain a basic approach.

Step 1. Let's assume that the payload (SOAP envelope) looks something like this (below):

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:ns1="http://www.vendor.com/soapendpoint/" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <SOAP-ENV:Header>
    <ns1:AuthenticationHeader>
      <vendorUserId>{requestUserId}</mktowsUserId>
      <vendorSignature>{requestSignature}</requestSignature>
      <vendorTimestamp>{requestTimestamp}</requestTimestamp>
    </ns1:AuthenticationHeader>
  </SOAP-ENV:Header>
  <SOAP-ENV:Body>
    <ns1:paramsGetMultipleCars>
      <leadSelector xsi:type="ns1:KeySelector">
        <keyType>MAKE</keyType>
        <keyValues>
          <stringItem>Ford</stringItem>
          <stringItem>Acura</stringItem>
        </keyValues>
      </leadSelector>
      <batchSize>100</batchSize>
    </ns1:paramsGetMultipleCars>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Step 2. Notice the tokens {requestUserId},{requestSignature} and {requestTimestamp}. They are part of the electronic signature.

Step 3. We will be using JavaScript to set the values of {requestUserId},{requestSignature} and {requestTimestamp}.

3.1 Create a Flow that works with a SOAP web service.

3.2 Create a JavaScript Flow, which will be used to set tokens.

Below is an example of the JavaScript code:

var javaImports = new JavaImporter(java.text, java.util, javax.crypto, 
javax.crypto.spec, org.apache.commons.codec.binary, com.toolsverse.config); 

with (javaImports) {

var props = SystemConfig.instance().getProperties();

var vendorUserId = "user";
var vendorSecretKey = "password";

var df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssZ");
var text = df.format(new java.util.Date());
var requestTimestamp = text.substring(0, 22) + ":" + text.substring(22);           
var encryptString = requestTimestamp + vendorUserId;

var secretKey = new SecretKeySpec(new java.lang.String(vendorSecretKey).getBytes(), "HmacSHA1");
var mac = Mac.getInstance("HmacSHA1");
mac.init(secretKey);
var rawHmac = mac.doFinal(new java.lang.String(encryptString).getBytes());
var hexChars = Hex.encodeHex(rawHmac);
var signature = new java.lang.String(hexChars); 

props.put('requestSignature', signature);
props.put('requestTimestamp', requestTimestamp);
props.put('requestUserId', vendorUserId);
}

Notice props.put(...) at the end of the JavaScript code. This is where the actual values of the tokens are set: {requestUserId},{requestSignature} and {requestTimestamp}.

3.3 Create a nested Flow, which includes the Flows created in steps 3.1 and 3.2.

Using self-signed SSL certificates

To allow an SSL handshake with the HTTP endpoint protected by self-signed or expired SSL certificates, enable the parameter Trust self-signed and expired certificates. 

Use client-side SSL certificate with HTTP Connection

Etlworks HTTP connector supports mutual authentication, which allows the user to present a certificate as well as the server. 

To configure HTTP Connection with the client-side SSL certificate (assuming that you already have a certificate):

Step 1. Use the field Client SSL Certificate to upload the certificate. The following Formats are supported: jks, pfx, p12, p11.

Step 2. Select SSL certificate type in the Client SSL Certificate Type field.

Step 3. Enter Passphrase in the Client SSL Password field.

Set allowed TLS version(s)

By default HTTP connector sets TLS (Transport Layer Security) version to 1.2 but it can automatically fallback to TLS 1.1 or even TLS 1.0 if it allowed by the host operating system. In some cases the third-party API might require an older version of TLS or enforces TLS 1.2. 

To set the allowed version(s) of TLS navigate to SSL section and use parameter Allowed TLS versions to set the versions. You can chose more than one version. 

Configure auto-retry when working with web services

To configure auto-retries, use the Number of retries field when configuring the HTTP Connection.

Available parameters

• Number of Retries: the number of retries allowed if the HTTP call was not successful. The maximum allowed number of retries is 10. The default is 0.
• Delay between retries: delay in milliseconds between retries if the HTTP call was not successful. The maximum allowed delay is 5 minutes (300,000 milliseconds). The default is 0.

Configure anti-throttling parameters

Most public and private APIs implement various throttling algorithms, such as rate-limit throttling, to prevent APIs from being overwhelmed by too many requests.

In Etlworks, you can set the artificial delay after the specified number of calls, which will reduce the rate of calls to the given API endpoint.

Available parameters

• Wait after N calls (ms): if this value is greater than 0, the system will pause and wait for the configured number of milliseconds (less or equal to 10 minutes) before making another call to this endpoint. The endpoint is derived from the URI authority (https://myapi.com/data -> myapi.com). You can override the endpoint.
• Number of calls: this property works together with Wait after N calls (ms) and defines the total number of calls to this endpoint the system can make before pausing for the configurable number of milliseconds.
• Endpoint: by default, the endpoint is derived from the URI authority (https://myapi.com/data -> myapi.com). Use this property to override the endpoint.

Use Preprocessor to programmatically change the parameters of the HTTP Connection

It is possible to programmatically change the parameters of the HTTP Connection using a program in JavaScript. This program can be entered in the Preprocessor field available for the HTTP Connection.

The most common use case is when you need digitally sign the HTTP request before sending it to the server, which is quite typical for SOAP-based APIs or for APIs that require a complicated authentication protocol.

Scripting language for Preprocessor

JavaScript

Available variables for Preprocessor

Possible return values for Preprocessor

If valued returned by JavaScript is instanceof com.toolsverse.etl.common.Alias, then:

• new request URL will be set to alias.getUrl()
• new user will be set to alias.getUserId()
• new password will be set to alias.getPassword()
• new parameters will be set to alias.getParams()

Example of returning alias object with a modified URL 

var alias = new Alias();

alias.setUrl('https://app.etlworks.com/' + url);

value = alias;

Example of using a preprocessor to digitally sign a request for CyberSource API

CyberSource API reference

var javaImports = new JavaImporter(com.toolsverse.config, 
javax.crypto.Mac, javax.crypto.spec.SecretKeySpec, java.util.Base64); 

with (javaImports) {

var props = SystemConfig.instance().getProperties();

var merchantId = properties.getProperty("header_v-c-merchant-id");
var host = properties.getProperty("header_host");

var reportName = props.get("CS_REPORT_NAME");
if (!reportName) {
    reportName = properties.getProperty("header_X-Report-Name");
}

var reportDate = props.get("CS_REPORT_DATE");
if (!reportDate) {
    reportDate = properties.getProperty("header_X-Report-Date");
    if (!reportDate) {
       reportDate = new Date(Date.now()).toISOString().split('T')[0];
    }
}

var date = new Date(Date.now()).toUTCString();
var resource = '/reporting/v3/report-downloads?organizationId=' 
+ merchantId + '&reportDate=' + reportDate + '&reportName=' + reportName;

var signatureString = 'host: ' + host + '\ndate: ' 
+ date + '\n(request-target): get ' + resource + '\nv-c-merchant-id: ' + merchantId;

var secretKey = new javax.crypto.spec.SecretKeySpec(java.util.Base64.getDecoder().
decode(password), "HmacSHA256");
var aKeyId = javax.crypto.Mac.getInstance("HmacSHA256");
aKeyId.init(secretKey);
aKeyId.update(signatureString.getBytes());
var aHeaders = aKeyId.doFinal();
var signature = java.util.Base64.getEncoder().encodeToString(aHeaders);

var signatureHeader = 'keyid="' + user + 
'", algorithm="HmacSHA256", headers="host date (request-target) v-c-merchant-id", signature="' 
+ signature + '"';

props.put("cs_auth_date", date);
props.put("cs_auth_signature", signatureHeader);
props.put("cs_url", 'https://' + host + resource);
}

Use Postprocessor to programmatically change the response content

Some HTTP endpoints return the content in the response. It is possible to change the content using Postprocessor.

Scripting language for Postprocessor

JavaScript

Available variables for Postprocessor

Return values for Postprocessor

• value = content - the new response content
• value = response - the original unmodified response content 
• value = null - empty response content

Multi-part upload

Multipart requests combine one or more sets of data into a single body, separated by boundaries. You typically use these requests for file uploads and for transferring data of several types in a single request (for example, a file along with a JSON object).

Configure connector for multi-part upload

Step 1. Set Content Type Header to multipart/form-data.

If the endpoint requires setting the boundaries (typically when it is implemented in PHP) when Content Type Header is set you can either skip step 1 or set the Content Type Header header manually:

Step 2. Set File Name property using the following format: key=value&key=value. If the key points to a file to upload set the value in the format {app.data}/filename. A file with a name filename in {app.data} must exist. Note that it doesn't have to be in {app.data}. Any location accessible by Etlworks will work.