【Hive笔记】 二、表、视图和索引

数据表

数据属于互联网公司的核心资产之一, 但是随着时间, 数据不断膨胀, 如何优化数据表从而平衡空间和时间的开销.本部分将介绍 Hive 表支持的分区表和分桶表思路.

两种手段既可以单独使用, 也可结合使用.

分区表

分区表: 利用特定字段针对数据进行分区(分类), 例如对于采集日志数据量巨大按照 date/hour 等粒度分类, 从而在针对单日某小时数据进行离线计算节省扫描时间.多重分区在 HDFS 上展现为多层文件夹.

-- 创建分区表
CREATE EXTERNAL TABLE tmp.sdk_log (
    log_id BIGINT,
    log_data STRING
)
PARTITIONED BY (
    date_pt STRING,
    hour_pt STRING
)   -- 按照采集日期/小时两层分区
ROW FORMAT DELIMITED    -- 内容格式
    FIELDS TERMINATED BY '\001'
    COLLECTION ITEMS TERMINATED BY '\002'
    MAP KEYS TERMINATED BY '\003'
STORED AS ORC           -- 存储格式
LOCATION '/hive/tmp/log';
-- 加载数据到分区表
LOAD DATA LOCAL INPATH "/log/log_20220219_15.txt" OVERWRITE INTO TABLE tmp.sdk_log PARTITION (date_pt=20220219, hour_pt=15)

分桶表

分桶表: 分区虽然提供一个可行方案, 但分区字段的选择尤为关键, 并且若产生过多分区小文件则会对 Hadoop 集群造成压力. Hive 借鉴字典(Map)中的分桶思路构建分桶表.

-- 创建分桶表
CREATE EXTERNAL TABLE tmp.cust_allinfo (
    cust_id BIGINT,
    cust_detail STRING
)
CLUSTERED BY (
    cust_id
)
SORTED BY (
    cust_id ASC
) INTO 4 BUCKETS
ROW FORMAT DELIMITED    -- 内容格式
    FIELDS TERMINATED BY '\001'
    COLLECTION ITEMS TERMINATED BY '\002'
    MAP KEYS TERMINATED BY '\003'
STORED AS ORC           -- 存储格式
LOCATION '/hive/tmp/log';
-- 加载数据到分桶表
SET hive.enforce.bucketing = true; -- Hive 2.x 不需要这一步
INSERT INTO TABLE tmp.cust_allinfo SELECT * FROM cust_sub;

视图

视图: 与关系型数据库(RDBMS)中视图概念一致, 表征一段查询语句的结果集. 并不提供持久化.

创建视图

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name   -- 视图名称
    [(column_name [COMMENT column_comment], ...) ]    --列名
    [COMMENT view_comment]  --视图注释
    [TBLPROPERTIES (property_name = property_value, ...)]  --额外信息
AS SELECT ...;

视图操作

-- 查看所有视图: 没有单独查看视图列表的语句, 只能使用 show tables
SHOW TABLES;
-- 查看某个视图
DESC view_name;
-- 查看某个视图详细信息
DESC FORMATTED view_name;
-- 删除视图
DROP VIEW [IF EXISTS] [db_name.]view_name;
-- 修改视图内容
ALTER VIEW [db_name.]view_name AS select_statement;
-- 修改视图属性
ALTER VIEW [db_name.]view_name SET TBLPROPERTIES table_properties;

索引

索引: 在0.7.0中引入, 从而优化针对特定索引列的限制操作, 减少查询开销.

创建索引

CREATE INDEX index_name     -- 索引名称
    ON TABLE base_table_name (col_name, ...)  -- 建立索引的列
    AS index_type    -- 索引类型
[WITH DEFERRED REBUILD]    -- 重建索引
[IDXPROPERTIES (property_name=property_value, ...)]  -- 索引额外属性
[IN TABLE index_table_name]    -- 索引表的名字
[
    [ ROW FORMAT ...] STORED AS ...
    | STORED BY ...
]   -- 索引表行分隔符 、 存储格式
[LOCATION hdfs_path]  -- 索引表存储位置
[TBLPROPERTIES (...)]   -- 索引表表属性
[COMMENT "index comment"];  -- 索引注释

索引操作

-- 显示表上所有列的索引
SHOW FORMATTED INDEX ON table_name;
-- 删除索引
DROP INDEX [IF EXISTS] index_name ON table_name;
-- 重建索引
ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

使用索引

默认情况下, 虽然建立了索引, 但是 Hive 在查询时候是不会自动去使用索引的, 需要开启相关配置.开启配置后, 涉及到索引列的查询就会使用索引功能去优化查询.

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
SET hive.optimize.index.filter=true;
SET hive.optimize.index.filter.compact.minsize=0;

总结

索引表缺陷在于无法自动重建, 需要手动重新执行作业, 在 3.0 开始移除该功能

  • 具备自动重写的物化视图可以产生于索引相似的效果(Hive 2.3增加物化视图支持, 3.0正式引入)
  • 列式存储格式(ORC/Parquet)存储时, 支持选择性扫描

参考资料

大数据框架
#Hive
【Hive笔记】 二、表、视图和索引
https://www.windism.cn/1585770774.html
作者
windism
发布于
2022年2月17日
许可协议