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

Overview

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

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

In 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 the HTTP connector.

In Connections

Step 1. Create source connection for well-known API.

Step 2. Create destination connection for the relational database. Test it.

In Flows

Step 3. Create a new well-known API to database flow.

Step 4. Add a new transformation and select from connection and endpoint and to connection and table.

Step 5. Click the MAPPING button 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 the well-known API with JDBC connector. Test it.

you will find them in the Database section. 

Step 2. Create a destination connection for the relational database. Test it.

In Flows

Step 3. Create a new database to database flow.

Step 4. Add the new transformation and select from connection, and object and to connection and table.

Step 5. Click the MAPPING button 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.