The scope of the project is to create an analytics database for a music application. The source data are stored in a bunch of JSON files. We process them and extract useful information about users, songs, artists, users' listing information. Next, push extracted data to the database for further analysis.
We have used virtualenv and pip for setting up our Python environment. Setting up the Python environment is fairly easy with virtualenv. You just need to run the following commands to set up the environment.
- Create a new virtual environment:
virtualenv -p python3.8 venv - Activate it:
source ./venv/bin/activate - Install dependencites:
pip install -r requirements.txt
Next, execute two following commands to create your database schema and insert data into the fact and dimensional tables.
- To create database schema:
python create_tables.py - To load the data into tables:
python etl.py
We employed a two-stage approach to building our extract, transform, load (ETL) pipeline:
- First, we used a Jupyter notebook (
etl.ipynb) to create functions that we need for our ETL process. - Next, we transferred that functions to a Python file (
etl.py).
Figure 1 shows the start scheme use used for the project. In our database scheme, we have one fact table (songplays) and four dimensional tables (users, songs, artists, time).
We are going to run a lot of analytical queries against our data stored in the database. We have selected star schema because it is the most appropriate database schema for our analytical queries.
Figure 1: The Database Schema
