redshift_cstore_fdw

copy.sql

copy users    from '/tmp/s3/allusers_pipe.txt'  with null as '' delimiter '|';
copy venue    from '/tmp/s3/venue_pipe.txt'                     delimiter '|';
copy category from '/tmp/s3/category_pipe.txt'  with null as '' delimiter '|';
copy date     from '/tmp/s3/date2008_pipe.txt'  with null as '' delimiter '|';
copy event    from '/tmp/s3/allevents_pipe.txt' with null as '' delimiter '|';
copy listing  from '/tmp/s3/listings_pipe.txt'  with null as '' delimiter '|';
copy sales    from '/tmp/s3/sales_tab.txt'      with null as '' delimiter '\t';

create_fdw_table.sql

CREATE EXTENSION cstore_fdw;
 
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
 
create foreign table users(
	userid integer not null,
	username char(8),
	firstname varchar(30),
	lastname varchar(30),
	city varchar(30),
	state char(2),
	email varchar(100),
	phone char(14),
	likesports boolean,
	liketheatre boolean,
	likeconcerts boolean,
	likejazz boolean,
	likeclassical boolean,
	likeopera boolean,
	likerock boolean,
	likevegas boolean,
	likebroadway boolean,
	likemusicals boolean)
SERVER cstore_server
OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/users.cstore',
        compression 'pglz');
 
create foreign table venue(
	venueid smallint not null,
	venuename varchar(100),
	venuecity varchar(30),
	venuestate char(2),
	venueseats integer)
SERVER cstore_server
OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/venue.cstore',
        compression 'pglz');
 
create foreign table category(
	catid smallint not null,
	catgroup varchar(10),
	catname varchar(10),
	catdesc varchar(50))
SERVER cstore_server
OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/category.cstore',
        compression 'pglz');
 
create foreign table date(
	dateid smallint not null,
	caldate date not null,
	day character(3) not null,
	week smallint not null,
	month character(5) not null,
	qtr character(5) not null,
	year smallint not null,
	holiday boolean default('N'))
SERVER cstore_server
OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/date.cstore',
        compression 'pglz');
 
create foreign table event(
	eventid integer not null,
	venueid smallint not null,
	catid smallint not null,
	dateid smallint not null ,
	eventname varchar(200),
	starttime timestamp)
SERVER cstore_server
OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/event.cstore',
        compression 'pglz');
 
create foreign table listing(
	listid integer not null,
	sellerid integer not null,
	eventid integer not null,
	dateid smallint not null  ,
	numtickets smallint not null,
	priceperticket decimal(8,2),
	totalprice decimal(8,2),
	listtime timestamp)
SERVER cstore_server
OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/listing.cstore',
        compression 'pglz');
 
create foreign table sales(
	salesid integer not null,
	listid integer not null,
	sellerid integer not null,
	buyerid integer not null,
	eventid integer not null,
	dateid smallint not null ,
	qtysold smallint not null,
	pricepaid decimal(8,2),
	commission decimal(8,2),
	saletime timestamp)
SERVER cstore_server
OPTIONS(filename '/var/lib/postgresql/9.3/main/cstore/sales.cstore',
        compression 'pglz');

create_plain_table.sql

create table users(
	userid integer not null,
	username char(8),
	firstname varchar(30),
	lastname varchar(30),
	city varchar(30),
	state char(2),
	email varchar(100),
	phone char(14),
	likesports boolean,
	liketheatre boolean,
	likeconcerts boolean,
	likejazz boolean,
	likeclassical boolean,
	likeopera boolean,
	likerock boolean,
	likevegas boolean,
	likebroadway boolean,
	likemusicals boolean);

create table venue(
	venueid smallint not null,
	venuename varchar(100),
	venuecity varchar(30),
	venuestate char(2),
	venueseats integer);

create table category(
	catid smallint not null,
	catgroup varchar(10),
	catname varchar(10),
	catdesc varchar(50));

create table date(
	dateid smallint not null,
	caldate date not null,
	day character(3) not null,
	week smallint not null,
	month character(5) not null,
	qtr character(5) not null,
	year smallint not null,
	holiday boolean default('N'));

create table event(
	eventid integer not null,
	venueid smallint not null,
	catid smallint not null,
	dateid smallint not null ,
	eventname varchar(200),
	starttime timestamp);

create table listing(
	listid integer not null,
	sellerid integer not null,
	eventid integer not null,
	dateid smallint not null  ,
	numtickets smallint not null,
	priceperticket decimal(8,2),
	totalprice decimal(8,2),
	listtime timestamp);

create table sales(
	salesid integer not null,
	listid integer not null,
	sellerid integer not null,
	buyerid integer not null,
	eventid integer not null,
	dateid smallint not null ,
	qtysold smallint not null,
	pricepaid decimal(8,2),
	commission decimal(8,2),
	saletime timestamp);