573 lines
23 KiB
SQL
573 lines
23 KiB
SQL
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 '人口密度'
|
||
) 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(40) 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.已处置',
|
||
`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` (
|
||
`geo_3d_code` varchar(32) NOT NULL COMMENT '三维网格码',
|
||
`sampling_time` date NOT NULL COMMENT '采样时间',
|
||
`rsrp` double NULL COMMENT '参考信号接收功率,单位dBm'
|
||
) ENGINE=OLAP
|
||
UNIQUE KEY(`geo_3d_code`, `sampling_time`)
|
||
COMMENT 'radio map模拟网格信号强度明细表'
|
||
DISTRIBUTED BY HASH(`sampling_time`) 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` (
|
||
`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 '时间'
|
||
) ENGINE=OLAP
|
||
DUPLICATE KEY(`longitude`, `latitude`, `altitude`)
|
||
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"
|
||
);
|
||
|
||
ALTER USER root@'%' IDENTIFIED BY "Qtdq5@!Lw5FgM8p#";
|
||
CREATE USER IF NOT EXISTS cmii@'%' IDENTIFIED BY 'Gy8F5jpaFG@G';
|
||
GRANT SELECT_PRIV,LOAD_PRIV ON cmii.* TO 'cmii'@'%'; |