-
Notifications
You must be signed in to change notification settings - Fork 14
/
HiveViewIndexes.md
54 lines (47 loc) · 1.07 KB
/
HiveViewIndexes.md
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
# Hive View and Indexes
## Creating a View
```
CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ]
[COMMENT table_comment]
AS SELECT ...
```
### Example
```
hive> CREATE VIEW emp_30000 AS
SELECT * FROM employee
WHERE salary>30000;
```
## Dropping a View
```
DROP VIEW view_name
hive> DROP VIEW emp_30000;
```
## Creating an Index
```
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 (...)]
```
### Example
```
hive> CREATE INDEX inedx_salary ON TABLE employee(salary)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD;
```
## Dropping an Index
```
DROP INDEX <index_name> ON <table_name>
hive> DROP INDEX index_salary ON employee;
```
## References
- [Hive - View and Indexes](https://www.tutorialspoint.com/hive/hive_views_and_indexes.htm)