How do I use SQL to view the schema of a table or database - Lindorm

You can use the DESCRIBE syntax to view the schema of an existing table or database when you want to create a new table or database with the similar schema.

Applicable engines

The DESCRIBE syntax is applicable to LindormTable and LindormTSDB.

Syntax

describe_statement ::= { DESCRIBE | DESC } [{ DATABASE | TABLE }] identifier

Supported parameters

The following table describes the parameters of the DESCRIBE syntax supported by LindormTable and LindormTSDB.

Parameter	LindormTable	LindormTSDB
DATABASE	✖️	〇
TABLE	〇	〇
identifier	〇	〇

Parameters

DATABASE

Only LindormTSDB supports the DATABASE parameter.

The DATABASE parameter is specified in the DESCRIBE syntax to obtain the schema of a database.

TABLE

The TABLE parameter is specified in the DESCRIBE syntax to obtain the schema of a table. Note that the table schema returned for LindormTSDB and LindormTable is not exactly the same.

identifier

If the DATABASE parameter is specified in the DESCRIBE statement, the identifier parameter specifies the database name by default. If the TABLE parameter is specified in the DESCRIBE statement, the identifier parameter specifies the table name by default. If the DATABASE and TABLE parameters are not specified in the DESCRIBE statement, the identifier parameter specifies the table name by default. For example, the following statement can be executed to obtain the schema of the test table in the current database: DESCRIBE test;

Examples

In the following examples, the test table named sensor is created by executing the following statement:

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
);

Obtain the schema of a table

DESCRIBE TABLE sensor; -- The complete statement.
DESC sensor; -- The abbreviated statement.

The following result is returned for LindormTable:

+--------------+------------+-------------+-----------+----------------+------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |   TYPE    | IS_PRIMARY_KEY | SORT_ORDER |
+--------------+------------+-------------+-----------+----------------+------------+
| default      | sensor     | device_id   | VARCHAR   | true           | ASC        |
| default      | sensor     | region      | VARCHAR   | true           | ASC        |
| default      | sensor     | time        | TIMESTAMP | true           | ASC        |
| default      | sensor     | temperature | DOUBLE    | false          | none       |
| default      | sensor     | humidity    | BIGINT    | false          | none       |
+--------------+------------+-------------+-----------+----------------+------------+

The following result is returned for LindormTSDB:

+-------------+-----------+------------+------------+--------------+
| columnName  | typeName  | columnKind | primaryKey | partitionTag |
+-------------+-----------+------------+------------+--------------+
| device_id   | VARCHAR   | TAG        | true       | true         |
| region      | VARCHAR   | TAG        | true       | true         |
| time        | TIMESTAMP | TIMESTAMP  | true       | false        |
| temperature | DOUBLE    | FIELD      | false      | false        |
| humidity    | BIGINT    | FIELD      | false      | false        |
+-------------+-----------+------------+------------+--------------+

Obtain the schema of a database

Obtain the schema of a time series database named DB1.

DESCRIBE DATABASE DB1;

The following result is returned:

+--------------------+-------+
|     attribute      | value |
+--------------------+-------+
| shard_num_per_node | 0     |
| skip_wal           | false |
| string_compression | false |
| cold_boundary      | 30    |
| partition_interval | 30    |
| ttl                | 60    |
| shard_num          | 0     |
+--------------------+-------+

The following fields are included in the returned results:

shard_num_per_node: The number of shards used by the database on each node. Default value: 1.
skip_wal: Indicates whether logs are recorded. The default value of this field is false, which indicates that logs are recorded for the database.
string_compression: Indicates whether STRING data is compressed. The default value of this field is false, which indicates that STRING data is not compressed.
shard_num: The total number of the database shards in the cluster. This field does not have the default value.
cold_boundary, partition_interval, and ttl: The attributes of the database that are configured in the CREATE DATABASE statement executed to create the database. For more information, see CREATE DATABASE.