DESC

Description

You can use the statement to perform the following operations:

  • View the schema of a table stored in your CelerData cloud account, along with the materialized view of the table.
  • View the schema of a table stored in the following external data sources, such as Apache Hive™.

Syntax

DESC[RIBE] [catalog_name.][db_name.]table_name [ALL];

Parameters

ParameterRequiredDescription
catalog_nameNoThe name of the internal catalog or an external catalog.
  • If you set the value of the parameter to the name of the internal catalog, which is default_catalog, you can view the schema of the table stored in your CelerData cloud account.
  • If you set the value of the parameter to the name of an external catalog, you can view the schema of the table stored in the external data source.
db_nameNoThe database name.
table_nameYesThe table name.
ALLNo
  • If this keyword is specified, you can view the type of the sort key, materialized view, and schema of a table stored in your CelerData cloud account. If this keyword is not specified, you only view the table schema.
  • Do not specify this keyword when you view the schema of a table stored in an external data source.

Output

+-----------+---------------+-------+------+------+-----+---------+-------+
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra |
+-----------+---------------+-------+------+------+-----+---------+-------+

The following table describes the parameters returned by this statement.

ParameterDescription
IndexNameThe table name. If you view the schema of a table stored in an external data source, this parameter is not returned.
IndexKeysTypeThe type of the sort key of the table. If you view the schema of a table stored in an external data source, this parameter is not returned.
FieldThe column name.
TypeThe data type of the column.
NullWhether the column values can be NULL.
  • yes: indicates the values can be NULL.
  • no: indicates the values cannot be NULL.
KeyWhether the column is used as the sort key.
  • true: indicates the column is used as the sort key.
  • false: indicates the column is not used as the sort key.
DefaultThe default value for the data type of the column. If the data type does not have a default value, a NULL is returned.
Extra
  • If you see the schema of a table stored in your CelerData cloud account, this field displays the following information about the column:
    • The aggregate function used by the column, such as SUM and MIN.
    • Whether a bloom filter index is created on the column. If so, the value of Extra is BLOOM_FILTER.
  • If you see the schema of a table stored in external data sources, this field displays whether the column is the partition column. If the column is the partition column, the value of Extra is partition key.

Note: For information about how a materialized view is displayed in the output, see Example 2.

Examples

Example 1: View the schema of example_table stored in your CelerData cloud account.

DESC example_table;

Or

DESC default_catalog.example_db.example_table;

The output of the preceding statements is as follows.

+-------+---------------+------+-------+---------+-------+
| Field | Type          | Null | Key   | Default | Extra |
+-------+---------------+------+-------+---------+-------+
| k1    | TINYINT       | Yes  | true  | NULL    |       |
| k2    | DECIMAL(10,2) | Yes  | true  | 10.5    |       |
| k3    | CHAR(10)      | Yes  | false | NULL    |       |
| v1    | INT           | Yes  | false | NULL    |       |
+-------+---------------+------+-------+---------+-------+

Example 2: View the schema, type of the sort key, and materialized view of sales_records stored in your CelerData cloud account. In the following example, one materialized view store_amt is created based on sales_records.

DESC db1.sales_records ALL;

+---------------+---------------+-----------+--------+------+-------+---------+-------+
| IndexName     | IndexKeysType | Field     | Type   | Null | Key   | Default | Extra |
+---------------+---------------+-----------+--------+------+-------+---------+-------+
| sales_records | DUP_KEYS      | record_id | INT    | Yes  | true  | NULL    |       |
|               |               | seller_id | INT    | Yes  | true  | NULL    |       |
|               |               | store_id  | INT    | Yes  | true  | NULL    |       |
|               |               | sale_date | DATE   | Yes  | false | NULL    | NONE  |
|               |               | sale_amt  | BIGINT | Yes  | false | NULL    | NONE  |
|               |               |           |        |      |       |         |       |
| store_amt     | AGG_KEYS      | store_id  | INT    | Yes  | true  | NULL    |       |
|               |               | sale_amt  | BIGINT | Yes  | false | NULL    | SUM   |
+---------------+---------------+-----------+--------+------+-------+---------+-------+

Example 3: View the schema of hive_table stored in your Hive cluster.

DESC hive_catalog.hive_db.hive_table;

+-------+----------------+------+-------+---------+---------------+ 
| Field | Type           | Null | Key   | Default | Extra         | 
+-------+----------------+------+-------+---------+---------------+ 
| id    | INT            | Yes  | false | NULL    |               | 
| name  | VARCHAR(65533) | Yes  | false | NULL    |               | 
| date  | DATE           | Yes  | false | NULL    | partition key | 
+-------+----------------+------+-------+---------+---------------+

References

In this article
Copyright © 2024 CelerData