The Sparkfy

Sparkify is a music streming startup with many daily listeners. Sparkfy's databases grew over time and the need arose to move this data to a cloud environment. So the goal of this project is to move their process and data to this new enviroment.

AWS Cloud

To build this architecture will be used the AWS cloud. The services used will be:

• IAM
• Redshift
• EC2
• S3

Sparkfy S3 to Redshift ETL

The main task of this project is build a ETL pepiline that extracts their data from S3, stages them in Redshift, and transforms data into a set of dimensional tables for their analytics team to continue finding insights into what songs their users are listening to. The following image show the system architecture for AWS S3 to Redshift ETL:

Datasets

Their data resides in S3, in a directory of JSON logs on user activity on the app, as well as a directory with JSON metadata on the songs in their app. There are two datasets :

• Song data: s3://udacity-dend/song_data
• Log data: s3://udacity-dend/log_data

To properly read log data s3://udacity-dend/log_data, it is need following metadata file: Log metadata: s3://udacity-dend/log_json_path.json

Song Dataset

The first dataset is a subset of real data from the Million Song Dataset(opens in a new tab). Each file is in JSON format and contains metadata about a song and the artist of that song. The files are partitioned by the first three letters of each song's track ID. For example, here are file paths to two files in this dataset.

            song_data/A/B/C/TRABCEI128F424C983.json
            song_data/A/A/B/TRAABJL12903CDCF1A.json

And below is an example of what a single song file, TRAABJL12903CDCF1A.json, looks like:

{
"num_songs": 1,
"artist_id": "ARJIE2Y1187B994AB7",
"artist_latitude": null, 
"artist_longitude": null,
"artist_location": "", 
"artist_name": "Line Renaud", 
"song_id": "SOUPIRU12A6D4FA1E1", 
"title": "Der Kleine Dompfaff",
"duration": 152.92036, 
"year": 0
}    

Log Dataset

The second dataset consists of log files in JSON format generated by this event simulator(opens in a new tab) based on the songs in the dataset above. These simulate app activity logs from an imaginary music streaming app based on configuration settings.

The log files in the dataset you'll be working with are partitioned by year and month. For example, here are file paths to two files in this dataset.

        log_data/2018/11/2018-11-12-events.json
        log_data/2018/11/2018-11-13-events.json

And below is an example of what the data in a log file, 2018-11-12-events.json, looks like:

Log JSON Metadata

The log_json_path.json file is used when loading JSON data into Redshift. It specifies the structure of the JSON data so that Redshift can properly parse and load it into the staging tables.

In the context of this project, you will need the log_json_path.json file in the COPY command, which is responsible for loading the log data from S3 into the staging tables in Redshift. The log_json_path.json file tells Redshift how to interpret the JSON data and extract the relevant fields. This is essential for further processing and transforming the data into the desired analytics tables. Below is what data is in log_json_path.json.

Data Warehouse

The schema type used for this build this datawarehouse is the star schema.This includes the following tables:

Fact Table

• songplays - records in event data associated with song plays i.e. records with page NextSong:
  • songplay_id, start_time, user_id, level, song_id, artist_id, session_id, location, user_agent

Dimension Tables

• users - users in the app

  • user_id, first_name, last_name, gender, level

• songs - songs in music database

  • song_id, title, artist_id, year, duration

• artists - artists in music database

  • artist_id, name, location, latitude, longitude

• time - timestamps of records in songplays broken down into specific units

  • start_time, hour, day, week, month, year, weekday

Project Template

In this project there are five scripts:

• cluster.py is where creater the cluster with IAM credencias and call the Redshfit client.

• cluster_manager.py is where manager deploy cluster.

• create_table.py is where will be create your fact and dimension tables for the star schema in Redshift.

• etl.py is where will be load data from S3 into staging tables on Redshift and then process that data into your analytics tables on Redshift.

• sql_queries.py is where will be define you SQL statements, which will be imported into the two other files above.

ETL Pipeline

Extraction

The Extract step is done extract data of S3 source and copy data in stage tables. See bellow the stages tables:

Total rows stage_events:8056 rows

Total rows stage_songs:385252 rows

Stage Tables

Stages Tables are tables created to store raw data. The main purpose this tables is use this raw data to make data transform . After the data being tranformed are inserted in analytical tables (final tables)

Transformation

The data transformation applied were:

• Handling null values: Where null values were replaced with default values
• Remove duplicates:Where rows with the same data were removed leaving just one
• Timestamp Normalization: Where the timestamp was transformed into understandable information

Load

Ater the transformation stage is done all transformed data was inserted into analysis tables (final table)

Quick Start

Pre requistes

• Python 3.x installed
• Create a new IAM user in your AWS account
• Give it AdministratorAccess, From Attach existing policies directly Tab
• Take note of the access key and secret
• Edit the file dwh.cfg in the same folder as this notebook and fill:

[AWS] KEY= YOUR_AWS_KEY SECRET= YOUR_AWS_SECRET

How to run

# create a virtual environment
python3 -m venv env

#activate virtual environment
source env/bin/activate

#install minimal prerequisites
pip3 install -r requirements.txt

#create and setup cluster
python3 cluster_manager.py

#create stages tables
#create final table
python3 create_tables.py

# makes extract from S3 to load stages tables
# makes transform  on stages table dados
# insert data transformed in final tables
python3 etl.py

**NOTES**:

• Execute each script one by one

• The cluster_manager.py and create_tables.py are runned fast.

• The load in staging tables (etl.py):

  • it takes a while to load all the tables
  • the load data in stage_songs takes a while approximately 42 minutes to load all the data into the table

Data Analysis

The analysis_data.ipynb contains some analysis about the songs players like:

1- Song most played

select s.title as song_name,COUNT(sp.song_id) as most_played 
FROM songplays sp
JOIN songs s on sp.song_id=s.song_id
GROUP BY(s.title)
ORDER  BY most_played DESC
limit 1;

2-Song song least played

select s.title as song_name,COUNT(sp.song_id) as least_played 
FROM songplays sp
JOIN songs s on sp.song_id=s.song_id
GROUP BY(s.title)
ORDER  BY least_played ASC
limit 1;

   

3-Song most played on 2018

SELECT s.title AS song_name, COUNT(sp.song_id) AS  most_played, t.year
FROM songplays sp
JOIN songs s ON sp.song_id = s.song_id
JOIN time t ON sp.start_time = t.start_time
WHERE t.year = 2018
GROUP BY s.title, t.year
ORDER BY  most_played  DESC
LIMIT 1;
   

Delete Cluster

After make analysis or test the features of Redshift delete the cluster. There are two ways todo that:

• Delete cluster by AWS console: More information go to official documentation

• Delete cluster using python code in analysis_data.ipynb