Summary

This project builds an ETL pipeline that extracts data from Amazon S3 and transforms data into a set of dimensional tables.

The project is written in python and uses Amazon s3 for file storage.

The project template includes three files:

1. etl.py reads data from S3, processes that data using Spark, and writes them back to S3
2. dl.cfgcontains your AWS credentials
3. README.md provides discussion on your process and decisions

Source Data

The source data is in log files given the Amazon S3 bucket.

• Song data: s3://udacity-dend/song_data
• Log data: s3://udacity-dend/log_data
• Log data json path: s3://udacity-dend/log_json_path.json

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.

Log files contains songplay events of the users in json format while song_data contains list of songs details.

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.

Database Schema

Using the song and event datasets, a star schema is created and optimized for queries on song play analysis. Following are the fact and dimension tables made for this project:

Fact Table:

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

Dimension Tables:

• users - users in the app
  • columns: user_id, first_name, last_name, gender, level
• songs - songs in music database
  • columns: song_id, title, artist_id, year, duration
• artists - artists in music database
  • columns: artist_id, name, location, lattitude, longitude
• time - timestamps of records in songplays broken down into specific units
  • columns: start_time, hour, day, week, month, year, weekday

Run Directions:

• Update the dl.cfg file

  • AWS_ACCESS_KEY_ID
  • AWS_SECRET_ACCESS_KEY

• Run python etl.py.

  • This will start pipeline which will read the data from files and populate the tables.