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');)
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)
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;
( 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.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.';
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;