Skip to content

Latest commit

 

History

History
60 lines (47 loc) · 2.48 KB

README.md

File metadata and controls

60 lines (47 loc) · 2.48 KB
Raw

Summary


This project builds an ETL pipeline that extracts data from Amazon S3, stages them in Redshift, and transforms data into a set of dimensional tables. This tests your database and ETL pipeline by running queries to compare your results with expected results.

The project is written in python and uses Amazon s3 for file storage and Amazon Redshift for database storage and data warehouse purpose.

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 dwh.cfg file
    • Amazon Redshift cluster credentials
    • IAM role
  • Run python create_tables.py.
    • This will create the database and all the required tables.
  • Run python etl.py.
    • This will start pipeline which will read the data from files and populate the tables.