-- ACS


CREATE TABLE acs3yr (
	"serialno" VARCHAR(15),
	"rt"       VARCHAR(3),
	"sporder"  INTEGER,
	"puma"     INTEGER,
	"st"       INTEGER,
	"adjinc"   DOUBLE,
	"pwgtp"    INTEGER,
	"agep"     INTEGER,
	"cit"      INTEGER,
	"citwp"    INTEGER,
	"cow"      INTEGER,
	"ddrs"     INTEGER,
	"dear"     INTEGER,
	"deye"     INTEGER,
	"dout"     INTEGER,
	"dphy"     INTEGER,
	"drat"     INTEGER,
	"dratx"    INTEGER,
	"drem"     INTEGER,
	"eng"      INTEGER,
	"fer"      INTEGER,
	"gcl"      INTEGER,
	"gcm"      INTEGER,
	"gcr"      INTEGER,
	"hins1"    INTEGER,
	"hins2"    INTEGER,
	"hins3"    INTEGER,
	"hins4"    INTEGER,
	"hins5"    INTEGER,
	"hins6"    INTEGER,
	"hins7"    INTEGER,
	"intp"     INTEGER,
	"jwmnp"    INTEGER,
	"jwrip"    INTEGER,
	"jwtr"     INTEGER,
	"lanx"     INTEGER,
	"mar"      INTEGER,
	"marhd"    INTEGER,
	"marhm"    INTEGER,
	"marht"    INTEGER,
	"marhw"    INTEGER,
	"marhyp"   INTEGER,
	"mig"      INTEGER,
	"mil"      INTEGER,
	"mlpa"     INTEGER,
	"mlpb"     INTEGER,
	"mlpc"     INTEGER,
	"mlpd"     INTEGER,
	"mlpe"     INTEGER,
	"mlpf"     INTEGER,
	"mlpg"     INTEGER,
	"mlph"     INTEGER,
	"mlpi"     INTEGER,
	"mlpj"     INTEGER,
	"mlpk"     INTEGER,
	"nwab"     INTEGER,
	"nwav"     INTEGER,
	"nwla"     INTEGER,
	"nwlk"     INTEGER,
	"nwre"     INTEGER,
	"oip"      INTEGER,
	"pap"      INTEGER,
	"relp"     INTEGER,
	"retp"     INTEGER,
	"sch"      INTEGER,
	"schg"     INTEGER,
	"schl"     INTEGER,
	"semp"     INTEGER,
	"sex"      INTEGER,
	"ssip"     INTEGER,
	"ssp"      INTEGER,
	"wagp"     INTEGER,
	"wkhp"     INTEGER,
	"wkl"      INTEGER,
	"wkw"      INTEGER,
	"yoep"     INTEGER,
	"anc"      INTEGER,
	"anc1p"    INTEGER,
	"anc2p"    INTEGER,
	"decade"   INTEGER,
	"dis"      INTEGER,
	"drivesp"  INTEGER,
	"esp"      INTEGER,
	"esr"      INTEGER,
	"hicov"    INTEGER,
	"hisp"     INTEGER,
	"indp"     INTEGER,
	"jwap"     INTEGER,
	"jwdp"     INTEGER,
	"lanp"     INTEGER,
	"migpuma"  INTEGER,
	"migsp"    INTEGER,
	"msp"      INTEGER,
	"naicsp"   VARCHAR(15),
	"nativity" INTEGER,
	"nop"      INTEGER,
	"oc"       INTEGER,
	"occp02"   VARCHAR(15),
	"occp10"   VARCHAR(15),
	"paoc"     INTEGER,
	"pernp"    INTEGER,
	"pincp"    INTEGER,
	"pobp"     INTEGER,
	"povpip"   INTEGER,
	"powpuma"  INTEGER,
	"powsp"    INTEGER,
	"privcov"  INTEGER,
	"pubcov"   INTEGER,
	"qtrbir"   INTEGER,
	"rac1p"    INTEGER,
	"rac2p"    INTEGER,
	"rac3p"    INTEGER,
	"racaian"  INTEGER,
	"racasn"   INTEGER,
	"racblk"   INTEGER,
	"racnhpi"  INTEGER,
	"racnum"   INTEGER,
	"racsor"   INTEGER,
	"racwht"   INTEGER,
	"rc"       INTEGER,
	"sfn"      INTEGER,
	"sfr"      INTEGER,
	"socp00"   VARCHAR(15),
	"socp10"   VARCHAR(15),
	"vps"      INTEGER,
	"waob"     INTEGER,
	"fagep"    INTEGER,
	"fancp"    INTEGER,
	"fcitp"    INTEGER,
	"fcitwp"   INTEGER,
	"fcowp"    INTEGER,
	"fddrsp"   INTEGER,
	"fdearp"   INTEGER,
	"fdeyep"   INTEGER,
	"fdoutp"   INTEGER,
	"fdphyp"   INTEGER,
	"fdratp"   INTEGER,
	"fdratxp"  INTEGER,
	"fdremp"   INTEGER,
	"fengp"    INTEGER,
	"fesrp"    INTEGER,
	"fferp"    INTEGER,
	"fgclp"    INTEGER,
	"fgcmp"    INTEGER,
	"fgcrp"    INTEGER,
	"fhins1p"  INTEGER,
	"fhins2p"  INTEGER,
	"fhins3c"  INTEGER,
	"fhins3p"  INTEGER,
	"fhins4c"  INTEGER,
	"fhins4p"  INTEGER,
	"fhins5c"  INTEGER,
	"fhins5p"  INTEGER,
	"fhins6p"  INTEGER,
	"fhins7p"  INTEGER,
	"fhisp"    INTEGER,
	"findp"    INTEGER,
	"fintp"    INTEGER,
	"fjwdp"    INTEGER,
	"fjwmnp"   INTEGER,
	"fjwrip"   INTEGER,
	"fjwtrp"   INTEGER,
	"flanp"    INTEGER,
	"flanxp"   INTEGER,
	"fmarhdp"  INTEGER,
	"fmarhmp"  INTEGER,
	"fmarhtp"  INTEGER,
	"fmarhwp"  INTEGER,
	"fmarhyp"  INTEGER,
	"fmarp"    INTEGER,
	"fmigp"    INTEGER,
	"fmigsp"   INTEGER,
	"fmilpp"   INTEGER,
	"fmilsp"   INTEGER,
	"foccp"    INTEGER,
	"foip"     INTEGER,
	"fpap"     INTEGER,
	"fpobp"    INTEGER,
	"fpowsp"   INTEGER,
	"fracp"    INTEGER,
	"frelp"    INTEGER,
	"fretp"    INTEGER,
	"fschgp"   INTEGER,
	"fschlp"   INTEGER,
	"fschp"    INTEGER,
	"fsemp"    INTEGER,
	"fsexp"    INTEGER,
	"fssip"    INTEGER,
	"fssp"     INTEGER,
	"fwagp"    INTEGER,
	"fwkhp"    INTEGER,
	"fwklp"    INTEGER,
	"fwkwp"    INTEGER,
	"fyoep"    INTEGER,
	"pwgtp1"   INTEGER,
	"pwgtp2"   INTEGER,
	"pwgtp3"   INTEGER,
	"pwgtp4"   INTEGER,
	"pwgtp5"   INTEGER,
	"pwgtp6"   INTEGER,
	"pwgtp7"   INTEGER,
	"pwgtp8"   INTEGER,
	"pwgtp9"   INTEGER,
	"pwgtp10"  INTEGER,
	"pwgtp11"  INTEGER,
	"pwgtp12"  INTEGER,
	"pwgtp13"  INTEGER,
	"pwgtp14"  INTEGER,
	"pwgtp15"  INTEGER,
	"pwgtp16"  INTEGER,
	"pwgtp17"  INTEGER,
	"pwgtp18"  INTEGER,
	"pwgtp19"  INTEGER,
	"pwgtp20"  INTEGER,
	"pwgtp21"  INTEGER,
	"pwgtp22"  INTEGER,
	"pwgtp23"  INTEGER,
	"pwgtp24"  INTEGER,
	"pwgtp25"  INTEGER,
	"pwgtp26"  INTEGER,
	"pwgtp27"  INTEGER,
	"pwgtp28"  INTEGER,
	"pwgtp29"  INTEGER,
	"pwgtp30"  INTEGER,
	"pwgtp31"  INTEGER,
	"pwgtp32"  INTEGER,
	"pwgtp33"  INTEGER,
	"pwgtp34"  INTEGER,
	"pwgtp35"  INTEGER,
	"pwgtp36"  INTEGER,
	"pwgtp37"  INTEGER,
	"pwgtp38"  INTEGER,
	"pwgtp39"  INTEGER,
	"pwgtp40"  INTEGER,
	"pwgtp41"  INTEGER,
	"pwgtp42"  INTEGER,
	"pwgtp43"  INTEGER,
	"pwgtp44"  INTEGER,
	"pwgtp45"  INTEGER,
	"pwgtp46"  INTEGER,
	"pwgtp47"  INTEGER,
	"pwgtp48"  INTEGER,
	"pwgtp49"  INTEGER,
	"pwgtp50"  INTEGER,
	"pwgtp51"  INTEGER,
	"pwgtp52"  INTEGER,
	"pwgtp53"  INTEGER,
	"pwgtp54"  INTEGER,
	"pwgtp55"  INTEGER,
	"pwgtp56"  INTEGER,
	"pwgtp57"  INTEGER,
	"pwgtp58"  INTEGER,
	"pwgtp59"  INTEGER,
	"pwgtp60"  INTEGER,
	"pwgtp61"  INTEGER,
	"pwgtp62"  INTEGER,
	"pwgtp63"  INTEGER,
	"pwgtp64"  INTEGER,
	"pwgtp65"  INTEGER,
	"pwgtp66"  INTEGER,
	"pwgtp67"  INTEGER,
	"pwgtp68"  INTEGER,
	"pwgtp69"  INTEGER,
	"pwgtp70"  INTEGER,
	"pwgtp71"  INTEGER,
	"pwgtp72"  INTEGER,
	"pwgtp73"  INTEGER,
	"pwgtp74"  INTEGER,
	"pwgtp75"  INTEGER,
	"pwgtp76"  INTEGER,
	"pwgtp77"  INTEGER,
	"pwgtp78"  INTEGER,
	"pwgtp79"  INTEGER,
	"pwgtp80"  INTEGER
);


copy into acs3yr from '/scratch/hannes/acs3yr.csv' locked;


-- ontime

CREATE TABLE "ontime" (
        "Year" smallint DEFAULT NULL,
        "Quarter" tinyint DEFAULT NULL,
        "Month" tinyint DEFAULT NULL,
        "DayofMonth" tinyint DEFAULT NULL,
        "DayOfWeek" tinyint DEFAULT NULL,
        "FlightDate" date DEFAULT NULL,
        "UniqueCarrier" char(7) DEFAULT NULL,
        "AirlineID" decimal(8,2) DEFAULT NULL,
        "Carrier" char(2) DEFAULT NULL,
        "TailNum" varchar(50) DEFAULT NULL,
        "FlightNum" varchar(10) DEFAULT NULL,
        "OriginAirportID" integer,
        "OriginAirportSeqID" integer,
        "OriginCityMarketID" integer,
        "Origin" char(5) DEFAULT NULL,
        "OriginCityName" varchar(100) DEFAULT NULL,
        "OriginState" char(2) DEFAULT NULL,
        "OriginStateFips" varchar(10) DEFAULT NULL,
        "OriginStateName" varchar(100) DEFAULT NULL,
        "OriginWac" decimal(8,2) DEFAULT NULL,
        "DestAirportID" integer,
        "DestAirportSeqID" integer,
        "DestCityMarketID" integer,
        "Dest" char(5) DEFAULT NULL,
        "DestCityName" varchar(100) DEFAULT NULL,
        "DestState" char(2) DEFAULT NULL,
        "DestStateFips" varchar(10) DEFAULT NULL,
        "DestStateName" varchar(100) DEFAULT NULL,
        "DestWac" decimal(8,2) DEFAULT NULL,
        "CRSDepTime" decimal(8,2) DEFAULT NULL,
        "DepTime" decimal(8,2) DEFAULT NULL,
        "DepDelay" decimal(8,2) DEFAULT NULL,
        "DepDelayMinutes" decimal(8,2) DEFAULT NULL,
        "DepDel15" decimal(8,2) DEFAULT NULL,
        "DepartureDelayGroups" decimal(8,2) DEFAULT NULL,
        "DepTimeBlk" varchar(20) DEFAULT NULL,
        "TaxiOut" decimal(8,2) DEFAULT NULL,
        "WheelsOff" decimal(8,2) DEFAULT NULL,
        "WheelsOn" decimal(8,2) DEFAULT NULL,
        "TaxiIn" decimal(8,2) DEFAULT NULL,
        "CRSArrTime" decimal(8,2) DEFAULT NULL,
        "ArrTime" decimal(8,2) DEFAULT NULL,
        "ArrDelay" decimal(8,2) DEFAULT NULL,
        "ArrDelayMinutes" decimal(8,2) DEFAULT NULL,
        "ArrDel15" decimal(8,2) DEFAULT NULL,
        "ArrivalDelayGroups" decimal(8,2) DEFAULT NULL,
        "ArrTimeBlk" varchar(20) DEFAULT NULL,
        "Cancelled" tinyint DEFAULT NULL,
        "CancellationCode" char(1) DEFAULT NULL,
        "Diverted" tinyint DEFAULT NULL,
        "CRSElapsedTime" decimal(8,2) DEFAULT NULL,
        "ActualElapsedTime" decimal(8,2) DEFAULT NULL,
        "AirTime" decimal(8,2) DEFAULT NULL,
        "Flights" decimal(8,2) DEFAULT NULL,
        "Distance" decimal(8,2) DEFAULT NULL,
        "DistanceGroup" tinyint DEFAULT NULL,
        "CarrierDelay" decimal(8,2) DEFAULT NULL,
        "WeatherDelay" decimal(8,2) DEFAULT NULL,
        "NASDelay" decimal(8,2) DEFAULT NULL,
        "SecurityDelay" decimal(8,2) DEFAULT NULL,
        "LateAircraftDelay" decimal(8,2) DEFAULT NULL,
        "FirstDepTime" varchar(10) DEFAULT NULL,
        "TotalAddGTime" varchar(10) DEFAULT NULL,
        "LongestAddGTime" varchar(10) DEFAULT NULL,
        "DivAirportLandings" varchar(10) DEFAULT NULL,
        "DivReachedDest" varchar(10) DEFAULT NULL,
        "DivActualElapsedTime" varchar(10) DEFAULT NULL,
        "DivArrDelay" varchar(10) DEFAULT NULL,
        "DivDistance" varchar(10) DEFAULT NULL,
        "Div1Airport" varchar(10) DEFAULT NULL,
        "Div1AiportID" integer,
        "Div1AirportSeqID" integer,
        "Div1WheelsOn" varchar(10) DEFAULT NULL,
        "Div1TotalGTime" varchar(10) DEFAULT NULL,
        "Div1LongestGTime" varchar(10) DEFAULT NULL,
        "Div1WheelsOff" varchar(10) DEFAULT NULL,
        "Div1TailNum" varchar(10) DEFAULT NULL,
        "Div2Airport" varchar(10) DEFAULT NULL,
        "Div2AirportID" integer,
        "Div2AirportSeqID" integer,
        "Div2WheelsOn" varchar(10) DEFAULT NULL,
        "Div2TotalGTime" varchar(10) DEFAULT NULL,
        "Div2LongestGTime" varchar(10) DEFAULT NULL,
        "Div2WheelsOff" varchar(10) DEFAULT NULL,
        "Div2TailNum" varchar(10) DEFAULT NULL,
        "Div3Airport" varchar(10) DEFAULT NULL,
        "Div3AirportID" integer,
        "Div3AirportSeqID" integer,
        "Div3WheelsOn" varchar(10) DEFAULT NULL,
        "Div3TotalGTime" varchar(10) DEFAULT NULL,
        "Div3LongestGTime" varchar(10) DEFAULT NULL,
        "Div3WheelsOff" varchar(10) DEFAULT NULL,
        "Div3TailNum" varchar(10) DEFAULT NULL,
        "Div4Airport" varchar(10) DEFAULT NULL,
        "Div4AirportID"  integer,
        "Div4AirportSeqID" integer,
        "Div4WheelsOn" varchar(10) DEFAULT NULL,
        "Div4TotalGTime" varchar(10) DEFAULT NULL,
        "Div4LongestGTime" varchar(10) DEFAULT NULL,
        "Div4WheelsOff" varchar(10) DEFAULT NULL,
        "Div4TailNum" varchar(10) DEFAULT NULL,
        "Div5Airport" varchar(10) DEFAULT NULL,
        "Div5AirportID"  integer,
        "Div5AirportSeqID" integer,
        "Div5WheelsOn" varchar(10) DEFAULT NULL,
        "Div5TotalGTime" varchar(10) DEFAULT NULL,
        "Div5LongestGTime" varchar(10) DEFAULT NULL,
        "Div5WheelsOff" varchar(10) DEFAULT NULL,
        "Div5TailNum" varchar(10) DEFAULT NULL
      ) ;


copy into ontime from '/data24r1/hannes/ontime/10m.csv' using delimiters ',','\n','"' NULL AS '' LOCKED;