hive的视图与索引的相关语法知识

1.views odds and ends
( the conceptual view still applies when the view and a query that uses it both contain an  ORDER BY clause or a  LIMIT clause. The view’s clauses are evaluated before the using query’s clauses.
For example, if the view has a LIMIT 100clause and the query has a LIMIT 200clause, you’ll get at most 100 results.)
CREATE VIEW IF NOT EXISTS shipments(time, part)
COMMENT 'Time and parts for shipments.'
TBLPROPERTIES ('creator' = 'me')
AS SELECT ...;
 (创建视图跟创建表一样，为了防止视图引用的表发生变化以至于视图出错，所以带上IF NOT EXISTS)
（You can also add a COMMENT for any or all of the new column names. The comments are not “inherited” from the definition of the original table.
Also, if the AS SELECT contains an expression without an alias—e.g.,  size(cols)(the number of items in  cols)—then Hive will use  _CN as the name, where  N is a number starting with 0. The view definition will fail if the AS SELECT clause is invalid.
Before the AS SELECT clause, you can also define TBLPROPERTIES, just like for tables.）
（Can also be used to copy a view, that is with a view as part of the LIKE expression:）
CREATE TABLE shipments2
LIKE shipments;
（You can also use the optional EXTERNAL keyword and LOCATION …clause, as before.）
--（A view is dropped in the same way as a table: As usual, IF EXISTS is optional.）
DROP VIEW IF EXISTS shipments;
（A view will be shown using SHOW TABLES(there is no SHOW VIEWS), however DROP TABLE cannot be used to delete a view.）
（As for tables, DESCRIBE shipments and DESCRIBE EXTENDED shipments displays the usual data  for  the  shipment view.  With  the  latter,  there  will  be  a  tableType value  in  the Detailed Table Information indicating the “table” is a VIRTUAL_VIEW.）
（Finally, views are read-only. You can only alter the metadata TBLPROPERTIESfor a view:
ALTER VIEW shipments SET TBLPROPERTIES ('created_at' = 'some_timestamp');）

注意：You cannot use a view as a target of an INSERT or LOAD command.

2.Index:
2.1创建索引：
需要被索引的表：
CREATE TABLE employees (
name  STRING,
salary  FLOAT,
subordinates ARRAY<STRING>,
deductions  MAP<STRING, FLOAT>,
address  STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);
创建的索引：
CREATE INDEX employees_index
ON TABLE employees (country)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' [or 'BITMAP']
WITH DEFERRED REBUILD
IDXPROPERTIES ('creator = 'me', 'created_at' = 'some_time')
IN TABLE employees_index_table
PARTITIONED BY (country, name)

COMMENT 'Employees indexed by country and name.';

2.2 重建索引：
ALTER INDEX employees_index
ON TABLE employees
PARTITION (country = 'US')
REBUILD;
显示索引：
SHOW FORMATTED INDEX ON employees;
删除索引：
DROP INDEX IF EXISTS employees_index ON TABLE employees;