What is the SQL operation method of Apache IoTDB 02/22 Update SLTechnology News&Howtos

What is the SQL operation method of Apache IoTDB

2025-02-22 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Internet Technology >

Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "what is the SQL operation method of Apache IoTDB". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Storage group operation

# create storage group IoTDB > set storage group to root.turbine

# query storage group IoTDB > SHOW STORAGE GROUP+-+ | storage group | +-+ | root.turbine | +-+

# Delete storage group IoTDB > delete storage group root.turbine

Create a time series

Create timeseries root.turbine.d1.s1 (temperature1) with datatype=FLOAT, encoding=GORILLA, compression=SNAPPY tags (unit=degree, owner=user1) attributes (description=mysensor1, location=BeiJing) create timeseries root.turbine.d1.s2 (temperature2) with datatype=FLOAT, encoding=GORILLA, compression=SNAPPY tags (unit=degree, owner=user1) attributes (description=mysensor2, location=TianJin) create timeseries root.turbine.d2.s1 (temperature1) with datatype=FLOAT, encoding=GORILLA, compression=SNAPPY tags (unit=degree, owner=user2) attributes (description=mysensor3, location=HeBei)

The sequence visualization registered above is the following picture (hand-drawn. Currently there is no visualization function)

In order to make it more convenient to use in practical application, in addition to the basic information such as path and coding of time series, we add three concepts: alias, label and attribute. The total size of tags and attributes is set by tag_attribute_total_size in the configuration file.

Alias: the alias of the test point, which can be used to read and write like the test point name, but can not be set.

Tags: key=value form, you can reverse query time series metadata through tags, such as units and owners, tags will be resident in memory. At present, only one tag query condition can be given, which can be accurately queried and fuzzy queried.

Attribute: in key=value form, attribute information, such as description information and location, can only be displayed according to the time series path. If there is no requirement for reverse query, it is recommended to define it as an attribute.

# insert update aliases, tags, attributes ALTER timeseries root.turbine.d1.s1 UPSERT ALIAS=newAlias TAGS (unit=Degree, owner=me) ATTRIBUTES (description=ha, newAttr=v1) # Delete time series delete timeseries root.turbine.d2.s1

Query sequence metadata based on paths and tags

View the child node of a path

Count the number of time series

# count all time series IoTDB > count timeseries+-+ | count | +-+ | 3 | +-+

# Group statistics time series, root is layer 0 IoTDB > count timeseries group by level=2+-+-+ | column | count | +-+-+ | root.turbine.d1 | 2 | | root.turbine.d2 | 1 | +-+-+

Query all devices

That is to query the path of the node at the penultimate layer.

DML data manipulation language

Reference documentation:

Http://iotdb.apache.org/UserGuide/Master/Operation%20Manual/DML%20Data%20Manipulation%20Language.html

Data writing

The values of one device, one timestamp, and multiple measuring points can be written at a time.

Insert into root.turbine.d1 (timestamp,s1,s2) values, insert into root.turbine.d1 (timestamp,s1,s2) values, insert into root.turbine.d1 (timestamp,s1,s2) values, insert into root.turbine.d1 (timestamp,s1,s2) values, insert into root.turbine.d1 (timestamp,s1,s2) values, insert into root.turbine.d1 (timestamp,s1,s2) values, Insert into root.turbine.d1 (timestamp,s1,s2) values (10pm 1pm 2)

Data deletion

Currently, only data before one point in time can be deleted, and data for any period of time will be deleted later.

Delete from root.turbine.d2.s1 where time select s1 S2 from root.turbine.d1+--+ | Time | root.turbine.d1.s1 | root.turbine.d1.s2 | +- -+ | 1970-01-01T08:00:00.001+08:00 | 1.0 | 2.0 | | 1970-01-01T08:00:00.002+08:00 | 1970 | 2.0 | | 1970-01-01T08:00:00 .003 + 08:00 | 1970 | 2.0 | 1970-01-01T08:00:00.004+08:00 | 1.0 | 2.0 | | 1970-01-01T08:00:00.005+08:00 | 1.0 | 2.0 | | 1970-01-01T08:00:00.006+08:00 | 1.0 | 2.0 | | 1970 | -01-01T08:00:00.010+08:00 | 1.0 | 2.0 | +-+

Single point blank value query

There is a lot of time stamp deviation in the data collected by the sensor, and it is easy to find the data when the time stamp is accurate. You can use previous or linear to fill in the blank value.

IoTDB > select S1 from root.turbine.d1 where time = 8-IoTDB.

# fill in IoTDB > select S1 from root.turbine.d1 where time = 8 fill (float [float]) +-- +-+ | Time | root.turbine.d1.s1 | +- -+-+ | 1970-01-01T08:00:00.008+08:00 | 1.0 | +-+-+

# if you want to limit the range of complementary values, if you exceed this range, you can add another parameter. To take the unit IoTDB > select S1 from root.turbine.d1 where time = 8 fill 1ms]) +-- +-+ | Time | root.turbine.d1.s1 | +-+- + | 1970-01-01T08:00:00.008+08:00 | null | +-+-+

Latest data query

In order to visualize the latest data in real time, we have done a separate query function for the latest data points. Using the select last keyword as a prefix, other grammars, like the original data, cannot be filtered with predicates.

IoTDB > select last * from root+--+ | Time | timeseries | value | +-+- -+-+ | 1970-01-01T08:00:00.010+08:00 | root.turbine.d1.s1 | 1.0 | | 1970-01-01T08:00:00.010+08:00 | root.turbine.d1.s2 | 2.0 | +-+

Aggregate query

Statistics of the aggregation value of time series, we currently treat each time series as an independent series, aggregation is also done in sub-series. The next version will add the ability to aggregate all sequences under a path.

IoTDB > select count (*) from root where time select last_value (S1) from root.turbine.d1 group by ([1m 10) 2ms) +-+-+ | Time | last_value (root.turbine.d1.s1) | +-- +- -- + | 1970-01-01T08:00:00.001+08:00 | 1.0 | | 1970-01-01T08:00:00.003+08:00 | 1.0 | | 1970-01-01T08:00:00.005+08:00 | 1.0 | | 1970-01-01T08:00 : 00.007-08null | null | | 1970-01-01T08:00:00.009+08:00 | null | +-- +-- +

# downsampling. If there is no value in a certain interval, you can fill the gap with the previous aggregate value. The fill function is previousIoTDB > select last_value (S1) from root.turbine.d1 group by ([1mae 10)) 2ms) fill (float []) +-- +-- + | Time | last_value (root.turbine.d1.s1) | + -+-- + | 1970-01-01T08:00:00.001+08:00 | 1.0 | 1970-01-01T08:00:00.003+08:00 | 1.0 | 1970-01-01T08:00:00.005+08:00 | 1. 0 | | 1970-01-01T08:00:00.007+08:00 | 1.0 | | 1970-01-01T08:00:00.009+08:00 | 1.0 | +-- +-- +

In addition, another blank filling method, previousuntillast, is also supported, which uses the leading value to fill the blank until it reaches the time value of the latest point, for example, when the latest point timestamp is 10 minutes 11 and 13, it will no longer be filled.

IoTDB > select last_value (S1) from root.turbine.d1 group by ((1mem15) 2ms) fill (float [previousuntillast]) +-+ | Time | last_value (root.turbine.d1.s1) | + -+-- + | 1970-01-01T08:00:00.003+08:00 | 1.0 | 1970-01-01T08:00:00.005+08:00 | 1.0 | 1970-01-01T08:00:00.007+08:00 | 1. 0 | | 1970-01-01T08:00:00.009+08:00 | 1.0 | | 1970-01-01T08:00:00.011+08:00 | 1.0 | | 1970-01-01T08:00:00.013+08:00 | null | | 1970-01-01T08:00:00.015+08:00 | null | +- -+

I don't know if you have noticed that the interval of this sentence is before opening and then closing, and the result set is also the time point of the closed interval. In this way, the query of sampling and filling blank value is realized by group by fill statement.

0.9.x reduced frequency aggregation query

The down-frequency aggregation syntax of the old version 0.9 is different from that of 0.10. There are mainly several parameters

(1) divide the timeline into segments at intervals of this length.

(2) to divide the origin, from which point to divide, you can use the endpoint of any segment. The default is 00:00:00 on January 1, 1970, that is, 0 of the timestamp.

(3) the display scope of the result set

After the first two parameters are fixed, the segmentation of the timeline is determined, and then the third parameter specifies the result set.

For example, query the daily average in May

Select avg (S1) from root.turbine.d1 group by (1D, 2020-05-01 00:00:00, [2020-05-01 00:00:00, 2020-05-31 23:59:59])

Query by device alignment

Through the above example, we can see that the default table structure of IoTDB query is [time, sequence 1, sequence 2, sequence n], all sequences will be aligned according to time, if there is a sequence that does not exist at a point in time, it will fill in the blank value, and the filtering of this table structure will be very strict when doing value filtering.

In order to ensure that various devices do not affect each other when querying, we support the query by time and device alignment. The table structure is [time, device ID, test point 1, test point 2, point n]. This is similar to the relational table structure, only adding align by device after the query statement.

IoTDB > select * from root align by device+--+-+---+---+ | Time | Device | S1 | S2 | +-- -01T08:00:00.001+08:00 | root.turbine.d1 | 1970 | 1970-01-01T08:00:00.002+08:00 | root.turbine.d1 | 1.0 | 1970-01-01T08:00:00.003+08:00 | root.turbine.d1 | 1.0 | 1970-01-01T08:00:00.004+08:00 | root.turbine.d1 | 1970-01-01T08:00:00.005+08:00 | root.turbine.d1 | 1.0 | 1970-01-01T08 : 00Apache IoTDB 00.006 SQL 08 root.turbine.d1 | root.turbine.d1 | 1.0 | 2.0 | | 1970-01-01T08:00:00.010+08:00 | root.turbine.d1 | 1.0 | 2.0 | +-- + Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.