–iotDB 数据类型
– 布尔:BOOLEAN
–整数:INT32
–长整数:INT64
–单精度浮点:FLOAT
–双精度浮点:DOUBLE
–字符串:TEXT
存储组相关
–创建存储组
set storage group to root.turbine;
– 查询存储组
SHOW STORAGE GROUP;
–删除存储组
DELETE STORAGE GROUP root.turbine;
–时间序列相关
– 样例建时间序列
CREATE TIMESERIES root.sg2.d1.s1 WITH DATATYPE=TEXT, ENCODING=PLAIN, COMPRESSOR=SNAPPY
CREATE TIMESERIES root.sg1.d1.s2 WITH DATATYPE=INT64, ENCODING=RLE, COMPRESSOR=SNAPPY
CREATE TIMESERIES root.sg1.d1.s3 WITH DATATYPE=INT64, ENCODING=RLE, COMPRESSOR=SNAPPY
–别名(compression):测点的别名,可以和测点名一样用来读写,可以不设置。
–标签(tags):key=value 形式,可以通过标签反向查询时间序列元数据,比如,单位和拥有者,标签会常驻内存。目前只能给定一个 tag 查询条件,可精确查询和模糊查询。(元数据管理,设备的节点零件来自不同的负责人)
–属性(attributes):key=value 形式,只能根据时间序列路径展示出属性信息,如描述信息和位置。如果没有反向查询的需求,建议定义成属性
create timeseries root.turbine.d3.s3(temperature1)
with datatype=FLOAT, encoding=GORILLA, compression=SNAPPY tags(unit=degree, owner=user1) attributes(description=mysensor1, location=BeiJing);
create timeseries root.turbine.d3.s4(temperature2)
with datatype=FLOAT, encoding=GORILLA, compression=SNAPPY tags(unit=degree, owner=user1) attributes(description=mysensor2, location=TianJin);
create timeseries root.turbine.d3.s5(temperature1)
with datatype=FLOAT, encoding=GORILLA, compression=SNAPPY tags(unit=degree, owner=user2) attributes(description=mysensor3, location=HeBei);
– 无人机飞行轨迹(实战建表,合理运用压缩方式和数据类型)
create timeseries root.tong.wrj.wrj_police_fly_details.id(主键) WITH DATATYPE=INT32, ENCODING=TS_2DIFF;
create timeseries root.tong.wrj.wrj_police_fly_details.lon(经度) WITH DATATYPE=DOUBLE, ENCODING=GORILLA,MAX_POINT_NUMBER=20;
create timeseries root.tong.wrj.wrj_police_fly_details.lat(纬度) WITH DATATYPE=DOUBLE, ENCODING=GORILLA,MAX_POINT_NUMBER=20;
create timeseries root.tong.wrj.wrj_police_fly_details.height(高度) WITH DATATYPE=FLOAT, ENCODING=GORILLA,MAX_POINT_NUMBER=10;
create timeseries root.tong.wrj.wrj_police_fly_details.task_id(任务id) WITH DATATYPE=INT32, ENCODING=RLE;
create timeseries root.tong.wrj.wrj_police_fly_details.device_id(无人机序列号) WITH DATATYPE=TEXT, ENCODING=PLAIN;
create timeseries root.tong.wrj.wrj_police_fly_details.create_by(创建人) WITH DATATYPE=TEXT, ENCODING=PLAIN;
create timeseries root.tong.wrj.wrj_police_fly_details.create_time(创建时间) WITH DATATYPE=INT64, ENCODING=PLAIN;
create timeseries root.tong.wrj.wrj_police_fly_details.update_by(修改人) WITH DATATYPE=TEXT, ENCODING=PLAIN;
create timeseries root.tong.wrj.wrj_police_fly_details.update_time(修改时间) WITH DATATYPE=INT64, ENCODING=PLAIN;
create timeseries root.tong.wrj.wrj_police_fly_details.own_o_id(拥有查看权限的机构id) WITH DATATYPE=TEXT, ENCODING=PLAIN;
create timeseries root.tong.wrj.wrj_police_fly_details.renewable_o_id(拥有修改权限的机构id) WITH DATATYPE=TEXT, ENCODING=PLAIN;
create timeseries root.tong.wrj.wrj_police_fly_details.point_speed(点速度) WITH DATATYPE=FLOAT, ENCODING=GORILLA,MAX_POINT_NUMBER=10;
– 删除时间序列
delete timeseries root.tong.wrj.wrj_police_fly_details.*;
–插入更新 别名、标签、属性
ALTER timeseries root.turbine.d1.s1 UPSERT ALIAS=newAlias TAGS(unit=Degree, owner=me) ATTRIBUTES(description=ha, newAttr=v1)
–删除时间序列
delete timeseries root.turbine.d2.s1
–创建并行时间序列
CREATE ALIGNED TIMESERIES root.tong.wrj.wrj_police_fly_details(
id(主键) INT32 ENCODING=TS_2DIFF,
lon(经度) DOUBLE ENCODING=GORILLA,
lat(纬度) DOUBLE ENCODING=GORILLA,
height(高度) FLOAT ENCODING=GORILLA,
task_id(任务id) INT32 ENCODING=RLE,
device_id(无人机序列号) TEXT ENCODING=PLAIN,
create_by(创建人) TEXT ENCODING=PLAIN,
create_time(创建时间) INT64 ENCODING=PLAIN,
update_by(修改人) TEXT ENCODING=PLAIN,
update_time(修改时间) INT64 ENCODING=PLAIN,
own_o_id(可以查看的机构id) TEXT ENCODING=PLAIN,
renewable_o_id(可以修改的机构id) TEXT ENCODING=PLAIN,
point_speed(点速度) FLOAT ENCODING=GORILLA)
–模版相关
–删除模版
drop schema template wrj_police_fly_details_tmp;
–创建设备的模版数据()
CREATE SCHEMA TEMPLATE wrj_police_fly_details(id(主键) INT32 ENCODING=TS_2DIFF)
CREATE SCHEMA TEMPLATE wrj_police_fly_details_tmp aligned (
id INT32 ENCODING=TS_2DIFF,
lon DOUBLE ENCODING=GORILLA,
lat DOUBLE ENCODING=GORILLA,
height FLOAT ENCODING=GORILLA,
create_by TEXT ENCODING=PLAIN,
create_time INT64 ENCODING=PLAIN,
update_by TEXT ENCODING=PLAIN,
update_time INT64 ENCODING=PLAIN,
own_o_id TEXT ENCODING=PLAIN,
renewable_o_id TEXT ENCODING=PLAIN,
point_speed FLOAT ENCODING=GORILLA);
– 绑定
set schema template wrj_police_fly_details_tmp to root.tong.wrj;
–解绑
unset schema template wrj_police_fly_details_tmp from root.tong.wrj;
–激活
create TIMESERIES of schema template wrj_police_fly_details_tmp on root.tong.wrj.wrj_police_fly_details;
–解除
deactivate schema template wrj_police_fly_details_tmp from root.tong.wrj.wrj_police_fly_details;
CREATE ALIGNED TIMESERIES root.tong.wrj.wrj_police_fly_details(
id(主键) INT32 ENCODING=TS_2DIFF,
lon(经度) DOUBLE ENCODING=GORILLA MAX_POINT_NUMBER=20)
CREATE ALIGNED TIMESERIES root.tong.wrj.wrj_police_fly_details(
id INT32 ENCODING=TS_2DIFF Alias='主键')
–常见DML语句
– 重复生成会覆盖原时间戳
insert into root.turbine.d1(timestamp,s1,s2) values(1657468800000,9,6);
insert into root.tong.wrj.wrj_police_fly_details(id,lon,lat,height,task_id,device_id,create_by,create_time,update_by,update_time)values
(1,114.42174249276451,30.456868981976015,127.09694,273,'3NZCJ3B004BCFN','test',1687672979,'test',1687672979);
–数据删除
delete from root.tong.wrj.wrj_police_fly_details.* where time >0;
delete from root.turbine.d2.s1 where time <= 10
– 查询总数
select count(id) from root.tong.wrj.wrj_police_fly_details;
– 查询指定列(不用带上时间列)
select id,lon,lat from root.tong.wrj.wrj_police_fly_details;
–删除数据
–查看子路径
show child paths root.com (查看root.com路径下的所有子路径)
– 查看满足条件的时间序列:
show timeseries where unit=元 (查看单位为‘元’的时间序列)
show timeseries LIMIT 1 (查看1条时间序列)
show timeseries LIMIT 1 OFFSET 3 (一般用于分页)
– 查看某个路径的孩子节点
show child paths root.turbine
–压缩方式
–统计所有时间序列数量
count timeseries
±----+
|count|
±----+
| 3|
±----+
–分组统计时间序列,root 为第 0 层
count timeseries group by level=2
±--------------±----+
| column|count|
±--------------±----+
|root.turbine.d1| 2|
|root.turbine.d2| 1|
±--------------±----+
一、基本压缩方式
IoTDB允许在创建一个时间序列的时候指定该列的压缩方式。
现阶段IoTDB支持以下几种压缩方式:
UNCOMPRESSED(不压缩)
SNAPPY 压缩
LZ4 压缩
GZIP 压缩
二、压缩比统计信息
压缩比统计信息文件:
data/system/compression_ratio/Ratio-{ratio_sum}-{memtable_flush_time}
ratio_sum: memtable压缩比的总和
memtable_flush_time: memtable刷盘的总次数
通过 ratio_sum / memtable_flush_time 可以计算出平均压缩比