Load data from the Million Song Dataset into a final dimensional model stored in S3.

A fictional music streaming startup, Sparkify, has grown their user base and song database and want to move their processes and data onto the cloud. 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. The files in this repository will assist in loading the data into a final dimensional model stored in Amazon S3.

Data is extracted from AWS S3 JSON files and inserted into the dimensional model shown below.

• The songplays parquet file is partitioned by year and month
• The time parquet file is partitioned by year and month
• The songs parquet file is partitioned by year and artist_id

• dimensional_model.er
  • Entity-relationship file used to generate ER diagram images. See https://github.com/kaishuu0123/vscode-erd
• etl.py
  • Create parquet files for dimensional model & store to Amazon S3
• run_etl.py
  • Optional: create AWS EMR cluster and run etl.py
• install-requirements.sh
  • Bash script to install necessary python3 packages to run etl.py
• dl.cfg
  • Config file for all necessary variables for AWS EMR

Clone this repository:

git clone https://github.com/rigganni/AWS-Spark-Million-Song-ETL

Set up the following variables in dl.cfg:

Ensure variables are set up in dl.cfg as described in installation above.

Optional: Create EMR cluster with run_etl.py and run etl.py. After etl.py completes cluster terminates.

python run_etl.py

Or run etl.py on existing AWS EMR cluster:

ssh your-AWS-EMR-cluster "time python3 " < ./etl.py >/tmp/results.txt 2>&1

Spark output is quite verbose. It is recommended to pipe both stderr and stdout to a file on your local computer. If output is streamed to your terminal session it can consume a large amount of system memory.

If running etl.py on already set up EMR cluster ensure that if you have installed the necessary requirements first:

ssh your-AWS-EMR-cluster < ./install-requirements.sh