Database Management

About 4 min

Database Management

In the table model, a database is the upper-level organization of tables and is used to manage a group of business-related tables. Before creating tables, writing data, or querying data, you usually need to create a database first and use USE <DATABASE_NAME> to specify the database used by the current session.

A database can be configured with properties such as TTL, time partition interval, SchemaRegionGroup count, and DataRegionGroup count. The TTL is used as the default data retention period for tables in the database. If a table has its own TTL, the table-level TTL takes precedence.

1. Basic Concepts

1.1 Database

A database is used to organize and manage multiple tables. Databases are usually divided by business domain, project, tenant, or data isolation requirements. For example, tables of devices that belong to the same business system can be placed in the same database to simplify lifecycle, permission, and query scope management.

In the table model, the database name is also the namespace of tables. After executing USE database1, subsequent table operations that do not explicitly specify a database name will apply to database1 by default.

1.2 TTL

TTL specifies how long data is retained, in milliseconds. Data older than the TTL will expire and be deleted automatically. A proper TTL helps control disk usage and prevents accumulated historical data from increasing storage cost and affecting query performance.

TTL can be set at the database level or at the table level. For more details, see Data Retention Time.

1.3 Time Partition Interval

The time partition interval determines how data is grouped into directories on disk. The default value is 604800000 ms, namely one week. The default value is usually sufficient.

1.4 RegionGroup

IoTDB divides metadata and data into Regions, which are managed by DataNodes. The database-level attributes SCHEMA_REGION_GROUP_NUM and DATA_REGION_GROUP_NUM indicate the number of metadata replica groups and data replica groups respectively, and usually do not need to be modified manually.

2. Database Management

2.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. After creation, it will be displayed uniformly in lowercase.
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 ("").

The WITH properties clause supports the following properties:

Property	Description	Default Value
`TTL`	Automatic data expiration time, in milliseconds. The value must be a positive integer	`INF`
`TIME_PARTITION_INTERVAL`	Time partition interval for the database, in milliseconds. The value must be a positive integer	`604800000` (7 days)
`MAX_SCHEMA_REGION_GROUP_NUM`	Maximum number of SchemaRegionGroups allowed during automatic expansion. The value must be a positive integer	`1`
`MAX_DATA_REGION_GROUP_NUM`	Maximum number of DataRegionGroups allowed during automatic expansion. The value must be a positive integer	`2`

Notes:

Property names are case-insensitive. For more details, see case-sensitivity.
SQL-based creation or modification of the maximum schema/data region group quota, namely maxSchemaRegionGroupNum and maxDataRegionGroupNum, is supported only when the schema_region_group_extension_policy and data_region_group_extension_policy parameters in iotdb-common.properties are set to the CUSTOM policy.
MAX_SCHEMA_REGION_GROUP_NUM and MAX_DATA_REGION_GROUP_NUM are supported since V2.0.10.2.

Examples:

CREATE DATABASE IF NOT EXISTS database1 with(TTL=31536000000);

2.2 Use a Database

Specify the current database as the namespace for table operations.

Syntax:

USE <DATABASE_NAME>

Example:

USE database1;

2.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:

USE database1;
SHOW CURRENT_DATABASE;

+---------------+
|CurrentDatabase|
+---------------+
|      database1|
+---------------+

2.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 and 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.
SchemaRegionGroupNum	Returned when using the `DETAILS` option, showing the number of metadata replica groups in the database. This usually does not need to be modified.
MaxSchemaRegionGroupNum	Returned when using the `DETAILS` option, showing the maximum number of metadata replica groups allowed for the database.
DataRegionGroupNum	Returned when using the `DETAILS` option, showing the number of data replica groups in the database. This usually does not need to be modified.
MaxDataRegionGroupNum	Returned when using the `DETAILS` option, showing the maximum number of data replica groups allowed for the database.

Examples:

SHOW DATABASES DETAILS;

+------------------+-------+-----------------------+---------------------+---------------------+--------------------+-----------------------+------------------+---------------------+
|          Database|TTL(ms)|SchemaReplicationFactor|DataReplicationFactor|TimePartitionInterval|SchemaRegionGroupNum|MaxSchemaRegionGroupNum|DataRegionGroupNum|MaxDataRegionGroupNum|
+------------------+-------+-----------------------+---------------------+---------------------+--------------------+-----------------------+------------------+---------------------+
|         database1|    INF|                      1|                    1|            604800000|                   1|                      1|                 2|                    2|
|information_schema|    INF|                   null|                 null|                 null|                null|                   null|              null|                 null|
+------------------+-------+-----------------------+---------------------+---------------------+--------------------+-----------------------+------------------+---------------------+

2.5 Update a Database

Used to modify some attributes in the database.

Syntax:

ALTER DATABASE (IF EXISTS)? database=identifier SET PROPERTIES propertyAssignments

Note:

The ALTER DATABASE operation currently only supports modifications to the database's MAX_SCHEMA_REGION_GROUP_NUM, MAX_DATA_REGION_GROUP_NUM, and TTL attributes.

Example:

ALTER DATABASE database1 SET PROPERTIES TTL=31536000000;
ALTER DATABASE database1 SET PROPERTIES MAX_SCHEMA_REGION_GROUP_NUM=2, MAX_DATA_REGION_GROUP_NUM=4;

2.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;