How to extract data from SaaS application or well-known API and load into database

Overview

In the Etlworks Integrator, it is possible to connect to practically any REST or SOAP API using a generic HTTP connector. In this article, we will be focusing on native connectors that provide quick point-to-point connectivity for APIs.

In the Etlwotks Integrator, there are two types of native connectors for APIs and services:

• Connectors for well-known APIs
• Connectors for SaaS apps and services

Connectors for well-known APIs

Here are some of the available connectors for well-known APIs. 

• Google Analytics
• Google Sheets
• Google AdWords
• Facebook
• Twitter

Create Flow where the source is a well-known API such as Google Analytics

In Connections

Access Connections here.

Step 1. Create a source Connection for a well-known API.

Step 2. Create a destination Connection for the relational database and test it.

In Flows

Access Flows here.

Step 3. Create a new Well-known API to database Flow.

Step 4. Add a new transformation. After which, select FROM Connection and endpoint and TO Connection and table.

Step 5. Click MAPPING and configure the Source SQL if needed (optional).

Step 6. Test the transformation.

Step 7. Add per-field Mapping if needed.

Step 8. Save the Flow and execute it manually.

In Schedules

Step 9. Schedule the Flow to be executed periodically.

Connectors for SaaS apps and services

Here are some of the available connectors for SaaS apps. 

• Salesforce
• HubSpot
• Premium connectors

Create a Flow where the source is SaaS app or service

In Connections

Step 1. Create a source Connection for a well-known API with a JDBC connector. Test it.

You will find them in the Database section. 

Step 2. Create a destination Connection for the relational database and test it.

In Flows

Step 3. Create a new Database to database Flow.

Step 4. Add a new transformation and select FROM Connection and object and TO Connection and table.

Step 5. Click MAPPING and configure a Source SQL if needed.

Step 6. Add per-field Mapping if needed.

Step 7. Test the transformation.

Step 8. Configure MERGE (UPSERT) if needed.

Step 9. Configure Change Replication if needed.

Step 10. Save the Flow and execute it manually.

In Schedules

Step 11. Schedule the Flow to be executed periodically.