Use DESCRIBE to view table and database schema - Lindorm

Use DESCRIBE (or its alias DESC) to inspect the schema of a table or database. This is useful when you need to understand the structure of an existing object before creating a similar one.

Applicable engines

DESCRIBE is supported by LindormTable and LindormTSDB.

Syntax

{ DESCRIBE | DESC } [{ DATABASE | TABLE }] <identifier>

Omitting the TABLE keyword defaults to describing a table, not a database. To describe a database, you must explicitly specify DATABASE.

DATABASE is supported only in LindormTSDB. LindormTable does not support the DATABASE parameter.

Parameters

Parameter	LindormTable	LindormTSDB	Description
`DATABASE`	Not supported	Supported	Returns the schema of a database.
`TABLE`	Supported	Supported	Returns the schema of a table.
`<identifier>`	Supported	Supported	The name of the table or database. If `DATABASE` is specified, `<identifier>` is the database name. Otherwise, `<identifier>` is the table name.

Examples

The following examples use a sensor table created with this 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)
);

Get the schema of a table

DESCRIBE TABLE sensor;  -- Full form
DESC sensor;            -- Abbreviated form

LindormTable result

LindormTable returns one row per column with the following fields:

Column	Description
`TABLE_SCHEMA`	The schema (database) the table belongs to.
`TABLE_NAME`	The table name.
`COLUMN_NAME`	The column name.
`TYPE`	The column data type, such as `VARCHAR`, `TIMESTAMP`, `DOUBLE`, or `BIGINT`.
`IS_PRIMARY_KEY`	Whether the column is part of the primary key. `true` or `false`.
`SORT_ORDER`	The sort order for primary key columns. `ASC` (ascending). Non-primary-key columns show `none`.

+--------------+------------+-------------+-----------+----------------+------------+
| 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       |
+--------------+------------+-------------+-----------+----------------+------------+

LindormTSDB result

LindormTSDB returns a different set of columns that reflect the time series data model. Each row represents one column with the following fields:

Column	Description
`columnName`	The column name.
`typeName`	The column data type, such as `VARCHAR`, `TIMESTAMP`, `DOUBLE`, or `BIGINT`.
`columnKind`	The role of the column in the time series model. `TAG` columns are dimension columns used for filtering and grouping. `FIELD` columns store the measured values. `TIMESTAMP` is the time column.
`primaryKey`	Whether the column is part of the primary key. `true` or `false`.
`partitionTag`	Whether the column is used as a partition key. `true` or `false`.

+-------------+-----------+------------+------------+--------------+
| 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        |
+-------------+-----------+------------+------------+--------------+

Get the schema of a database

This example describes a LindormTSDB database named DB1.

DESCRIBE DATABASE DB1;

LindormTSDB returns one row per database attribute with the following fields:

Attribute	Default	Description
`shard_num_per_node`	1	The number of shards on each node.
`skip_wal`	false	Whether write-ahead logging (WAL) is disabled. `false` means WAL is enabled and writes are logged.
`string_compression`	false	Whether STRING column compression is enabled. `false` means STRING data is stored uncompressed.
`shard_num`	—	The total number of shards across the cluster. No default value.
`cold_boundary`	—	Configured in `CREATE DATABASE`.
`partition_interval`	—	Configured in `CREATE DATABASE`.
`ttl`	—	Configured in `CREATE DATABASE`.

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

For details on cold_boundary, partition_interval, and ttl, see CREATE DATABASE.