According to our data model we will need five tables for our ETL project.
Let’s line up the table field names with their data types. Some of the JSON returned from the API is in list form and it is stored in the database in list form – check out the emails and actions fields below.
The PostgreSQL to create the Journeys table:
CREATE TABLE public.journeys
(
jname character varying(4000) COLLATE pg_catalog.”default”,
lastcheck timestamp without time zone,
queued boolean,
folderid integer,
webjourneyrunning boolean,
journeyid integer NOT NULL,
status character varying(4000) COLLATE pg_catalog.”default”,
screenshot text COLLATE pg_catalog.”default”,
createdat timestamp without time zone,
domainid integer,
nextcheck timestamp without time zone,
userid character varying(4000) COLLATE pg_catalog.”default”,
emails text[] COLLATE pg_catalog.”default”,
journeyoptions json,
actions json[],
CONSTRAINT journeyidpk PRIMARY KEY (journeyid)
)
All posts in this ETL series:
Install PostgreSQL Database
Simple Data Model
Create Tables in PostgreSQL Database
Getting JSON API data with Python
Inserting JSON data into Postgres with Python