Skip to main content

Information_Schema Database

TDengine includes a built-in database named INFORMATION_SCHEMA to provide access to database metadata, system information, and status information. This information includes database names, table names, and currently running SQL statements. All information related to TDengine maintenance is stored in this database. It contains several read-only tables. These tables are more accurately described as views, and they do not correspond to specific files. You can query these tables but cannot write data to them. The INFORMATION_SCHEMA database is intended to provide a unified method for SHOW commands to access data. However, using SELECT ... FROM INFORMATION_SCHEMA.tablename offers several advantages over SHOW commands:

  1. You can use a USE statement to specify the INFORMATION_SCHEMA database as the current database.
  2. You can use the familiar SELECT syntax to access information, provided that you know the table and column names.
  3. You can filter and order the query results. More generally, you can use any SELECT syntax that TDengine supports to query the INFORMATION_SCHEMA database.
  4. Future versions of TDengine can add new columns to INFORMATION_SCHEMA tables without affecting existing business systems.
  5. It is easier for users coming from other database management systems. For example, Oracle users can query data dictionary tables.
info
  • SHOW statements are still supported for the convenience of existing users.
  • Some columns in the system table may be keywords, and you need to use the escape character '`' when querying, for example, to query the VGROUPS in the database test:
   select `vgroups` from ins_databases where name = 'test';

This document introduces the tables of INFORMATION_SCHEMA and their structure.

INS_DNODES

Provides information about dnodes. Similar to SHOW DNODES.

#ColumnData TypeDescription
1vnodesSMALLINTCurrent number of vnodes on the dnode. It should be noted that vnodes is a TDengine keyword and needs to be escaped with ` when used as a column name.
2support_vnodesSMALLINTMaximum number of vnodes on the dnode
3statusBINARY(10)Current status
4noteBINARY(256)Reason for going offline or other information
5idSMALLINTDnode ID
6endpointBINARY(134)Dnode endpoint
7createTIMESTAMPCreation time

INS_MNODES

Provides information about mnodes. Similar to SHOW MNODES.

#ColumnData TypeDescription
1idSMALLINTMnode ID
2endpointBINARY(134)Mnode endpoint
3roleBINARY(10)Current role
4role_timeTIMESTAMPTime at which the current role was assumed
5create_timeTIMESTAMPCreation time

INS_QNODES

Provides information about qnodes. Similar to SHOW QNODES.

#ColumnData TypeDescription
1idSMALLINTQnode ID
2endpointBINARY(134)Qnode endpoint
3create_timeTIMESTAMPCreation time

INS_CLUSTER

Provides information about the cluster.

#ColumnData TypeDescription
1idBIGINTCluster ID
2nameBINARY(134)Cluster name
3create_timeTIMESTAMPCreation time

INS_DATABASES

Provides information about user-created databases. Similar to SHOW DATABASES.

#ColumnData TypeDescription
1nameBINARY(32)Database name
2create_timeTIMESTAMPCreation time
3ntablesINTNumber of standard tables and subtables (not including supertables)
4vgroupsINTNumber of vgroups. It should be noted that vnodes is a TDengine keyword and needs to be escaped with ` when used as a column name.
6replicaINTNumber of replicas. It should be noted that replica is a TDengine keyword and needs to be escaped with ` when used as a column name.
7strictBINARY(3)Strong consistency. It should be noted that strict is a TDengine keyword and needs to be escaped with ` when used as a column name.
8durationINTDuration for storage of single files. It should be noted that duration is a TDengine keyword and needs to be escaped with ` when used as a column name.
9keepINTData retention period. It should be noted that keep is a TDengine keyword and needs to be escaped with ` when used as a column name.
10bufferINTWrite cache size per vnode, in MB. It should be noted that buffer is a TDengine keyword and needs to be escaped with ` when used as a column name.
11pagesizeINTPage size for vnode metadata storage engine, in KB. It should be noted that pagesize is a TDengine keyword and needs to be escaped with ` when used as a column name.
12pagesINTNumber of pages per vnode metadata storage engine. It should be noted that pages is a TDengine keyword and needs to be escaped with ` when used as a column name.
13minrowsINTMaximum number of records per file block. It should be noted that minrows is a TDengine keyword and needs to be escaped with ` when used as a column name.
14maxrowsINTMinimum number of records per file block. It should be noted that maxrows is a TDengine keyword and needs to be escaped with ` when used as a column name.
15compINTCompression method. It should be noted that comp is a TDengine keyword and needs to be escaped with ` when used as a column name.
16precisionBINARY(2)Time precision. It should be noted that precision is a TDengine keyword and needs to be escaped with ` when used as a column name.
17statusBINARY(10)Current database status
18retentionsBINARY (60)Aggregation interval and retention period. It should be noted that retentions is a TDengine keyword and needs to be escaped with ` when used as a column name.
19single_stableBOOLWhether the database can contain multiple supertables. It should be noted that single_stable is a TDengine keyword and needs to be escaped with ` when used as a column name.
20cachemodelBINARY(60)Caching method for the newest data. It should be noted that cachemodel is a TDengine keyword and needs to be escaped with ` when used as a column name.
21cachesizeINTMemory per vnode used for caching the newest data. It should be noted that cachesize is a TDengine keyword and needs to be escaped with ` when used as a column name.
22wal_levelINTWAL level. It should be noted that wal_level is a TDengine keyword and needs to be escaped with ` when used as a column name.
23wal_fsync_periodINTInterval at which WAL is written to disk. It should be noted that wal_fsync_period is a TDengine keyword and needs to be escaped with ` when used as a column name.
24wal_retention_periodINTWAL retention period. It should be noted that wal_retention_period is a TDengine keyword and needs to be escaped with ` when used as a column name.
25wal_retention_sizeINTMaximum WAL size. It should be noted that wal_retention_size is a TDengine keyword and needs to be escaped with ` when used as a column name.
26wal_roll_periodINTWAL rotation period. It should be noted that wal_roll_period is a TDengine keyword and needs to be escaped with ` when used as a column name.
27wal_segment_sizeBIGINTWAL file size. It should be noted that wal_segment_size is a TDengine keyword and needs to be escaped with ` when used as a column name.
28stt_triggerSMALLINTThe threshold for number of files to trigger file merging. It should be noted that stt_trigger is a TDengine keyword and needs to be escaped with ` when used as a column name.
29table_prefixSMALLINTThe prefix length in the table name that is ignored when distributing table to vnode based on table name. It should be noted that table_prefix is a TDengine keyword and needs to be escaped with ` when used as a column name.
30table_suffixSMALLINTThe suffix length in the table name that is ignored when distributing table to vnode based on table name. It should be noted that table_suffix is a TDengine keyword and needs to be escaped with ` when used as a column name.
31tsdb_pagesizeINTThe page size for internal storage engine, its unit is KB. It should be noted that tsdb_pagesize is a TDengine keyword and needs to be escaped with ` when used as a column name.

INS_FUNCTIONS

Provides information about user-defined functions.

#ColumnData TypeDescription
1nameBINARY(64)Function name
2commentBINARY(255)Function description. It should be noted that comment is a TDengine keyword and needs to be escaped with ` when used as a column name.
3aggregateINTWhether the UDF is an aggregate function. It should be noted that aggregate is a TDengine keyword and needs to be escaped with ` when used as a column name.
4output_typeBINARY(31)Output data type
5create_timeTIMESTAMPCreation time
6code_lenINTLength of the source code
7bufsizeINTBuffer size

INS_INDEXES

Provides information about user-created indices. Similar to SHOW INDEX.

#ColumnData TypeDescription
1db_nameBINARY(32)Database containing the table with the specified index
2table_nameBINARY(192)Table containing the specified index
3index_nameBINARY(192)Index name
4db_nameBINARY(64)Index column
5index_typeBINARY(10)SMA or FULLTEXT index
6index_extensionsBINARY(256)Other information For SMA indices, this shows a list of functions. For FULLTEXT indices, this is null.

INS_STABLES

Provides information about supertables.

#ColumnData TypeDescription
1stable_nameBINARY(192)Supertable name
2db_nameBINARY(64)All databases in the supertable
3create_timeTIMESTAMPCreation time
4columnsINTNumber of columns
5tagsINTNumber of tags. It should be noted that tags is a TDengine keyword and needs to be escaped with ` when used as a column name.
6last_updateTIMESTAMPLast updated time
7table_commentBINARY(1024)Table description
8watermarkBINARY(64)Window closing time. It should be noted that watermark is a TDengine keyword and needs to be escaped with ` when used as a column name.
9max_delayBINARY(64)Maximum delay for pushing stream processing results. It should be noted that max_delay is a TDengine keyword and needs to be escaped with ` when used as a column name.
10rollupBINARY(128)Rollup aggregate function. It should be noted that rollup is a TDengine keyword and needs to be escaped with ` when used as a column name.

INS_TABLES

Provides information about standard tables and subtables.

#ColumnData TypeDescription
1table_nameBINARY(192)Table name
2db_nameBINARY(64)Database name
3create_timeTIMESTAMPCreation time
4columnsINTNumber of columns
5stable_nameBINARY(192)Supertable name
6uidBIGINTTable ID
7vgroup_idINTVgroup ID
8ttlINTTable time-to-live. It should be noted that ttl is a TDengine keyword and needs to be escaped with ` when used as a column name.
9table_commentBINARY(1024)Table description
10typeBINARY(20)Table type

INS_TAGS

#ColumnData TypeDescription
1table_nameBINARY(192)Table name
2db_nameBINARY(64)Database name
3stable_nameBINARY(192)Supertable name
4tag_nameBINARY(64)Tag name
5tag_typeBINARY(64)Tag type
6tag_valueBINARY(16384)Tag value

INS_USERS

Provides information about TDengine users.

#ColumnData TypeDescription
1user_nameBINARY(23)User name
2privilegeBINARY(256)User permissions
3create_timeTIMESTAMPCreation time

INS_GRANTS

Provides information about TDengine Enterprise Edition permissions.

#ColumnData TypeDescription
1versionBINARY(9)Whether the deployment is a licensed or trial version
2cpu_coresBINARY(9)CPU cores included in license
3dnodesBINARY(10)Dnodes included in license. It should be noted that dnodes is a TDengine keyword and needs to be escaped with ` when used as a column name.
4streamsBINARY(10)Streams included in license. It should be noted that streams is a TDengine keyword and needs to be escaped with ` when used as a column name.
5usersBINARY(10)Users included in license. It should be noted that users is a TDengine keyword and needs to be escaped with ` when used as a column name.
6accountsBINARY(10)Accounts included in license. It should be noted that accounts is a TDengine keyword and needs to be escaped with ` when used as a column name.
7storageBINARY(21)Storage space included in license. It should be noted that storage is a TDengine keyword and needs to be escaped with ` when used as a column name.
8connectionsBINARY(21)Client connections included in license. It should be noted that connections is a TDengine keyword and needs to be escaped with ` when used as a column name.
9databasesBINARY(11)Databases included in license. It should be noted that databases is a TDengine keyword and needs to be escaped with ` when used as a column name.
10speedBINARY(9)Write speed specified in license (data points per second)
11querytimeBINARY(9)Total query time specified in license
12timeseriesBINARY(21)Number of metrics included in license
13expiredBINARY(5)Whether the license has expired
14expire_timeBINARY(19)When the trial period expires

INS_VGROUPS

Provides information about vgroups.

#ColumnData TypeDescription
1vgroup_idINTVgroup ID
2db_nameBINARY(32)Database name
3tablesINTTables in vgroup. It should be noted that tables is a TDengine keyword and needs to be escaped with ` when used as a column name.
4statusBINARY(10)Vgroup status
5v1_dnodeINTDnode ID of first vgroup member
6v1_statusBINARY(10)Status of first vgroup member
7v2_dnodeINTDnode ID of second vgroup member
8v2_statusBINARY(10)Status of second vgroup member
9v3_dnodeINTDnode ID of third vgroup member
10v3_statusBINARY(10)Status of third vgroup member
11nfilesINTNumber of data and metadata files in the vgroup
12file_sizeINTSize of the data and metadata files in the vgroup
13tsmaTINYINTWhether time-range-wise SMA is enabled. 1 means enabled; 0 means disabled.

INS_CONFIGS

Provides system configuration information.

#ColumnData TypeDescription
1nameBINARY(32)Parameter
2valueBINARY(64)Value. It should be noted that value is a TDengine keyword and needs to be escaped with ` when used as a column name.

INS_DNODE_VARIABLES

Provides dnode configuration information.

#ColumnData TypeDescription
1dnode_idINTDnode ID
2nameBINARY(32)Parameter
3valueBINARY(64)Value. It should be noted that value is a TDengine keyword and needs to be escaped with ` when used as a column name.

INS_TOPICS

#ColumnData TypeDescription
1topic_nameBINARY(192)Topic name
2db_nameBINARY(64)Database for the topic
3create_timeTIMESTAMPCreation time
4sqlBINARY(1024)SQL statement used to create the topic

INS_SUBSCRIPTIONS

#ColumnData TypeDescription
1topic_nameBINARY(204)Subscribed topic
2consumer_groupBINARY(193)Subscribed consumer group
3vgroup_idINTVgroup ID for the consumer
4consumer_idBIGINTConsumer ID

INS_STREAMS

#ColumnData TypeDescription
1stream_nameBINARY(64)Stream name
2create_timeTIMESTAMPCreation time
3sqlBINARY(1024)SQL statement used to create the stream
4statusBIANRY(20)Current status
5source_dbBINARY(64)Source database
6target_dbBIANRY(64)Target database
7target_tableBINARY(192)Target table
8watermarkBIGINTWatermark (see stream processing documentation). It should be noted that watermark is a TDengine keyword and needs to be escaped with ` when used as a column name.
9triggerINTMethod of triggering the result push (see stream processing documentation). It should be noted that trigger is a TDengine keyword and needs to be escaped with ` when used as a column name.