A music streaming startup, Sparkify, has grown their user base and song database even more and want to move their data warehouse to a data lake. 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.

• This project aimed at establishing an ETL pipeline to extract song and log data hosted in an S3 bucket " s3a://udacity-dend ".
• Then Spark is used to process the data via pyspark into different parquet formatted fact and dimensional tables ( star schema ) that will be loaded back into s3,
• This should improve the performance and support provided to the analysts' team to glean insights on the listeners' behavior, songs performance, and a lot more.

Sparkify provided two datasets that reside in S3. Here are the S3 links for each:

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

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. The files are partitioned by the first three letters of each song's track ID. For example, here are filepaths 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}

The second 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 an imaginary music streaming app based on configuration settings. The log files in the dataset are partitioned by year and month.

• Sample Data:

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.

• The Extraction step is straight forward with pyspark
• The transformation is needed to fix some issues with the data like:
  • The timestamp is originally in uniz format that shoudl be coverted to a readable timestamp format from which the year, month, day, hour entries can be extracted.
  • The step of fixing the timestamp is involved in the creation of the time dimension table and the songplays fact table.
  • Removing duplicates has been conducted as well as part of the transformation.

OLAP queries on the songplays fact table are straightforward, and additional fields can be easily accessed in the four dimension tables users, songs, artists, and time. A star schema is a robust choice for this use case since it's streamlined through denormalization and simple queries are valid to get the required results.

1. songplays - records in event data associated with song plays i.e. records with page NextSong

• Fields songplay_id, timestamp, user_id, level, song_id, artist_id, session_id, location, user_agent, month, year

2. users - users in the app

• Fields userId, firstName, lastName, gender, level

3. songs - songs in music database

• Fields song_id, title, artist_id, artist_name, year, duration

4. artists - artists in music database

• Fields artist_id, name, location, lattitude, longitude

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

• Fields datetime, Hour, DOW, DOM, DOY, week, Month, year,

This is our main engine, by running it: * The song and log data are loaded from the s3 bucket, * Processed and transformed to create the fact and dimension tables of the star schema and * Finally, the tables are written into parquet files and uploaded to another s3 bucket.

This is to try and test the scirpt locally before implemnting it on an EMR context.

This file holds the aws credentials.

In the terminal run the following commands

$ unzip data/log_data.zip -d song_data
$ unzip data/song_data.zip -d events
$ mkdir output_data

$ pip install -r requirements.txt

1. Set environment variables AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY.
2. Run ETL pipeline : python etl.py

1. Create an AWS account
2. Create an EMR cluster
3. Create a S3 bucket to recieve the output data
4. SSH into the Cluster's master node, scp the etl.py file into it and spark-submit etl.py file.