Client Background

Client: A Leading Marketing Tech Firm in Australia

Industry Type: Marketing

Services: Marketing Solutions

Organization Size: 50+

Project Objective

To make a software code that takes data from a source and ingests it into a database present on a server. The scripts should automatically execute after regular intervals of time. 

Project Description

The client had several data sources that were updated with new data regularly. The client wanted software that triggers itself automatically and takes data from those data sources and ingests it into a database that is hosted on a Linode server. Also, the date parameters in the query should be changed dynamically using the current date. Further, we had to assist in setting up the Tableau BI tool on the client’s PC and connect the Postgres database to the tableau. 

Our Solution

  1. We setup a linux server on linode.
  2. Install Postgres on this linux server.
  3. Create a database and create a new user. Grant this new user all privileges on the database.
  4. Create a table within the database. This table has columns with datatypes as specified by the client.
  5. Write a python script that makes GET request to the client specified data source and store the response in json format.
  6. Inside the python script itself, establish  a connection to our postgres database using the pscopg2 module and user credentials.
  7. Ingest the data into postgres using INSERT query in python script.
  8. Write code to get the today’s date using the datetime module. Using this, calculate yesterday’s date. Now we can use these as parameters inside our query to the data source.
  9. Move these python files to our server.
  10. Install and setup Cron on our server. 
  11. Add the task to run specified python files at regular intervals to Cron.
  12. Repeat steps 4 to 11 for every new data source.

Project Deliverables

  1. Python Script
  2. Working linode server with cron installed
  3. Tableau installation and connection to postgres
  4. Project Documentation

Tools used

Linode server

VS Code 

Language/techniques used

Python 

Bash

PSQL.

Skills used

Python programming

Postgres SQL 

Linux scripting

Databases used

Postgres

Web Cloud Servers used

Linode

What are the technical Challenges Faced during Project Execution

  1. Avoiding duplicates was a challenge. 
  2. Since Client was living in Australia all the timezone (on server and in code) were changed to AEDT. 

How the Technical Challenges were Solved

  1. Used uniqueid Column to check for duplicates. 
  2. Used pytz module to change timezones.

Business Impact

This solution helps in maintaining a copy of all data sources inside our Postgres database. Also, the data is 24/7 available. Since data inside the Postgres is updated regularly, graphs in the tableau are also up to date.

Project Snapshots

Project website url

https://github.com/X360pro/Web-connector-for-tableu

RELATED ARTICLESMORE FROM AUTHOR