In Etlworks, 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
HTTP (web services) connector
Start creating a connector to the web service by selecting the
ETL with web services
Extract, transform, and load data when web service is a source or destination.
Bulk operations with web services
Copy response to a file, send the file as a payload, and more.
Working with paginated APIs
Create a Flow
Create custom API connectors
In Etlworks, it's possible to create a custom connector to practically any REST or SOAP API using a generic HTTP connector.
Working with APIs in Etlworks
Watch how to create an ETL flow for any HTTP-based API
How to extract data from web service and load into database
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.
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
Related case study
Load data from JIRA into the data warehouse
"The actual Flow developed by the team extracts and loads various types of information"
Generic HTTP connector
Examples of the custom connectors built using generic HTTP connector
In Etlworks, 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 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 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 SaaS apps and services
Etlworks includes hundreds of native connectors for SaaS apps and services. Here are some (but not all) of the native connectors:
- Google Analytics
- Google Sheets
- Google AdWords
- Google BigQuery
- Apache Hive
- Amazon MWS Feeds API
- Amazon Marketplace
- Social networks
- 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:
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.
ETL with web services
When the web service is a source, Etlworks reads the response for the HTTP call, transforms it, and loads it into the destination.
When the web service is a destination, Etlworks 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
In Etlworks, 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.
Tips and tricks when working with web services
Here are some tips and tricks when working with web services and APIs.