Database Management

Create Database

According to the storage model we can set up the corresponding database. Two SQL statements are supported for creating databases, as follows:

  1. IoTDB > create database root.ln
  2. IoTDB > create database root.sgcc

We can thus create two databases using the above two SQL statements.

It is worth noting that when the path itself or the parent/child layer of the path is already created as database, the path is then not allowed to be created as database. For example, it is not feasible to create root.ln.wf01 as database when two databases root.ln and root.sgcc exist. The system gives the corresponding error prompt as shown below:

  1. IoTDB> CREATE DATABASE root.ln.wf01
  2. Msg: 300: root.ln has already been created as database.
  3. IoTDB> create database root.ln.wf01
  4. Msg: 300: root.ln has already been created as database.

The LayerName of database can only be characters, numbers, underscores. If you want to set it to pure numbers or contain other characters, you need to enclose the database name with backticks (``).

Besides, if deploy on Windows system, the LayerName is case-insensitive, which means it’s not allowed to create databases root.ln and root.LN at the same time.

Show Databases

After creating the database, we can use the SHOW DATABASES statement and SHOW DATABASES <PathPattern> to view the databases. The SQL statements are as follows:

  1. IoTDB> SHOW DATABASES
  2. IoTDB> SHOW DATABASES root.**

The result is as follows:

  1. +-------------+----+-------------------------+-----------------------+-----------------------+
  2. |database| ttl|schema_replication_factor|data_replication_factor|time_partition_interval|
  3. +-------------+----+-------------------------+-----------------------+-----------------------+
  4. |    root.sgcc|null|                        2|                      2|                 604800|
  5. |      root.ln|null|                        2|                      2|                 604800|
  6. +-------------+----+-------------------------+-----------------------+-----------------------+
  7. Total line number = 2
  8. It costs 0.060s

Delete Database

User can use the DELETE DATABASE <PathPattern> statement to delete all databases matching the pathPattern. Please note the data in the database will also be deleted.

  1. IoTDB > DELETE DATABASE root.ln
  2. IoTDB > DELETE DATABASE root.sgcc
  3. // delete all data, all timeseries and all databases
  4. IoTDB > DELETE DATABASE root.**

Count Databases

User can use the COUNT DATABASE <PathPattern> statement to count the number of databases. It is allowed to specify PathPattern to count the number of databases matching the PathPattern.

SQL statement is as follows:

  1. IoTDB> count databases
  2. IoTDB> count databases root.*
  3. IoTDB> count databases root.sgcc.*
  4. IoTDB> count databases root.sgcc

The result is as follows:

  1. +-------------+
  2. |     database|
  3. +-------------+
  4. |    root.sgcc|
  5. | root.turbine|
  6. |      root.ln|
  7. +-------------+
  8. Total line number = 3
  9. It costs 0.003s
  11. +-------------+
  12. |     database|
  13. +-------------+
  14. |            3|
  15. +-------------+
  16. Total line number = 1
  17. It costs 0.003s
  19. +-------------+
  20. |     database|
  21. +-------------+
  22. |            3|
  23. +-------------+
  24. Total line number = 1
  25. It costs 0.002s
  27. +-------------+
  28. |     database|
  29. +-------------+
  30. |            0|
  31. +-------------+
  32. Total line number = 1
  33. It costs 0.002s
  35. +-------------+
  36. |     database|
  37. +-------------+
  38. |            1|
  39. +-------------+
  40. Total line number = 1
  41. It costs 0.002s