Parsing Repeated Data into Pivotable Format in Excel
Sometimes you gain access to structured data but it isn’t a straightforward Copy and Transpose job to get the data into a useful format for analysis or database ingestion. To …
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 …
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 …
Get Observepoint API Data with Python
Here we make a call to the Observepoint API and inspect the results before loading into the PostgreSQL database. It is also translated into JSON. The python code to grab …
Create tables in PostgreSQL
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 …
Create Database in PostgreSQL
Starting with the simple data model we will create the necessary database and users in the postgreSQL database. The PGAdmin client (https://www.pgadmin.org/) is a nice GUI for the database that …
Simple Data Model
Today we are working with Version2 of Observepoint’s API. The database tables will be created from this model. Here’s the data model created for this project. Observepoint is an Automated …
Install PostgreSQL Database – Windows
Installing PostgreSQL DB on Windows 10 Download: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads Install: Skipped installing additional components with stackbuilder: All posts in this ETL series: Install PostgreSQL Database Simple Data Model Create Tables in …