Hive：构建于 Hadoop 之上、让你像写 SQL 一样编写 MapReduce 程序

标签：name -- 分区 Hadoop Hive MapReduce 数据 select

Hive 介绍

本次我们来聊一聊 Hive，它是由 Facebook 开源的一款基于 Hadoop 的数据仓库工具，用于解决海量结构化日志的数据统计与分析。Hive 通过将结构化的数据映射为一张表，并提供类 SQL 查询功能，让开发人员能够编写 SQL 进行数据分析。

在介绍 Hadoop 的时候我们说过，使用 MapReduce 编程会很麻烦，但程序员很熟悉 SQL，于是 Hive 就出现了。它可以让我们像写 SQL 一样来进行编程，会自动将 SQL 进行转化，翻译成 MapReduce。所以从这里可以看出，Hive 就相当于一个翻译官，可以让开发者不用直接面对 MapReduce。我们只需要写 SQL 即可，至于 SQL 语句怎么对应成 MapReduce，那么是 Hive 做的事情，不需要我们关心。

当然这里说的 SQL，更准确来说应该是 HQL（Hive SQL），它和 SQL 非常类似，可以说你只要会 MySQL、PostgreSQL 等关系型数据库，那么使用 Hive 没有任何障碍。

因此 Hive 处理的数据仍存储在 HDFS 上，但是将数据抽象成了一张张表。而底层分析数据采用的也依旧是 MapReduce，只是将类 SQL 语言和 MapReduce 做了一层映射，可以帮我们把 SQL语句翻译成 MapReduce。执行程序运行在 YARN 上，这是显而易见的，因为实际计算的仍是 MapReduce。

那么问题来了，既然数据被抽象成了表，那么这些表结构、字段信息存在哪呢？所以除了 HDFS 之外，Hive 还依赖关系型数据库，比如 MySQL。在将 HDFS 上的数据抽象成一张表时，这些表信息都存在关系型数据库中，而这些表信息也叫作元信息、或者元数据。

所以 Hive 的数据分为两部分：

元数据：存储在关系型数据库中
实际数据：存储在 HDFS 中

Hive 的优点

操作接口采用类 SQL 的语法，提供快速开发的能力（简单、容易上手）
避免了去写 MapReduce，减少开发人员的学习成本
Hive 的执行延迟比较高，因此 Hive 擅长于数据分析、对实时性要求不高的场合
还是因为 Hive 的延迟比较高，使得 Hive 的优势在于处理大数据，对于处理小数据没有优势
Hive 支持用户自定义函数，用户可以根据自己的需求来实现自己的函数

Hive 的缺点

Hive 的 HQL 表达能力比较有限，比如：迭代式算法无法表达、数据挖掘方面不擅长
Hive 的效率比较低，虽然 Hive 能自动地生成 MapReduce 作业，但是通常情况下不够智能化
以及 Hive 调优比较困难，粒度较粗

Hive 的架构

然后再来看看 Hive 的架构长什么样子？

我们来解释一下这每一个部分。

用户接口 Client

用于访问 Hive 的客户端，比如 CLI（Hive Shell）。然后 Hive 还提供了一个服务叫 HiveServer2，它允许多种编程语言使用 Apache Thrift 进行连接，进而操作 Hive。比如 Python 的 PyHive，它便是使用 Thrift 通过 HiveServer2 与 Hive 交互。当然除了 Thrift，还有 JDBC / ODBC，一般用于 Java 和 C++。

元数据 MetaStore

元数据包括：数据库（默认是 default）、表名、表的拥有者、列 / 分区字段、表的类型（是否是外部表）、表数据的所在目录等。

Hive 自带了一个 derby 数据库，用作 MetaStore。但由于 derby 只支持单客户端访问，因此在生产环境中我们一般会换成 MySQL。

驱动器 Driver

可以理解为整个 Hive 框架，它由以下几部分组成。

SQL 解析器（SQL Parser）：将 SQL 字符串转换成抽象语法树（AST）
语义分析（Semantic Analyzer）：将 AST 进一步划分为 QeuryBlock
逻辑计划生成器（Logical Plan Gen）：基于语法树生成逻辑计划
逻辑优化器（Logical Optimizer）：对逻辑计划进行优化
物理计划生成器（Physical Plan Gen）：根据优化后的逻辑计划生成物理计划
物理优化器（Physical Optimizer）：对物理计划进行优化
执行器（Execution）：执行该计划，得到查询结果并返回给客户端

不管 Hive 本身有多么复杂，它本质上做的事情用一句话便可描述：对 SQL 进行分析，生成相应的 MapReduce，操作 HDFS 上的数据。

Hive 和数据库之间的差异

由于 Hive 采用了类似于 SQL 的查询语言 HQL（Hive Query Language），因此很容易将 Hive 理解为数据库。但是从结构上看，Hive 除了和数据库拥有类似的查询语言之外，再无相似之处。下面我们就从多个维度，来比较一下这两者。

1. 查询语言

由于 SQL 被广泛应用在数据仓库中，因此专门针对 Hive 的特性设计了类 SQL 查询语言 HQL，熟悉 SQL 开发的开发者可以很方便的使用 Hive 进行开发。

2. 数据的存储位置

Hive 建立在 Hadoop 之上，因此实际数据存储在 HDFS 中，而数据库则是可以将数据保存在块设备或本地文件系统中。

3. 数据更新

由于 Hive 针对的是数据仓库，而数据仓库的内容是读多写少的，因此 Hive 不建议对数据进行改写，所有的数据都应该在加载的时候确定好，而数据库中的数据通常是需要进行修改的。

4. 索引

Hive 在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描，因此也没有对数据中的某些 key 建立索引。Hive 要访问数据中满足条件的特定值时，需要暴力扫描整个数据集，因此访问延迟较高。而由于 MapReduce 的引入，Hive 可以并行访问数据，因此即使没有索引，对于大数据量的访问，Hive 仍可以体现出优势。

数据库中，通常会针对一个或几个列建立索引，因此对于少量的具有特定条件的数据访问，数据库可以有很高的效率，较低的延迟。

所以由于 Hive 访问数据的延迟较高，决定了它不适合在线数据查询。

5. 执行引擎

Hive 中大多数查询的执行是通过 Hadoop 提供的 MapReduce 实现的，而数据库通常有自己的执行引擎。

6. 执行延迟

Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce 框架，由于 MapReduce 本身具有较高的延迟，因此在利用 MapReduce 执行 Hive 查询时，也会有较高的延迟。

相对的，数据库的执行延迟就比较低了，当然这个低是有条件的，即数据的规模较小。当数据的规模大到超过了数据库的处理能力时，Hive 的并行计算会更有优势。

7. 可扩展性

由于 Hive 建立在 Hadoop 之上，所以 Hive 的可扩展性和 Hadoop 是一样的（世界上最大的 Hadoop 集群在 Yahoo，2009 年的规模在 4000 个节点左右）。而数据库由于 ACID 语义的严格限制，扩展性非常有限，目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有 100 台左右。

8. 数据规模

由于 Hive 建立在集群上，可以利用 MapReduce 执行并行计算，因此能够支持很大规模的数据。对应的，数据库支持的数据规模较小。

Hive 安装

下面我们来安装 Hive，由于它是 Apache 的顶级项目，所以官网是 hive.apache.org。我们直接进入下载页面，点击下载指定的版本即可，这里我下载的是最新版 3.1.3。

注意：使用 Hive 之前，要确保当前节点已经安装了 Java 和 Hadoop。

下载完成后，上传到服务器，然后解压到 /opt 目录，再将其 bin 目录配置到环境变量中。

以上是 Hive 的目录结构，其中 bin 目录负责存放一些启动文件，conf 目录负责存放配置文件，lib 目录负责存放依赖的 Jar 包，examples 负责存放一些测试用例等等。

修改配置、启动 Hive

然后我们修改配置文件，首先修改 hive-env.sh。不过 conf 目录下面没有这个文件，但是有 hive-env.sh.template，所以直接 cp 一下即可。

在里面需要指定两个配置，分别是 Hadoop 的安装目录（HADOOP_HOME）和 Hive 的配置文件所在目录（HIVE_CONF_DIR）。

修改完之后我们配置 MetaStore，前面说了，Hive 默认使用 derby 存储元数据，我们需要换成关系型数据库，这里就用 MySQL 吧。关于 MySQL 的安装就不演示了，我这里已经安装好了。然后修改 hive-site.xml，但 conf 下面没有该文件，因此我们需要手动创建，然后在里面写上如下内容：

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>

    <!-- 指定 MySQL 连接的 URL，后续将元数据存在 metastore 这个库中 -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
    </property>

    <!-- 指定连接 MySQL 的驱动，后面还需要单独下载 -->
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <!-- 如果 MySQL 版本不超过 5.7，那么要写 com.mysql.jdbc.Driver -->
        <!-- 我这里是 MySQL 8.0.18，因此要写 com.mysql.cj.jdbc.Driver -->      
        <value>com.mysql.cj.jdbc.Driver</value>
    </property>

    <!-- 指定连接 MySQL 的用户 -->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>

    <!-- 指定连接 MySQL 的密码 -->
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>密码
        <value>123456</value>
    </property>

    <!-- Hive 元数据存储版本验证 -->
    <property>
        <name>hive.metastore.schema.verification</name>
        <value>false</value>
    </property>
    
    <!-- 在启动时自动创建数据库中缺失的表、列和约束等 -->
    <property>
        <name>datanucleus.schema.autoCreateAll</name>
        <value>true</value>
    </property>

</configuration>

元数据存储换成了 MySQL，那么 Hive 要有相应的驱动去连接，而 Hive 没有内置连接 MySQL 的驱动，所以需要单独下载。我们可以使用菜鸟教程提供的下载路径，下载完成后丢到 Hive 的 lib 目录中即可。

然后我们初始化元数据库，执行命令 schematool -dbType mysql -initSchema。初始化成功之后，我们启动 Hive，看看效果。

成功启动 Hive，显示当前只有一个 default 库。注意这里的 default，它是 Hive 的 default，不是 MySQL 的 default。相对于用户来说，Hive 有一个默认的数据库 default，而这个 default 库的相关信息存储在 MySQL 中。我们看一下 MySQL 内部都存了什么？

我们看到 metastore 这个库已经自动创建了，然后里面有很多的表，其中表 DBS 记录了 Hive 创建的数据库，TBLS 记录了 Hive 创建的表，COLUMNS_V2 记录了 Hive 表中的列。当然 metastore 中还有很多其它表，而这些表是做什么的，暂时不用关心，总之 MySQL 中的元数据不要擅自改动，Hive 会自动维护它。

然后我们创建一张表测试一下，注意：这里创建表可不是在 MySQL 中创建，而是在 Hive 中，创建之后的表的元信息会保存在 MySQL 中。

然后我们通过 Hadoop 的 webUI 查看一下，创建的数据默认位于 /user/hive/warehouse 中（MySQL 的表 DBS 也记录了路径信息）。如果你不想使用默认路径，可以在 hive-site.xml 中指定 hive.metastore.warehouse.dir 来自定义存储目录。

我们在 Hive 中创建的表 girls，在 HDFS 中会对应一个目录，该目录里面有一个文件 000000_0，存储了具体的数据。我们将其下载下来，看看里面都有什么？

这不就是刚才写入的数据吗，而且我们看到里面只有纯数据，像什么字段名、类型统统都没有。所以这就是 Hive，它的数据分为两部分：实际数据和元数据，其中实际数据存储在 HDFS 中，至于表的元信息则存在 MySQL 中。然后查找的时候，会先去 MySQL 中查找对应的元信息，然后在 HDFS 上找到对应的数据。所以整体流程还是比较简单的，至于图中的 0x01 则不用管，它就是一个分隔符。

如果我们将 MySQL 给卸载掉，那么元数据会消失，但实际数据则不会，因为它是存在 HDFS 上的。当我们重新安装之后，只需要重新执行建表逻辑即可，依旧可以 select 出数据，因为数据一直都在 HDFS 上。

下面我们再来做个实验，用 Python 创建一个文件并写入数据：

>>> import os
>>> os.system(f"echo '2\x01satori\x0116\x01jireidenn' >> 1.txt")
0
>>> os.system(f"echo '3\x01koishi\x0116\x01jireidenn' >> 2.txt")
0

再将这两个文件上传到 HDFS 中。

所以在 Hive 上创建一张表，相当于在 HDFS 上创建一个目录，该目录中的文件存放实际导入的数据。然后查询该表时，等价于去 HDFS 上查询该目录下的文件内容。因此我们手动导入的文件里面的内容，也被打印出来了。

通过修改配置文件、启动 Hive，然后做一些小测试，我们算是已经认识了 Hive。总之过程很简单，分为两步：先在 MySQL 中找元数据，再根据元数据找 HDFS 上的实际数据。

配置服务让第三方访问

我们目前是通过启动一个 hive shell 的方式访问的，但生产上肯定是要通过代码、或者数据库管理工具去操作 Hive，就目前来说可以办到吗？

答案显然是不行的，因为我们目前没有启动任何一个和 Hive 有关的服务，没有服务也就没有相应的端口，所以在外界根本没有任何东西可以连接。不像 HDFS，由于启动了 NameNode 和 DataNode，在外界可以通过 50070 端口连接，但是 Hive 的话则不行，因为没有启动相关的服务。

因此命令行输入 hive 启动一个 shell 只是方便测试，而如果想让第三方使用 Hive 上的数据，那么就必须给 Hive 启动一个服务，这个服务就是之前说的 HiveServer2。因此在远程访问 Hive 数据时，客户端并未直接访问 Hadoop 集群，而是由 Hivesever2 代理访问。

我们修改配置文件 hive_site.xml，加入如下内容。

<property>
    <!-- 指定 hiveserver2 绑定的地址 -->
    <name>hive.server2.thrift.bind.host</name>
    <value>xiaoyun</value>
</property>

<property>
    <!-- 指定 hiveserver2 绑定的端口，默认也是 10000 端口 -->
    <name>hive.server2.thrift.port</name>
    <value>10000</value>
</property>

<property>
    <!-- 该参数需要解释一下，hiveserver2 在代理访问 Hadoop 集群的时候，是以什么用户访问的呢 -->
    <!-- 是启动 hiveserver2 进程的用户，还是连接 hiveserver2 的客户端所在节点的登录用户 -->  
    <!-- 答案由 hive.server2.enable.doAs 参数决定，该参数表示是否启动 hiveserver2 的用户模拟功能 -->  
    <!-- 若启用，则 hiveserver2 会模拟成客户端的登录用户去访问 Hadoop 集群的数据 -->  
    <!-- 若不启用，则 hiveserver2 会直接使用启动用户去访问 Hadoop 集群的数据，默认是开启的 -->      
    <name>hive.server2.enable.doAs</name>
    <value>true</value>
</property>

关于 hive.server2.enable.doAs 参数，如果不好理解的话，我们画一张图就简单了。

在生产环境，推荐开启用户模拟功能，因为开启后才能保证各用户之间的权限隔离。不过还没结束，hivesever2 的模拟用户功能，依赖于 Hadoop 提供的 proxy user（代理用户功能），只有 Hadoop 中的代理用户才能模拟其它用户的身份访问 Hadoop 集群。因此需要将 hiveserver2 的启动用户设置为 Hadoop 的代理用户，做法是修改 Hadoop 的 core-site.xml，在里面添加如下内容：

<!-- 配置任意节点的 root 用户可以作为代理用户 -->
<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
</property>

<!-- 配置 root 用户可以代理任意用户 -->
<property>
    <name>hadoop.proxyuser.root.users</name>
    <value>*</value>
</property>

<!-- 配置 root 用户可以代理任意组 -->
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
</property>

配置完成后，重启 Hadoop 集群。然后启动 hiveserver2 服务：hive --service hiveserver2，这依旧是一个前台进程。

hiveserver2 启动成功后，我们用 Python 来连接它，这里我将服务器的 10000 端口暴露出来，然后在本地机器上使用 impala 模块进行连接。该模块在使用前需要先安装：

pip install thrift_sasl
pip install thrift
pip install impyla

安装成功之后，我们来操作一波。

我们看到此时数据就成功的访问了，怎么样，是不是很简单呢？所以通过 hive --service hiveserver2 的方式向外界暴露一个服务，支持外界来访问相关的数据。但我们注意一下里面的 auth_mechanism 参数，它表示 Hive 的认证机制，可以在 hive-site.xml 中通过 hive.server2.authentication 设置，然后使用 impala 连接时指定的 auth_mechanism 参数的值要与之匹配，这个后面会说。当然默认情况下是 PLAIN，你也可以指定为 NOSASL，表示不认证。不过绝大部分生产环境中我们都是使用 Kerberos 认证，这个时候需要你本地有 Keytab 文件，然后将 auth_mechanism 指定为 GSSAPI，这个后续再聊。

另外在具体编程时，我们更习惯使用 SQLAlchemy，因此也可以创建一个引擎去连接 Hive。

impala 这个包很久没更新了，它适配的还是 1.4 版本的 SQLAlchemy，而我当前的版本是 2.0.23，所以会抛出一些警告，不过使用是没有问题的。

连接 Hive 除了使用 impala 之外，还可以使用 pyhive，安装方式如下：

pip install thrift_sasl
pip install thrift
pip install pyhive

我们测试一下 pyhive，看看能不能访问：

答案是可以的，以上就是在 Mac 上使用 Python 连接 Hive 的方式，至于 Windows 如何连接可以自己测试一下。不过说实话，Hive 服务一般都部署在公司内网中，并且采用 Kerberos 认证，想在外界连接基本不可能。

因此最后再补充一下，如何在 Linux 上使用 Python 连接。

# 需要先安装一些系统库
# CentOS
yum install cyrus-sasl cyrus-sasl-devel cyrus-sasl-lib
# 如果是 Ubuntu
apt-get install libsasl2-modules libsasl2-dev libsasl2-2

# 安装 Python 第三方库，不管使用 impala 还是 pyhive，都需要安装以下两个包
pip install thrift_sasl
pip install thrift
# 最后 impala 和 pyhive 二者选其一即可
pip install impyla
pip install pyhive

我们测试一下：

访问没有问题，另外当前 hiveserver2 是前台启动的，我们改成后台启动。

nohup hive --service hiveserver2 >> /dev/null&

后续可以通过 Hive Shell 访问，也可以通过 Python 访问。另外再补充一点，目前输入 hive 的话，会进入交互式界面，但我们也可以在不进入交互式的情况下执行 SQL 语句。

通过 hive -e "SQL 语句" 便可以在不进入 shell 的情况下，执行 SQL。另外除了执行语句之外，我们还可以把语句写在文件里，通过 hive -f 文件名，来执行文件里面的多条 SQL 语句。

常见属性配置

关于 Hive 还有很多其它配置，我们来说一说。首先在进入 hive shell 的时候，左侧的提示符是 hive >，它没有标识当前位于哪一个库。如果希望标识当前所在的库，比如 default，那么就显示 hive (default) >，这个时候可以修改 hive-site.xml。

<property>
    <!-- 显示当前位于哪一个库 -->
    <name>hive.cli.print.current.db</name>
    <value>true</value>
</property>

<property>
    <!-- 查询出来的数据默认是不显示表头的，可以让它显示 -->
    <name>hive.cli.print.header</name>
    <value>true</value>
</property>

Hive 的运行日志默认位于 /tmp 目录，我们需要修改它所在的路径，不然节点重启后，日志就没了。

cp hive-log4j2.properties.template hive-log4j2.properties
# 然后修改 hive-log4j2.properties 里面的 property.hive.log.dir 
# 比如 property.hive.log.dir=/opt/apache-hive-3.1.3-bin/logs

Hive 还支持设置 JVM 堆内存，新版本的 Hive 启动的时候，默认申请的 JVM 堆内存大小为 256M。JVM 堆内存申请的太小，会导致后期开启本地模式、执行复杂的 SQL 时经常报错：java.lang.OutOfMemoryError: Java heap space。因此最好提前调整一下 HADOOP_HEAPSIZE 这个参数，我们修改 hive-env.sh：

# 改成 1024 或 2048
export HADOOP_HEAPSIZE=1024

以上就是 Hive 的安装以及相关配置，如果你不是纯粹的大数据运维团队的话，安装和配置方面应该不需要你来负责。我们作为开发，重点还是要掌握 Hive 的语法，能够熟练使用 HQL 操作里面的数据才是第一位的，下面我们就来学习一下 Hive 的 SQL 语法。

Hive 的 DDL

学习 Hive 和学习 MySQL 是类似的，首先来看看 Hive 的 DDL。所谓 DDL，无非就是操作数据库、操作表、以及操作表字段，我们一个一个说。

数据库

在 Hive 中如何创建一个数据库呢？

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

注意里面的 LOCATION，它表示数据存储在 HDFS 上的路径，不指定的话默认为 ${hive.metastore.warehouse.dir}/database_name.db。

hive.metastore.warehouse.dir 可以在 hive-site.xml 中进行设置，默认是 /user/hive/warehouse/。

hive> create database languages location '/languages';
OK
Time taken: 0.731 seconds
hive>

languages 数据库就创建好了，并且数据会存储在 HDFS 的 /languages 中，我们看一下 webUI。

显示 languages 目录已经创建，然后再看一下 MySQL，因为 Hive 的库信息都存在 MySQL 的 DBS 表中。

元数据也显示正常。

说完了创建，再来看看查询。

-- LIKE 支持通配符，* 代表任意个任意字符，| 表示或的关系
SHOW DATABASES [LIKE 'identifier_with_wildcards'];

我们举例说明：

然后也可以查看某个数据库的具体信息：

-- 语法：DESCRIBE DATABASE [EXTENDED] db_name;
hive> describe database languages;
OK
languages               hdfs://localhost:9000/languages root    USER
Time taken: 0.229 seconds, Fetched: 1 row(s)

在创建数据库的时候还可以通过 WITH DBPROPERTIES 指定一些附加属性（我们之后再说），如果在获取数据库信息时指定了 EXTENDED，那么这些附加属性也会显示。

说完了添加和查询，再来看看修改。用户可以使用 ALTER DATABASE 命令修改数据库的信息，其中能够修改的信息包括：dbproperties、location、owner user。需要注意的是：修改数据库的 location，不会改变当前已有表的路径信息，而是改变后续创建的新表的父目录。

-- 修改 dbproperties
ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);

-- 修改 location
ALTER DATABASE database_name SET LOCATION hdfs_path;

-- 修改owner user
ALTER DATABASE database_name SET OWNER USER user_name;

可以自己测试一下。

最后是删除数据库，语法如下：

DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

RESTRICT 表示严格模式，若数据库不为空，则会删除失败，默认为该模式。CASCADE 表示级联模式，若数据库不为空，则会将库中的表一并删除。

比较简单，和 MySQL 比较类似。如果想切换数据库，那么也是使用 USE 语句。

表

说完操作数据库的 DDL，再来看看操作表的 DDL，不过我们需要先来了解 Hive 都有哪些类型。

还是比较简单的，基本都是熟悉的类型名称。像 tinyint、smallint 不怎么常用，毕竟是大数据框架，这两老铁用的实在是不多。然后 string 类型就相当于 MySQL 的 text 类型，可以存储任意长度的字符串（长度可达 2GB）。而日期类型可以是 timestamp，表示年月日时分秒，也可以是 date（图中忘记写了），表示年月日。

以上都是一些基础类型，Hive 还支持更复杂的复合类型，比如：struct、map、array，我们分别解释。

struct 类型可以想象成 C 的 struct，每个结构体内部会定义成员以及类型。在 Hive 中类型定义方式为：struct <a: int, b: string>，然后在访问的时候通过属性操作符 . 来访问。
map 是一组键值对的集合，定义方式为 map<string, int>，它类似于 Python 的字典。
array 是相同类型的变量的集合，定义方式为 array<string>，访问时基于索引访问即可，下表从 0 开始。

然后 Hive 的原子数据类型也支持隐式转换：

任何一个整数类型都可以转化为范围更广的类型，比如 tinyint 可以转为 int、int 可以转为 bingint
所有整数类型、float、string 都可以隐式地转化为 double
tinyint、smallint、int 都可以转为 float
boolean 类型不可以隐式转化为其它任何类型

如果无法隐式转换，那么也可以通过 cast 函数显式转换，比如 cast('1' as int) 将字符串转成整数。如果强制转换失败，那么表达式返回 NULL。

创建表

下面我们来创建一张表，展示一下这些类型，注意：创建表时可以指定非常多的属性。

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name   
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format] 
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]

看着让人有点头皮发麻，不过我们先不说那么多，因为里面有一部分属性和后面的内容相关，我们一点点来。假设要创建这样一张表，里面有 4 个字段，信息如下：

name：姓名，显然是 varchar(255) 类型
hobby：兴趣，一个人可以有很多兴趣，所以这是一个 array
privacy_info：私人信息，比如体重->float, 奖学金->int，所以这是一个结构体
score：成绩，语文:90, 数学:89, 英语:92，这可以是一个 map

那么怎么定义这张表呢？

/*
创建表时可以指定可选的 TEMPORARY 或 EXTERNAL
CREATE TEMPORARY TABLE: 
    表示创建临时表，该表只在当前会话可见，会话结束，表会被删除
    
CREATE EXTERNAL TABLE: 
    表示创建外部表，默认情况下创建的是内部表（管理表），那么内部表和外部表有什么区别呢？
    如果是内部表，那么 Hive 会完全接管该表，包括元数据和 HDFS 数据
    删除内部表，会同时删除元数据和 HDFS 数据

    如果是外部表，那么 Hive 只会接管元数据，而不会接管 HDFS 数据
    当删除外部表时，只会删除元数据，HDFS 数据会依旧保留
    因此外部表适用于对数据进行长期存储和分析，在多个应用之间共享数据
*/
create table student (
    name varchar(255),
    hobby array<varchar(255)>,
    privacy_info struct<weight: float, scholarship: int>,
    score map<string, int>
)
-- 还没完，还有一个很重要的事情就是分割符
-- 对于每一个字段来说，我们在插入数据的时候，是使用逗号分割
-- 但是对于集合来说，有多个元素，我们要怎么分开呢，因此需要指定分割符

-- 字段之间，使用逗号分割，默认的
row format delimited fields terminated by ','

-- 对于复合类型来说，我们使用 _ 分割里面的每一个元素
-- 比如 hobby 可以是 `唱歌_跳舞_RAP`，info 可以是 `95.4_1000`，score 可以是 `语文:90_数学:95_英语:89`
collection items terminated by '_'

-- 对于 map，我们使用 : 分隔键值对
map keys terminated by ':'

-- 每一行使用 \n 分隔
lines terminated by '\n'

-- 文件存储格式，常用的有 textfile（默认值），sequence file，orc file、parquet file 等等
-- STORED AS 'textfile'

-- 指定表所对应的 HDFS 路径，默认值为 ${hive.metastore.warehouse.dir}/db_name.db/table_name
-- LOCATION '/user/warehouse/...'

-- 配置表的一些 KV 键值对参数
-- TBLPROPERTIES('x' = 'y')
;

我们执行建表语句，然后导入数据，数据位于 student.txt 中，内容如下：

小花,吃饭_画画,90.2_1000,语文:89_数学:90_英语:90
小强,宠物_音乐_滑雪,132.5_3000,语文:98_数学:95_英语:97

然后通过 load data local inpath "文件名" into table 表名的方式将文件导入到表中。

数据成功导入，而且也查询到了。那么问题来了，像 array、map、struct，如何获取它们内部指定的元素呢？

-- hobby[1] 表示获取数组中索引为 1 的元素
-- privacy_info.weight 表示获取结构体中成员为 weight 的元素
-- score['语文'] 表示获取 map 中 key 为 '语文' 的元素
select name, hobby[1], privacy_info.weight, score['语文'] from student;
OK
小花    画画    90.2    89
小强    音乐    132.5   98
Time taken: 0.473 seconds, Fetched: 2 row(s)

关于创建表，Hive 也支持通过已有的表进行创建，比如 create table table_name as select ...。

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name 
[COMMENT table_comment] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

利用 select 查询语句返回的结果直接建表，表的结构和查询语句的结构保持一致，且包含 select 查询返回的内容。

Hive 还允许使用 create table table_name like exist_table_name 语法来复刻一张已存在的表结构，但不包含数据。

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[LIKE exist_table_name]
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]

以上就是创建表的方式，可以自己测试一波。

查看表

表创建完了，如何查看呢？

SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards'];

like 支持通配符，其中 * 表示任意个任意字符，| 表示或的关系。

也可以使用 DESCRIBE FORMATTED 来查看某张表的详细信息：

以上就是查看表信息。

修改表

对表重命名：

ALTER TABLE table_name RENAME TO new_table_name

给表添加字段，可同时添加多个：

ALTER TABLE table_name ADD COLUMNS (列名 类型, 列名 类型 ...)

修改表字段：

ALTER TABLE table_name CHANGE COLUMN col_old_name col_new_name column_type 
[COMMENT col_comment] [FIRST|AFTER column_name]

当然也可以修改表的类型，比如将内部表改成外部表。

-- 其它属性也是可以修改的
ALTER TABLE table_name SET TBLPROPERTIES('EXTERNAL' = 'TRUE');

修改表很少用，大致了解一下即可。

删除表

最后是删除表：

# 如果要清空表数据（必须是内部表），那么可以用 TRUNCATE
DROP TABLE [IF EXISTS] table_name;

以上就是关于表的相关操作，在实际开发中我们一般不会对表结构本身做太多的修改。

数据的导入与导出

介绍完数据库和表的创建，我们来看看如何导入与导出数据。

数据的导入

数据导入的话可以使用 insert 语句，这个比较简单，和关系型数据库是类似的。

INSERT INTO 表(col1, col2, ...) 
VALUES (val1, val2, ...), (val1, val2, ...)

还可以将一张表的记录插入到另一张表中：

-- 将表 2 的记录插入到表 1 当中，当然还可以筛选字段、指定行数
INSERT INTO 表1 AS SELECT * FROM 表2;

和普通关系型数据库不一样的是，Hive 除了 insert into 之外，还有一个 insert overwrite，表示将表清空之后再插入，而 insert into 是直接追加。

此外表不存在的时候也可以插入数据，会先创建表。

-- 根据从表 2 选出的字段创建表 1，然后再将数据导入到表 1 中，表 1 的字段类型和表 2 保持一致
-- 当然这里还可以执行更加复杂的查询, 然后根据其结果创建新表
CREATE TABLE 表1 AS SELECT * FROM 表2;

以上这些都比较简单，但如果有一个大文件，按照目前的方式，我们需要先把文件内容读取出来，然后再 insert 到表中。那么问题来了，可不可以直接将文件导入到表中呢？显然是可以的，因为之前已经用过了。

LOAD DATA [LOCAL] INPATH '数据路径' [OVERWRITE] 
INTO TABLE table_name [PARTITION (partition_col1 = val1, ...)]

LOAD DATA: 表示加载数据;
LOCAL: 表示从本地加载, 否则从HDFS上加载;
INPATH: 表示数据加载的路径;
OVERWRITE: 表示覆盖表中已有数据, 否则追加;
INTO TABLE: 表示加载到哪张表中;
PARTITION: 表示上传到指定分区, 需要是分区表, 关于什么是分区表稍后介绍;

数据的导出

当我们执行了一个 SELECT 语句，如何将查询的结果导出到文件中呢？

INSERT OVERWRITE [LOCAL] DIRECTORY "本地目录（不存在则创建）"
[ROW FORMAT row_format] 
[STORED AS file_format] 
SELECT * FROM table_name;

如果不加 LOCAL，则是导出到 HDFS 上面。当然查询不一定是 SELECT * ，还可以是其它复杂查询，这里就用 SELECT * 代替了。

然后查看一下本地文件，虽然我们指定的是 1.txt，但很明显它是一个目录，因为一张表对应一个目录，该目录里面才是具体存放的数据文件。

当然分区表是个例外，分区表里面还是目录，也就是每一个分区，后续介绍分区表。

我们看到数据都在里面了，但问题是连在一起了，显然这样的数据也不好处理。因此在导出数据的时候，最好指定分隔符，语法跟创建表的时候是一样的。

这里没有指定 LOCAL，那么会在 HDFS 上创建，我们看一下结果。

这样的话，导出的数据是不是长得就好看一些了呢？

除了 INSERT 导出之外，还可以使用 export 命令。

EXPORT TABLE table_name TO 'HDFS 路径'；

注意：EXPORT 导出之后，指定的路径下会存在两个目录：_metadata 和 data，对应元数据和实体数据。然后除了 EXPORT 之外，还有 IMPORT，负责导入数据，刚才没有说，因为它需要和 EXPORT 搭配使用。并不是任何一个存在的 HDFS 目录，都可以 IMPORT，只有 EXPORT 导出的数据才可以 IMPORT。

IMPORT [EXTERNAL] TABLE table_name FROM 'HDFS 路径'

此时 IMPORT 会读取指定 HDFS 路径下的 _metadata 和 data，将数据完全恢复出来，因为同时包含了元数据和实体数据，所以恢复出来的结果是一样的。因此 EXPORT 和 IMPORT 主要用于两个 Hadoop 平台集群之间的 Hive 表迁移。

在 IMPORT 时，如果 table_name 不存在时，会自动创建。但如果 table_name 已存在，则列信息必须和原始表保持一致。

数据查询

数据查询可以说是重中之重，毕竟作为一款数据分析框架，我们更多还是使用它的查询功能。而且如果你不是专门做大数据运维的话，那么应该只会用到查询功能，因为环境搭建，数据库以及相关表的创建，会由专门的人负责。

虽然数据查询很重要，但毕竟 HQL 和 SQL 是高度相似的，而且学习 Hive 说明肯定也熟悉关系型数据库。所以关于数据查询，我们还是会从零开始介绍，但一切特别简单的知识点就不啰嗦了。

首先创建一张表，结构如下：

create table staff
(   
    id         int     comment  '编号',
    name       string  comment  '姓名',
    city       string  comment  '所在城市',
    company    string  comment  '所在公司',
    email      string  comment  '个人邮箱',
    join_date  date    comment  '入职日期'
)
row format delimited fields terminated by ',';

然后我们用 Python 的 faker 模块生成一些伪数据，导入进去，然后看一下长什么样子。

这里我生成了 20 条假数据，里面的人名、地名都是虚构的。

使用 SELECT 语句初步探索

在 staff 表中，存储了员工的信息，我们现在要找到 id、name、join_date。

SELECT id, name, join_date FROM staff
/*
 id     name    join_date
1001    赵莉    2005-12-18
1002    谭杨    1987-03-16
1003    何波    2012-04-14
1004    段琳    2007-02-20
1005    蔡淑华  2022-12-15
1006    杨柳    1976-07-26
1007    张超    2008-09-04
1008    张秀荣  1994-12-05
1009    杨强    1984-06-15
1010    黄坤    1987-06-13
*/

上面只显示了部分字段的数据，这种查询表中指定字段的操作在关系运算中被称为投影（Projection），投影是针对表进行的垂直选择，保留需要的字段用于生成新的表。以下是投影操作的示意图：

投影操作中包含一个特殊的操作，就是查询表中所有的字段，并且可以使用 * 表示全部字段。

SELECT * FROM staff

Hive 在解析该语句时，会使用表中的字段名进行扩展：

-- 等价于
SELECT id, name, city, company, email, join_date FROM staff;

注意：星号可以用来快速编写查询语句，但在实际项目中不要使用这种写法。因为应用程序可能并不需要所有的字段，使用 * 会返回过多的无用数据；另外当表结构发生变化时，* 返回的内容也会发生改变。

除了查询表的字段之外，SELECT 语句还支持扩展的投影操作，包括基于字段的算术运算、函数和表达式等。

结果没有问题，但返回的字段名变了，这里给出了一个 _c1。为了明确含义，我们也可以通过 AS 关键字指定别名。

这样的话，返回的字段名就不会给人造成困惑了。另外除了给字段指定别名，还可以给表指定别名。

SELECT * FROM staff AS s

返回的结果是一样的，因此对于单表查询来讲，给表起不起别名没任何影响。但当多个表 JOIN 的时候，为了区分 SELECT 后面的字段到底是哪一个表的字段，我们在选择的时候就不会只输入字段名了，而是会通过 "表名.字段名" 的方式，可如果表名比较长，那么给表起别名就很有意义了。

补充：as 其实是可以省略的，可以使用一个空格或者多个空格代替，比如：

SELECT name, CONCAT(city, ':', name) city_name FROM staff

在语句中使用别名不会修改数据库中存储的表名或者列名，别名只在当前语句中生效。

另外在 Hive 中可以像其它编程语言一样使用注释，注释能够方便我们理解代码的作用，但不会被执行。Hive 即支持单行注释，也支持多行注释。

SELECT id, -- id
name -- 姓名
/* 
  我们要从 staff 表中查询 id 和 name
*/  
FROM staff

SELECT ... FROM ... 是最基本的查询形式，但有时候我们会看到一种更简单的查询：只有 SELECT 子句，没有 FROM 子句的查询。

这种形式的查询语句通常用于快速查找信息，或者当作计算器使用，类似于关系型数据库一样，它并不属于 Hive（HQL）的语法标准，而是 Hive 框架自己的扩展。我们不需要针对某张具体的表，可以直接通过 Hive 对指定的数据进行计算。但是很少这么做，因为这样的话，数据需要我们手动输入，那这样的话还要 Hive 做什么？直接拿其它的编程语言手动计算不就行啦。

通过查询条件实现数据过滤

我们上面学习了如何使用 SELECT 和 FROM 查询表数据，不过在实际应用中通常并不需要返回表的全部数据，而只需要找出满足某些条件的结果，比如某个部门的员工或者某个产品最近几天的销售情况。在 Hive 中，可以通过查询条件实现数据的过滤。

-- 返回 id 为 1005 的员工
SELECT * FROM staff WHERE id = 1005

WHERE 位于 FROM 之后，用于指定一个或者多个过滤条件，只有满足条件的数据才会返回，其它数据将被忽略。比如我们筛选所有的字段，但并不是每一行都要，只有当该行的 id 字段等于 1005 时，我们才要。

在 Hive 中，WHERE 子句也被称为谓词（Predicate），当然 SQL 里面也是如此。

这种通过查询条件过滤数据的操作在关系运算中被称为选择（Selection）。它是针对表进行的水平选择，保留满足条件的行用于生成新的表。以下是选择操作的示意图：

在查询条件中，使用最多的就是数据的比较运算。

比较运算符可以比较两个数值的大小，包括字符、数字以及日期类型的数据。下表列出了 Hive 中的各种比较运算符：

这些运算符都好理解，我们来看一个日期数据的比较操作，我们想知道哪些员工在 2018 年 1 月 1 日之后入职，可以使用以下查询：

SQL 中的 date '2018-1-1' 表示将日期格式的字符串转成日期，但在 Hive 中我们也可以直接输入字符串，会存在一个隐式转化，当然绝大部分关系型数据库也是支持的。此外我们也可以使用 cast('2018-1-1') as date 这种方式，之前说过的。另外关于日期格式的字符串，更标准的写法应该是 '2018-01-01' 这种，当然 '2018-1-1' 也没问题，因为这两者在转成日期的时候是没有区别的。

再来看看 between，说白了 id between a and b，等价于 id >= a and id <= b。

SELECT id, name FROM staff WHERE id BETWEEN 1002 AND 1004
/*
 id   name
1002  谭杨
1003  何波
1004  段琳
*/

然后是 IN 运算符，它可以用于查找数组中的值，比如查询 id 为 1003 或 1007 的员工。

SELECT * FROM staff WHERE id in (1003, 1005)
/*
 id     name    join_date
1003    何波    2012-04-14
1007    张超    2008-09-04
*/

只要匹配列表中的任何一个值，都会返回结果。IN 运算符还有一个常见的用途就是子查询的结果匹配，我们后面会说。

空值（NULL）是 Hive 中的一个特殊值，代表了缺失或者未知的数据。与其它编程语言（如 Java）不同，Hive 中判断一个值是否为空不能使用等于或者不等于。当然我们这里的数据在生成的时候，忘记设置空值了，里面没有空数据。

总之，判断是否为空应该是 IS，如果值为空，那么 IS NULL 返回真、IS NOT NULL 返回假；如果表达式的值不为空，IS NULL 返回假，IS NOT NLL 返回真。

不能使用 = 或者 != 来判断是否为空，将一个值与一个 NULL 进行数学比较，结果仍然是 NULL；即使是将两个 NULL 进行比较，结果也是 NULL。因此以下运算的结果均为 NULL，用于查询条件的话不会返回任何结果：

null = 5
null = null
null != null

-- 该 SQL 不会返回任何数据，因为和 NULL 进行数学比较，得到的还是 NULL
-- 而 NULL 为假，所以不会返回任何数据
SELECT * FROM staff WHERE id != NULL

总之 Hive 的语法和 SQL 可以说高度相似，上面的一些用法和普通的 SQL 也是一样的。因此如果你会 SQL，那么学 Hive 简直是轻而易举。

如果仅仅能够指定单个过滤条件，就无法满足复杂的查询需求，所以 Hive 中还有用于构建复杂条件的逻辑运算符。借助于逻辑代数中的逻辑运算，Hive 提供了三个逻辑运算符。

AND：逻辑与，只有当两边都为真，结果才为真；

-- 选择 id > 1005，并且 join_date 小于 2000-01-01 的记录
SELECT id, join_date FROM staff WHERE id > 1005 AND join_date < '2000-01-01'
/*
 id    join_date
1006  1976-07-26
1008  1994-12-05
1009  1984-06-15
1010  1987-06-13
*/

OR：逻辑或，只要有一边为真，结果就为真；

-- 选择 id > 1005，或者 join_date 小于 2000-01-01 的记录
SELECT id, join_date FROM staff WHERE id > 1005 OR join_date < '2000-01-01'
/*
 id    join_date
1002  1987-03-16
1006  1976-07-26
1007  2008-09-04
1008  1994-12-05
1009  1984-06-15
1010  1987-06-13
*/

NOT：逻辑非，将结果取反，也就是真变假、假变真，但是 NULL 取反的结果还是 NULL；

SELECT id, join_date FROM staff WHERE NOT (id > 1005 OR join_date < '2000-01-01')
/*
 id    join_date
1001  2005-12-18
1003  2012-04-14
1004  2007-02-20
1005  2022-12-15
*/

对于逻辑运算符 AND 和 OR，Hive 会使用短路运算（short-circuit evaluation）。也就是说，只要前面的表达式能够决定最终的结果，就不执行后面的计算，从而提高运算效率。因此，以下语句不会产生除零错误：

SELECT *
FROM staff
WHERE 1 = 0 AND 1/0 = 1;

SELECT *
FROM staff
WHERE 1 = 1 OR 1/0 = 1;

而 NOT 运算符可以结合其它的运算符一起使用，用于对查询条件的结果取反：

not between: 位于范围之外
not in: 不再数组之中
not like: 不匹配某个模式, like 运算符用于字符串的模糊查找, 将在后面介绍
not exists: 子查询中不存在结果, 关于子查询和 exists, 也在后面介绍

补充：在筛选数据的时候会有重复数据，可以使用 distinct 关键字进行去重。

SELECT DISTINCT city FROM staff

distinct 位于 select 之后，而不是像其它过滤条件一样位于 where 之后。其次，查询结果中重复的记录只会出现一次。与 distinct 相反的是 all，用于返回不去重的结果，但我们通常不需要加上 all 关键字，因为它是默认的行为。另外为了消除重复值，Hive 需要对结果进行排序，然后扫描重复值，因此大量数据的重复值处理可能会降低查询的速度。

如何在 Hive 中进行模糊查找

之前介绍了利用 where 子句中的查询条件过滤数据，包括比较运算符、逻辑运算符以及空值判断等。同时，我们也提到了 like 运算符可以用于字符串的模糊查找，下面就来讨论一下 Hive 中的模糊匹配。

当需要查找的信息不太确定时，例如只记住了某个员工姓名的一部分，可以使用模糊查找的功能进行搜索。Hive 提供了两种模糊匹配的方法：like 运算符和 rlike正则表达式。

比如查找姓张的员工：

该语句使用了一个新的运算符：like，用于指定一个模式，并且返回匹配该模式的数据。like 运算符支持两个通配符：

%：百分号可以匹配任意个任意字符
_：下划线可以匹配一个任意字符

以下是一些模式和匹配的字符串：

like 'en%'：匹配以 "en" 开头的字符串，例如 "english languages"、"end"
like '%en%'：匹配包含 "en" 的字符串，例如 "length"、"when are you"
like '%en'：匹配以 "en" 结尾的字符串，例如 "ten"、"when"
like 'Be_'：匹配以 "Be" 开头，再加上一个任意字符的字符串，例如 "Bed"、"Bet"
like '_e%'，匹配以一个任意字符加上 "e" 开头的字符串，例如"her"、"year"

如果想要执行相反的操作，返回不匹配某个模式的数据，可以使用 not like 运算符。

由于 % 和 _ 是 like 运算符中的通配符，那么当查找内容本身包含了 % 或者 _ 时，应该如何指定模式呢？例如想要知道哪些数据包含了 "10%"（百分之十，而不是以 10 开始的字符串），这种情况需要用到转义字符（escape character）。

转义字符可以对通配符 % 和 _ 进行转义，将它们当作普通字符使用。默认的转义字符为反斜杠（\），因此想选择字符串中包含 "25%" 的，就应该这么写：

-- 倒数第二个 % 的前面有 \，表示转义，所以 \% 就是普通的 %
SELECT xxx FROM xxx WHERE xxx LIKE "%25\%%";

然后在使用 like 查找数据时，还需要注意的一个问题就是大小写。虽然汉字不需要区分大小写，但英文字母却有大小写之分，而 like 是区分大小写的。因此面对这种情况，我们还可以使用更强大的正则表达式，通过 rlike 实现。

正则表达式用于检索或者替换符合某个模式（规则）的文本，很多的编程语言和编辑工具都提供了正则表达式搜索和替换，比如文本编辑器 Notepad++。正则表达式本身的相关语法就不说了，网上一搜一大堆。

通过 (?i) 可以让正则忽略大小写，但要注意 Hive 里面使用的正则表达式基于 Java，在 Java 中正则表达式的 \w 是不匹配中文的，而 Python 是匹配的，这一点有所差别。

.{2} 可以匹配，但 \w{2}则不行。

使用 ORDER BY 对数据进行排序显示

上面我们讨论了如何使用 like 运算符和正则表达式进行文本数据的模糊查找，但返回的数据的顺序未必是我们希望的，有些时候我们希望返回的数据能够按照一定的顺序。而如果想要查询的结果按照某种规则进行排序，则需要使用 order by 子句。

SELECT col1, col2, ...
FROM table_name
ORDER BY col [ASC | DESC], col2 [ASC | DESC], ...;

order by 用于指定排序的字段，asc 表示升序排序（ascending），desc 表示降序排序（descending），默认为升序排序。如果指定多个排序字段，那么首先按照第一个字段进行排序，如果第一个字段的值相同，再按照第二个字段进行排序，以此类推。

对于升序排序，数字按照从小到大的顺序排列，字符按照编码的顺序排列，日期时间按照从早到晚的顺序排列；降序排序正好相反。然后还有空值，空值默认为最小，升序排序在最上面，降序排序在最下面。

什么是函数？如何利用函数提高数值计算的效率

Hive 主要的功能就是对数据进行处理和分析，为了避免重复造轮子，提高数据处理的效率，Hive 为我们提供了许多标准的功能模块：函数（Function）。那什么是函数呢？函数是一种具有某种功能的模块，可以接收零个或多个输入值，并且返回一个输出值。而函数主要分为两种类型：

标量函数（scalar function）：针对每个输入参数，返回一个输出结果，例如 ABS(x) 可以计算 x 的绝对值。
聚合函数（aggregate function）：基于一组数据进行计算，返回一个输出结果，例如 AVG 函数可以计算一组数据的平均值。

我们先介绍标量函数，聚合函数将会在后续进行介绍。为了方便学习，我们将常见的标量函数分为以下几类：数值函数、字符串函数、日期函数以及类型转换函数。

数值函数

先来看看数值函数。

比较简单，可以自己测试一下。

常见函数之文本数据处理

介绍完 Hive 中常见的数值函数，接下来我们继续学习用于处理文本数据的字符串函数，像字符串的拼接、大小写转换、子串的查找和替换等，Hive 都是支持的。

ascii(x)：返回字符串 x 的第一个字符的 ascii 码

select ascii('abc'); -- 97

lower(x)：返回字符串 x 的小写格式

select lower('maTSUri'); -- matsuri

upper(x)：返回字符串 x 的大写格式

select upper('maTSUri'); -- MATSURI

concat(x1, x2, x3, ...)：使用空格将多个字符串拼接起来

select concat('万', '明', '珠'); -- 万明珠
select concat('万', NULL, '明', '珠'); -- NULL
select concat('万', 123, '明', '珠'); -- 万123明珠

concat(sep, x1, x2, x3, ...)：使用分隔符 sep 将多个字符串拼接起来

select concat_ws('-', '万', '明', '珠'); -- 万-明-珠

substr(x, start, count)：截取字符串

-- 从第 4 个字符截取到结尾
select substr('人生不如意十之八九', 4); -- 如意十之八九
-- 从第 4 个字符截取 4 个字符
select substr('人生不如意十之八九', 4, 4); -- 如意十之

-- 除了正着数, 还可以反着数
-- 从倒数第 4 个字符截取 4 个字符
select substr('人生不如意十之八九', -4, 4); -- 十之八九

instr(x, y)：查找字符串 y 在字符串 x 中首次出现的位置

select instr('人生不如意十之八九', '十之八九'); -- 6
-- 查找不到返回0

length(x)：计算字符串 x 的长度

-- 计算的是字符个数
select length('人生不如意十之八九'); -- 9

printf(format, x, ...)：格式化函数

select printf("%s ||| %s ||| %s", id, name, city) from staff limit 5;
/*
1001 ||| 赵莉 ||| 秀华县
1002 ||| 谭杨 ||| 超县
1003 ||| 何波 ||| 岩市
1004 ||| 段琳 ||| 英市
1005 ||| 蔡淑华 ||| 阳市
*/

trim(x)：取出字符串两边的空格，ltrim只去除左边空格，rtrim只去除右边空格

select printf("'%s'", " abc "), printf("'%s'", trim(" abc "));
-- ' abc '	'abc'

replace(x, old_string, new_string)：将字符串 x 中的 old_string 替换为 new_string。

select replace("abcabcabc", "abc", "ABC"); -- ABCABCABC

regexp_replace(x, pattern, new_string)：将字符串 x 中，符合正则 pattern 的部分替换为new_string，因此该函数也可以实现 replace。

select regexp_replace("satori", "s", "S"); -- Satori
select regexp_replace("satori koishi", "sa|ko", "**"); -- **tori **ishi

regexp_extract(x, pattern, index)：将字符串 x 按照 pattern 进行分组，返回第 index 个组

select regexp_extract("satori", "sa(.*)", 1);  -- tori
select regexp_extract("satori", "sa(.)(.*)", 2);  -- ori

repeat(x, count)：将字符串 x 重复 count 次

select repeat("万明珠", 5); -- 万明珠万明珠万明珠万明珠万明珠

split(x, delimiter)：将字符串 x 按照 delimiter 进行分割

select split("2020-01-01", "-"); -- ["2020","01","01"]

以上就是 Hive 中常见的字符函数，掌握这些函数可以方便我们对文本数据进行清洗和转换等处理。除了这些函数之外，其实Hive里面还提供了大量的字符处理函数，当我们需要实现某种操作时，可以先查找数据库的文档，避免重复实现已有的功能。

日期相关的函数

下面我们来看看 Hive 中如何处理时间，主要使用以下几个函数。

date_format：格式化时间

-- 年月日时分秒的话是：yyyy-MM-dd HH:mm:ss
select date_format('2018-08-09', 'yyyy-MM-dd');

date_add：时间和天数相加

select date_add('2018-08-09', 5), date_add('2018-08-09', -5);
-- 2018-08-14  2018-08-04

里面直接传递一个字符串和一个数值即可，当然还有 date_sub 函数表示相减，其实个人觉得有一个就够用了。

datediff：时间和时间相减

select datediff('2018-08-09', '2017-12-30'); -- 222

日期指定部分获取，可以通过 year、month、day、hour、minute、second 获取日期指定的部分。

select year('2018-08-09'), day('2017-12-30'); -- 2018  30

to_date：将 timestamp 类型转成 date

select to_date("2017-01-01 12:33:55"); -- 2017-01-01
select cast("2017-01-01 12:33:55" AS date); -- 2017-01-01

我们看到这些函数可以直接作用在字符串上，但字符串必须是 "2020-01-01" 这种，如果是 "2020/01/01" 这种格式会报错。

当然还有一些其它函数：

current_timestamp：当前的时期加时间
current_date：当前日期
from_unixtime：将时间戳转成 timestamp

集合函数

再来补充一下集合函数，在 Hive 中 array、map、struct 属于集合类型，为了操作方便，Hive 也针对集合类型提供了一些函数。

数组相关函数

array(val1, val2, ...)：创建一个数组。
array_contains：判断数组中是否包含某个元素。
sort_array：对数组进行排序。
size：查看数组的元素个数。

select array(2, 3, 4, 1); -- [2,3,4,1]
select array_contains(array(2, 3, 4, 1), 2); -- true
select sort_array(array(2, 3, 4, 1)); -- [1,2,3,4]
select size(array(2, 3, 4, 1)); -- 4

map 相关函数

map(key1, val1, key2, val2, ...)：创建一个 map。
map_keys：返回 map 所有的 key。
map_values：返回 map 所有的 value。
size：查看 map 的长度。

select map('a', 1, 'b', 2); -- {"a":1,"b":2}
select map_keys(map('a', 1, 'b', 2)); -- ["a","b"]
select map_values(map('a', 1, 'b', 2)); -- [1,2]
select size(map('a', 1, 'b', 2)); -- 2

struct 相关函数

struct(val1, val2, ...)：创建结构体，成员名称默认为 col1、col2，....。
named_struct(field1, val1, field2, val2, ...)：创建结构体，但同时指定名称。

select struct(1, 2, 3); --{"col1":1,"col2":2,"col3":3}
select named_struct("a", 1, "b", 2, "c", 3);  -- {"a":1,"b":2,"c":3}

关于函数就说到这儿，你也可以通过 show functions 命令查看 Hive 都内置了哪些函数，通过 desc function [extended] some_func 查看该函数的具体用法。

使用 CASE 表达式实现 IF-THEN-ELSE 逻辑

下面我们介绍一种为 Hive 查询增加逻辑处理功能的方法：CASE 表达式。CASE 表达式可以根据不同条件产生不同的结果，实现类似于编程语言中的 IF-THEN-ELSE 逻辑功能。例如根据员工的 KPI 计算相应的涨薪幅度，根据考试成绩评出优秀、良好、及格等。

CASE 表达式支持两种形式：简单 CASE 表达式和搜索 CASE 表达式，先来看简单 CASE 表达式。

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  [ELSE default_result]
END

首先计算 expression 的值，然后依次与 WHEN 列表中的值（value1，value2，…）进行比较，找到第一个相等的值并返回对应的结果（result1，result2，…）。如果没有找到相等的值，则返回 ELSE，如果没有指定 ELSE，返回 NULL 值。

-- 随便举个栗子吧
select age,
       case age
            when 30 then '三十'
            when 35 then '三十五'
            when 40 then '四十'
            else '不是三十、三十五、四十'
        end as age1
from people;

然后是搜索 CASE 表达式。

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  [ELSE default_result]
END

按照顺序依次计算每个分支中的条件（condition1，condition2，…），找到第一个结果为真的分支并返回相应的结果（result1，result2，…）。如果没有任何条件为真，返回 ELSE，如果没有指定 ELSE，返回 NULL 值。

所有的简单 CASE 表达式都可以替换为等价的搜索 CASE 表达式，我们刚才的例子就可以改写成如下：

select age,
       case 
            when age = 30 then '三十'
            when age = 35 then '三十五'
            when age = 40 then '四十'
            else '不是三十、三十五、四十'
        end as age1
from people;

我们看到简单 CASE 表达式仅能判断相等的情况，如果是更复杂的情况，则需要使用搜索 CASE 表达式，也就是 CASE 后面什么也不用加，直接把条件写在 WHEN 后面，这样不仅能判断相等的情况，还可以进行更复杂的判断。

select age,
       case
            when age < 30 then '青年'
            when age < 45 then '中年'
            when age < 60 then '老年'
            else '耄耋'
        end as age1
from people;

CASE 表达式除了可以用于查询语句的 SELECT 列表，也可以出现在其他子句中，例如 WHERE、ORDER BY 等，举个例子。

select age, level
from people
where level not in ('工程师', '经济师', '会计师');

-- 但是问题来了，如果我们希望 level 为 null 的记录也算在内的话，该怎么做呢?
-- where level not in ('工程师', '经济师', '会计师', null); 可以吗?
-- 以上这个条件等价于: where level = '工程师' or level = '经济师' or level = '会计师' or level = null
-- 但是 level = null 是永远不会成立的


-- 所以我们应该这么做
select age, level
from people
where case
          when level not in ('工程师', '经济师', '会计师') then true
          when level is null then true
          end;

ORDER BY 也是类似的，可以自己尝试。

聚合函数对数据进行汇总

汇总分析是数据报表中的基本功能，例如销售额度的汇总统计、计算学生的平均身高以及标准差等。为此，Hive 提供了许多具有汇总功能的聚合函数。在 SQL 中，聚合函数（Aggregate Function）用于对一组数据进行汇总计算，并且返回单个分析结果。常见的聚合函数包括：

count，统计查询结果的行数
avg，计算一组数值的平均值
sum，计算一组数值的总和
max，计算一组数据中的最大值
min，计算一组数据中的最小值
collect_list，将一组数据组成一个列表
collect_set，将一组数据组成一个集合（会去重）

select max(join_date), min(join_date) from staff;
-- 2022-12-15    1976-07-26

select collect_list(id) from staff;
-- [1001,1002,1003,1004,1005,1006,1007,1008,1009,1010]

聚合函数在单独使用时，会将所有的数据作为一个整体进行统计；但在实际应用中，我们通常需要将数据按照某些规则进行分组，然后分别进行汇总统计。例如，按照部门计算员工的平均月薪，按照不同的产品和渠道统计销售金额等。为了实现这种分组统计的功能，需要将聚合函数与分组操作（GROUP BY）一起使用。

select sum(amount) as total_amound, sale_date 
from sales group by sale_date;

上面的 SQL 将 sale_date 相同的归为一组，然后对 amount 进行求和，当然这些都比较简单了，和 MySQL 是类似的。

如果 group by 字段包含空值，那么所有的空值会被分为一组。

数据在分完组后还可以进行过滤，我们知道 where 条件可以用于过滤表中的数据，但如果要对分组之后的数据进行过滤，是不是也可以使用 where 实现呢？答案是不可以，where 子句中不允许使用聚合函数。因为 Hive 的 where 子句在 group by 子句之前执行（关于执行顺序，我们后面会说），它是针对 from 中的表进行数据过滤。也就是说，where 子句执行时还没有进行分组操作，所以 where 后面不能跟聚合函数。

为了支持基于汇总结果的过滤，Hive 提供了 having 子句，同时要求 having 必须与 group by 一起使用。

select sum(amount) as total_amound, sale_date 
from sales group by sale_date having sum(amount) > 200000;

因此在 SQL 中可以使用 where 子句对表进行过滤，同时使用 having 对分组结果进行过滤。

从性能的角度来说，应该尽量使用 where 条件过滤掉更多的数据，而不是等到分组之后再使用 having 进行过滤。但如果因业务需要，只能基于汇总之后的结果进行过滤，就另当别论了。

空字段赋值

如果在查询某个字段的时候，希望给空值一个默认值该怎么办呢？所以 Hive 有一个 nvl 函数：nvl(column, value)。如果字段为 NULL，那么返回指定的值，如果字段不为 NULL，返回字段的值。如果都为 NULL，那么就只能返回 NULL 了。

当然 nvl 里面还可以是两个字段，如果第一个字段的值为 NULL，就用第二个字段的值代替。

-- 因为 name 没有为空的，所以当 id 为 1005 时，给 name 设置为空
select nvl(case when id = 1005 then null else name end, "空空如也")
from staff;
/*
赵莉
谭杨
何波
段琳
空空如也
杨柳
张超
张秀荣
杨强
黄坤
*/

我们看到空值被替换成了指定的值，当然这么做有点刻意了，并且也能发现 nvl 完全可以使用 case when 来实现。

列转行

假设我们有这样的数据：

name      hobby
椎名真白   画画-吃饭-跳舞
古明地觉   唱歌-洞察人心-调戏恋恋

我们要将其变成下面这种格式：

name      hobby
椎名真白    画画
椎名真白    吃饭
椎名真白    跳舞
古明地觉    唱歌
古明地觉    洞察人心
古明地觉    调戏恋恋

下面我们就来创建这样的表，然后导入数据。

下面来操作一波。

hive> select * from girls_info;
OK
椎名真白        画画-吃饭-跳舞
古明地觉        唱歌-洞察人心-调戏恋恋
Time taken: 0.141 seconds, Fetched: 2 row(s)

-- 按照 "-" 进行分割
hive> select split(hobby, "-") from girls_info;
OK
["画画","吃饭","跳舞"]
["唱歌","洞察人心","调戏恋恋"]
Time taken: 0.17 seconds, Fetched: 2 row(s)

-- explode 被称为炸裂函数，会将数组给炸开，将元素一个个取出来
hive> select explode(split(hobby, "-")) from girls_info;
OK
画画
吃饭
跳舞
唱歌
洞察人心
调戏恋恋
Time taken: 0.116 seconds, Fetched: 6 row(s)

既然如此，如果想实现我们刚才说的效果，那么再加上 name 字段不就行了吗？但很不幸，这样是会报错的，单独写可以，要是和其它字段组合就不行了。因为它没有办法自动做笛卡尔积，所以如果想实现这一点，我们需要借助一个语法。

-- 写法正确，但不是我们要的效果
select name, hobby from girls_info;  

-- 看起来像是我们想要的，但语法不对
select name, explode(split(hobby, "-")) from girls_info;

-- lateral view explode(category) 表示打开后的结果，然后起一个别名 hobby_name
-- 在 select 语句中选择 hobby_name，注意：必须要起一个别名
-- 至于里面 tem_table 是给表起的别名，这个别名是必须要有的，即便不用，也要写在那里
select name, hobby_name from girls_info 
lateral view explode(split(hobby, "-")) tem_table as hobby_name;

我们测试一下：

语法有点奇怪，但不复杂。

两表 JOIN

和 MySQL 一样，不再赘述。但需要注意：Hive 只支持等值连接，意思就是 ON 后面的条件，只支持 =，不支持其它的符号。比如：

select a.id, a.name, b.id, b.name from a join b on a.id = b.id;

以上是合法的，但 ON a.id > b.id 则不行，当然 ON 后面的条件一般都是等于。

内连接：join
左连接：left join
右连接：right join
全连接：full join

当然也可以以多表连接，但是注意条件，n 个表 JOIN，至少要有 n-1 个连接条件。

联合

UNION 和 UNION ALL 负责对两个查询进行上下合并，这点是和 JOIN 有区别的，JOIN 是左右关联，UNION 和 UNION ALL 是上下拼接。其中 UNION 在拼接的时候会去重，UNION ALL 不去重。

注意在使用 UNION 的时候，要满足两个结果集的列必须相同（数量相同、类型相同）。

窗口函数

按照功能，常用窗口可划分为如下几类：聚合函数、跨行取值函数、排名函数。

聚合函数

max
min
sum
avg
count

跨行取值函数

lead、lag
first_value、last_value

排名函数

rank
dense_rank
row_number

有点累了，这部分先不写了。可以去网上搜索窗口函数相关的用法，和 MySQL 是类似的。

系统函数和自定义函数

我们说 Hive 提供了大量的系统函数：

show functions：查看自带了哪些函数，300 个左右。
desc function 函数：查看某一个函数的用法。
desc function extended 函数：查看某一个函数的详细用法。

在大部分情况下，这些函数足够我们使用，但也有例外，因此 Hive 还支持我们自定义函数。自定义函数分为如下几种：

UDF(user defined function)：一进一出
UDAF(user defined aggregation function)：多进一出，类似于聚合函数，count，sum等等
UDTF(user defined table-generating function)：一进多出，如 lateral view explode()

关于自定义函数，由于我不是 Java 方向的，所以这里不再说了。

分区表

普通的表对应 HDFS 的一个目录，目录里面是存储数据的文件。分区表也是对应 HDFS 文件系统上的一个独立目录，但是该目录下面依旧是目录，我们称之为分区目录，分区目录里面才是数据文件。因此分区表就是把一个大的数据集，根据业务需要分割成多个小的数据集，然后在查询的时候指定分区，从提高效率，我们举例说明。

staff 是我们创建的一张表，所以在 HDFS 上面对应一个目录，目录里面存放的是具体的数据文件，点击就可以下载了。之前我们说过 Hive 是没有索引的，因此在查询数据时会全表扫描。

而分区表会按照字段进行分区，然后每个分区对应一个目录（分区目录），分区目录里面存储各自的分区数据。当我想查找数据时，只需要去对应的分区去查就可以了，不用再全表扫描。尤其是当数据量很大的时候，指定分区表是非常有必要的，其实我们在生产中建立的表绝大部分都是分区表，而且基本都是以月、或者天作为单位。

比如 365 天的数据，如果不指定分区，那么 HDFS 目录里面可能会有 365 个文件，在查找的时候会从这 365 个文件中从头查找，即便我们能够确定数据是在第 200 天产生的。但如果是分区表就不一样了，我们可以按天分区，如果数据量增长速度极快，那么你还可以按小时分区。按照天分区的话，HDFS 目录里面就相当于有 365 个分区目录（子目录），每个分区目录里面存放了对应的数据，那么当我们想要第 200 天的数据的时候，只需要到第 200 天对应的分区里面去找就 ok 了。

下面我们就来创建一下分区表，并插入数据。

create table info (id int, name string, dt string)
-- 创建分区字段 day，它和普通字段是等价的
partitioned by (day string)
row format delimited fields terminated by ',';

然后我们用 Python 生成 10000 条假数据，其中 id 是自增整数，名字是使用随机生成的，dt 一律都是 2020-03-08。

1,苏佳,2020-03-08
2,李建军,2020-03-08
3,李超,2020-03-08
4,卢刚,2020-03-08
5,陈丹,2020-03-08
6,张明,2020-03-08
7,王桂兰,2020-03-08
8,胡春梅,2020-03-08
......

然后通过 load data local inpath 导入到 Hive 中。

这里多指定了一个 partition，表示加载到 '2020-03-08' 这个分区中。注意：对于分区表，如果导入数据时不指定分区，在 Hive 2.x 的时候会报错，但 3.x 会默认创建一个分区。

分区目录创建成功，然后我们再创建一个分区，数据不变，将分区日期改成 9 号。

load data local inpath "/root/data.txt" into table info partition(day='2020-03-09');
-- 往分区写入数据还可以使用 insert 语句，比如：
-- insert into info partition(day='2020-03-09') values(...)
-- 只需要在 表名 后面加上分区即可，其它地方不变

此时便有两个分区了。

两个分区都有 10000 条数据，然后我们来查询。

select COUNT(*) from info; -- 20000
select COUNT(*) from info where day = '2020-03-08'; -- 10000

对于分区表，如果不指定分区，那么会依次查询所有分区。但也可以基于分区字段查询指定分区的数据，特别是按天分区的数据，在查询时可以有效减少扫描的数据量（前提是你知道数据是在哪一天，或者哪一个月）。

另外，分区字段和普通表字段是是等价的，在查询的时候可以任意使用。

通过分区字段 day 可以得知，这些数据都是在 2020-03-08 号产生的，当然我们也可以查询指定分区的数据。

那么问题来了，Hive 是如何做到的呢？之前说了元数据都存在 MySQL 里面，在数据库 metastore 里面有一张 PARTITIONS 表，它保存了分区的具体信息。

分区字段也是数据的元信息，存储在 MySQL 里面，通过分区信息来找到对应的分区。所以我们是先找到分区，然后再到分区里面查找数据，因此虽然 Hive 没有索引，但这个分区是不是也有点索引的感觉呢？

不过分区字段它并不是我们在表中真正意义上创建的字段（分区字段名不能和定义的表的字段名之间有重复，也就是不能用表的字段再作为分区的字段），它只是作为元信息。不过在查询的时候，所有字段都是一视同仁的，都可以用作 WHERE 筛选，以及被 SELECT 查询出来。

分区操作

添加分区

我们之前是通过 load data local inpath partition(...) 创建了分区，并把数据导入到了对应的分区中，但也可以直接创建分区。

-- 可以一次性创建多个分区
ALTER TABLE info ADD PARTITION(day='2020-03-10') PARTITION(day='2020-03-11');

我们执行之后，看一下 HDFS：

现在 /user/hive/warehouse/info/day=2020-03-10 里面是没有数据的，我们之前可以通过 load data local inpath 这种方式将数据增加到分区里面，并且会自动创建分区。而现在分区有了，如果手动将文件上传到 HDFS，那么能不能访问到数据呢？我们来试一试：

显然是可以的，所以再次证明了，元数据和实际数据不存在所谓的先后顺序。不管是先有元数据、还是先有原始数据，只要两者都存在、并且建立映射关系，那么就能访问到。

删除分区

当不再需要某个分区，也可以删除它。

ALTER TABLE info DROP PARTITION(day = '2020-03-10'), PARTITION(day = '2020-03-11');

注意：创建分区时，多个分区之间使用空格分隔；而删除分区时，多个分区之间使用逗号分隔，感觉这个有点莫名其妙。

如果刷新 HDFS 页面，会发现分区已经没有了。

查看分区

show partitions table_name：查看分区表有多少分区；desc formatted table_name：查看分区表的结构

里面包含了分区表的详细信息。

分区修复

Hive 将分区表的所有分区信息都保存在了元数据中，只有元数据与 HDFS 上的分区路径一致时，分区表才能正常读写数据。若用户手动创建 / 删除分区路径，Hive 都是感知不到的，这样就会导致 Hive 的元数据和 HDFS 的分区路径不一致。再比如，若分区表为外部表，用户执行 drop partition 命令后，分区元数据会被删除，而 HDFS 的分区路径不会被删除，同样会导致 Hive 的元数据和 HDFS 的分区路径不一致。

如果出现这种情况，可通过以下几种手段进行修复。

add partition

若手动创建 HDFS 的分区路径，Hive 无法识别，可通过 add partition 命令增加分区元数据信息，从而使元数据和分区路径保持一致。

drop partition

若手动创建 HDFS 的分区路径，Hive 无法识别，可通过 drop partition 命令删除分区元数据信息，从而使元数据和分区路径保持一致。

msck

若分区元数据和 HDFS 的分区路径不一致，还可使用 msck 命令进行修复。

-- 增加 HDFS 路径存在、但元数据缺失的分区信息
-- 还可以简写成：msck repair table `table_name`
msck repair table `table_name` add partitions;

-- 增加 HDFS 路径已删除、但元数据仍存在的分区信息
msck repair table `table_name` add partitions;

-- 同步 HDFS 路径和元数据分区信息，相当于同时执行上面两个命令
msck repair table `table_name` sync partitions

记得在使用 AWS 的 Athena 服务时，每次添加数据之后都要 msck 一下。

二级分区表

当数据量比较大时，我们可以按天进行分区，但如果一天的数据量也很大，该怎么办呢？可能有人觉得，按小时分区不就行了。首先这是一个办法，但这会导致目录过于扁平化，比如一年会产生 365 * 24 个目录，而这些目录都在同一层级。

面对这种情况，我们可以对数据进行二次分区，在按天分区的基础上，再对每天的数据按小时分区。

create table dept_partition2(
    ...
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';

在添加数据的时候，需要同时指定 day 和 hour，比如 partition(day = '2020-03-05', hour='12')。这样 HDFS 存储路径里面还是 365 个目录，每个目录里面又有 12 个子目录，这 12 个子目录里面存储每个小时的数据。

分桶表

除了分区表之外，Hive 中还存在一个分桶表。首先分区提供了一个隔离数据和优化查询的便利方式，但并非所有的数据集都可以形成合理的分区。因此对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分区针对的是数据的存储路径，而分桶针对的是数据文件。

分桶表的基本原理是，首先为每行数据计算一个 hash 值，然后对桶的个数取模，最后将取模运算结果相同的行写入同一个文件中，而该文件就称为一个桶（bucket）。

创建 && 导入数据

下面我们来创建一个分桶表：

create table tong(id int, name string)
clustered by(id) 
-- sort by(id)，创建桶的时候，还可以基于桶字段进行排序
into 4 buckets -- 分成四个桶
row format delimited fields terminated by ',';

分区表是 partitioned by，分桶是 clustered by，但我们发现分桶的字段在表的普通字段里面是存在的。因此这也是分桶和分区的一个很大的区别，分区字段相当于在原有字段的基础上多增加了一个，而分桶字段则是表字段里面已经存在的字段，所以定义桶字段时不需要再单独指定类型了。

然后将导入数据：

0,asSA
1,SEcd
2,OzBE
3,tYZf
4,fopk
5,FlSB
6,AvBu
7,nMwS
8,lXzb
9,IcTz

导入成功之后，我们查看 HDFS。

我们看到有 4 个文件，说明确实分成了 4 个桶。

分桶抽样查询

对于非常大的数据集，有时候需要进行抽样，也就是需要一个代表性的结果，而不是全部结果，那么分桶表很适合这种业务。

select * from tong tablesample(bucket 1 out of 4 on id)

解释一下这里的 tablesample(bucket x out of y on id) 语法，on id 很好理解，就是基于 id 字段。而 y 必须是 bucket 数（我们这里设置的是 4）的倍数或者因子，然后 Hive 根据 y 的大小，决定抽样的比例。

例如我们上面创建的分桶表是四个桶，那么当 y=8 的时候，表示 4/8，抽1/2个桶。如果 y=2，表示 4/2，抽两个桶，至于 x 则表示从第几个桶开始抽。并且抽样的时候也有相应的规则，会按照 x、x+y、x+2y、··· 这样的顺序。比如有 16 个桶，那么 bucket 1 out of 4，就会抽到 1、1+4、1+4+4、1+4+4+4，即 1、5、9、13 这四个桶。也正因为如此我们需要 x 小于等于 y，否则报错。

可能有人担心，会不会到最后我们要取的桶的位置超过了所有的桶数，比如总共 16 个桶，但是我们却要第 17 个桶，会不会出现这种结果呢？显然不会，因为 x 小于等于 y 就决定了，不会出现这种结果。

举个例子，假设有 z 个桶，那么最后一个桶的位置就是 x + (z/y - 1) * y = x + z - y，由于x小于等于y，因此最多取到最后一个桶（当 x=y 的时候）。

还是比较简单的。

文件格式和压缩

Hadoop 在存储文件的时候，支持多种压缩格式。

而提到了压缩格式，那么就必须提文件格式，不同的文件格式对压缩的友好性也不同。为 Hive 的表数据选择一个合适的文件格式，对查询性能的提高是十分有帮助的。Hive 表数据的存储格式，可以选择 text file、orc、parquet、sequence file 等等。

CREATE TABLE table_name
(......)
STORED AS [textfile | orc | parquet | sequencefile];

其中 textfile（文本文件）是 Hive 默认使用的文件格式，文本文件中的一行内容，就对应 Hive 表中的一行记录。而 orc 和 parquet 则都使用了列式存储，关于这两种文件的存储格式就不说了，感兴趣可以网上查阅相关资料。

CREATE TABLE table_name
(......)
STORED AS orc
tblproperties("orc.compress"="ZLIB", 
              "orc.compress.size"="262144",
              "orc.stripe.size"="67108864", 
              "orc.row.index.stride"="10000");

当存储文件指定为 orc 格式时，还可以设置一些参数：

orc.compress：压缩格式，可选项为 None、ZLIB、SNAPPY，默认为 ZLIB
orc.compress.size：每个压缩块的大小，ORC 文件是分块压缩的，默认为 262144
orc.stripe.size：每个 stripe 的大小，默认为 67108864
orc.row.index.stride：索引步长，每个多少行数据建立一条索引，默认为 10000

同理当指定为 parquet 格式时，也可以设置一些参数。

parquet.compression：压缩格式。
parquet.block.size：行组大小，通常与 HDFS 块大小保持一致，默认为 134217728
parquet.page.size：页大小，默认为 1048576

一般来说，采用列式存储的文件格式对压缩更为友好，因为列式存储和数据压缩通常是伴生的。所以我们将来在创建表的时候，不妨通过 STORED AS 将文件格式指定为 parquet，再通过 TPLPROPERTIES 选择一个合适的压缩格式，可以极大地提升查询和存储性能。

然后不光数据可以压缩，计算结果也可以压缩。比如对单个 MapReduce 的中间结果（Map 输出的数据）进行压缩，可以降低 shuffle 阶段的网络 IO。

-- 开启 MapReduce 中间数据压缩功能
set mapreduce.map.output.compress=true;
-- 设置 MapReduce 中间数据数据的压缩方式
-- DEFLATE -> org.apache.hadoop.io.compress.DefaultCodec
-- gzip -> org.apache.hadoop.io.compress.GzipCodec
-- bzip2 -> org.apache.hadoop.io.compress.BZip2Codec
-- LZO -> com.hadoop.compression.lzo.LzopCodec
-- Snappy -> org.apache.hadoop.io.compress.SnappyCodec
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

除了单个 MapReduce 的中间结果，还可以对单条 SQL 语句的中间结果（两个 MR 之间的临时数据）进行压缩。

-- 是否对两个 MR 之间的临时数据进行压缩
set hive.exec.compress.intermediate=true;
-- 压缩方式
set hive.intermediate.compression.codec= org.apache.hadoop.io.compress.SnappyCodec;

关于文件格式和压缩就了解这么多。

查看执行计划

这一小节还没有写。

Hive 性能调优