Articles in this section

See more

ETL data from any source to API

  • Updated
Follow

Etlworks can extract data from any source — database, file, API, or SaaS app — and load it into a destination HTTP API. The right technique depends on what payload shape the destination API expects:

  1. Send all records as a JSON array in a single HTTP call. Best when both the source and the payload are relatively flat.
  2. Send one record at a time using a tokenized payload. Best when the API expects a complex or nested payload — use a JSON / XML template with {tokens} per record.
  3. Send one record at a time using a tokenized URL. Best when the API expects parameters in the URL (query string or path) and the parameters or endpoint vary per record.

These can be combined with nested flows and a driving SQL query to send data in exactly the shape the API expects.

Method 1: send all records as a JSON array (single HTTP call)

Use when both the source and the API payload are relatively flat and the API accepts a JSON array of records in one HTTP call.

  1. In Connections, create the source connection.
  2. Create a destination HTTP API connection.

  3. If the API requires authentication, configure it. For OAuth2, see Authentication Methods for HTTP API Connector — OAuth2.
  4. In Formats, create a JSON format with default settings.
  5. In Flows, create a new …to Web Service flow — for example, Database to Web Service.

  6. Add a transformation:
    • FROM — the source connection and table.
    • TO — the HTTP connection, the JSON format, and the API endpoint.

  7. Optional: configure Source SQL under Mapping.
  8. Optional: configure field mapping.
  9. Test the transformation. The expected output is a JSON array: 
    [
   { "id": 1, "first_name": "Joe",  "last_name": "Doe", "dob": "01/01/2000" },
   { "id": 2, "first_name": "Jane", "last_name": "Doe", "dob": "01/01/2001" }
]
  10. If you need a more deeply nested JSON payload, see techniques for working with nested data.

Method 2: send one record at a time using a tokenized payload

Use when the API requires a complex or nested payload. A template with {tokens} builds a custom JSON or XML body for each record.

  1. In Connections, create the source database connection.
  2. Create a destination HTTP API connection.
  3. If the API requires authentication, configure it. For OAuth2, see Authentication Methods for HTTP API Connector — OAuth2.
  4. Create a Call HTTP Endpoint flow.

  5. Use the HTTP connection as the Flow connection.
  6. On the Parameters tab, enter the payload using {tokens}. Example: 
    {
   "source": "test",
   "userId":      {id},
   "firstName":   {first_name},
   "lastName":    {last_name},
   "dateOfBirth": {dob}
}

  7. Create a nested flow.

  8. Add the HTTP-call flow from steps 4–6 to the nested flow and click Configure condition and loop.

  9. Set Loop Type to SQL, select the source connection, and enter the driving SQL.

How does token replacement in the payload work?

When the nested flow runs, it executes the loop SQL, creates a parameter for each column in each returned row, and substitutes those values into {tokens} in the payload template before sending the request.

Method 3: send one record at a time using a tokenized URL

Use when the API expects parameters in the URL — query string or path — and the endpoint or parameter values vary per record.

The setup is almost identical to Method 2. On the destination HTTP connection, use {tokens} in the URL:

https://myapi.com/?id={id}&firstName={first_name}&lastName={last_name}&dob={dob}

How does token replacement in the URL work?

Same as Method 2: the nested flow runs the loop SQL, builds parameters from each returned row, and substitutes them into the URL before the request fires.

Next steps

Related articles