Versions Compared

Old Version 135

changes.mady.by.user Lefty Leverenz

Saved on

compared with

New Version 136

changes.mady.by.user Prasanth J

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Table of Content Zone
maxLevel4
locationtop
typelist

Describe Database

Info
titleVersion information

As of Hive 0.7.

Code Block
DESCRIBE DATABASE [EXTENDED] db_name;

DESCRIBE DATABASE shows the name of the database, its comment (if one has been set), and its root location on the filesystem. EXTENDED also shows the database properties.

Describe Table/View/Column

Code Block
DESCRIBE [EXTENDED|FORMATTED]
  [db_name.]table_name[.col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

DESCRIBE shows the list of columns including partition columns for the given table. If the EXTENDED keyword is specified then it will show all the metadata for the table in Thrift serialized form. This is generally only useful for debugging and not for general use. If the FORMATTED keyword is specified, then it will show the metadata in a tabular format.

Note: DESCRIBE EXTENDED shows the number of rows only if statistics were gathered when the data was loaded (see Newly Created Tables), and if the Hive CLI is used instead of a Thrift client or Beeline. HIVE-6285 will address this issue. Although ANALYZE TABLE gathers statistics after the data has been loaded (see Existing Tables), it does not currently provide information about the number of rows.

If a table has a complex column then you can examine the attributes of this column by specifying table_name.complex_col_name (and '$elem$' for array element, '$key$' for map key, and '$value$' for map value). You can specify this recursively to explore the complex column type.

For a view, DESCRIBE EXTENDED or FORMATTED can be used to retrieve the view's definition. Two relevant attributes are provided: both the original view definition as specified by the user, and an expanded definition used internally by Hive.

Info
titleVersion information

In Hive 0.10.0 and earlier, no distinction is made between partition columns and non-partition columns while displaying columns for DESCRIBE TABLE. From Hive 0.12.0 onwards, they are displayed separately.

In Hive 0.13.0 and later, the configuration parameter hive.display.partition.cols.separately lets you use the old behavior, if desired (HIVE-6689). For an example, see the test case in the patch for HIVE-6689.

Info
titleBug fixed in Hive 0.10.0

Database qualifiers for table names were introduced in Hive 0.7.0, but they were broken for DESCRIBE until a bug fix in Hive 0.10.0 (HIVE-1977).

 

Display column statistics

ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS will compute column statistics for all columns in the specified table (and for all partitions if table is partitioned). To view the gathered column statistics following statements can be used

Code Block
DESCRIBE FORMATTED [db_name.]table_name.column_name;
DESCRIBE FORMATTED [db_name.]table_name.column_name PARTITION (partition_desc);

Describe Partition

Code Block
DESCRIBE [EXTENDED|FORMATTED] [db_name.]table_name PARTITION partition_spec;

This statement lists metadata for a given partition. The output is similar to that of DESCRIBE table_name. Presently, the column information associated with a particular partition is not used while preparing plans.

Example:

Code Block
DESCRIBE page_view PARTITION (ds='2008-08-08');

...