airline_ontime_schema_ch.sql

CREATE DATABASE IF NOT EXISTS `airline`;
CREATE TABLE IF NOT EXISTS `airline`.`ontime` (
  `Year`                 UInt16,
  `Quarter`              Nullable(UInt8),
  `Month`                UInt8,
  `DayofMonth`           Nullable(UInt8),
  `DayOfWeek`            Nullable(UInt8),
  `FlightDate`           Date,
  `UniqueCarrier`        Nullable(String),
  `AirlineID`            Nullable(UInt32),
  `Carrier`              Nullable(String),
  `TailNum`              Nullable(String),
  `FlightNum`            Nullable(String),
  `OriginAirportID`      Nullable(UInt32),
  `OriginAirportSeqID`   Nullable(UInt32),
  `OriginCityMarketID`   Nullable(UInt32),
  `Origin`               Nullable(String),
  `OriginCityName`       Nullable(String),
  `OriginState`          Nullable(String),
  `OriginStateFips`      Nullable(String),
  `OriginStateName`      Nullable(String),
  `OriginWac`            Nullable(UInt32),
  `DestAirportID`        Nullable(UInt32),
  `DestAirportSeqID`     Nullable(UInt32),
  `DestCityMarketID`     Nullable(UInt32),
  `Dest`                 Nullable(String),
  `DestCityName`         Nullable(String),
  `DestState`            Nullable(String),
  `DestStateFips`        Nullable(String),
  `DestStateName`        Nullable(String),
  `DestWac`              Nullable(UInt32),
  `CRSDepTime`           Nullable(UInt32),
  `DepTime`              Nullable(UInt32),
  `DepDelay`             Nullable(Float32),
  `DepDelayMinutes`      Nullable(Float32),
  `DepDel15`             Nullable(Float32),
  `DepartureDelayGroups` Nullable(Int32),
  `DepTimeBlk`           Nullable(String),
  `TaxiOut`              Nullable(Float32),
  `WheelsOff`            Nullable(UInt32),
  `WheelsOn`             Nullable(UInt32),
  `TaxiIn`               Nullable(Float32),
  `CRSArrTime`           Nullable(UInt32),
  `ArrTime`              Nullable(UInt32),
  `ArrDelay`             Nullable(Float32),
  `ArrDelayMinutes`      Nullable(Float32),
  `ArrDel15`             Nullable(Float32),
  `ArrivalDelayGroups`   Nullable(Int32),
  `ArrTimeBlk`           Nullable(String),
  `Cancelled`            Nullable(Float32),
  `CancellationCode`     Nullable(String),
  `Diverted`             Nullable(Float32),
  `CRSElapsedTime`       Nullable(Float32),
  `ActualElapsedTime`    Nullable(Float32),
  `AirTime`              Nullable(Float32),
  `Flights`              Nullable(Float32),
  `Distance`             Nullable(Float32),
  `DistanceGroup`        Nullable(Float32),
  `CarrierDelay`         Nullable(Float32),
  `WeatherDelay`         Nullable(Float32),
  `NASDelay`             Nullable(Float32),
  `SecurityDelay`        Nullable(Float32),
  `LateAircraftDelay`    Nullable(Float32),
  `FirstDepTime`         Nullable(String),
  `TotalAddGTime`        Nullable(String),
  `LongestAddGTime`      Nullable(String),
  `DivAirportLandings`   Nullable(String),
  `DivReachedDest`       Nullable(String),
  `DivActualElapsedTime` Nullable(String),
  `DivArrDelay`          Nullable(String),
  `DivDistance`          Nullable(String),
  `Div1Airport`          Nullable(String),
  `Div1AirportID`        Nullable(UInt32),
  `Div1AirportSeqID`     Nullable(UInt32),
  `Div1WheelsOn`         Nullable(String),
  `Div1TotalGTime`       Nullable(String),
  `Div1LongestGTime`     Nullable(String),
  `Div1WheelsOff`        Nullable(String),
  `Div1TailNum`          Nullable(String),
  `Div2Airport`          Nullable(String),
  `Div2AirportID`        Nullable(UInt32),
  `Div2AirportSeqID`     Nullable(UInt32),
  `Div2WheelsOn`         Nullable(String),
  `Div2TotalGTime`       Nullable(String),
  `Div2LongestGTime`     Nullable(String),
  `Div2WheelsOff`        Nullable(String),
  `Div2TailNum`          Nullable(String),
  `Div3Airport`          Nullable(String),
  `Div3AirportID`        Nullable(UInt32),
  `Div3AirportSeqID`     Nullable(UInt32),
  `Div3WheelsOn`         Nullable(String),
  `Div3TotalGTime`       Nullable(String),
  `Div3LongestGTime`     Nullable(String),
  `Div3WheelsOff`        Nullable(String),
  `Div3TailNum`          Nullable(String),
  `Div4Airport`          Nullable(String),
  `Div4AirportID`        Nullable(UInt32),
  `Div4AirportSeqID`     Nullable(UInt32),
  `Div4WheelsOn`         Nullable(String),
  `Div4TotalGTime`       Nullable(String),
  `Div4LongestGTime`     Nullable(String),
  `Div4WheelsOff`        Nullable(String),
  `Div4TailNum`          Nullable(String),
  `Div5Airport`          Nullable(String),
  `Div5AirportID`        Nullable(UInt32),
  `Div5AirportSeqID`     Nullable(UInt32),
  `Div5WheelsOn`         Nullable(String),
  `Div5TotalGTime`       Nullable(String),
  `Div5LongestGTime`     Nullable(String),
  `Div5WheelsOff`        Nullable(String),
  `Div5TailNum`          Nullable(String)
) ENGINE = MergeTree(FlightDate, (FlightDate, Year, Month), 8192);