Getting 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. The integrator 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 it is 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. NOTE: Etlworks includes native Salesforce JDBC connector but you can use an HTTP connector if the object or API you are looking for is not provided by the native connector.
• Connecting to JIRA API (with pagination)
• Connecting to Microsoft Graph API
• Connecting to Marketo REST API. NOTE: Etlworks includes native premium Marketo JDBC connector but you can use an HTTP connector if the object or API you are looking for is not provided by the native connector.

Native connectors for business applications and APIs

Eltworks includes the following native connectors for the 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 than150 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 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 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, 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 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 a 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 dynamically configure connections and transformations based on input parameters. 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 a 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 very large files