Jx5rS3x8

1CREATE DATABASE IF NOT EXISTS flights;
2
3CREATE EXTERNAL TABLE IF NOT EXISTS airports_str (
4  iata STRING
5  , airport STRING
6  , city STRING
7  , state STRING
8  , country STRING
9  , latitude DOUBLE
10  , longitude DOUBLE
11) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
12STORED AS TEXTFILE LOCATION '/tmp/airports/'
13TBLPROPERTIES('skip.header.line.count'='1');
14
15create table if not exists airports as (
16  select iata, city, state, country, cast(latitude as double), cast (longitude as double)
17  from airports_str
18);
19
20CREATE EXTERNAL TABLE IF NOT EXISTS airlines_bi_pq (
21  year INT
22  , month INT
23  , day INT
24  , dayofweek INT
25  , dep_time INT
26  , crs_dep_time INT
27  , arr_time INT
28  , crs_arr_time INT
29  , carrier STRING
30  , flight_num INT
31  , tail_num INT
32  , actual_elapsed_time INT
33  , crs_elapsed_time INT
34  , airtime INT
35  , arrdelay INT
36  , depdelay INT
37  , origin STRING
38  , dest STRING
39  , distance INT
40  , taxi_in INT
41  , taxi_out INT
42  , cancelled INT
43  , cancellation_code STRING
44  , diverted INT
45  , carrier_delay INT
46  , weather_delay INT
47  , nas_delay INT
48  , security_delay INT
49  , late_aircraft_delay INT
50  , date_yyyymm STRING
51) STORED AS PARQUET LOCATION '/tmp/airlines';