Sparkify Song Data Analysis

Problem Statement

Sparkify is an online new music streaming app. They have collected user activity info and want to do analysis on what songs the users are listening to by doing analytical search on the data.As of now the information is store in JSON files and other metadata files and there is no easy way to search. The ta

As their data engineer tasked with building an ETL pipeline that extracts their data from S3, processes them using Spark, and loads the data back into S3 as a set of dimensional tables. This will allow their analytics team to continue finding insights in what songs their users are listening to.

Solution

The solution that is designed to solve the above problem is to build a ETL pipeline that extracts data from S3, processes them using Spark, and loads the data back into S3 as a set of dimensional tables. This will allow analytics team to continue finding insights in what songs their users are listening to.

Software

• AWS
• Python 3.6 with panda,spark,pyspark library
• Jupyter Notebooks

Programming languages

• SQL
• Python
• JSON

DataSet

• Song Dataset The first dataset is 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.

• Log Dataset The dataset consists of log files in JSON format generated by this event simulator based on the songs in the dataset above. These simulate app activity logs from a music streaming app based on specified configurations.

Pre-requisites

1. Create an AWS user with programmer access and attach policy for S3 Read and Write

How to run from Udacity Workspace 👇

1. Run Run From Udacity.ipynb Jupyter notebook

How to run from local

1. Download the files
2. Modify dl.cfg with your AWS KEY and ID
3. Run etl.py

A Quick 🏃 of the flow 👇

1. Database is designed in Star Schema with Fact and Dimensions table.
2. Program is designed in a way to first import the data into songs, artist, user, time in parquet files and then from these table do the search query to fill the fact songplays parquet files.
3. The Source files AWS path are kept in configuration
4. For importing and reading json for song records df = spark.read.json(song_data)
5. For importing and reading json object per line for log jsons df = spark.read.json(log_data)
6. After massaging the data into pyspark dataframes from song and log json files, data is written into parquet files