Skip to content

Latest commit

 

History

History
76 lines (60 loc) · 3.61 KB

README.md

File metadata and controls

76 lines (60 loc) · 3.61 KB
Raw

Data Modeling with Apache Cassandra

Purpose

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 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.

Dataset

There is one dataset called event_data which is in a directory of CSV files partitioned by date.

Queries

For NoSQL databases, we design the schema based on the queries we know we want to perform.

We want to ask 3 question of our data:

  1. Give me the artist, song title and song's length in the music app history that was heard during sessionId = 338, and itemInSession = 4
    • SELECT artist, song, length from table_1 WHERE sessionId=338 AND itemInSession=4
  2. Give me only the following: name of artist, song (sorted by itemInSession) and user (first and last name) for userid = 10, sessionid = 182.
    • SELECT artist, song, firstName, lastName FROM table_2 WHERE userId=10 and sessionId=182
  3. Give me every user name (first and last) in my music app history who listened to the song 'All Hands Against His Own'
    • SELECT firstName, lastName FROM table_3 WHERE song='All Hands Against His Own'

Answers to the Questions

Question 1

Give me the artist, song title and song's length in the music app history that was heard during sessionId = 338, and itemInSession = 4

SELECT artist, song, length 
from session_items
WHERE sessionId=338 AND itemInSession=4;
artist song length
0 Faithless Music Matters (Mark Knight Dub) 495.30731201171875

Question 2

Give me only the following: name of artist, song (sorted by itemInSession) and user (first and last name) for userid = 10, sessionid = 182

SELECT artist, song, firstName, lastName
FROM session_users 
WHERE userId=10 and sessionId=182;
artist song firstName lastName
0 Down To The Bone Keep On Keepin' On Sylvie Cruz
1 Three Drives Greece 2000 Sylvie Cruz
2 Sebastien Tellier Kilometer Sylvie Cruz
3 Lonnie Gordon Catch You Baby (Steve Pitron & Max Sanna Radio Edit) Sylvie Cruz

Question 3

Give me every user name (first and last) in my music app history who listened to the song 'All Hands Against His Own'.

SELECT firstName, lastName 
FROM song_users
WHERE song='All Hands Against His Own';
firstName lastName
0 Jacqueline Lynch
1 Tegan Levine
2 Sara Johnson