hive数据仓库摘录和总结

Hive技术文档

——Author HuangFx  2013/01/29

Hive是什么？

Hive是蜂房的意思，为什么hadoop上的这层数据仓库叫Hive？

因为生物学上蜂房是一个结构相当精良的建筑，取名Hive足见则个数据仓库在数据存储上也是堪称精良的。Hive是Facebook开发的构建于Hadoop集群之上的数据仓库应用，它提供了类似于SQL语法的HQL语句作为数据访问接口，这使得普通分析人员的应用Hadoop的学习曲线变缓。

第一：Hive是建立在 Hadoop 上的数据仓库基础构架。

第二：很低的学习代价便可以让用户在Hadoop中进行存储、查询和分析存储的大规模数据。

简单的理解：如果用户只是需要完成大规模数据的分析这件事情，那么，你只要有一套hadoop环境+一个hive数据库，只要你懂SQL，你不必懂MapReduce程序如何编程、hadoop如何工作，你的SQL需求将自动被编译到整个集群中去进行分布式计算，以提高分析效率。

Hive是大数据的必然！

Facebook

Hive是Facebook开发的构建于Hadoop集群之上的数据仓库应用，它提供了类似于SQL语法的HQL语句作为数据访问接口，这使得普通分析人员的应用Hadoop的学习曲线变缓。至于Facebook为什么使用Hadoop和Hive组建其数据仓库，其内部人员分享了他们的一些经历，大致的过程是如下的：

1，Facebook的数据仓库一开始是构建于MySQL之上的，但是随着数据量的增加某些查询需要几个小时甚至几天的时间才能完成。

2，当数据量接近1T的时候，mysqld后台进程宕掉，这时他们决定将他们数据仓库转移到Oracle。当然这次转移的过程也是付出了很大的代价的，比如支持的SQL方言不同，修改以前的运行脚本等等。

3，Oracle应付几T的数据还是没有问题的，但是在开始收集用户点击流的数据（每天大约400G）之后，Oracle也开始撑不住了，由此又要考虑新的数据仓库方案。

4，内部开发人员花了几周的时间建立了一个并行日志处理系统Cheetah，这样的话勉强可以在24小时之内处理完一天的点击流数据。

5，Cheetah也存在许多缺点。后来发现了Hadoop项目，并开始试着将日志数据同时载入Cheetah和Hadoop做对比，Hadoop在处理大规模数据时更具优势，后来将所有的工作流都从Cheetah转移到了Hadoop，并基于Hadoop做了很多有价值的分析。

6，后来为了使组织中的多数人能够使用Hadoop，开发了Hive，Hive提供了类似于SQL的查询接口，非常方便。与此同时还开发了一些其它工具。

7，现在集群存储2.5PB的数据，并且以每天15TB的数据在增长，每天提交3000个以上的作业，大约处理55TB的数据...

现在很多大的互联网公司出于成本考虑都在研究、使用Hadoop；数据的价值正得到越来越多的人的重视，而这种重视，又体现出Hadoop存在的巨大价值。

淘宝

使用hive的原因

1 超大数据集设计的计算扩展能力

2 支持HiveSql查询——简单，学习代价低

3 统一的元数据管理

淘宝对hive的贡献

1 UDFs

2 建立、删除临时函数

3 GBK支持

4 完全JDBC

5 Multi Distinct Aggregation支持

6 认证和权限

7 bug fix

淘宝未来对hive的计划

1 Hive IDE

2 Multi Distinct Aggregation优化

3 Multi Group By优化

4 极限存储的索引和文件

5 表统计信息的支持

6 采用TFile做列存储

腾讯 

TDM团队在Hive上的工作

持续优化Hive的查询优化器，使SQL更加高效的运行；

不断引入一些database特性、包括hash join、列存储、多存储引擎、SQL/MED等，将成熟的数据库/并行数据库理论应用于Hive之上，使它的性能不断与并行数据仓库接近；

加强MapReduce层的优化，使MR执行框架更合理高效的执行Hive翻译的MR代码。

TDM团队主要的工作是优化hive的查询效率。

我司

金融数据信息的与日俱增，使得某些数据表变得极为庞大，这种大的规模程度用传统的关系型数据库查询语句处理大表的关联查询已经遇到了瓶颈。金融数据大表的出现对hive在数据存储中的应用与发展提供了先决条件。

　　 数据量（行）

    表1 2247667 224w

    表2 85201483 8520w

表3 28376850 2837w

表4 28735268 2873w

 

Hive的阶段进展

1 在hadoop的平台架构下，完成hive数据仓库与mysql、sqlserver以及其他关系型数据的数据迁移。

2 学习和研究HiveSql查询语句的优化。其实对于用户来说，或者说对于Hive程序员来说，优化HiveSql语句是整个工作的主要任务。

3 学习和研究Hive执行过程的优化，这是比较高级别的优化，需要深入了解Hive和hadoop的原理和过程。

 

Hive的基础架构

 

Hive的数据

元数据

（表相关信息）存在关系数据库中。

为什么要存在RDBMS中，获取元数据信息需要很低的延时，这在hdfs上是无法满足。

分析HiveSQL之后生成的MapReduce任务在运行的时候如果需要访问元数据信息时，它并不会直接去访问MetaStore。那么，他们是如何获得需要的元数据信息的呢？原来，当将生成的物理计划序列化到plan.xml的时候，已经将相应的元数据信息保存到了plan.xml中。而plan.xml文件之后会被放入Hadoop的分布式缓存中，所以MapReduce任务就可以从分布式缓存中获得需要的元数据信息。

数据

存在hdfs中。

Hive 没有专门的数据存储格式，也没有为数据建立索引，用户可以非常自由的组织 Hive 中的表，只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。

第一，不加外部链接创建的表，数据存储在hive-site.xml配置的${hive.metastore.warehouse.dir} 指定的数据仓库的目录下；数据需要load。

例子：

<property>

   <name>hive.metastore.warehouse.dir</name>

   <value>/user/hive/warehouse</value>

   <description>location of default database for the warehouse</description>

</property>

第二，加外部链接穿件的表，数据存在创建表时指定的hdfs路径下。

数据的存储关系

 Partition 对应于数据库中的 Partition 列的密集索引，但是 Hive 中 Partition 的组织方式和数据库中的很不相同。在 Hive 中，表中的一个 Partition 对应于表下的一个目录，所有的 Partition 的数据都存储在对应的目录中。例如：pvs 表中包含 ds 和 city 两个 Partition，则对应于 ds = 20090801, ctry = US 的 HDFS 子目录为：/wh/pvs/ds=20090801/ctry=US；对应于 ds = 20090801, ctry = CA 的 HDFS 子目录为；/wh/pvs/ds=20090801/ctry=CA

 Buckets 对指定列计算 hash，根据 hash 值切分数据，目的是为了并行，每一个 Bucket 对应一个文件。将 user 列分散至 32 个 bucket，首先对 user 列的值计算 hash，对应 hash 值为 0 的 HDFS 目录为：/wh/pvs/ds=20090801/ctry=US/part-00000；hash 值为 20 的 HDFS 目录为：/wh/pvs/ds=20090801/ctry=US/part-00020

 这些信息属于元数据信息，可以去mysql中查看。

 

Hive的执行原理

HiveSQL的数据类型

Primitive types:

· TINYINT

· SMALLINT

· INT

· BIGINT

· BOOLEAN

· FLOAT

· DOUBLE

· STRING

· BINARY(Note: Only available starting with Hive 0.8.0)

· TIMESTAMP(Note: Only available starting with Hive 0.8.0)

Complex types:

· arrays: ARRAY<data_type>

· maps: MAP<primitive_type, data_type>

· structs: STRUCT<col_name : data_type [COMMENT col_comment], ...>

· union: UNIONTYPE<data_type, data_type, ...>

符合类型的应用：

CREATE TABLE union_test(

foo UNIONTYPE<int, double, array<string>, struct<a:int,b:string>>);

SELECT foo FROM union_test;

{0:1}

{1:2.0}

{2:["three","four"]}

{3:{"a":5,"b":"five"}}

{2:["six","seven"]}

{3:{"a":8,"b":"eight"}}

{0:9}

{1:10.0}

HiveSQL和SQL

两者大同小异，在语法上基本一致，在数据类型等稍有差异，而这些QL将是用户与hive进行交互的直接语言！

查询语言	HQL	SQL
数据存储位置	HDFS	Raw Device 或者 Local FS
数据格式	用户定义	系统决定
数据更新	不支持	支持
索引	无	有
执行	MapRedcue	Executor
执行延迟	高	低
可扩展性	高	低
数据规模	大	小

 

https://cwiki.apache.org/confluence/display/Hive/LanguageManual

DDL：

CREATE TABLE IF NOT EXISTS pokes (foo INT, bar STRING);

 

CREATE TABLE u_data (   userid INT,   movieid INT,   rating INT,   unixtime STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;

 

SHOW TABLES;

ALTER TABLE pokes ADD COLUMNS (new_col INT);

DROP TABLE IF EXISTS pokes;

DML：

LOAD DATA  LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;

Load语句的使用注意Local和overwrite的选择。

Sql query：

hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(*) WHERE a.foo > 0 GROUP BY a.bar;   hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(*) FROM invites a WHERE a.foo > 0 GROUP BY a.bar; ROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(*) WHERE a.foo > 0 GROUP BY a.bar;   hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(*) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;

hive> FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo;

Hive的查询优化

载录 http://www.open-open.com/lib/view/open1341214750402.html

join优化

Join查找操作的基本原则：应该将条目少的表/子查询放在 Join 操作符的左边。原因是在 Join 操作的 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生内存溢出错误的几率。

Join查找操作中如果存在多个join，且所有参与join的表中其参与join的key都相同，则会将所有的join合并到一个mapred程序中。

案例：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)  在一个mapre程序中执行join

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)   在两个mapred程序中执行join

Map join的关键在于join操作中的某个表的数据量很小，案例：

SELECT /*+ MAPJOIN(b) */ a.key, a.value

  FROM a join b on a.key = b.key 

Mapjoin 的限制是无法执行a FULL/RIGHT OUTER JOIN b，和map join相关的hive参数：hive.join.emit.interval  hive.mapjoin.size.key  hive.mapjoin.cache.numrows

由于join操作是在where操作之前执行，所以当你在执行join时，where条件并不能起到减少join数据的作用；案例：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

  WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

最好修改为：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b

  ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

在join操作的每一个mapred程序中，hive都会把出现在join语句中相对靠后的表的数据stream化，相对靠前的变的数据缓存在内存中。当然，也可以手动指定stream化的表：SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

group by 优化

Map端聚合，首先在map端进行初步聚合，最后在reduce端得出最终结果，相关参数：

· hive.map.aggr = true是否在 Map 端进行聚合，默认为 True

· hive.groupby.mapaggr.checkinterval = 100000在 Map 端进行聚合操作的条目数目

数据倾斜聚合优化，设置参数hive.groupby.skewindata = true，当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

合并小文件

文件数目过多，会给 HDFS 带来压力，并且会影响处理效率，可以通过合并 Map 和 Reduce 的结果文件来消除这样的影响：

· hive.merge.mapfiles = true是否和并 Map 输出文件，默认为 True

· hive.merge.mapredfiles = false是否合并 Reduce 输出文件，默认为 False

· hive.merge.size.per.task = 256*1000*1000合并文件的大小

Hive实现(not) in

通过left outer join进行查询,（假设B表中包含另外的一个字段 key1 

select a.key from a left outer join b on a.key=b.key where b.key1 is null

通过left semi join 实现 in

SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

Left semi join 的限制：join条件中右边的表只能出现在join条件中。

排序优化

Order by 实现全局排序，一个reduce实现，效率低

Sort by 实现部分有序，单个reduce输出的结果是有序的，效率高，通常和DISTRIBUTE BY关键字一起使用（DISTRIBUTE BY关键字 可以指定map 到 reduce端的分发key）

CLUSTER BY col1 等价于DISTRIBUTE BY col1 SORT BY col1

使用分区

Hive中的每个分区都对应hdfs上的一个目录，分区列也不是表中的一个实际的字段，而是一个或者多个伪列，在表的数据文件中实际上并不保存分区列的信息与数据。Partition关键字中排在前面的为主分区（只有一个），后面的为副分区

静态分区：静态分区在加载数据和使用时都需要在sql语句中指定

          案例：(stat_date='20120625',province='hunan')

动态分区：使用动态分区需要设置hive.exec.dynamic.partition参数值为true，默认值为false，在默认情况下，hive会假设主分区时静态分区，副分区使用动态分区；如果想都使用动态分区，需要设置set hive.exec.dynamic.partition.mode=nostrick，默认为strick

          案例：(stat_date='20120625',province)

Distinct 使用

Hive支持在group by时对同一列进行多次distinct操作，却不支持在同一个语句中对多个列进行distinct操作。

Hql使用自定义的mapred脚本

注意事项：在使用自定义的mapred脚本时，关键字MAP REDUCE 是语句SELECT TRANSFORM ( ... )的语法转换，并不意味着使用MAP关键字时会强制产生一个新的map过程，使用REDUCE关键字时会产生一个red过程。

自定义的mapred脚本可以是hql语句完成更为复杂的功能，但是性能比hql语句差了一些，应该尽量避免使用，如有可能，使用UDTF函数来替换自定义的mapred脚本

UDTF

UDTF将单一输入行转化为多个输出行，并且在使用UDTF时，select语句中不能包含其他的列，UDTF不支持嵌套，也不支持group by 、sort by等语句。如果想避免上述限制，需要使用lateral view语法，案例：

select a.timestamp, get_json_object(a.appevents, '$.eventid'), get_json_object(a.appenvets, '$.eventname') from log a;

select a.timestamp, b.*

from log a lateral view json_tuple(a.appevent, 'eventid', 'eventname') b as f1, f2;

其中，get_json_object为UDF函数，json_tuple为UDTF函数。

UDTF函数在某些应用场景下可以大大提高hql语句的性能，如需要多次解析json或者xml数据的应用场景。

聚合函数count和sum

Count和sum函数可能是在hql语句中使用的最为频繁的两个聚合函数了，但是在hive中count函数在计算distinct value时支持加入条件过滤。