Python ETL for Observepoint Folders into PostgreSQL Database
Create the table: CREATE TABLE public.folders ( appsrunning INTEGER, appsinqueue INTEGER, fname text COLLATE pg_catalog."default", apprulefailures INTEGER, createdbyusername text COLLATE …
PostgreSQL to join Array column
This is the PostgreSQL to pull all the Rules and associated Journey information for a single journeyid. Learned how to join an array column (actions.rules) from Gary Seiling’s website. SELECT …
Development Tools
JSON Formatter: https://jsonformatter.curiousconcept.com/ This makes our JSON data retrieved from APIs super easy to read. Observepoint Javascript Regex Tester: http://regex.observepoint.com/ Helpful to test Javascript regex
Python Script to Pull JSON from API and Insert into DB
This script is used to populate the jctions table with the journeyids from the journeys table that we just loaded into the PostgreSQL Database. def loadActions(c): print(’:readActions() start’) cur = …
ETL for ObservePoint API Journey Rules
Create the table in our PostgreSQL database: CREATE TABLE public.rules ( fromtemplate BOOLEAN, rulename text COLLATE pg_catalog."default", checktimes INTEGER, accountid INTEGER, …
Insert JSON data into PostgreSQL DB using Python
Now that the table has been created and we have inspected the JSON data returned by the Observepoint API we are finally ready to load the data. First up are …