Sample redshift code to load from JSON file using jsonpaths

Sample Redshift code

### begin Redshift code ###

// get rid of the old stuff
drop table crashyplane;

// create a new table
create table crashyplane (
		event varchar(255) not null,
		"time" int,
		distinct_id varchar(255) distkey sortkey not null,
		"$city" varchar(255),
		"$region" varchar(255),
		"$referrer" varchar(255),
		"Source" varchar(255),
		"High Score  (ms)" DECIMAL,
		 mp_country_code	varchar(255),
		"Number of games" int,
		"New High Score" DECIMAL,
		"$variant_id" int
);

// bring over data, jsonpaths from s3
	copy crashyplane 
	from 's3://mixpanel1/crashyplane.txt' credentials 'aws_access_key_id=****;aws_secret_access_key=****' 
	json 's3://mixpanel1/crashy_jsonpaths.json'
	;


// set up error handling
select d.query, substring(d.filename,14,20), 
	d.line_number as line, 
	substring(d.value,1,16) as value,
	substring(le.err_reason,1,48) as err_reason
	from stl_loaderror_detail d, stl_load_errors le
	where d.query = le.query
	and d.query = pg_last_copy_id(); 


## begin crashy_jsonpaths.json data

{
    "jsonpaths": [
        "$['event']",
    	"$['properties']['time']",
    	"$['properties']['distinct_id']",
    	"$['properties']['$city']",
    	"$['properties']['$region']",
    	"$['properties']['$referrer']",
        "$['properties']['Source']",
    	"$['properties']['High Score  (ms)']",
    	"$['properties']['mp_country_code']",
    	"$['properties']['Number of games']",
        "$['properties']['New High Score']",
        "$['properties']['$variant_id']"
    ]
}

## begin sample data

{"event":"App Opened","properties":{"time":1396310400,"distinct_id":"257627","$city":"Berlin","$experiments":{"197":523},"$import":true,"$referrer":"$direct","$region":"Berlin","High Score  (ms)":1079,"Number of games":303,"Source":"Facebook","mp_country_code":"DE"}}
{"event":"App Opened","properties":{"time":1396310402,"distinct_id":"974478","$city":"Morristown","$experiments":{"197":523},"$import":true,"$referrer":"http://duckduckgo.com/wmcdngvipewsu","$region":"New Jersey","High Score  (ms)":6415,"Number of games":690,"Source":"BrightRoll","mp_country_code":"US"}}
{"event":"App Opened","properties":{"time":1396310406,"distinct_id":"85643","$city":"Shenyang","$experiments":{"197":523},"$import":true,"$referrer":"http://reddit.com/rseqxrjgrd","$region":"Liaoning","High Score  (ms)":3770,"Number of games":930,"Source":"Organic","mp_country_code":"CN"}}
{"event":"App Opened","properties":{"time":1396310406,"distinct_id":"271742","$city":"Richfield","$experiments":{"197":523},"$import":true,"$referrer":"http://reddit.com/oatfznoviwr","$region":"Utah","High Score  (ms)":6188,"Number of games":37,"Source":"Facebook","mp_country_code":"US"}}
{"event":"App Opened","properties":{"time":1396310407,"distinct_id":"153012","$experiments":{"197":523},"$import":true,"$referrer":"http://bing.com/lhptp","High Score  (ms)":7209,"Number of games":299,"Source":"Facebook","mp_country_code":"US"}}
{"event":"App Opened","properties":{"time":1396310408,"distinct_id":"904639","$city":"Cupertino","$experiments":{"197":523},"$import":true,"$referrer":"http://google.com/lfntqatspgclgk","$region":"California","High Score  (ms)":9721,"Number of games":355,"Source":"Organic","mp_country_code":"US"}}
{"event":"Game Started","properties":{"time":1396310412,"distinct_id":"34667","$experiments":{"197":523},"$import":true,"$referrer":"http://twitter.com/qdv","High Score  (ms)":6568,"Number of games":757,"Source":"Organic","mp_country_code":"NL"}}
{"event":"App Opened","properties":{"time":1396310424,"distinct_id":"603526","$city":"Minneapolis","$experiments":{"197":523},"$import":true,"$referrer":"http://reddit.com/g","$region":"Minnesota","High Score  (ms)":4036,"Number of games":358,"Source":"BrightRoll","mp_country_code":"US"}}
{"event":"App Opened","properties":{"time":1396310427,"distinct_id":"232298","$experiments":{"197":525},"$import":true,"$referrer":"http://baidu.com/uxzypvemdhh","High Score  (ms)":615,"Number of games":492,"Source":"Facebook","mp_country_code":"HR"}}
{"event":"App Opened","properties":{"time":1396310430,"distinct_id":"1550215","$city":"Seattle","$experiments":{"197":523},"$import":true,"$referrer":"http://facebook.com/msodfznvroq","$region":"Washington","High Score  (ms)":5068,"Number of games":836,"Source":"Facebook","mp_country_code":"US"}}