Purpose:
Build a data warehouse for analytical operations. to allow Sparkify wants to analyze the data they've been collecting on songs and user activity on their new music streaming app. the analytics team is particularly interested in understanding what songs users are listening to.
Benefits
The query will be fast and simplify
The 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.
Design schema
Star schema because it's more effective for handling queries
Fact Table 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 Dimension Tables 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, lattitude, longitude time - timestamps of records in songplays broken down into specific units start_time, hour, day, week, month, year, weekday
sample example from table songplays
songplay_id |start_time |user_id| level| song_id|artist_id |session_id location | user_agent
0 |00:57.8 |73 paid| None| None |954 | Tampa-St. Petersburg-Clearwater, FL |"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2" |
1 |01:30.8 |24 paid| None| None |984 | Lake Havasu City-Kingman, AZ |"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36" |
2 |04:01.8 |24 paid| None| None |984 | Lake Havasu City-Kingman, AZ |"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36" |
3 |04:55.8 |73 paid| None| None |954 | Tampa-St. Petersburg-Clearwater, FL |"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2" |
4 |07:13.8 |24 paid| None| None |984 | Lake Havasu City-Kingman, AZ |"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36" |
5 |10:56.8 |24 paid| None| None |984 | Lake Havasu City-Kingman, AZ |"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36" |
Etl
Python 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.
used Etl to read files json and insert data into tables
Example Query
SELECT u.gender,count(*) FROM songplays s inner join users u on (s.user_id=u.user_id) group by u.gender;
The above query explain how many Male and Female using the application
to start this project folow below step:
1-run create_tables.py to cretate db and create tables. 2-run etl.py to read json files and process data and insert into tables.