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
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 the Etlworks Integrator, it's possible to create a custom connector to practically any REST or SOAP API using a generic HTTP connector. |
|
Videos
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
|
Related resources
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
SaaS startup Load data from JIRA into the data warehouse |
"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 SaaS apps and services
The Etlworks Integrator includes hundreds of native connectors for SaaS apps and services. 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:
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
Comments
0 comments
Please sign in to leave a comment.