Note: this project was completed as a part of my coursework for my Nanodegree in Data Engineering on the online learning portal Udacity. The problem setup and datasets were provided by Udacity.
A startup called Sparkify wants to streamline data analysis and collecting on songs and user activity on their new music streaming app. A priority is understanding which songs users are listening to and when, and to allow for efficient querying of a database that currently exists in the form of JSON logs of user activity and the song library's metadata.
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.
song_data/A/B/C/TRABCEI128F424C983.json
song_data/A/A/B/TRAABJL12903CDCF1A.json
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 are partitioned by year and month. For example, here are filepaths to two files in this dataset.
log_data/2018/11/2018-11-12-events.json
log_data/2018/11/2018-11-13-events.json
To optimize queries for songplay analysis, the database is set up with a star schema, a fact table of all songplay instances, and dimension tables of users, songs, artists, and times (Primary keys are noted in italics) :
- songplays - records in log data associated with song plays i.e. records with page NextSong
- songplay_id, start_time, user_id, level, song_id, artist_id, session_id, location, user_agent
- users - users in the app
- user_id, first_name, last_name, gender, level
- songs - songs in music database
- song_id, title, artist_id, year, duration
- artists - artists in music database
- artist_id, name, location, latitude, longitude
- time - timestamps of records in songplays broken down into specific units
- start_time , hour, day, week, month, year, weekday
A star schema setup allows for simplified queries and fast aggregations, with the fact table songplays containing play instances and all the primary keys for the remaining dimension tables, with further detail.
Analyst must run create_tables.py followed by etl.py. Accessing sparkifydb will allow user to load songplays fact table, as well as users, songs, artists, and time dimension tables. e.g. If user is searching for the user IDs of users listening to the song "I Didn't Mean To", a sample query would read:
("""SELECT songplays.user_id
FROM songplays
INNER JOIN songs ON songplays.song_id = songs.song_id
WHERE songs.title=%s;""")
where the title "I Didn't Mean To" is passed to the query.