Sparkify wants to analyze the data they've been collecting on songs and user activity on their new music streaming app. The analysis team is particularly interested in understanding what songs users are listening to. Currently, there is no easy way to query the data to generate the results, since the data reside in a directory of CSV files on user activity on the app.
They'd like a data engineer to create an Apache Cassandra database which can create queries on song play data to answer the questions, and wish to bring you on the project. Your role is to create a database for this analysis. You'll be able to test your database by running queries given to you by the analytics team from Sparkify to create the results.
You will create an Apache Cassandra database which will host data collected on songs and user activity on Sparkify's new music streaming app.
- python
- Apache Cassandra
You will work with one dataset: event_data/
. The directory of CSV files is partitioned by date. Here are examples of filepaths to two files in the dataset:
event_data/2018-11-08-events.csv
event_data/2018-11-09-events.csv
Each CSV file contains event data from users for a specific day. The CSV is comprised of the following fields:
Field | Description |
---|---|
artist | artist name |
auth | tracks whether the user logged in or logged out |
firstName | user first name |
gender | user gender |
itemInSession | number of items for a specific session |
lastName | user last name |
length | length of session/event |
level | tracks whether the user paid for the session or if the session was free |
location | user location |
method | HTTP methods |
page | tracks the page name such as 'NextSong', 'Home', 'Logout', 'Settings', 'Downgrade', 'Login', 'Help', 'Error', 'Upgrade' |
registration | registration timestamp |
sessionId | session id |
song | song name |
status | tracks the status of the request such as 200, 307, 404 |
ts | timestamp in millisecond |
userId | user id |
Note:
Prior to populating the tables, you will merge all the CSV data under event_datafile_new.csv
. This merged data includes
Clone this repository
git clone https://github.com/najuzilu/DM-ApacheCassandra.git
- conda
- python 3.8
- cassandra-driver
- pandas
Create a virtual environment through Anaconda using
conda env create --file environment.yml
- Run
create_tables.py
to create the tablespython create_tables.py
- Run
etl.py
to execute the ETL pipeline and load the data in the databasepython etl_tables.py
- Run
stylized_facts.py
to make sure that all the tables have been populated successfully.python stylized_facts.py
Yuna Luzi - @najuzilu
Distributed under the MIT License. See LICENSE
for more information.