在Hive中使用索引[译]

本文重点介绍Hive中索引所涉及的概念。包括以下主题:

  • 什么时候使用索引
  • Index的作用
  • 在表中创建索引
  • 对Hive中的索引执行一些操作

什么是索引

索引是对特定记录的引用(比如一列或多列),而不是搜索所有记录,我们可以使用索引来查询特定记录,索引可以自动维护,使得我们可以以最小的开销进行记录搜索。同时索引加速了数据的搜索。

为什么使用索引

Hive是Hadoop平台的一个顶层数据仓库工具,它提供了SQL类型的接口来对大型数据集执行查询。由于Hive处理大数据,文件自然很大,可以跨越到TB和PB级。我们对这么大量的数据执行任何操作或查询,都需要大量的时间。

在Hive表中,有许多行和列。如果我们只想对某些列执行查询而不建立索引,则需要大量的时间,因为查询将对表中的所有列执行。

使用索引的主要优点是每当我们对具有索引的表执行查询时,查询不需要扫描表中的所有行。此外,它首先检查索引,然后转到特定列并执行操作。

因此,如果我们使用索引,Hive查询将先查看索引,然后在更少的时间内执行所需的操作。当然我们最关注的也就是时间。

什么时候使用索引

索引可以在以下场景使用:

  • 如果数据量非常大
  • 如果查询执行的时间比您预期的更多。
  • 如果需要快速查询。
  • 构建数据模型。

索引保存在Hive中的单独表中,它不会影响原表中的数据。在Hive中索引的另一个主要优点是索引也可以根据我们拥有的数据大小进行分区。

Hive中索引的类型

  • Compact Indexing
  • Bitmap Indexing

Bitmap索引在Hive 0.8中引入,通常用于较少值的情况。

Compact和Bitmap索引的区别

主要区别是存储不同块中的行的映射值。当Hive表中的数据默认存储在HDFS中时,它们分布在集群中的节点上。需要正确识别数据,如块索引中的数据。此数据将能够识别哪个块存在于哪个块中,以便在触发查询时,它可以直接进入该块。因此,在执行查询时,它首先检查索引,然后直接进入该块。

Compact索引存储索引列的值和其blockid(译者注:紧凑索引存储每个值的HDFS块号,而不是存储文件内偏移量。因此存储不会占用过多的磁盘空间,且对于值被聚簇(clustered)存储于相近行的情况,索引仍然有效。)
Bitmap索引将索引列值和行列表存储为bitmap。(译者注:位图索引使用压缩的位集合(bitset)来高效存储具有某个特殊值的行。这种索引一般适合于具有较少取值可能(low-cardinality)的列(如性别或国别))

什么是bitmap

bitmap是用于图像文件格式的类型,我们重新定义bitmap索引:“Bitmap索引将值和行列表的组合存储为数字图像。“

以下是可以对Hive索引执行的操作:

  • 创建索引
  • 现实索引
  • 修改索引
  • 删除索引

创建索引

创建compact索引的语法如下:

1
2
3
4
CREATE INDEX index_name
ON TABLE table_name (columns,....)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD;
  • 将其中的index_name替换为你要创建索引的名字
  • 在ON TABLE行,把table_name替换为你要创建的索引的表的表名,columns里面制定你需要创建索引的列。
  • ‘org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’行指定一个内置CompactIndexHandler类用于创建的索引。
  • 如果你指定了DEFERED REBUILD,那么一开始这个index是空的。我们可以用ALTER INDEX语句rebuild它。

此语法将为我们的表创建索引,但是index是空的,我们对其进行REBUILD。为了执行这样的操作,我们需要使用alter命令,这会启动一个MapReduce作业。

1
ALTER INDEX index_nam on table_name REBUILD

这个Alter语句会完成REBUILDED索引

举例-创建索引

在本节中,我们将首先对非索引表执行hive查询,并记下所花费的时间。

在第二部分中,我们将对索引表执行相同的查询,然后将比较两次的查询时间。

让我们使用olympic数据集创建表

创建表格

1
create table olympic(athelete STRING,age INT,country STRING,year STRING,closing STRING,sport STRING,gold INT,silver INT,bronze INT,total INT) row format delimited fields terminated by '\t' stored as textfile;

这里我们创建了名为olympic的表指定为textfile格式。
你可以通过describe olympic命令查看该表的Schema
接着往表格中倒入数据

1
load data local inpath ‘path of your fileinto table olympic;

creating_table

执行查询:

SELECT AVG(age) from olympic;

Avg_operation_olympic_table

从上图中我们看到总用时26.405433646812956,其中执行处理使用了21.08秒

现在,让我们为该表创建索引

1
2
3
4
CREATE INDEX olympic_index
ON TABLE olympic (age)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD;

creating_compact_index

ALTER INDEX olympic_index on olympic REBUILD;

rebuild_index

我们已经在olympic表的age字段建立索引,通过以下命令可以查看

show formatted index on olympic;

display_index

让我们看下同样操作所花费的时间

Avg_operation_compact_indexed_table

现在总时间为26.405433646812956,计算时间为17.26之前的例子少。

这是减少查询时间的例子。

是否可以在同一个表上使用不同的索引

我们可以在表上建立多个索引并且不限类型。 让我们在该表上建立一个Bitmap索引

1
2
3
4
5
CREATE INDEX olympic_index_bitmap
ON TABLE olympic (age)
AS 'BITMAP'
WITH DEFERRED REBUILD;
ALTER INDEX olympic_index_bitmap on olympic REBUILD;

这里As ‘BITMAP’定义索引的类型为BITMAP

creating_bitmap_index

查看索引结构

1
SHOW FORMATTED INDEX ON employee;

display_indexes

现在有两个索引,显示如上

在两个索引上执行平均操作

现在让在这两个索引上执行同样的平均查询语句

Avg_operation_with_two_indexes

与compact索引同样的结果 17.614 秒。为什么会这样呢?

Note: 如果在同一个表的同一个字段上有两种类型的索引,先建立的索引会用于查询。

现在我们通过以下语句删除一个索引

1
DROP INDEX IF EXISTS olympic_index ON olympic;

如下图我们删除了compact索引
delete_index

在Bitmap索引上执行查询

Avg_operation_bitmap_index

我们现在获得的执行时间为 26.105433646812956秒,但是执行时间为16.47 秒,低于以上两个例子

通过以上的案例,总结如下:(译者注:我用的Parquet格式,并未发现索引有毛作用=。=)

  • 索引减少了查询执行的时间。
  • 同一个表上可以建立多个索引。
  • 我们可以根据数据创建需要的索引类型
  • 在一些场景中Bitmap索引要比Compact索引快,反之亦然。

Tuning(译者附加)

1.显示执行计划

1
2
3
4
EXPLAIN select sum(number) from coltab;
--加上EXTENDED关键字显示更详细的执行计划
EXPLAIN EXTENDED select from coltab;

什么时候不用索引?

不符合以下场景的情况请不要使用索引

  • 建议索引建立在您经常执行操作的列上。
  • 构建过多索引会降低查询的性能。
  • 请根据你的数据来选择索引类型,如果你的数据需要Bitmap,那就不应该用compact。这将导致执行查询的时间增加。

原文:Indexing in Hive

参考

Hive SQL执行计划深度解析
hive使用本地模式
Indexing in Hive

打赏支持:支付宝/微信。如果你觉得我的文章对你有所帮助,可以打赏我哟。