CREATE DATABASE IF NOT EXISTS cmii;
USE cmii;
CREATE TABLE IF NOT EXISTS `dwd_reg_airspace_grid_detail_dd` (
  `geoCode` varchar(32) NOT NULL COMMENT '网格码',
  `areaCode` varchar(32) NOT NULL COMMENT '空域标识码',
  `areaCategory` tinyint NOT NULL COMMENT '空域类型，1：飞行活动空域 2：划设空域',
  `geoType` varchar(32) NULL COMMENT '	网格类型，2：二维网格，3：三维网格'
) ENGINE=OLAP
UNIQUE KEY(`geoCode`, `areaCode`)
COMMENT '空域网格明细表'
DISTRIBUTED BY HASH(`areaCode`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);


CREATE TABLE IF NOT EXISTS `dwd_reg_flight_act_grid_detail_dd` (
  `report_date` date NOT NULL COMMENT '活动日期',
  `geo_num4` varchar(32) NOT NULL COMMENT '二维网格码',
  `flight_code` varchar(32) NOT NULL COMMENT '飞行活动标识码'
) ENGINE=OLAP
UNIQUE KEY(`report_date`, `geo_num4`, `flight_code`)
COMMENT '飞行活动网格明细表'
AUTO PARTITION BY RANGE (date_trunc(`report_date`, 'month'))
()
DISTRIBUTED BY HASH(`report_date`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_2dcode_dd` (
  `geo2DCode` varchar(32) NOT NULL COMMENT '二维网格码',
  `maxLat` double NULL COMMENT '最大纬度',
  `maxLng` double NULL COMMENT '最大经度',
  `minLat` double NULL COMMENT '最小纬度',
  `minLng` double NULL COMMENT '最小经度',
  `geo2DCodeInteger` bigint NULL COMMENT '二维网格十进制整型',
  `min2DCodeInteger` bigint NULL COMMENT '子网格十进制整型',
  `max2DCodeInteger` bigint NULL COMMENT '子网格十进制整型'
) ENGINE=OLAP
UNIQUE KEY(`geo2DCode`)
COMMENT '二维网格位置明细表'
DISTRIBUTED BY HASH(`geo2DCode`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);


CREATE TABLE IF NOT EXISTS `dwd_reg_grid_3dcode_dd` (
  `geo3DCode` varchar(32) NOT NULL COMMENT '三维网格码',
  `maxLat` double NOT NULL COMMENT '最大纬度',
  `maxLng` double NULL COMMENT '最大经度',
  `minLat` double NULL COMMENT '最小纬度',
  `minLng` double NULL COMMENT '最小经度',
  `minHeight` double NULL COMMENT '最小高度',
  `maxHeight` double NULL COMMENT '最大高度',
  `min3DCodeOx` varchar(32) NULL COMMENT '最小网格码八进制',
  `max3DCodeOx` varchar(32) NULL COMMENT '最大网格码八进制'
) ENGINE=OLAP
UNIQUE KEY(`geo3DCode`)
COMMENT '三维网格明细表'
DISTRIBUTED BY HASH(`geo3DCode`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_network_signal_detail_dd` (
  `geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码',
  `snr` double NULL COMMENT '信噪比',
  `rsrp` double NULL COMMENT '参考信号接收功率,单位dBm',
  `rsrq` double NULL COMMENT '参考信号接收质量',
  `pci` varchar(320) NULL COMMENT '网络小区编号',
  `latest_lat84` double NULL COMMENT '最近一次测量点纬度',
  `latest_lng84` double NULL COMMENT '最近一次测量点经度',
  `latest_height` double NULL COMMENT '最近一次测量点相对高度',
  `latest_time` datetime NULL COMMENT '最近一次测量时间'
) ENGINE=OLAP
UNIQUE KEY(`geo_3d_code`)
COMMENT '网格信号强度明细表'
DISTRIBUTED BY HASH(`geo_3d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_risk_detail_dd` (
  `geo_2d_code` varchar(32) NOT NULL COMMENT '二维网格码',
  `height` smallint NOT NULL COMMENT '相对高度',
  `risk_level` tinyint NULL COMMENT '风险等级,-1:缺数据无法计算,1:低风险,2:中风险,3:高风险',
  `calculation_time` datetime NULL COMMENT '计算时间'
) ENGINE=OLAP
UNIQUE KEY(`geo_2d_code`, `height`)
COMMENT '网格风险等级明细表'
DISTRIBUTED BY HASH(`geo_2d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);


CREATE TABLE IF NOT EXISTS `dwd_reg_model_grid_detail_dd` (
  `model_id` varchar(32) NOT NULL COMMENT '模型ID',
  `geo_3d_code` varchar(32) NOT NULL COMMENT '二维网格码',
  `min_lng` double NOT NULL COMMENT '最小经度',
  `min_lat` double NULL COMMENT '最小纬度',
  `max_lng` double NULL COMMENT '最大经度',
  `max_lat` double NULL COMMENT '最大纬度',
  `min_height` double NULL COMMENT '最小高度',
  `max_height` double NULL COMMENT '最大高度'
) ENGINE=OLAP
UNIQUE KEY(`model_id`, `geo_3d_code`)
COMMENT '模型网格明细表'
DISTRIBUTED BY HASH(`model_id`, `geo_3d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dws_reg_flight_act_grid_1d` (
  `report_date` date NOT NULL COMMENT '活动日期',
  `geo_num4` varchar(32) NOT NULL COMMENT '二维网格码',
  `flight_total` bigint NOT NULL COMMENT '飞行活动统计数量'
) ENGINE=OLAP
UNIQUE KEY(`report_date`, `geo_num4`)
COMMENT '飞行活动网格流量聚合表'
AUTO PARTITION BY RANGE (date_trunc(`report_date`, 'month'))
()
DISTRIBUTED BY HASH(`report_date`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dws_reg_grid_population_1d` (
  `geo_2d_code` varchar(32) NOT NULL COMMENT '(二维网格码17级)',
  `time` datetime NOT NULL COMMENT '统计时间',
  `population` int NULL COMMENT '人口数',
  `population_density` double NULL COMMENT '人口密度',
  `dis_code` varchar(32) NULL COMMENT '区域码'
) ENGINE=OLAP
UNIQUE KEY(`geo_2d_code`, `time`)
COMMENT '网格人口统计日表'
AUTO PARTITION BY RANGE (date_trunc(`time`, 'month'))
()
DISTRIBUTED BY HASH(`geo_2d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `ods_bridge_object_track_sense` (
  `object_id` varchar(64) NOT NULL COMMENT '目标唯一编号',
  `provider_code` varchar(32) NOT NULL COMMENT '设备提供方编码',
  `device_type` int NOT NULL COMMENT '设备类型',
  `device_id` varchar(128) NOT NULL COMMENT '设备id',
  `pt_time` bigint NOT NULL COMMENT '上报时间戳',
  `msg_cnt` bigint NULL COMMENT '消息编号',
  `longitude` double NOT NULL COMMENT '经度',
  `latitude` double NOT NULL COMMENT '纬度',
  `altitude` float NULL,
  `height` float NULL COMMENT '距地高度',
  `speed` float NOT NULL COMMENT '目标速度',
  `time` datetime(3) NOT NULL COMMENT '探测时间',
  `extension` variant NULL COMMENT '扩展信息'
) ENGINE=OLAP
DUPLICATE KEY(`object_id`)
COMMENT '设备目标探测数据表'
AUTO PARTITION BY RANGE (date_trunc(`time`, 'month'))()
DISTRIBUTED BY HASH(`object_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

CREATE TABLE IF NOT EXISTS `ods_bridge_uas_track_report` (
  `uas_id` varchar(64) NOT NULL COMMENT '无人机识别码',
  `third_code` varchar(32) NOT NULL COMMENT '三方平台编码',
  `angle` float NOT NULL COMMENT '航迹角',
  `speed` float NOT NULL COMMENT '地速',
  `latitude` double NOT NULL COMMENT '纬度',
  `longitude` double NOT NULL COMMENT '经度',
  `altitude` double NOT NULL COMMENT '海拔高度',
  `height` float NULL COMMENT '距地高度',
  `time` datetime(3) NOT NULL COMMENT '上报时间',
  `extension` variant NULL COMMENT '扩展信息'
) ENGINE=OLAP
DUPLICATE KEY(`uas_id`)
COMMENT '合作无人机上报数据表'
AUTO PARTITION BY RANGE (date_trunc(`time`, 'month'))()
DISTRIBUTED BY HASH(`uas_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);


CREATE TABLE IF NOT EXISTS `ods_reg_warn_item` (
  `uas_id` varchar(256) NOT NULL COMMENT '无人机识别码（或探测目标id',
  `warn_code` varchar(32) NOT NULL COMMENT '告警唯一标识',
  `id` bigint NOT NULL COMMENT 'mysql自增ID',
  `id_type` tinyint NULL COMMENT '无人机的id类型,1、三方平台 2、监管无人机 3、RemoteID  4.探测轨迹',
  `cooperate` tinyint NOT NULL COMMENT '是否为合作类 1.合作； 0.非合作',
  `provider_code` varchar(64) NULL COMMENT '设备来源厂家编号',
  `device_id` varchar(64) NULL COMMENT '数据来源设备id（设备方定义id)',
  `device_type` int NULL COMMENT '来源设备类型(0、5G-A基站，（5ga） 1、雷达设备，(radar)2、频谱设备， (spec)3、光电设备，(oe)4、反制设备,（cm）5、诱骗设备，(dec)6、干扰设备，(ifr) 7、指挥车，(cv)8、察打一体， (isrs)999、其他设备(other))',
  `event` tinyint NOT NULL COMMENT '告警类型(1 偏航预警；2 闯入管制区；3 闯入临时空域；4.非合飞行）',
  `warn_key` varchar(32) NULL COMMENT '告警特征值(uasId、event、refcode等产生）',
  `warning_content` varchar(256) NULL COMMENT '告警信息,主要是存放各种告警说明',
  `start_time` datetime NOT NULL COMMENT '开始时间',
  `end_time` datetime NULL COMMENT '结束时间',
  `status` tinyint NOT NULL COMMENT '告警状态：0：告警中，1:告警结束',
  `longitude84` double NULL COMMENT '产生告警时经度(WGS-84)',
  `latitude84` double NULL COMMENT '产生告警时纬度(WGS-84)',
  `height` double NULL COMMENT '产生告警时高度',
  `altitude` double NULL COMMENT '拔高度，单位米',
  `speed` double NULL COMMENT '产生告警时的地速度',
  `end_type` tinyint NULL COMMENT '结束类型，0：自动结束，1：手动结束',
  `ref_area_code` varchar(32) NULL COMMENT 'event为2，3时：首次产生告警时关联空域编号； event为3时：飞行活动编号',
  `ref_area_name` varchar(160) NULL COMMENT 'event为2，3时：首次产生告警时关联空域名称.只存第一个空域名称',
  `uav_update_time` datetime NULL COMMENT '无人机信息更新时间(以下为告警最新对应无人机信息)',
  `uav_lng` double NULL COMMENT '最近1次告警时无人机经度',
  `uav_lat` double NULL COMMENT '最近1次告警时无人机纬度',
  `uav_height` double NULL COMMENT '最近1次告警时无人机高度',
  `uav_altitude` double NULL COMMENT '最近1次告警时无人机的拔高度，单位米',
  `uav_speed` double NULL COMMENT '最近1次告警时无人机地速度',
  `is_deal` tinyint NULL COMMENT '是否已经处置。 0.未处置 1.已处置',
  `city_code` varchar(32) NULL COMMENT '城市编号',
  `update_at` datetime NULL COMMENT '修改时间',
  `create_at` datetime NULL COMMENT '创建时间'
) ENGINE=OLAP
DUPLICATE KEY(`uas_id`)
COMMENT '设备目标探测数据表'
AUTO PARTITION BY RANGE (date_trunc(`start_time`, 'month'))()
DISTRIBUTED BY HASH(`uas_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

CREATE TABLE IF NOT EXISTS `ods_reg_grid_network_signal_loss` (
  `geo_2d_code` varchar(32) NOT NULL COMMENT '二维网格编码',
  `flight_height` smallint NOT NULL COMMENT '相对空域高度,单位:m',
  `path_loss` double NOT NULL COMMENT '预估信号损耗，负值，数值越大表示信号质量越好',
  `path_loss_normalized` double NOT NULL COMMENT '预估信号损耗标准化计算值'
) ENGINE=OLAP
DUPLICATE KEY(`geo_2d_code`)
COMMENT '二维网格网络信号损耗表'
DISTRIBUTED BY HASH(`geo_2d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

CREATE TABLE IF NOT EXISTS `ods_reg_grid_poi_density` (
  `geo_2d_code` varchar(32) NOT NULL COMMENT '二维网格编码',
  `poi_density` double NOT NULL COMMENT 'poi密度',
  `poi_density_normalized` double NOT NULL COMMENT 'poi密度标准化计算值'
) ENGINE=OLAP
DUPLICATE KEY(`geo_2d_code`)
COMMENT '二维网格poi密度表'
DISTRIBUTED BY HASH(`geo_2d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

CREATE TABLE IF NOT EXISTS `ods_reg_grid_pop_exposure_risk` (
  `geo_2d_code` varchar(32) NOT NULL COMMENT '二维网格编码',
  `pop_exposure_risk` double NOT NULL COMMENT '人口暴露风险',
  `pop_exposure_risk_normalized` double NOT NULL COMMENT '人口暴露风险标准化计算值'
) ENGINE=OLAP
DUPLICATE KEY(`geo_2d_code`)
COMMENT '二维网格人口暴露风险'
DISTRIBUTED BY HASH(`geo_2d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_tmp_airline_grid_detail_dd` (
  `airLineId` varchar(128) NOT NULL COMMENT '航线ID',
  `geo3DCode` varchar(32) NOT NULL COMMENT '三维网格码',
  `maxLat` double NOT NULL COMMENT '最大纬度',
  `maxLng` double NULL COMMENT '最大经度',
  `minLat` double NULL COMMENT '最小纬度',
  `minLng` double NULL COMMENT '最小经度',
  `minHeight` double NULL COMMENT '最小高度',
  `maxHeight` double NULL COMMENT '最大高度'
) ENGINE=OLAP
UNIQUE KEY(`airLineId`, `geo3DCode`)
COMMENT '三维网格明细表'
DISTRIBUTED BY HASH(`airLineId`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_mock_network_signal_detail_dd` (
  `data_access_id` int NULL COMMENT '数据接入信息id',
  `geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码',
  `sampling_time` date NOT NULL COMMENT '采样时间',
  `rsrp` double NULL COMMENT '参考信号接收功率,单位dBm'
) ENGINE=OLAP
UNIQUE KEY(`data_access_id`, `geo_3d_code`, `sampling_time`)
COMMENT 'radio map模拟网格信号强度明细表'
DISTRIBUTED BY HASH(`data_access_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `ods_external_attack_track_report` (
  `source` varchar(255) NULL COMMENT '数据来源',
  `type` varchar(255) NULL COMMENT '数据来源类型',
  `attack_id` varchar(255) NULL COMMENT '告警 id',
  `src_ip` varchar(255) NULL COMMENT '源 IP',
  `src_port` int NULL COMMENT '源端口',
  `timestamp` bigint NULL COMMENT '时间戳',
  `dest_ip` varchar(255) NULL COMMENT '目的地ip',
  `dest_port` int NULL COMMENT '目的端口',
  `attack_type` varchar(255) NULL COMMENT '攻击类型',
  `attack_name` varchar(255) NULL COMMENT '攻击名称',
  `ext` variant NULL COMMENT '扩展字段'
) ENGINE=OLAP
DUPLICATE KEY(`source`, `type`)
COMMENT '外部攻击数据'
DISTRIBUTED BY HASH(`source`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

CREATE TABLE IF NOT EXISTS `ods_external_data_track_report` (
  `source` varchar(20) NOT NULL COMMENT '数据来源',
  `type` varchar(20) NOT NULL COMMENT '数据类型',
  `id` bigint NOT NULL AUTO_INCREMENT(1) COMMENT 'ID',
  `def_date` datetime(3) NOT NULL COMMENT '创建时间',
  `param` variant NOT NULL COMMENT '数据实体'
) ENGINE=OLAP
DUPLICATE KEY(`source`, `type`)
COMMENT '设备目标探测数据表'
AUTO PARTITION BY RANGE (date_trunc(`def_date`, 'month'))()
DISTRIBUTED BY HASH(`source`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

CREATE TABLE IF NOT EXISTS `ods_external_micro_meteorology` (
  `data_access_id` int NOT NULL COMMENT '数据接入信息id',
  `longitude` decimal(9,0) NOT NULL COMMENT '网格在投影坐标系下的X坐标',
  `latitude` decimal(9,0) NOT NULL COMMENT '网格在投影坐标系下的Y坐标',
  `altitude` decimal(9,0) NOT NULL COMMENT '网格在投影坐标系下的Z坐标',
  `size` tinyint NOT NULL COMMENT '网格大小(m)',
  `horizontal_wind_speed` double NULL COMMENT '水平风速(m/s)',
  `horizontal_direction` double NULL COMMENT '水平风向',
  `vertical_wind_speed` double NULL COMMENT '垂直风速(m/s)',
  `time` datetime NOT NULL COMMENT '时间',
  `temperature` double NULL COMMENT '温度',
  `humidity` double NULL COMMENT '湿度',
  `air_pressure` double NULL COMMENT '气压'
) ENGINE=OLAP
DUPLICATE KEY(`data_access_id`)
COMMENT '微气象数据表'
AUTO PARTITION BY RANGE (date_trunc(`time`, 'day'))()
DISTRIBUTED BY HASH(`altitude`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_city_detail_dd` (
  `geo_2d_code` varchar(32) NOT NULL COMMENT '二维网格码',
  `city_code` int NOT NULL COMMENT '城市码'
) ENGINE=OLAP
UNIQUE KEY(`geo_2d_code`)
COMMENT '网格城市明细表'
DISTRIBUTED BY HASH(`geo_2d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_uav_capacity_detail_dd` (
  `geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码,20级64m',
  `capacity_micro` double NOT NULL COMMENT '微型',
  `capacity_light` double NULL COMMENT '轻型',
  `capacity_small` double NULL COMMENT '小型',
  `capacity_medium` double NULL COMMENT '中型',
  `capacity_large` double NULL COMMENT '大型'
) ENGINE=OLAP
UNIQUE KEY(`geo_3d_code`)
COMMENT '网格风险等级明细表'
DISTRIBUTED BY HASH(`geo_3d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_ground_risk_detail_dd` (
  `geo_2d_code` varchar(32) NOT NULL COMMENT '二维网格码,20级64m',
  `fatality_area_normalize` double NOT NULL COMMENT '死亡区域比例',
  `risk_normalize` double NULL COMMENT '风险比例',
  `risk_level` tinyint NOT NULL COMMENT '风险等级,1:低风险,2:中风险,3:高风险'
) ENGINE=OLAP
UNIQUE KEY(`geo_2d_code`)
COMMENT '地面碰撞风险明细表'
DISTRIBUTED BY HASH(`geo_2d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_flight_risk_detail_dd` (
  `geo_3d_code` varchar(20) NOT NULL COMMENT '三维网格码,20级64m',
  `hour` tinyint NOT NULL COMMENT '模拟时间,0-23时',
  `risk_radio` double NOT NULL COMMENT '风险比例',
  `risk_level` tinyint NOT NULL COMMENT '风险等级,1:低风险,2:中风险,3:高风险'
) ENGINE=OLAP
UNIQUE KEY(`geo_3d_code`, `hour`)
COMMENT '网格风险等级明细表'
DISTRIBUTED BY HASH(`geo_3d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_comprehensive_risk_detail_dd` (
  `geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码,20级64m',
  `hour` tinyint NULL COMMENT '模拟时间,0-23时',
  `path_loss` double NOT NULL COMMENT '信号丢失',
  `fatality_area_normalize` double NOT NULL COMMENT '死亡区域标准化',
  `risk_normalize` double NULL COMMENT '风险标准化',
  `rsrp_normalize` double NULL COMMENT 'rsrp标准化',
  `total_risk` double NULL COMMENT '总风险',
  `risk_level` tinyint NOT NULL COMMENT '风险等级,1:低风险,2:中风险,3:高风险'
) ENGINE=OLAP
UNIQUE KEY(`geo_3d_code`, `hour`)
COMMENT '地面碰撞风险明细表'
DISTRIBUTED BY HASH(`geo_3d_code`, `hour`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_3d_xyz_risk_detail_dd` (
  `geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码',
  `max_Y` int NOT NULL COMMENT '最大纬度',
  `max_X` int NULL COMMENT '最大经度',
  `min_Y` int NULL COMMENT '最小纬度',
  `min_X` int NULL COMMENT '最小经度',
  `min_Z` int NULL COMMENT '最小高度',
  `max_Z` int NULL COMMENT '最大高度',
  `building` tinyint NULL COMMENT '是否建筑.0:无,1:有',
  `risk_coefficient` double NULL COMMENT '风险系数'
) ENGINE=OLAP
UNIQUE KEY(`geo_3d_code`)
COMMENT '三维网格风险明细表'
DISTRIBUTED BY HASH(`geo_3d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_3d_risk_detail_dd` (
  `geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码',
  `max_lat` double NOT NULL COMMENT '最大纬度',
  `max_lng` double NULL COMMENT '最大经度',
  `min_lat` double NULL COMMENT '最小纬度',
  `min_lng` double NULL COMMENT '最小经度',
  `min_height` double NULL COMMENT '最小高度',
  `max_height` double NULL COMMENT '最大高度',
  `x` bigint NULL COMMENT 'x',
  `y` bigint NULL COMMENT 'y',
  `z` bigint NULL COMMENT 'z',
  `building` tinyint NULL COMMENT '是否建筑.0:无,1:有',
  `risk_coefficient` double NULL COMMENT '风险系数'
) ENGINE=OLAP
UNIQUE KEY(`geo_3d_code`)
COMMENT '三维网格风险明细表'
DISTRIBUTED BY HASH(`geo_3d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE `ods_reg_oe_device_object_trace` (
  `device_id` varchar(128) NOT NULL COMMENT '设备id',
  `id` varchar(32) NOT NULL COMMENT 'id',
  `object_id` varchar(128) NOT NULL COMMENT '目标id',
  `platform_code` varchar(32) NOT NULL COMMENT '平台编码',
  `device_name` varchar(128) NOT NULL COMMENT '设备名称',
  `device_longitude` double NULL COMMENT '设备经度',
  `device_latitude` double NULL COMMENT '设备纬度',
  `device_altitude` float NULL COMMENT '设备海拔高度',
  `device_local_altitude` double NULL COMMENT '设备所在地面海拔高度',
  `device_type` int NULL COMMENT '设备类型',
  `duration` int NULL COMMENT '时长',
  `file_size` bigint NULL COMMENT '文件大小',
  `zone_id` bigint NULL COMMENT '防区id',
  `zone_name` varchar(128) NULL COMMENT '防区名称',
  `zone_core_area` text NULL COMMENT '核心区域',
  `zone_handle_area` text NULL COMMENT '处置区域',
  `zone_warning_area` text NULL COMMENT '预警区域',
  `pt_time` bigint NOT NULL COMMENT '上报时间戳',
  `start_time` datetime(3) NOT NULL COMMENT '追踪开始时间',
  `end_time` datetime(3) NOT NULL COMMENT '追踪结束时间',
  `ai_object` text NULL COMMENT 'ai检测结果',
  `object_type` variant NULL COMMENT '结果类型'
) ENGINE=OLAP
DUPLICATE KEY(`device_id`)
COMMENT '设备目标探测数据表'
AUTO PARTITION BY RANGE (date_trunc(`start_time`, 'month'))()
DISTRIBUTED BY HASH(`device_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

CREATE TABLE `ods_reg_oe_device_ai_alarm` (
  `device_id` varchar(128) NOT NULL COMMENT '设备id',
  `id` varchar(128) NOT NULL COMMENT 'id',
  `device_name` varchar(128) NOT NULL COMMENT '设备名称',
  `device_type` int NOT NULL COMMENT '探测源类型',
  `zone_name` varchar(128) NOT NULL COMMENT '防区名称',
  `object_id` varchar(128) NULL COMMENT '目标id',
  `file_id` varchar(32) NOT NULL COMMENT '文件id',
  `file_size` bigint NOT NULL COMMENT '文件大小',
  `platform_code` varchar(32) NOT NULL COMMENT '平台编码',
  `pt_time` bigint NOT NULL COMMENT '上报时间戳',
  `msg_cnt` bigint NULL COMMENT '消息编号',
  `time` datetime(3) NOT NULL COMMENT '检测时间',
  `record_id` varchar(64) NULL COMMENT '关联视频id',
  `ai_object` text NULL COMMENT 'ai检测结果',
  `object_type` variant NOT NULL COMMENT '结果类型',
  `zone_id` varchar(128) NULL COMMENT '防区id'
) ENGINE=OLAP
UNIQUE KEY(`device_id`, `id`)
COMMENT '设备目标探测数据表'
DISTRIBUTED BY HASH(`device_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_2d_property_dd` (
  `geo_2d_code` varchar(32) NOT NULL COMMENT '二维网格码',
  `zone_id` varchar(32) NOT NULL COMMENT '验证区id',
  `green_area` double NULL COMMENT '绿地面积,预处理空间交互后得出',
  `water_area` double NULL COMMENT '水域面积,预处理空间交互后得出',
  `road_area` double NULL COMMENT '道路面积,预处理空间交互后得出',
  `buildings` text NULL COMMENT '建筑物面积和高列表,预处理空间交互后得出',
  `population_density` double NULL COMMENT '人口密度,预处理从人口热力表查询',
  `max_lat` double NOT NULL COMMENT '最大纬度',
  `max_lng` double NOT NULL COMMENT '最大经度',
  `min_lat` double NOT NULL COMMENT '最小纬度',
  `min_lng` double NOT NULL COMMENT '最小经度'
) ENGINE=OLAP
UNIQUE KEY(`geo_2d_code`, `zone_id`)
COMMENT '二维网格属性表'
DISTRIBUTED BY HASH(`geo_2d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_3d_property_dd` (
  `geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码',
  `zone_id` varchar(32) NOT NULL COMMENT '验证区id',
  `green_area` double NULL COMMENT '绿地面积,预处理空间交互后得出',
  `water_area` double NULL COMMENT '水域面积,预处理空间交互后得出',
  `road_area` double NULL COMMENT '道路面积,预处理空间交互后得出',
  `buildings` text NULL COMMENT '建筑物面积和高列表,预处理空间交互后得出',
  `population_density` double NULL COMMENT '人口密度,预处理从人口热力表查询',
  `signal_strength` double NULL COMMENT '网络信号,空域评估预处理从网络信号表查询',
  `collection_time` datetime NULL COMMENT '网络信号,空域评估预处理从网络信号表查询',
  `historical_flight_flow` datetime NULL COMMENT '飞行流量,预处理从飞行流量表查询一小时内的数据打码统计',
  `max_y` int NULL COMMENT 'max_Y,预处理坐标转换得出(未使用)',
  `max_x` int NULL COMMENT 'max_X,预处理坐标转换得出(未使用)',
  `min_y` int NULL COMMENT 'min_Y,预处理坐标转换得出(未使用)',
  `min_x` int NULL COMMENT 'min_X,预处理坐标转换得出(未使用)',
  `min_z` int NULL COMMENT 'min_Z,预处理坐标转换得出(未使用)',
  `max_z` int NULL COMMENT 'max_Z,预处理坐标转换得出(未使用)',
  `building` tinyint NULL COMMENT '是否建筑.0:无,1:有',
  `risk_coefficient` double NULL COMMENT '综合评估风险系数,预处理综合风险评估得出(未使用)',
  `max_lat` double NOT NULL COMMENT '最大纬度',
  `max_lng` double NOT NULL COMMENT '最大经度',
  `max_height` double NOT NULL COMMENT '最大高度',
  `min_lat` double NOT NULL COMMENT '最小纬度',
  `min_lng` double NOT NULL COMMENT '最小经度',
  `min_height` double NOT NULL COMMENT '最小高度'
) ENGINE=OLAP
UNIQUE KEY(`geo_3d_code`, `zone_id`)
COMMENT '三维网格属性表'
DISTRIBUTED BY HASH(`geo_3d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_algorithm_risk_detail_dd` (
  `geo_3d_code` varchar(32) NOT NULL COMMENT '网格码',
  `ground_collision_risk_score` double NULL COMMENT '地面碰撞风险得分',
  `ground_collision_risk_level` int NULL COMMENT '地面碰撞风险等级',
  `air_flight_risk_score` double NULL COMMENT '空中飞行风险得分',
  `air_flight_risk_level` int NULL COMMENT '空中飞行风险等级',
  `total_risk_score` double NULL COMMENT '综合风险得分',
  `total_risk_level` int NULL COMMENT '综合风险等级'
) ENGINE=OLAP
UNIQUE KEY(`geo_3d_code`)
COMMENT '网格风险等级明细表'
DISTRIBUTED BY HASH(`geo_3d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_model_release_dd` (
  `geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码',
  `building_height` double NULL COMMENT '建筑物高度,shp解析后得出',
  `area` double NULL COMMENT '土地类型在网格内的面积,shp解析打码再做空间交互后得出',
  `geometry` text NULL COMMENT '土地类型在网格内的geometry,shp解析打码再做空间交互后得出',
  `model_id` varchar(32) NOT NULL COMMENT '模型ID',
  `min_lng` double NULL COMMENT '最小经度',
  `min_lat` double NULL COMMENT '最小纬度',
  `max_lng` double NULL COMMENT '最大经度',
  `max_lat` double NULL COMMENT '最大纬度',
  `min_height` double NULL COMMENT '最小高度',
  `max_height` double NULL COMMENT '最大高度'
) ENGINE=OLAP
UNIQUE KEY(`geo_3d_code`)
COMMENT '网格模型发布明细表'
DISTRIBUTED BY HASH(`geo_3d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_network_signal_release_dd` (
  `geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码',
  `snr` double NULL COMMENT '信噪比',
  `rsrp` double NULL COMMENT '参考信号接收功率,单位dBm',
  `rsrq` double NULL COMMENT '参考信号接收质量',
  `pci` varchar(32) NULL COMMENT '网络小区编号',
  `collection_time` datetime NULL COMMENT '信号采集时间',
  `max_lat` double NOT NULL COMMENT '最大纬度',
  `max_lng` double NULL COMMENT '最大经度',
  `min_lat` double NULL COMMENT '最小纬度',
  `min_lng` double NULL COMMENT '最小经度',
  `min_height` double NULL COMMENT '最小高度',
  `max_height` double NULL COMMENT '最大高度'
) ENGINE=OLAP
UNIQUE KEY(`geo_3d_code`)
COMMENT '网格网络信号发布明细表'
DISTRIBUTED BY HASH(`geo_3d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"function_column.sequence_col" = "collection_time",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `dwd_reg_grid_population_release_dd` (
  `geo_2d_code` varchar(32) NOT NULL COMMENT '二维网格码',
  `population` int NULL COMMENT '人口数',
  `population_density` double NULL COMMENT '人口密度',
  `collection_time` datetime NULL COMMENT '人口采集时间',
  `min_lng` double NOT NULL COMMENT '最小经度',
  `min_lat` double NOT NULL COMMENT '最小纬度',
  `max_lng` double NOT NULL COMMENT '最大经度',
  `max_lat` double NOT NULL COMMENT '最大纬度'
) ENGINE=OLAP
UNIQUE KEY(`geo_2d_code`)
COMMENT '网格人口统计发布明细表'
DISTRIBUTED BY HASH(`geo_2d_code`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"function_column.sequence_col" = "collection_time",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `ods_reg_grid_land` (
  `load_id` varchar(32) NOT NULL COMMENT '导入批次id,后端生成',
  `geo_2d_code` varchar(32) NOT NULL COMMENT '二维网格码(20级)',
  `shape_type` int NOT NULL COMMENT '土地利用类型,客户端输入,0:建筑物,1:绿地,2:路网,3:水域',
  `hash_geometry` varchar(255) NOT NULL COMMENT '原始geometry hash',
  `geometry` text NULL COMMENT '原始geometry',
  `load_time` datetime NOT NULL COMMENT '导入时间,后端生成',
  `load_name` varchar(32) NOT NULL COMMENT '导入名称,客户端输入',
  `building_height` double NULL COMMENT '建筑物高度,shp解析后得出',
  `land_id` varchar(32) NOT NULL COMMENT '土地id,shp解析后为每个geometry随机生成id',
  `area` double NOT NULL COMMENT '土地类型在网格内的面积,shp解析打码再做空间交互后得出',
  `release_status` int NOT NULL COMMENT '发布状态，0:未发布,1:发布',
  `max_lat` double NOT NULL COMMENT '最大纬度',
  `max_lng` double NOT NULL COMMENT '最大经度',
  `min_lat` double NOT NULL COMMENT '最小纬度',
  `min_lng` double NOT NULL COMMENT '最小经度'
) ENGINE=OLAP
UNIQUE KEY(`load_id`, `geo_2d_code`, `shape_type`, `hash_geometry`)
COMMENT '网格土地利用类型源表'
DISTRIBUTED BY HASH(`load_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `ods_reg_grid_model` (
  `load_id` varchar(32) NOT NULL COMMENT '导入批次id,后端生成',
  `geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码(22级)',
  `load_time` datetime NOT NULL COMMENT '导入时间,后端生成',
  `load_name` varchar(32) NULL COMMENT '导入名称,客户端输入',
  `building_height` double NULL COMMENT '建筑物高度,shp解析后得出',
  `area` double NULL COMMENT '面积',
  `geometry` text NULL COMMENT '原始geometry',
  `model_id` varchar(32) NOT NULL COMMENT '模型id,shp解析后为每个geometry随机生成id',
  `release_status` int NOT NULL COMMENT '发布状态，0:未发布,1:发布',
  `min_lng` double NOT NULL COMMENT '最小经度',
  `min_lat` double NOT NULL COMMENT '最小纬度',
  `max_lng` double NOT NULL COMMENT '最大经度',
  `max_lat` double NOT NULL COMMENT '最大纬度',
  `min_height` double NOT NULL COMMENT '最小高度',
  `max_height` double NOT NULL COMMENT '最大高度'
) ENGINE=OLAP
UNIQUE KEY(`load_id`, `geo_3d_code`)
COMMENT '网格模型源表'
DISTRIBUTED BY HASH(`load_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `ods_reg_grid_network_signal` (
  `load_id` varchar(32) NOT NULL COMMENT '导入批次id,后端生成',
  `geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码(20级)',
  `load_time` datetime NOT NULL COMMENT '导入时间,后端生成',
  `load_name` varchar(32) NULL COMMENT '导入名称,客户端输入',
  `snr` double NULL COMMENT '信噪比,客户端输入',
  `rsrp` double NULL COMMENT '参考信号接收功率,单位dBm,客户端输入',
  `rsrq` double NULL COMMENT '参考信号接收质量,客户端输入',
  `pci` varchar(32) NULL COMMENT '网络小区编号,客户端输入',
  `collection_time` datetime NOT NULL COMMENT '信号采集时间',
  `release_status` int NOT NULL COMMENT '发布状态，0:未发布,1:发布',
  `max_lat` double NOT NULL COMMENT '最大纬度',
  `max_lng` double NOT NULL COMMENT '最大经度',
  `min_lat` double NOT NULL COMMENT '最小纬度',
  `min_lng` double NOT NULL COMMENT '最小经度',
  `min_height` double NOT NULL COMMENT '最小高度',
  `max_height` double NOT NULL COMMENT '最大高度'
) ENGINE=OLAP
UNIQUE KEY(`load_id`, `geo_3d_code`)
COMMENT '网格网络信号源表'
DISTRIBUTED BY HASH(`load_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `ods_reg_grid_population` (
  `load_id` varchar(32) NOT NULL COMMENT '导入批次id,后端生成',
  `geo_2d_code` varchar(32) NOT NULL COMMENT '二维网格码(20级)',
  `load_time` datetime NOT NULL COMMENT '导入时间,后端生成',
  `load_name` varchar(32) NULL COMMENT '导入名称,客户端输入',
  `time` datetime NULL COMMENT '统计时间,客户端输入',
  `population` int NULL COMMENT '人口数,客户端输入',
  `population_density` double NULL COMMENT '人口密度,客户端输入',
  `release_status` int NOT NULL COMMENT '发布状态，0:未发布,1:发布',
  `max_lat` double NOT NULL COMMENT '最大纬度',
  `max_lng` double NOT NULL COMMENT '最大经度',
  `min_lat` double NOT NULL COMMENT '最小纬度',
  `min_lng` double NOT NULL COMMENT '最小经度'
) ENGINE=OLAP
UNIQUE KEY(`load_id`, `geo_2d_code`)
COMMENT '网格人口统计源表'
DISTRIBUTED BY HASH(`load_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `ods_reg_grid_verification_zone` (
  `zone_id` varchar(40) NOT NULL COMMENT '空域验证区id',
  `geo_type` int NOT NULL COMMENT '网格码类型,0:二维网格码,1:三维网格码',
  `geo_code` varchar(33) NOT NULL COMMENT '网格码(20级)',
  `max_lat` double NOT NULL COMMENT '最大纬度',
  `max_lng` double NOT NULL COMMENT '最大经度',
  `min_lat` double NOT NULL COMMENT '最小纬度',
  `min_lng` double NOT NULL COMMENT '最小经度',
  `min_height` double NULL COMMENT '最小高度',
  `max_height` double NULL COMMENT '最大高度'
) ENGINE=OLAP
UNIQUE KEY(`zone_id`, `geo_type`, `geo_code`)
COMMENT '空域验证区网格源表'
DISTRIBUTED BY HASH(`zone_id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE TABLE IF NOT EXISTS `ods_bridge_object_track_sense_analyze` (
  `id` bigint NOT NULL COMMENT '主键',
  `device_type` int NOT NULL COMMENT '设备类型',
  `device_id` varchar(32) NOT NULL COMMENT '设备id',
  `object_id` varchar(64) NOT NULL COMMENT '目标唯一编号',
  `provider_code` varchar(32) NOT NULL COMMENT '设备提供方编码',
  `device_name` varchar(255) NULL COMMENT '设备名称',
  `time` datetime(3) NOT NULL COMMENT '探测时间（毫秒精度）',
  `track_time` int NULL COMMENT '轨迹时长(单位S)',
  `first_detect_time` datetime(3) NULL COMMENT '首次发现时间（毫秒精度）',
  `last_detect_time` datetime(3) NULL COMMENT '最后一次发现时间（毫秒精度）',
  `uav_sn` varchar(255) NULL COMMENT '无人机sn',
  `uav_model` varchar(255) NULL COMMENT '无人机机型',
  `white_list_flag` int NULL DEFAULT "0" COMMENT '是否在白名单中 0:不在白名单 1:在白名单',
  `uav_identity` varchar(255) NULL COMMENT '唯一产品识别码',
  `identify_way` varchar(255) NULL COMMENT '轨迹识别方式',
  `identify_result` varchar(255) NULL COMMENT '轨迹最终识别结果',
  `extension` text NULL COMMENT '扩展数据',
  `in_defense_zone` int NULL DEFAULT "0" COMMENT '轨迹是否在防区中 0:不在 1:在防区',
  `trace_oedevice_id` varchar(255) NULL COMMENT '跟踪该轨迹的光电设备ID集合',
  `create_at` datetime NOT NULL COMMENT '创建时间',
  `update_at` datetime NOT NULL COMMENT '更新时间'
) ENGINE=OLAP
UNIQUE KEY(`id`)
COMMENT '目标轨迹分析表'
DISTRIBUTED BY HASH(`id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE USER IF NOT EXISTS cmii@'%' IDENTIFIED BY 'Gy8F5jpaFG@G';
GRANT SELECT_PRIV,LOAD_PRIV ON cmii.* TO 'cmii'@'%';