apache hive - Hive - View and Indexes - hive tutorial - hadoop hive - hadoop hive - hiveql
What is a view in a database?
- A database view is a searchable object in a database that is defined by a query.
- Though a view doesn't store data, some refer to a views as “virtual tables,” you can query a view like you can a table.
- A view can combine data from two or more table, using joins, and also just contain a subset of information.
Learn Hive Tutorials - Hive View - Hive Example
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
What is an Index in DB?
- An index is a copy of selected columns of data from a table that can be searched very efficiently that also includes a low-level disk block address or direct link to the complete row of data it was copied from.
- Some databases extend the power of indexing by letting developers create indexes on functions or expressions.
Learn Hive Tutorials - Hive Multi Level Index - Hive Example
How to create and manage views:
- Views are generated based on user requirements.
- We can save any result set data as a view.
- The usage of view in Hive is same as that of the view in SQL. It is a standard RDBMS concept.
- We can execute all DML operations on a view.
Learn Hive Tutorials - Hive Create And Indexes - Hive Example
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
Creating a View
- To create a view at the time of executing a SELECT statement.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
Syntax
CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ]
[COMMENT table_comment]
AS SELECT ...
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
Example
- Assume wikitechy_employee table as given below, with the fields Id, Name, Salary, Designation, and Dept.
- Generate a query to retrieve the wikitechy_employee details who earn a salary of more than Rs 30000.
- We store the result in a view named emp_30000.
+------+--------------+-------------+-------------------+--------+
| 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 below query retrieves the wikitechy_employee details:
hive> CREATE VIEW emp_30000 AS
SELECT * FROM wikitechy_employee
WHERE salary>30000;
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
Dropping a View
- Use the DROP VIEW statement to drop views. Any views that are dependent on the view being dropped are made inoperative.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
Syntax
DROP VIEW view_name
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
- The following below query drops a view named as emp_30000:
hive> DROP VIEW emp_30000;
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
Creating an Index
- An Index is nothing but a pointer on a particular column of a table.
- Creating an index means creating a pointer on a particular column of a table.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
Syntax
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 (...)]
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
Example
- To Use the same wikitechy_employee table that we have used earlier with the fields Id, Name, Salary, Designation, and Dept.
- To create an index named index_salary on the salary column of the wikitechy_employee table.
- The following below query creates an index:
hive> CREATE INDEX inedx_salary ON TABLE wikitechy_employee(salary)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler';
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
- It is a pointer to the salary column.
- If the column is modified, the changes are stored using an index value.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
Dropping an Index
- To drop an index, the index must be contained in your schema, or you must have the DROP ANY INDEX system privilege.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
Syntax
DROP INDEX <index_name> ON <table_name>
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
- The following below query drops an index named index_salary:
hive> DROP INDEX index_salary ON wikitechy_employee;