...
All Keywords | |||||
---|---|---|---|---|---|
Version | Non-reserved Keywords | Reserved Keywords | |||
Hive 1.2.0 |
|
| |||
Hive 2.0.0 | removed: added: | added: | |||
Hive 2.1.0 | added: | added: | |||
Hive 2.2.0 | removed: added: | | removed: added: | added: DAYOFWEEK
| , VIEWS|
Hive 2.3.0 | .0added: | removed: added: |
Info | ||
---|---|---|
| ||
REGEXP and RLIKE are non-reserved keywords prior to Hive 2.0.0 and reserved keywords starting in Hive 2.0.0 (HIVE-11703). |
Reserved keywords are permitted as identifiers if you quote them as described in Supporting Quoted Identifiers in Column Names (version 0.13.0 and later, see HIVE-6013). Most of the keywords are reserved through HIVE-6617 in order to reduce the ambiguity in grammar (version 1.2.0 and later). There are two ways if the user still would like to use those reserved keywords as identifiers: (1) use quoted identifiers, (2) set hive.support.sql11.reserved.keywords=false. (version 2.1.0 and earlier)
Create/Drop/Alter/Use Database
...
| added: | |
Hive 3.0.0 | removed: added: | removed: added: |
Hive 3.1.0 | N/A | N/A |
Hive 4.0.0 | added: | added: |
Info | ||
---|---|---|
| ||
REGEXP and RLIKE are non-reserved keywords prior to Hive 2.0.0 and reserved keywords starting in Hive 2.0.0 (HIVE-11703). |
Reserved keywords are permitted as identifiers if you quote them as described in Supporting Quoted Identifiers in Column Names (version 0.13.0 and later, see HIVE-6013). Most of the keywords are reserved through HIVE-6617 in order to reduce the ambiguity in grammar (version 1.2.0 and later). There are two ways if the user still would like to use those reserved keywords as identifiers: (1) use quoted identifiers, (2) set hive.support.sql11.reserved.keywords=false. (version 2.1.0 and earlier)
Create/Drop/Alter/Use Database
Anchor | ||||
---|---|---|---|---|
|
Code Block |
---|
CREATE [REMOTE] |
Code Block |
---|
CREATE [REMOTE] (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[MANAGEDLOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
|
The uses of SCHEMA and DATABASE are interchangeable – they mean the same thing. CREATE DATABASE was added in Hive 0.6 (HIVE-675). The WITH DBPROPERTIES clause was added in Hive 0.7 (HIVE-1836).
MANAGEDLOCATION was added to database in Hive 4.0.0 (HIVE-22995). LOCATION now refers to the default directory for external tables and MANAGEDLOCATION refers to the default directory for managed tables. Its recommended that MANAGEDLOCATION be within metastore.warehouse.dir so all managed tables have a common root where common governance policies. It can be used with metastore.warehouse.tenant.colocation to have it point to a directory outside the warehouse root directory to have a tenant based common root where quotas and other policies can be set.
REMOTE databases were added in Hive 4.0.0 (HIVE-24396) for support for Data connectors. See documentation for Data connectors.
Drop Database
Code Block |
---|
DROP (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [RESTRICT|CASCADE]; [MANAGEDLOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)]; |
The uses of SCHEMA and DATABASE are interchangeable – they mean the same thing. CREATE DATABASE was The uses of SCHEMA and DATABASE are interchangeable – they mean the same thing. DROP DATABASE was added in Hive 0.6 (HIVE-675). The default behavior is RESTRICT, where DROP DATABASE will fail if the database is not empty. To drop the tables in the database as well, use DROP DATABASE ... CASCADE. Support for RESTRICT and CASCADE The WITH DBPROPERTIES clause was added in Hive 0.8 7 (HIVE-20901836).
Alter Database
MANAGEDLOCATION was added to database in Hive 4.0.0 (HIVE-22995). LOCATION now refers to the default directory for external tables and MANAGEDLOCATION refers to the default directory for managed tables. Its recommended that MANAGEDLOCATION be within metastore.warehouse.dir so all managed tables have a common root where common governance policies. It can be used with metastore.warehouse.tenant.colocation to have it point to a directory outside the warehouse root directory to have a tenant based common root where quotas and other policies can be set.
REMOTE databases were added in Hive 4.0.0 (HIVE-24396) for support for Data connectors. See documentation for Data connectors.
Drop Database
Code Block |
---|
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
|
The uses of SCHEMA and DATABASE are interchangeable – they mean the same thing. DROP DATABASE was added in Hive 0.6 (HIVE-675). The default behavior is RESTRICT, where DROP DATABASE will fail if the database is not empty. To drop the tables in the database as well, use DROP DATABASE ... CASCADE. Support for RESTRICT and CASCADE was added in Hive 0.8 (HIVE-2090).
Alter Database
Code Block |
---|
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...); -- (Note: SCHEMA added in Hive 0.14.0 |
Code Block |
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...); -- (Note: SCHEMA added in Hive 0.14.0) ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0) ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later) ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION hdfs_path; OWNER [USER|ROLE] user_or_role; -- (Note: Hive 40.013.0 and later) ; SCHEMA added in Hive 0.14.0) ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later) ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION hdfs_path; -- (Note: Hive 4.0.0 and later) |
The uses of SCHEMA and DATABASE The uses of SCHEMA and DATABASE are interchangeable – they mean the same thing. ALTER SCHEMA was added in Hive 0.14 (HIVE-6601).
...
Table of Content Zone | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
These statements provide a way to query the Hive metastore for existing data and metadata accessible to this Hive system. 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 Connectors
Since Hive 4.0.0 via HIVE-24396 SHOW CONNECTORS lists all of the connectors defined in the metastore (depending on the user's access). Show Tables/Views/Materialized Views/Partitions/IndexesShow Tables
Show Views
Show Materialized Views
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.
Note: Please use hr >= 10 instead of hr - 10 >= 0 to filter the results, as Metastore would not push the latter predicate down into the underlying storage. 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. 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.
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. For more information, see the TBLPROPERTIES clause in Create Table above. Show Create Table
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
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
SHOW COLUMNS shows all the columns in a table including partition columns.
Show Functions
SHOW FUNCTIONS lists all the user defined and builtin functions, filtered by the the regular expression if specified with LIKE. Show Granted Roles and PrivilegesHive deprecated authorization mode / 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
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):
Show Conf
SHOW CONF returns a description of the specified configuration property.
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
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:
Show Compactions
SHOW COMPACTIONS returns a list of all tables and partitions compaction requests currently being compacted processed or scheduled for compaction when Hive transactions are being used, including this information:
Examples:
Compactions are initiated automatically, but can also be initiated manually with an ALTER TABLE COMPACT statement. |
...