Datalakes with Spark

Data Lakes using spark EMR and s3.

Project Introduction:

For the 4th project of the udacity Data Engineering NanoDegree I developed a data lake for the music streaming company Sparkify. 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.

Project Description

As their data engineer, I was 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.
The database and ETL pipeline were tested by running queries given to me by the analytics team from Sparkify and compared my results with their expected results.

Project Steps:

Create Table Schemas:

Create Spark session
Read the song data from s3 and process the data in order to create the songs and artist tables. Once the processing is finished the tables are then re-uploaded to S3 as parquet files.
Read the song data from s3 and process the data in order to create the users, times and songplays tables. Once the processing is finished the tables are then re-uploaded to S3 as parquet files.

Fact Table

songplays - records in log 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, lattitude, longitude time - timestamps of records in songplays broken down into specific units
start_time, hour, day, week, month, year, weekday

Project files:

etl.py reads data from S3, processes that data using Spark, and writes them back to S3
dl.cfg config file, which contains the AWS configuration details. This file is not contained in the repository as they are my credentials.

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

Log Dataset

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 you'll be working with are partitioned by year and month. For example, here are filepaths to two files in this dataset.

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

aabid0193/spark_datalake_etl