[Hive-languagemanual] Create/drop/grant/revoke Roles and Privileges/show use

Create/drop/grant/revoke Roles and Privileges

Hive Default authorization-legacy Mode has information about these DDL statements:

• CREATE ROLE
• GRANT ROLE
• REVOKE ROLE
• GRANT Privilege_type
• REVOKE Privilege_type
• DROP ROLE
• SHOW ROLE GRANT
• SHOW GRANT

For SQL standard based authorization in Hive 0.13.0 and later releases, see these DDL statements:

• Role Management Commands
  • CREATE ROLE
  • GRANT ROLE
  • REVOKE ROLE
  • DROP ROLE
  • SHOW ROLES
  • SHOW ROLE GRANT
  • SHOW Current ROLES
  • SET ROLE
  • SHOW Principals
• Object Privilege Commands
  • GRANT Privilege_type
  • REVOKE Privilege_type
  • SHOW GRANT

Show

• Show Databases
• Show tables/partitions/indexes
  • Show Tables
  • Show partitions
  • Show table/partition Extended
  • Show Table Properties
  • Show Create Table
  • Show Indexes
• Show Columns
• Show Functions
• Show granted Roles and privileges
• Show Locks
• Show Conf
• Show transactions
• Show compactions

These statements provide a through to query the hive Metastore for existing data and metadata accessible to this hive system.

Show Databases

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 is ' * ' for any character (s) or ' | ' for a choice. Examples is ' employees ', ' emp* ', ' emp*|*ees ', all of the which would match the database named ' Employees '.

Show Tables/partitions/indexesshow Tables

SHOW TABLES [IN database_name] [identifier_with_wildcards];

SHOW TABLESLists all the base tables and views in the current database (or the one explicitly named using the IN clause) with Name s matching the optional regular expression. Wildcards in the regular expression can only is ' * ' for any character (s) or ' | ' for a choice. Examples is ' Page_view ', ' page_v* ', ' *view|page* ', all which would match the ' page_view ' table. Matching tables is listed in alphabetical order. It is not a error if there is no matching tables found in Metastore. If No regular expression is given and all tables in the selected database is listed.

Show partitions

SHOW PARTITIONS table_name;

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

Version Information

Icon

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. For example:

SHOW PARTITIONS table_name PARTITION(ds=‘2010-03-03‘);            -- (Note: Hive 0.6and later)SHOW PARTITIONS table_name PARTITION(hr=‘12‘);                    -- (Note: Hive 0.6and later)SHOW PARTITIONS table_name PARTITION(ds=‘2010-03-03‘, hr=‘12‘);   -- (Note: Hive 0.6and later)

Version Information

Icon

Starting with Hive 0.13.0, SHOW partitions can specify a database (HIVE-5912).

SHOW PARTITIONS [db_name.]table_name [PARTITION(partition_spec)];   -- (Note: Hive 0.13.0and later)

Example:

SHOW PARTITIONS databaseFoo.tableBar PARTITION(ds=‘2010-03-03‘, hr=‘12‘);   -- (Note: Hive 0.13.0and later)

Show table/partition Extended

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

SHOW TABLE EXTENDED would 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, M Axfilesize, Minfilesize,lastaccesstime, and LastUpdateTime. If partition is present, it would output the given partition ' s file system information instead of table ' s file system infor Mation.

Show Table Properties

Version Information

Icon

As of Hive 0.10.0.

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

Version Information

Icon

As of Hive 0.10.

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

Version Information

Icon

As of Hive 0.7.

SHOW [FORMATTED] (INDEX|INDEXES) ON table_with_index [(FROM|IN) db_name];

SHOW INDEXES shows all of the INDEXES in a certain column, as well as information about Them:index name, table name, name s of the columns used as keys, index table name, index type, and comment. If The formatted keyword is used and then column titles was printed for each column.

Show Columns

Version Information

Icon

As of Hive 0.10.

SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name];

SHOW COLUMNS shows all the COLUMNS in a table including partition COLUMNS.

Show Functions

SHOW FUNCTIONS "a.*";

SHOW FUNCTIONS lists all the user defined and Builtin FUNCTIONS matching the regular expression. To get any functions use ". *"

Show granted Roles and privileges

Hive Default authorization-legacy Mode has information about these SHOW statements:

• SHOW ROLE GRANT
• SHOW GRANT

In Hive 0.13.0 and later releases, SQL standard based authorization have these SHOW statements:

• SHOW ROLE GRANT
• SHOW GRANT
• SHOW Current ROLES
• SHOW ROLES
• SHOW Principals

Show Locks

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 is being used, SHOW LOCKS returns this information (see HIVE-6460):

• Database name
• Table name
• Partition name (if the table is partitioned)
• The state of the lock is in, which can:
  • "Acquired", Haven requestor holds the lock
  • "Waiting", haven requestor is waiting for the lock
  • "Aborted", Haven lock have timed out but have not yet been cleaned up
• The type of lock, which can be:
  • "Exclusive" –no one else can hold the lock in 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; conf usingly, 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 lo CKS is allowed (obtained by update and delete)
• ID of the transaction this lock are associated with, if there is one
• Last time the holder of this lock sent a heartbeat indicating it is still alive
• The time the lock was acquired, if it had been acquired
• Hive user who requested the lock
• Host the user is running on

Show Conf

Version Information

Icon

As of Hive 0.14.0.

SHOW CONF <configuration_name>;

SHOW CONF Returns a description of the specified configuration property.

• Default value
• Required Type
• Description

Note that show CONF does does show the current value of a configuration property. For settings, use the ' Set ' command in the CLI or a HiveQL script (see Commands) or in Beeline (see Beeli NE Hive Commands).

Show transactions

Version Information

Icon

As of Hive 0.13.0 (see Hive transactions).

SHOW TRANSACTIONS;

SHOW transactions is to use by administrators when Hive transactions is being used. It returns a list of all currently open and aborted transactions on the system, including this information:

• Transaction ID
• Transaction state
• User who started the transaction
• Machine where the transaction is started

Show compactions

Version Information

Icon

As of Hive 0.13.0 (see Hive transactions).

SHOW COMPACTIONS;

SHOW Compactions Returns a list of all tables and partitions currently being compacted or scheduled for compaction when Hi ve transactions is 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 are in, which can be:
  • "Initiated" –waiting in the queue to be compacted
  • "Working" –being compacted
  • "Ready for cleaning", haven compaction have been done and the old files is 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 is initiated automatically, but can also is initiated manually with an ALTER TABLE COMPACT statement.

[Hive-languagemanual] Create/drop/grant/revoke Roles and Privileges/show use