Overview

This is a basic script to pull data from Shopify's API and push it into a local mySQL data warehouse. The schema is very simple -- no surrogate keys, no slowly changing dimensions -- and uses Shopify object IDs as primary keys.

Tables include:

• Product
• SKU
• Order
• Order Line

Installation

Assuming you have Git installed, git clone the repository.

git clone git@github.com:kenkyhuang/shopify-dw.git

Assuming you have virtualenv installed, install the necessary Python packages.

virtualenv env --no-site-packages
pip install -r requirements.txt

Assuming you have RubyGems installed, install the shopify_api Ruby gem.

In puller/settings.rb, set your Shopify API key, API password and store name (i.e. the "xyz" in "xyz.shopify.com". See here for details on generating a Shopify API key.

gem install shopify_api

Load the mySQL schema.

/usr/local/mysql/bin/mysql -u root shopify < sql/schema.sql

Kick off the ETL script. This will:

• Pull from the Shopify API's Product and Order resources and write JSON data to the /json folder
• Transform JSON into flat text files and write these to the /import folder
• Import data into mySQL tables as defined in the schema

./run.sh

This script can be then scheduled via a cron job.

crontab -e