Database Management

In the table model, a database is the top-level organizational structure for 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 and specify the database used by the current session through USE <DATABASE_NAME>.

A database can be configured with properties such as TTL, time partition interval, the maximum number of SchemaRegionGroups, and the maximum number of DataRegionGroups. The database-level 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 organizes and manages multiple tables. Databases can be divided by business domain, project, tenant, or data isolation requirements. For example, tables for a group of devices in the same business system can be placed in one database to manage their lifecycle, permissions, and query scope uniformly.

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

1.2 TTL

TTL specifies how long data is retained, in milliseconds. Data that exceeds the TTL is automatically expired and deleted. Setting an appropriate TTL controls disk space usage and prevents accumulated historical data from affecting storage costs and query performance.

TTL can be set at either the database level or the table level. For more information, see TTL Delete Data.

1.3 Time Partition Interval

The time partition interval determines the time range used to group data into directories on disk. The default value is 604800000 ms, or one week, and is suitable for most scenarios.

1.4 RegionGroup

IoTDB divides metadata and data into Regions managed by DataNodes. The MAX_SCHEMA_REGION_GROUP_NUM and MAX_DATA_REGION_GROUP_NUM database properties specify the maximum numbers of schema replica groups and data replica groups, respectively. These properties generally do not need to be changed manually.

2. Database Management

2.1 Create a Database

Creates a database.

Syntax:

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

Description:

1. <DATABASE_NAME> is the database name and has the following characteristics:
   • It is case-insensitive and is displayed in lowercase after the database is created.
   • It cannot exceed 64 characters.
   • A name that contains underscores (_), digits (except as the first character), or English letters can be created directly.
   • A name that contains special characters (such as a backtick), Chinese characters, or starts with a digit must be enclosed in double quotation marks ("").
2. 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
MAX_SCHEMA_REGION_GROUP_NUM	Maximum number of SchemaRegionGroups to which the database can automatically expand. The value must be a positive integer. Supported starting from V2.0.11.	1
MAX_DATA_REGION_GROUP_NUM	Maximum number of DataRegionGroups to which the database can automatically expand. The value must be a positive integer. Supported starting from V2.0.11.	2

Notes:

• Property names are case-insensitive. For details, see Case Sensitivity.
• The maximum schema/data region group quotas, maxSchemaRegionGroupNum and maxDataRegionGroupNum, can be set or adjusted through SQL when creating or modifying a database only when schema_region_group_extension_policy and data_region_group_extension_policy in iotdb-common.properties are set to CUSTOM.

Example:

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

2.2 Use a Database

Specifies the current database as the namespace for tables.

Syntax:

USE <DATABASE_NAME>

Example:

USE database1;

2.3 View the Current Database

Returns the name of the database used by the current session. If no database has been specified with a USE statement, the default value 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:

Column Name	Description
Database	Database name.
TTL	Data retention period. A database-level TTL applies to all tables in the database by default. You can also set or update a table-level TTL through CREATE TABLE or ALTER TABLE.
SchemaReplicationFactor	Number of schema replicas used to ensure metadata availability. This value can be changed through schema_replication_factor in iotdb-system.properties.
DataReplicationFactor	Number of data replicas used to ensure data availability. This value can be changed through data_replication_factor in iotdb-system.properties.
TimePartitionInterval	Time partition interval, which determines the time range used to group data into directories on disk. The default value of one week is suitable for most scenarios.
SchemaRegionGroupNum	Returned with DETAILS. Number of schema replica groups currently owned by the database.
MaxSchemaRegionGroupNum	Returned with DETAILS. Maximum number of schema replica groups allowed for the database.
DataRegionGroupNum	Returned with DETAILS. Number of data replica groups currently owned by the database.
MaxDataRegionGroupNum	Returned with DETAILS. Maximum number of data replica groups allowed for the database.

Example:

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 Count Databases

Counts the total number of databases. This syntax is supported starting from V2.0.11.

Syntax:

COUNT DATABASES

Example:

TimechoDB> COUNT DATABASES
+-----+
|count|
+-----+
|    2|
+-----+

2.6 Update a Database

Modifies supported database properties.

Syntax:

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

Description:

1. ALTER DATABASE currently supports modifying only MAX_SCHEMA_REGION_GROUP_NUM, MAX_DATA_REGION_GROUP_NUM, and TTL.

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.7 Delete a Database

Deletes a database.

Syntax:

DROP DATABASE (IF EXISTS)? <DATABASE_NAME>

Description:

1. A database can be dropped even if it is the current database selected by USE.
2. Dropping a database deletes all tables in the database and all data stored in those tables.

Example:

DROP DATABASE IF EXISTS database1;