This project uses psql and python to retrieve data from the SQL news database.
Here Virtual Box + vagrant were used.
run $ vagrant up to set up vagrant then $ vagrant ssh to log in to the Linux VM.
Move the news.sql and logsAnalysis.py files to your vagrant directory.
Two views were created to query this database easier, to create these views run $ psql news inside your vagrant terminal to operate on the database, then type the create view commands below:
This view strips the path column from the Log table of the path and only leaves the article’s slug from that log. It is used to join articles on logs using the slug.
to create this view:
CREATE VIEW articleLogs as
SELECT replace(path, '/article/', '')
FROM log;
This view displays each day, the number of successful logs and number of failed logs for each day. It is used to calculate the percentage of failed logs for each day.
to create this view:
CREATE VIEW dayStatus as
SELECT time::timestamp::date as day,
count(case status when '200 OK' then 1 else null end) as success,
count(case status when '404 NOT FOUND' then 1 else null end) as faliure
FROM log
GROUP BY day;
To run this program, inside your VM run $ vagrant ssh cd to /vagrant, where your news.sql and logsAnalysis.py files should exist, and type $ python logsAnalysis.py to run.