A startup called Sparkify wants to analyze the data they've been collecting on songs and user activity on their new music streaming app. An analytics team is particularly interested in understanding what songs users are listening to. Currently, they don't have an easy way to query their data, which resides in a directory of JSON logs on user activity on the app, as well as a directory with JSON metadata on the songs in their app.

This is where the data engineer comes in. His task here is to create a Postgres database and an ETL pipeline, which make this data easier to access and optimize the queries for song play analysis.

The following applies in this project:

• to create a fact table and dimension tables in the form of a star schema
• to implement an ETL pipeline that transfers the data from the files in the folder /data into the tables to Postgres

To implement the project you will need the following things:

• Python
• PostgreSQL
• Jupyter

When importing two modules I ran into problems that I want to briefly explain here:

• the module psycopg2 that is used in the notebook etl.ipynb

  • I had to install psycopg2-binary

  pip install psycopg2-binary

• the module sql that is used in the notebook test.ipynb

  • I had to install ipython-sql

  pip install ipython-sql

The first dataset is a subset of real data from the Million Song Dataset. Each file is in JSON format and contains metadata about a song and the artist of that song. The files are partitioned by the first three letters of each song's track ID. For example, here are filepaths to two files in this dataset.

data/song_data/A/B/C/TRABCEI128F424C983.json
data/song_data/A/A/B/TRAABJL12903CDCF1A.json

And below is an example of what a single song file, TRAABJL12903CDCF1A.json, looks like.

{"num_songs": 1, "artist_id": "ARJIE2Y1187B994AB7", "artist_latitude": null, "artist_longitude": null, "artist_location": "", "artist_name": "Line Renaud", "song_id": "SOUPIRU12A6D4FA1E1", "title": "Der Kleine Dompfaff", "duration": 152.92036, "year": 0}

The second dataset consists of log files in JSON format generated by this event simulator based on the songs in the dataset above. These simulate activity logs from a music streaming app based on specified configurations.

The log files in the dataset you'll be working with are partitioned by year and month. For example, here are filepaths to two files in this dataset.

data/log_data/2018/11/2018-11-12-events.json
data/log_data/2018/11/2018-11-13-events.json

And below is an example of what the data in a log file, 2018-11-12-events.json, looks like.

During the data modeling with the focus on normalization, the following tables were created, which are structured in a star schema

• the fact table songplays
• the four dimension tables users, artists, time, songs

Here you have a look at the Entity Relationship Diagram

The ETL pipeline consists of two main functions that are implemented in etl.py

• process_song_file

  • Here the data from the respective song file is processed and transferred to the database

• process_log_file

  • Here the data from the respective log file is processed and transferred to the database

To generate the tables in the database run:

python create_tables.py

And then to run the ETL-Pipeline run the following script:

python etl.py

The data is now clearly structured and the analytics team now has an easy way to query the data.
Thus, the startup Sparkify now has a good basis to analyze the data from their new music streaming app.