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

Overview

This Flow extracts data from a well-known business application or API, such as Google Analytics or Salesforce, transforms and loads it into the database.

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 well-known APIs:

• HTTP-based connectors
• JDBC-based connectors

HTTP-based connectors

• Google Analytics
• Google AdWords
• Google Sheets
• Amazon MWS
• Facebook
• Twitter

Create Flow where the source is a well-known API with an HTTP connector

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.

Native JDBC connectors

• Salesforce
• Elasticsearch
• Amazon Athena
• Google BigQuery
• Apache Hive
• Premium JDBC connectors

Create Flow where the source is a well-known API with the native JDBC connector

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.