[Hive-languagemanual] Create/drop/alter View create/drop/alter Index create/drop Function

Create/drop/alter View

• Create View
• Drop View
• Alter View Properties
• Alter View as Select

Version Information

Icon

View support was only available in Hive 0.6 and later.

Create View

CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ]  [COMMENT view_comment]  [TBLPROPERTIES (property_name = property_value, ...)]  AS SELECT ...;

CREATE view creates a view with the given name. An error was thrown if a table or view with the same name already exists. You can use IF not EXISTS to skip the error.

If No column names is supplied, the names of the view ' s columns would be derived automatically from the defining SELECT EX Pression. (If the SELECT contains unaliased scalar expressions such as x+y, the resulting view column names'll be generated in the Form _C0, _C1, etc.) When renaming columns, column comments can also optionally is supplied. (Comments is not automatically inherited from underlying columns.)

A CREATE View statement would fail if the VIEW ' s defining SELECT expression is invalid.

Note that a view is a purely logical object with no associated storage. (No support for materialized views are currently available in Hive.) When a query references a view, the view's definition is evaluated on order to produce a set of rows for further Processin G by the query. (This was a conceptual description; in fact, as part of the query optimization, Hive could combine the view ' s definition with the Query ' s, e.g. pushing filters from the query to the The view.)

A View ' s schema is frozen at the time of the view is created; Subsequent changes to underlying tables (e.g. adding a column) won't is reflected in the view ' s schema. If an underlying table was dropped or changed in a incompatible fashion, subsequent attempts to query the invalid view wil L fail.

Views is read-only and May is used as the target of Load/insert/alter. For changing metadata, see ALTER VIEW.

A view may contain ORDER by and LIMIT clauses. If A referencing query also contains these clauses, the query-level clauses is evaluated after the view clauses (and after any other operations in the query). For example, if a view specifies limit 5, and a referencing query are executed as (SELECT * from v limit), then at the most 5 rows would be returned.

Starting with Hive 0.13.0, the view's SELECT statement can include one or more common table expressions (CTEs) as shown in The SELECT syntax. For examples of CTEs in the CREATE VIEW statements, see Common Table Expression.

Example of view creation:

create VIEW onion_referrers (URL comment  ' URL of referring page ' "    COMMENT  Code class= "Java string" > ' referrers to the Onion website '    as    select DISTINCT referrer_url    from Page_view    where page_url= ' http://www.theonion.com ' ;

Use SHOW create TABLE to display the CREATE VIEW statement this created a view.

Drop View

DROP VIEW [IF EXISTS] view_name;

DROP view removes metadata for the specified view. (It is illegal to use DROP TABLE on a view.)

When dropping a view referenced by other views, no warning is given (the dependent view was left dangling as invalid and Must is dropped or recreated by the user).

In Hive 0.7.0 or later, DROP returns a error if the view doesn ' t exist, unless if EXISTS is specified or the Configuratio N variable hive.exec.drop.ignorenonexistent is set to true.

Example:

DROP VIEW onion_referrers;

Alter View Properties

ALTER VIEW view_name SET TBLPROPERTIES table_properties;table_properties:  : (property_name = property_value, property_name = property_value, ...)

As with the ALTER TABLE, you can use the this statement to add your own metadata to a view.

Alter View as Select

Version Information

Icon

As of Hive 0.11.

ALTER VIEW view_name AS select_statement;

Alter View as Select changes the definition of a View, which must exist. The syntax is similar to, for create view and the effect are the same as for create OR REPLACE view.

Note:the view must already exist, and if the view has partitions, it could is replaced by Alter view as Select.

Create/drop/alter Index

Version Information

Icon

As of Hive 0.7.

This section provides a brief introduction to Hive indexes, which is documented more fully here:

• Overview of Hive Indexes
• Indexes Design Document

In Hive 0.12.0 and earlier releases, the index name was case-sensitive for CREATE Index and DROP index statements. However, ALTER INDEX requires an index name is created with lowercase letters (see HIVE-2752). This bug was fixed in Hive 0.13.0 by making index names case-insensitive for all HiveQL statements. For releases prior to 0.13.0, the best practice are to use lowercase letters for all index names.

Create Index

CREATE INDEX index_name  ON TABLE base_table_name (col_name, ...)  AS index_type  [WITH DEFERRED REBUILD]  [IDXPROPERTIES (property_name=property_value, ...)]  [IN TABLE index_table_name]  [     [ ROW FORMAT ...] STORED AS ...     | STORED BY ...  ]  [LOCATION hdfs_path]  [TBLPROPERTIES (...)]  [COMMENT "index comment"];

CREATE Index creates an index on a table using the given list of columns as keys. See CREATE INDEX in the Indexes design document.

Drop Index

DROP INDEX [IF EXISTS] index_name ON table_name;

DROP INDEX drops the index, as well as deleting the index table.

In Hive 0.7.0 or later, DROP returns a error if the index doesn ' t exist, unless if EXISTS is specified or the Configurati On variable hive.exec.drop.ignorenonexistent are set to true.

Alter Index

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

ALTER INDEX ... REBUILD builds an index that is created using the WITH DEFERRED REBUILD clause, or rebuilds a previously built index. If PARTITION is specified, the only this PARTITION is rebuilt.

Create/drop functiontemporary functionscreate Temporary Function

CREATE TEMPORARY FUNCTION function_name AS class_name;

This statement lets you create a function, that's implemented by the class_name. You can use this function in Hive queries as long as the session lasts. You can use any class, which is in the class path of Hive. You can add jars to class path by executing ' Add JAR ' statements. Refer to the CLI, Hive Interactive Shell Commands, including Hive Resources, Add/delete files from the Hive classpath. Using This, you can register User Defined Functions (UDF ' s).

Also See Hive Plugins for general information about creating custom UDFs.

Drop Temporary Function

You can unregister a UDF as follows:

DROP TEMPORARY FUNCTION [IF EXISTS] function_name;

In Hive 0.7.0 or later, DROP returns a error if the function doesn ' t exist, unless if EXISTS is specified or the Configur ation variable hive.exec.drop.ignorenonexistent is set to true.

Permanent Functions

In Hive 0.13 or later, functions can is registered to the Metastore, so they can is referenced in a query without have t o Create a temporary function each session.

Create Function

Version Information

Icon

As of Hive 0.13.0 (HIVE-6047).

CREATE FUNCTION [db_name.]function_name AS class_name  [USING JAR|FILE|ARCHIVE ‘file_uri‘[, JAR|FILE|ARCHIVE ‘file_uri‘] ];

This statement lets you create a function, that's implemented by the class_name. Jars, files, or archives which need to being added to the environment can is specified with the USING clause; When the function was referenced for the first time by a Hive session, these resources would be added to the environment as If ADD Jar/file had been issued. If Hive is no in local mode and then the resource location must be a non-local URI such as an HDFS location.

The function is added to the database specified, or to the current database at the time, the function is created . The function can be referenced by fully qualifying the function name (db_name.funciton_name), or can be referenced without Qualification if the function is in the current database.

Drop Function

Version Information

Icon

As of Hive 0.13.0 (HIVE-6047).

DROP FUNCTION [IF EXISTS] function_name;

DROP returns an error if the function doesn ' t exist, unless if EXISTS is specified or the configuration variable hive.exec . Drop.ignorenonexistent is set to true.

[hive-languagemanual] create/drop/alter View create/drop/alter Index create/drop Function