hive 语句总结_关于Hive的一些总结

一.数据类型

hive数据类型包含基本数据类型(int,double...)和集合数据类型(array,map,struct)。

传统数据库是写时模式，数据在写入数据库时进行模式检查，Hive是读时模式。

二.HiveSQL

内部表和外部表：删除一个内部表时，Hive同时会删除这个表中的数据。删除外部表时，只会删除外部表的元数据。有些HiveSQL语法不适用于外部表。

分区：对数据进行分区，可以使得查询更快，这是因为仅仅需要查询指定目录下的内容。当在where子句中增加谓词按照分区值过滤时，这些谓词被称为分区过滤器。

Hive支持Hive Join语句，但只支持等值连接(不支持大于、小于)。不支持在ON子句中使用OR。

JOIN优化：

(1)对3个以上表进行JOIN连接时，如果每个ON子句都使用相同连接键，只会产生一个MapReduce job。

(2)Hive假定查询中最后一个表是最大的那个表。对每行记录进行连接操作时，它会尝试把其他表缓存起来，扫描最后那个表进行计算。因此，用户需要保证连接查询中的表大小从左到右是依次增加的。

ORDER BY和SORT BY

ORDER BY会对查询结果集进行一个全局排序，也就是说所有数据都通过一个reducer进行处理，对于大数据集，这个过程会消耗很长时间。SORT BY只会在每个reducer中对数据进行排序，也就是执行一个局部排序的过程。这样可以保证每个reducer的输出数据都是有序的(并非全局有序)。这样可以提高后面全局排序的效率。

含有SORT BY的DISTRIBUTE BY

DISTRIBUTE BY控制map的输出在reducer中如何划分。默认情况下，在使用SORT BY时，不同reducer的输出内容会有重叠，如果希望一批数据在一起处理，可以使用DISTRIBUTE BY保证其被分发到同一个reducer中处理，然后使用SORT BY来保证有序。

Hive要求DISTRIBUTE BY语句写在SORT BY语句之前。

三.Schema设计

不要有过多分区

HDFS设计存储大文件，而不是大量小文件。使用过多分区可能导致创建大量HDFS小文件。一个分区对应着一个包含多个文件的文件夹。如果表存在数百个分区，那么每天可能创建好几万个小文件。NameNode必须把所有系统文件的元数据信息保存在内存中，大量的小文件会增大NameNode的压力。

MapReduce会将一个job转换为多个task。每个文件都是一个task，所以大量的小文件会造成大量的task，每个task都是一个新的JVM实例，这需要开启和销毁的开销。

如果用户不能找到合适的分区方式的话，可以考虑使用分桶存储。

同一份数据多种处理

Hive可以从一个数据源产生多个数据聚合，无需每次聚合都要重新扫描。比如：

insert overwrite table sales select * from history where action='purchased';

insert overwrite table creditsselect * from history where action='returned';

可以转换为以下语句：

from history

insert overwrite sales select * where action='purchased'

insert overwrite credits select * where action='returned';

分桶

分区提供一个隔离数据和优化查询的便利方式。但是并非所有数据集都可形成合理分区，特别是之前所提到的要确定合适的划分大小。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

例子：对表weblog进行分桶，使用user_id字段作为分桶字段，则字段值会根据用户指定的值进行哈希分发到桶中。同一个user_id下的记录通常会存储到同一个桶内。假设用户数比桶数多得多，每个桶内就会包含多个用户记录。

create table weblog(user_id INT, url STRING, source_ip STRING) PARTITIONED BY (dt STRING) CLUSTERED BY (user_id) INTO 96 BUCKETS;

四.调优

EXPLAIN

例子：EXPLAIN select sum(number) from onecol;

EXPLAIN会打印出抽象语法树，表明Hive是如何将查询解析成token(符号)以及literal(字面值)的。可以通过EXPLAIN分区物理执行计划以分析复杂的或执行效率低的查询。

需要尝试各种调优时，可以在“逻辑”层看到这些调整会产生什么影响。

EXPLAIN EXTENDED会产生更多输出信息。

LIMIT调优

默认情况下，LIMIT语句需要执行整个查询语句，然后返回部分结果。可以避免这种情况，开启一个配置属性，使用LIMIT语句时，对源数据进行抽样：

image.png

这个功能一个缺点是输入中有用数据永远不会被处理到。

JOIN优化

把最大的表放在JOIN语句的最右边，或者直接使用/* streamtable(table_name) */语句指出。

本地模式

对于小数据集，可以通过本地模式在单台机器上处理所有任务，执行时间会缩短，开启方式：

image.png

并行执行

hive的一个job可能包含多个stage，默认情况下hive一次只执行一个stage，开启参数可以使得stage并发执行：

image.png

严格模式

设置hive.mapred.mode值为strict，禁止3种类型查询：

(1)对于分区表，除非where中含有分区字段过滤条件限制数据范围，否则不允许执行。

(2)对于使用了ORDER BY语句的查询，必须使用LIMIT语句。

(3)限制笛卡尔积的查询。

调整mapper和reducer个数

如果mappper和reducer任务太多，会导致过多的开销；如果太少，不能充分利用并行性。

hive按照输入数据量大小来确定reducer个数，可以通过dfs -count命令来计算输入量大小。

属性hive.exec.reducers.bytes.reducer的默认值是1GB。

在共享集群上处理大任务时，为了控制资源利用情况，属性hive.exec.reducers.max非常重要。可以阻止某个查询消耗太多reducer资源。

JVM重用

JVM适用于小文件及task特别多的场景，这类场景大多数执行时间都很短。

Hadoop默认使用派生JVM来执行map和reduce任务，这时JVM的启动过程会造成相当大的开销，尤其执行的job包含大量task任务的情况。

JVM重用可使得JVM实例在同一个job中重新使用N次，N的值可以设置。

image.png

五.文件格式与压缩方法

GZip和Snappy压缩的文件不可再分，BZip2和LZO提供了块(BLOCK)级别的压缩，可以再分。

Hive中文件是如何分隔成行(记录)的？文本文件使用\n(换行符)作为行分隔符。如果不是默认的文本文件格式，用户需要指定Hive使用的InputFormat(定义了如何划分记录)和OutputFormat(定义了如何将这些划分写回到文件或控制台输出中)。

开启中间压缩，减少map和reduce task间的数据传输量

sequence file存储格式

大多数的压缩文件不可分割。hadoop支持的sequence file存储格式可以将一个文件划分为多个块，采用一种可分割的方式对块进行压缩。

六.函数

如何添加自定义UDF？

Java代码编译，打成jar包。

image.png

image.png

删除函数：

image.png

七.自定义Hive文件和记录格式

文件格式

(1)SequenceFile

SequenceFile文件是含有键-值对的二进制文件，其是Hadoop本身就支持的一种标准文件格式。

SequenceFile可以在块级别和记录级别进行压缩，这对于优化磁盘利用率和I/O来说非常有意义。同时仍然可以支持按照块级别的文件分隔，以方便并行处理。

(2)RCfile

对于特定类型的数据和应用来说，采用列式存储会更好。比如，指定表有非常多的字段，大多数的查询只涉及到其中一小部分字段，这时扫描所有行而过滤掉大部分数据显然是浪费。

基于以上场景，Hive设计了RCFile。

Hive提供了一个rcfilecat工具展示RCFile文件内容：

hive --service rcfilecat /user/hive/warehouse/columntable/000000_0

记录格式：SerDe

SerDe是序列化/反序列化的简写形式。一个SerDe包含了将一条记录的非结构化字节(文件存储的一部分)转化成Hive可以使用的一条记录的过程。

Hive在内部使用自定义的InputFormat读取一行数据记录，之后传递给SerDe.deserialize()方法进行处理。

常用的SerDe有CSV SerDe、TSV SerDe，JSON SerDe，Avro Hive SerDe等。

八.Thrift服务

Thrift是一个软件框架，用于跨语言的服务开发。Thrift允许客户端用不同语言通过编程方式远程访问Hive。

管理HiveServer

(1)在生产环境中使用HiveServer

客户端机器需要进行的形成执行计划和管理task的工作现在需要由服务端来完成。如果同时执行多个客户端的话，会对单个HiveServer造成太大压力。一种解决办法是使用TCP负载均衡或者通过代理为一组后面的服务器进行均衡连接。

一种可用的工具是haproxy。

Hive ThriftMetastore

Hive会话会直连到一个JDBC数据库，这个数据库用作元数据存储数据库。Hive提供了一个可选组件名为ThriftMetastore。

hive --service metastoe &

netstat -an | grep 9083

九.锁

Hive缺少通常在update和insert类型的查询中使用到的对于列、行或者查询级别的锁支持。但是Hadoop和Hive是多用户系统，在一些情况下，锁是必要的。

结合Zookeeper支持锁功能

配置Hive，使其可以使用Zookeeper来启用并发支持。

在$hive_home/hive-site.xml配置文件中，增加如下属性：

image.png

Hive中提供了2种类型的锁，读取某个表的时候需要共享锁，修改表的操作需要独占锁。