22-meta.md 34.0 KB
Newer Older
1
---
2
title: Information_Schema Database
D
danielclow 已提交
3 4
sidebar_label: Metadata
description: This document describes how to use the INFORMATION_SCHEMA database in TDengine.
5 6
---

7
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:
8

9 10 11 12 13
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.
14

15 16 17
:::info

- SHOW statements are still supported for the convenience of existing users.
18
- 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`:
19 20 21 22 23
```sql 
   select `vgroups` from ins_databases where name = 'test';
``` 

:::
24

25
This document introduces the tables of INFORMATION_SCHEMA and their structure.
26 27 28

## INS_DNODES

29
Provides information about dnodes. Similar to SHOW DNODES.
30

dengyihao's avatar
dengyihao 已提交
31 32 33 34 35 36 37 38 39
| #   |   **Column**   | **Data Type** | **Description**                                                                                                                                          |
| --- | :------------: | ------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1   |     vnodes     | SMALLINT      | Current 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. |
| 2   | support_vnodes | SMALLINT      | Maximum number of vnodes on the dnode                                                                                                                    |
| 3   |     status     | BINARY(10)    | Current status                                                                                                                                           |
| 4   |      note      | BINARY(256)   | Reason for going offline or other information                                                                                                            |
| 5   |       id       | SMALLINT      | Dnode ID                                                                                                                                                 |
| 6   |    endpoint    | BINARY(134)   | Dnode endpoint                                                                                                                                           |
| 7   |     create     | TIMESTAMP     | Creation time                                                                                                                                            |
40 41 42

## INS_MNODES

43
Provides information about mnodes. Similar to SHOW MNODES.
44

dengyihao's avatar
dengyihao 已提交
45 46 47 48 49 50 51
| #   | **Column**  | **Data Type** | **Description**                            |
| --- | :---------: | ------------- | ------------------------------------------ |
| 1   |     id      | SMALLINT      | Mnode ID                                   |
| 2   |  endpoint   | BINARY(134)   | Mnode endpoint                             |
| 3   |    role     | BINARY(10)    | Current role                               |
| 4   |  role_time  | TIMESTAMP     | Time at which the current role was assumed |
| 5   | create_time | TIMESTAMP     | Creation time                              |
52 53 54

## INS_QNODES

55
Provides information about qnodes. Similar to SHOW QNODES.
56

dengyihao's avatar
dengyihao 已提交
57 58 59 60 61
| #   | **Column**  | **Data Type** | **Description** |
| --- | :---------: | ------------- | --------------- |
| 1   |     id      | SMALLINT      | Qnode ID        |
| 2   |  endpoint   | BINARY(134)   | Qnode endpoint  |
| 3   | create_time | TIMESTAMP     | Creation time   |
62 63 64

## INS_CLUSTER

65
Provides information about the cluster.
66

dengyihao's avatar
dengyihao 已提交
67 68 69 70 71
| #   | **Column**  | **Data Type** | **Description** |
| --- | :---------: | ------------- | --------------- |
| 1   |     id      | BIGINT        | Cluster ID      |
| 2   |    name     | BINARY(134)   | Cluster name    |
| 3   | create_time | TIMESTAMP     | Creation time   |
72 73 74

## INS_DATABASES

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

dengyihao's avatar
dengyihao 已提交
77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
| #   |      **Column**      | **Data Type** | **Description**                                                                                                                                                                                                                   |
| --- | :------------------: | ------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1   |         name         | BINARY(32)    | Database name                                                                                                                                                                                                                     |
| 2   |     create_time      | TIMESTAMP     | Creation time                                                                                                                                                                                                                     |
| 3   |       ntables        | INT           | Number of standard tables and subtables (not including supertables)                                                                                                                                                               |
| 4   |       vgroups        | INT           | Number of vgroups. It should be noted that `vnodes` is a TDengine keyword and needs to be escaped with ` when used as a column name.                                                                                              |
| 6   |       replica        | INT           | Number of replicas. It should be noted that `replica` is a TDengine keyword and needs to be escaped with ` when used as a column name.                                                                                            |
| 7   |        strict        | BINARY(4)     | Obsoleted                                                                                                                                                                                                                         |
| 8   |       duration       | INT           | Duration 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.                                                                         |
| 9   |         keep         | INT           | Data retention period. It should be noted that `keep` is a TDengine keyword and needs to be escaped with ` when used as a column name.                                                                                            |
| 10  |        buffer        | INT           | Write 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.                                                                              |
| 11  |       pagesize       | INT           | Page 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.                                                           |
| 12  |        pages         | INT           | Number 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.                                                               |
| 13  |       minrows        | INT           | Maximum 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.                                                                      |
| 14  |       maxrows        | INT           | Minimum 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.                                                                      |
| 15  |         comp         | INT           | Compression method. It should be noted that `comp` is a TDengine keyword and needs to be escaped with ` when used as a column name.                                                                                               |
| 16  |      precision       | BINARY(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.                                                                                              |
| 17  |        status        | BINARY(10)    | Current database status                                                                                                                                                                                                           |
| 18  |      retentions      | BINARY (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.                                                                  |
| 19  |    single_stable     | BOOL          | Whether 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.                                                   |
| 20  |      cachemodel      | BINARY(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.                                                                         |
| 21  |      cachesize       | INT           | Memory 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.                                                           |
| 22  |      wal_level       | INT           | WAL level. It should be noted that `wal_level` is a TDengine keyword and needs to be escaped with ` when used as a column name.                                                                                                   |
| 23  |   wal_fsync_period   | INT           | Interval 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.                                                             |
| 24  | wal_retention_period | INT           | WAL 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.                                                                             |
| 25  |  wal_retention_size  | INT           | Maximum 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.                                                                                   |
| 26  |   wal_roll_period    | INT           | WAL 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.                                                                                   |
| 27  |   wal_segment_size   | BIGINT        | WAL 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.                                                                                        |
| 28  |     stt_trigger      | SMALLINT      | The 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.                                                 |
| 29  |     table_prefix     | SMALLINT      | The 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. |
| 30  |     table_suffix     | SMALLINT      | The 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. |
| 31  |    tsdb_pagesize     | INT           | The 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.                                               |
109 110 111

## INS_FUNCTIONS

112
Provides information about user-defined functions.
113

dengyihao's avatar
dengyihao 已提交
114 115 116 117 118 119 120 121 122 123 124 125
| #   |  **Column**   | **Data Type** | **Description**                                                                                                                                                |
| --- | :-----------: | ------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1   |     name      | BINARY(64)    | Function name                                                                                                                                                  |
| 2   |    comment    | BINARY(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.                       |
| 3   |   aggregate   | INT           | Whether 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. |
| 4   |  output_type  | BINARY(31)    | Output data type                                                                                                                                               |
| 5   |  create_time  | TIMESTAMP     | Creation time                                                                                                                                                  |
| 6   |   code_len    | INT           | Length of the source code                                                                                                                                      |
| 7   |    bufsize    | INT           | Buffer size                                                                                                                                                    |
| 8   | func_language | BINARY(31)    | UDF programming language                                                                                                                                       |
| 9   |   func_body   | BINARY(16384) | UDF function body                                                                                                                                              |
| 10  | func_version  | INT           | UDF function version. starting from 0. Increasing by 1 each time it is updated                                                                                 |
126 127 128

## INS_INDEXES

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

dengyihao's avatar
dengyihao 已提交
131 132 133 134 135 136 137 138
| #   |    **Column**    | **Data Type** | **Description**                                                       |
| --- | :--------------: | ------------- | --------------------------------------------------------------------- |
| 1   |     db_name      | BINARY(32)    | Database containing the table with the specified index                |
| 2   |    table_name    | BINARY(192)   | Table containing the specified index                                  |
| 3   |    index_name    | BINARY(192)   | Index name                                                            |
| 4   |     db_name      | BINARY(64)    | Index column                                                          |
| 5   |    index_type    | BINARY(10)    | SMA or tag index                                                      |
| 6   | index_extensions | BINARY(256)   | Other information For SMA/tag indices, this shows a list of functions |
139 140 141

## INS_STABLES

142
Provides information about supertables.
143

dengyihao's avatar
dengyihao 已提交
144 145 146 147 148 149 150 151 152 153 154 155
| #   |  **Column**   | **Data Type** | **Description**                                                                                                                                                           |
| --- | :-----------: | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1   |  stable_name  | BINARY(192)   | Supertable name                                                                                                                                                           |
| 2   |    db_name    | BINARY(64)    | All databases in the supertable                                                                                                                                           |
| 3   |  create_time  | TIMESTAMP     | Creation time                                                                                                                                                             |
| 4   |    columns    | INT           | Number of columns                                                                                                                                                         |
| 5   |     tags      | INT           | Number of tags. It should be noted that `tags` is a TDengine keyword and needs to be escaped with ` when used as a column name.                                           |
| 6   |  last_update  | TIMESTAMP     | Last updated time                                                                                                                                                         |
| 7   | table_comment | BINARY(1024)  | Table description                                                                                                                                                         |
| 8   |   watermark   | BINARY(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.                                 |
| 9   |   max_delay   | BINARY(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. |
| 10  |    rollup     | BINARY(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.                              |
156 157 158

## INS_TABLES

159
Provides information about standard tables and subtables.
160

dengyihao's avatar
dengyihao 已提交
161 162 163 164 165 166 167 168 169 170 171 172
| #   |  **Column**   | **Data Type** | **Description**                                                                                                                    |
| --- | :-----------: | ------------- | ---------------------------------------------------------------------------------------------------------------------------------- |
| 1   |  table_name   | BINARY(192)   | Table name                                                                                                                         |
| 2   |    db_name    | BINARY(64)    | Database name                                                                                                                      |
| 3   |  create_time  | TIMESTAMP     | Creation time                                                                                                                      |
| 4   |    columns    | INT           | Number of columns                                                                                                                  |
| 5   |  stable_name  | BINARY(192)   | Supertable name                                                                                                                    |
| 6   |      uid      | BIGINT        | Table ID                                                                                                                           |
| 7   |   vgroup_id   | INT           | Vgroup ID                                                                                                                          |
| 8   |      ttl      | INT           | Table 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. |
| 9   | table_comment | BINARY(1024)  | Table description                                                                                                                  |
| 10  |     type      | BINARY(20)    | Table type                                                                                                                         |
173 174 175

## INS_TAGS

dengyihao's avatar
dengyihao 已提交
176 177 178 179 180 181 182 183
| #   | **Column**  | **Data Type** | **Description** |
| --- | :---------: | ------------- | --------------- |
| 1   | table_name  | BINARY(192)   | Table name      |
| 2   |   db_name   | BINARY(64)    | Database name   |
| 3   | stable_name | BINARY(192)   | Supertable name |
| 4   |  tag_name   | BINARY(64)    | Tag name        |
| 5   |  tag_type   | BINARY(64)    | Tag type        |
| 6   |  tag_value  | BINARY(16384) | Tag value       |
184

wmmhello's avatar
wmmhello 已提交
185 186
## INS_COLUMNS

dengyihao's avatar
dengyihao 已提交
187 188 189 190 191 192 193 194 195 196 197
| #   |  **Column**   | **Data Type** | **Description**  |
| --- | :-----------: | ------------- | ---------------- |
| 1   |  table_name   | BINARY(192)   | Table name       |
| 2   |    db_name    | BINARY(64)    | Database name    |
| 3   |  table_type   | BINARY(21)    | Table type       |
| 4   |   col_name    | BINARY(64)    | Column name      |
| 5   |   col_type    | BINARY(32)    | Column type      |
| 6   |  col_length   | INT           | Column length    |
| 7   | col_precision | INT           | Column precision |
| 8   |   col_scale   | INT           | Column scale     |
| 9   | col_nullable  | INT           | Column nullable  |
wmmhello's avatar
wmmhello 已提交
198

199 200
## INS_USERS

201
Provides information about TDengine users.
202

dengyihao's avatar
dengyihao 已提交
203 204 205 206 207
| #   | **Column**  | **Data Type** | **Description**  |
| --- | :---------: | ------------- | ---------------- |
| 1   |  user_name  | BINARY(23)    | User name        |
| 2   |  privilege  | BINARY(256)   | User permissions |
| 3   | create_time | TIMESTAMP     | Creation time    |
208 209 210

## INS_GRANTS

211
Provides information about TDengine Enterprise Edition permissions.
212

dengyihao's avatar
dengyihao 已提交
213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228
| #   | **Column**  | **Data Type** | **Description**                                                                                                                                                |
| --- | :---------: | ------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1   |   version   | BINARY(9)     | Whether the deployment is a licensed or trial version                                                                                                          |
| 2   |  cpu_cores  | BINARY(9)     | CPU cores included in license                                                                                                                                  |
| 3   |   dnodes    | BINARY(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.                  |
| 4   |   streams   | BINARY(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.                |
| 5   |    users    | BINARY(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.                    |
| 6   |  accounts   | BINARY(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.              |
| 7   |   storage   | BINARY(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.          |
| 8   | connections | BINARY(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. |
| 9   |  databases  | BINARY(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.            |
| 10  |    speed    | BINARY(9)     | Write speed specified in license (data points per second)                                                                                                      |
| 11  |  querytime  | BINARY(9)     | Total query time specified in license                                                                                                                          |
| 12  | timeseries  | BINARY(21)    | Number of metrics included in license                                                                                                                          |
| 13  |   expired   | BINARY(5)     | Whether the license has expired                                                                                                                                |
| 14  | expire_time | BINARY(19)    | When the trial period expires                                                                                                                                  |
229 230 231

## INS_VGROUPS

232
Provides information about vgroups.
233

dengyihao's avatar
dengyihao 已提交
234 235 236 237 238 239 240 241 242 243 244 245 246 247 248
| #   | **Column** | **Data Type** | **Description**                                                                                                                     |
| --- | :--------: | ------------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| 1   | vgroup_id  | INT           | Vgroup ID                                                                                                                           |
| 2   |  db_name   | BINARY(32)    | Database name                                                                                                                       |
| 3   |   tables   | INT           | Tables in vgroup. It should be noted that `tables` is a TDengine keyword and needs to be escaped with ` when used as a column name. |
| 4   |   status   | BINARY(10)    | Vgroup status                                                                                                                       |
| 5   |  v1_dnode  | INT           | Dnode ID of first vgroup member                                                                                                     |
| 6   | v1_status  | BINARY(10)    | Status of first vgroup member                                                                                                       |
| 7   |  v2_dnode  | INT           | Dnode ID of second vgroup member                                                                                                    |
| 8   | v2_status  | BINARY(10)    | Status of second vgroup member                                                                                                      |
| 9   |  v3_dnode  | INT           | Dnode ID of third vgroup member                                                                                                     |
| 10  | v3_status  | BINARY(10)    | Status of third vgroup member                                                                                                       |
| 11  |   nfiles   | INT           | Number of data and metadata files in the vgroup                                                                                     |
| 12  | file_size  | INT           | Size of the data and metadata files in the vgroup                                                                                   |
| 13  |    tsma    | TINYINT       | Whether time-range-wise SMA is enabled. 1 means enabled; 0 means disabled.                                                          |
249 250 251

## INS_CONFIGS

252
Provides system configuration information.
253

dengyihao's avatar
dengyihao 已提交
254 255 256 257
| #   | **Column** | **Data Type** | **Description**                                                                                                         |
| --- | :--------: | ------------- | ----------------------------------------------------------------------------------------------------------------------- |
| 1   |    name    | BINARY(32)    | Parameter                                                                                                               |
| 2   |   value    | BINARY(64)    | Value. It should be noted that `value` is a TDengine keyword and needs to be escaped with ` when used as a column name. |
258 259 260

## INS_DNODE_VARIABLES

261
Provides dnode configuration information.
262

dengyihao's avatar
dengyihao 已提交
263 264 265 266 267
| #   | **Column** | **Data Type** | **Description**                                                                                                         |
| --- | :--------: | ------------- | ----------------------------------------------------------------------------------------------------------------------- |
| 1   |  dnode_id  | INT           | Dnode ID                                                                                                                |
| 2   |    name    | BINARY(32)    | Parameter                                                                                                               |
| 3   |   value    | BINARY(64)    | Value. It should be noted that `value` is a TDengine keyword and needs to be escaped with ` when used as a column name. |
D
dapan1121 已提交
268 269 270

## INS_TOPICS

dengyihao's avatar
dengyihao 已提交
271 272 273 274 275 276
| #   | **Column**  | **Data Type** | **Description**                        |
| --- | :---------: | ------------- | -------------------------------------- |
| 1   | topic_name  | BINARY(192)   | Topic name                             |
| 2   |   db_name   | BINARY(64)    | Database for the topic                 |
| 3   | create_time | TIMESTAMP     | Creation time                          |
| 4   |     sql     | BINARY(1024)  | SQL statement used to create the topic |
D
dapan1121 已提交
277 278 279

## INS_SUBSCRIPTIONS

dengyihao's avatar
dengyihao 已提交
280 281 282 283 284 285 286 287
| #   |   **Column**   | **Data Type** | **Description**             |
| --- | :------------: | ------------- | --------------------------- |
| 1   |   topic_name   | BINARY(204)   | Subscribed topic            |
| 2   | consumer_group | BINARY(193)   | Subscribed consumer group   |
| 3   |   vgroup_id    | INT           | Vgroup ID for the consumer  |
| 4   |  consumer_id   | BIGINT        | Consumer ID                 |
| 5   |     offset     | BINARY(64)    | Consumption progress        |
| 6   |      rows      | BIGINT        | Number of consumption items |
D
dapan1121 已提交
288 289 290

## INS_STREAMS

dengyihao's avatar
dengyihao 已提交
291 292 293 294 295 296 297 298 299 300 301
| #   |  **Column**  | **Data Type** | **Description**                                                                                                                                                                                |
| --- | :----------: | ------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1   | stream_name  | BINARY(64)    | Stream name                                                                                                                                                                                    |
| 2   | create_time  | TIMESTAMP     | Creation time                                                                                                                                                                                  |
| 3   |     sql      | BINARY(1024)  | SQL statement used to create the stream                                                                                                                                                        |
| 4   |    status    | BINARY(20)    | Current status                                                                                                                                                                                 |
| 5   |  source_db   | BINARY(64)    | Source database                                                                                                                                                                                |
| 6   |  target_db   | BINARY(64)    | Target database                                                                                                                                                                                |
| 7   | target_table | BINARY(192)   | Target table                                                                                                                                                                                   |
| 8   |  watermark   | BIGINT        | Watermark (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.                          |
| 9   |   trigger    | INT           | Method 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. |