This repository loads data from the Million Song Dataset into a star schema. Files are loaded from JSON format and inserted into a Postgres database.

A fictional startup called Sparkify wants to analyze the data they've been collecting on songs and user activity on their new music streaming app. The analytics team is particularly interested in understanding what songs users are listening to. Currently, they don't have an easy way to query their data, which resides 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.

They'd like Postgres database with tables designed to optimize queries on song play analysis. You'll be able to test your database and ETL pipeline by running queries given to you by the analytics team from Sparkify and compare your results with their expected results.

The database will be used for analysis only (OLAP) so utilized a star schema with the following tables:

• Fact Tables
  • songplays
• Dimensions
  • users
  • songs
  • artists
  • time

Using this design will reduce slow joins and return SQL queries to analysts much quicker.

The python module pandas was used to perform JSON data ingestion and transformation. It simplifies the both extracting and trasforming the data with the use of dataframe objects. Loading the data was done by PSQL INSERT queries.

• conda-requirements.yml
  • Anaconda yaml file to create environment with correct Python modules
• create_tables.py
  • Creates or recreates sparkifydb and required objects
• etl.ipynb
  • Jupyter notebook used to develop ETL
• etl.py
  • Used to ETL full dataset in Postgres database sparkifydb
• sql_queries.py
  • All sql queries used for ETL are stored here
• test.ipynb
  • Test file to ensure correct records loaded to tables in sparkifydb

Clone this repository.

You will need a Postgres instance to connect to and update the following variablbes in etl.py, etl.ipynb, and test.ipynb:

• host
• user
• password

Download data for the Million Song Dataset and extract into the song_data directory as shown below:

data
  |
  - log_data
  - song_data

Log data was generated by Udacity.

Create conda environment with the yaml specification file conda-requirements.yml.

• etl.ipynb
  • Set host, user, password for your environment
  • Set conda environment to one created from conda-requirements.yml
• etl.py
  • Set host, user, password for your environment
  • Activate conda environment created from conda-requirements.yml
  • Call python etl.py