Database Management

February 18, 2025About 2 min

Database Management

1. Database Management

1.1 Create a Database

This command is used to create a database.

Syntax:

 CREATE DATABASE (IF NOT EXISTS)? <DATABASE_NAME> (WITH properties)?

**Note: **

<DATABASE_NAME>: The name of the database, with the following characteristics:

Case-insensitive.
Can include commas (,), underscores (_), numbers, letters, and Chinese characters.
Maximum length is 64 characters.
Names with special characters or Chinese characters must be enclosed in double quotes ("").

WITH properties: Property names are case-insensitive and currently cannot be modified. For more details, refer to the case sensitivity rules case-sensitivity。Configurable properties include:

Property	Description	Default Value
TTL	Automatic data expiration time, in milliseconds	`INF`
TIME_PARTITION_INTERVAL	Time partition interval for the database, in milliseconds	`604800000` (7 days)
SCHEMA_REGION_GROUP_NUM	Number of metadata replica groups; generally does not require modification	`1`
DATA_REGION_GROUP_NUM	Number of data replica groups; generally does not require modification	`2`

Examples:

CREATE DATABASE database1;
CREATE DATABASE IF NOT EXISTS database1;

// Sets TTL to 1 year.
CREATE DATABASE IF NOT EXISTS database1 with(TTL=31536000000);

1.2 Use a Database

Specify the current database as the namespace for table operations.

Syntax:

USE <DATABASE_NAME>

Example:

USE database1

1.3 View the Current Database

Displays the name of the currently connected database. If no USE statement has been executed, the default is null.

Syntax:

SHOW CURRENT_DATABASE

Example:

IoTDB> SHOW CURRENT_DATABASE;
+---------------+
|CurrentDatabase|
+---------------+
|           null|
+---------------+

IoTDB> USE test;

IoTDB> SHOW CURRENT_DATABASE;
+---------------+
|CurrentDatabase|
+---------------+
|   iot_database|
+---------------+

1.4 View All Databases

Displays all databases and their properties.

Syntax:

SHOW DATABASES (DETAILS)?

Columns Explained:

Column Name	Description
database	Name of the database.
TTL	Data retention period. If TTL is specified when creating a database, it applies to all tables within the database. You can also set or update the TTL of individual tables using create table 、alter table .
SchemaReplicationFactor	Number of metadata replicas, ensuring metadata high availability. This can be configured in the `iotdb-system.properties` file under the `schema_replication_factor` property.
DataReplicationFactor	Number of data replicas, ensuring data high availability. This can be configured in the `iotdb-system.properties` file under the `data_replication_factor` property.
TimePartitionInterval	Time partition interval, determining how often data is grouped into directories on disk. The default is typically one week.
Model	Returned when using the `DETAILS` option, showing the data model corresponding to each database (e.g., timeseries tree model or device table model).

Examples:

IoTDB> show databases
+---------+-------+-----------------------+---------------------+---------------------+
| Database|TTL(ms)|SchemaReplicationFactor|DataReplicationFactor|TimePartitionInterval|
+---------+-------+-----------------------+---------------------+---------------------+
|test_prop|    300|                      3|                    2|               100000|
|    test2|    300|                      3|                    2|            604800000|
+---------+-------+-----------------------+---------------------+---------------------+
IoTDB> show databases details
+---------+-------+-----------------------+---------------------+---------------------+-----------------------+-----------------------+
| Database|TTL(ms)|SchemaReplicationFactor|DataReplicationFactor|TimePartitionInterval|SchemaRegionGroupNum|  DataRegionGroupNum|
+---------+-------+-----------------------+---------------------+---------------------+-----------------------+-----------------------+
|test_prop|    300|                      3|                    2|               100000|                      1|                      2|
|    test2|    300|                      3|                    2|            604800000|                      1|                      2|
+---------+-------+-----------------------+---------------------+---------------------+-----------------------+-----------------------+

1.5 Update a Database

Currently not supported; will be available after version 2.0.2.1.

1.6 Delete a Database

Deletes the specified database and all associated tables and data.

Syntax:

DROP DATABASE (IF EXISTS)? <DATABASE_NAME>

Note:

A database currently in use can still be dropped.
Deleting a database removes all its tables and stored data.

Example:

DROP DATABASE IF EXISTS database1