CREATE TABLE ALARM_HANDLE
(  ID varchar  null,
   VEHICLE_ID  binary(16) not NULL,
   ALARM_START_TIME BIGINT NOT NULL,
   ALARM_TYPE  INTEGER NOT NULL,
   PLATE_NUMBER VARCHAR NULL,
   ASSIGNMENT_NAME VARCHAR NULL,
   PROFESSIONALS_NAME VARCHAR NULL,
   ALARM_END_TIME BIGINT,
   ALARM_START_LOCATION VARCHAR NULL,
   ALARM_END_LOCATION VARCHAR NULL,
   ALARM_SOURCE INTEGER NULL,
   SPEED VARCHAR NULL,
   ANGLE VARCHAR NULL,
   POSITIONAL_ID VARCHAR NULL,
   PERSON_ID VARCHAR NULL,
   PERSON_NAME VARCHAR NULL,
   HANDLE_TIME BIGINT,
   STATUS INTEGER,
   HANDLE_TYPE VARCHAR NULL,
   DESCRIPTION VARCHAR NULL,
   HEIGHT VARCHAR NULL,
   RECORDER_SPEED VARCHAR NULL,
   FENCE_TYPE VARCHAR NULL,
   FENCE_NAME VARCHAR NULL,
   MONITOR_TYPE INTEGER NULL,
   PROTOCOL_TYPE INTEGER NULL,
   SWIFT_NUMBER VARCHAR NULL,
   PUSH_TYPE INTEGER NULL,
   CAL_STANDARD INTEGER NULL,
   SPEED_TYPE  INTEGER  NULL,
   SEVERITY DOUBLE NULL,
   SPEED_LIMIT DOUBLE NULL,
   SPEED_TIME INTEGER NULL,
   MAX_SPEED DOUBLE NULL,
   GROUP_ID BINARY(16) NULL,
   REMARK VARCHAR NULL,
   CONSTRAINT PK PRIMARY KEY (VEHICLE_ID,ALARM_START_TIME,ALARM_TYPE)
) COMPRESSION='snappy' SPLIT ON (decode('0F','HEX'),decode('1F','HEX'),decode('2F','HEX'),decode('3F','HEX'),decode('4F','HEX'),decode('5F','HEX'),decode('6F','HEX'),decode('7F','HEX'),decode('8F','HEX'),decode('9F','HEX'),decode('AF','HEX'),decode('BF','HEX'),decode('CF','HEX'),decode('DF','HEX'),decode('EF','HEX'));

CREATE TABLE BUSINESS_AREAS
(  ID VARCHAR NOT NULL,
   POSITION_INFO_ID VARCHAR NULL,
   BUSINESS_ID VARCHAR NULL,
   NAME VARCHAR NULL,
   LOCATION VARCHAR NULL,  CONSTRAINT PK PRIMARY KEY (ID)
);

CREATE TABLE INTEGRATED_STATISTICS
(
   ID VARCHAR NULL,
   VEHICLE_ID BINARY(16) NOT NULL,
   DAY_TIME BIGINT NOT NULL,
   PLATE_NUMBER VARCHAR NULL,
   GPS_MILE DOUBLE,
   TRAVEL_TIME BIGINT,
   DOWN_TIME BIGINT,
   OVER_SPEED_TIMES BIGINT,
   TRAVEL_TIMES BIGINT,
   ALARM_TIMES BIGINT,
   NIGHT_TRAVEL_MILE DOUBLE
   CONSTRAINT PK PRIMARY KEY (VEHICLE_ID,DAY_TIME)
);

CREATE TABLE POSITIONAL
(
   ID VARCHAR NULL,
   VEHICLE_ID BINARY(16) NOT NULL,
   DEVICE_NUMBER VARCHAR,
   SIM_CARD VARCHAR NULL,
   ASSIGNMENT_NAME VARCHAR NULL,
   VTIME BIGINT NOT NULL,
   ALARM VARCHAR NULL,
   STATUS VARCHAR NULL,
   LONGTITUDE VARCHAR NULL,
   LATITUDE VARCHAR NULL,
   ADDRESS VARCHAR NULL,
   HEIGHT VARCHAR NULL,
   RECORDER_SPEED VARCHAR NULL,
   SPEED VARCHAR NULL,
   ANGLE VARCHAR NULL,
   TEMPERTURE VARCHAR NULL,
   RESERVE VARCHAR NULL,
   GPS_MILE VARCHAR NULL,
   DEVICE_ID_ONE VARCHAR NULL,
   DEVICE_ID_TWO VARCHAR NULL,
   MESSAGE_LENGTH_ONE VARCHAR NULL,
   MESSAGE_LENGTH_TWO VARCHAR NULL,
   TOTAL_OILWEAR_ONE VARCHAR NULL,
   TOTAL_OILWEAR_TWO VARCHAR NULL,
   OILTANK_TEMPERATURE_ONE VARCHAR NULL,
   OILTANK_TEMPERATURE_TWO VARCHAR NULL,
   TRANSIENT_OILWEAR_ONE VARCHAR NULL,
   TRANSIENT_OILWEAR_TWO VARCHAR NULL,
   TOTAL_TIME_ONE VARCHAR NULL,
   TOTAL_TIME_TWO VARCHAR NULL,
   PLATE_NUMBER VARCHAR NULL,
   VEHICLE_TYPE VARCHAR NULL,
   FUEL_TYPE VARCHAR NULL,
   AIR_CONDITION_STATUS VARCHAR NULL,
   SATELLITE_NUMBER VARCHAR NULL,
   OIL_TANK_ID_ONE VARCHAR NULL,
   OIL_TANK_ID_TWO VARCHAR NULL,
   OIL_TANK_MSG_LENGTH_ONE VARCHAR NULL,
   OIL_TANK_MSG_LENGTH_TWO VARCHAR NULL,
   FUEL_TEM_ONE VARCHAR NULL,
   FUEL_TEM_TWO VARCHAR NULL,
   ENVIRONMENT_TEM_ONE VARCHAR NULL,
   ENVIRONMENT_TEM_TWO VARCHAR NULL,
   FUEL_AMOUNT_ONE VARCHAR NULL,
   FUEL_AMOUNT_TWO VARCHAR NULL,
   FUEL_SPILL_ONE VARCHAR NULL,
   FUEL_SPILL_TWO VARCHAR NULL,
   OIL_TANK_ONE VARCHAR NULL,
   OIL_TANK_TWO VARCHAR NULL,
   AD_HEIGHT_ONE VARCHAR NULL,
   OIL_HEIGHT_ONE VARCHAR NULL,
   AD_HEIGHT_TWO VARCHAR NULL,
   OIL_HEIGHT_TWO VARCHAR NULL,
   MILEAGE_SPEED DOUBLE NULL,
   MILEAGE_TOTAL DOUBLE NULL,
   WINCH_STATUS INTEGER NULL,
   WINCH_ORIENTATION INTEGER NULL,
   WINCH_SPEED INTEGER NULL,
   WINCH_TIME INTEGER NULL,
   WINCH_COUNTER INTEGER NULL,
   WINCH_ROTATE_TIME INTEGER NULL,
   TEMP_VALUE_ONE INTEGER NULL,
   TEMP_TIME_ONE INTEGER NULL,
   TEMP_HIGH_LOW_ONE INTEGER NULL,
   TEMP_VALUE_TWO INTEGER NULL,
   TEMP_TIME_TWO INTEGER NULL,
   TEMP_HIGH_LOW_TWO INTEGER NULL,
   TEMP_VALUE_THREE INTEGER NULL,
   TEMP_TIME_THREE INTEGER NULL,
   TEMP_HIGH_LOW_THREE INTEGER NULL,
   TEMP_VALUE_FOUR INTEGER NULL,
   TEMP_TIME_FOUR INTEGER NULL,
   TEMP_HIGH_LOW_FOUR INTEGER NULL,
   TEMP_VALUE_FIVE INTEGER NULL,
   TEMP_TIME_FIVE INTEGER NULL,
   TEMP_HIGH_LOW_FIVE INTEGER NULL,
   WETNESS_VALUE_ONE INTEGER NULL,
   WETNESS_TIME_ONE INTEGER NULL,
   WETNESS_HIGH_LOW_ONE INTEGER NULL,
   WETNESS_VALUE_TWO INTEGER NULL,
   WETNESS_TIME_TWO INTEGER NULL,
   WETNESS_HIGH_LOW_TWO INTEGER NULL,
   WETNESS_VALUE_THREE INTEGER NULL,
   WETNESS_TIME_THREE INTEGER NULL,
   WETNESS_HIGH_LOW_THREE INTEGER NULL,
   WETNESS_VALUE_FOUR INTEGER NULL,
   WETNESS_TIME_FOUR INTEGER NULL,
   WETNESS_HIGH_LOW_FOUR INTEGER NULL,
   UPLOAD_TIME VARCHAR NULL,
   BATTERY_ELECTRICITY INTEGER NULL,
   BATTERY_VOLTAGE INTEGER NULL,
   SIGNAL_STRENGTH INTEGER NULL,
   TERMINAL_VERSION VARCHAR NULL,
   LOCATION_TYPE INTEGER NULL,
   ABNORMITY_STATUS INTEGER NULL,
   ABNORMITY_TYPE INTEGER NULL,
   ABNORMITY_LEVEL INTEGER NULL,
   ABNORMITY_FATIGUE_TIME INTEGER NULL,
   ABNORMITY_MEDIA_TOTAL INTEGER NULL,
   ABNORMITY_MEDIA_INFO VARCHAR NULL,
   DRIVER_STATUS INTEGER NULL,
   DRIVER_TYPE INTEGER NULL,
   DRIVER_LEVEL INTEGER NULL,
   DRIVER_MEDIA_TOTAL INTEGER NULL,
   DRIVER_RESERVE_VALUE INTEGER NULL,
   DRIVER_MEDIA_INFO VARCHAR NULL,
   AIR_COMP_STATUS INTEGER NULL,
   ORIGINAL_LONGTITUDE VARCHAR NULL,
   ORIGINAL_LATITUDE VARCHAR NULL,
   ROAD_MARKINGS_TYPE INTEGER NULL,
   ROAD_MARKINGS_DATA INTEGER NULL,
   LIMITING_VELOCITY INTEGER NULL,
   IO_ONE INTEGER NULL,
   IO_TWO INTEGER NULL,
   IO_THREE INTEGER NULL,
   IO_FOUR INTEGER NULL,
   SIGNAL_STATE BIGINT NULL,
   WORK_INSPECTION_METHOD_ONE INTEGER NULL,
   WORKING_POSITION_ONE INTEGER NULL,
   CONTINUE_TIME_ONE BIGINT NULL,
   CHECK_DATA_ONE DOUBLE NULL,
   WORK_TOTAL_ONE BIGINT NULL, 
   STANDBY_TOTAL_ONE BIGINT NULL, 
   HALT_TOTAL_ONE BIGINT NULL, 
   IMPORTANT_DATA_ONE INTEGER NULL, 
   WORK_INSPECTION_METHOD_TWO INTEGER NULL, 
   WORKING_POSITION_TWO  INTEGER NULL, 
   CONTINUE_TIME_TWO BIGINT NULL, 
   CHECK_DATA_TWO DOUBLE NULL, 
   WORK_TOTAL_TWO BIGINT NULL, 
   STANDBY_TOTAL_TWO BIGINT NULL, 
   HALT_TOTAL_TWO BIGINT NULL, 
   IMPORTANT_DATA_TWO INTEGER NULL,
   PROTOCOL_TYPE VARCHAR NULL,
   SUB_PROTOCOL_TYPE VARCHAR NULL,
   SATELLITES_NUMBER VARCHAR NULL,
   USER_CODE VARCHAR NULL,
   DEVICE_ELECTRIC INTEGER NULL,
   DRIVING_ELECTRIC INTEGER NULL,
   COLD_STORAGE INTEGER NULL,
   KEEP1 INTEGER NULL,
   KEEP2 INTEGER NULL,
   ALARM_SIGN INTEGER NULL,
   WIFI_INFO VARCHAR NULL,
   STATION_INFO VARCHAR NULL,
   HUMIDITY_SIGN INTEGER NULL,
   TEMPERATURE_SIGN INTEGER NULL,
   OIL_WEAR_SIGN INTEGER NULL,
   OILMASS_SIGN INTEGER NULL,
   MONITOR_TYPE INTEGER NULL,
   IO_OBJ_ONE VARCHAR NULL,
   IO_OBJ_TWO VARCHAR NULL,
   LIQUID_ONE_OBJ VARCHAR NULL,
   LIQUID_TWO_OBJ VARCHAR NULL,
   LIQUID_THREE_OBJ VARCHAR NULL,
   LIQUID_FOUR_OBJ VARCHAR NULL,
   LIQUID_FIVE_OBJ VARCHAR NULL,
   LIQUID_SIX_OBJ VARCHAR NULL,
   LIQUID_SEVEN_OBJ VARCHAR NULL,
   LIQUID_EIGHT_OBJ VARCHAR NULL,
   FLUCTUATE_VALUE_ONE DOUBLE NULL,
   FLUCTUATE_VALUE_TWO DOUBLE NULL,
   LOAD_OBJ_ONE VARCHAR NULL,
   LOAD_OBJ_TWO VARCHAR NULL,
   OBD_OBJ VARCHAR NULL,
   CONSTRAINT PK PRIMARY KEY (VEHICLE_ID,VTIME)
) COMPRESSION='snappy' SPLIT ON (decode('0F','HEX'),decode('1F','HEX'),decode('2F','HEX'),decode('3F','HEX'),decode('4F','HEX'),decode('5F','HEX'),decode('6F','HEX'),decode('7F','HEX'),decode('8F','HEX'),decode('9F','HEX'),decode('AF','HEX'),decode('BF','HEX'),decode('CF','HEX'),decode('DF','HEX'),decode('EF','HEX'));

CREATE INDEX POSITIONAL_INDEX on POSITIONAL(VTIME) INCLUDE(LONGTITUDE,LATITUDE) ASYNC;

CREATE TABLE POSITION_INFO
(
   ID VARCHAR NULL,
   LONGTITUDE VARCHAR NOT NULL,
   LATITUDE VARCHAR NOT NULL,
   ADCODE VARCHAR NULL,
   BUILDING VARCHAR NULL,
   BUILDING_TYPE VARCHAR NULL,
   CITY VARCHAR NULL,
   CITY_CODE VARCHAR NULL,
   DISTRICT VARCHAR NULL,
   NEIGHBORHOOD VARCHAR NULL,
   NEIGHBORHOOD_TYPE VARCHAR NULL,
   PROVINCE VARCHAR NULL,
   STREET VARCHAR NULL,
   STREET_NUMBER VARCHAR NULL,
   TOWNSHIP VARCHAR NULL,
   CROSSES VARCHAR NULL,
   FORMATTED_ADDRESS VARCHAR NULL,
   POIS VARCHAR NULL,
   ROADS VARCHAR NULL,
   CONSTRAINT PK PRIMARY KEY (LONGTITUDE,LATITUDE)
);

CREATE TABLE POSITION_STATISTIC
(
  VEHICLE_ID BINARY(16) NOT NULL,
   ADDRESS VARCHAR NOT NULL,
   ADDR_COUNT INTEGER,
   LONGTITUDE_NORTH VARCHAR NULL,
   LATITUDE_NORTH VARCHAR NULL,
   LONGTITUDE_SOUTH VARCHAR NULL,
   LATITUDE_SOUTH VARCHAR NULL,
   LONGTITUDE_EAST VARCHAR NULL,
   LATITUDE_EAST VARCHAR NULL,
   LONGTITUDE_WEST VARCHAR NULL,
   LATITUDE_WEST VARCHAR NULL,
   CONSTRAINT PK PRIMARY KEY (VEHICLE_ID,ADDRESS)
);

CREATE TABLE VEHICLE_ONLINE_MONTH
(
   ID VARCHAR NULL,
   VEHICLE_ID BINARY(16) NOT NULL,
   GROUP_ID BINARY(16) NOT NULL,
   MONTH_DATE BIGINT NOT NULL,
   DAY1 INTEGER NULL,
   DAY2 INTEGER NULL,
   DAY3 INTEGER NULL,
   DAY4 INTEGER NULL,
   DAY5 INTEGER NULL,
   DAY6 INTEGER NULL,
   DAY7 INTEGER NULL,
   DAY8 INTEGER NULL,
   DAY9 INTEGER NULL,
   DAY10 INTEGER NULL,
   DAY11 INTEGER NULL,
   DAY12 INTEGER NULL,
   DAY13 INTEGER NULL,
   DAY14 INTEGER NULL,
   DAY15 INTEGER NULL,
   DAY16 INTEGER NULL,
   DAY17 INTEGER NULL,
   DAY18 INTEGER NULL,
   DAY19 INTEGER NULL,
   DAY20 INTEGER NULL,
   DAY21 INTEGER NULL,
   DAY22 INTEGER NULL,
   DAY23 INTEGER NULL,
   DAY24 INTEGER NULL,
   DAY25 INTEGER NULL,
   DAY26 INTEGER NULL,
   DAY27 INTEGER NULL,
   DAY28 INTEGER NULL,
   DAY29 INTEGER NULL,
   DAY30 INTEGER NULL,
   DAY31 INTEGER NULL,
   
   CONSTRAINT PK PRIMARY KEY (VEHICLE_ID,GROUP_ID,MONTH_DATE)
) COMPRESSION='snappy' SPLIT ON (decode('0F','HEX'),decode('1F','HEX'),decode('2F','HEX'),decode('3F','HEX'),decode('4F','HEX'),decode('5F','HEX'),decode('6F','HEX'),decode('7F','HEX'),decode('8F','HEX'),decode('9F','HEX'),decode('AF','HEX'),decode('BF','HEX'),decode('CF','HEX'),decode('DF','HEX'),decode('EF','HEX'));

CREATE TABLE VEHICLE_MILE_MONTH
(
   ID VARCHAR NULL,
   VEHICLE_ID BINARY(16) NOT NULL,
   GROUP_ID BINARY(16) NOT NULL,
   MONTH_DATE BIGINT NOT NULL,
   DAY1 DOUBLE NULL,
   DAY2 DOUBLE NULL,
   DAY3 DOUBLE NULL,
   DAY4 DOUBLE NULL,
   DAY5 DOUBLE NULL,
   DAY6 DOUBLE NULL,
   DAY7 DOUBLE NULL,
   DAY8 DOUBLE NULL,
   DAY9 DOUBLE NULL,
   DAY10 DOUBLE NULL,
   DAY11 DOUBLE NULL,
   DAY12 DOUBLE NULL,
   DAY13 DOUBLE NULL,
   DAY14 DOUBLE NULL,
   DAY15 DOUBLE NULL,
   DAY16 DOUBLE NULL,
   DAY17 DOUBLE NULL,
   DAY18 DOUBLE NULL,
   DAY19 DOUBLE NULL,
   DAY20 DOUBLE NULL,
   DAY21 DOUBLE NULL,
   DAY22 DOUBLE NULL,
   DAY23 DOUBLE NULL,
   DAY24 DOUBLE NULL,
   DAY25 DOUBLE NULL,
   DAY26 DOUBLE NULL,
   DAY27 DOUBLE NULL,
   DAY28 DOUBLE NULL,
   DAY29 DOUBLE NULL,
   DAY30 DOUBLE NULL,
   DAY31 DOUBLE NULL,
   
   CONSTRAINT PK PRIMARY KEY (VEHICLE_ID,GROUP_ID,MONTH_DATE)
) COMPRESSION='snappy' SPLIT ON (decode('0F','HEX'),decode('1F','HEX'),decode('2F','HEX'),decode('3F','HEX'),decode('4F','HEX'),decode('5F','HEX'),decode('6F','HEX'),decode('7F','HEX'),decode('8F','HEX'),decode('9F','HEX'),decode('AF','HEX'),decode('BF','HEX'),decode('CF','HEX'),decode('DF','HEX'),decode('EF','HEX'));

CREATE TABLE GROUP_ONLINE_MONTH
(
   ID VARCHAR NULL,
   GROUP_ID BINARY(16) NOT NULL,
   MONTH_DATE BIGINT NOT NULL,
   DAY1 VARCHAR NULL,
   DAY2 VARCHAR NULL,
   DAY3 VARCHAR NULL,
   DAY4 VARCHAR NULL,
   DAY5 VARCHAR NULL,
   DAY6 VARCHAR NULL,
   DAY7 VARCHAR NULL,
   DAY8 VARCHAR NULL,
   DAY9 VARCHAR NULL,
   DAY10 VARCHAR NULL,
   DAY11 VARCHAR NULL,
   DAY12 VARCHAR NULL,
   DAY13 VARCHAR NULL,
   DAY14 VARCHAR NULL,
   DAY15 VARCHAR NULL,
   DAY16 VARCHAR NULL,
   DAY17 VARCHAR NULL,
   DAY18 VARCHAR NULL,
   DAY19 VARCHAR NULL,
   DAY20 VARCHAR NULL,
   DAY21 VARCHAR NULL,
   DAY22 VARCHAR NULL,
   DAY23 VARCHAR NULL,
   DAY24 VARCHAR NULL,
   DAY25 VARCHAR NULL,
   DAY26 VARCHAR NULL,
   DAY27 VARCHAR NULL,
   DAY28 VARCHAR NULL,
   DAY29 VARCHAR NULL,
   DAY30 VARCHAR NULL,
   DAY31 VARCHAR NULL,
   
   CONSTRAINT PK PRIMARY KEY (GROUP_ID,MONTH_DATE)
) COMPRESSION='snappy' SPLIT ON (decode('0F','HEX'),decode('1F','HEX'),decode('2F','HEX'),decode('3F','HEX'),decode('4F','HEX'),decode('5F','HEX'),decode('6F','HEX'),decode('7F','HEX'),decode('8F','HEX'),decode('9F','HEX'),decode('AF','HEX'),decode('BF','HEX'),decode('CF','HEX'),decode('DF','HEX'),decode('EF','HEX'));