构建于hadoop之上、让你像写SQL一样编写MapReduce程序：Hive的使用方法

Hive 介绍

什么是Hive?

Hive 是基于 Hadoop 的一个数据仓库工具，由 Facebook 开源，用于解决海量结构化日志的数据统计。它可以将结构化的数据映射为类似数据库中的一张表，并提供 类SQL 查询功能，本质是将 HQL(Hive SQL) 转化为MapReduce程序。

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

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

在介绍 Hadoop 的时候，我们说过 Hive 处理的数据存储在 HDFS 上；底层分析数据采用的依旧是 MapReduce，但是将 类SQL语言 和 MapReduce 做了一层映射，可以帮我们把 SQL语句 翻译成 MapReduce；执行程序运行在 YARN 上，这是显而易见的，因为实际计算的仍是 MapReduce嘛。

但是 HDFS 上的数据并没有所谓的表结构、字段信息，所以除了 HDFS 之外，Hive还依赖关系型数据库，比如：MySQL。在将 HDFS 上的数据抽象成一张表时，这些表信息都存在关系型数据库中，而这些表信息也叫作元信息、或者元数据，它是存储在关系型数据库中的，而实际数据则存在 HDFS 上。

hive的优点

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

hive的缺点

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

hive的架构

1. 用户接口：Client

CLI(hive shell)，pyhive(Python访问hive)，WebUi(浏览器访问hive)

2. 元数据存储：MetaStore

负责存储元数据，元数据包括：表名、表所属的数据库(默认是default)、表的拥有者、列/分区字段、表的类型(是否是外部表)、表的数据所在目录等等

3. 实际数据存储：HDFS

使用HDFS进行存储，使用MapReduce进行计算

4. 驱动器：Driver

1.解析器(sql parser)：将SQL字符串转成抽象语法树(ast)，这一步一般都用于第三方工具完成，比如antlr，然后对ast进行语法分析，比如表是否存在、字段是否存在、逻辑是否有误等等

2.编译器(physical plan)：将ast编译生成逻辑执行计划

3.优化器(query optimizer)：对逻辑执行计划进行优化

4.执行器(execution)：把逻辑执行计划转化为可以运行的物理计划

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。但是这里我们不采用官方的社区版本，而是采用Cloudera公司的CDH版本。下载地址是：http://archive.cloudera.com/cdh5/cdh/5 ，我们进入这个页面，找到：hive-1.1.0-cdh5.15.1.tar.gz，点击下载即可。注意：使用 Hive 需要本地有 jdk 和 Hadoop，而Hadoop我在上一篇博客中已经介绍了，可以去看一下，并且目前 Hadoop 的服务都已经启动了。而在安装 Hadoop 的时候，采用的CDH版本是 5.15.1，所以这里 Hive 的CDH版本也是5.15.1。

下载完毕之后直接解压到 /opt 目录下即可，大数据组件我都放在 /opt 目录下，然后再将 bin 目录配置到环境变量。

然后看一下 Hive 的目录结构：

bin目录是用来执行一些命令的，我们注意到没有sbin目录，sbin目录一般是存放启动文件的，就像hadoop的sbin一样，显然Hive是将两者合并了到bin目录里面了。conf是存放配置的，lib是存放一些jar包的，还有examples案例，等等。

然后下面老规矩，肯定要修改配置文件。

我们需要修改 hive-env.sh，但是 conf 下面没有这个文件，不过有hive-env.sh.template，所以需要先 cp hive-env.sh.template hive-env.sh。我们需要指定 HADOOP_HOME 和 HIVE_CONF_DIR，也就是 Hadoop 的安装目录 和 conf目录的绝对路径。

export HADOOP_HOME=/opt/hadoop-2.6.0-cdh5.15.1/
export HIVE_CONF_DIR=/opt/hive-1.1.0-cdh5.15.1/conf

修改完毕，由于我已经配置了环境变量，所以在家目录启动一下 hive，直接输入 hive 即可。如果没有配置环境变量，那么需要进入到 bin 目录里面。

我们看到此时我们通过 shell 的方式启动了一个客户端，我们看到默认是有一个default库。但是 Hive 默认存储元数据使用的库叫 Derby，我们需要将其改成 MySQL、或者 PostgreSQL，这里我们就用 MySQL吧。

安装 MySQL。

1. wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

2. rpm -ivh mysql-community-release-el7-5.noarch.rpm

3. yum install mysql-server(如果失败的话，可以先执行一下yum update)

4. mysqld --initialize

5. systemctl start mysqld

6. 输入：mysqladmin --version 回车，如果出现类似  mysqladmin Ver 8.42 Distrib 5.6.50, for Linux on x86_64 说明安装成功

7. 设置密码：mysqladmin -u root password 你的密码

8. mysql -u root -p 进入数据库

MySQL就安装成功了，然后我们指定 Hive 使用 MySQL来存储元数据。

这里我们修改conf目录下的 hive-site.xml，但是没有这个文件，所以我们要 touch hive-site.xml，然后打开在里面输入如下内容：

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

<configuration>

    <!-- 指定mysql, 将元数据存在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>
        <value>com.mysql.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需要有相应的驱动，Java连接MySQL的驱动网上随便一搜就能找到，这里我们可以使用菜鸟教程提供的 https://www.runoob.com/java/java-mysql-connect.html，进去会看到页面所提供的下载路径。下载完毕之后，直接扔到 HIVE_HOME 的 lib 目录里面去就行了。

然后我们再来启动一下hive客户端，启动之后观察一下 MySQL 的 database。

我们看到 metastore 这个库已经自动帮我们创建了，下面我们创建一张表测试一下。注意：这里创建表可不是在 MySQL 中创建，而是在 hive 中创建，创建之后元数据信息会存储在MySQL中。然后我们 insert 数据，会保存在 HDFS 中。

然后我们通过 webUI 的方式查看一下，创建的数据默认位于 /user/hive/warehouse 中，我们可以通过在 hive-site.xml 中指定 hive.metastore.warehouse.dir 来自定义存储目录。

girls是一个目录、代指表，里面有一个文件 000000_0 存储了实际的数据，我们将其下载下来看看里面的内容是什么。

这不就是我们插入的数据吗，而且我们看到这只是单纯的数据，像什么字段名、类型统统都没有。所以这就是 Hive，HDFS 只存储实际数据，至于表的元信息则存在 MySQL 中。然后在查找的时候，会先去 MySQL 中查找对应的元信息，然后在 HDFS 上找到对应的数据。所以整体流程还是比较简单的，至于图中的 SOH 则不用管，它类似于分隔符一样的东西。

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

下面我们再来做个实验，我们手动往 HDFS 上拷贝一份文件：

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

因此有两个过程：先在MySQL中找元数据，再根据元数据找HDFS上的实际数据。

配置服务让第三方访问

我们目前是通过启动一个 hive shell 的方式访问的，如果我们想通过代码、或者数据库管理工具去连接的话，目前可以办到吗？

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

因此命令行输入 hive 启动一个 shell 只是方便测试，而如果想让第三方使用 Hive 上的数据，那么就必须给 Hive 启动一个服务。

我们需要在 hive-site.xml 中添加如下信息：

<property>
    <!-- 指定元数据要连接的地址 -->
    <name>hive.metastore.uris</name>
    <value>thrift://matsuri:9083</value>
</property>

但是这样的话直接启动 hive 虽然是可以的，但是尝试访问数据的时候会报错，因为无法连接指定地址，我们需要先开启服务才可以。开启服务通过 hive --service metastore，这是一个前台进程，你需要再启动一个窗口，然后就可以在命令行中启动hive shell 并访问数据了。虽然这种方式对于本地而言有点麻烦，但是至少它向外界暴露了一条通道，可以支持外界访问，但是我们不推荐这种方式，所以这个配置我就不加了，可以自己测试一下。

我们来看另一种配置 Hive 服务的方式，我们推荐使用这一种，还是修改配置文件，在里面加入如下内容：

<property>
    <!-- 指定hiveserver2要连接的地址 -->
    <name>hive.server2.thrift.bind.host</name>
    <value>matsuri</value>
</property>

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

<!-- 以上两个配置其实也可以不用加, 因为默认就是上面两个配置 -->

然后启动 hiveserver2 服务，hive --service hiveserver2，这依旧是一个前台进程。

然后我们在 Windows 上通过 Python 的 impala 模块来访问一下。

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

然后我们来介绍一下如何安装impala，毕竟它的安装还是比较麻烦的。

1. pip install pure-sasl；

2. pip install thrift_sasl==0.2.1 --no-deps；

3. pip install thrift==0.9.3；

4. pip install thriftpy==0.3.9；

以上四个包安装没有任何问题，直接pip install即可，只是在安装过程第四个包thriftpy的时候会显示：ERROR: thrift-sasl 0.2.1 requires sasl>=0.2.1, which is not installed.，但是不用管，忽略它即可。

然后安装一个叫做 bitarray 的包，但由于它是使用C扩展编写的，在Windows上安装会报出gcc编译错误。可以下载vscode，但是比较麻烦，推荐的办法是进入 https://www.lfd.uci.edu/~gohlke/pythonlibs/ 进行下载对应Python版本的bitarray，进行安装。

最后 pip install impyla，安装的时候叫 impyla，但是使用的时候是impala。

安装成功之后还没完，如果安装之后你直接像上图代码那样连接的话，应该会报出如下错误。

"""
Traceback (most recent call last):
  File "D:/satori/1.py", line 14, in <module>
    conn = connect(host='47.94.174.89', port=10000, database="default", auth_mechanism='PLAIN')
  File "C:\python38\lib\site-packages\impala\dbapi.py", line 144, in connect
    service = hs2.connect(host=host, port=port,
  File "C:\python38\lib\site-packages\impala\hiveserver2.py", line 825, in connect
    transport.open()
  File "C:\python38\lib\site-packages\thrift_sasl\__init__.py", line 75, in open
    self._send_message(self.START, chosen_mech)
  File "C:\python38\lib\site-packages\thrift_sasl\__init__.py", line 94, in _send_message
    self._trans.write(header + body)
TypeError: can't concat str to bytes
"""

根据提示信息：我们进入thrift_sasl模块下的 __init__.py 中，找到第94行。

# 值得一提的是，python中的缩进应该是四个空格，但是thrift_sasl用的两个空格，不过不影响
  def _send_message(self, status, body):
    header = struct.pack(">BI", status, len(body))
    self._trans.write(header + body)
    self._trans.flush()

  # 我们看到报错的原因是header + body、因为字符串和字节无法相加
  # 将上面代码改成如下
  def _send_message(self, status, body):
    header = struct.pack(">BI", status, len(body))
    if isinstance(body, str):
        body = bytes(body, encoding="utf-8")
    self._trans.write(header + body)
    self._trans.flush()

然后重新执行，成功获取数据。我个人到此就执行成功了，按照上述方法安装应该能够正常访问，但是你也可能还会报出如下错误。

"""
ThriftParserError: ThriftPy does not support generating module with path in protocol 'c'
"""
# 如果报出上面错误，那么进入site-packages\thriftpy\parser\parser.py
# 找到第488行，应该会看到一个条件语句，如下：
    if url_scheme == '':
        with open(path) as fh:
            data = fh.read()
    elif url_scheme in ('http', 'https'):
        data = urlopen(path).read()
    else:
        raise ThriftParserError('ThriftPy does not support generating module '
                                'with path in protocol \'{}\''.format(
                                    url_scheme))

"""
将
if url_scheme == '':
改成
if url_scheme == '' or len(url_scheme) == 1:
即可
"""

可能有的小伙伴觉得这种方式不够优雅，能不能通过 SQLAlchemy 创建引擎的方式来访问呢？答案是可以的。

额外的参数通过 connect_args 参数指定，以字典的形式。另外除了 impala 之外我们还可以使用 pyhive，不过个人更推荐 impala。因为笔者在工作中这两个都用过，发现 pyhive 在读取数据的时候偶尔会出现数据串行的情况，但是 impala 不会。

以上就是在Windows上使用Python连接hive的方式，不过个人觉得这只适合学习，如果你想连接你们公司内网的 Hive 服务的话，希望应该渺茫。毕竟生产环境中几乎都是采用Kerberos认证，这种情况下在Windows上再想连接的话会贼麻烦。而且个人觉得，像数仓这种东西，一般都只能在指定的内网中访问中，在本地公网环境能连接的可能性不大。

当然如果你不是纯粹的大数据运维团队的话，个人觉得也不用在意这一点，因为我们做开发的话，重点还是要掌握 Hive 的语法，能够熟练使用 HQL 操作里面的数据才是第一位的。

当然我们说这种方式启动的服务是前台进程，一旦终端关闭，服务也就停止了，因此我们需要后台启动。

而后台启动服务，我们需要使用Linux中的nohup命令：nohup hive --service hiveserver2 >> /dev/null&，这样启动的进程就是后台的了。注意：这种方式，除了可以让外界访问之外，hive shell 本身也是可以访问的。

当然目前输入 hive 的话，会进入交互式界面，但我们也可以在不进入交互式的情况下执行SQL语句。

hive -e "SQL语句"

这样的话就可以在不用进入 shell 的情况下，执行语句了。

另外除了执行语句之外，我们还可以把语句写在文件里，通过 hive -f 文件名，来执行文件里面的SQL语句。不管是哪种方式，它们都还支持重定向，比如：hive -f "select * from girls" > 1.txt，可以将查询结果重定向到指定的文件中。

Hive 的数据类型

下面我们来看看 Hive 的数据类型，数据类型有基本数据类型 和 集合数据类型。

基本数据类型

还是比较简单的，基本都是熟悉的类型名称。像 tinyint、smallint 交互不怎么常用，毕竟是大数据框架，这两老铁用的实在是不多。然后string类型就相当于数据库的 varchar 类型，它是一个可变的字符串，只不过不能显示的声明最多存储多少个字符，不过理论上可以存储 2GB 的字符串。而日期类型可以是 timestamp（年月日时分秒），或者 date（年月日），当然 date 图中忘记写了。

集合数据类型

Hive 有三种复杂类型：struct、map 和 array。array 和 map 就想成 Python 的 列表和字典即可，而struct则等价于 C 或者 Go 的结构体，它封装了一个命名字段的集合，复杂数据类型允许任意层次的嵌套。

不过这种复杂的数据类型不是很常用，下面来演示一下。想基础数据类型我们就一笔带过了，有关系型数据库经验的话肯定不是问题。

首先定义一张表，里面有4个字段。name、hobby、info、score。

• name：姓名，显然是string类型
• hobby：兴趣，一个人可以有很多兴趣，所以这是一个array
• info：个人信息，比如身高->int,性别->string,这是一个结构体
• score：成绩，语文:90,数学:89,英语:92，这是一个map

那么怎么定义这张表呢？

create table student (
    name string,
    hobby array<string>,
    info struct<length: int, gender: string>,
    score map<string, int>
)
--还没完，还有一个很重要的事情就是分割符
--对于每一个字段来说，我们在插入数据的时候，是使用逗号分割
--但是对于集合来说，有多个元素，我们要怎么分开呢，因此我们需要指定分割符

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

--对于集合来说，我们使用 _ 分割里面的每一个元素
collection items terminated by '_'
--比如hobby就是 唱歌_跳舞
--info就是 160_女
map keys terminated by ':'
-- 对于map，我们使用:分隔键值对，那么score就可以是 总分:285
lines terminated by '\n'; 
--也可以不用指定，多行默认使用\n分割

我们把注释去掉，执行一下，在表创建成功之后便可以插入数据了。

这里我们将数据写在文件里面，命名为 /root/student.txt。

椎名真白,吃饭_画画,160_女,语文:89_数学:90_英语:90
古明地觉,宠物_古明地恋,155_女,语文:88_数学:95_英语:90

我们看到struct、map、array，内部所有的元素都是采用 _ 进行分隔，至于 map 里面的键值对则是使用 :，然后我们可以在 hive  shell 中通过 load data local inpath "文件名" into table 表名 的方式进行导入。

此时我们看到数据已经被导入了，而且也成功的查询到了。

那么问题来了，我们如何获取array、map、struct中的指定元素呢？

-- hobby[1]表示获取数据组中索引为1的元素
-- info.length表示获取结构体中成员为length的元素
-- score['语文']表示获取map中key为'语文'的元素
select name, hobby[1], info.length, score['语文'] from student;

怎么样，是不是成功获取了呢？另外，这个打印的格式是不是不太好看啊，明显都错位了。因此为了打印好看，我们后面就使用 Python 来执行了，封装一个函数，将语句传进去即可，然后会返回 DataFrame，从而打印漂亮的输出。

变量 hql 里面的就是执行的SQL（HQL）语句，调用 execute_hql 函数，将 HQL 传进去即可得到结果，和使用 hive shell 是一样的，只不过打印明显变好看了。而且使用 Python 这种方式打印我们还可以自动得到字段信息，默认使用 hive shell 执行得到的结果是没有字段信息的。另外，最左边的 0 1 不属于 hive 返回的数据信息，它是 DataFrame 自带的，不用关心。

类型转换

Hive 的原子数据类型是可以进行隐式转换的，例如某表达式使用 int 类型，tinyint会自动转换为int类型，但是 Hive 不会反向转化。例如某表达式使用 tinyint 类型，而 int 类型不会转化为 tinyint 类型，它会返回错误，除非使用 cast 进行转化。

1. 首先是 隐式转化。

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

2. 可以使用cast进行 显式转化。

可以使用 cast('1' as int) 将字符串 '1' 转换为整型1；如果强制转化失败，比如cast('xxx' as int)，则表达值返回空值NULL。

'1' + 2，显然；两个元素都转成了double。

DDL 数据定义

下面我们来看看 Hive 支持的DDL操作，在 Hive 中 DDL 远没有 DML 重要，但我们还是要了解的。

创建数据库

创建一个数据库很简单，和MySQL类似，当然 Hive 默认有一个 default 库。

create database db_hive; -- 表示创建 db_hive 这个库

但如果 db_hive 已经存在的话会报错，因此更严格一点的写法是 create database if not exists db_hive，我们看到这和 MySQL 是高度相似的。

我们执行一下，显示创建成功，但是创建之后的数据库在哪呢？显然如之前说了，都在 /user/hivewarehouse/ 下面，我们来看看。

可以看到我们在 default 数据库中建的表也在 /user/hivewarehouse/ 下面，建的数据库也是一样的。因此为了区分，会自动在我们建的数据库名后面加上一个 .db，来区分这是一个数据库。我们在db_hive数据库中建一张表看看，而在db_hive数据库里面建立的表，显然在 /user/hivewarehouse/db_hive.db 路径下面会多一个目录。

然后我们查看一下。

可以看到该库下已经有我们创建的表了。而且我们创建数据库的也可以自己指定位置，只需要加上一个 location 路径 即可。

create database if not exists komeijisatori location '/komeijisatori123'

执行之后就会创建了一个名为 komeijisatori 的数据库，但是路径在 / 下面，并且指定了新名字 komeijisatori123。所以一个库和一张表在 HDFS 上都会对应一个目录，并且它们的名字是一致的，当然数据库的话在HDFS上结尾会多出一个 .db。但是它们的名字也可以不一样，只不过一般我们都是设置成一样的，为了统一，否则的话只会给自己找麻烦。

查询数据库

这个就没必要介绍了吧，只是 hive 中查询数据库还支持模糊查询。

还可以查看数据库信息，desc database 数据库，显示详细信息的话 desc database extended 数据库。

修改数据库

用户可以使用 alter database 来为某个数据库设置键值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据所在的位置，只能改一些附加的信息，比如创建时间等等，这里不介绍了，没啥太大卵用。

删除数据库

drop database 数据库，可以看到基本上和 MySQL 的语法差不多。如果某个数据库删不掉，那么可以使用 drop database 数据库 cascade 语法强制删除。

hive> show databases;
OK
db_hive
default
Time taken: 0.013 seconds, Fetched: 2 row(s)

hive> drop database db_hive;  # 我们看到删不掉, 因为数据库不为空, 里面存在着表
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. 
InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)

hive> drop database db_hive cascade;  # 加上 cascade 强制删除
OK
Time taken: 0.741 seconds

hive> show databases;  # 删除成功
OK
default
Time taken: 0.016 seconds, Fetched: 1 row(s)
hive> 

创建表

创建表还是蛮复杂的，首先我们来看一下最基本的建表语句。

create table 表名 (
    列名 类型 [comment 注释],
    列名 类型 [comment 注释],
    列名 类型 [comment 注释],
    ......
);

和关系型数据库创建表的语法类似，但是我们之前某次创建表的时候可没有这么简单，还制定了好多的分隔符啥的。那么下面再来看一看复杂的建表语句：

create [external] table [if not exists] 表名(
   列名 类型 [comment 注释],
   列名 类型 [comment 注释],
   列名 类型 [comment 注释],
    ......
)
[comment 表注释]
[partitioned by (列名 类型 [comment 注释], ···)]
[clustered by (列名,列名,列名,···)]
[sorted by (列名[asc|desc], ···) into num_buckets buckets]
[row format row_format]
[stored as file_format]
[location hdfs_path]

我们先简单解释一下，后续会详细介绍：

• external：表示创建一个外部表，不加表示创建内部表
• [comment 表注释]：除了给字段添加注释，还可以给表添加注释
• partitioned by：按照指定字段分区
• clustered by：按照指定字段分桶
• sorted by：按照字段排序，很少用
• row format：元素之间按照···分割
• stored：按照指定格式存储，默认是txt
• location：之前说了，指定hdfs存储路径

内部表和外部表

内部表和外部表的主要区别就在于删除方面。我们默认的创建的表都是内部表，也叫管理表。当我们删除一个表的时候，实际上删除的是数据库里面的元数据，而原始的数据信息存在hdfs上面。如果是内部表，hive会控制生命周期，在删除元数据的时候，HDFS上数据也会删除。但如果是外部表，那么删除元数据，不会影响HDFS上的原始数据，因为两者是不同的对象管理，我们来演示一下。

create table girl1(id int);
create external table girl2(id int);

创建两张表，girl1是内部表，girl2是外部表，然后将两个表全部删除，看看会有什么影响。

然后我们去 HDFS 上看一看。

我们看到外部表girl2还在，但是内部表girl1已经没了。

所以如果创建的是外部表的话，即使删除了元数据，HDFS上的原始数据依旧坚挺在那里。

那么我们如何选择呢？如何只是作为中间的临时表，建议使用内部表，否则还要手动删除HDFS上的数据，如果是一直使用的表，那么建立外部表，这样更安全。另外Hive是如何查询数据的呢？其实Hive是通过MySQL存储的元数据信息然后找到对应的存储数据的HDFS路径，这两者无论先有哪一个都无所谓。我们可以先有元信息，然后在指定的HDFS路径上存数据；也可以先有数据，然后再把数据存储的路径作为元信息存储到MySQL中，都是一样的，这两者没有所谓的先后顺序。

我们绝大部分建立的都是外部表，内部表只有在作为中间临时表才会用。

但是问题来了，如果我们创建表的时候不小心创建错了，本来想创建外部表结果创建成了内部表该怎么办呢？其实hive是支持我们修改的。

首先我们需要确定这张表到底是内部表还是外部表，可以使用 desc formatted table_name 来查看。

我们查看之前创建的girls表，发现输出了很多信息，比如：字段名、类型，所在数据库、所有者、创建时间等等，然后我们看到Table Type是MANAGED_TABLE，所以这是一张管理表（内部表）。

修改表的类型。

alter table 表名 set tblproperties('EXTERNAL'='TRUE')，表示把表变为外部表，同理FALSE的话，是把表变为内部表。注意：EXTERNAL、TRUE、FALSE这三者是固定写法，区分大小写，不能用小写。

成功修改为外部表。

修改表

1. 首先是重命名。

alter table 表名 rename to 新表名

2. 增加一列

alter table 表名 add columns (列名 类型, 列名 类型 ...)，既然是columns，肯定是可以添加多列。

3. 修改一列

alter table 表名 change column 列名 新列名 类型，可以只改名字，也可以只改类型，当然新的列名和类型必须都写。

4. 替换一列

这个比较怪异，alter table 表名 replace columns (列名 类型, 列名 类型)，这个替换表示的是把原来表的列全部清空，然后把指定的新的列添加进去。比如原来的表有id,name字段，所以如果是replace columns (uid int, age int)，那么此时的表就只有uid 和 int。所以比较让人难以理解，是把原来的所有列全删了，把新指定的列加进去。如果是替换单个列，就用change。但是注意的是我们改的是元数据，实际数据依旧存储在hdfs上面，是不会变的。

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

分区表

普通的表对应HDFS的一个目录，目录里面是存储数据的文件。分区表也是对应一个 HDFS 文件系统上的独立目录，但是该目录下面是则不再是具体的文件了，而还是目录。而里面的分目录就是Hive中的分区，所以把一个大的数据集根据业务需要分割成多个小的数据集。然后在查询的时候指定分区，会提高效率。怎么理解呢？我们举个例子：

girls是我们的一张表，所以在HDFS上面是一个目录，girls目录里面存放的是具体的数据文件，点击就可以下载了。之前我们说过Hive是没有索引的，因此在查询数据的时候，是暴力的全表扫描。而分区表的话，表示按照字段进行分区，比如按照时间进行分区，1号、2号、3号，那么等于说是按照时间把表分成了三个区域，这三个区域当然还在girls里面，但是它们不再是具体的数据文件，而也是一个目录（分区目录），然后每个目录里面存储各自对应的分区数据。那么当我想查找对应的数据，就只需要去对应的分区(比如1号的数据，只需要到1号分区里面去查找)就可以了，就不用全表扫描了。尤其是当数据量很大的时候，指定分区表是非常有必要的，其实我们在生产中建立的表绝大部分都是分区表。

比如365天的数据，如果不指定分区，那么girls里面可能会有365个文件，那么当查找的时候会从这365个文件中从头查找，即便我们只想从第200天的数据中查找。但如果是分区表就不一样了，我们可以按照天分区，如果数据量增长速度极快，那么你还可以按照小时分区。按照天分区的话，girls里面就相当于有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是自增整数，名字是使用Python的faker模块随机生成的，dt一律都是 2020-03-08。

1,庄秀华,2010-03-08
2,李欣,2010-03-08
3,乔兵,2010-03-08
4,钱建国,2010-03-08
5,王伟,2010-03-08
6,朱玉,2010-03-08
7,许桂香,2010-03-08
8,王柳,2010-03-08
9,刘秀珍,2010-03-08
10,李健,2010-03-08
11,程芳,2010-03-08
12,曹亮,2010-03-08
13,黄博,2010-03-08
14,李玉兰,2010-03-08
15,曹淑英,2010-03-08
16,田颖,2010-03-08
............

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

但是在插入数据的时候报错了，报了个什么错误呢？告诉我们 因为目标表被分区了，我们需要指定分区字段。

补充一下，创建表的时候，分区字段不能出现在定义的表的字段里面。我们通过指定 partition(day='2010-03-08')，表示这是一个day='2010-03-08'的分区。再比如今天是 '2019-03-09'，那么我想把今天的数据存在一个分区里，就可以指定partition(day='2019-03-09')，表示这个分区存放了 '2019-03-09' 的所有数据。

此时我们再使用webUI查看一下，发现里面是一个目录，注意这是目录。如果不是分区表，那么就是文件。我们点进去看看：

我们点进去就能看到我们 load 进来的文件，所以分区分的就是目录。那比如说，我们再创建一个分区。

此时又创建了一个 day='2010-06-07' 的分区，然后查看一下HDFS。

可以看到就又多了一个分区。那么我们就来查询一下数据，两个分区存的数据是一样的，都是10000条。

我们原来的数据只有三个字段，但是现在是4个，说明把分区字段 day 也给加上去了。当然由于我们只选了10条，这10条都是分区day='2010-03-08'下面的，因为不指定分区的话，默认是从第一个分区开始选。

如果通过分区作为筛选字段的话就不一样了，此时不会再重头筛选，而是会从指定的分区中筛选。

所以默认情况下是操作所有分区，我们统计一下记录总数，以及查询一下 id = 100 的。

我们看到总共的记录数是20000，说明统计的时候是把所有分区都给算进去的。当然查询其他内容也是一样的，默认是所有分区，我们生成的数据里面的 dt 都是 "2010-03-08"，但是有两个分区，数据一样，所以在查找 id = 100 的记录的时候，将两个分区的数据都找出来了。而如果我们想指定分区的话，直接将day（分区字段）当成普通字段来指定即可。

那么Hive是如何做到的呢？我们之前说了元数据都存在MySQL里面，而我们指定的数据库是metastore，我们就进去看看，里面到底存了哪些表？

可以看到里面存了很多表，其中的 TBLS 存储的就是HDFS数据的元信息，而且我用箭头还指向了PARTITIONS这张表。不用想，从名字就能看出来这是与我们现在的分区有关系，我们来看看这张表长什么样子。

看到了没，分区字段也是数据的元信息，是存储在MySQL里面的，通过分区信息来找到对应的分区。所以我们是先找到分区，然后再到分区里面查找数据，所以说Hive虽然没有索引，但是这个分区是不是也有点索引的感觉呢？不过分区的这个字段它并不是我们在表中真正意义上创建的字段（所以才说指定的分区字段名不能和我们定义的表的字段名之间有重复，也就是不能用表的字段再作为分区的字段），它只是作为元信息。不过你可以认为它是我们普通地定义的表的字段之一、并在查询的时候把所有的字段一视同仁，毕竟在查询的时候分区字段也会跟着一块出来。

分区操作

添加分区：

我们之前是通过 load data local inpath，然后指定分区，从而创建了分区并且把数据导入了对应的分区中，那么可不可以直接创建分区呢？显然是可以的，通过 alter table info add partition(day='2018-9-9') partition(day='2018-9-10') ... 操作，是的，我们可以一次性指定多个分区，当然也可以一次只指定一个。

然后我们查看一下HDFS上的元信息。

发现多了几个分区，就是我们刚刚创建的，当然里面都没有数据。

我们注意到这个 /user/hive/warehouse/info/day=2018-09-09，里面是没有数据的。我们之前可以通过 load data local inpath 这种方式将数据增加到分区里面。那么通过手动上传文件的方式，将数据上传到HDFS，然后通过SQL能不能访问呢？我们来试一试：

数据是上传成功了的，但是能不能通过sql访问呢？

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

删除分区：

alter table info drop partition(day='2018-09-09'),partition(day='2018-09-10'),...，注意：和创建分区不同，除了把add换成了drop之外，多个分区之间是使用逗号分隔的，这个比较恶心，你要使用空格都使用空格，使用逗号都使用逗号。但是创建多个分区，使用空格分隔；删除多个分区，使用逗号分隔，就有点莫名其妙。

再来看看HDFS：

分区已被成功删除。

查看分区：

show partitions table，查看分区表有多少分区。desc formatted table查看分区表结构。

数据的导入与导出

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

数据的导入

数据导入的话可以使用 insert 语句，但如果我们有一个大文件的话，那么我们需要先把文件内容读取出来，然后再 insert 进去，可不可以直接将文件导入到 Hive 中呢？

显然是可以的，因为我们之前已经用过了，也就是 load data local inpath，下面我们再来介绍一下这个语句。

load data [local] inpath '数据路径' [overwrite] into table table_name [partition (partition_col1 = val1, ...)]

• load data: 表示加载数据;
• local: 表示从本地加载, 否则从HDFS上加载;
• inpath: 表示数据加载的路径;
• overwrite: 表示覆盖表中已有数据, 否则追加;
• into table: 表示加载到哪张表中;
• partition: 表示上传到指定分区, 需要是分区表;

然后就是 insert 语句，这个比较简单了，和关系型数据库是类似的。

insert into 表(col1, col2, ...) values (val1, val2, ...), (val1, val2, ...)

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

insert into 表1 as select * from 表2; -- 表示将表2的记录插入到表1当中，当然还可以筛选字段、指定行数

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

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

create table 表1 as select * from 表2; -- 根据从表2选出的字段创建表1, 然后再将数据导入到表1中
-- 表1的字段类型和表2保持一致, 当然这里我们还可以执行更加复杂的查询, 然后根据其结果创建新表

数据的导出

insert 导出

你没有看错，insert 除了可以导入之外，还可以导出。可以将查询的结果导出到本地：

insert overwrite [local] directory "本地目录(不存在则创建)" select * from 表名

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

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

我们看到数据都在里面了，但是貌似这些数据都连在一起了，这样的数据貌似也不好用啊。而我们在创建表的时候可以指定分隔符，表示我们通过本地数据导入的时候，本地数据的每一行的每一个字段之间用指定分隔符分隔。那么同理我们能不能在导出的时候也指定个分隔符、使得导出的数据的每一行的每一个字段之间也是用指定分隔符分隔呢？答案是可以的，语法跟创建表的时候是一样的。

现在再来看看数据长什么样子。

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

HDFS命令导出

这个比较简单，没必要介绍了，hdfs dfs -get。

Hive Shell命令导出

直接在linux终端下，通过 hive -e 'select * from student' > file，这个也很简单，hive -e 查询，表示就是在不进入shell的时候执行查询，然后导入到文件里面去。如果想追加，那么使用 >> 即可。

这种方式导出的数据要更好看一些。

export命令导出

这个不是很常用，它会导出到 HDFS 上。

export table 表名 to 'hdfs目录'

虽然指定的 yoyoyo.txt，但其实这是一个目录，里面有一个data目录，data目录里面才是我们导出的文件。

除了 export 之外，还有 import，import 必须要和 export 搭配使用，因为 import 只能导入 export 导出的数据。并不是任何一个存在的 HDFS 目录，都可以 import，export导出的数据才可以import，注意到那个_metadata了吗？这就表示是 export 导出的。

至于导入可以使用：import table 表名 from "hdfs路径"，注意：table可以不存在，但是如果存在则列的信息必须要一致。

export 和 import 主要用于两个 Hadoop 平台集群之间的 Hive 表迁移。

Sqoop导出

这是一个框架，也是Apache的一个顶级项目，所以它不单单是一个命令，这个框架我已经介绍过了。Sqoop不仅可以把文件 HDFS 上的数据导入到本地，还可以导入到数据库，这个是可以和你的业务进行对接的。

数据的清空

清空表：truncate table 表名，结果会删除 HDFS 上的数据，但是元数据会保留。就跟MySQL一样，只删除数据、但是字段信息保留。注意：清空表只能清空内部表（管理表），外部表 Hive 是没有权限清空的，如果试图清空一个外部表是会报错的。

数据查询

数据的查询可以说是重中之重，之前介绍的创建数据库、数据表、修改表等等都远没有数据的查询重要，所以这里我们从零开始详细介绍。即便HQL和SQL是高度相似的，但我们还是建议稳扎稳打，一步步前进，把路重新再走一遍。

这里我们创建一张新的表，然后导入新数据，然后进行语法学习。首先是创建表：

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

然后我们用 Python 的 faker 模块生成一些伪数据，导入进去：

先来看一下数据集长什么样子：

里面的数据都是随机生成的，不涉及任何真实的人名、地名，如有雷同纯属巧合。

使用 select 语句初步探索

查询是数据库中最常见的操作，所以我们先来了解一下基本的查询语句。

查询指定字段：

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

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

投影操作中包含一个特殊的操作，就是查询表中所有的字段。

查询全部字段：

查看表中的全部字段可以使用一个简单的写法，就是使用星号（*）表示全部字段。例如，以下语句查询员工表中的所有数据：

select * from staff;

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

-- 等价于
select id, name, city, company, email, date from staff;

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

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

扩展操作：

比如：我们将员工的 id 加10，返回。

是我们看到，返回的字段名变了，这里给出了一个 _c1，那么我们可不可以指定返回的名字呢？答案是可以的。

指定别名：

为了提高查询结果的可读性，可以使用别名为表或者字段指定一个临时的名称。Hive 中使用关键字 as 指定别名。我们为上面的示例指定一些更好理解的标题：

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

select * from staff as s;

返回的结果是一样的，另外我们看到给表起不起别名貌似跟之前没啥区别啊。是的，对于单表查询来讲给表起不起别名没任何影响。但是，对于多个表 join 的时候，为了区分 select 后面的字段到底是哪一个表的字段，我们在选择的时候就不会只输入字段名了，而是会通过 表名.字段名 的方式，可如果表名比较长，那么给表起别名就很有意义了。

select s.id, s.name from staff as s;

我们要找 staff 表下面的id，但是我们给 staff 起了个别名叫 s，那么我们就可以通过 s 来代指 staff 这张表。当然对于单表查询而言，即使给表指定了别名，我们依旧可以只输入字段名。

select id, name from staff as s;

这种方式也是可以的，只不过单表查询一般不会给表起别名。

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

select id + 1 id_incr_1, name n from staff s;

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

注释：

另在 Hive 中可以像其它编程语言一样使用注释；注释可以方便我们理解代码的作用，但不会被执行。Hive 只支持单行注释，以两个减号（--）开始，直到这一行结束。而一般的关系型数据还支持C语言风格的多行注释（/* … */），但 Hive 不支持。

select id, --id
name -- 姓名
-- 我们要从staff表中查询id和name
from staff;

无表查询：

在 Hive 中，select ... from ... 是最基本的查询形式；但是，有时候我们会看到一种更简单的查询：只有 select 子句，没有 from 子句的查询。

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

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

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

查询条件：

在 Hive 语句中，使用关键字 where 指定查询的过滤条件。以下语句只返回 id 为 30 的员工信息：

其中，where 位于 from 之后，用于指定一个或者多个过滤条件；只有满足条件的数据才会返回，其他数据将被忽略。比如我们筛选所有的字段，但并不是每一行都要，只有当该行的 id 字段所对应的值等于 30 的，我们才要。

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

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

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

比较运算符：

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

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

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

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

然后是 in 操作符。

in 运算符可以用于查找列表中的值，比如以下例子表示查询 id 为30或36或38的员工。

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

空值判断：

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

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

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

• null = 5
• null = null
• null != null

我们这里测试一下，我们就选择 id 不为 null的，显然条件应该是 id is not null。但是这里我们写成 id != null 看看会有什么结果：

显然没有返回任何值，因为不管 id 的值什么，它在和 null 使用 != 进行比较的时候结果都是 null，而 null 为假，所以不返回数据。

因此我们应该使用 is not null 的方式。

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

复合条件：

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

and："逻辑与" 运算符，只有当两边的条件都为真时，结果才为真，返回数据；否则，不返回数据

or："逻辑或"运算符。只要有一个条件为真，结果就为真，返回数据；否则，不返回数据。

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

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

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

第一个查询由于 and 左边的结果为假，肯定不会返回任何结果，因此也就不会计算 1/0；第二个查询由于 or 左边的结果为真，一定会返回结果，同样不会产生除零错误。

not："逻辑非"运算符。用于将判断结果取反，真变为假，假变为真；空值取反后仍然为空值。

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

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

运算符优先级：

将多个逻辑运算符进行组合，可以构造任意复杂的查询条件。不过，需要注意不同的运算符之间的优先级问题。

注意条件中有一个 name is null，我们这里的name很明显没有为空的，按理说应该筛选不出来数据才对啊。所以此时就涉及到运算符优先级问题，对于 id >= 90 来说，它的右边是 or，因此只要左边成立整体就成立，因此 id >= 90 的记录会直接被筛选出来。但如果 id < 90的话，显然 or 左边就不成立了，因此要看 or 右边的，也就是 id <= 5 and name is null。而 and 右边的 name is null 不成立，加上 and 要求两边都成立整体才成立，所以 id >= 5 and name is null 显然为假。

而我们的本意是先找出 id >= 90 或者 id <= 5的，然后在此基础上再找到 name 为空的，所以应该这么做。

通过 括号 将其限定起来即可。

去除重复值：

我们在筛选数据的时候会有重复数据，比如员工都来自哪些地区，但是某个地区可能出现不止一次。因此可以使用 distinct 关键字去除查询结果中的重复数据。

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

如何在 Hive 中进行模糊查找

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

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

LIKE 运算符：

查找 name 中包含 '川' 的。

该语句使用了一个新的运算符：like。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 是区分大小写的，这里以之前创建的 girls 表为例。

正则表达式：

正则表达式用于检索或者替换符合某个模式（规则）的文本，很多的编程语言和编辑工具都提供了正则表达式搜索和替换，比如文本编辑器 Notepad++。正则表达式的具体使用不会说的特别细致，网上一大堆可以去搜索。

而 Hive 中通过 rlike 实现正则表达式。

Hive 里面使用的正则表达式基于Java，但是注意：Java里面正则表达式的 \w 是不匹配中文的，而Python是匹配的，这一点是一个差别。

使用order by对数据进行排序显示

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

select col1, col2, ...
from t
order by col1 [asc | desc], col2 [asc | desc], ...;

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

对于升序排序，数字按照从小到大的顺序排列，字符按照编码的顺序排列，日期时间按照从早到晚的顺序排列；降序排序正好相反。

还有一个关键的点就是空值，如果是空值的话，那么要怎么排序呢。

在Hive中，默认空值为最小。因此升序排序是在最上面，降序排序是在最下面。

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

Hive 主要的功能就是对数据进行处理和分析，为了避免重复造轮子，提高数据处理的效率，Hive 为我们提供了许多标准的功能模块：函数（Function）。

那么什么是函数呢？函数是一种具有某种功能的模块，可以接收零个或多个输入值，并且返回一个输出值。而函数主要分为两种类型：

标量函数（scalar function）。标量函数针对每个输入参数，返回一个输出结果。例如，ABS(x) 可以计算 x 的绝对值。

聚合函数（aggregate function）。聚合函数基于一组数据进行计算，返回一个输出结果。例如，AVG 函数可以计算一组数据的平均值。

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

下面来举个栗子：

常见函数之文本数据处理

上面我们介绍了 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('夏', '色', '祭'); -- 夏色祭

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('夏色祭世界第一', '世界第一'); -- 4
-- 查找不到返回0

locate(y, x, pos)：查找字符串 y 在 字符串 x 的 第 pos 个字符之后首次出现的位置，注意：这里的x和y位置变了，所以Hive这点就比较恶心。

select instr('夏色祭世界第一世界第一', '世界第一'); -- 4
select locate('世界第一', '夏色祭世界第一世界第一', 5); -- 8

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

-- 计算的是字符个数
select length('夏色祭世界第一'); -- 7

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

select printf("%s ||| %s ||| %s", id, name, date) from staff limit 5;
-- 1 ||| 吉本 香織 ||| 1970-01-03
-- 2 ||| 小泉 舞 ||| 1992-12-22
-- 3 ||| 宮沢 あすか ||| 1970-10-22
-- 4 ||| 小泉 篤司 ||| 1972-06-30
-- 5 ||| 田中 健一 ||| 2009-03-06

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

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

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

select regexp_replace("satori", "s", "S");
-- Satori

select regexp_replace("matsuri fubuki", "ma|ki", "~~");
-- ~~tsuri fubu~~

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); -- 夏色祭夏色祭夏色祭夏色祭夏色祭

lpad(x, count, pad)：用字符串pad对字符串x进行左填充，达到字节数为count

-- 注意: 我们说是字节, 所以此时中文不再适合, 容易出错
-- 当然如果填充字符pad是ascii字符, 那么是可以的
select lpad("matsuri", 20, "fubuki");  -- fubukifubukifmatsuri
-- 此时字节为19个, 如果再加一个字符串 "fubuki" 就超出 20 了, 所以到此为止了
-- 同理还有 rpad, 表示右填充

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

日期相关的函数

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

date_format：格式化时间

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

date_add：时间和天数相加

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

datediff：时间和时间相减

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

如果是timestamp类型、想转成date的话，可以使用to_date函数，当然使用cast也是可以的。

而且我们看到这些函数都是直接作用在字符串上的，并且字符串必须是 "2020-1-1" 这种，如果是 "2020/1/1" 这种格式会报错。

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

下面我们介绍一种为 HQL 语句增加逻辑处理功能的方法：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 字段，然后when后面写一个值，来判断字段的值和when后面的值是否相等，但这也仅能判断相等的情况。如果是更复杂的情况，那么就要使用搜索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，计算一组数据中的最小值

接下来我们分别演示这些函数的作用。

-- 查看总数,对于count，可以使用count(*)、count(字段)，甚至是count(1)也可以
select count(*), count(date), count(1) from staff; -- 

当前记录总数为 100，在聚合函数的参数中加上 distinct 关键字，可以在计算之前排除重复值。

聚合函数在计算时，忽略输入值为 NULL 的数据行；如 count(city) 计算的是 city 不为null的数量（当然这里没有null），但是count(*)和count(1)例外，它们计算的是总数。

这里我们只有 id 这一个整型，就以它为例子了，而且我们看到 avg(id) 等价于 sum(id) / count(id)。

group by分组统计

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

数据分组：

SQL 中的 group by 子句可以将数据按照某种规则进行分组。

group by 将 city 的每个不同取值分为一组，每个组返回一条记录。由于员工表中存在68种不同的city，所以返回的结果是68条记录。该语句使用 distinct 关键字的效果相同，当然我们不仅可以指定一个聚合字段，还可以指定多个，group by col1, col2, ...。

然而我们在分组的时候，绝大部分都是搭配聚合函数使用。基于分组字段，将不同的值分为n组，然后在组内分别应用聚合函数进行统计，每个组返回一个结果。

另外group by后面也是可以跟表达式的。

如果包含空值的话，那么所有的空值会被分为一组。

分组后的过滤：

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

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

另外 having 后面的聚合里的字段并不一定要出现在select中，比如：select city from staff group by city having count(*) > 2。

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

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

空字段赋值

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

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

列转行

假设我们有这样的数据：

move		category
疑犯追踪    悬疑,动作,科幻,剧情
lie to me    悬疑,警匪,动作,心理,剧情
战狼2       战争,动作,灾难

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

move	   category
疑犯追踪     悬疑
疑犯追踪     动作
疑犯追踪     科幻
疑犯追踪     剧情
lie to me   悬疑
lie to me   警匪
lie to me   动作
lie to me   心理
lie to me   剧情
战狼2       战争
战狼2       动作
战狼2       灾难

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

首先来看一个 explode 函数：

可以看到 explode 这个函数会自动将数组打开，将元素一个一个的取出来。既然如此，如果想实现我们刚才说的效果，那么再加上movie字段不就行了吗？

但很不幸，这样是会报错的，单独写可以，要是和其它字段组合就不行了。因为它没有办法自动做笛卡尔积，所以如果想实现这一点，我们需要借助一个语法。

lateral view：这个中文翻译为 侧写，比如说：小姐姐你的裙子好漂亮啊，把你肚子上的小肉肉都给遮住了。

select movie, category from movie_info; -- 这种语法是对的，但是不是我们想要的
select movie, explode(category) from movie_info;--是我们想要的，但是语法不对

--怎么办呢？需要这么写
--lateral view explode(category)表示打开后的结果，然后起一个别名category_name
--在select的时候筛选即可,至于那个tem_table是什么？那个是给表起的别名
--这个别名是必须要有的，即便你不用，也要写在那里
select movie, category_name 
from 
movie_info lateral view explode(category) tem_table as category_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个连接条件。但是多个连接条件之间只能用and，如果是or会报错。个人觉得这算是缺陷吧，用的时候要注意。

窗口函数

懒得写了，有点累了，可以去网上搜索，窗口函数比想象中简单。

系统函数 与 自定义函数

首先 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 方向的，所以这里不再说了。

分桶表

分桶表用的比较少，它和分区表一样，都是为了方便查询而使用的。只不过分区针对的是数据的存储路径，分桶针对的是数据的存储文件。

• 分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可以形成合理的分区。
• 分桶是将数据集分解成更容易管理的若干部分的另一个技术。

创建&&导入数据

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

create table tong(id int, name string)
clustered by(id) 
--分区表是partitioned by,分桶是clustered by
--但是我们发现这分桶的字段是表的普通字段里面存在的
--因此这是分桶和分区的一个很大的区别，
--分区字段相当于在原有字段的基础上多增加了一个
--分桶字段则是表字段里面已经存在的字段，所以不需要指定类型了
into 4 buckets --分成四个桶
row format delimited fields terminated by ',';

然后来导入数据：

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

分桶抽样查询

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

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

这里的 tablsample(bucker x out of y on id) 是一个语法，on id很好理解，就是基于id，y必须是 table 的 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，即1 5 9 13这四个桶。也正因为如此我们需要x小于等于y，否则报错。

那可能会有人觉得，会不会到最后我们要取的桶的位置超过所有的桶数，比如总共16个桶，但是我们却要第17个桶，会不会出现这种结果呢？显然不会的，因为x小于等于y就决定了，不会出现这种结果。假设有z个桶，我们分y个，显然我们要 z/y 个，那么最后一个桶的位置就是 x + (z/y - 1) * y = x + z - y，由于x小于等于y，因此最多取到最后一个桶（当x=y的时候）。

我们看到此时访问的就是第一个桶里面的数据，怎么样是不是很简单呢。

fetch 抓取& 本地调优

fetch抓取：

fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。例如：select * from table，在这种情况下，Hive可以简单的读取 table 的存储目录下的文件，然后输出查询结果到控制台。

在 hive-default.xml.template 中有如下属性：

<property>
	<name>hive.fetch.task.conversion</name>
    <value>more</value>
</property>

默认是more，老版本minimal，该属性为more 的时候，在全局查找、字段查找、limit查找等、都不走MapReduce。

本地调优：

大多数的 job 是需要 Hadoop 提供的完整可扩展性来处理大数据集的。不过，有时 Hive 的输入数据量是非常小的。在这种情况下，为触发查询执行任务消耗的时间可能会比实际job的执行时间要多得多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。

用户可以通过设置hive.exec.mode.local.auto的值为true，来让hive在适当的时候启动这个优化。

set hive.exec.mode.local.auto=true # 开启本地MapReduce

# 设置local MapReduce的最大数据输入量，当输入量小于这个值时采用local MapReduce的方式，默认是134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=500000000

# 设置local MapReduce的最大文件输出个数，当文件输入个小于这个值的时候，采用local MapReduce的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10

小结

Hive的内容我们就说到这里，重点是在查询上面，所以我们花了很大的笔墨去介绍Hive的语法，当然窗口函数是个例外，写到最后实在是累了。就我本人而言，Hive 的部署、调优什么的，基本上不由我负责，我们只是使用 Hive 进行 OLAP 罢了。

当然 Hive 还有很多的内容我们没有说，比如：join 只是一笔带过，排名函数、两个结果集进行union等等我们也没有说。不过这些东西基本上和SQL一样，而且网上也有很多资料。

下一篇博客我们来聊一聊Spark。