DESCRIBE TABLE

Description

DESCRIBE TABLE statement returns the basic metadata information of a table. The metadata information includes column name, column type and column comment. Optionally a partition spec or column name may be specified to return the metadata pertaining to a partition or column respectively.

Syntax

  1. { DESC | DESCRIBE } [ TABLE ] [ format ] table_identifier [ partition_spec ] [ col_name ]

Parameters

  • format

    Specifies the optional format of describe output. If EXTENDED is specified then additional metadata information (such as parent database, owner, and access time) is returned.

  • table_identifier

    Specifies a table name, which may be optionally qualified with a database name.

    Syntax: [ database_name. ] table_name

  • partition_spec

    An optional parameter that specifies a comma separated list of key and value pairs for partitions. When specified, additional partition metadata is returned.

    Syntax: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

  • col_name

    An optional parameter that specifies the column name that needs to be described. The supplied column name may be optionally qualified. Parameters partition_spec and col_name are mutually exclusive and can not be specified together. Currently nested columns are not allowed to be specified.

    Syntax: [ database_name. ] [ table_name. ] column_name

Examples

  1. -- Creates a table `customer`. Assumes current database is `salesdb`.
  2. CREATE TABLE customer(
  3.         cust_id INT,
  4.         state VARCHAR(20),
  5.         name STRING COMMENT 'Short name'
  6.     )
  7.     USING parquet
  8.     PARTITIONED BY (state);
  10. INSERT INTO customer PARTITION (state = 'AR') VALUES (100, 'Mike');
  12. -- Returns basic metadata information for unqualified table `customer`
  13. DESCRIBE TABLE customer;
  14. +-----------------------+---------+----------+
  15. |               col_name|data_type|   comment|
  16. +-----------------------+---------+----------+
  17. |                cust_id|      int|      null|
  18. |                   name|   string|Short name|
  19. |                  state|   string|      null|
  20. |# Partition Information|         |          |
  21. |             # col_name|data_type|   comment|
  22. |                  state|   string|      null|
  23. +-----------------------+---------+----------+
  25. -- Returns basic metadata information for qualified table `customer`
  26. DESCRIBE TABLE salesdb.customer;
  27. +-----------------------+---------+----------+
  28. |               col_name|data_type|   comment|
  29. +-----------------------+---------+----------+
  30. |                cust_id|      int|      null|
  31. |                   name|   string|Short name|
  32. |                  state|   string|      null|
  33. |# Partition Information|         |          |
  34. |             # col_name|data_type|   comment|
  35. |                  state|   string|      null|
  36. +-----------------------+---------+----------+
  38. -- Returns additional metadata such as parent database, owner, access time etc.
  39. DESCRIBE TABLE EXTENDED customer;
  40. +----------------------------+------------------------------+----------+
  41. |                    col_name|                     data_type|   comment|
  42. +----------------------------+------------------------------+----------+
  43. |                     cust_id|                           int|      null|
  44. |                        name|                        string|Short name|
  45. |                       state|                        string|      null|
  46. |     # Partition Information|                              |          |
  47. |                  # col_name|                     data_type|   comment|
  48. |                       state|                        string|      null|
  49. |                            |                              |          |
  50. |# Detailed Table Information|                              |          |
  51. |                    Database|                       default|          |
  52. |                       Table|                      customer|          |
  53. |                       Owner|                 <TABLE OWNER>|          |
  54. |                Created Time|  Tue Apr 07 22:56:34 JST 2020|          |
  55. |                 Last Access|                       UNKNOWN|          |
  56. |                  Created By|               <SPARK VERSION>|          |
  57. |                        Type|                       MANAGED|          |
  58. |                    Provider|                       parquet|          |
  59. |                    Location|file:/tmp/salesdb.db/custom...|          |
  60. |               Serde Library|org.apache.hadoop.hive.ql.i...|          |
  61. |                 InputFormat|org.apache.hadoop.hive.ql.i...|          |
  62. |                OutputFormat|org.apache.hadoop.hive.ql.i...|          |
  63. |          Partition Provider|                       Catalog|          |
  64. +----------------------------+------------------------------+----------+
  66. -- Returns partition metadata such as partitioning column name, column type and comment.
  67. DESCRIBE TABLE EXTENDED customer PARTITION (state = 'AR');
  68. +------------------------------+------------------------------+----------+
  69. |                      col_name|                     data_type|   comment|
  70. +------------------------------+------------------------------+----------+
  71. |                       cust_id|                           int|      null|
  72. |                          name|                        string|Short name|
  73. |                         state|                        string|      null|
  74. |       # Partition Information|                              |          |
  75. |                    # col_name|                     data_type|   comment|
  76. |                         state|                        string|      null|
  77. |                              |                              |          |
  78. |# Detailed Partition Inform...|                              |          |
  79. |                      Database|                       default|          |
  80. |                         Table|                      customer|          |
  81. |              Partition Values|                    [state=AR]|          |
  82. |                      Location|file:/tmp/salesdb.db/custom...|          |
  83. |                 Serde Library|org.apache.hadoop.hive.ql.i...|          |
  84. |                   InputFormat|org.apache.hadoop.hive.ql.i...|          |
  85. |                  OutputFormat|org.apache.hadoop.hive.ql.i...|          |
  86. |            Storage Properties|[serialization.format=1, pa...|          |
  87. |          Partition Parameters|{transient_lastDdlTime=1586...|          |
  88. |                  Created Time|  Tue Apr 07 23:05:43 JST 2020|          |
  89. |                   Last Access|                       UNKNOWN|          |
  90. |          Partition Statistics|                     659 bytes|          |
  91. |                              |                              |          |
  92. |         # Storage Information|                              |          |
  93. |                      Location|file:/tmp/salesdb.db/custom...|          |
  94. |                 Serde Library|org.apache.hadoop.hive.ql.i...|          |
  95. |                   InputFormat|org.apache.hadoop.hive.ql.i...|          |
  96. |                  OutputFormat|org.apache.hadoop.hive.ql.i...|          |
  97. +------------------------------+------------------------------+----------+
  99. -- Returns the metadata for `name` column.
  100. -- Optional `TABLE` clause is omitted and column is fully qualified.
  101. DESCRIBE customer salesdb.customer.name;
  102. +---------+----------+
  103. |info_name|info_value|
  104. +---------+----------+
  105. | col_name|      name|
  106. |data_type|    string|
  107. |  comment|Short name|
  108. +---------+----------+