Support OrderBy in SHOW TIMESERIES/DEVICES

Open Cpaulyz opened this issue 1 year ago • 1 comments

Description

Can be used with time condition.

SHOW TIEMSERIES

IoTDB> show timeseries
+--------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
|    Timeseries|Alias|Database|DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+--------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
| root.db.d1.s1| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.db.d1.s2| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.db.d2.s1| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.db.d2.s2| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.db1.d2.s1| null|root.db1|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.db1.d2.s2| null|root.db1|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.aaa.d2.s1| null|root.aaa|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.aaa.d2.s2| null|root.aaa|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
+--------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
Total line number = 8
It costs 0.027s
IoTDB> show timeseries order by timeseries
+--------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
|    Timeseries|Alias|Database|DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+--------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
|root.aaa.d2.s1| null|root.aaa|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.aaa.d2.s2| null|root.aaa|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.db.d1.s1| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.db.d1.s2| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.db.d2.s1| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.db.d2.s2| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.db1.d2.s1| null|root.db1|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.db1.d2.s2| null|root.db1|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
+--------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
Total line number = 8
It costs 0.011s
IoTDB> show timeseries order by timeseries desc
+--------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
|    Timeseries|Alias|Database|DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+--------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
|root.db1.d2.s2| null|root.db1|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.db1.d2.s1| null|root.db1|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.db.d2.s2| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.db.d2.s1| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.db.d1.s2| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
| root.db.d1.s1| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.aaa.d2.s2| null|root.aaa|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.aaa.d2.s1| null|root.aaa|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
+--------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
Total line number = 8
IoTDB> show timeseries root.db.** where time>1
+-------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
|   Timeseries|Alias|Database|DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+-------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
|root.db.d2.s1| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.db.d2.s2| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.db.d1.s1| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.db.d1.s2| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
+-------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
Total line number = 4
It costs 1.538s
IoTDB> show timeseries root.db.** where time>1 order by timeseries desc
+-------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
|   Timeseries|Alias|Database|DataType|Encoding|Compression|Tags|Attributes|Deadband|DeadbandParameters|ViewType|
+-------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
|root.db.d2.s2| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.db.d2.s1| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.db.d1.s2| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
|root.db.d1.s1| null| root.db|  DOUBLE| GORILLA|        LZ4|null|      null|    null|              null|    BASE|
+-------------+-----+--------+--------+--------+-----------+----+----------+--------+------------------+--------+
Total line number = 4
It costs 0.052s

Notice:

Can not be used together with latest.
Only support timeseries as order key now.

IoTDB> show timeseries order by alias
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: SHOW TIMESERIES only supports ORDER BY TIMESERIES now.
IoTDB> show timeseries order by abc
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: SHOW TIMESERIES only supports ORDER BY TIMESERIES now.
IoTDB> show latest timeseries order by timeseries
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: ORDER BY and LATEST cannot be used at the same time.

SHOW DEVICES

Only support device as order key now.

IoTDB> show devices
+-----------+---------+--------+---+
|     Device|IsAligned|Template|TTL|
+-----------+---------+--------+---+
| root.db.d1|    false|    null|INF|
| root.db.d2|    false|    null|INF|
|root.db1.d2|    false|    null|INF|
|root.aaa.d2|    false|    null|INF|
+-----------+---------+--------+---+
Total line number = 4
It costs 0.025s
IoTDB> show devices order by device
+-----------+---------+--------+---+
|     Device|IsAligned|Template|TTL|
+-----------+---------+--------+---+
|root.aaa.d2|    false|    null|INF|
| root.db.d1|    false|    null|INF|
| root.db.d2|    false|    null|INF|
|root.db1.d2|    false|    null|INF|
+-----------+---------+--------+---+
Total line number = 4
It costs 0.037s
IoTDB> show devices order by device desc
+-----------+---------+--------+---+
|     Device|IsAligned|Template|TTL|
+-----------+---------+--------+---+
|root.db1.d2|    false|    null|INF|
| root.db.d2|    false|    null|INF|
| root.db.d1|    false|    null|INF|
|root.aaa.d2|    false|    null|INF|
+-----------+---------+--------+---+
Total line number = 4
It costs 0.030s
IoTDB> show devices root.db.* where time>1 order by device desc
+----------+---------+--------+---+
|    Device|IsAligned|Template|TTL|
+----------+---------+--------+---+
|root.db.d2|    false|    null|INF|
|root.db.d1|    false|    null|INF|
+----------+---------+--------+---+
Total line number = 2
It costs 0.060s

IoTDB> show devices order by devices desc
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: SHOW DEVICES only supports ORDER BY DEVICE now.
IoTDB> show devices order by abc desc
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: SHOW DEVICES only supports ORDER BY DEVICE now.

Jun 21 '24 17:06 Cpaulyz

Consider show child nodes and show child paths?

Jul 08 '24 10:07 CritasWang