This is the last post in the Hive series, I will release some more about HiveQL soon, everyone follow along.
View
This article describes how to create and manage views. Views are created based on user requests. You can save any result set data as a view. Using view in Hive is like in SQL. This is a standard RDBMS concept. We can perform all DML operations on one view.
Like in SQL, a VIEW view is a virtual table in a database whose content is defined through a certain SQL statement. A VIEW consists of rows and columns just like a real table. The fields in a view are fields from one or more actual tables in the Database.
The difference between a VIEW view and a table is that VIEW is not considered a data storage structure that exists in a database. In essence, the data observed in VIEW is retrieved from tables via data query statements and is used to restrict database access or to hide complex data.
Create view
You can create a view at the time of executing a SELECT statement. The syntax is as follows:
1 2 3 4 | CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ] [COMMENT table_comment] AS SELECT ... |
For example
Let’s get an example view. Assume the employee table is given below, with the fields Id, Name, Salary, Designation and Dept. Create a query to get details about employee salaries higher than Rs 30,000. We store the result in a view named emp_30000
1 2 3 4 5 6 7 8 9 10 | +------+--------------+-------------+-------------------+--------+ | ID | Name | Salary | Designation | Dept | +------+--------------+-------------+-------------------+--------+ |1201 | Gopal | 45000 | Technical manager | TP | |1202 | Manisha | 45000 | Proofreader | PR | |1203 | Masthanvali | 40000 | Technical writer | TP | |1204 | Krian | 40000 | Hr Admin | HR | |1205 | Kranthi | 30000 | Op Admin | Admin | +------+--------------+-------------+-------------------+--------+ |
The following query retrieves details of the employee using the above scenario:
1 2 3 4 | hive> CREATE VIEW emp_30000 AS SELECT * FROM employee WHERE salary>30000; |
Delete view
Use the following syntax to delete a view:
1 2 | DROP VIEW view_name |
The following query will delete a view named emp_30000:
1 2 | hive> DROP VIEW emp_30000; |
Index
Create the index
An index is nothing but a pointer on a specific column of the table. Creating an index means creating a cursor on a specific column of the table. Its syntax is as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE INDEX index_name ON TABLE base_table_name (col_name, ...) AS 'index.handler.class.name' [WITH DEFERRED REBUILD] [IDXPROPERTIES (property_name=property_value, ...)] [IN TABLE index_table_name] [PARTITIONED BY (col_name, ...)] [ [ ROW FORMAT ...] STORED AS ... | STORED BY ... ] [LOCATION hdfs_path] [TBLPROPERTIES (...)] |
For example
Let’s take an example of an index. Use the same employee table as above with the fields Id, Name, Salary, Designation, Dept. Create an index named index_salary on the salary column of the employee table.
The following query creates an index:
1 2 3 | hive> CREATE INDEX index_salary ON TABLE employee(salary) AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'; |
It is a pointer on the salary column. If the column is changed, the change is stored using an index value.
Delete the index
The following syntax is used to delete the index named index_salary:
1 2 | DROP INDEX <index_name> ON <table_name> |
The following query deletes an index named index_salary:
1 2 | hive> DROP INDEX index_salary ON employee; |