Get started with web services

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 HTTP Connection Type from the gallery. Read more	ETL with web services Extract, transform, and load data when web service is a source or destination. Read more	Bulk operations with web services Copy response to a file, send the file as a payload, and more. Read more
Working with paginated APIs Etlworks provides flexible options to integrate and work with paginated APIs using various pagination methods, such as offset-based, cursor-based, token-based, based on presence of data and time-based pagination Read more	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. Read more	Use memory Connection to work with the response from a web service This is particularly useful in two common scenarios: 1. Retrieving the unmodified response from the web service and saving it to a global variable. 2. Parsing the response and performing actions based on the content, such as throwing an exception conditionally. Read more

 

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 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

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

Tips and tricks when working with web services Read more

 

Related case study

TriNet case study Load data from complex third-party APIs into Snowflake, with zero-maintenance pipelines	TriNet, a leading provider of HR solutions for small and medium-sized businesses, leverages Etlworks to simplify the integration of data from highly complex third-party APIs into Snowflake. By utilizing Etlworks’ robust capabilities, TriNet implemented zero-maintenance pipelines that run autonomously, ensuring reliability and scalability for years without manual intervention.
RecBi case study Multistep workflows for APIs, Salesforce, MySQL, email systems, and Google Cloud services.	RecBi, a leading provider of business intelligence solutions, uses Etlworks to power complex data integration workflows across APIs, Salesforce, MySQL databases, email systems, Google Cloud services, file-based sources, and more. With multi-step pipelines capable of processing data for multiple customers in a single run, Etlworks delivers the flexibility and stability RecBi needs to stay agile and responsive to client demands.
Marketo case study Extract data from Marketo via APIs for large enterprise clients.	Marketo, a global leader in marketing automation, partnered with Etlworks to deliver custom data integration solutions for high-profile clients, including Bank of New Zealand, Sage, CPA, and more. Using Etlworks, Marketo extracted leads, opportunities, and custom objects from their API connector to build robust, zero-maintenance solutions that “just work.” Etlworks provided exceptional support, enabling Marketo to deliver enterprise-scale solutions quickly and efficiently.
More case studies

 

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, 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:

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

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

Bulk operations

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.

• Read about the parametrization of the Connection

Tips and tricks when working with web services

Here are some tips and tricks when working with web services and APIs.