show命令在SQL中的使用频率是非常高的,本文中主要介绍了show的几个常用方法
Show Command
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
lists all the base tables and views in the current database (or the one explicitly named using theIN
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 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 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 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
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 ;
SHOW LOCKS EXTENDED;
SHOW LOCKS PARTITION ();
SHOW LOCKS PARTITION () EXTENDED;
SHOW LOCKS (DATABASE|SCHEMA) database_name;
It returns a list of all currently open and aborted transactions in the system, including this information: