Skip to main content

Metadata

TDengine has a built-in database called INFORMATION_SCHEMA, which provides access to database metadata, system information, and status, such as the names of databases or tables, the SQL statements currently being executed, etc. This database stores information about all other databases maintained by TDengine. It contains multiple read-only tables. In fact, these tables are views, not base tables, so there are no files associated with them. Therefore, you can only query these tables, and cannot perform write operations like INSERT. The INFORMATION_SCHEMA database is designed to provide access to the information provided by various SHOW statements supported by TDengine (such as SHOW TABLES, SHOW DATABASES) in a more consistent way. Compared to SHOW statements, using SELECT ... FROM INFORMATION_SCHEMA.tablename has the following advantages:

  1. You can use the USE statement to set INFORMATION_SCHEMA as the default database
  2. You can use the familiar syntax of the SELECT statement, only needing to learn some table names and column names
  3. You can filter, sort, and perform other operations on the query results. In fact, you can use any SELECT statement supported by TDengine to query the tables in INFORMATION_SCHEMA
  4. TDengine can flexibly add columns to the existing tables in INFORMATION_SCHEMA in future evolutions without worrying about impacting existing business systems
  5. More interoperable with other database systems. For example, Oracle database users are familiar with querying tables in the Oracle data dictionary
info
  • Since SHOW statements are already familiar and widely used by developers, they are still retained.
  • Some columns in the system tables may be keywords, and need to use the escape character '`' when querying, for example, to query how many VGROUPs the database test has:
   select `vgroups` from ins_databases where name = 'test';

This chapter will detail the tables and table structures in the built-in metadata database INFORMATION_SCHEMA.

INS_DNODES

Provides information about dnodes. You can also use SHOW DNODES to query this information. Users with SYSINFO set to 0 cannot view this table.

#Column NameData TypeDescription
1vnodesSMALLINTThe actual number of vnodes in the dnode. Note, vnodes is a TDengine keyword, and needs to be escaped with ` when used as a column name.
2support_vnodesSMALLINTMaximum number of supported vnodes
3statusBINARY(10)Current status
4noteBINARY(256)Information such as the reason for being offline
5idSMALLINTdnode id
6endpointBINARY(134)Address of the dnode
7createTIMESTAMPCreation time

INS_MNODES

Provides information about mnodes. You can also use SHOW MNODES to query this information. Users with SYSINFO set to 0 cannot view this table.

#Column NameData TypeDescription
1idSMALLINTmnode id
2endpointBINARY(134)address of the mnode
3roleBINARY(10)current role
4role_timeTIMESTAMPtime when current role was assumed
5create_timeTIMESTAMPcreation time

INS_QNODES

Information about QNODEs in the current system. You can also use SHOW QNODES to query this information. Users with SYSINFO set to 0 cannot view this table.

#Column NameData TypeDescription
1idSMALLINTqnode id
2endpointVARCHAR(134)address of the qnode
3create_timeTIMESTAMPcreation time

INS_SNODES

Information about SNODEs in the current system. You can also use SHOW SNODES to query this information. Users with SYSINFO set to 0 cannot view this table.

#Column NameData TypeDescription
1idSMALLINTsnode id
2endpointVARCHAR(134)address of the snode
3create_timeTIMESTAMPcreation time

INS_CLUSTER

Stores cluster-related information. Users with SYSINFO set to 0 cannot view this table.

#Column NameData TypeDescription
1idBIGINTcluster id
2nameVARCHAR(134)cluster name
3create_timeTIMESTAMPcreation time

INS_DATABASES

Provides information about database objects created by users. You can also use SHOW DATABASES to query this information.

#Column NameData TypeDescription
1nameVARCHAR(64)database name
2create_timeTIMESTAMPcreation time
3ntablesINTnumber of tables in the database, including subtables and basic tables but excluding supertables
4vgroupsINTnumber of vgroups in the database. Note, vgroups is a TDengine keyword and must be escaped with ` when used as a column name.
6replicaINTnumber of replicas. Note, replica is a TDengine keyword and must be escaped with ` when used as a column name.
7strictVARCHAR(4)deprecated parameter
8durationVARCHAR(10)time span for storing data in a single file. Note, duration is a TDengine keyword and must be escaped with ` when used as a column name. Internally stored in minutes, may be displayed in days or hours when queried
9keepVARCHAR(32)data retention duration. Note, keep is a TDengine keyword and must be escaped with ` when used as a column name. Internally stored in minutes, may be displayed in days or hours when queried
10bufferINTsize of the write cache memory block for each vnode, in MB. Note, buffer is a TDengine keyword and must be escaped with ` when used as a column name.
11pagesizeINTpage size for the metadata storage engine in each VNODE, in KB. Note, pagesize is a TDengine keyword and must be escaped with ` when used as a column name.
12pagesINTnumber of cache pages for the metadata storage engine in each vnode. Note, pages is a TDengine keyword and must be escaped with ` when used as a column name.
13minrowsINTminimum number of records in a file block. Note, minrows is a TDengine keyword and must be escaped with ` when used as a column name.
14maxrowsINTmaximum number of records in a file block. Note, maxrows is a TDengine keyword and must be escaped with ` when used as a column name.
15compINTdata compression method. Note, comp is a TDengine keyword and must be escaped with ` when used as a column name.
16precisionVARCHAR(2)time resolution. Note, precision is a TDengine keyword and must be escaped with ` when used as a column name.
17statusVARCHAR(10)database status
18retentionsVARCHAR(60)data aggregation period and retention duration. Note, retentions is a TDengine keyword and must be escaped with ` when used as a column name.
19single_stableBOOLindicates whether only one supertable can be created in this database. Note, single_stable is a TDengine keyword and must be escaped with ` when used as a column name.
20cachemodelVARCHAR(60)indicates whether to cache recent data of subtables in memory. Note, cachemodel is a TDengine keyword and must be escaped with ` when used as a column name.
21cachesizeINTsize of memory used to cache recent data of subtables in each vnode. Note, cachesize is a TDengine keyword and must be escaped with ` when used as a column name.
22wal_levelINTWAL level. Note, wal_level is a TDengine keyword and must be escaped with ` when used as a column name.
23wal_fsync_periodINTdata write-to-disk period. Note, wal_fsync_period is a TDengine keyword and must be escaped with ` when used as a column name.
24wal_retention_periodINTWAL retention duration, in seconds. Note, wal_retention_period is a TDengine keyword and must be escaped with ` when used as a column name.
25wal_retention_sizeINTWAL retention limit. Note, wal_retention_size is a TDengine keyword and must be escaped with ` when used as a column name.
26stt_triggerSMALLINTnumber of disk files that trigger file merging. Note, stt_trigger is a TDengine keyword and must be escaped with ` when used as a column name.
27table_prefixSMALLINTlength of the prefix to ignore when the internal storage engine allocates a VNODE to store data for a table based on its name. Note, table_prefix is a TDengine keyword and must be escaped with ` when used as a column name.
28table_suffixSMALLINTlength of the suffix to ignore when the internal storage engine allocates a VNODE to store data for a table based on its name. Note, table_suffix is a TDengine keyword and must be escaped with ` when used as a column name.
29tsdb_pagesizeINTpage size in the time-series data storage engine. Note, tsdb_pagesize is a TDengine keyword and must be escaped with ` when used as a column name.

INS_FUNCTIONS

Information about user-created custom functions.

#Column NameData TypeDescription
1nameVARCHAR(64)Function name
2commentVARCHAR(255)Additional explanation. Note that comment is a TDengine keyword and needs to be escaped with ``.
3aggregateINTWhether it is an aggregate function. Note that aggregate is a TDengine keyword and needs to be escaped with ``.
4output_typeVARCHAR(31)Output type
5create_timeTIMESTAMPCreation time
6code_lenINTCode length
7bufsizeINTBuffer size
8func_languageVARCHAR(31)Programming language of the custom function
9func_bodyVARCHAR(16384)Function body definition
10func_versionINTFunction version number. Initial version is 0, and the version number increases by 1 with each update.

INS_INDEXES

Provides information about indexes created by the user. You can also use SHOW INDEX to query this information.

#Column NameData TypeDescription
1db_nameVARCHAR(32)Database name containing the table with this index
2table_nameVARCHAR(192)Name of the table containing this index
3index_nameVARCHAR(192)Index name
4column_nameVARCHAR(64)Column name on which the index is built
5index_typeVARCHAR(10)Currently includes SMA and tag
6index_extensionsVARCHAR(256)Additional information about the index. For SMA/tag types of indexes, it is a list of function names.

INS_STABLES

Provides information about supertables created by users.

#Column NameData TypeDescription
1stable_nameVARCHAR(192)Supertable name
2db_nameVARCHAR(64)Name of the database containing the supertable
3create_timeTIMESTAMPCreation time
4columnsINTNumber of columns
5tagsINTNumber of tags. Note, tags is a TDengine keyword, use ` to escape when using it as a column name.
6last_updateTIMESTAMPLast update time
7table_commentVARCHAR(1024)Table comment
8watermarkVARCHAR(64)Window closing time. Note, watermark is a TDengine keyword, use ` to escape when using it as a column name.
9max_delayVARCHAR(64)Maximum delay for pushing calculation results. Note, max_delay is a TDengine keyword, use ` to escape when using it as a column name.
10rollupVARCHAR(128)rollup aggregation function. Note, rollup is a TDengine keyword, use ` to escape when using it as a column name.

INS_TABLES

Provides information about basic tables and subtables created by users

#Column NameData TypeDescription
1table_nameVARCHAR(192)Table name
2db_nameVARCHAR(64)Database name
3create_timeTIMESTAMPCreation time
4columnsINTNumber of columns
5stable_nameVARCHAR(192)Name of the supertable it belongs to
6uidBIGINTTable ID
7vgroup_idINTvgroup ID
8ttlINTTable's time to live. Note, ttl is a TDengine keyword, use ` to escape when using it as a column name.
9table_commentVARCHAR(1024)Table comment
10typeVARCHAR(21)Table type

INS_TAGS

#Column NameData TypeDescription
1table_nameVARCHAR(192)Table name
2db_nameVARCHAR(64)Name of the database the table belongs to
3stable_nameVARCHAR(192)Name of the supertable
4tag_nameVARCHAR(64)Name of the tag
5tag_typeVARCHAR(64)Type of the tag
6tag_valueVARCHAR(16384)Value of the tag

INS_COLUMNS

#Column NameData TypeDescription
1table_nameVARCHAR(192)Table name
2db_nameVARCHAR(64)Name of the database the table belongs to
3table_typeVARCHAR(21)Table type
4col_nameVARCHAR(64)Name of the column
5col_typeVARCHAR(32)Type of the column
6col_lengthINTLength of the column
7col_precisionINTPrecision of the column
8col_scaleINTScale of the column
9col_nullableINTWhether the column can be null

INS_USERS

Provides information about users created in the system. Users with SYSINFO attribute as 0 cannot view this table.

#Column NameData TypeDescription
1nameVARCHAR(24)Username
2superTINYINTWhether the user is a superuser, 1: Yes, 0: No
3enableTINYINTWhether the user is enabled, 1: Yes, 0: No
4sysinfoTINYINTWhether the user can view system information, 1: Yes, 0: No
5create_timeTIMESTAMPCreation time
6allowed_hostVARCHAR(49152)IP whitelist

INS_GRANTS

Provides information about enterprise edition licenses. Users with SYSINFO attribute as 0 cannot view this table.

#Column NameData TypeDescription
1versionVARCHAR(9)Enterprise license description: official (officially licensed)/trial (trial)
2cpu_coresVARCHAR(9)Number of CPU cores licensed for use
3dnodesVARCHAR(10)Number of dnode nodes licensed for use. Note, dnodes is a TDengine keyword, use ` to escape when used as a column name.
4streamsVARCHAR(10)Number of streams licensed for creation. Note, streams is a TDengine keyword, use ` to escape when used as a column name.
5usersVARCHAR(10)Number of users licensed for creation. Note, users is a TDengine keyword, use ` to escape when used as a column name.
6accountsVARCHAR(10)Number of accounts licensed for creation. Note, accounts is a TDengine keyword, use ` to escape when used as a column name.
7storageVARCHAR(21)Amount of storage space licensed for use. Note, storage is a TDengine keyword, use ` to escape when used as a column name.
8connectionsVARCHAR(21)Number of client connections licensed for use. Note, connections is a TDengine keyword, use ` to escape when used as a column name.
9databasesVARCHAR(11)Number of databases licensed for use. Note, databases is a TDengine keyword, use ` to escape when used as a column name.
10speedVARCHAR(9)Number of data points per second licensed for writing
11querytimeVARCHAR(9)Total duration of queries licensed for use
12timeseriesVARCHAR(21)Number of time-series licensed for use
13expiredVARCHAR(5)Whether expired, true: expired, false: not expired
14expire_timeVARCHAR(19)Trial period expiration time

INS_VGROUPS

Information on all vgroups in the system. Users with SYSINFO property set to 0 cannot view this table.

#Column NameData TypeDescription
1vgroup_idINTvgroup id
2db_nameVARCHAR(32)Database name
3tablesINTNumber of tables in this vgroup. Note that tables is a TDengine keyword and must be escaped with ` when used as a column name.
4statusVARCHAR(10)Status of this vgroup
5v1_dnodeINTID of the dnode of the first member
6v1_statusVARCHAR(10)Status of the first member
7v2_dnodeINTID of the dnode of the second member
8v2_statusVARCHAR(10)Status of the second member
9v3_dnodeINTID of the dnode of the third member
10v3_statusVARCHAR(10)Status of the third member
11nfilesINTNumber of data/metadata files in this vgroup
12file_sizeINTSize of data/metadata files in this vgroup
13tsmaTINYINTWhether this vgroup is dedicated to Time-range-wise SMA, 1: Yes, 0: No

INS_CONFIGS

System configuration parameters.

#Column NameData TypeDescription
1nameVARCHAR(32)Configuration item name
2valueVARCHAR(64)Value of the configuration item. Note that value is a TDengine keyword and must be escaped with ` when used as a column name.

INS_DNODE_VARIABLES

Configuration parameters for each dnode in the system. Users with SYSINFO attribute 0 cannot view this table.

#Column NameData TypeDescription
1dnode_idINTID of the dnode
2nameVARCHAR(32)Configuration item name
3valueVARCHAR(64)The value of the configuration item. Note, value is a TDengine keyword, use ` to escape when used as a column name.

INS_TOPICS

#Column NameData TypeDescription
1topic_nameVARCHAR(192)Name of the topic
2db_nameVARCHAR(64)DB associated with the topic
3create_timeTIMESTAMPCreation time of the topic
4sqlVARCHAR(1024)SQL statement used to create the topic

INS_SUBSCRIPTIONS

#Column NameData TypeDescription
1topic_nameVARCHAR(204)Subscribed topic
2consumer_groupVARCHAR(193)Consumer group of the subscriber
3vgroup_idINTvgroup id assigned to the consumer
4consumer_idBIGINTUnique id of the consumer
5userVARCHAR(24)Username of the consumer logging in
6fqdnVARCHAR(128)fqdn of the consumer's machine
7offsetVARCHAR(64)Consumption progress of the consumer
8rowsBIGINTNumber of data rows consumed by the consumer

INS_STREAMS

#Column NameData TypeDescription
1stream_nameVARCHAR(64)Stream computing name
2create_timeTIMESTAMPCreation time
3sqlVARCHAR(1024)SQL statement provided for creating stream computing
4statusVARCHAR(20)Current status of the stream
5source_dbVARCHAR(64)Source database
6target_dbVARCHAR(64)Destination database
7target_tableVARCHAR(192)Target table for stream computing output
8watermarkBIGINTWatermark, see SQL manual for stream computing. Note, watermark is a TDengine keyword, use ` to escape when used as a column name.
9triggerINTMode of pushing computation results, see SQL manual for stream computing. Note, trigger is a TDengine keyword, use ` to escape when used as a column name.

INS_USER_PRIVILEGES

Note: Users with SYSINFO property set to 0 cannot view this table.

#Column NameData TypeDescription
1user_nameVARCHAR(24)Username
2privilegeVARCHAR(10)Privilege description
3db_nameVARCHAR(65)Database name
4table_nameVARCHAR(193)Table name
5conditionVARCHAR(49152)Subtable privilege filter condition