Hive基础知识笔记（含MySQL metastore伪分布式安装配置流程）一、介绍二、安装配置三、常用命令与设置四、服务与组件五、HiveQL六、表七、查询数据八、UDF

68 阅读 0 评论 45 点赞

我是靠谱客的博主风趣哑铃，最近开发中收集的这篇文章主要介绍Hive基础知识笔记（含MySQL metastore伪分布式安装配置流程）一、介绍二、安装配置三、常用命令与设置四、服务与组件五、HiveQL六、表七、查询数据八、UDF，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

该笔记涉及代码：https://github.com/hackeryang/Hadoop-Exercises/tree/master/src/main/java/Hive

一、介绍

1.Hive是一个构建在Hadoop平台上的用类似SQL的语句HQL来协助读写与管理分布式系统上大数据集的数据仓库框架。可以将结构化的数据文件映射成一张数据库表，将类SQL语句转换为MapReduce任务运行，几行查询语句就能实现一长段MapReduce程序的功能，大大提升了开发效率，使擅长SQL而不熟悉Java和MapReduce、编程较弱的数据分析人员能在HDFS大规模数据集上方便地利用HQL查询、汇总和分析数据。Hive能把SQL中的表和字段转换为HDFS中的目录和文件。Hive本身不存储和计算数据，而完全依赖于HDFS和MapReduce，可理解为一个client将SQL操作转换为相应的MapReduce job在集群上运行。Hive的架构如下所示：

其中用户客户端的三个接口中最常用的是shell方式，Hive Driver就是将用户编写的HQL语法匹配转换成MapReduce模板并形成job执行的核心组件。metastore元数据库将每个表的元数据信息存储在RDBMS中，支持的数据库包括自带的derby和MySQL，实际生产工作中基本都会使用MySQL，因为derby不允许用户打开多个client对其同时操作，只能用一个客户端打开并操作，也就是同一时刻只能被一个用户所使用。使用MySQL就可以对数据进行多用户并发操作。Hive每个表的实际数据存储在HDFS中，但它本身只是个client工具，因此并没有像Spark和Hadoop那样搭建时需要考虑伪分布式还是全分布式。

2.Hive与RDBMS的区别如下：

(1)存储文件的系统不同，Hive使用Hadoop平台的HDFS，RDBMS使用本地服务器的文件系统。

(2)Hive使用MapReduce的计算模型，RDBMS使用自己设计的计算模型。

(3)RDBMS为实时查询业务而设计，而Hive为海量数据挖掘和分析而设计，实时性较差，适合离线处理场景。

(4)Hive能够通过Hadoop分布式系统轻易扩展存储能力和计算能力，而RDBMS较难扩展，往往在单个高性能服务器上运行。

二、安装配置

3.Hive本机伪分布式环境的安装与配置如下所示：

(1)尽管Hive默认使用derby作为元数据库，但生产环境中基本都使用MySQL作为Metastore，所以先安装配置MySQL，使用如下命令安装软件源中最新版本的MySQL：

sudo apt-get update  #更新软件源
sudo apt-get install mysql-server  #安装mysql

上面的安装会自动包括mysql-client和mysql-common等组件，因此不需要额外再安装。安装过程中命令行终端会出现如下要求设置MySQL的root用户的密码，设置后等待安装完成就好：

安装完成后用以下两个命令确认是否能正确启动，如果显示mysql节点处于LISTEN状态说明启动成功，如下所示：

(2)复习一下常用MySQL命令。

a.进入mysql命令行模式，使用如下命令：

mysql -u root -p

b.在MySQL shell模式下的一些命令，查看目前的文字编码：

show variables like “char%”;

c.显示目前已有的数据库：

show databases;

其中mysql库是安装后初始的库之一，它非常重要，包含MySQL的系统信息，改密码和新增用户等实际上也是在该库中的某个表中进行操作。

d.使用某个数据库并查看该库中的表：

use <数据库名>;
show tables;

e.显示某个表的结构：

describe <表名>;

f.查看MySQL版本：

show variables like ‘version’;

g.退出MySQL命令行模式：

exit;

h.重启MySQL服务（例如修改了配置），使用如下命令：

service mysql restart

i.最后，如果要关闭MySQL服务，可以用以下命令：

service mysql stop

(3)下载MySQL JDBC驱动包备用（地址：https://dev.mysql.com/downloads/connector/j/），后面配置Hive与MySQL相连接时会用到。进入页面后在选择操作系统下拉框中选择“Platform Independent”换为兼容所有操作系统的压缩包下载，如下所示：

(4)安装好MySQL后下载Hive，如果之前安装的是CDH版Hadoop，可以通过以下网址下载CDH版Hive：

http://archive.cloudera.com/cdh5/cdh/5/

打开网页后可以按ctrl+F查找hive，等页面加载完成后滚动到最后才是各组件的下载地址，注意下载的组件后面有cdh版本，最好与之前下载的hadoop的cdh版本相同，不会出现无法预料的奇怪兼容问题，如下所示：

下载好后通过以下命令将tat.gz格式的压缩包解压到自己想要的目录中：

tar -zvxf /mnt/sda6/hive-1.1.0-cdh5.15.0.tar.gz -C /mnt/sda6/Hadoop

(5)配置Hive的环境变量，使用sudo vim /etc/profile命令编辑系统环境变量文件（也可以编辑~/.bashrc文件，这是当前用户使用命令行时的环境变量，/etc/profile是整个系统所有用户的环境变量），按i键进入编辑模式后，在其中加上HIVE_HOME变量并在PATH变量中添加Hive的内容，然后按ESC退出编辑模式后按冒号并输入wq写入保存并退出，如下所示：

然后在命令行输入source /etc/profile命令使环境变量文件立刻生效，就不必cd到Hive的根目录可以直接在任何路径下启用Hive。

(6)修改Hive的配置文件，进入$HIVE_HOME/conf目录，将hive-env.sh.template模板文件去掉.template后缀重命名为hive-env.sh，并修改该文件设置HADOOP_HOME、HIVE_CONF_DIR和HIVE_AUX_JARS_PATH的值，如下所示：

接着，$HIVE_HOME/conf目录中如果有hive-default.xml.template模板就将其重命名为hive-site.xml，再使用vim $HIVE_HOME/conf/hive-site.xml命令编辑该配置文件，如果没有就直接用该命令，会在该目录下创建一个叫hive-site.xml的空配置文件，然后按i进入编辑模式，在该文件中修改或填入以下内容后按ESC退出编辑模式，再按冒号键后输入wq保存并退出：

第一个属性指metastore的JDBC URL地址，这里设置为本机上的mysql，并在启动hive时自动查看mysql中是否存在名为“hive”的数据库，若不存在则创建它。第二个属性指定metastore连接到MySQL的JDBC驱动类。第三个属性指连接metastore的用户名，这里就是登录MySQL的用户名。第四个属性是对应用户名的密码，这里也是MySQL上的密码。

(7)非常重要的一步，一定要记得把先前下载的MySQL JDBC驱动包文件夹中的mysql-connector-java-x.x.x.jar文件复制到$HIVE_HOME/lib目录下，该JAR包是连接MySQL的JDBC驱动，没有它就无法连接MySQL，其中就包含上面配置文件指定的“com.mysql.jdbc.Driver”类。如果不能黏贴过去，可能是lib目录的读写权限问题，可以右键该目录点击属性，然后点击“权限”选项卡修改“组”和“其他人”的权限为可读写，如下所示：

(8)配置完成，先要启动MySQL服务和Hadoop才能启动Hive，输入以下命令启动前两者（Hadoop的安装配置见https://blog.csdn.net/qq_33588730/article/details/81123614和https://blog.csdn.net/qq_33588730/article/details/81228315）：

service mysql start
hdfs namenode -format  #首次启动Hadoop需要，若修改了HDFS存储设置则下次开机后启动再也不需要该命令
start-all.sh
mr-jobhistory-daemon.sh start historyserver

然后启动Hive的metastore和hiveserver2两个服务，使用如下命令：

hive --service metastore &
hive --service hiveserver2 &

如果没有“&”符号，这两个命令由于是持续运行的服务，所以终端会被一直占据，“&”加在这种命令后面可以使持续运行的命令在后台运行，从而可以输入下一条命令。在使用某个持续在命令行中使用第一个命令后，光标会看起来一直在原地闪没响应，不显示绿色用户名，是因为它是一个一直占用线程的后台服务，实际上已经成功启动，其实这时候可以直接输入第二条命令，如下面白色高亮处所示：

该第二条命令执行后，命令行光标也会处于原地闪烁的挂起状态，这时候直接输入jps命令回车就好，可以看到Hive的两个后台服务已经启动成功，名字为两个RunJar，如下所示：

接着在命令行中输入hive命令，将会启动Hive交互命令行，很多命令操作和MySQL相同或类似，输入“show databases;”命令查看Hive中包含的数据库，初始状态包含一个名为“default”的库，如下所示：

能够使用HQL命令，说明Hive已经安装成功，接下来在Hive交互命令行中输入“exit;”命令，再用mysql -u root -p命令进入MySQL数据库的交互命令行，进入后输入“show databases;”命令，可以看到由于Hive服务的初始化，MySQL中自动建立了名为“hive”的库（名称与hive-site.xml中的设置对应），其中包含28张表，如下所示：

使用hadoop fs -ls命令也可以看到，Hive的启动会在HDFS上建立一个/user/hive/warehouse目录，里面会存放以后Hive新建的表，表名实际上就是一个目录（如stations表），表中导入的数据实际上就是导入的文件本身，如下所示：

(9)后续开发UDF等程序时会用到IDE，以Intellij IDEA为例配置maven依赖，在maven工程的pom.xml文件中加入关于Hive的依赖即可（版本要与自己所下的Hive版本相对应，如果是CDH版可参考https://www.cloudera.com/documentation/enterprise/release-notes/topics/cdh_vd_cdh5_maven_repo.html）：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.cloudera.hadoop</groupId>
    <artifactId>bigdata</artifactId>
    <version>1.0-SNAPSHOT</version>

    <repositories>
        <repository>
            <id>cloudera</id>
            <url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
            <releases>
                <enabled>true</enabled>
            </releases>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
    </repositories>

<dependencies>
    ......
<dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>1.1.0-cdh5.15.0</version>
</dependency>
......
</dependencies>

三、常用命令与设置

4.与MySQL一样，查看Hive中有哪些表的命令也是“show tables;”，命令也同样需要以分号结束，也一样对大小写不敏感。第一次安装使用Hive时，该命令会花几秒钟执行，因为系统采用延迟(lazy)策略，在此时才在机器上创建metastore。除了使用交互命令行，也可以利用-f参数运行指定文件中的脚本指令，例如：

hive -f script.q

对于较短脚本命令，可用-e参数直接嵌入短命令，此时该短命令不需要分号作为结束符，如下所示：

该测试用的dummy表可以用如下命令来创建：

echo ‘X’ > /tmp/dummy.txt
hive -e ‘CREATE TABLE dummy (value STRING); 
LOAD DATA LOCAL INPATH ‘/tmp/dummy.txt’ 
OVERWRITE INTO TABLE dummy’;

每次运行时，Hive都会把操作运行时的信息打印到终端里，例如查询所花的时间。可以在启动程序时使用-S参数不显示这些信息而只输出查询结果，如下所示：

5.与RDBMS相同，Hive也把数据组织成表。可以使用CREATE TABLE语句为气象数据集新建一张表：

hive> CREATE TABLE records (year STRING, temperature INT, quality INT)
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY 't';

其中第一行表示创建records表，包含year，temperature和quality三列及其数据类型。第二行的ROW FORMAT子句为HQL独有，表示数据文件的每一行是由制表符隔开的文本。Hive以每行三个字段（即表中三列），字段间以制表符分割，每行以换行符分隔的方式读取数据。向Hive输入数据的命令如下所示：

hive> LOAD DATA LOCAL INPATH '/mnt/sda6/sample.txt'
    > OVERWRITE INTO TABLE records;

该命令使Hive把指定本地磁盘（LOCAL关键字，不加为从HDFS上导入）上的文件导入到表records中，LOAD DATA语句中的OVERWRITE关键字使Hive删除该表对应目录中已有的所有文件，若没有该关键字则只会把新文件加入以表名命名的目录中（会覆盖掉同名文件）。该操作不会解析文件或存储为内部数据库格式，而是按原样逐字存储。如果是默认使用Derby数据库的本机模式，默认会把Hive表存储在/usr/hive/warehouse目录中，该例子的sample.txt会存在/usr/hive/warehouse/records目录下。该存储目录可以通过hive.metastore.warehouse.dir属性控制。在records表中导入数据后，查询命令如下所示：

可以看到Hive shell命令转化成了一个MapReduce job，该命令的意思是使用MAX()聚集函数和GROUP BY子句在每个年份组中找到最高气温，需要注意的是所有非聚集函数的变量都需要在最后GROUP BY语句中出现，Hive将一个查询命令转化为一个MapReduce作业运行，在HDFS上进行查询。

6.与Hadoop相同，Hive也用XML文件进行配置，Apache Hive中包含hive-site.xml和hive-default.xml，但CDH版Hive中没有，需要自己新建。可以在命令行中加入--config参数来修改Hive查找所有配置文件的目录：

hive --config /<想要的目录>

该命令与在hive-env.sh文件中修改HIVE_CONF_DIR变量的效果一样。还可以在hive命令后传递-hiveconf参数设置通信会话属性，例如如下命令设定在session中使用一个伪分布式集群：

hive -hiveconf fs.defaultFS=hdfs://localhost 
-hiveconf mapreduce.framework.name=yarn 
-hiveconf yarn.resourcemanager.address=localhost:8032

在Hive交互命令行中，还可以使用SET命令更改设置，例如下面的命令使表的定义中使用“桶”(bucket)：

hive> SET hive.enforce.bucketing=true;

还可以用只带属性名称的SET命令查看属性设置的值，如果SET后面不加任何参数会列出Hive设置的所有属性取值：

使用SET -v可以列出系统中所有属性，包括Hadoop默认值。Hive设置属性有覆盖优先级，下面表示优先级顺序：

(1)交互命令行SET命令。(2)命令行-hiveconf参数。(3)hive-site.xml等配置文件。(4)Hive默认值与Hadoop默认配置文件(core-default.xml等）。

7.Hive支持多个执行引擎，默认为MapReduce，也可以设置为Apache Tez，如下所示：

hive> SET hive.execution.engine=tez;

可以在${java.io.tmpdir}/${user.name}目录下找到hive.log文件，它是Hive的错误日志，在很多系统中${java.io.tmpdir}就是/tmp，如下所示：

如果希望将错误日志放到别的目录，可以在命令行使用以下命令：

hive -hiveconf hive.log.dir=’/XXX/${user.name}’

日志配置在$HIVE_HOME/conf/hive-log4j.properties文件中，可以编辑该文件来修改日志级别与其他日志设置，更方便的是用命令行设置，例如下面命令将debug信息发送到控制台：

hive -hiveconf hive.root.logger=DEBUG,console

四、服务与组件

8.Hive shell只是其中一种服务，还可以使用hive --service参数指明使用哪种服务，输入hive --service help命令可以获得可用服务列表。Hive还包括以下常用服务：

(1)CLI，Hive的交互式命令行接口。这是默认服务，不过即将废弃替换为beeline。

(2)hiveserver2，使Hive以提供Thrift服务的服务端形式运行，允许用不同语言编写的客户端进行访问。相比hiveserver在支持认证和多用户并发上有较大改进。Thrift是一个跨语言RPC服务框架，使用户可以根据自身需要采用不同语言独立分开开发客户端和服务后端，Thrift作为不同语言前后端的中间媒介，通过IDL将各语言统一编译成对应的特定语言接口文件，使不同语言前后端能够兼容通信。使用Thrift、JDBC和ODBC连接器的客户端需要运行Hive服务器来与Hive进行通信，设置server2.thrift.port属性可以指明服务器监听通信连接的端口号（默认为10000）。

(3)Beeline，类似CLI的嵌入式Hive命令行接口。也可使用JDBC连接到一个HiveServer2进程。

(4)hwi，Hive的web接口。在没有安装客户端的情况下可以使用该web页面简易替代CLI，但只有结果输出没有中间过程信息显示在控制台，在生产环境中的安全性和易用性不是很好，默认情况下未安装，需要下载.war文件配置。除此之外，Hue是一个功能更全面的Hadoop Web接口，能够调试Hadoop各种生态应用，包括运行Hive查询和浏览metastore的功能。

(5)jar，与hadoop jar等价，包含运行类路径中必要的Java程序。

(6)metastore，默认情况和Hive server运行在同一个进程中，如果启用该单独服务，metastore会作为一个独立的远程进程运行。设置METASTORE_PORT环境变量（或使用-p命令行选项）可以指定服务器监听端口号，默认为9083。

9.如果使用hive --service hiveserver2命令启动Hive server后台服务，客户端可以以不同机制连接到该服务器。客户端与服务之间的联系有以下几种：

(1)Thrift客户端。

(2)JDBC驱动。定义在org.apache.hadoop.hive.jdbc.HiveDriver类中。在以jdbc:hive2://host:port/dbname形式配置JDBC URI后，Java客户端可以在指定主机和端口连接到远程Hive服务。该驱动将Java编译为特定语言接口文件后，也可以调用支持Thrift接口的Hive服务端API。如果URI设置为jdbc:hive2://，会用JDBC内嵌模式连接Hive,此时Hive和发出请求的客户端在同一个JVM中运行，不使用Thrift。Beeline CLI也使用JDBC驱动与Hive通信。

(3)ODBC驱动。允许支持ODBC协议的应用程序（例如一些商业情报软件）连接到Hive，Apache版Hive中没有预装ODBC驱动，需要自己配置。Hive完整架构图如下所示：

10.metastore是Hive元数据的存储库，包含Hive创建的database和table等的属性信息，比如表，表结构，字段名称，字段长度等信息，一般存在关系型数据库中如Derby和MySQL等。它的作用是：客户端连接metastore服务，metastore再去连接MySQL存取元数据，有了metastore服务就能使多个客户端同时并发连接，且这些客户端不需要知道MySQL的用户和密码，可以直接通过已配置好与MySQL之间连接的metastore服务即可，提供了更好的管理性和安全性。默认情况metastore服务与Hive服务运行在同一个JVM中，且使用本地磁盘存储的Derby数据库，但是Derby只支持一个用户连接metastore，不能多用户并发会报错。metastore的常用配置属性如下所示：

如果使用MySQL作为metastore，javax.jdo.option.ConnectionURL要设为jdbc:mysql://host/dbname?createDatabaseIfNotExist=true，而javax.jdo.option.ConnectionDriverName设为com.mysql.cj.jdbc.Driver（即将废弃com.mysql.jdbc.Driver）。同时MySQL的JDBC驱动JAR文件(Connector/J)必须在$HIVE_HOME/lib目录中。如果使用远程metastore服务器，要把hive.metastore.uris属性设为metasotre服务器的URI，多个URI用逗号分隔，URI形式为thrift://host:port，这里端口号对应于启动metastore服务器时设置的METASTORE_PORT值。三种使用metastore的方式如下所示：

11.Hive起初对HDFS和MapReduce底层的依赖意味着它的体系结构不同于传统RDBMS，不过随着不断的升级，Hive在使用感觉上越来越像传统SQL。两者的对比如下所示：

(1)传统SQL表的模式是写时模式(schma on write)，即写入保存表数据时对数据进行校验，不符合格式的数据无法写入到表中，写时模式有利于提升查询性能，因为可以对列进行索引，并对数据进行压缩，但是加载数据会花更多时间，而且开发时不能完全预料查询应该使用什么索引和加载时模式；而Hive是读时模式(schema on read)，即在写入保存表数据时不对数据进行校验，而是在读取数据时校验，不符合格式的数据设置为NULL，这样加载速度更快，因为不需要读取数据进行解析(parse)，再序列化以数据库内部格式存入磁盘，数据加载操作只是文件复制或移动。

(2)更新、事务和索引是传统SQL最重要的特性，而Hive不支持更新特性，因为它被用于大数据场景，对整个表进行全表扫描(full-table scan)是经常的操作，表更新是通过把数据变换后放入新表实现的，因为HDFS不提供就地文件更新，所以插入、更新和删除操作引起的变化都被保存在一个较小的增量文件中，由metastore在后台运行的MapReduce作业会定期将这些增量文件合并到基表(base table)文件中，该功能要将表启用事务特性才能使用。

Hive支持表级(table-level)锁和分区级(partition-level)锁。有了锁可以防止一个进程删除正在被另一个进程读取的表，锁由ZooKeeper自动管理，用户不必执行获得与释放锁的操作，但依然可以通过SHOW LOCKS语句获取已获得哪些锁的信息，默认未启用锁。

在某些情况下Hive的索引能加快查询速度。例如SELECT * from t WHERE x=a，只需要扫描表文件的一小部分，所以可以利用列x上的索引。目前Hive的索引分为紧凑(compact)索引和位图(bitmap)索引两类。紧凑索引存储表中每个值的HDFS块(block)号，而不是存储该值在文件中的偏移位置，所以存储不会占用太多空间，且对于值被聚簇(clustered)存储于相近行的情况，索引依然有效。位图索引使用压缩的位集合(bitset)来高效存储具有某个特殊值的行，这种索引适合具有较少取值种类的列（例如性别和国籍）。

五、HiveQL

12.HiveQL是SQL-92、MySQL和Oracle SQL语言的混合体。SQL和HiveQL特性的比较如下所示：

Hive支持一些复杂的数据类型，例如数组、映射和结构，支持的数据类型如下所示：

几种基本数据类型基本对应于Java中的类型，例如TINYINT、SMALLINT、INT、BIGINT等价于Java的byte、short、int和long，FLOAT和DOUBLE对应于Java的float和double（分别为32位和64位浮点数）。DECIMAL表示任意精度的小数，类似于Java的BigDecimal类型，常用于表示货币值。因此DECIMAL(5,2)表示-999.99到999.99之间的数，逗号前后分别是精度(precision)和标度(scale)，分别指总共的位数和小数点后的位数。DECIMAL(5)表示-99999到99999之间的整数，如果精度省略则默认为10，即DECIMAL等价于DECIMAL(10,0)，精度最大值为38，标度值不能超过精度值。

Hive中存储文本的数据类型有三种。STRING是一个无最大长度声明的可变长字符串（理论上可存2GB字符数）。VARCHAR与STRING相似，但它需要声明最大长度（范围在1到65535之间），例如VARCHAR(100)。CHAR是固定长度的字符串，在没填满容量时会用空格填充尾部，例如CHAR(100)。当CHAR值用于字符串比较时会忽略尾部空格。TIMESTAMP表示精度为纳秒的时间戳，但并未存储时区信息，可以使用to_utc_timestamp和from_utc_timestamp函数来进行时区转换。

13.Hive有四种复杂数据类型：ARRAY、MAP、STRUCT和UNION。前两者相当于Java中的array和map，而STRUCT是一种记录类型，它封装了一个命名的字段集合。UNION是从几种数据类型中指明选择一种，UNION的值必须与这些数据类型之一完全匹配。复杂数据类型声明必须用尖括号指明其中数据字段的类型，如下所示：

hive> CREATE TABLE complex (
    > c1 ARRAY <INT>,
    > c2 MAP<STRING,INT>,
    > c3 STRUCT<a:STRING,b:INT,c:DOUBLE>,
    > c4 UNIONTYPE<STRING,INT>
    > );

如果已经把上面表中“文字示例”那一列中这四个类型的数据加载到complex表中，查询命令可以如下所示：

14.Hive的操作符与SQL相同，包括例如x=’a’，x IS NULL，x LIKE ‘a%’，x+1，x OR y等。不同的地方有例如“||”是逻辑或(OR)操作符，而不是字符串连接(concatenation)操作符，在MySQL和Hive中，字符串连接要用concat()函数。可以在Hive shell环境下输入“SHOW FUNCTIONS;”命令获取可用函数列表。要了解某个特定函数的使用帮助，可使用DESCRIBE命令，如下所示：

hive> DESCRIBE FUNCTION length;
OK
length(str | binary) - Returns the length of str or number of bytes in binary data
Time taken: 0.026 seconds, Fetched: 1 row(s)

15.Hive支持原始数据类型的隐式类型转换，转换规则如下：

(1)任何数值类型都可以隐式转换为一个范围更广的类型或者文本类型(STRING、VARCHAR、CHAR)。

(2)所有文本类型都可以隐式转换为另一种文本类型。

(3)文本类型都能隐式转换为DOUBLE或DECIMAL。

(4)BOOLEAN类型不能显式或隐式转换为任何其他类型。

(5)TIMESTAMP和DATE可以隐式转换为文本类型。

除了隐式转换，还可以使用CAST操作显式进行类型转换。例如CAST(‘1’ AS INT)将字符串转为整数，如果执行例如CAST(‘X’ AS INT)等失败的强制类型转换操作，表达式会返回空值NULL。

六、表

16.Hive的表逻辑上由存储的数据和描述表中数据形式的相关元数据组成。数据一般在HDFS或亚马逊S3中，元数据在RDBMS中而不是HDFS中。很多RDBMS提供了多个命名空间(namespace)的支持，这样多个用户与应用可以被分隔到不同数据库或模式中。Hive也同样可以划分多个数据库和库中的表，能够使用CREATE DATABASE dbname、USE dbname以及DROP DATABASE dbname等语句，能通过dbname.tablename来唯一确定一张表。如果表操作命令中没有指明数据库，默认是指default数据库中的表。

在Hive创建表时，默认情况Hive负责管理数据，即会把数据移入设置的仓库目录(warehouse directory)，称为托管表(managed table)，另一种选择是创建一个外部表(external table)，会使Hive到仓库目录以外的位置访问数据。这两种表的区别在LOAD和DROP命令的语义上。在用Hive托管的仓库目录存储创建的表数据时（即使用托管表），Hive将HDFS上的数据文件移动到自己的仓库目录中，如下所示：

上面操作将HDFS上存储的hdfs://user/tom/data.txt移动到Hive管理的仓库目录中，即hdfs://user/hive/warehouse/managed_table，相当于LOAD是一个移动操作，由于该加载操作就是文件系统中的文件移动或重命名，因此它的执行速度很快。如果要丢弃该表，可使用以下命令：

hive> DROP TABLE managed_table;

这样该表的元数据和数据都会被一起删除。对于外部表而言，创建和删除表两个操作的结果就不同，可以由用户控制数据的创建和删除，外部数据的位置也需要在创建表时指定，如下所示：

使用EXTERNAL关键字后，Hive知道该数据不由自己的仓库目录管理，不会把它转移过去，在定义时甚至不会检查该外部路径是否存在。这样的特性可以使用户把创建数据推迟到创建表之后进行。在丢弃外部表时，Hive不会删除数据，只会删除元数据。对于两种表应该选用哪一种，如果所有处理都由Hive完成，应该使用托管表。如果要用Hive和其他工具处理同一个数据集，应该使用外部表，一般把存放在HDFS（不由Hive创建）的初始数据集用作外部表，然后用Hive的LOAD功能将数据移到Hive的仓库目录托管表中。外部表也可以用于从Hive导出数据供其他应用程序使用。

17.Hive把表组织成分区(partition)，这是一种根据分区列(partition column，如日期)的值对表进行粗略划分的机制，使用分区可以加快数据分片(slice)的查询速度。表或分区可以进一步分为桶(bucket)，它会为数据提供额外的结构以获得更高效的查询处理，例如通过用户ID划分桶，可以在所有用户集合的随机样本上快速计算基于用户的查询。

举个分区的例子，日志文件每条记录包含一个时间戳，如果用日期来分区，同一天记录会被放在同一个分区中，好处是对于限制到某个特定日期的查询处理就会很高效，只需要扫描查询范围内的分区文件。一个表可以用多个维度进行分区，例如除了对日志进行分区以外，还可以进一步根据国家对每个分区进行子分区(subpartition)，以加速根据地理位置进行的查询。

分区是在创建表的时候用PARTITIONED BY子句定义的，该子句需要定义列的列表，例如对于日志文件，可能要把每条记录定义为由时间戳和日志行组成，如下所示：

hive> CREATE TABLE logs (ts BIGINT, line STRING)
    > PARTITIONED BY (dt STRING, country STRING);

在把数据加载到分区表时，要显式指定分区值：

hive> LOAD DATA LOCAL INPATH ‘input/hive/partitions/file1’
    > INTO TABLE logs
    >PARTITION (dt=’2001-01-01’, country=’GB’);

其中dt为日期分区，country为国家子分区。在Hive的存储中，分区只是表目录下嵌套的子目录。更多文件加载到logs表后在HDFS上的目录结构可能会像下面这样：

可以看到表名logs是个目录，分区dt是一个子目录，子分区GB又是dt的子目录，导入的表数据实际上就是导入的文件本身。在hive shell中可以用SHOW PARTITONS命令查询表中有哪些分区：

要注意的是，PARTITIONED BY子句中的列定义是表中正式的列，称为分区列(partition column)，但是数据文件并不包含这些列的值，因为它们源于目录名。可以在SELECT语句中通过分区列来限制扫描位置，使Hive只查询相关的分区，如下面例子所示，该命令只会扫描file1到file4，并返回dt分区列的值，这个值是从目录名中读取的，因为它们在数据文件中并不存在：

hive> SELECT ts, dt, line FROM logs WHERE country=’GB’;

18.把表或分区组织成桶(bucket)可以获得更高的查询处理效率，桶为表加上了额外结构，在处理有些查询时Hive能够利用该结构，连接两个在相同列（包含连接列）上划分了桶的表，可使用map端连接(map-side join)高效地实现。而且，把表划分为桶可以使采样(sampling)更高效，即在处理大规模数据集时，在开发和修改查询的阶段可以在数据集的一小部分数据中进行试验性的查询，会带来很多方便。使用CLUSTERED BY子句可以指定划分桶所用的列和要划分的桶的个数，如下所示的例子中使用用户ID列来确定如何划分桶：

hive> CREATE TABLE bucketed_users (id INT, name STRING)
    > CLUSTERED BY (id) INTO 4 BUCKETS;

上面例子划分桶的位置是对用于分桶的列(用户ID)进行哈希再除以桶的个数取余数来获得。对于map端连接的情况，首先两个表以相同方式划分桶，处理左边表内某个桶的mapper知道右边表内相匹配的行在对应桶内，这样mapper只需要获取那个桶（右边表内存储数据的一小部分）即可连接。该方法不一定需要两个表必须有相同的桶个数，两个表的桶个数是倍数关系也可以。桶中数据可以根据一个或多个列进行排序，这样对每个桶的连接变成了高效的归并排序(merge-sort)，可以进一步提升map端连接的效率，如下例子声明一个表使用排序桶：

hive> CREATE TABLE bucketed_users (id INT, name STRING)
    > CLUSTERED BY (id) SORTED BY (id ASC) INTO 4 BUCKETS;

Hive并不检查数据文件中的桶是否和表定义中的桶一致（无论是对于桶的数量或用于划分桶的列）。如果两者不匹配，在查询时可能会碰到错误或未定义的结果。因此，最好让Hive来进行划分桶的操作，虽然把Hive外生成的数据直接加载到划分成桶的表中也可以。

19.对于一个没有划分桶的用户表：

要向分桶后的表中填充数据，需要先使用以下命令：

hive> SET hive.enforce.bucketing = true;

这样Hive就知道用表定义中声明的数量来创建桶，然后使用INSERT命令即可：

hive> INSERT OVERWRITE TABLE bucketed_users
    > SELECT * FROM users;

在文件系统中，每个桶实际上就是表或分区目录里的一个文件。桶n是按照字典序排列的第n个文件。桶也对应于MapReduce的输出文件分区，一个job产生的桶（输出文件）和reduce task个数相同。运行如下命令可以查看之前创建的bucketed_users表的布局：

hive> dfs -ls /user/hive/warehouse/bucketed_users;

该命令将会显示4个新建的文件，文件名如下所示：

第一个桶中包括用户ID 0和4，因为一个INT的哈希值就是该整数本身，再除以桶数4之后的余数都为0。可以用如下命令来确认是否在该文件中：

用TABLESAMPLE子句对该表进行取样，能获得相同结果，该子句会将查询限定在表的一部分桶内，而不是扫描整个表，如下所示：

桶的个数从1开始计数，所以该查询会从4个桶中的第一个获取数据，对于一个大规模均匀分布数据集，这会返回表中约1/4的数据行。如果使用其他比例例如1/2采样，将会大约返回一半的数据行（不一定是严格一半，因为这个比例不一定是桶数的整数倍）：

该查询只需要读取和TABLESAMPLE子句匹配的桶文件，而不读取其他桶，所以取样划分了桶的表是高效的操作。但如果使用rand()函数对没有划分为桶的表进行取样，即使只需要读取少部分数据，也依然会扫描整个表的数据集：

20.Hive从两个维度对表的存储进行管理：

(1)行格式(row format)，指行及其一行中的字段如何存储，行格式的定义由SerDe完成，它指序列化和反序列化工具(Serializer-Deserializer)。当查询表时，SerDe作为反序列化工具把文件中字节形式的数据行反序列化为Hive的内部数据对象形式。当执行INSERT或CTAS等写入操作时，SerDe作为序列化工具会把Hive的数据行中的内部数据对象格式序列化为字节形式并写到输出文件中。

(2)文件格式(file format)，指一行中字段容器的格式，最简单的格式是纯文本文件，也可以使用面向行和面向列的二进制格式。

如果在创建表时没有用ROW FORMAT或STORED AS子句，默认存储格式是分隔的文本，文件中每行(line)存储一个数据行(row)，可以通过hive.default.fileformat属性设置默认格式。默认行内分隔符不是制表符，而是ASCII中的Control-A，因为它出现在字段文本中的可能性很小，Hive无法对分隔符进行转义，所以挑选一个不会在数据字段中用到的字符作为分隔符很重要。集合类元素的默认分隔符为Control-B，用于分隔ARRAY或STRUCT或MAP的键值对中的元素。默认的映射键分隔符为Control-C，用于分隔MAP的键和值。表中各行之间用换行符分隔。

所以，语句CREATE TABLE ...;等价于下面对所有参数显式说明的语句，可以使用八进制格式表示分隔符，例如001表示Control-A：

Hive使用一个名为LazySimpleSerDe的SerDe来处理这种分隔格式以及面向行的MapReduce文本输入和输出格式，它对字段的反序列化是延迟处理的，只有访问字段时才进行反序列化。这种分隔文本的默认存储格式并不紧凑，因为它以冗长的形式进行存储，例如布尔值实际上以文本字符串true和false的形式存储。但这种简单格式在使用其他工具（例如MapReduce或Streaming）处理时会比较容易。

21.除了默认的分隔文本存储格式，第二种是二进制存储格式，包括SequenceFile、Avro数据文件、Parquet文件、RCFile与ORCFile等。使用二进制格式只需在CREATE TABLE语句中的STORED BY子句中作相应声明即可（例如STORED AS SEQUENCEFILE），不需要指定ROW FORMAT，因为其格式由底层二进制文件格式来控制。二进制存储格式可划分为两大类，面向行的格式和面向列的格式，前者适合同时处理一行中很多列的情况，后者适合只访问表中一小部分列的查询情况。Hive支持两种面向行的格式，分别为Avro数据文件和顺序文件，都是可分割和压缩的格式。支持的面向列的格式有Parquet、RCFile和ORCFile。

22.除了上面两种存储格式，还可以自己定制和选择不同的SerDe。下面的例子使用RegexSerDe，采用一个正则表达式从一个文本文件中读取定长的气象观测站元数据：

hive> CREATE TABLE stations (usaf STRING, wban STRING, name STRING)
    > ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
    > WITH SERDEPROPERTIES (
    > "input.regex"="(\d{6}) (\d{5}) (.{29}) .*"
    > );

由上所示，SerDe可以使用WITH SERDEPROPERTIES子句设置额外属性，这里设置RegexSerDe独有的input.regex属性，指在反序列化期间将要使用的正则表达式模式，用来将数据行(row)中的部分文本转化为列的集合，利用Java的正则表达式语法识别一组组的括号来确定列（称为捕获组capturing group）。在该例子中，有三个捕获组：usaf（六位数的标识符）、wban（五位数的标识符）以及name（29个字符的定长列）。然后用以下LOAD DATA语句向表中输入数据（DATA后加上LOCAL关键字表示从本地磁盘导入，不加表示从HDFS中导入）：

hadoop fs -mkdir /input
hadoop fs -put /mnt/sda6/stations-fixed-width.txt /input
hive
hive> LOAD DATA INPATH ‘/input/stations-fixed-width.txt’ INTO TABLE stations;

从HDFS中导入数据文件到Hive的stations表的过程中会调用SerDe序列化数据文件，在下面的查询命令中会调用SerDe对表中数据反序列化，从而使每一行的字段都能正确解析出来：

hive> SELECT * FROM stations LIMIT 4;
OK
010000	99999	BOGUS NORWAY
010003	99999	BOGUS NORWAY
010010	99999	JAN MAYEN
010013	99999	ROST
Time taken: 0.392 seconds, Fetched: 4 row(s)

23.LOAD DATA操作实际上就是把要导入Hive表中的文件复制或移动到以表名为名称的目录中。也可以用INSERT语句把数据从一个Hive表上的数据填充到另一个表，或者也可以在新建表的时候使用CTAS语句结构，即CREATE TABLE ... AS SELECT。如果想把数据从一个RDBMS中直接导入Hive，需要用到Apache Sqoop组件。对于分区的表，可以使用PARTITION子句来指明数据要插入哪个分区，如下所示：

hive> INSERT OVERWRITE TABLE target PARTITION (dt=’2001-01-01’)
    > SELECT col1, col2 FROM source;

OVERWRITE关键字意味着target表或’2001-01-01’分区中的内容会被SELECT语句的结果替换掉。如果要直接向已有内容的表或分区添加记录，可以使用INSERT INTO TABLE。还可以使用动态分区插入(dynamic-partition insert)的方式，即在SELECT语句中通过使用分区值来动态指明分区，如下所示：

hive> INSERT OVERWRITE TABLE target PARTITION (dt)
    > SELECT col1, col2, dt FROM source;

24.在HiveQL中，可以把INSERT语句和FROM互相颠倒，把FROM子句放在最前面，效果相同，如下所示：

hive> FROM source
    > INSERT OVERWRITE TABLE target
    > SELECT col1,col2;

可以在同一个查询中使用多个INSERT子句，此时这种倒过来的语法会使查询含义更清楚，成为多表插入(multitable insert)，这样比使用多个单独的INSERT语句效率更高，因为只需要扫描一遍源表就可以生成多个不相交的输出，下面的例子根据气象数据集来计算多种不同的统计数据，这里只有一个源表records2，但有三个表用于存放针对这个源表的三个不同查询所产生的结果：

hive> FROM records2
    > INSERT OVERWRITE TABLE stations_by_year
    > SELECT year, COUNT(DISTINCT station)
    > GROUP BY year
    > INSERT OVERWRITE TABLE records_by_year
    > SELECT year, COUNT(1)
    > GROUP BY year
    > INSERT OVERWRITE TABLE good_records_by_year
    > SELECT year, COUNT(1)
    > WHERE temperature != 9999 AND quality IN (0, 1, 4, 5, 9)
    > GROUP BY year;

25.除了上面的LOAD DATA和INSERT，也可以使用CREATE TABLE ... AS SELECT创建新表。新表的列定义是从SELECT子句所检索的列导出的。在下面的例子中，创建的target表有两列，分别名为col1和col2，它们的数据类型与源表中对应的列相同：

hive> CREATE TABLE target
    > AS
    > SELECT col1, col2 FROM source;

CTAS操作是原子性的，如果SELECT查询由于某种原因失败，是不会创建新表的。

26.由于Hive使用读时模式，所以创建表以后可以灵活支持对表定义的修改，不过很多时候同样需要自己确保修改导入的数据是否符合新的结构。可以使用ALTER TABLE语句来重命名表：

hive> ALTER TABLE source RENAME TO target;

在更新表的元数据以外，该语句还会把表目录移到新名称所对应的目录下，即从/user/hive/warehouse/source被重命名为/user/hive/warehouse/target。而对于外部表该操作只会更新元数据，不会移动目录。Hive也允许修改列的定义、添加新的列，甚至用一组新的列替换表内已有的列，例如增加一个新列：

hive> ALTER TABLE target ADD COLUMNS (col3 STRING);

新列col3会添加到已有非分区列的后面，由于数据文件没有被更新，所以查询时col3的所有值会返回null。因为Hive表通常存储在HDFS上，HDFS的特性是不能在原文件上修改原有记录，只支持在文件末尾追加数据和生成新文件的增量(append)操作，所以更新表的内容需要创建一个定义了新列的新表，然后使用SELECT语句把数据填充进新表中。原来的数据类型可以用新的数据类型来进行解释，这就是修改表的元数据（如列名或数据类型）的直观效果。

27.DROP TABLE语句用于删除表的数据和元数据，如果是外部表，就只删除元数据，数据不会受到影响。如果要删除表内所有数据，但是保留表的定义，可使用TRUNCATE TABLE tablename语句。但该TRUNCATE语句对外部表不起作用，只能在Hive shell中使用dfs -rmr命令来直接删除外部表目录。另一种类似目的的方法是使用LIKE关键字创建一个与第一个表模式相同的新表，如下所示：

hive> CREATE TABLE new_table LIKE existing_table;

七、查询数据

28.Hive中可以使用ORDER BY子句对数据进行排序，该子句将对所有输入进行全排序，但很多时候不需要结果全局排序，只需要部分排序，此时可以换用Hive的SORT BY子句，该子句为每个reducer产生一个排序文件。有时候需要控制某个特定行到哪个reducer，这样是为了进行后续的聚集操作，此时可以使用DISTRIBUTE BY子句，下面的例子根据年份和气温对对象数据集进行排序，以确保所有具有相同年份的行最终都在同一个reducer分区中：

hive> FROM records
    > SELECT year, temperature
    > DISTRIBUTE BY year
    > SORT BY year ASC, temperature DESC;
1949   111
1949   78
1950   22
1950   0
1950   -11

后续的查询可以利用这个在同一文件中已经分好组并降序排好序的年份气温。如果SORT BY和DISTRIBUTE BY中所用的列相同，可以缩写为CLUSTER BY以同时指定两者所用的列。

29.与Hadoop Streaming类似，TRANSFORM、MAP和REDUCE子句可以在Hive中调用外部脚本和程序，例如下面的Python脚本和Hive使用该外部脚本的查询例子可以过滤掉质量指数不正常的气温读数行：

在运行查询之前，要用ADD FILE命令在Hive中注册脚本。通过该命令可以把需要的脚本文件传到HDFS集群上。查询本身把year，temperature和quality字段以制表符分隔的行的形式流式传递给脚本is_good_quality.py，并把制表符分隔的输出解析为year和temperature字段并输出。上面的示例没有使用reducer，如果要用查询的嵌套形式，可以指定map和reduce函数，使用MAP和REDUCE关键字，在这两个语句的位置使用SELECT TRANSFORM也有同样的效果，解析输出每年最大温度值的脚本max_temperature_reduce.py和查询例子如下所示：

上述查询先利用map脚本将map结果输出到map_output文件中，再利用reduce脚本处理map_output输出每年的温度最大值。

30.与直接使用MapReduce相比，使用Hive的一个好处是它大大简化了一些常用操作，例如连接。内连接是最简单的一种连接，两个输入表之间的每次匹配都会在输出表里生成一行。例如如下两个表，sales表列出人名及其所买商品的ID，things表列出商品的ID和名称：

可以用下面的命令对两个表进行内连接：

ON子句后括号内的谓词用于连接，Hive只支持等值连接(equijoin)，意味着在连接谓词中只能使用等号。可以在括号内连接谓词中使用AND关键字分隔多个表达式来连接多个列，还可以在命令中使用多个JOIN tablename ON子句来连接多个表，但JOIN子句中表的顺序很重要，一般最好将最大的表放在最后。上面的连接也可以在FROM后列出要连接的表，然后在WHERE子句中指定连接条件，下面的例子会获得和上面查询同样的效果：

hive> SELECT sales.*, things.* FROM sales, things
    > WHERE sales.id = things.id;

单个连接用一个MapReduce作业实现。但如果多个连接的连接条件中使用了相同的列，则平均每个连接可以用少于一个MapReduce作业来实现。可以在查询前使用EXPLAIN关键字查看Hive将为该查询使用多少个MapReduce作业，如下所示：

hive> EXPLAIN
    > SELECT sales.*, things.*
    > FROM sales JOIN things ON (sales.id = things.id);

EXPLAIN的输出中有很多查询执行计划的详细信息，包括抽象语法树、Hive执行各阶段的依赖图以及各阶段信息等。一个阶段像MapReduce作业文件移动的一个操作。

31.外连接与内连接不同，后者必须要两张表的对应两行能对的上才会输出，外连接可以输出在连接表中匹配不了的数据行。例如前面的sales表中Ali没有出现在输出中，因为所买商品ID没有在things表中找到匹配行，如果把连接类型改为LEFT OUTER JOIN，则左侧表(sales)中即使有在要连接的右侧表(things)中无法匹配的数据行，查询还是会返回左侧表中的每一行，如下所示：

此时返回了Ali所在数据行，因为这一行在右侧表things中没有匹配的行，所以things表的对应列为NULL。Hive也支持右外连接(right outer join)，即与左连接相反，该例子中右侧表things中所有商品都会输出，即使有的商品没有被买过无法匹配左侧表sales，如下所示：

最后还有一种全外连接(full outer join)，即两个连接表中所有行在输出中都会出现：

32.下面的IN子查询可以查找things表中在sales表中出现过的所有行：

hive> SELECT * FROM things WHERE things.id IN (SELECT id FROM sales);

半连接LEFT SEMI JOIN子句可以有同样的效果，但要遵循右表(sales)只能在ON子句中出现的规则，比如不能在SELECT表达式中引用右表，如下所示：

33.在左右表的连接过程中，如果其中一个表小到可以放入内存，Hive会把较小的表放入每个mapper的内存中执行连接操作，称为map连接。执行map连接不使用reducer，因此这个查询对RIGHT或FULL OUTER JOIN无效，因为只有在对所有输入进行聚集的步骤（即reduce）才能检测到哪个数据行无法匹配。map连接可以用于分桶的表，因为处理左侧表的桶的mapper加载右侧表中对应的桶即可执行连接，此时需要将下面的选项启用：

hive> SET hive.optimize.bucketmapjoin=true;

34.子查询是内嵌在一个SQL语句中的另一个SELECT语句。Hive对子查询的支持很有限，它只允许子查询出现在FROM后的子句中，或一些特殊情况下的WHERE子句中，下面例子可以找到每年每个气象站最高气温的均值，这里FROM中的子查询用于计算每个气象站-日期组合数据中的最高气温，然后外层查询使用AVG聚集函数计算这些最高读数的均值，外层查询像访问表那样访问子查询的结果，所以必须为子查询赋予一个别名mt：

hive> SELECT station, year, AVG(max_temperature)
    > FROM (
    > SELECT station, year, MAX(temperature) AS max_temperature
    > FROM records
    > WHERE temperature != 9999 AND quality IN (0, 1, 4, 5, 9)
    > GROUP BY station, year
    > ) mt
    > GROUP BY station, year;

35.视图是一种用SELECT语句定义的虚表(virtual table)，并不以磁盘上实际存储的表形式呈现给用户，一般呈现上会有所限制，即用户只能访问被授权可以看到的表的子集。在Hive中创建视图时并不把视图本身实际存储到磁盘上，视图的SELECT语句只是在执行引用视图的语句时才会执行。如果一个视图要对基表进行大规模变换，或者视图的查询会频繁执行，最好还是新建一个表，使用CREATE TABLE ... AS SELECT语句把视图需要的内容存储到新表中。下面的例子中需要查找每年各个气象站气温最大值的均值，首先为所有有效温度记录(quality字段为特定值）创建一个视图，如下所示：

hive> CREATE VIEW valid_records
    > AS
    > SELECT * FROM records
    > WHERE temperature != 9999 AND quality IN (0, 1, 4, 5, 9);

创建视图时并不执行查询，查询只是存储在metastore中，“SHOW TABLES;”命令的结果也会包括视图。可以使用“DESCRIBE EXTENDED valid_records;”命令查看某个视图的详细定义信息。下面的例子为每个观测站每年的最高气温创建第二个视图，该视图基于valid_record视图：

hive> CREATE VIEW max_temperatures (station, year, max_temperature)
    > AS
    > SELECT station, year, MAX(temperature) FROM valid_records
    > GROUP BY station, year;

对于最高气温列这里显式列出了名称，因为该列是一个聚集表达式，如果不指明列名Hive会自己起一个别名（例如_c2）。也可以在SELECT语句中使用AS子句来为列命名。有了上面两个视图，执行查询的例子如下所示：

hive> SELECT station, year, AVG(max_temperature)
    > FROM max_temperatures
    > GROUP BY station, year;

MapReduce在将Hive的视图查询转化为job时，job的数目与不加视图查询的个数是一样的，例如每个GROUP BY命令划分一个job。Hive的视图是只读的，无法通过视图为基表加载或插入数据。

八、UDF

36.有时用户需要的查询无法使用Hive提供的内置函数来表示，用户可以编写自己定义的函数(user-defined function，UDF)使Hive插入用户写的处理代码并在查询时调用它们。UDF必须用Java编写，其他语言可以使用SELECT TRANSFROM...USING语句来使用其他语言的处理脚本。Hive中有三种UDF：

(1)普通UDF，作用于单个数据行，且产生一个数据行作为输出，大多数函数例如数学函数与字符串函数都属于这一类。

(2)用户自定义的聚集函数(user-defined aggregate function，UDAF)，接受多个输入数据行，并产生一个输出数据行，例如COUNT和MAX等。

(3)用户自定义的表生成函数(user-defined table-generating function，UDTF)，作用于单个数据行，且产生多个数据行（即一张表）作为输出。例如，一个表只有一列（名为x），其中包含字符串数组，它的表定义如下：

hive> CREATE TABLE arrays (x ARRAY<STRING>)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY '01'
    > COLLECTION ITEMS TERMINATED BY '02’;

用LOAD DATA命令导入arrays表中的数据如下：

例如使用名为explode的UDTF对表进行变换，该函数为数组中每一项输出一行，带有UDTF的SELECT语句在使用时有些限制，例如不能检索额外的列表达式，使用方式如下：

37.下面的例子显示了如何编写和使用名为strip的UDF，用于剪除字符串尾字符：

package Hive;

import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class Strip extends UDF {  //剪除字符串首尾字符的UDF
    private Text result=new Text();

    public Text evaluate(Text str){  //必须重写evaluate()方法，去除输入首尾两端的空白字符
        if(str==null){
            return null;
        }

        result.set(StringUtils.strip(str.toString()));
        return result;
    }

    public Text evaluate(Text str,String stripChars){  //去除字符串首尾两端中包含在指定字符集合(stripChars)里的任何字符
        if(str==null){
            return null;
        }
        result.set(StringUtils.strip(str.toString(),stripChars));
        return result;
    }
}

其中evaluate()方法不是由API预定义的，因为它可接受的参数个数、数据类型及返回值的数据类型都是不确定由用户决定的，所以Hive会检查UDF是否可以找到和函数调用相匹配的evaluate()方法，因此用户必须重写该函数。在IDE终端中使用mvn package命令将该程序编译封装成JAR包并存到HDFS后，在metastore中注册该函数并使用CREATE FUNCTION语句创建和起名，如下所示：

hadoop fs -mkdir /path
hadoop fs -put /mnt/sda6/UDF.jar /path
hive
hive> CREATE FUNCTION strip AS 'Hive.Strip'
    > USING JAR ‘hdfs://localhost/path/UDF.jar';

上面命令中AS后面是“包名.类名”的形式，JAR后是该jar包在HDFS上的URI。现在可以像使用Hive的内置函数一样使用该UDF，如下所示：

在CREATE后加入TEMPORARY关键字可以创建一个只在Hive会话期间有效的临时函数，并不会在metastore中存储下来，如下所示：

hive> ADD JAR hdfs://localhost/path/UDF.jar;
hive> CREATE TEMPORARY FUNCTION strip AS 'Hive.Strip';

使用临时函数时，最好在主目录中创建一个.hiverc文件，用于包含定义这些UDF的命令，该文件会在每个Hive会话开始时自动运行。要想在Hive启动时调用ADD JAR，可以在指定Hive启动时查找附加JAR文件的路径，该路径会被加入Hive的类路径（包括task的类路径），对于每次运行Hive时自动添加UDF库很有用。有两种指明路径的方法：

(1)在hive命令后传递--auxpath选项：

hive --auxpath hdfs://localhost/path/UDF.jar

(2)在运行Hive前设置$HIVE_HOME/conf/hive-env.sh文件中的HIVE_AUX_JARS_PATH环境变量，附加路径可以是一个逗号分隔的JAR文件路径或包含JAR文件的目录。

38.聚集函数比普通UDF难写，因为值是在块内进行聚集的，这些块可能分布在很多任务中，因此实现时要能够把部分的聚集值组合成最终结果，例子如下所示，用于计算一组整数的最大值：

package Hive;

import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
import org.apache.hadoop.io.IntWritable;

public class Maximum extends UDAF {  //计算一组整数最大值的UDAF，需要继承UDAF类

    //需要包含一至多个嵌套的实现了UDAFEvaluator接口的静态类，用于提供整数最大值计算的UDAF重载
    public static class MaximumIntUDAFEvaluator implements UDAFEvaluator{
        private IntWritable result;

        public void init() {  //必须实现该方法，负责初始化计算函数并设置它的内部状态
            result=null;
        }

        //必须实现该方法，每次对一个新值进行聚集计算时都会调用该方法，括号中接受的参数和Hive中被调用函数的参数是对应的
        public boolean iterate(IntWritable value){
            if(value==null){  //检查传入参数值是否为空，如果是就忽略
                return true;
            }
            if(result==null){
                result=new IntWritable(value.get());  //result变量初始化为第一个输入的值
            }else{  //result已赋过值，则与当前输入值比较并覆盖为更大值
                result.set(Math.max(result.get(),value.get()));
            }
            return true;
        }

        public IntWritable terminatePartial(){  //必须实现该方法，Hive需要部分聚集计算结果时会调用，返回目前为止已经计算的当前状态和结果
            return result;
        }

        public boolean merge(IntWritable other){  //必须实现该方法，在Hive决定要合并一个部分聚集值和另一个部分聚集值时调用，括号内输入对象必须和terminatePartial()返回类型一致
            return iterate(other);
        }

        public IntWritable terminate(){  //必须实现该方法，Hive需要最终聚集结果时会调用
            return result;
        }
    }
}

将上面程序打包成JAR文件并上传到HDFS上后，执行该函数的命令如下所示：

hive> CREATE TEMPORARY FUNCTION maximum AS 'Hive.Maximum'
    > USING JAR 'hdfs://localhost/path/UDAF.jar';
hive> SELECT maximum(temperature) FROM records;

下图显示了计算函数的处理流程：

39.上面的示例中部分聚集结果可以使用和最终结果相同的数据类型(IntWritable)来表示，但是对于更复杂的聚集函数例如计算一组double值均值的UDAF就不能这样，因为在数学算式上两个部分的double平均值合并成最终均值是不行的。作为替代，可以用一个数对（即目前已处理的double值累积和以及目前已经处理过的数的个数）来表示部分聚集结果。代码例子如下所示：

package Hive;

import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
import org.apache.hadoop.hive.serde2.io.DoubleWritable;

public class Mean extends UDAF {  //计算一组double值平均值的UDAF，需要继承UDAF类

    //需要包含一至多个嵌套的实现了UDAFEvaluator接口的静态类，用于提供double平均值计算的UDAF重载
    public static class MeanDoubleUDAFEvaluator implements UDAFEvaluator{
        public static class PartialResult{
            double sum;
            long count;
        }

        private PartialResult partial;

        public void init(){  //必须实现该方法，负责初始化计算函数并设置它的内部状态
            partial=null;
        }

        //必须实现该方法，每次对一个新值进行聚集计算时都会调用该方法，括号中接受的参数和Hive中被调用函数的参数是对应的
        public boolean iterate(DoubleWritable value){  //Hive中的DoubleWritable类可以自动序列化和反序列化
            if(value==null){  //检查传入参数值是否为空，如果是就忽略
                return true;
            }
            if(partial==null){
                partial=new PartialResult();  //将自己的结果初始化为新的结果对象
            }
            partial.sum+=value.get();  //将输入累加起来形成总和
            partial.count++;  //每读到一个输入，数字个数就加1
            return true;
        }

        public PartialResult terminatePartial(){  //必须实现该方法，Hive需要部分聚集计算结果时会调用，返回目前为止已经计算的当前状态和结果
            return partial;
        }

        //必须实现该方法，在Hive决定要合并一个部分聚集值和另一个部分聚集值时调用，括号内输入对象必须和terminatePartial()返回类型一致
        public boolean merge(PartialResult other){
            if(other==null){  //如果没有来自其他分区的部分结果，就不合并
                return true;
            }
            if(partial==null){  //如果自己的部分结果不存在，则将部分结果初始化为新的结果对象
                partial=new PartialResult();
            }
            partial.sum+=other.sum;  //从其他分区的累加结果也加过来合并
            partial.count+=other.count;  //其他分区积累的数字个数也累加过来
            return true;
        }

        public DoubleWritable terminate(){  //必须实现该方法，Hive需要最终聚集结果时会调用
            if(partial==null){
                return null;
            }
            return new DoubleWritable(partial.sum/partial.count);  //返回总的平均值
        }
    }
}

40.Hive的运行机制如下所示：