text: CREATE DATABASE IF NOT EXISTS ${DB};
  
  USE ${DB};
  
  DROP TABLE IF EXISTS AIRLINE_PERF_EXT;
  
  CREATE EXTERNAL TABLE AIRLINE_PERF_EXT (
      Year_                                              STRING,
      Quarter                                            STRING,
      Month_                                             STRING,
      DayofMonth                                         STRING,
      DayOfWeek                                          STRING,
      FlightDate                                         STRING,
      Marketing_Airline_Network                          STRING,
      Operated_or_Branded_Code_Share_Partners            STRING,
      DOT_ID_Marketing_Airline                           STRING,
      IATA_Code_Marketing_Airline                        STRING,
      Flight_Number_Marketing_Airline                    STRING,
      Originally_Scheduled_Code_Share_Airline            STRING,
      DOT_ID_Originally_Scheduled_Code_Share_Airline     STRING,
      IATA_Code_Originally_Scheduled_Code_Share_Airline  STRING,
      Flight_Num_Originally_Scheduled_Code_Share_Airline STRING,
      Operating_Airline                                  STRING,
      DOT_ID_Operating_Airline                           STRING,
      IATA_Code_Operating_Airline                        STRING,
      Tail_Number                                        STRING,
      Flight_Number_Operating_Airline                    STRING,
      OriginAirportID                                    STRING,
      OriginAirportSeqID                                 STRING,
      OriginCityMarketID                                 STRING,
      Origin                                             STRING,
      OriginCityName                                     STRING,
      OriginState                                        STRING,
      OriginStateFips                                    STRING,
      OriginStateName                                    STRING,
      OriginWac                                          STRING,
      DestAirportID                                      STRING,
      DestAirportSeqID                                   STRING,
      DestCityMarketID                                   STRING,
      Dest                                               STRING,
      DestCityName                                       STRING,
      DestState                                          STRING,
      DestStateFips                                      STRING,
      DestStateName                                      STRING,
      DestWac                                            STRING,
      CRSDepTime                                         STRING,
      DepTime                                            STRING,
      DepDelay                                           STRING,
      DepDelayMinutes                                    STRING,
      DepDel15                                           STRING,
      DepartureDelayGroups                               STRING,
      DepTimeBlk                                         STRING,
      TaxiOut                                            STRING,
      WheelsOff                                          STRING,
      WheelsOn                                           STRING,
      TaxiIn                                             STRING,
      CRSArrTime                                         STRING,
      ArrTime                                            STRING,
      ArrDelay                                           STRING,
      ArrDelayMinutes                                    STRING,
      ArrDel15                                           STRING,
      ArrivalDelayGroups                                 STRING,
      ArrTimeBlk                                         STRING,
      Cancelled                                          STRING,
      CancellationCode                                   STRING,
      Diverted                                           STRING,
      CRSElapsedTime                                     STRING,
      ActualElapsedTime                                  STRING,
      AirTime                                            STRING,
      Flights                                            STRING,
      Distance                                           STRING,
      DistanceGroup                                      STRING,
      CarrierDelay                                       STRING,
      WeatherDelay                                       STRING,
      NASDelay                                           STRING,
      SecurityDelay                                      STRING,
      LateAircraftDelay                                  STRING,
      FirstDepTime                                       STRING,
      TotalAddGTime                                      STRING,
      LongestAddGTime                                    STRING,
      DivAirportLandings                                 STRING,
      DivReachedDest                                     STRING,
      DivActualElapsedTime                               STRING,
      DivArrDelay                                        STRING,
      DivDistance                                        STRING,
      Div1Airport                                        STRING,
      Div1AirportID                                      STRING,
      Div1AirportSeqID                                   STRING,
      Div1WheelsOn                                       STRING,
      Div1TotalGTime                                     STRING,
      Div1LongestGTime                                   STRING,
      Div1WheelsOff                                      STRING,
      Div1TailNum                                        STRING,
      Div2Airport                                        STRING,
      Div2AirportID                                      STRING,
      Div2AirportSeqID                                   STRING,
      Div2WheelsOn                                       STRING,
      Div2TotalGTime                                     STRING,
      Div2LongestGTime                                   STRING,
      Div2WheelsOff                                      STRING,
      Div2TailNum                                        STRING,
      Div3Airport                                        STRING,
      Div3AirportID                                      STRING,
      Div3AirportSeqID                                   STRING,
      Div3WheelsOn                                       STRING,
      Div3TotalGTime                                     STRING,
      Div3LongestGTime                                   STRING,
      Div3WheelsOff                                      STRING,
      Div3TailNum                                        STRING,
      Div4Airport                                        STRING,
      Div4AirportID                                      STRING,
      Div4AirportSeqID                                   STRING,
      Div4WheelsOn                                       STRING,
      Div4TotalGTime                                     STRING,
      Div4LongestGTime                                   STRING,
      Div4WheelsOff                                      STRING,
      Div4TailNum                                        STRING,
      Div5Airport                                        STRING,
      Div5AirportID                                      STRING,
      Div5AirportSeqID                                   STRING,
      Div5WheelsOn                                       STRING,
      Div5TotalGTime                                     STRING,
      Div5LongestGTime                                   STRING,
      Div5WheelsOff                                      STRING,
      Div5TailNum                                        STRING,
      Duplicate                                          STRING
  ) PARTITIONED BY (year_month STRING)
      ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.OpenCSVSerde"
          WITH SERDEPROPERTIES ( "separatorChar" = "," )
      STORED AS TEXTFILE
      LOCATION "${BASEDIR}/${DB}.db/airline_perf_ext"
      TBLPROPERTIES ( "skip.header.line.count" = "1" );
  
  CREATE EXTERNAL TABLE AIRLINE_PERF_EXT_ORC (
      Year_                                              STRING,
      Quarter                                            STRING,
      Month_                                             STRING,
      DayofMonth                                         STRING,
      DayOfWeek                                          STRING,
      FlightDate                                         STRING,
      Marketing_Airline_Network                          STRING,
      Operated_or_Branded_Code_Share_Partners            STRING,
      DOT_ID_Marketing_Airline                           STRING,
      IATA_Code_Marketing_Airline                        STRING,
      Flight_Number_Marketing_Airline                    STRING,
      Originally_Scheduled_Code_Share_Airline            STRING,
      DOT_ID_Originally_Scheduled_Code_Share_Airline     STRING,
      IATA_Code_Originally_Scheduled_Code_Share_Airline  STRING,
      Flight_Num_Originally_Scheduled_Code_Share_Airline STRING,
      Operating_Airline                                  STRING,
      DOT_ID_Operating_Airline                           STRING,
      IATA_Code_Operating_Airline                        STRING,
      Tail_Number                                        STRING,
      Flight_Number_Operating_Airline                    STRING,
      OriginAirportID                                    STRING,
      OriginAirportSeqID                                 STRING,
      OriginCityMarketID                                 STRING,
      Origin                                             STRING,
      OriginCityName                                     STRING,
      OriginState                                        STRING,
      OriginStateFips                                    STRING,
      OriginStateName                                    STRING,
      OriginWac                                          STRING,
      DestAirportID                                      STRING,
      DestAirportSeqID                                   STRING,
      DestCityMarketID                                   STRING,
      Dest                                               STRING,
      DestCityName                                       STRING,
      DestState                                          STRING,
      DestStateFips                                      STRING,
      DestStateName                                      STRING,
      DestWac                                            STRING,
      CRSDepTime                                         STRING,
      DepTime                                            STRING,
      DepDelay                                           STRING,
      DepDelayMinutes                                    STRING,
      DepDel15                                           STRING,
      DepartureDelayGroups                               STRING,
      DepTimeBlk                                         STRING,
      TaxiOut                                            STRING,
      WheelsOff                                          STRING,
      WheelsOn                                           STRING,
      TaxiIn                                             STRING,
      CRSArrTime                                         STRING,
      ArrTime                                            STRING,
      ArrDelay                                           STRING,
      ArrDelayMinutes                                    STRING,
      ArrDel15                                           STRING,
      ArrivalDelayGroups                                 STRING,
      ArrTimeBlk                                         STRING,
      Cancelled                                          STRING,
      CancellationCode                                   STRING,
      Diverted                                           STRING,
      CRSElapsedTime                                     STRING,
      ActualElapsedTime                                  STRING,
      AirTime                                            STRING,
      Flights                                            STRING,
      Distance                                           STRING,
      DistanceGroup                                      STRING,
      CarrierDelay                                       STRING,
      WeatherDelay                                       STRING,
      NASDelay                                           STRING,
      SecurityDelay                                      STRING,
      LateAircraftDelay                                  STRING,
      FirstDepTime                                       STRING,
      TotalAddGTime                                      STRING,
      LongestAddGTime                                    STRING,
      DivAirportLandings                                 STRING,
      DivReachedDest                                     STRING,
      DivActualElapsedTime                               STRING,
      DivArrDelay                                        STRING,
      DivDistance                                        STRING,
      Div1Airport                                        STRING,
      Div1AirportID                                      STRING,
      Div1AirportSeqID                                   STRING,
      Div1WheelsOn                                       STRING,
      Div1TotalGTime                                     STRING,
      Div1LongestGTime                                   STRING,
      Div1WheelsOff                                      STRING,
      Div1TailNum                                        STRING,
      Div2Airport                                        STRING,
      Div2AirportID                                      STRING,
      Div2AirportSeqID                                   STRING,
      Div2WheelsOn                                       STRING,
      Div2TotalGTime                                     STRING,
      Div2LongestGTime                                   STRING,
      Div2WheelsOff                                      STRING,
      Div2TailNum                                        STRING,
      Div3Airport                                        STRING,
      Div3AirportID                                      STRING,
      Div3AirportSeqID                                   STRING,
      Div3WheelsOn                                       STRING,
      Div3TotalGTime                                     STRING,
      Div3LongestGTime                                   STRING,
      Div3WheelsOff                                      STRING,
      Div3TailNum                                        STRING,
      Div4Airport                                        STRING,
      Div4AirportID                                      STRING,
      Div4AirportSeqID                                   STRING,
      Div4WheelsOn                                       STRING,
      Div4TotalGTime                                     STRING,
      Div4LongestGTime                                   STRING,
      Div4WheelsOff                                      STRING,
      Div4TailNum                                        STRING,
      Div5Airport                                        STRING,
      Div5AirportID                                      STRING,
      Div5AirportSeqID                                   STRING,
      Div5WheelsOn                                       STRING,
      Div5TotalGTime                                     STRING,
      Div5LongestGTime                                   STRING,
      Div5WheelsOff                                      STRING,
      Div5TailNum                                        STRING,
      Duplicate                                          STRING
  )   STORED AS ORC
      LOCATION "${BASEDIR}/${DB}.db/airline_perf_ext_orc";