Hive 基础知识_辰阳天宇_hive基础知识

网络投稿 02-07 2298

1. Hive和HBase的区别

HBase是一个用来处理HDFS上文件的NoSQL数据库，建立于HDFS之上。 Hive是封装了MapReduce的操作，让用户可以通过写sql语句的方式，实现MapReduce操作。 Hive基于一个统一的查询分析层，通过SQL语句的方式对HDFS上的数据进行查询、统计和分析。

2. Hive是什么

（1）Hive是一个SQL解析引擎，将SQL语句转译成MR Job，然后再Hadoop平台上运行，达到快速开发的目的。（2）Hive中的表示纯逻辑表，只有表的定义等，即表的元数据（存储于MySQL中）。本质就是Hadoop的目录/文件，这种设计方式实现了元数据与数据存储分离。（3）Hive本身不存储数据，它完全依赖HDFS和MapReduce。（4）Hive中没有定义专门的数据格式，需要由用户指定三个属性：

列分割符：空格、\t、\001行分隔符：\n读取文件数据的方法：TextFile（可读性好、但数据不压缩占据空间比较大）、SequenceFile（hadoop提供的一种二进制文件，通过Writable接口以<key,value>形式序列化到文件中）、RCFile(Hive专门推出的，用于面向列的格式) 3. Hive中的SQL与传统SQL区别

可扩展性：

Hive可以自定义一些函数进行扩展（1）UDF：

直接应用于select语句。通常查询的时候，需要对数据字段做一些格式化的处理。例如：大小写转换。特点：一进一出，一对一的关系\

（2）UDAF：

直接应用于group by语句。特点：多对一的关系 \

（3）UDTF：

d特点：一对多的关系数据检查

读时模式： Hive只有在读的时候才会检查、解析字段和schema。优点是在写的过程中不需要解析数据，从而load data非常迅速。缺点是读的时候很慢。写时模式：传统的SQL只有在写的时候才会建立索引、压缩、数据一致性、字段检查等等。优点是读的时候会得到优化，读很快。缺点是写的时候很慢。

Hive与传统关系数据特点比较 hive 和关系数据库存储文件的系统不同，Hive使用的是Hadoop的HDFS（Hadoop的分布式文件系统），关系数据库则是服务器本地的文件系统；Hive使用的计算模型是MapReduce，而关系数据库则是自己设计的计算模型关系数据库都是为实时查询的业务进行设计的，而Hive则是为海量数据做数据挖掘设计的，实时性很差Hive继承于Hadoop，从而拥有很容易扩展自己的存储能力和计算能力，而关系数据库在这方面比数据库差很多。 4. Hive生态架构

（1）用户接口

CLI：用户接口——Client终端。启动时，会同时启动一个Hive副本 Client： Hive的客户端，用户连接至Hive Server GUI：通过浏览器访问Hive

（2）语句转换

Driver：语句转换——sql->MapReduce，Hive自身不会生成MR，而是通过一个执行计划来去执行MR（XML文件-》MR。 Compiler（解析器）：生成抽象语法树语法分析器：验证查询语句逻辑计划生成器（包括优化器）：生成操作符树查询计划生成器：转换为MapReduce任务

（3）数据存储

元数据： Hive 的元数据信息通常存储在关系型数据库中,常用MySQL数据库作为元数据库管理。生成文件： Hive数据以文件形式存储在HDFS的指定目录下面。查询计划：Hive语句生成查询计划，由MapReduce调用执行。默认本地数据库：derby(单用户模式)、MySQL(多用户模式、远程服务模式)

5. Hive数据管理

Hive的表的本质就是Hadoop的目录/文件。 Hive默认表存放路径一般都是在你工作目录的Hive目录里面，按表名做文件夹分开。如果有你分区表的话，分区值是子文件夹，可以直接在其他的M/R job里直接应用这部分数据。 Hive支持4个数据模型。

数据表

Table（内部表）：创建表的结构和数据，删除表的时候时会将表的元数据和数据都删掉。 External Table（外部表）：仅创建表的结构，删除外部表时仅删除表的结构，数据不会被删掉。

分区表和分桶表

Partition：可以按照自定义的属性进行分类，类似于Reduce的概念分成一个一个桶。一个表可以有多个分区，表中的要给Partition分区对应于表下的一个目录，所有的Partition的数据都存储再对应的目录中。Partition用于辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。 Bucket ：Hive会针对某一个列进行桶的组织，通常对列值做Hash。类似于mysql中的分库，将表内的数据均匀分布到指定大小的各个子表中。采集数据时，可以仅查看子表内的数据来了解数据情况，方便采样。使用Join的时候会自动激活Map端的map-sdie Join，方便了使用Join，从而实现了优化查询。

TypeNameHDFS DirectoryTablemobile_user/lbs（数仓名字）/mobile_userPartitionaction（字段）=insight, day = 20130220/lbs/mobile_user/action=insight/day = 20130220Bucketclusted by user into 32 buckets/lbs/mobile_user/action=insight/day = 20130220/part-00000

6. Hive内部表和外部表

Hive用create创建表的时候，选择创建方式：

create table：默认创建内部表，此表将会创建一个与数据相关联的表。内部表数据由Hive自身管理，若用户删除内部表，则存储数据也会被删除。create external table：创建外部表，此表仅创建一个表结构，不与数据相关联。外部表数据由HDFS管理，若删除外部表仅会删除hive表的结构信息，不会删除存储的数据。 7. Hive中的Bucket hive中table可以拆分成partition，table和partition可以通过‘CLUSTERED BY ’进一步分bucket，bucket中的数据可以通过‘SORT BY’排序。create table bucket_user (id int,name string)clustered by (id) into 4 buckets;‘set hive.enforce.bucketing = true’ 可以自动控制上一轮reduce的数量从而适配bucket的个数，当然，用户也可以自主设置mapred.reduce.tasks去适配 bucket个数。数据sampling – 提升某些查询操作效率，例如mapside join。查看sampling数据： – hive> select * from student tablesample(bucket 1 out of 2 on id); – tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) – y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32 时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据 8. Hive数据类型

9. Hive的优化

Hive的本质实际上就是MapReduce

在hive的执行语句当中的执行查询的顺序，例如一条sql:

select … from … where … group by … having … order by …

执行顺序： from … where … select … group by … having … order by … 其实总结hive的执行顺序也是总结mapreduce的执行顺序：MR程序的执行顺序： map阶段： 1.执行from加载，进行表的查找与加载 2.执行where过滤，进行条件过滤与筛选 3.执行select查询：进行输出项的筛选 4.map端文件合并：map端本地溢出写文件的合并操作，每个map最终形成一个临时文件，然后按列映射到对应的。

Reduce阶段： 1.group by：对map端发送过来的数据进行分组并进行计算。 2.select：最后过滤列用于输出结果 3.order by 排件

所以通过上面的例子我们可以看到，在进行select之后我们序后进行结果输出到HDFS文会形成一张表，在这张表当中做分组排序这些操作。

先来看一看Map的优化

Map的优化

Reduce的优化

select pt, count(1) from popt_tbaccountcopy_mes where pt = '2012-07-0' group by pt;

group by pt：将数据按pt聚合 where pt = '2012-07-0'：查询出pt = '2012-07-0'的数据 count(1)：每个查询到的数据计数最终得到的是将pt = '2012-07-0'的数据进行聚合为key，并将计数求和作为value。若改为

select count(1) from popt_tbaccountcopy_mes where pt = '2012-07-0';

最终也是实现计数求和功能，但只在一个Reducer上运行。

分区裁剪（Partition） Where中的分区条件，会提前生效，不必特意做子查询，直接Join和GroupBy。

笛卡尔积 join的时候不加on条件或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积。

Map join /*+ MAPJOIN(tablelist) */，必须是小表，不要超过1G，或者50万条记录。

Union all 先做union all再做join或group by等操作可以有效减少MR过程，尽管是多个Select，最终只有一个 mr。

Multi-insert & multi-group by – 从一份基础表中按照不同的维度，一次组合出不同的数据

FROM from_statement INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1)] select_statement1 group by key1 INSERT OVERWRITE TABLE tablename2 [PARTITION (partcol2=val2)] select_statement2 group by key2

Automatic merge

当文件大小比阈值小时，hive会启动一个mr进行合并。hive.merge.mapfiles = true 是否和并 Map 输出文件，默认为 True。hive.merge.mapredfiles = false 是否合并 Reduce 输出文件，默认为 False。hive.merge.size.per.task = 25610001000 合并文件的大小。

Multi-Count Distinct

必须设置参数：set hive.groupby.skewindata=true;select dt, count(distinct uniq_id), count(distinct ip)from ods_log where dt = 20170301 group by dt 10. Hive的Join优化（1）一个和多个MR 生成一个MR job SELECT a.val, b.val, c.val \ FROM a \ JOIN b ON (a.key = b.key1) \ JOIN c ON (a.key = c.key1) 生成多个MR job SELECT a.val, b.val, c.val \ FROM a \ JOIN b ON (a.key = b.key1) \ JOIN c ON (c.key = b.key1)

MR越多耗时时间会越长，优化目标是让MR越少越好。

（2）Join优化——表连接顺序

在对两个表做join时，如果一个表是小表、另一个表是大表的时候，可以提前把这个表放到内存里去，可以提升处理性能。按照JOIN顺序中的最后一个表应该尽量是大表，因为JOIN前一阶段生成的数据会存在于 Reducer的buffer中，通过stream最后面的表，直接从Reducer的buffer中读取已经缓冲的中间结果数据（这个中间结果数据可能是JOIN顺序中，前面表连接的结果的Key，数据量相对较小，内存开销就小），这样，与后面的大表进行连接时，只需要从buffer中读取缓存的Key，与大表中的指定Key进行连接，速度会更快，也可能避免内存缓冲区溢出。例如，在执行select x,y from A join B时， hive会默认左边为小表，将数据放到内存，右边为大表，将数据做类似流数据。

通过参数也可以指定哪个表被视为大表，那个表被视为小表。指定小表

SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a JOIN b ON a.key = b.key;

加入参数/*+ MAPJOIN(b) */，可将b视为小表。 MAPJION会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map是进行了join操作，省去了 reduce运行的效率也会高很多。指定大表

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

加入参数/*+ STREAMTABLE(a) */，可将a视为大表，尝试将b和c视为小表进行缓存。左连接时，左表中出现的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'

这种方式是先进行JOIN在进行WHERE筛选。这样在JOIN过程中可能会输出大量结果，再对这些结果进行过滤，比较耗时。

ASELECT a.val, b.val FROM a LEFT OUTER JOIN b ON(a.key=b.key AND b.ds='2009-07-07')

可以进行优化对上一种情况进行优化，这种方式是将WHERE条件放在ON后，在JOIN的过程中，就对不满足条件的记录进行了预先过滤，JOIN和ON内部筛选同时进行。

（3）Join优化——并行执行

同步执行hive的多个阶段，hive在执行过程，将一个查询转化成一个或者多个阶段。某个特定的job可能包含众多的阶段，而这些阶段可能并非完全相互依赖的，也就是说可以并行执行，这样可能使得整个job的执行时间缩短。hive执行开启：set hive.exec.parallel=true

11. Hive的优化——数据倾斜操作 JoinGroup byCount Distinct 原因 key分布不均导致人为的建表疏忽业务数据特点症状任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。倾斜度平均记录数超过50W且最大记录数是超过平均记录数的4倍。最长时长比平均时长超过4分钟且最大时长超过平均时长的2倍。万能方法 hive.groupby.skewindata=true （1）大小表关联原因 Hive在进行join时，按照join的key进行分发，而在join左边的表的数据会首先读入内存，如果左边表的key相对分散，读入内存的数据会比较小，join任务执行会比较快；而如果左边的表key比较集中，而这张表的数据量很大，那么数据倾斜就会比较严重，而如果这张表是小表，则还是应该把这张表放在join左边。思路将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率。使用map的join让小表先进入内存方法 small_table join big_table （2）大大表关联原因日志中有一部分的userid是空或者是0的情况，导致在用user_id进行hash分桶的时候，会将日志中userid为0或者空的数据分到一起，导致了过大的斜率。思路把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终结果。方法 on case when(x.uid=’-’ or x.uid=‘0’ or x.uid is null) then concat(‘dp_hive_search’,rand()) else x.uid end = f.user_id; （3）聚合时存在大量特殊值原因做count distinct时，该字段存在大量值为NULL或空的记录。思路 count distinct时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union。方法select cast(count(distinct(user_id))+1 as bigint) as user_cnt from tab_a where user_id is not null and user_id<>"" 其中cast() as datatype用于数据类型转换