(三)Hive的视图，分区表，分桶表，索引

Natasha_
1,481 阅读9分钟

1. 视图

1.1 简介

Hive 中的视图和 RDBMS 中视图的概念一致，都是一组数据的逻辑表示，本质上就是一条 SELECT 语句的结果集。

视图是纯粹的逻辑对象，没有关联的存储，当查询引用视图时，Hive 可以将视图的定义与查询结合起来。

1.2 创建视图

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

在 Hive 中可以使用 CREATE VIEW 创建视图，如果已存在具有相同名称的表或视图，则会抛出异常，建议使用 IF NOT EXISTS 预做判断。

在使用视图时候需要注意以下事项：

  • 视图是只读的，不能用作 LOAD / INSERT / ALTER 的目标；

  • 在创建视图时候视图就已经固定，对基表的后续更改将不会反映在视图；

  • 删除基表并不会删除视图，需要手动删除视图；

  • 视图可能包含 ORDER BY 和 LIMIT 子句，如果引用视图的查询语句也包含这类子句，其执行优先级低于视图对应字句。例如，视图 custom_view 指定 LIMIT 5，查询语句为 select * from custom_view LIMIT 10，此时结果最多返回 5 行。

  • 创建视图时，如果未提供列名，则将从 SELECT 语句中自动派生列名；

  • 创建视图时，如果 SELECT 语句中包含其他表达式，例如 x + y，则列名称将以c0，c1 等形式生成；

    CREATE VIEW  IF NOT EXISTS custom_view AS SELECT empno, empno+deptno , 1+2 FROM emp;

    https://github.com/heibaiying

1.3 查看视图

-- 查看所有视图：
show tables;
-- 查看某个视图
desc view_name;
-- 查看某个视图详细信息
desc formatted view_name;

1.4 删除视图

DROP VIEW [IF EXISTS] [db_name.]view_name;

删除视图时，如果被删除的视图被其他视图所引用，这时候程序不会发出警告，但是引用该视图其他视图已经失效，需要进行重建或者删除。

1.5 修改视图

ALTER VIEW [db_name.]view_name AS select_statement;

被更改的视图必须存在，且视图不能具有分区，如果视图具有分区，则修改失败。

1.6 修改视图属性

语法：

ALTER VIEW [db_name.]view_name SET TBLPROPERTIES (property_name = property_value, property_name = property_value, ...);

示例：

ALTER VIEW custom_view SET TBLPROPERTIES ('create'='heibaiying','date'='2019-05-05');

https://github.com/heibaiying

2. 分区表

2.1 概念

Hive 中的表对应为 HDFS 的目录，在查询数据时候，默认会对全表进行扫描，这样时间和性能的消耗都非常大。

分区为 HDFS 上目录的子目录，数据按照分区存储在子目录中。如果查询的 where 字句的中包含分区条件，则会直接从该分区去查找，而不是扫描整个表目录，合理的分区设计可以极大提高查询速度和性能。

实际上分区这个概念非常常见，比如在我们常用的 Oracle 数据库中，当表中的数据量不断增大，查询数据的速度就会下降，这时可以对表进行分区。

表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据存放到多个表空间（物理文件上），这样查询数据时，就不必要每次都扫描整张表，从而提升查询性能。

2.2 使用场景

通常，在管理大规模数据集的时候都需要进行分区，比如将日志文件按天进行分区，从而保证数据细粒度的划分，使得查询性能得到提升。

2.3 创建分区表

在 Hive 中可以使用 PARTITIONED BY 子句创建分区表。表可以包含一个或多个分区列，程序会为分区列中的每个不同值组合创建单独的数据目录。下面的我们创建一张雇员表作为测试：

 CREATE EXTERNAL TABLE emp_partition(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2)
    )
    PARTITIONED BY (deptno INT)   -- 按照部门编号进行分区
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_partition';

2.4 加载数据到分区表

加载数据到分区表时候必须要指定数据所处的分区：

# 加载部门编号为20的数据到表中
LOAD DATA LOCAL INPATH "/usr/file/emp20.txt" OVERWRITE INTO TABLE emp_partition PARTITION (deptno=20)

# 加载部门编号为30的数据到表中
LOAD DATA LOCAL INPATH "/usr/file/emp30.txt" OVERWRITE INTO TABLE emp_partition PARTITION (deptno=30)

2.5 查看分区目录

这时候我们直接查看表目录，可以看到表目录下存在两个子目录，分别是 deptno=20 和 deptno=30,这就是分区目录，分区目录下才是我们加载的数据文件。

# hadoop fs -ls  hdfs://hadoop001:8020/hive/emp_partition/

当查询语句的 where 包含 deptno=20，则会去对应的分区目录下进行查找，而不用扫描全表。

3. 分桶表

3.1 介绍

分区提供了一个通过隔离数据来优化查询的方案，但是并非所有的数据集都可以形成合理的分区，而且分区的数量也不是越多越好。过多的分区条件可能会导致很多分区上没有数据，同时 Hive 会限制动态分区可以创建的最大分区数，用来避免过多分区文件对文件系统产生负担。

鉴于以上原因，Hive 还提供了一种更加细粒度的数据拆分方案：分桶表 (bucket Table)。

分桶表会将指定列的值进行哈希散列，并对 bucket（桶数量）取余，然后存储到对应的 bucket（桶）中。

3.2 理解分桶表

对于 Java 开发人员而言，Hive 中的分桶概念和 Java 数据结构中的 HashMap 的分桶概念是一样的。

当调用 HashMap 的 put( ) 方法存储数据时，程序会先对 key 值调用 hashCode( ) 方法计算出哈希值，然后对数组长度取余计算出 index，最后将数据存储在数组 index 位置的链表上，链表达到一定阈值后会转换为红黑树 (JDK1.8+)：

https://github.com/heibaiying

3.3 创建分桶表

在 Hive 中，我们可以通过 CLUSTERED BY 指定分桶的列，并通过 SORTED BY 指定桶中数据的排序列：

  CREATE EXTERNAL TABLE emp_bucket(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno INT)
    CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS  --按照员工编号散列到四个 bucket 中
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_bucket';

3.4 加载数据到分桶表

直接使用 Load 语句向分桶表加载数据，数据是可以加载成功的，但是数据并不会分桶。

这是由于分桶的实质是对指定字段做了 hash 散列后，存放到对应文件中，这意味着向分桶表中插入数据是必然要通过 MapReduce，且 Reducer 的数量必须等于分桶的数量。由于以上原因，分桶表的数据通常只能使用 CTAS(CREATE TABLE AS SELECT) 方式插入，因为 CTAS 操作会触发 MapReduce。

加载数据步骤如下：

3.4.1 设置强制分桶

set hive.enforce.bucketing = true; --Hive 2.x 不需要这一步

3.4.2 CTAS导入数据

INSERT INTO TABLE emp_bucket SELECT *  FROM emp;  --这里的 emp 表就是一张普通的雇员表

可以从执行日志看到 CTAS 触发 MapReduce 操作，且 Reducer 数量和建表时候指定 bucket 数量一致：

https://github.com/heibaiying

3.5 查看分桶文件

bucket(桶) 本质上就是表目录下的具体文件：

https://github.com/heibaiying

4. 分区表和分桶表结合使用

分区表和分桶表的本质都是将数据按照不同粒度进行拆分，从而使得在查询时候不必扫描全表，只需要扫描对应的分区或分桶，从而提升查询效率。

两者可以结合起来使用，从而保证表数据在不同粒度上都能得到合理的拆分。

下面是 Hive 官方给出的示例：

CREATE TABLE page_view_bucketed(
    viewTime INT, 
    userid BIGINT,
    page_url STRING, 
    referrer_url STRING,
    ip STRING )
 PARTITIONED BY(dt STRING)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY '\001'
 COLLECTION ITEMS TERMINATED BY '\002'
 MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;

导入数据时需要指定分区：

INSERT OVERWRITE page_view_bucketed
PARTITION (dt='2009-02-25')
SELECT * FROM page_view WHERE dt='2009-02-25';

5. 索引

5.1 介绍

Hive 在 0.7.0 引入了索引的功能，索引的设计目标是提高表某些列的查询速度。如果没有索引，带有谓词的查询（如 'WHERE table1.column = 10'）会加载整个表或分区并处理所有行。但是如果 column 存在索引，则只需要加载和处理文件的一部分。

5.2 索引原理

在指定列上建立索引，会产生一张索引表（表结构如下），里面的字段包括：索引列的值、该值对应的 HDFS 文件路径、该值在文件中的偏移量。

在查询涉及到索引字段时，首先到索引表查找索引列值对应的 HDFS 文件路径及偏移量，这样就避免了全表扫描。

col_namedata_typecomment
empnoint建立索引的列
_bucketnamestringHDFS文件路径
_offsetsarray偏移量

5.3 自动使用索引

默认情况下，虽然建立了索引，但是 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;

5.4 创建索引

语法：

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"];                           --索引注释

示例：在 emp 表上针对 empno 字段创建名为 emp_index,索引数据存储在 emp_index_table 索引表中。

create index emp_index 
    on table emp(empno) as  
    'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' 
    with deferred rebuild 
    in table emp_index_table;

此时索引表中是没有数据的，需要重建索引才会有索引的数据。

5.5 查看索引

语法：

--显示表上所有列的索引
SHOW FORMATTED INDEX ON table_name;

示例：

SHOW INDEX ON emp;

https://github.com/heibaiying

5.6 删除索引

--删除索引会删除对应的索引表
DROP INDEX [IF EXISTS] index_name ON table_name;

5.7 重建索引

语法:

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

示例:

alter index emp_index on emp rebuild;

Hive 会启动 MapReduce 作业去建立索引，建立好后查看索引表数据如下：

三个表字段分别代表：索引列的值、该值对应的 HDFS 文件路径、该值在文件中的偏移量。

img

6、索引的缺陷

索引表最主要的一个缺陷在于：索引表无法自动 rebuild，这也就意味着——如果表中有数据新增或删除，则必须手动 rebuild，重新执行 MapReduce 作业，生成索引表数据。同时按照官方文档 说明，Hive 会从 3.0 开始移除索引功能。

avatar
文章
阅读
粉丝