[时序数据/IoTDB/SeaTunnel] 基于 Apache SeaTunnel 实现MySQL时序数据同步并写入 Apache IotDB 数据库（加列方式）

0 序

1 实现步骤

数据来源库: Mysql

DDL

-- ============================================================
-- 车辆时序信号数据流表 DDL（MySQL 8.0+）
-- 表名：vehicle_vhr.vhr_can_raw
-- 说明：基于车辆时序信号数据流表结构生成，含 200 行仿真 DML
-- 时序建模：collect_time=TIME 时间轴，vin=TAG，复合主键(vin,collect_time)
-- 生成时间：2026-10-16
-- ============================================================

DROP TABLE IF EXISTS vehicle_vhr.vhr_can_raw;

CREATE TABLE IF NOT EXISTS vehicle_vhr.vhr_can_raw (
    collect_time        BIGINT      NOT NULL                COMMENT '采集时间（毫秒时间戳）【TIME】',
    vin                 VARCHAR(32) NOT NULL                COMMENT '车架号【TAG】',
    tbox_pdid           VARCHAR(64) NOT NULL                COMMENT 'TBOX的设备ID【Attribute】',
    model_code          VARCHAR(64) NOT NULL                COMMENT '车型代码【Attribute】',
    total_mileage       FLOAT       DEFAULT NULL            COMMENT '累计驾驶里程(km)【FIELD】',
    subtotal_mileage    FLOAT       DEFAULT NULL            COMMENT '小计驾驶里程(km)【FIELD】',
    online_status       INT         DEFAULT NULL            COMMENT '在线状态:0-下线;1-上线【FIELD】',
    drive_speed         FLOAT       DEFAULT NULL            COMMENT '车辆行驶速度(km/h)【FIELD】',
    engine_speed        FLOAT       DEFAULT NULL            COMMENT '发动机转速(rpm)【FIELD】',
    skylight_status     INT         DEFAULT NULL            COMMENT '天窗状态:0-关闭;1-打开【FIELD】',
    bms_soc             FLOAT       DEFAULT NULL            COMMENT '动力电池管理系统-电池剩余电量/State of Charge(Kw*h)【FIELD】',
    bms_soh             FLOAT       DEFAULT NULL            COMMENT '动力电池管理系统-电池健康度/State of Health(%)【FIELD】',
    remaing_oil_percent FLOAT       DEFAULT NULL            COMMENT '剩余油量百分比(%)【FIELD】',
    PRIMARY KEY (vin, collect_time),
    KEY idx_collect_time (collect_time),
    KEY idx_model_code (model_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆时序信号数据流表';

DML

-- ============================================================
-- DML：插入 200 条仿真时序信号（20 台车 × 10 采样点）
-- ============================================================

INSERT INTO vehicle_vhr.vhr_can_raw (collect_time, vin, tbox_pdid, model_code, total_mileage, subtotal_mileage, online_status, drive_speed, engine_speed, skylight_status, bms_soc, bms_soh, remaing_oil_percent) VALUES
    (1760436387000, 'LSGKB52HU4MG9Q9TD', 'SDWFKSLDA6Y2Z15H', 'M9-2025-ExtendedRange-Ultra-FourWheelDrive', '28709.35', '0.0', 1, '0.0', '857.42', 0, '58.93', '92.48', '67.82'),
    (1760436867000, 'LSGKB52HU4MG9Q9TD', 'SDWFKSLDA6Y2Z15H', 'M9-2025-ExtendedRange-Ultra-FourWheelDrive', '28719.32', '9.97', 1, '85.5', '1951.57', 1, '58.7', '92.48', '67.7'),
    (1760437227000, 'LSGKB52HU4MG9Q9TD', 'SDWFKSLDA6Y2Z15H', 'M9-2025-ExtendedRange-Ultra-FourWheelDrive', '28727.2', '17.85', 1, '94.5', '2012.64', 0, '58.47', '92.48', '67.21'),
    (1760437707000, 'LSGKB52HU4MG9Q9TD', 'SDWFKSLDA6Y2Z15H', 'M9-2025-ExtendedRange-Ultra-FourWheelDrive', '28727.2', '17.85', 1, '0.0', '779.28', 0, '57.92', '92.48', '67.04');

目标时序库: Apache IoTDB

建模思路 = loTDB 树模型:设备模板 + 1类设备时序数据集1个库(库≈传统的表)

  • 每个 iotdb 【database】 下所有【时间序列】(Time Series)共享同一【存储组】(Storage Group)

Step1 建库

-- 1) 建库（必须提前做，否则 auto-create 会按 default_storage_group_level 乱切层级）
CREATE DATABASE root.ods_project_001.ods_vehicle_can_conditions;
  -- DROP DATABASE root.ods_project_001.ods_vehicle_can_conditions;

show databases;
  • 反例： （不要提前创建: ods_project_001，否则会导致 root.ods_project_001.ods_vehicle_can_conditions 创建失败）
IoTDB> create database root.ods_project_001;
Msg: The statement is executed successfully.
IoTDB> CREATE DATABASE root.ods_project_001.ods_vehicle_can_conditions;
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 501: root.ods_project_001 has already been created as database

Step2 创建【设备模板】，显式声明测点类型

  • 【测点】可理解为1个设备的1个传感器。
-- 2) 创建设备模板，显式声明测点类型（按你的业务选精度）
--    temperature/pressure 用 FLOAT；bms_soc 是百分比 0~100，用 INT32 足够
CREATE DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions (
  total_mileage       FLOAT,
  subtotal_mileage    FLOAT,
  online_status       INT32,
  drive_speed         FLOAT,
  engine_speed        FLOAT,
  skylight_status     INT32,
  bms_soc             FLOAT,
  bms_soh             FLOAT
);
  --  drop device template ods_project_001_device_template_ods_vehicle_can_conditions;
  • 补充情况：支持创建时间对齐的设备模板

IoTDB 的设备模板（Schema Template）本身支持创建 “时间对齐” 模式，但要注意这里 “对齐” 有两层含义，分开说清楚：

  • 1 在树模型（Tree Model）下，创建设备模板时可以【显式声明时间对齐属性】，语法上分三种：
模板类型 语法 效果
非对齐模板（默认） CREATE SCHEMA TEMPLATE t1 (s1, s2, s3) 各测点各自独立时间戳，时间轴不对齐
全部对齐模板 CREATE SCHEMA TEMPLATE ALIGNED t1 (s1, s2, s3) 所有测点共享同一时间戳，形成对齐序列（aligned series）
分组对齐模板 CREATE SCHEMA TEMPLATE t1 (ALIGNED(s1, s2), s3) 组内测点对齐共享时间戳，s3 独立

挂载到设备后，该设备下的测点即成为对齐序列——同一时刻的所有测点写入同一时间戳，向量式存储，压缩和查询效率更高。所以你的需求在树模型下完全可以满足。

  • 2 树模型下，另一种可能的“对齐”= 设备间测点集一致
  • 设备模板的另一核心作用就是让挂载了同一模板的所有设备拥有完全相同的测点结构（设备结构对齐）。
    这是模板最本质的价值，也是你提到的“设备模板”本身默认具备的能力。
  • 3 另外，表模型（Table Model）下的情况

如果你用的是 IoTDB 2.x 的表模型，那么“【对齐】”是天然成立的：宽表每行共享一个时间戳，同一行的所有非空测点即按该时间戳对齐存储，无需像树模型那样手动声明 ALIGNED。

  • 补充情况： 创建 iotdb 树模型下的 Time Series（本文档中未实施、未体现）

iotdb 树模型下的 Time Series 类比： iotdb 表模型下的 Table

-- 或: 创建时间对齐的 TimeSeries (不带 tag / attribute)
create database root.ods_project_001; -- 库名通过 sys_project 表 ProjectCode 对应的 dwDatabaseName 决定
CREATE ALIGNED TIMESERIES root.ods_project_001.{DwTableName}.{本体对象主键的值,如:设备ID}(
  temp      FLOAT encoding=RLE   compression=SNAPPY
  latitude  FLOAT encoding=PLAIN compressor=SNAPPY,
  longitude FLOAT encoding=PLAIN compressor=SNAPPY
);


-- 或: 创建时间不对齐的 TimeSeries (带 tag / attribute)
--   每个测点可独立指定数据类型、编码、压缩方式、tags、attributes（在 1.1.3 的情况下，tag/attribute问题：不现实/无法落地）
create aligned timeseries root.ods_project_001.{DwTableName}.{本体对象主键的值,如:设备ID}(
  temp     FLOAT   encoding=RLE      compression=SNAPPY tags(unit=c, kind=sensor)      attributes(desc=temperature_probe, location=line_a),
  humidity DOUBLE  encoding=GORILLA  compression=SNAPPY tags(unit=pct, kind=sensor)    attributes(desc=humidity_probe, location=line_a),
  voltage  INT32   encoding=TS_2DIFF compression=LZ4    tags(unit=mv, kind=meter)      attributes(desc=voltage_meter, location=cabinet_3),
  status   BOOLEAN encoding=PLAIN    compression=SNAPPY tags(kind=switch)              attributes(desc=running_state, location=cabinet_3),
  model    TEXT    encoding=PLAIN                       tags(kind=info)                attributes(desc=device_model)
);

Step3 把模板挂到 database 节点上 （含：设备模板的管理维护操作）

  • 把模板挂到 database 节点上（官方推荐挂在 database 层，不要挂到 root 这种上层）
SET DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions TO root.ods_project_001.ods_vehicle_can_conditions;
  • 查验操作
-- 1) 列出所有已创建的模板名
SHOW DEVICE TEMPLATES;
  旧版（1.x）叫 `SHOW SCHEMA TEMPLATES` / `SHOW NODES IN SCHEMA TEMPLATE`，2.0 统一改成 `DEVICE TEMPLATE`，旧写法大概率仍兼容，但建议用新写法
  

-- 2) 查看某个模板里定义了哪些测点（类型/编码/压缩）
IoTDB> SHOW NODES IN DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions;
+----------------+--------+--------+-----------+
|      ChildNodes|DataType|Encoding|Compression|
+----------------+--------+--------+-----------+
|   total_mileage|   FLOAT| GORILLA|        LZ4|
|         bms_soh|   FLOAT| GORILLA|        LZ4|
|     drive_speed|   FLOAT| GORILLA|        LZ4|
|         bms_soc|   FLOAT| GORILLA|        LZ4|
|   online_status|   INT32|TS_2DIFF|        LZ4|
|subtotal_mileage|   FLOAT| GORILLA|        LZ4|
| skylight_status|   INT32|TS_2DIFF|        LZ4|
|    engine_speed|   FLOAT| GORILLA|        LZ4|
+----------------+--------+--------+-----------+
Total line number = 8
It costs 0.010s


-- 3) 设备模板，被挂载在了哪些路径上（SET 过的位置）
IoTDB> SHOW PATHS SET DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions;
+---------------------------------------+
|                                  Paths|
+---------------------------------------+
|root.ods_project_001.ods_vehicle_can_conditions|
+---------------------------------------+
Total line number = 1
It costs 0.012s


-- 4) 哪些路径已经真正激活模板、建出了实际 timeseries（USING）
SHOW PATHS USING DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions;
IoTDB> SHOW PATHS USING DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions;
+-----+
|Paths|
+-----+
+-----+
Empty set.
It costs 0.005s

（写入成功数据后：）
IoTDB> SHOW PATHS USING DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions
+---------------------------------------------------------+
|                                                    Paths|
+---------------------------------------------------------+
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HMCE3PVMJR|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HP9RCQM3VN|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HMUY85HQG2|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HVWYF35KD9|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H2KB0Y7S5G|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HPSU5LA02R|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H6Q8WKJS9K|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HABYFPQELV|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HJY5GXRDQ8|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HTMPYR6Z5R|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HU4MG9Q9TD|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HZLUGKR6EY|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H8X99K9R1B|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HB47E13CV2|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H37ND0B3ZE|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HTKGCS5387|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HWFYEE0EJ2|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HPG3N1GLDA|
+---------------------------------------------------------+
Total line number = 20
It costs 0.008s
  • 如果后续要删除、卸载【设备模板】
-- 第一步：停用模板——删除该路径下所有由模板激活出来的 timeseries | 它会把模板在该路径下激活的所有 timeseries 连同数据一起删掉
-- [x] DEACTIVATE DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions FROM root.ods_project_001.ods_vehicle_can_conditions;
DEACTIVATE DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions FROM root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ;

-- 第二步：卸载模板
UNSET DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions FROM root.ods_project_001.ods_vehicle_can_conditions;

2步骤等效于： DELETE TIMESERIES OF DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions FROM root.ods_project_001.ods_vehicle_can_conditions;

Step4 插入数据到 树模型的 Database Node (可选)

  • 如果要【手动插入数据】，是这样的：
INSERT INTO root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ(timestamp, total_mileage, subtotal_mileage, online_status, drive_speed, engine_speed, skylight_status, bms_soc, bms_soh)
VALUES (1760527996000, 49472.3, 0, 1, 0, 767.58, 0, 89.41 , 96.6);

INSERT INTO root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ(timestamp, total_mileage, subtotal_mileage, online_status, drive_speed, engine_speed, skylight_status, bms_soc, bms_soh)
VALUES (1760528356000, 49477.6, 5.28, 1, 105.5, 2144.05, 0, 89.18, 96.6);

删某测点全部数据: DELETE FROM root.ods_project_001.ods_vehicle_can_conditions.**;

Step5 查询时序数据

方式1：表模型下，基于树模型的 TimeSeries 在 表模型模式下【只读的视图表】进行数据查询

  • 表模型模式下，创建视图表
-- 表模型下，创建视图表
CREATE DATABASE viewdb_ods_project_001; -- 先建库

CREATE OR REPLACE VIEW viewdb_ods_project_001.ods_vehicle_can_conditions (
  vin                 STRING TAG,
  total_mileage       FLOAT  FIELD,
  subtotal_mileage    FLOAT  FIELD,
  online_status       INT32  FIELD,
  drive_speed         FLOAT  FIELD,
  engine_speed        FLOAT  FIELD,
  skylight_status     INT32  FIELD,
  bms_soc             FLOAT  FIELD,
  bms_soh             FLOAT  FIELD
  -- current2 DOUBLE FIELD FROM current -- 把【视图表】的 `current2` 列映射到【树模型 TimeSeries】里的 `current` 测点
)
AS root.ods_project_001.ods_vehicle_can_conditions.**;

-- 表模型下，查看 VIEW
IoTDB:ods_project_001> SHOW TABLES;
+--------------------------+-----------+
|                 TableName|    TTL(ms)|
+--------------------------+-----------+
|ods_vehicle_can_conditions|31536000000|
+--------------------------+-----------+
Total line number = 1
It costs 0.002s
IoTDB:ods_project_001> SHOW TABLES FROM viewdb_ods_project_001;
+--------------------------+-------+
|                 TableName|TTL(ms)|
+--------------------------+-------+
|ods_vehicle_can_conditions|    INF|
+--------------------------+-------+
Total line number = 1
It costs 0.004s
IoTDB:ods_project_001> show create table viewdb_ods_project_001.ods_vehicle_can_conditions;
+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------+
|                      View|
                                                                                                                             Create View|
+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------+
|ods_vehicle_can_conditions|CREATE VIEW "ods_vehicle_can_conditions" ("time" TIMESTAMP TIME,"vin" STRING TAG,"total_mileage" FLOAT FIELD,"subtotal_mileage" FLOAT FIELD,"online_status" INT32 FIELD,"drive_speed" FLOAT FIELD,"engine_speed" FLOAT FIELD,"skyl
ight_status" INT32 FIELD,"bms_soc" FLOAT FIELD,"bms_soh" FLOAT FIELD) WITH (ttl='INF') AS root."ods_project_001"."ods_vehicle_can_conditions".**|
+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------+
Total line number = 1
It costs 0.006s


-- 补充操作： 删除 VIEW
-- DROP VIEW IF EXISTS viewdb_ods_project_001.ods_vehicle_can_conditions;
  • 表模型下，查看视图表的数据明细
IoTDB:ods_project_001> select * from viewdb_ods_project_001.ods_vehicle_can_conditions where vin = 'LSGKB52H29BV6AZEZ' order by time desc,vin asc limit 5;
+-----------------------------+-----------------+-------------+----------------+-------------+-----------+------------+---------------+-------+-------+
|                         time|              vin|total_mileage|subtotal_mileage|online_status|drive_speed|engine_speed|skylight_status|bms_soc|bms_soh|
+-----------------------------+-----------------+-------------+----------------+-------------+-----------+------------+---------------+-------+-------+
|2025-10-15T20:45:16.000+08:00|LSGKB52H29BV6AZEZ|      49501.8|           29.48|            1|       52.3|     1716.84|              1|  85.32|   96.6|
|2025-10-15T20:38:16.000+08:00|LSGKB52H29BV6AZEZ|      49497.5|           25.12|            1|        0.0|      955.26|              1|  85.74|   96.6|
|2025-10-15T20:29:16.000+08:00|LSGKB52H29BV6AZEZ|      49497.5|           25.12|            1|        0.0|      998.88|              1|  86.39|   96.6|
|2025-10-15T20:21:16.000+08:00|LSGKB52H29BV6AZEZ|      49497.5|           25.12|            1|       72.6|     2368.79|              0|  86.69|   96.6|
|2025-10-15T20:14:16.000+08:00|LSGKB52H29BV6AZEZ|      49491.4|           19.08|            1|       50.0|     1464.55|              1|  87.36|   96.6|
+-----------------------------+-----------------+-------------+----------------+-------------+-----------+------------+---------------+-------+-------+
Total line number = 5
It costs 0.023s

方式2：树模型下，直接查询

  • 树模型下，查询数据明细
SELECT vin,total_mileage,drive_speed,bms_soc,bms_soh FROM root.ods_project_001.ods_vehicle_can_conditions.** limit 10
  -- time 是隐含字段，不能出现在 SELECT 中，会报错


IoTDB> SELECT vin,total_mileage,drive_speed,bms_soc,bms_soh FROM root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR limit 10
+-----------------------------+-----------------------------------------------------------------------+---------------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+
|                         Time|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.total_mileage|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.drive_speed|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.bms_soc|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.bms_soh|
+-----------------------------+-----------------------------------------------------------------------+---------------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+
|2025-10-08T00:30:38.000+08:00|                                                                21818.6|                                                                  0.0|                                                            68.81|                                                            98.07|
|2025-10-08T00:34:38.000+08:00|                                                                21822.2|                                                                 72.1|                                                            68.14|                                                            98.07|
|2025-10-08T00:42:38.000+08:00|                                                                21827.5|                                                                105.5|                                                            67.72|                                                            98.07|
|2025-10-08T00:52:38.000+08:00|                                                                21827.5|                                                                  0.0|                                                            67.23|                                                            98.07|
|2025-10-08T01:00:38.000+08:00|                                                                21827.5|                                                                  0.0|                                                            66.62|                                                            98.07|
|2025-10-08T01:11:38.000+08:00|                                                                21840.4|                                                                 97.0|                                                            66.05|                                                            98.07|
|2025-10-08T01:18:38.000+08:00|                                                                21852.8|                                                                106.4|                                                            65.82|                                                            98.07|
|2025-10-08T01:24:38.000+08:00|                                                                21852.8|                                                                  0.0|                                                            65.22|                                                            98.07|
|2025-10-08T01:35:38.000+08:00|                                                                21852.8|                                                                  0.0|                                                            64.65|                                                            98.07|
|2025-10-08T01:40:38.000+08:00|                                                                21859.6|                                                                 81.0|                                                            63.89|                                                            98.07|
+-----------------------------+-----------------------------------------------------------------------+---------------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+
Total line number = 10
It costs 0.010s


IoTDB> select * from viewdb_ods_project_001.ods_vehicle_can_conditions limit 10;
+-----------------------------+-----------------+-------------+----------------+-------------+-----------+------------+---------------+-------+-------+
|                         time|              vin|total_mileage|subtotal_mileage|online_status|drive_speed|engine_speed|skylight_status|bms_soc|bms_soh|
+-----------------------------+-----------------+-------------+----------------+-------------+-----------+------------+---------------+-------+-------+
|2025-10-04T09:15:30.000+08:00|LSGKB52HMCE3PVMJR|      38842.9|             0.0|            1|        0.0|         0.0|              0|  77.47|  92.33|
|2025-10-04T09:26:30.000+08:00|LSGKB52HMCE3PVMJR|      38858.2|           15.28|            1|      114.6|         0.0|              1|  77.08|  92.33|
|2025-10-04T09:29:30.000+08:00|LSGKB52HMCE3PVMJR|      38863.8|           20.84|            1|      111.1|         0.0|              1|  76.56|  92.33|
|2025-10-04T09:37:30.000+08:00|LSGKB52HMCE3PVMJR|      38863.8|           20.84|            1|        0.0|         0.0|              1|  75.91|  92.33|
|2025-10-04T09:46:30.000+08:00|LSGKB52HMCE3PVMJR|      38863.8|           20.84|            1|        0.0|         0.0|              1|  75.61|  92.33|
|2025-10-04T09:54:30.000+08:00|LSGKB52HMCE3PVMJR|      38871.4|           28.46|            1|      114.3|         0.0|              0|  75.05|  92.33|
|2025-10-04T10:01:30.000+08:00|LSGKB52HMCE3PVMJR|      38874.7|           31.73|            1|       65.5|         0.0|              0|  74.66|  92.33|
|2025-10-04T10:10:30.000+08:00|LSGKB52HMCE3PVMJR|      38874.7|           31.73|            1|        0.0|         0.0|              1|  74.29|  92.33|
|2025-10-04T10:19:30.000+08:00|LSGKB52HMCE3PVMJR|      38874.7|           31.73|            1|        0.0|         0.0|              0|  74.05|  92.33|
|2025-10-04T10:25:30.000+08:00|LSGKB52HMCE3PVMJR|      38876.5|           33.57|            1|       36.8|         0.0|              1|  73.64|  92.33|
+-----------------------------+-----------------+-------------+----------------+-------------+-----------+------------+---------------+-------+-------+
Total line number = 10
It costs 0.022s
  • 树模式下，查看 Time Series
-- 全库（数据量大时慎用，会扫出海量行）
IoTDB> SHOW TIMESERIES;
+--------------------------------------------------------------------------+-----+---------------------------------------+--------+--------+-----------+----+----------+--------+------------------+--------+
|                                                                Timeseries|Alias|                               Database|DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+--------------------------------------------------------------------------+-----+---------------------------------------+--------+--------+-----------+----+----------+--------+------------------+--------+
|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.total_mileage| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.bms_soh| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|     root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.drive_speed| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.bms_soc| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.online_status| null|root.ods_project_001.ods_vehicle_can_conditions|   INT32|TS_2DIFF|        LZ4|null|      null|    null|              null|    BASE|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.subtotal_mileage| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.skylight_status| null|root.ods_project_001.ods_vehicle_can_conditions|   INT32|TS_2DIFF|        LZ4|null|      null|    null|              null|    BASE|
|    root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.engine_speed| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
+--------------------------------------------------------------------------+-----+---------------------------------------+--------+--------+-----------+----+----------+--------+------------------+--------+
Total line number = 8
It costs 0.004s


-- 指定路径前缀，推荐带通配符
IoTDB> SHOW TIMESERIES root.ods_project_001.ods_vehicle_can_conditions.**;
+--------------------------------------------------------------------------+-----+---------------------------------------+--------+--------+-----------+----+----------+--------+------------------+--------+
|                                                                Timeseries|Alias|                               Database|DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+--------------------------------------------------------------------------+-----+---------------------------------------+--------+--------+-----------+----+----------+--------+------------------+--------+
|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.total_mileage| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.bms_soh| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|     root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.drive_speed| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.bms_soc| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.online_status| null|root.ods_project_001.ods_vehicle_can_conditions|   INT32|TS_2DIFF|        LZ4|null|      null|    null|              null|    BASE|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.subtotal_mileage| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.skylight_status| null|root.ods_project_001.ods_vehicle_can_conditions|   INT32|TS_2DIFF|        LZ4|null|      null|    null|              null|    BASE|
|    root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.engine_speed| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
+--------------------------------------------------------------------------+-----+---------------------------------------+--------+--------+-----------+----+----------+--------+------------------+--------+
Total line number = 8
It costs 0.006s


-- 看某个具体设备ID(VIN)对应的所有 TimeSeries
IoTDB> SHOW TIMESERIES root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.**;
+--------------------------------------------------------------------------+-----+---------------------------------------+--------+--------+-----------+----+----------+--------+------------------+--------+
|                                                                Timeseries|Alias|                               Database|DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+--------------------------------------------------------------------------+-----+---------------------------------------+--------+--------+-----------+----+----------+--------+------------------+--------+
|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.total_mileage| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.bms_soh| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|     root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.drive_speed| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.bms_soc| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.online_status| null|root.ods_project_001.ods_vehicle_can_conditions|   INT32|TS_2DIFF|        LZ4|null|      null|    null|              null|    BASE|
|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.subtotal_mileage| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.skylight_status| null|root.ods_project_001.ods_vehicle_can_conditions|   INT32|TS_2DIFF|        LZ4|null|      null|    null|              null|    BASE|
|    root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ.engine_speed| null|root.ods_project_001.ods_vehicle_can_conditions|   FLOAT| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
+--------------------------------------------------------------------------+-----+---------------------------------------+--------+--------+-----------+----+----------+--------+------------------+--------+
Total line number = 8
It costs 0.007s

利弊分析（当前建模方式）

  • 好处：

    • 未来如果数据平台的底层要切换/支持 TD-Engine / InfluxDB / OpenGemini，也不易于迁移、兼容。
    • 短查询友好。
      • 短查询 = 单次请求只取少量序列 + 有限时间范围 + 无复杂计算，即 "点查 / 小范围直查"，耗时毫秒级。
      • 反例（长 / 分析查询）：跨大量序列的全量扫描、聚合、降采样、窗口函数、多表关联 —— 而这是表模型的强项。

  • 弊端：

    • 不方便为设备维护tag、attribute等非时序的静态属性
      • 当tag、attribute的值要在时序数据写入时才可确定时（tag列、attribute可提前确定）：
        • 树模型的tag只能在树路径上体现，且设备数量太多后，有series元数据爆炸的风险
    • attribute 就更不支持了
    • 不方便基于Tag做1....N个、1...M类设备的数据分析
      • 这种数分需求在企业很常见、Palantir本体时序分析也更需要
    • 海量设备下，series元数据可能容易(内存)爆炸
    • 如果是异构传感器的设备们，同一表下可能存在大量空值的TSP列。

  • FAQ

    • Q: 基于Apache loTDB 树模型，用模板 CREATE SCHEMA TEMPLATE t2 ALIGNED (lat, lon) 统一建模，能支持后期对同一批设备追加时间对齐的新测点？能
      • 【对齐设备】可继续用 CREATE ALIGNED TIMESERIES/ALTER DEVICE TEMPLATE t2 ADD (speed FLOAT); 加【对齐测点】
    • Q: 树模型的对齐与不对齐的本质区别？

补充：日常运维排查操作（树模型：设备模板、TimeSeries）

-- 1. 模板在不在、挂哪了
SHOW DEVICE TEMPLATES;
SHOW PATHS SET DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions;

-- 2. 已经有多少 VIN 真正落了数据
SHOW PATHS USING DEVICE TEMPLATE ods_project_001_device_template_ods_vehicle_can_conditions;
COUNT DEVICES root.ods_project_001.ods_vehicle_can_conditions.**;

-- 3. 序列总数 & 按 VIN 聚合是否符合预期（每车应等于测点数）
COUNT TIMESERIES root.ods_project_001.ods_vehicle_can_conditions.**;
COUNT TIMESERIES root.ods_project_001.ods_vehicle_can_conditions.** GROUP BY LEVEL = 4;

-- 4. 抽样看一条序列的类型是否被模板正确接管
SHOW TIMESERIES root.ods_project_001.ods_vehicle_can_conditions.LSVAU2180EXXXXXX.**;

构建数据管道: Apache SeaTunnel

  • 推荐文献
  • ioTDBv2 Sink
  • Apache SeaTunnel 信息
  • 版本: 2.3.13

mysql-to-iotdb_tree.conf

  • vim /data/seatunnel-2.3.13/demo-jobs/mysql-to-iotdb_tree.conf
env {
  parallelism = 1
  job.mode = "BATCH"
}

source {
  Jdbc {
    url = "jdbc:mysql://192.168.xxx.xxx:3306/vehicle_vhr?useSSL=false&useUnicode=true&characterEncoding=utf8&allowLoadLocalInfile=false&autoDeserialize=false&allowLocalInfile=false&allowUrlInLocalInfile=false&serverTimezone=Asia/Shanghai"
    driver = "com.mysql.cj.jdbc.Driver"
    user = "root"
    password = "xxx"
    query = "select `collect_time`, `vin`, `tbox_pdid`, `model_code`, `total_mileage`, `subtotal_mileage`, `online_status`, `drive_speed`,`engine_speed`,`skylight_status`,`bms_soc`,`bms_soh`,`remaing_oil_percent` from `vehicle_vhr`.`vhr_can_raw`"
  }
}

transform {

}

sink {
  IoTDB {
    node_urls = ["192.168.xxx.xxx:6667"]
    username = "root"
    password = "root"
	# IoTDB 的 SQL 方言/客户端访问的数据模型模式: tree(默认，目前仅支持) / table
	sql_dialect = "tree"
	# IoTDB-table：指定数据库 | IoTDB-tree：指定设备存储组（路径前缀），例如：deviceID = \${storage_group} + “.” + \${key_device}，如: "root.test_group"
	storage_group = "root.ods_project_001.ods_vehicle_can_conditions"
    # IoTDB-tree：在 SeaTunnelRow 中指定字段名称作为设备 ID; | IoTDB-table：指定 SeaTunnelRow 中的【字段名】称作为表名
    key_device = "vin"
	
	# IoTDB-table：在 SeaTunnelRow 中指定字段名称作为时间列（默认使用处理时间） | IoTDB-Tree：在 SeaTunnelRow 中指定字段名称作为时间戳（默认使用处理时间）
    key_timestamp = "collect_time"
	# IoTDB-tree: invalid   IoTDB-tree：无效 | IoTDB-table：在 SeaTunnelRow 中指定字段名称作为 TAG 列
	key_tag_fields = ["vin"]
	# key_measurement_fields | IoTDB 表：指定 SeaTunnelRow 中的字段名称作为 FIELD 列（默认使用除 key_device、key_timestamp、key_tag_fields 和 key_attribute_fields 以外的字段）
    # key_measurement_fields | IoTDB 树：指定 SeaTunnelRow 中用作测量的字段名称（默认使用除 key_device&key_timestamp 以外的字段）
	key_measurement_fields = [ "total_mileage", "subtotal_mileage", "online_status", "drive_speed", "engine_speed", "skylight_status","bms_soc","bms_soh" ]
    # IoTDB-tree: invalid   IoTDB-tree：无效 | IoTDB-table：指定 SeaTunnelRow 中字段名称作为属性列使用	
    # key_attribute_fields = ["tbox_pdid", "model_code"]
	# 在批量写入中，数据会在缓冲区数量达到 batch_size 数或时间到达 batch_interval_ms
	batch_size = 1024
	# 在 IoTDB 客户端中启用 rpc 压缩，仅在 IoTDB-Tree 中有效
	enable_rpc_compression = true
  }
}

运行命令（定时运行）

cd /data/seatunnel-2.3.13/
sh bin/seatunnel.sh --config demo-jobs/mysql-to-iotdb_tree.conf -m local

数据查询使用

查询各个设备、各个测点列的最新值

  • 树模型下
  • 设备模板挂在 root.ods_project_001.ods_vehicle_can_conditions 下，每个设备是 ...<deviceId>.<measurement>，用 * 通配设备节点即可一次查出所有设备：
  • LAST 是 IoTDB 树模型的关键词型查询，返回各测点最大时间戳的数据点，走内存缓存，微秒级延迟^1。
  • 想要某个设备单独查，把 * 换成设备节点即可：FROM root.ods_project_001.ods_vehicle_can_conditions.<deviceId>。
IoTDB> SELECT LAST total_mileage, subtotal_mileage, online_status, drive_speed, engine_speed, skylight_status, bms_soc, bms_soh FROM root.ods_project_001.ods_vehicle_can_conditions.*;
+-----------------------------+--------------------------------------------------------------------------+-------+--------+
|                         Time|                                                                Timeseries|  Value|DataType|
+-----------------------------+--------------------------------------------------------------------------+-------+--------+
|2025-10-08T01:40:38.000+08:00|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.total_mileage|21859.6|   FLOAT|
|2025-10-08T01:40:38.000+08:00|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.subtotal_mileage|  40.98|   FLOAT|
|2025-10-08T01:40:38.000+08:00|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.online_status|      1|   INT32|
|2025-10-08T01:40:38.000+08:00|     root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.drive_speed|   81.0|   FLOAT|
|2025-10-08T01:40:38.000+08:00|    root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.engine_speed|    0.0|   FLOAT|
|2025-10-08T01:40:38.000+08:00| root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.skylight_status|      0|   INT32|
|2025-10-08T01:40:38.000+08:00|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.bms_soc|  63.89|   FLOAT|
|2025-10-08T01:40:38.000+08:00|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HA7JSM83QR.bms_soh|  98.07|   FLOAT|
|2025-10-08T04:22:56.000+08:00|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HMUY85HQG2.total_mileage|10089.5|   FLOAT|
|2025-10-08T04:22:56.000+08:00|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HMUY85HQG2.subtotal_mileage|  34.04|   FLOAT|
|2025-10-08T04:22:56.000+08:00|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HMUY85HQG2.online_status|      1|   INT32|
|2025-10-08T04:22:56.000+08:00|     root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HMUY85HQG2.drive_speed|   59.4|   FLOAT|
...(省略)
|2025-10-03T22:51:54.000+08:00|    root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H8X99K9R1B.engine_speed|1445.92|   FLOAT|
|2025-10-03T22:51:54.000+08:00| root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H8X99K9R1B.skylight_status|      0|   INT32|
|2025-10-03T22:51:54.000+08:00|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H8X99K9R1B.bms_soc|  91.77|   FLOAT|
|2025-10-03T22:51:54.000+08:00|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52H8X99K9R1B.bms_soh|   98.4|   FLOAT|
|2025-10-10T15:32:54.000+08:00|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HZLUGKR6EY.total_mileage|11258.5|   FLOAT|
|2025-10-10T15:32:54.000+08:00|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HZLUGKR6EY.subtotal_mileage|  51.19|   FLOAT|
|2025-10-10T15:32:54.000+08:00|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HZLUGKR6EY.online_status|      1|   INT32|
|2025-10-10T15:32:54.000+08:00|     root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HZLUGKR6EY.drive_speed|  102.5|   FLOAT|
|2025-10-10T15:32:54.000+08:00|    root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HZLUGKR6EY.engine_speed|2762.33|   FLOAT|
|2025-10-10T15:32:54.000+08:00| root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HZLUGKR6EY.skylight_status|      0|   INT32|
...(省略)
|2025-10-01T12:55:26.000+08:00|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HWFYEE0EJ2.subtotal_mileage|  23.76|   FLOAT|
|2025-10-01T12:55:26.000+08:00|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HWFYEE0EJ2.online_status|      0|   INT32|
|2025-10-01T12:55:26.000+08:00|     root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HWFYEE0EJ2.drive_speed|   46.0|   FLOAT|
|2025-10-01T12:55:26.000+08:00|    root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HWFYEE0EJ2.engine_speed|1433.83|   FLOAT|
|2025-10-01T12:55:26.000+08:00| root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HWFYEE0EJ2.skylight_status|      1|   INT32|
|2025-10-01T12:55:26.000+08:00|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HWFYEE0EJ2.bms_soc|   82.9|   FLOAT|
|2025-10-01T12:55:26.000+08:00|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HWFYEE0EJ2.bms_soh|  95.53|   FLOAT|
|2025-10-05T01:23:20.000+08:00|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HTKGCS5387.total_mileage|38023.2|   FLOAT|
|2025-10-05T01:23:20.000+08:00|root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HTKGCS5387.subtotal_mileage|  40.07|   FLOAT|
|2025-10-05T01:23:20.000+08:00|   root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HTKGCS5387.online_status|      1|   INT32|
|2025-10-05T01:23:20.000+08:00|     root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HTKGCS5387.drive_speed|   36.1|   FLOAT|
|2025-10-05T01:23:20.000+08:00|    root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HTKGCS5387.engine_speed|    0.0|   FLOAT|
|2025-10-05T01:23:20.000+08:00| root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HTKGCS5387.skylight_status|      0|   INT32|
|2025-10-05T01:23:20.000+08:00|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HTKGCS5387.bms_soc|  43.57|   FLOAT|
|2025-10-05T01:23:20.000+08:00|         root.ods_project_001.ods_vehicle_can_conditions.LSGKB52HTKGCS5387.bms_soh|  96.05|   FLOAT|
+-----------------------------+--------------------------------------------------------------------------+-------+--------+
Total line number = 160
It costs 0.068s
IoTDB>
  • 表模型方言（基于视图表）

树模型没法用普通 SELECT 直接 "取最新"，表模型也一样 —— 视图是整段时序历史的投影，要借助聚合或窗口函数。两种方案，语义不同：

① last_by 聚合：每个测点各自独立的最新值（推荐，最贴合我的需求）

SELECT vin,
       last(time) AS last_ts,
       last_by(total_mileage,   time) AS total_mileage,
       last_by(subtotal_mileage,time) AS subtotal_mileage,
       last_by(online_status,   time) AS online_status,
       last_by(drive_speed,     time) AS drive_speed,
       last_by(engine_speed,    time) AS engine_speed,
       last_by(skylight_status, time) AS skylight_status,
       last_by(bms_soc,         time) AS bms_soc,
       last_by(bms_soh,         time) AS bms_soh
FROM viewdb_ods_project_001.ods_vehicle_can_conditions
GROUP BY vin;

IoTDB:viewdb_ds_50560> SELECT vin, last(time) AS last_ts, last_by(total_mileage,   time) AS total_mileage, last_by(subtotal_mileage,time) AS subtotal_mileage, last_by(online_status,   time) AS online_status, last_by(drive_speed,     time) AS drive_speed, last_by(engine_speed,    time) AS engine_speed, last_by(skylight_status, time) AS skylight_status, last_by(bms_soc,         time) AS bms_soc, last_by(bms_soh,         time) AS bms_soh FROM viewdb_ods_project_001.ods_vehicle_can_conditions GROUP BY vin;
+-----------------+-----------------------------+-------------+----------------+-------------+-----------+------------+---------------+-------+-------+
|              vin|                      last_ts|total_mileage|subtotal_mileage|online_status|drive_speed|engine_speed|skylight_status|bms_soc|bms_soh|
+-----------------+-----------------------------+-------------+----------------+-------------+-----------+------------+---------------+-------+-------+
|LSGKB52HA7JSM83QR|2025-10-08T01:40:38.000+08:00|      21859.6|           40.98|            1|       81.0|         0.0|              0|  63.89|  98.07|
|LSGKB52HMUY85HQG2|2025-10-08T04:22:56.000+08:00|      10089.5|           34.04|            1|       59.4|      2016.0|              1|  93.53|  95.23|
|LSGKB52HVWYF35KD9|2025-10-13T22:18:34.000+08:00|      19842.2|           39.86|            1|       30.0|         0.0|              0|  79.34|  94.48|
|LSGKB52H2KB0Y7S5G|2025-10-08T12:10:07.000+08:00|      26468.0|           42.01|            1|      104.7|     3365.63|              1|  90.34|  96.99|
|LSGKB52HJY5GXRDQ8|2025-10-01T01:23:54.000+08:00|      24737.5|           40.62|            1|       86.1|         0.0|              0|  29.99|  93.32|
|LSGKB52HTMPYR6Z5R|2025-10-08T05:34:54.000+08:00|      14235.6|           32.85|            1|       54.2|     1848.65|              0|  54.56|  95.57|
|LSGKB52HU4MG9Q9TD|2025-10-14T19:17:27.000+08:00|      28740.0|           30.69|            1|       64.8|     1956.84|              1|  54.97|  92.48|
|LSGKB52H29BV6AZEZ|2025-10-15T20:45:16.000+08:00|      49501.8|           29.48|            1|       52.3|     1716.84|              1|  85.32|   96.6|
|LSGKB52H37ND0B3ZE|2025-10-10T21:05:26.000+08:00|      14647.9|           36.07|            1|       68.8|     2617.27|              1|  68.73|  95.38|
|LSGKB52HPG3N1GLDA|2025-10-05T23:05:53.000+08:00|      22112.9|           33.88|            1|      103.6|     3359.99|              1|  57.86|  96.92|
|LSGKB52HMCE3PVMJR|2025-10-04T10:25:30.000+08:00|      38876.5|           33.57|            1|       36.8|         0.0|              1|  73.64|  92.33|
|LSGKB52HP9RCQM3VN|2025-10-07T23:19:43.000+08:00|      48190.0|           31.27|            1|       94.6|     3114.03|              1|  27.56|  97.97|
|LSGKB52HPSU5LA02R|2025-10-05T19:08:02.000+08:00|      28123.3|           39.89|            1|      106.6|     3484.43|              0|  64.21|  96.42|
|LSGKB52H6Q8WKJS9K|2025-10-15T18:36:49.000+08:00|      39716.6|           33.56|            1|      111.6|     2866.46|              1|  68.69|  95.72|
|LSGKB52HABYFPQELV|2025-10-04T05:41:21.000+08:00|      39133.3|           22.56|            1|       86.9|     3006.64|              0|  65.44|  94.45|
|LSGKB52HZLUGKR6EY|2025-10-10T15:32:54.000+08:00|      11258.5|           51.19|            1|      102.5|     2762.33|              0|  70.25|  98.96|
|LSGKB52H8X99K9R1B|2025-10-03T22:51:54.000+08:00|      26408.7|           28.93|            1|       44.8|     1445.92|              0|  91.77|   98.4|
|LSGKB52HB47E13CV2|2025-10-05T07:13:55.000+08:00|      51328.8|           20.89|            1|       81.2|         0.0|              1|  93.31|   94.8|
|LSGKB52HTKGCS5387|2025-10-05T01:23:20.000+08:00|      38023.2|           40.07|            1|       36.1|         0.0|              0|  43.57|  96.05|
|LSGKB52HWFYEE0EJ2|2025-10-01T12:55:26.000+08:00|      33137.8|           23.76|            0|       46.0|     1433.83|              1|   82.9|  95.53|
+-----------------+-----------------------------+-------------+----------------+-------------+-----------+------------+---------------+-------+-------+
Total line number = 20
It costs 0.065s

last_by(col, time) = 该列在最大时间戳那行的值；last(time) = 该设备最新时间戳。
这是官方案例 SELECT device_id, last(time), last_by(temperature, time) FROM table1 GROUP BY device_id 的标准用法^3。

② 窗口函数：每个设备 "最近一行快照"（整行对齐到设备最大时间戳） （全表扫描、大数据量场景下，不建议； iotdb 2.0.11 未测成功，对 iotdb 数据库版本有要求 或有bug）

SELECT * FROM viewdb_ods_project_001.ods_vehicle_can_conditions QUALIFY ROW_NUMBER() OVER (PARTITION BY vin ORDER BY time DESC) = 1;

SELECT vin, total_mileage, subtotal_mileage, online_status, drive_speed,
       engine_speed, skylight_status, bms_soc, bms_soh, last_ts
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY vin ORDER BY time DESC) AS rn,
           time AS last_ts
    FROM viewdb_ods_project_001.ods_vehicle_can_conditions
) t
WHERE rn = 1;
  • 两者区别（关键）
维度 树模型 SELECT LAST 表模型 last_by+GROUP BY 表模型 QUALIFY+ROW_NUMBER
语义 每个测点各自最新点 每个测点各自最新点（独立） 每个设备最近一整行快照
各测点时间戳 可不同 可不同 强制对齐到同一最大时间戳
一行一个设备 否（长表） 是 是
性能 内存缓存，最快 走聚合 走窗口排序

要点：树模型的 SELECT LAST 和表模型的 last_by 语义一致（各自独立最新点）；若你只要"设备最近状态快照"，才用 QUALIFY 方案。整表只取最新一行可简写为 ORDER BY time DESC LIMIT 1[^5]
https://iotdb.apache.org/UserGuide/latest-Table/SQL-Manual/Limit-Offset-Clause.html

F 附件

F.1 创建 iotDB的表模型示例

- 创建表模型
-- time: 时间列 / 树模型中 time 是严格保留字，不允许使用；但表模型中可以。
CREATE TABLE IF NOT EXISTS ods_project_001.ods_vehicle_can_conditions (
  time                TIMESTAMP TIME,                 
  vin                 STRING    TAG,
  series_code         STRING    ATTRIBUTE,        
  model_code          STRING    ATTRIBUTE, 
  total_mileage       FLOAT     FIELD,
  subtotal_mileage    FLOAT     FIELD,
  online_status       INT32     FIELD,
  drive_speed         FLOAT     FIELD,
  engine_speed        FLOAT     FIELD,
  skylight_status     INT32     FIELD,
  bms_soc             FLOAT     FIELD,
  bms_soh             FLOAT     FIELD
) COMMENT '基于CAN总线的车辆工况时序数据' WITH (TTL=31536000000);
  • 查询数据
select * from ods_project_001.ods_vehicle_can_conditions;
  • 删除数据
delete from ods_project_001.ods_vehicle_can_conditions where vin = 'xxx';

F.2 基于IoTDB树模型的多行方式

MySQL

DROP TABLE IF EXISTS vehicle_vhr.vhr_vehicle_can_sensors_timeseries;

CREATE TABLE IF NOT EXISTS vehicle_vhr.vhr_vehicle_can_sensors_timeseries (
    collect_time BIGINT      NOT NULL                COMMENT '采集时间（毫秒时间戳）【TIME】',
    vin          VARCHAR(32) NOT NULL                COMMENT '车架号【TAG】',
    sensor_name  VARCHAR(64) NOT NULL                COMMENT '传感器名称：total_mileage/subtotal_mileage/online_status/drive_speed/engine_speed/skylight_status/bms_soc/bms_soh/remaing_oil_percent【FIELD】',
    sensor_value DOUBLE      DEFAULT NULL            COMMENT '传感器值【FIELD】',
    PRIMARY KEY (vin, collect_time, sensor_name),
    KEY idx_collect_time (collect_time),
    KEY idx_sensor_name (sensor_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆CAN信号时序传感器长表(宽表unpivot)';

-- ============================================================
-- DML：插入 1800 条仿真时序信号（20 台车 x 10 采样点 x 9 传感器）
-- ============================================================

INSERT INTO vehicle_vhr.vhr_vehicle_can_sensors_timeseries (collect_time, vin, sensor_name, sensor_value) VALUES
    (1759248474000, 'LSGKB52HJY5GXRDQ8', 'total_mileage', 24696.9),
    (1759248474000, 'LSGKB52HJY5GXRDQ8', 'subtotal_mileage', 0),
    (1759248474000, 'LSGKB52HJY5GXRDQ8', 'online_status', 1),
    (1759248474000, 'LSGKB52HJY5GXRDQ8', 'drive_speed', 0),
    (1759248474000, 'LSGKB52HJY5GXRDQ8', 'engine_speed', 0),
    (1759248474000, 'LSGKB52HJY5GXRDQ8', 'skylight_status', 1),
    (1759248474000, 'LSGKB52HJY5GXRDQ8', 'bms_soc', 34.24),
    (1759248474000, 'LSGKB52HJY5GXRDQ8', 'bms_soh', 93.32),
    (1759248474000, 'LSGKB52HJY5GXRDQ8', 'remaing_oil_percent', 0),
    (1759248954000, 'LSGKB52HJY5GXRDQ8', 'total_mileage', 24698.8),
    (1759248954000, 'LSGKB52HJY5GXRDQ8', 'subtotal_mileage', 1.9),
    (1759248954000, 'LSGKB52HJY5GXRDQ8', 'online_status', 1),
    (1759248954000, 'LSGKB52HJY5GXRDQ8', 'drive_speed', 38),
    (1759248954000, 'LSGKB52HJY5GXRDQ8', 'engine_speed', 0),
    (1759248954000, 'LSGKB52HJY5GXRDQ8', 'skylight_status', 1),
    (1759248954000, 'LSGKB52HJY5GXRDQ8', 'bms_soc', 33.9),
    (1759248954000, 'LSGKB52HJY5GXRDQ8', 'bms_soh', 93.32),
    (1759248954000, 'LSGKB52HJY5GXRDQ8', 'remaing_oil_percent', 0),
    (1759249614000, 'LSGKB52HJY5GXRDQ8', 'total_mileage', 24709.3),
     ...

IotDB

  • 树模型下
  • 关键点： sensor_name 作为 Field 列，而非 Tag 列。

否则，无法实现——因为：IoTDB 树模式中，设备模板的机制是：模板内声明一组固定测点名，挂载到一个前缀路径后，该前缀下任意后代节点都能作为设备，激活时把模板测点 "实例化" 到设备节点下；而测点名（最末节点）必须写在模板里，且名字固定，不支持通配符 / 动态名作为测点。

-- tree
CREATE DATABASE root.project_123.ods_vehicle_can_conditions;
  -- DROP DATABASE root.project_123.ods_vehicle_can_conditions

CREATE DEVICE TEMPLATE project_123_device_template_ods_vehicle_can_conditions (
  sensor_name         STRING,
  sensor_value        DOUBLE
);
  -- DROP DEVICE TEMPLATE project_123_device_template_ods_vehicle_can_conditions

SET DEVICE TEMPLATE project_123_device_template_ods_vehicle_can_conditions TO root.project_123.ods_vehicle_can_conditions;
  • 表模型下
CREATE DATABASE viewdb_project_123; -- 先建库

CREATE OR REPLACE VIEW viewdb_project_123.ods_vehicle_can_conditions (
  vin                 STRING TAG,
  sensor_name         STRING  FIELD,
  sensor_value        DOUBLE  FIELD
)
AS root.project_123.ods_vehicle_can_conditions.**;
  -- DROP VIEW viewdb_project_123.ods_vehicle_can_conditions;


select * from viewdb_project_123.ods_vehicle_can_conditions;
  • 树模型下，写数据
INSERT INTO root.project_123.ods_vehicle_can_conditions.LSGKB52H29BV6AZEZ(timestamp, sensor_name, sensor_value) VALUES 
(1760528356000, "bms_soc", 87.9)
,(1760528356000, "bms_soh", 95.5);

SeaTunnel

  • vim /data/seatunnel-2.3.13/demo-jobs/mysql-to-iotdb_tree-multi-rows.conf
# 树模型版

env {
  parallelism = 1
  job.mode = "BATCH"
}

source {
  Jdbc {
    url = "jdbc:mysql://192.168.xxx.xxx:3306/vehicle_vhr?useSSL=false&useUnicode=true&characterEncoding=utf8&allowLoadLocalInfile=false&autoDeserialize=false&allowLocalInfile=false&allowUrlInLocalInfile=false&serverTimezone=Asia/Shanghai"
    driver = "com.mysql.cj.jdbc.Driver"
    user = "root"
    password = "xxx"
    #query = "select `collect_time`, `vin`, `tbox_pdid`, `model_code`, `total_mileage`, `subtotal_mileage`, `online_status`, `drive_speed`,`engine_speed`,`skylight_status`,`bms_soc`,`bms_soh`,`remaing_oil_percent` from `vehicle_vhr`.`vhr_can_raw`"
    query = "select collect_time, vin, sensor_name, sensor_value from vehicle_vhr.vhr_vehicle_can_sensors_timeseries"
  }
}

transform {

}

sink {
  IoTDB {
    node_urls = ["192.168.xxx.xxx:6667"]
    username = "root"
    password = "root"
    # IoTDB 的 SQL 方言/客户端访问的数据模型模式: tree(默认，目前仅支持) / table
    sql_dialect = "tree"
    # IoTDB-table：指定数据库 | IoTDB-tree：指定设备存储组（路径前缀），例如：deviceID = \${storage_group} + “.” + \${key_device}，如: "root.test_group"
    storage_group = "root.project_123.ods_vehicle_can_conditions"
    # IoTDB-tree：在 SeaTunnelRow 中指定字段名称作为设备 ID; | IoTDB-table：指定 SeaTunnelRow 中的【字段名】称作为表名
    key_device = "vin"

    # IoTDB-table：在 SeaTunnelRow 中指定字段名称作为时间列（默认使用处理时间） | IoTDB-Tree：在 SeaTunnelRow 中指定字段名称作为时间戳（默认使用处理时间）
    key_timestamp = "collect_time"
    # IoTDB-tree: invalid   IoTDB-tree：无效 | IoTDB-table：在 SeaTunnelRow 中指定字段名称作为 TAG 列
    #key_tag_fields = ["vin"]
    # key_measurement_fields | IoTDB 表：指定 SeaTunnelRow 中的字段名称作为 FIELD 列（默认使用除 key_device、key_timestamp、key_tag_fields 和 key_attribute_fields 以外的字段）
    # key_measurement_fields | IoTDB 树：指定 SeaTunnelRow 中用作测量的字段名称（默认使用除 key_device&key_timestamp 以外的字段）
    key_measurement_fields = [ "sensor_name","sensor_value" ]
    # IoTDB-tree: invalid   IoTDB-tree：无效 | IoTDB-table：指定 SeaTunnelRow 中字段名称作为属性列使用  
    # key_attribute_fields = ["tbox_pdid", "model_code"]
        # 在批量写入中，数据会在缓冲区数量达到 batch_size 数或时间到达 batch_interval_ms
        batch_size = 1024
  }
}
  • 运行
sh bin/seatunnel.sh --config demo-jobs/mysql-to-iotdb_tree-multi-rows.conf -m local

Z FAQ

Q: 如果 iotdb (版本=2.x) viewdb_ods_project_001.ods_vehicle_can_conditions 视图表对应的树模型的数据量在 10亿条、（5000台设备、200个测点列、每秒上报1条、平均日活6h、TTL/存储3个月）的话，其树模型是否能支撑住？建议的iotdb 集群规格？

  • 问题描述

如果 iotdb (版本=2.x) viewdb_ods_project_001.ods_vehicle_can_conditions 视图表对应的树模型的数据量在 10亿条、（5000台设备、200个测点列、每秒上报1条、平均日活6h、TTL/存储3个月）的话，其树模型是否能支撑住？建议的iotdb 集群规格？
即

日增：1.08 亿条、216亿个测点；
月增： 32.4亿条、6480亿个测点；
合计： 97.2亿条、19440亿个测点；

如果用视图表查询设备最新状态的方案1、方案2来查询的话，查询性能又如何？

  • AI的评估结论：能支撑住。这是一个"中大规模但完全在 IoTDB 设计能力内"的车联网场景，核心瓶颈不在能否扛住，而在突发写入峰值、1M 序列元数据内存、以及查询要避免全表扫描。

未实际实测

关键测算

指标 数值 说明
时间序列数 1,000,000 5000 台 × 200 测点
峰值写入 100 万点/秒 5000 台 × 200 点，日活 6h 内持续
平均写入（24h） 25 万点/秒 6h 有数据、18h 无
记录数/日 1.08 亿条 5000 台 × 3600s × 6h
测点数/日 216 亿点 = 1.08 亿 × 200
记录数/月(30d) 32.4 亿条
测点数/月 6480 亿点
3 个月合计 97.2 亿条 / 19440 亿点 TTL=3 个月，稳态存储
单副本存储(估) 约 8–12 TB 按 4–6 字节/点（Gorilla 压缩，含时间戳）

存储估算依赖压缩生效：假设测点列的数据值变化较缓，【Gorilla 编码】下 4–6 字节/点是合理区间。若值抖动剧烈或未开压缩（PLAIN=16 字节/点），会到 30TB+ 量级。

推荐 IoTDB 2.x 集群规格（生产 HA）

角色 数量 规格 备注
ConfigNode 3 4C / 8G 元数据与调度，要求奇数≥3
DataNode 3 32C / 128G 内存 / NVMe SSD 每节点约 15TB 可用磁盘（3 副本）
副本数 3 关键数据建议 3 副本；可降为 2 省钱
网络 万兆（10GbE） 写入 100 万点/s + 跨节点查询
磁盘总量 ≥ 36 TB 原始 单副本 ~10TB × 3 副本 + 30% 余量（WAL/头文件/索引/压缩临时空间）

内存分配要点：每 DataNode 128G 中，写入缓冲/memtable ≥ 32G（吃掉 100 万点/s 峰值）、1M 序列元数据约 1–2G、查询缓存若干 G。三个 DataNode 每节点约承担 33 万点/s 写入，余量充足。

两个必须注意的工程点：

  1. 突发写入：数据集中在 6h 内涌入（100 万点/s 持续），必须在无数据的 18h 窗口安排 compaction / TTL 清理，否则峰值期 I/O 会被压实占满。
  2. TTL=3 个月：稳态下数据不再增长（约 97.2 亿条封顶），这是支撑住的关键前提；若 TTL 失效或延长，存储与查询成本线性上升。

两种查询方案在 97.2 亿条规模下的性能

方案 预估耗时 原理与风险 结论
方法一 INNER JOIN + GROUP BY MAX(time) 秒级 ~ 1 分钟 底层树模型按序列列式存储、每个 chunk 带 min/max 时间统计。若优化器把 MAX(time) 下推到序列级时间统计，只需读 1M 序列的 chunk 元数据 + 100 万次点查 → 快；若退化成全表扫描，则要读 97.2 亿行 → 分钟级 ✅ 可行，首选
方法二 ROW_NUMBER 开窗全量扫描 十几分钟 ~ 半小时+ 窗口函数必须真正读完全表排序，无法用统计信息；97.2 亿行扫描 + 1M 分区排序，内存压力大，有 OOM 风险 ❌ 不建议在此规模使用

更优的实践建议：如果这是"实时查看每台车每个测点最新值"的高频查询，不要每次都跑全表 SQL。优先用树模型的原生 SELECT LAST ...（走 last cache，百万序列毫秒~秒级返回），或在应用层维护一张"最新状态表"增量更新。视图 SQL 适合低频分析，不适合高频实时最新值。

AI决策建议：按上面 3 CN + 3 DN（32C/128G/NVMe）的集群，写入 100 万点/s、稳态约 97 亿条，IoTDB 完全扛得住；查询务必用方法一（JOIN+MAX）而非方法二（ROW_NUMBER），高频实时最新值则直接走树模型 LAST 查询。

image

AI 评估

image

AI 评估

Y 推荐文献

X 参考文献

posted @ 2026-10-23 10:59  千千寰宇  阅读(6)  评论(0)    收藏  举报