AmetricX

Data Onboarding – transformer builder

Enable your customers and partners to upload CSV/XLS files and

transform them to match your internal data schemas.

Follow this Open Source project -> GitHub

Demo
Share CSV Files for FREE

Step by step guide to integrate data from a REST API into a Postgres table with Retool workflows. This guide takes you step by step connecting to the API endpoint, transforming the data and inserting results into a Postgres database running in Retool. Watch the video below for all the details.

AmetricX to Postgres with Retool Workflow

Integrate Metrics and KPIs with internal systems

AmetricX provides an API for direct access to metrics from a third party application. Retool offer a powerful Workflow builder to help you keep your metrics synchronized with an external application.

Table of contents

Photo by Tobias Fischer on Unsplash

Create target Postgres table

Navigate to Retool’s Query Library and initiate the creation of a new query within the Retool Database, designating the table name as “supply_inventory.” This procedural step establishes the framework for the efficient retrieval and manipulation of data within the specified database table, enhancing your capacity to interact with and manage supply inventory data seamlessly.

CREATE TABLE IF NOT EXISTS supply_inventory
(
    metric_name character varying COLLATE pg_catalog."default" NOT NULL,
    "timestamp" timestamp with time zone NOT NULL DEFAULT now(),
    category CHARACTER VARYING,
    sku CHARACTER VARYING,
    status CHARACTER VARYING,
    metric_numeric_value double precision,
    updated_at timestamp with time zone NOT NULL,
    CONSTRAINT metrics_data_pkey PRIMARY KEY (metric_name, "timestamp")
)

Define Connection to your REST API endpoint

Under Resources in Retool click on Create new->Resource->REST API

Enter the base URL for your API server.


We employ AmetricX for our REST call operations, and this example is adaptable to any REST API that yields a list. Choose the endpoint that aligns with the AmetricX environment, and for more detailed information, refer to the documentation on Endpoints.

Create a new Retool Workflow

Click on Workflow->Create New->Workflow

Define a new workflow to handle retrieving and transforming the metric’s data before updating your target table.

Define the Retool Workflow

Components:

  • REST API Query
  • Column name transformation
  • Insert into Postgres

REST API Call

This is the call to your REST API endpoint. Define any parameters or payload attributes here

Column Transformation Code

Add a Code block to your Retool workflow. This code block is in Python . It separates the column metric_name into three new columns.

new_list = []
for record in query1.data:
  category,sku,status = record['metric_name'].split(":")
  record['category'] = category
  record['sku'] = sku
  record['status'] = status
  new_list.append(record)

return new_list

Write to DB

Add a Resource Query element to your Retool workflow.

Select Retool Database from the Resource list and the table supply_inventory from the Table List. The action type is Bulk insert Records. The data comes from the Code Block (code1.data_

Home » Retool workflow: ultimate step-by-step guide. From REST to Postgres