Deivid Robim Linkedin
A startup called 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.
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.
They'd like a data engineer to create a Postgres database with tables designed to optimize queries on song play analysis. The task is to create a star schema for Postgres and develop an ETL pipeline which will transfer the data from local files to the database.
Data-Modeling-with-Postgresql
│ README.md # Project description
│ requirements.txt # Python packages required to execute scripts
└───data # The datasets
| |
│ └───log_data
│ | │ ...
| └───song_data
│ │ ...
│
└───src # Source code
│ | etl.ipynb # ETL helper notebook
| | test.ipynb # Psql queries notebook
│ │ create_tables.py # Schema creation script
| | etl.py # ETL script
| | sql_queries.py # Definition of all sql queries
- Python3
- Song dataset: It's 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
{
"num_songs":1,
"artist_id":"ARD7TVE1187B99BFB1",
"artist_latitude":null,
"artist_longitude":null,
"artist_location":"California - LA",
"artist_name":"Casual",
"song_id":"SOMZWCG12A8C13C480",
"title":"I Didn't Mean To",
"duration":218.93179,
"year":0
}
- Log dataset: It 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.
{
"artist":null,
"auth":"Logged In",
"firstName":"Walter",
"gender":"M",
"itemInSession":0,
"lastName":"Frye",
"length":null,
"level":"free",
"location":"San Francisco-Oakland-Hayward, CA",
"method":"GET",
"page":"Home",
"registration":1540919166796.0,
"sessionId":38,
"song":null,
"status":200,
"ts":1541105830796,
"userAgent":"\"Mozilla\/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/36.0.1985.143 Safari\/537.36\"",
"userId":"39"
}
Using the song and log datasets, you'll need to create a star schema optimized for queries on song play analysis.
This includes the following tables.
• songplays - records in log data associated with song plays i.e. records with page NextSong
table schema: songplay_id, start_time, user_id, level, song_id, artist_id, session_id, location, user_agent
• users - users in the app
table schema: user_id, first_name, last_name, gender, level
• songs - songs in music database
table schema: song_id, title, artist_id, year, duration
• artists - artists in music database
table schema: artist_id, name, location, latitude, longitude
• time - timestamps of records in songplays broken down into specific units
table schema: start_time, hour, day, week, month, year, weekday
Clone repository to local machine
git clone https://github.com/drobim-data-engineering/Data-Modeling-with-Postgresql.git
Change directory to local repository
cd Data-Modeling-with-Postgresql
Create Python Virtual Environment
python3 -m venv venv # create virtualenv
source venv/bin/activate # activate virtualenv
pip install -r requirements.txt # install requirements
Run scripts
cd src/
python -m create_tables.py # create schema
python -m etl.py # load data
Check results
jupyter notebook # launch jupyter notebook app
The notebook interface will appear in a new browser window or tab.
Navigate to src/validation.ipynb and run the code cells