Data Modeling with Cassandra

About The Project

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.

Description

You will create an Apache Cassandra database which will host data collected on songs and user activity on Sparkify's new music streaming app.

Tools

• python
• Apache Cassandra

Datasets

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

Getting Started

Clone this repository

git clone https://github.com/najuzilu/DM-ApacheCassandra.git

Prerequisites

• conda
• python 3.8
• cassandra-driver
• pandas

Create a virtual environment through Anaconda using

conda env create --file environment.yml

Project Steps

1. Run create_tables.py to create the tables

   python create_tables.py

2. Run etl.py to execute the ETL pipeline and load the data in the database

   python etl_tables.py

3. Run stylized_facts.py to make sure that all the tables have been populated successfully.

   python stylized_facts.py

Authors

Yuna Luzi - @najuzilu

License

Distributed under the MIT License. See LICENSE for more information.