Skip to main content

System Tables

April 3, 2025About 5 min

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 NameData TypeColumn TypeDescription
databaseSTRINGTAGDatabase name
ttl(ms)STRINGATTRIBUTEData retention time
schema_replication_factorINT32ATTRIBUTESchema replica count
data_replication_factorINT32ATTRIBUTEData replica count
time_partition_intervalINT64ATTRIBUTETime partition interval
schema_region_group_numINT32ATTRIBUTENumber of schema region groups
data_region_group_numINT32ATTRIBUTENumber 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 NameData TypeColumn TypeDescription
databaseSTRINGTAGDatabase name
table_nameSTRINGTAGTable name
ttl(ms)STRINGATTRIBUTEData retention time
statusSTRINGATTRIBUTEStatus
commentSTRINGATTRIBUTEDescription/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 NameData TypeColumn TypeDescription
region_idINT32TAGRegion ID
datanode_idINT32TAGDataNode ID
typeSTRINGATTRIBUTEType (SchemaRegion/DataRegion)
statusSTRINGATTRIBUTEStatus (Running,Unknown, etc.)
databaseSTRINGATTRIBUTEDatabase name
series_slot_numINT32ATTRIBUTENumber of series slots
time_slot_numINT64ATTRIBUTENumber of time slots
rpc_addressSTRINGATTRIBUTERPC address
rpc_portINT32ATTRIBUTERPC port
internal_addressSTRINGATTRIBUTEInternal communication address
roleSTRINGATTRIBUTERole (Leader/Follower)
create_timeTIMESTAMPATTRIBUTECreation time
tsfile_size_bytesINT64ATTRIBUTE- 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 NameData TypeColumn TypeDescription
query_idSTRINGTAGQuery ID
start_timeTIMESTAMPATTRIBUTEQuery start timestamp (precision matches system precision)
datanode_idINT32ATTRIBUTEDataNode ID that initiated the query
elapsed_timeFLOATATTRIBUTEQuery execution duration (in seconds)
statementSTRINGATTRIBUTESQL statement of the query
userSTRINGATTRIBUTEUser 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 NameData TypeColumn TypeDescription
databaseSTRINGTAGDatabase name
table_nameSTRINGTAGTable name
column_nameSTRINGTAGColumn name
datatypeSTRINGATTRIBUTEColumn data type
categorySTRINGATTRIBUTEColumn category
statusSTRINGATTRIBUTEColumn status
commentSTRINGATTRIBUTEColumn 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 NameData TypeColumn TypeDescription
idSTRINGTAGPipe name
creation_timeTIMESTAMPATTRIBUTECreation time
stateSTRINGATTRIBUTEPipe status (RUNNING/STOPPED)
pipe_sourceSTRINGATTRIBUTESource plugin parameters
pipe_processorSTRINGATTRIBUTEProcessor plugin parameters
pipe_sinkSTRINGATTRIBUTESink plugin parameters
exception_messageSTRINGATTRIBUTEException message
remaining_event_countINT64ATTRIBUTERemaining event count (-1if Unknown)
estimated_remaining_secondsDOUBLEATTRIBUTEEstimated 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 NameData TypeColumn TypeDescription
plugin_nameSTRINGTAGPlugin name
plugin_typeSTRINGATTRIBUTEPlugin type (Builtin/External)
class_nameSTRINGATTRIBUTEPlugin's main class name
plugin_jarSTRINGATTRIBUTEPlugin'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 NameData TypeColumn TypeDescription
topic_nameSTRINGTAGSubscription topic name
consumer_group_nameSTRINGTAGConsumer group name
subscribed_consumersSTRINGATTRIBUTESubscribed 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 NameData TypeColumn TypeDescription
topic_nameSTRINGTAGSubscription topic name
topic_configsSTRINGATTRIBUTETopic 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.

Copyright © 2025 The Apache Software Foundation. Apache IoTDB, IoTDB, Apache, the Apache feather logo, and the Apache IoTDB project logo are either registered trademarks or trademarks of The Apache Software Foundation in all countries

Having questions? Connect with us on QQ, WeChat, or Slack. Join the community