Hive学习笔记(4)

Hive学习笔记

笔记内容主要来自Hive编程指南

HiveQL:视图

Hive视图是一个逻辑结构，因为它不像一个表会存储数据。Hive目前暂不支持物化视图。

使用视图来降低查询复杂度

-- Hive查询语句中多层嵌套
FROM(
    SELECT * FROM people JOIN cart
    ON (cart.people_id=people.id) WHERE firstname='john'
) a SELECT a.lastname WHERE a.id=3;

-- 嵌套子查询变成了一个视图
CREATE VIEW shorter_join AS
SELECT * FROM people JOIN cart
ON (cart.people_id=people.id) WHERE firstname='john';

-- 现在就可以像操作表一样来操作这个视图
SELECT lastname FROM shorter_join WHERE id=3;

使用视图来限制基于条件过滤的数据

-- Hive通过创建视图来限制数据访问可以用来保护信息不被随意查询

CREATE TABLE userinfo(firstname string, lastname string, ssn string, password string);
-- 通过视图隐藏了ssn和password
CREATE VIEW safer_user_info AS 
SELECT firstname, lastname FROM userinfo;

动态分区中的视图和 map 类型

示例数据

CREATE EXTERNAL TABLE dynamictable(cols map<string,string>)
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\004'
    COLLECTION ITEMS TERMINATED BY '\001'
    MAP KEYS TERMINATED BY '\002'
STORED AS TEXTFILE;

--可以创建一个视图取出 type值 等于 request 的 city,state和part 3个字段。
CREATE VIEW orders(state, city, part) AS
SELECT cols["state"], cols["city"], cols["part"]
FROM dynamictable
WHERE cols["type"] = "request";

-- 可以加where限制条件
CREATE VIEW shipments(time, part) AS
SELECT cols["time"], cols["part"]
FROM dynamictable
WHERE cols["type"] = "response";

视图零碎

-- 创建视图
CREATE VIEW IF NOT EXISTS shipments(time, part)
COMMENT 'Time and parts for shipments.'
TBLPROPERTIES('creator'='me')
AS SELECT ...;

--放弃视图
DROP VIEW IF EXISTS shipments;

--更新
ALTER VIEW shipments SET TBLPROPERTIES('created_at'='some_timestamp');

HiveQL:索引

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);

-- 下面仅对分区字段country 建立索引
CREATE INDEX employees_index
ON TABLE employees (country)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
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.';


--Bitmap索引普遍应用于排重后值较少的列
CREATE INDEX employees_index
ON TABLE employees (country)
AS '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.';

重建索引

-- 如果用户指定了 DEFERRED REBUILD,那么新索引将呈现空白状态。在任何时候，都可以进行第一次索引创建或者使用 ALTER INDEX 对索引进行重建
ALTER INDEX employees_index
ON TABLE employees
PARTITION (country='US')
REBUILD;
-- 如果省略掉PARTITION，那么将会对所有分区进行重建索引
--在工作流可以对对应的索引执行重建索引语句ALTER INDEX...REBUILD

显示索引

-- 显示对于这个索引表对所有列所建立的索引
SHOW FORMATTED INDEX ON employees;

-- INDEXS 列举出多个索引信息
SHOW FORMATTED INDEXS ON employees;

删除索引

-- 删除一个索引将会删除这个索引表
DROP INDEX IF EXISTS employees_index ON TABLE employees;

实现一个定制化的索引处理器

Hive Wiki 页面具有实现一个定制化的索引处理器的完整的例子，链接是 https://cwiki.apache.org/confluence/display/Hive/IndexDev#CREATE_INDEX 其中还包括了索引的初步设计文档。

---