Get started with web services

In the 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.

What can you do with web services in Etlworks Integrator

ETL with web services Extract, transform, and load data when web service is a source or destination. Read more

Extract data from business application or well-known API and load into database This Flow extracts data from a well-known business application or API, such as Google Analytics or Salesforce, transforms and loads it into the database. Read more

Bulk operations with web services Copy response to a file, send the file as a payload, and more. Read more

 

Videos

How to extract data from web service and load into database Create a source HTTP Connection. Create a destination Connection for the relational database. Create a Format for the payload returned by the web service. Link Connection and Format Learn how to flatten the nested dataset. Create a new Web Service to database Flow. Read the full article

 

Related resources

HTTP (web services) connector Start creating a connector to the web service by selecting the HTTP Connection Type from the gallery. Read more	Cannot connect to a web service This has a list of the common errors why you cannot connect to a web service and the solution for each. Read more
How to use memory Connection to parse the response from the web service We will create a flow, which executes other flows by name, reads the response, and throws an exception if the flow called by name was executed with an error Read more	Working with paginated APIs Create a Flow Extract data from the Web Service and load into the database”  where the source is a web service and the destination is a temporary database. Read more

 

Related case study

SaaS startup Load data from JIRA into the data warehouse Read the case study

"The actual Flow developed by the team extracts and loads various types of information"

 

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 the 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. The 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. The 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

The Etlworks Integrator includes the following native connectors for business applications and well-known APIs. Here are some (but not all) of the native connectors:

• 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 the 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 the Etlworks 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 the Etlworks Integrator, a web service can be a source or a destination in the ETL transformation.

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

When the web service is a destination, the 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 the 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 
• Call HTTP endpoint, optionally send a 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