Get started with web services

In Etlworks Integrator, it is possible to extract, transform and load data from and into practicably any REST-based or SOAP-based web service. This also includes a growing number of native connectors to the most commonly used business applications and APIs.

Connectors

Generic HTTP connector

• Read how to create a Connection to any web service.
• Read how to configure authentication.

Examples of the custom connectors built using generic HTTP connector

In Etlworks Integrator, it's possible to create a custom connector to practically any REST or SOAP API using a generic HTTP connector. Below are some of the examples:

• Connecting to Salesforce API using an HTTP connector. Etlworks Integrator includes a native Salesforce JDBC connector, but you can use an HTTP connector if the native connector does not provide the object or API you are looking for.
• Connecting to JIRA API (with pagination).
• Connecting to Microsoft Graph API.
• Connecting to Marketo REST API. Etlworks Integrator includes a native premium Marketo JDBC connector, but you can use an HTTP connector if the native connector does not provide the object or API you are looking for.

Native connectors for business applications and APIs

Etlworks Integrator includes the following native connectors for business applications and well-known APIs.

• Salesforce
• Marketo
• HubSpot
• Smartsheet
• Magento
• OData
• Google Analytics 
• Google Sheets 
• Google AdWords
• Google BigQuery
• Apache Hive
• Amazon MWS Feeds API 
• Amazon Marketplace
• Social networks 
• Elasticsearch
• Amazon Athena
• Premium connectors (more than 150 business applications)

Data exchange formats

When working with web services, it is often required to create a payload or parse a response, which can be encoded using one of the following data exchange Formats: 

• CSV 
• Fixed Length Text 
• JSON 
• JSON dataset 
• XML 
• XML dataset 
• Avro
• Parquet
• Key-Value
• HL7 2.x 
• HL7 FHIR 

Testing and exploring API endpoints

Use the Etlworks Explorer to test the Connection to a web service and explore API endpoints.

Step 1. Create an HTTP Connection to the web service.

Step 2. Create a Format for the response. The most commonly used Formats are JSON, XML, and CSV.

Step 3. Open Etlworks Explorer, select the Connection created in Step 1 and link it to the Format created in Step 2. Read more about how to link the Format to a Connection in Explorer.

Step 4. Explore the metadata (endpoints and fields), view data in a grid, and a raw Format, query data, and discover dependencies using SQL.

ETL with web services

In Etlworks Integrator, a web service can be a source or a destination in the ETL transformation.

When the web service is a source, Etlworks Integrator reads the response for the HTTP call, transforms it, and loads it into the destination.

When the web service is a destination, Etlworks Integrator creates a payload by transforming the source and sends it to the destination HTTP endpoint.

Below are some of the most common ETL examples for web services:

• Extract data from the web service, transform and load it into the database
• Extract data from the business application using native connector, transform and load it into the database
• Create an ARRAY of rows from all records in a table and send it as a payload in a single HTTP call

Bulk operations

In Etlworks Integrator, it is not required to use a source to destination transformation to send a payload to the API endpoint or to read a response.

Below are some of the examples:

• Copy response from the HTTP request to a file 
• Send the content of the file as a payload for an HTTP endpoint 
• Create a payload from each record in a table and send it to the HTTP endpoint, one record at a time 
• Modify URL parameters using data in each record in a table and make an HTTP call, one record at a time 
• Send user-defined payload to an HTTP endpoint

Parametrization and dynamic URLs

Parameterization is a way to configure Connections and transformations based on input parameters dynamically. The input parameters can be global variables or Flow variables.

• Read about the parametrization of the Connection
• Read about dynamic URLs

Tips and tricks when working with web services

• Capture the response code and response headers from the last HTTP call
• Copy response from the HTTP request to a file
• Configuring auto-retry when working with web services 
• Send the content of the file as a payload for an HTTP endpoint
• Working with paginated APIs 
• Creating a payload for HTTP call using a template with tokens
• How to use a memory Connection to parse the response from the web service
• Debugging HTTP requests
• Using templates to define fields 
• Handling field names with special characters 
• Handling columns with a blank name 
• Working with duplicated column names 
• Calculating field value 
• Working with huge files