hive之路10-show命令

show命令在SQL中的使用频率是非常高的，本文中主要介绍了show的几个常用方法

Show Command

Show Databases

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 DATABASES [LIKE 'identifier_with_wildcards'];

Show Tables/Views/Materialized Views/Partitions/Indexes

Show Tables

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.

SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

Show Views

SHOW VIEWS;                                -- show all views in the current database
SHOW VIEWS 'test_*';                       -- show all views that start with "test_"
SHOW VIEWS '*view2';                       -- show all views that end in "view2"
SHOW VIEWS LIKE 'test_view1|test_view2';   -- show views named either "test_view1" or "test_view2"
SHOW VIEWS FROM test1;                     -- show views from database test1
SHOW VIEWS IN test1;                       -- show views from database test1 (FROM and IN are same)
SHOW VIEWS IN test1 "test_*";

Show Partitions

SHOW PARTITIONS lists all the existing partitions for a given base table. Partitions are listed in alphabetical order.

It is also possible to specify parts of a partition specification to filter the resulting list.

• 按照字母的顺序显示分区
• 指定显示的分区

SHOW PARTITIONS table_name;

-- 指定显示的分区
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)

Show Table/Partition Extended

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.

• 显示的信息更全面

SHOW TABLE EXTENDED [IN|FROM database_name] LIKE 'identifier_with_wildcards' [PARTITION(partition_spec)];

-- demo
hive> show table extended like part_table;
OK

Show Table Properties

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 TBLPROPERTIES tblname;
SHOW TBLPROPERTIES tblname("foo");

Show Locks

锁主要是作用于表和分区上面。该命令显示的具体信息包含：

1. 表名称和分区名称
2. 锁状态：获得、等待、终止
   1. “acquired” – the requestor holds the lock
   2. “waiting” – the requestor is waiting for the lock
   3. “aborted” – the lock has timed out but has not yet been cleaned up
3. 锁类型：独占锁、共享读锁、共享写锁
   1. “exclusive” – no one else can hold the lock at the same time (obtained mostly by DDL operations such as drop table)
   2. “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)
   3. “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)
4. 与锁关联的事务ID(若存在)
5. 请求锁的用户
6. 用户运行的主机

SHOW LOCKS ;
SHOW LOCKS  EXTENDED;
SHOW LOCKS  PARTITION ();
SHOW LOCKS  PARTITION () EXTENDED;
SHOW LOCKS (DATABASE|SCHEMA) database_name;

Show Transactions

It returns a list of all currently open and aborted transactions in the system, including this information:

1. 事务ID
2. 事务状态
3. 启动事务的用户
4. 运行事务的机器
5. 事务启动的时间戳