/
flights_orc.sql
115 lines (115 loc) · 2.58 KB
/
flights_orc.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
CREATE TABLE flights_orc (
Year INT,
Quarter INT,
Month INT,
DayofMonth INT,
DayOfWeek INT,
FlightDate VARCHAR,
UniqueCarrier VARCHAR,
AirlineID INT,
Carrier VARCHAR,
TailNum VARCHAR,
FlightNum INT,
OriginAirportID INT,
OriginAirportSeqID INT,
OriginCityMarketID INT,
Origin VARCHAR,
OriginCityName VARCHAR,
OriginState VARCHAR,
OriginStateFips INT,
OriginStateName VARCHAR,
OriginWac INT,
DestAirportID INT,
DestAirportSeqID INT,
DestCityMarketID INT,
Dest VARCHAR,
DestCityName VARCHAR,
DestState VARCHAR,
DestStateFips INT,
DestStateName VARCHAR,
DestWac INT,
CRSDepTime INT,
DepTime INT,
DepDelay INT,
DepDelayMinutes INT,
DepDel15 INT,
DepartureDelayGroups INT,
DepTimeBlk VARCHAR,
TaxiOut INT,
WheelsOff INT,
WheelsOn INT,
TaxiIn INT,
CRSArrTime INT,
ArrTime INT,
ArrDelay INT,
ArrDelayMinutes INT,
ArrDel15 INT,
ArrivalDelayGroups INT,
ArrTimeBlk VARCHAR,
Cancelled TINYINT,
CancellationCode VARCHAR,
Diverted TINYINT,
CRSElapsedTime INT,
ActualElapsedTime INT,
AirTime INT,
Flights INT,
Distance INT,
DistanceGroup INT,
CarrierDelay INT,
WeatherDelay INT,
NASDelay INT,
SecurityDelay INT,
LateAircraftDelay INT,
FirstDepTime INT,
TotalAddGTime INT,
LongestAddGTime INT,
DivAirportLandings INT,
DivReachedDest INT,
DivActualElapsedTime INT,
DivArrDelay INT,
DivDistance INT,
Div1Airport VARCHAR,
Div1AirportID INT,
Div1AirportSeqID INT,
Div1WheelsOn INT,
Div1TotalGTime INT,
Div1LongestGTime INT,
Div1WheelsOff INT,
Div1TailNum VARCHAR,
Div2Airport VARCHAR,
Div2AirportID INT,
Div2AirportSeqID INT,
Div2WheelsOn INT,
Div2TotalGTime INT,
Div2LongestGTime INT,
Div2WheelsOff INT,
Div2TailNum VARCHAR,
Div3Airport VARCHAR,
Div3AirportID INT,
Div3AirportSeqID INT,
Div3WheelsOn INT,
Div3TotalGTime INT,
Div3LongestGTime INT,
Div3WheelsOff INT,
Div3TailNum VARCHAR,
Div4Airport VARCHAR,
Div4AirportID INT,
Div4AirportSeqID INT,
Div4WheelsOn INT,
Div4TotalGTime INT,
Div4LongestGTime INT,
Div4WheelsOff INT,
Div4TailNum VARCHAR,
Div5Airport VARCHAR,
Div5AirportID INT,
Div5AirportSeqID INT,
Div5WheelsOn INT,
Div5TotalGTime INT,
Div5LongestGTime INT,
Div5WheelsOff INT,
Div5TailNum VARCHAR
)
WITH (
external_location = 's3://starburstdata-oreilly/sample-data/ontime/orc/',
format = 'ORC'
);