- 版本2.0.4,表是天分区,一天2400万数据
- 建表语句
CREATE TABLE `ods_kafka_vehicle_dynamic_info` (
`date_time` datetime(6) NULL COMMENT '上报时间',
`vin` varchar(255) NULL COMMENT '车辆vin',
`vin_id` varchar(255) NULL,
`ts` bigint(20) NULL COMMENT '时间戳',
`longitude` double NULL COMMENT '经度',
`latitude` double NULL COMMENT '纬度',
`low_battery_voltage` double NULL,
`battery_electricity` double NULL,
`battery_electricity_front` double NULL,
`speed` double NULL,
`drive_mode` int(11) NULL,
`control_mode` int(11) NULL,
`use_battery` int(11) NULL,
`network_delay` int(11) NULL,
`remaining_battery_range` double NULL COMMENT '剩余续航里程 单位km',
`rf_pressure` double NULL COMMENT '右前胎压',
`lr_pressure` double NULL COMMENT '左后胎压',
`lf_pressure` double NULL COMMENT '左前胎压',
`rr_pressure` double NULL COMMENT '右后胎压',
`azimuth` double NULL COMMENT '航向角',
INDEX idx_vin (`vin`) USING INVERTED COMMENT '',
INDEX idx_vin_id (`vin_id`) USING INVERTED COMMENT '',
INDEX idx_control_mode (`control_mode`) USING INVERTED COMMENT '',
INDEX index_ts (`ts`) USING INVERTED COMMENT ''
) ENGINE=OLAP
DUPLICATE KEY(`date_time`)
COMMENT '车辆动态信息上报表-来源kafka'
PARTITION BY RANGE(`date_time`)
(PARTITION p20231204 VALUES [('2023-12-04 00:00:00'), ('2023-12-05 00:00:00')),
PARTITION p20231205 VALUES [('2023-12-05 00:00:00'), ('2023-12-06 00:00:00')),
分区很多大概是290个
分区很多大概是290个
分区很多大概是290个
PARTITION p20240627 VALUES [('2024-06-27 00:00:00'), ('2024-06-28 00:00:00')),
PARTITION p20240628 VALUES [('2024-06-28 00:00:00'), ('2024-06-29 00:00:00')),
PARTITION p20240629 VALUES [('2024-06-29 00:00:00'), ('2024-06-30 00:00:00')))
DISTRIBUTED BY RANDOM BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "10",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"storage_format" = "V2",
"compression" = "ZSTD",
"light_schema_change" = "true",
"compaction_policy" = "time_series",
"time_series_compaction_goal_size_mbytes" = "1024",
"time_series_compaction_file_count_threshold" = "2000",
"time_series_compaction_time_threshold_seconds" = "3600",
"time_series_compaction_empty_rowsets_threshold" = "5",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
); |
- sql语句, ods库和app库,两个有join操作
WITH t1 AS (SELECT ts,a.vin,vin_id,longitude,latitude,azimuth,speed,battery_electricity,battery_electricity_front,use_battery
FROM ods.ods_kafka_vehicle_dynamic_info AS a
JOIN (
SELECT vin, MAX(ts) AS max_ts
FROM ods.ods_kafka_vehicle_dynamic_info
WHERE date_time >= '2024-06-25 00:00:00' and date_time <= '2024-06-25 23:59:59'
and vin in ('XXXXXX', 'XXXX', '大概430个vin')
GROUP BY vin
) AS b
ON a.vin = b.vin AND a.ts = b.max_ts
)
SELECT
COALESCE(a.ts, b.ts) AS ts,
COALESCE(a.vin, b.vin) AS vin,
COALESCE(a.vin_id, b.vin_id) AS vinId,
COALESCE(a.longitude, b.longitude) AS longitude,
COALESCE(a.latitude, b.latitude) AS latitude,
COALESCE(a.azimuth, b.azimuth) AS azimuth,
COALESCE(a.speed, b.speed) AS speed,
COALESCE(a.battery_electricity, b.battery_electricity) AS batteryElectricity,
COALESCE(a.battery_electricity_front, b.battery_electricity_front) AS batteryElectricityFront,
COALESCE(a.use_battery, b.use_battery) AS useBattery
FROM t1 a
FULL OUTER JOIN app.app_last_geoinfo_for_vehicle b ON a.vin = b.vin
where 1=1
and b.vin in ('XXXXXX', 'XXXX', '大概430个vin')
order by ts desc;