These statements provide a way to query the Hive metastore for existing data and metadata accessible to this Hive system. Show Databases| Code Block |
|---|
SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];
|
SHOW DATABASES or SHOW SCHEMAS lists all of the databases defined in the metastore. The uses of SCHEMAS and DATABASES are interchangeable – they mean the same thing. The optional LIKE clause allows the list of databases to be filtered using a regular expression. Wildcards in the regular expression can only be '*' for any character(s) or '|' for a choice. Examples are 'employees', 'emp*', 'emp*|*ees', all of which will match the database named 'employees'. Show Tables/Partitions/IndexesShow Tables| Code Block |
|---|
SHOW TABLES [IN database_name] ['identifier_with_wildcards'];
|
SHOW TABLES lists all the base tables and views in the current database (or the one explicitly named using the IN clause) with names matching the optional regular expression. Wildcards in the regular expression can only be '*' for any character(s) or '|' for a choice. Examples are 'page_view', 'page_v*', '*view|page*', all which will match the 'page_view' table. Matching tables are listed in alphabetical order. It is not an error if there are no matching tables found in metastore. If no regular expression is given then all tables in the selected database are listed.
Show Partitions| Code Block |
|---|
SHOW PARTITIONS table_name;
|
SHOW PARTITIONS lists all the existing partitions for a given base table. Partitions are listed in alphabetical order. | Info |
|---|
| As of Hive 0.6, SHOW PARTITIONS can filter the list of partitions as shown below. |
It is also possible to specify parts of a partition specification to filter the resulting list. | Code Block |
|---|
| SHOW PARTITIONS table_name PARTITION(ds='2010-03-03'); -- (Note: Hive 0.6 and later)
SHOW PARTITIONS table_name PARTITION(hr='12'); -- (Note: Hive 0.6 and later)
SHOW PARTITIONS table_name PARTITION(ds='2010-03-03', hr='12'); -- (Note: Hive 0.6 and later)
|
| Info |
|---|
| Starting with Hive 0.13.0, SHOW PARTITIONS can specify a database (HIVE-5912). |
| Code Block |
|---|
SHOW PARTITIONS [db_name.]table_name [PARTITION(partition_spec)]; -- (Note: Hive 0.13.0 and later)
|
| Code Block |
|---|
| SHOW PARTITIONS databaseFoo.tableBar PARTITION(ds='2010-03-03', hr='12'); -- (Note: Hive 0.13.0 and later)
|
Show Table/Partition Extended| Code Block |
|---|
SHOW TABLE EXTENDED [IN|FROM database_name] LIKE 'identifier_with_wildcards' [PARTITION(partition_spec)];
|
SHOW TABLE EXTENDED will list information for all tables matching the given regular expression. Users cannot use regular expression for table name if a partition specification is present. This command's output includes basic table information and file system information like totalNumberFiles, totalFileSize, maxFileSize, minFileSize,lastAccessTime, and lastUpdateTime. If partition is present, it will output the given partition's file system information instead of table's file system information. | Code Block |
|---|
| hive> show table extended like part_table;
OK
tableName:part_table
owner:thejas
location:file:/tmp/warehouse/part_table
inputformat:org.apache.hadoop.mapred.TextInputFormat
outputformat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
columns:struct columns { i32 i}
partitioned:true
partitionColumns:struct partition_columns { string d}
totalNumberFiles:1
totalFileSize:2
maxFileSize:2
minFileSize:2
lastAccessTime:0
lastUpdateTime:1459382233000
|
Show Table Properties| Code Block |
|---|
SHOW TBLPROPERTIES tblname;
SHOW TBLPROPERTIES tblname("foo");
|
The first form lists all of the table properties for the table in question one per row separated by tabs. The second form of the command prints only the value for the property that's being asked for. Show Create Table| Code Block |
|---|
SHOW CREATE TABLE ([db_name.]table_name|view_name);
|
SHOW CREATE TABLE shows the CREATE TABLE statement that creates a given table, or the CREATE VIEW statement that creates a given view. Show Indexes| Code Block |
|---|
SHOW [FORMATTED] (INDEX|INDEXES) ON table_with_index [(FROM|IN) db_name];
|
SHOW INDEXES shows all of the indexes on a certain column, as well as information about them: index name, table name, names of the columns used as keys, index table name, index type, and comment. If the FORMATTED keyword is used, then column titles are printed for each column. Show Columns| Code Block |
|---|
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name];
|
SHOW COLUMNS shows all the columns in a table including partition columns. Show Functions| Code Block |
|---|
SHOW FUNCTIONS "a.*";
|
SHOW FUNCTIONS lists all the user defined and builtin functions matching the regular expression. To get all functions use ".*" Show Granted Roles and PrivilegesHive Default Authorization - Legacy Mode has information about these SHOW statements: In Hive 0.13.0 and later releases, SQL standard based authorization has these SHOW statements: Show Locks| Code Block |
|---|
| SHOW LOCKS <table_name>;
SHOW LOCKS <table_name> EXTENDED;
SHOW LOCKS <table_name> PARTITION (<partition_spec>);
SHOW LOCKS <table_name> PARTITION (<partition_spec>) EXTENDED;
SHOW LOCKS (DATABASE|SCHEMA) database_name; -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0) |
SHOW LOCKS displays the locks on a table or partition. See Hive Concurrency Model for information about locks. SHOW LOCKS (DATABASE|SCHEMA) is supported from Hive 0.13 for DATABASE (see HIVE-2093) and Hive 0.14 for SCHEMA (see HIVE-6601). SCHEMA and DATABASE are interchangeable – they mean the same thing. When Hive transactions are being used, SHOW LOCKS returns this information (see HIVE-6460): - database name
- table name
- partition name (if the table is partitioned)
- the state the lock is in, which can be:
- "acquired" – the requestor holds the lock
- "waiting" – the requestor is waiting for the lock
- "aborted" – the lock has timed out but has not yet been cleaned up
- Id of the lock blocking this one, if this lock is in "waiting" state
- the type of lock, which can be:
- "exclusive" – no one else can hold the lock at the same time (obtained mostly by DDL operations such as drop table)
- "shared_read" – any number of other shared_read locks can lock the same resource at the same time (obtained by reads; confusingly, an insert operation also obtains a shared_read lock)
- "shared_write" – any number of shared_read locks can lock the same resource at the same time, but no other shared_write locks are allowed (obtained by update and delete)
- ID of the transaction this lock is associated with, if there is one
- last time the holder of this lock sent a heartbeat indicating it was still alive
- the time the lock was acquired, if it has been acquired
- Hive user who requested the lock
- host the user is running on
Show Conf| Code Block |
|---|
| SHOW CONF <configuration_name>; |
SHOW CONF returns a description of the specified configuration property. - default value
- required type
- description
Note that SHOW CONF does not show the current value of a configuration property. For current property settings, use the "set" command in the CLI or a HiveQL script (see Commands) or in Beeline (see Beeline Hive Commands). Show Transactions| Code Block |
|---|
| SHOW TRANSACTIONS; |
SHOW TRANSACTIONS is for use by administrators when Hive transactions are being used. It returns a list of all currently open and aborted transactions in the system, including this information: - transaction ID
- transaction state
- user who started the transaction
- machine where the transaction was started
Show Compactions| Code Block |
|---|
| SHOW COMPACTIONS; |
SHOW COMPACTIONS returns a list of all tables and partitions currently being compacted or scheduled for compaction when Hive transactions are being used, including this information: - database name
- table name
- partition name (if the table is partitioned)
- whether it is a major or minor compaction
- the state the compaction is in, which can be:
- "initiated" – waiting in the queue to be compacted
- "working" – being compacted
- "ready for cleaning" – the compaction has been done and the old files are scheduled to be cleaned
- thread ID of the worker thread doing the compaction (only if in working state)
- the time at which the compaction started (only if in working or ready for cleaning state)
Compactions are initiated automatically, but can also be initiated manually with an ALTER TABLE COMPACT statement. |