System Tables

IoTDB has a built-in system database called INFORMATION_SCHEMA, which contains a series of system tables for storing IoTDB runtime information (such as currently executing SQL statements, etc.). Currently, the INFORMATION_SCHEMA database only supports read operations.

1. System Database

• ​Name​: INFORMATION_SCHEMA
• ​Commands​: Read-only, only supports Show databases (DETAILS) / Show Tables (DETAILS) / Use. Other operations will result in an error: "The database 'information_schema' can only be queried."
• ​Attributes​: TTL=INF, other attributes default to null
• ​SQL Example​:

IoTDB> show databases
+------------------+-------+-----------------------+---------------------+---------------------+
|          Database|TTL(ms)|SchemaReplicationFactor|DataReplicationFactor|TimePartitionInterval|
+------------------+-------+-----------------------+---------------------+---------------------+
|information_schema|    INF|                   null|                 null|                 null|
+------------------+-------+-----------------------+---------------------+---------------------+

IoTDB> show tables from information_schema
+-------------+-------+
|    TableName|TTL(ms)|
+-------------+-------+
|    databases|    INF|
|       tables|    INF|
| pipe_plugins|    INF|
|subscriptions|    INF|
|      regions|    INF|
|      columns|    INF|
|       topics|    INF|
|      queries|    INF|
|        pipes|    INF|
+-------------+-------+

2. System Tables

• ​Names​: DATABASES, TABLES, REGIONS, QUERIES, COLUMNS, PIPES, PIPE_PLUGINS, SUBSCRIPTION, TOPICS (detailed descriptions in later sections)
• ​Operations​: Read-only, only supports SELECT, COUNT/SHOW DEVICES, DESC. Any modifications to table structure or content are not allowed and will result in an error: "The database 'information_schema' can only be queried."
• ​Column Names​: System table column names are all lowercase by default and separated by underscores (_).

2.1 DATABASES

• Contains information about all databases in the cluster.
• Table structure is as follows:

Column Name	Data Type	Column Type	Description
database	STRING	TAG	Database name
ttl(ms)	STRING	ATTRIBUTE	Data retention time
schema_replication_factor	INT32	ATTRIBUTE	Schema replica count
data_replication_factor	INT32	ATTRIBUTE	Data replica count
time_partition_interval	INT64	ATTRIBUTE	Time partition interval
schema_region_group_num	INT32	ATTRIBUTE	Number of schema region groups
data_region_group_num	INT32	ATTRIBUTE	Number of data region groups

• Query Example:

IoTDB> select * from information_schema.databases
+------------------+-------+-------------------------+-----------------------+-----------------------+-----------------------+---------------------+
|          database|ttl(ms)|schema_replication_factor|data_replication_factor|time_partition_interval|schema_region_group_num|data_region_group_num|
+------------------+-------+-------------------------+-----------------------+-----------------------+-----------------------+---------------------+
|information_schema|    INF|                     null|                   null|                   null|                   null|                 null|
|         database1|    INF|                        1|                      1|              604800000|                      0|                    0|
+------------------+-------+-------------------------+-----------------------+-----------------------+-----------------------+---------------------+

2.2 TABLES

• Contains information about all tables in the cluster.
• Table structure is as follows:

Column Name	Data Type	Column Type	Description
database	STRING	TAG	Database name
table_name	STRING	TAG	Table name
ttl(ms)	STRING	ATTRIBUTE	Data retention time
status	STRING	ATTRIBUTE	Status
comment	STRING	ATTRIBUTE	Description/comment

• Note: Possible values for status: USING, PRE_CREATE, PRE_DELETE. For details, refer to the View Tables in Table Management documentation
• Query Example:

IoTDB> select * from information_schema.tables
+------------------+-------------+-----------+------+-------+
|          database|   table_name|    ttl(ms)|status|comment|
+------------------+-------------+-----------+------+-------+
|information_schema|    databases|        INF| USING|   null|
|information_schema|       tables|        INF| USING|   null|
|information_schema| pipe_plugins|        INF| USING|   null|
|information_schema|subscriptions|        INF| USING|   null|
|information_schema|      regions|        INF| USING|   null|
|information_schema|      columns|        INF| USING|   null|
|information_schema|       topics|        INF| USING|   null|
|information_schema|      queries|        INF| USING|   null|
|information_schema|        pipes|        INF| USING|   null|
|         database1|       table1|31536000000| USING|   null|
+------------------+-------------+-----------+------+-------+

2.3 REGIONS

• Contains information about all regions in the cluster.
• Table structure is as follows:

Column Name	Data Type	Column Type	Description
region_id	INT32	TAG	Region ID
datanode_id	INT32	TAG	DataNode ID
type	STRING	ATTRIBUTE	Type (SchemaRegion/DataRegion)
status	STRING	ATTRIBUTE	Status (Running,Unknown, etc.)
database	STRING	ATTRIBUTE	Database name
series_slot_num	INT32	ATTRIBUTE	Number of series slots
time_slot_num	INT64	ATTRIBUTE	Number of time slots
rpc_address	STRING	ATTRIBUTE	RPC address
rpc_port	INT32	ATTRIBUTE	RPC port
internal_address	STRING	ATTRIBUTE	Internal communication address
role	STRING	ATTRIBUTE	Role (Leader/Follower)
create_time	TIMESTAMP	ATTRIBUTE	Creation time
tsfile_size_bytes	INT64	ATTRIBUTE	- For​DataRegion with statistics ​​: Total file size of TsFiles. - ForDataRegion without statistics(Unknown):-1. - For​SchemaRegion​:null.

• Query Example:

IoTDB> select * from information_schema.regions
+---------+-----------+------------+-------+---------+---------------+-------------+-----------+--------+----------------+------+-----------------------------+-----------------+
|region_id|datanode_id|        type| status| database|series_slot_num|time_slot_num|rpc_address|rpc_port|internal_address|  role|                  create_time|tsfile_size_bytes|
+---------+-----------+------------+-------+---------+---------------+-------------+-----------+--------+----------------+------+-----------------------------+-----------------+
|        0|          1|SchemaRegion|Running|database1|             12|            0|    0.0.0.0|    6667|       127.0.0.1|Leader|2025-03-31T11:19:08.485+08:00|             null|
|        1|          1|  DataRegion|Running|database1|              6|            6|    0.0.0.0|    6667|       127.0.0.1|Leader|2025-03-31T11:19:09.156+08:00|             3985|
|        2|          1|  DataRegion|Running|database1|              6|            6|    0.0.0.0|    6667|       127.0.0.1|Leader|2025-03-31T11:19:09.156+08:00|             3841|
+---------+-----------+------------+-------+---------+---------------+-------------+-----------+--------+----------------+------+-----------------------------+-----------------+

2.4 QUERIES

• Contains information about all currently executing queries in the cluster. Can also be queried using the SHOW QUERIES syntax.
• Table structure is as follows:

Column Name	Data Type	Column Type	Description
query_id	STRING	TAG	Query ID
start_time	TIMESTAMP	ATTRIBUTE	Query start timestamp (precision matches system precision)
datanode_id	INT32	ATTRIBUTE	DataNode ID that initiated the query
elapsed_time	FLOAT	ATTRIBUTE	Query execution duration (in seconds)
statement	STRING	ATTRIBUTE	SQL statement of the query
user	STRING	ATTRIBUTE	User who initiated the query

• Query Example:

IoTDB> select * from information_schema.queries
+-----------------------+-----------------------------+-----------+------------+----------------------------------------+----+
|               query_id|                   start_time|datanode_id|elapsed_time|                               statement|user|
+-----------------------+-----------------------------+-----------+------------+----------------------------------------+----+
|20250331_023242_00011_1|2025-03-31T10:32:42.360+08:00|          1|       0.025|select * from information_schema.queries|root|
+-----------------------+-----------------------------+-----------+------------+----------------------------------------+----+

2.5 COLUMNS

• Contains information about all columns in tables across the cluster
• Table structure is as follows:

Column Name	Data Type	Column Type	Description
database	STRING	TAG	Database name
table_name	STRING	TAG	Table name
column_name	STRING	TAG	Column name
datatype	STRING	ATTRIBUTE	Column data type
category	STRING	ATTRIBUTE	Column category
status	STRING	ATTRIBUTE	Column status
comment	STRING	ATTRIBUTE	Column description

Notes: Possible values for status: USING, PRE_DELETE. For details, refer to Viewing Table Columns in Table Management documentation.

Users can only query tables for which they have select permissions .

• Query Example:

IoTDB> select * from information_schema.columns where database = 'database1'
+---------+----------+------------+---------+---------+------+-------+
| database|table_name| column_name| datatype| category|status|comment|
+---------+----------+------------+---------+---------+------+-------+
|database1|    table1|        time|TIMESTAMP|     TIME| USING|   null|
|database1|    table1|      region|   STRING|      TAG| USING|   null|
|database1|    table1|    plant_id|   STRING|      TAG| USING|   null|
|database1|    table1|   device_id|   STRING|      TAG| USING|   null|
|database1|    table1|    model_id|   STRING|ATTRIBUTE| USING|   null|
|database1|    table1| maintenance|   STRING|ATTRIBUTE| USING|   null|
|database1|    table1| temperature|    FLOAT|    FIELD| USING|   null|
|database1|    table1|    humidity|    FLOAT|    FIELD| USING|   null|
|database1|    table1|      status|  BOOLEAN|    FIELD| USING|   null|
|database1|    table1|arrival_time|TIMESTAMP|    FIELD| USING|   null|
+---------+----------+------------+---------+---------+------+-------+

2.6 PIPES

• Contains information about all pipes in the cluster
• Table structure is as follows:

Column Name	Data Type	Column Type	Description
id	STRING	TAG	Pipe name
creation_time	TIMESTAMP	ATTRIBUTE	Creation time
state	STRING	ATTRIBUTE	Pipe status (RUNNING/STOPPED)
pipe_source	STRING	ATTRIBUTE	Source plugin parameters
pipe_processor	STRING	ATTRIBUTE	Processor plugin parameters
pipe_sink	STRING	ATTRIBUTE	Sink plugin parameters
exception_message	STRING	ATTRIBUTE	Exception message
remaining_event_count	INT64	ATTRIBUTE	Remaining event count (-1if Unknown)
estimated_remaining_seconds	DOUBLE	ATTRIBUTE	Estimated remaining time in seconds (-1if Unknown)

• Query Example:

select * from information_schema.pipes
+----------+-----------------------------+-------+--------------------------------------------------------------------------+--------------+-----------------------------------------------------------------------+-----------------+---------------------+---------------------------+
|        id|                creation_time|  state|                                                               pipe_source|pipe_processor|                                                              pipe_sink|exception_message|remaining_event_count|estimated_remaining_seconds|
+----------+-----------------------------+-------+--------------------------------------------------------------------------+--------------+-----------------------------------------------------------------------+-----------------+---------------------+---------------------------+
|tablepipe1|2025-03-31T12:25:24.040+08:00|RUNNING|{__system.sql-dialect=table, source.password=******, source.username=root}|            {}|{format=hybrid, node-urls=192.168.xxx.xxx:6667, sink=iotdb-thrift-sink}|                 |                    0|                        0.0|
+----------+-----------------------------+-------+--------------------------------------------------------------------------+--------------+-----------------------------------------------------------------------+-----------------+---------------------+---------------------------+

2.7 PIPE_PLUGINS

• Contains information about all PIPE plugins in the cluster
• Table structure is as follows:

Column Name	Data Type	Column Type	Description
plugin_name	STRING	TAG	Plugin name
plugin_type	STRING	ATTRIBUTE	Plugin type (Builtin/External)
class_name	STRING	ATTRIBUTE	Plugin's main class name
plugin_jar	STRING	ATTRIBUTE	Plugin's JAR file name (nullfor builtin type)

• Query Example:

IoTDB> select * from information_schema.pipe_plugins
+---------------------+-----------+-------------------------------------------------------------------------------------------------+----------+
|          plugin_name|plugin_type|                                                                                       class_name|plugin_jar|
+---------------------+-----------+-------------------------------------------------------------------------------------------------+----------+
|IOTDB-THRIFT-SSL-SINK|    Builtin|org.apache.iotdb.commons.pipe.agent.plugin.builtin.connector.iotdb.thrift.IoTDBThriftSslConnector|      null|
|   IOTDB-AIR-GAP-SINK|    Builtin|   org.apache.iotdb.commons.pipe.agent.plugin.builtin.connector.iotdb.airgap.IoTDBAirGapConnector|      null|
|      DO-NOTHING-SINK|    Builtin|        org.apache.iotdb.commons.pipe.agent.plugin.builtin.connector.donothing.DoNothingConnector|      null|
| DO-NOTHING-PROCESSOR|    Builtin|        org.apache.iotdb.commons.pipe.agent.plugin.builtin.processor.donothing.DoNothingProcessor|      null|
|    IOTDB-THRIFT-SINK|    Builtin|   org.apache.iotdb.commons.pipe.agent.plugin.builtin.connector.iotdb.thrift.IoTDBThriftConnector|      null|
|         IOTDB-SOURCE|    Builtin|                org.apache.iotdb.commons.pipe.agent.plugin.builtin.extractor.iotdb.IoTDBExtractor|      null|
+---------------------+-----------+-------------------------------------------------------------------------------------------------+----------+

2.8 SUBSCRIPTIONS

• Contains information about all data subscriptions in the cluster
• Table structure is as follows:

Column Name	Data Type	Column Type	Description
topic_name	STRING	TAG	Subscription topic name
consumer_group_name	STRING	TAG	Consumer group name
subscribed_consumers	STRING	ATTRIBUTE	Subscribed consumers

• Query Example:

IoTDB> select * from information_schema.subscriptions where topic_name = 'topic_1'
+----------+-------------------+--------------------------------+
|topic_name|consumer_group_name|            subscribed_consumers|
+----------+-------------------+--------------------------------+
|   topic_1|                cg1|[c3, c4, c5, c6, c7, c0, c1, c2]|
+----------+-------------------+--------------------------------+

2.9 TOPICS

• Contains information about all data subscription topics in the cluster
• Table structure is as follows:

Column Name	Data Type	Column Type	Description
topic_name	STRING	TAG	Subscription topic name
topic_configs	STRING	ATTRIBUTE	Topic configuration parameters

• Query Example:

IoTDB> select * from information_schema.topics
+----------+----------------------------------------------------------------+
|topic_name|                                                   topic_configs|
+----------+----------------------------------------------------------------+
|     topic|{__system.sql-dialect=table, start-time=2025-01-10T17:05:38.282}|
+----------+----------------------------------------------------------------+

3. Permission Description

• GRANT/REVOKE operations are not supported for the information_schema database or any of its tables.
• All users can view information_schema database details via the SHOW DATABASES statement.
• All users can list system tables via SHOW TABLES FROM information_schema.
• All users can inspect system table structures using the DESC statement.
• Currently, only the root user can query data from system tables via SELECT—other users will receive empty result sets.