Timeseries Management

Create Timeseries

According to the storage model selected before, we can create corresponding timeseries in the two databases respectively. The SQL statements for creating timeseries are as follows:

  1. IoTDB > create timeseries root.ln.wf01.wt01.status with datatype=BOOLEAN,encoding=PLAIN
  2. IoTDB > create timeseries root.ln.wf01.wt01.temperature with datatype=FLOAT,encoding=RLE
  3. IoTDB > create timeseries root.ln.wf02.wt02.hardware with datatype=TEXT,encoding=PLAIN
  4. IoTDB > create timeseries root.ln.wf02.wt02.status with datatype=BOOLEAN,encoding=PLAIN
  5. IoTDB > create timeseries root.sgcc.wf03.wt01.status with datatype=BOOLEAN,encoding=PLAIN
  6. IoTDB > create timeseries root.sgcc.wf03.wt01.temperature with datatype=FLOAT,encoding=RLE

From v0.13, you can use a simplified version of the SQL statements to create timeseries:

  1. IoTDB > create timeseries root.ln.wf01.wt01.status BOOLEAN encoding=PLAIN
  2. IoTDB > create timeseries root.ln.wf01.wt01.temperature FLOAT encoding=RLE
  3. IoTDB > create timeseries root.ln.wf02.wt02.hardware TEXT encoding=PLAIN
  4. IoTDB > create timeseries root.ln.wf02.wt02.status BOOLEAN encoding=PLAIN
  5. IoTDB > create timeseries root.sgcc.wf03.wt01.status BOOLEAN encoding=PLAIN
  6. IoTDB > create timeseries root.sgcc.wf03.wt01.temperature FLOAT encoding=RLE

Notice that when in the CREATE TIMESERIES statement the encoding method conflicts with the data type, the system gives the corresponding error prompt as shown below:

  1. IoTDB > create timeseries root.ln.wf02.wt02.status WITH DATATYPE=BOOLEAN, ENCODING=TS_2DIFF
  2. error: encoding TS_2DIFF does not support BOOLEAN

Please refer to Encoding for correspondence between data type and encoding.

Create Aligned Timeseries

The SQL statement for creating a group of timeseries are as follows:

  1. IoTDB> CREATE ALIGNED TIMESERIES root.ln.wf01.GPS(latitude FLOAT encoding=PLAIN compressor=SNAPPY, longitude FLOAT encoding=PLAIN compressor=SNAPPY)

You can set different datatype, encoding, and compression for the timeseries in a group of aligned timeseries

It is also supported to set an alias, tag, and attribute for aligned timeseries.

Delete Timeseries

To delete the timeseries we created before, we are able to use (DELETE | DROP) TimeSeries <PathPattern> statement.

The usage are as follows:

  1. IoTDB> delete timeseries root.ln.wf01.wt01.status
  2. IoTDB> delete timeseries root.ln.wf01.wt01.temperature, root.ln.wf02.wt02.hardware
  3. IoTDB> delete timeseries root.ln.wf02.*
  4. IoTDB> drop timeseries root.ln.wf02.*

Show Timeseries

  • SHOW LATEST? TIMESERIES pathPattern? whereClause? limitClause?

    There are four optional clauses added in SHOW TIMESERIES, return information of time series

Timeseries information includes: timeseries path, alias of measurement, database it belongs to, data type, encoding type, compression type, tags and attributes.

Examples:

  • SHOW TIMESERIES

    presents all timeseries information in JSON form

  • SHOW TIMESERIES <PathPattern>

    returns all timeseries information matching the given <PathPattern>. SQL statements are as follows:

  1. IoTDB> show timeseries root.**
  2. IoTDB> show timeseries root.ln.**

The results are shown below respectively:

  1. +-------------------------------+--------+-------------+--------+--------+-----------+-------------------------------------------+--------------------------------------------------------+--------+-------------------+
  2. |                     timeseries|   alias|     database|dataType|encoding|compression|                                       tags|                                              attributes|deadband|deadband parameters|
  3. +-------------------------------+--------+-------------+--------+--------+-----------+-------------------------------------------+--------------------------------------------------------+--------+-------------------+
  4. |root.sgcc.wf03.wt01.temperature|    null|    root.sgcc|   FLOAT|     RLE|     SNAPPY|                                       null|                                                    null|    null|               null|
  5. |     root.sgcc.wf03.wt01.status|    null|    root.sgcc| BOOLEAN|   PLAIN|     SNAPPY|                                       null|                                                    null|    null|               null|
  6. |             root.turbine.d1.s1|newAlias| root.turbine|   FLOAT|     RLE|     SNAPPY|{"newTag1":"newV1","tag4":"v4","tag3":"v3"}|{"attr2":"v2","attr1":"newV1","attr4":"v4","attr3":"v3"}|    null|               null|
  7. |     root.ln.wf02.wt02.hardware|    null|      root.ln|    TEXT|   PLAIN|     SNAPPY|                                       null|                                                    null|    null|               null|
  8. |       root.ln.wf02.wt02.status|    null|      root.ln| BOOLEAN|   PLAIN|     SNAPPY|                                       null|                                                    null|    null|               null|
  9. |  root.ln.wf01.wt01.temperature|    null|      root.ln|   FLOAT|     RLE|     SNAPPY|                                       null|                                                    null|    null|               null|
  10. |       root.ln.wf01.wt01.status|    null|      root.ln| BOOLEAN|   PLAIN|     SNAPPY|                                       null|                                                    null|    null|               null|
  11. +-------------------------------+--------+-------------+--------+--------+-----------+-------------------------------------------+--------------------------------------------------------+--------+-------------------+
  12. Total line number = 7
  13. It costs 0.016s
  15. +-----------------------------+-----+-------------+--------+--------+-----------+----+----------+--------+-------------------+
  16. |                   timeseries|alias|     database|dataType|encoding|compression|tags|attributes|deadband|deadband parameters|
  17. +-----------------------------+-----+-------------+--------+--------+-----------+----+----------+--------+-------------------+
  18. |   root.ln.wf02.wt02.hardware| null|      root.ln|    TEXT|   PLAIN|     SNAPPY|null|      null|    null|               null|
  19. |     root.ln.wf02.wt02.status| null|      root.ln| BOOLEAN|   PLAIN|     SNAPPY|null|      null|    null|               null|
  20. |root.ln.wf01.wt01.temperature| null|      root.ln|   FLOAT|     RLE|     SNAPPY|null|      null|    null|               null|
  21. |     root.ln.wf01.wt01.status| null|      root.ln| BOOLEAN|   PLAIN|     SNAPPY|null|      null|    null|               null|
  22. +-----------------------------+-----+-------------+--------+--------+-----------+----+----------+--------+-------------------+
  23. Total line number = 4
  24. It costs 0.004s

  • SHOW TIMESERIES LIMIT INT OFFSET INT

    returns all the timeseries information start from the offset and limit the number of series returned. For example,

  1. show timeseries root.ln.** limit 10 offset 10

  • SHOW LATEST TIMESERIES

    all the returned timeseries information should be sorted in descending order of the last timestamp of timeseries

It is worth noting that when the queried path does not exist, the system will return no timeseries.

Count Timeseries

IoTDB is able to use COUNT TIMESERIES <Path> to count the number of timeseries matching the path. SQL statements are as follows:

  1. IoTDB > COUNT TIMESERIES root.**
  2. IoTDB > COUNT TIMESERIES root.ln.**
  3. IoTDB > COUNT TIMESERIES root.ln.*.*.status
  4. IoTDB > COUNT TIMESERIES root.ln.wf01.wt01.status

Besides, LEVEL could be defined to show count the number of timeseries of each node at the given level in current Metadata Tree. This could be used to query the number of sensors under each device. The grammar is: COUNT TIMESERIES <Path> GROUP BY LEVEL=<INTEGER>.

For example, if there are several timeseries (use show timeseries to show all timeseries):

  1. +-------------------------------+--------+-------------+--------+--------+-----------+-------------------------------------------+--------------------------------------------------------+--------+-------------------+
  2. |                     timeseries|   alias|     database|dataType|encoding|compression|                                       tags|                                              attributes|deadband|deadband parameters|
  3. +-------------------------------+--------+-------------+--------+--------+-----------+-------------------------------------------+--------------------------------------------------------+--------+-------------------+
  4. |root.sgcc.wf03.wt01.temperature|    null|    root.sgcc|   FLOAT|     RLE|     SNAPPY|                                       null|                                                    null|    null|               null|
  5. |     root.sgcc.wf03.wt01.status|    null|    root.sgcc| BOOLEAN|   PLAIN|     SNAPPY|                                       null|                                                    null|    null|               null|
  6. |             root.turbine.d1.s1|newAlias| root.turbine|   FLOAT|     RLE|     SNAPPY|{"newTag1":"newV1","tag4":"v4","tag3":"v3"}|{"attr2":"v2","attr1":"newV1","attr4":"v4","attr3":"v3"}|    null|               null|
  7. |     root.ln.wf02.wt02.hardware|    null|      root.ln|    TEXT|   PLAIN|     SNAPPY|                               {"unit":"c"}|                                                    null|    null|               null|
  8. |       root.ln.wf02.wt02.status|    null|      root.ln| BOOLEAN|   PLAIN|     SNAPPY|                    {"description":"test1"}|                                                    null|    null|               null|
  9. |  root.ln.wf01.wt01.temperature|    null|      root.ln|   FLOAT|     RLE|     SNAPPY|                                       null|                                                    null|    null|               null|
  10. |       root.ln.wf01.wt01.status|    null|      root.ln| BOOLEAN|   PLAIN|     SNAPPY|                                       null|                                                    null|    null|               null|
  11. +-------------------------------+--------+-------------+--------+--------+-----------+-------------------------------------------+--------------------------------------------------------+--------+-------------------+
  12. Total line number = 7
  13. It costs 0.004s

Then the Metadata Tree will be as below:

Timeseries - 图1

As can be seen, `root` is considered as `LEVEL=0`. So when you enter statements such as:

  1. IoTDB > COUNT TIMESERIES root.** GROUP BY LEVEL=1
  2. IoTDB > COUNT TIMESERIES root.ln.** GROUP BY LEVEL=2
  3. IoTDB > COUNT TIMESERIES root.ln.wf01.* GROUP BY LEVEL=2

You will get following results:

  1. +------------+-----------------+
  2. |      column|count(timeseries)|
  3. +------------+-----------------+
  4. |   root.sgcc|                2|
  5. |root.turbine|                1|
  6. |     root.ln|                4|
  7. +------------+-----------------+
  8. Total line number = 3
  9. It costs 0.002s
  11. +------------+-----------------+
  12. |      column|count(timeseries)|
  13. +------------+-----------------+
  14. |root.ln.wf02|                2|
  15. |root.ln.wf01|                2|
  16. +------------+-----------------+
  17. Total line number = 2
  18. It costs 0.002s
  20. +------------+-----------------+
  21. |      column|count(timeseries)|
  22. +------------+-----------------+
  23. |root.ln.wf01|                2|
  24. +------------+-----------------+
  25. Total line number = 1
  26. It costs 0.002s

Note: The path of timeseries is just a filter condition, which has no relationship with the definition of level.

Tag and Attribute Management

We can also add an alias, extra tag and attribute information while creating one timeseries.

The differences between tag and attribute are:

  • Tag could be used to query the path of timeseries, we will maintain an inverted index in memory on the tag: Tag -> Timeseries
  • Attribute could only be queried by timeseries path : Timeseries -> Attribute

The SQL statements for creating timeseries with extra tag and attribute information are extended as follows:

  1. create timeseries root.turbine.d1.s1(temprature) with datatype=FLOAT, encoding=RLE, compression=SNAPPY tags(tag1=v1, tag2=v2) attributes(attr1=v1, attr2=v2)

The temprature in the brackets is an alias for the sensor s1. So we can use temprature to replace s1 anywhere.

IoTDB also supports using AS function to set alias. The difference between the two is: the alias set by the AS function is used to replace the whole time series name, temporary and not bound with the time series; while the alias mentioned above is only used as the alias of the sensor, which is bound with it and can be used equivalent to the original sensor name.

Notice that the size of the extra tag and attribute information shouldn’t exceed the tag_attribute_total_size.

We can update the tag information after creating it as following:

  • Rename the tag/attribute key
  1. ALTER timeseries root.turbine.d1.s1 RENAME tag1 TO newTag1
  • reset the tag/attribute value
  1. ALTER timeseries root.turbine.d1.s1 SET newTag1=newV1, attr1=newV1
  • delete the existing tag/attribute
  1. ALTER timeseries root.turbine.d1.s1 DROP tag1, tag2
  • add new tags
  1. ALTER timeseries root.turbine.d1.s1 ADD TAGS tag3=v3, tag4=v4
  • add new attributes
  1. ALTER timeseries root.turbine.d1.s1 ADD ATTRIBUTES attr3=v3, attr4=v4
  • upsert alias, tags and attributes

add alias or a new key-value if the alias or key doesn’t exist, otherwise, update the old one with new value.

  1. ALTER timeseries root.turbine.d1.s1 UPSERT ALIAS=newAlias TAGS(tag3=v3, tag4=v4) ATTRIBUTES(attr3=v3, attr4=v4)
  • show timeseries using tags
  1. SHOW TIMESERIES (<`PathPattern`>)? WhereClause

returns all the timeseries information that satisfy the where condition and match the pathPattern. SQL statements are as follows:

  1. ALTER timeseries root.ln.wf02.wt02.hardware ADD TAGS unit=c
  2. ALTER timeseries root.ln.wf02.wt02.status ADD TAGS description=test1
  3. show timeseries root.ln.** where unit=c
  4. show timeseries root.ln.** where description contains 'test1'

The results are shown below respectly:

  1. +--------------------------+-----+-------------+--------+--------+-----------+------------+----------+--------+-------------------+
  2. |                timeseries|alias|     database|dataType|encoding|compression|        tags|attributes|deadband|deadband parameters|
  3. +--------------------------+-----+-------------+--------+--------+-----------+------------+----------+--------+-------------------+
  4. |root.ln.wf02.wt02.hardware| null|      root.ln|    TEXT|   PLAIN|     SNAPPY|{"unit":"c"}|      null|    null|               null|
  5. +--------------------------+-----+-------------+--------+--------+-----------+------------+----------+--------+-------------------+
  6. Total line number = 1
  7. It costs 0.005s
  9. +------------------------+-----+-------------+--------+--------+-----------+-----------------------+----------+--------+-------------------+
  10. |              timeseries|alias|     database|dataType|encoding|compression|                   tags|attributes|deadband|deadband parameters|
  11. +------------------------+-----+-------------+--------+--------+-----------+-----------------------+----------+--------+-------------------+
  12. |root.ln.wf02.wt02.status| null|      root.ln| BOOLEAN|   PLAIN|     SNAPPY|{"description":"test1"}|      null|    null|               null|
  13. +------------------------+-----+-------------+--------+--------+-----------+-----------------------+----------+--------+-------------------+
  14. Total line number = 1
  15. It costs 0.004s
  • count timeseries using tags
  1. COUNT TIMESERIES (<`PathPattern`>)? WhereClause
  2. COUNT TIMESERIES (<`PathPattern`>)? WhereClause GROUP BY LEVEL=<INTEGER>

returns all the number of timeseries that satisfy the where condition and match the pathPattern. SQL statements are as follows:

  1. count timeseries
  2. count timeseries root.** where unit = c
  3. count timeseries root.** where unit = c group by level = 2

The results are shown below respectly :

  1. IoTDB> count timeseries
  2. +-----------------+
  3. |count(timeseries)|
  4. +-----------------+
  5. |                6|
  6. +-----------------+
  7. Total line number = 1
  8. It costs 0.019s
  9. IoTDB> count timeseries root.** where unit = c
  10. +-----------------+
  11. |count(timeseries)|
  12. +-----------------+
  13. |                2|
  14. +-----------------+
  15. Total line number = 1
  16. It costs 0.020s
  17. IoTDB> count timeseries root.** where unit = c group by level = 2
  18. +--------------+-----------------+
  19. |        column|count(timeseries)|
  20. +--------------+-----------------+
  21. |  root.ln.wf02|                2|
  22. |  root.ln.wf01|                0|
  23. |root.sgcc.wf03|                0|
  24. +--------------+-----------------+
  25. Total line number = 3
  26. It costs 0.011s

Notice that, we only support one condition in the where clause. Either it’s an equal filter or it is an contains filter. In both case, the property in the where condition must be a tag.

create aligned timeseries

  1. create aligned timeseries root.sg1.d1(s1 INT32 tags(tag1=v1, tag2=v2) attributes(attr1=v1, attr2=v2), s2 DOUBLE tags(tag3=v3, tag4=v4) attributes(attr3=v3, attr4=v4))

The execution result is as follows:

  1. IoTDB> show timeseries
  2. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+
  3. |    timeseries|alias|     database|dataType|encoding|compression|                     tags|                 attributes|deadband|deadband parameters|
  4. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+
  5. |root.sg1.d1.s1| null|     root.sg1|   INT32|     RLE|     SNAPPY|{"tag1":"v1","tag2":"v2"}|{"attr2":"v2","attr1":"v1"}|    null|               null|
  6. |root.sg1.d1.s2| null|     root.sg1|  DOUBLE| GORILLA|     SNAPPY|{"tag4":"v4","tag3":"v3"}|{"attr4":"v4","attr3":"v3"}|    null|               null|
  7. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+

Support query：

  1. IoTDB> show timeseries where tag1='v1'
  2. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+
  3. |    timeseries|alias|     database|dataType|encoding|compression|                     tags|                 attributes|deadband|deadband parameters|
  4. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+
  5. |root.sg1.d1.s1| null|     root.sg1|   INT32|     RLE|     SNAPPY|{"tag1":"v1","tag2":"v2"}|{"attr2":"v2","attr1":"v1"}|    null|               null|
  6. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+

The above operations are supported for timeseries tag, attribute updates, etc.