/DM-ApacheCassandra

Data Modeling with Apache Cassandra

Primary LanguagePythonMIT LicenseMIT

Language Contributors Stargazers Forks Issues MIT License LinkedIn


Logo

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

image_event_datafile_new

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.