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_listWrite 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_
