刘智豪
2022-11-29
 2780

Hive


Hive数据仓库

image-20221025164346480

第一章 Hive 体系架构（了解）

1.1 Hive 概述

Hive 由 Facebook 公司开源，主要用于解决海量结构化日志数据的离线分析。Hive 是建立在 Hadoop 上的一个开源的数据仓库工具，它提供了一系列工具，可以用来进行数据提取、转化、加载。这是一种在 Hadoop 上实现大规模数据存储、查询和分析的机制。Hive 可以将结构化的数据文件映射为一张表，并提供了类 SQL 查询语言 HiveQL（Hive Query Language）。Hive 的本质是将 HiveQL 语句转化成 MapReduce 程序，并提交到 Hadoop 集群上运行。Hive 让不熟悉 MapReduce 的开发人员直接编写 SQL 语句来实现对大规模数据的统计分析操作，大大降低了学习门槛，同时也提升了开发效率。总之，Hive 处理的数据存储在 HDFS 上，Hive 分析数据底层的实现是 MapReduce，执行程序运行在 YARN 上。

相对于传统关系型数据库，从内部实现原理和 HiveQL 语言运行机制来看，Hive 具有如下特点：

1. 查询语言与 SQL 接近。

由于 SQL 被广泛应用在数据仓库中，因此，专门针对 Hive 的特性设计了类 SQL 的查询语言 HiveQL。熟悉 SQL 开发的开发者可以很方便地使用 Hive 进行开发。HiveQL 中对查询语句的解释、优化、生成查询计划是由 Hive 引擎完成的。

2. 并行执行。

Hive 中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的，查询计划被转化为 MapReduce 任务，在 Hadoop 中并行执行。

3. 使用 HDFS 存储。

Hive 是建立在 Hadoop 之上的，所有 Hive 的数据都是存储在 HDFS 中的。而其它数据库则将数据保存在块设备或者本地文件系统中。

4. 支持自定义数据格式。

Hive 中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三个属性：列分隔符（通常为空格、“\t”、“\x001”）、行分隔符（“\n”）以及读取文件数据的方法（Hive 默认的文件格式包括 TextFile、SequenceFile、RCFile 等）。由于在加载数据的过程中，不需要从用户数据格式到 Hive 定义的数据格式的转换，因此，Hive 在加载的过程中不会对数据本身进行任何修改，而只是将数据内容复制或者移动到相应的 HDFS 目录中。其加载数据的效率高于传统数据库。

5. 不支持数据更新。

由于 Hive 是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive 不支持对数据的改写和添加，所有的数据都是在加载时确定好的。这与传统数据库支持增删改有所不同。

6. 不支持索引。

之前已介绍过，Hive 在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描，因此也没有对数据中的某些 Key 建立索引。Hive 要访问数据中满足条件的特定值时，需要暴力扫描整个数据，因此访问延迟较高。由于 MapReduce 的引入，Hive 可以并行访问数据，因此即使没有索引，对于大数据量的访问，Hive 仍然可以体现出优势。传统数据库中，通常会针对一个或者几个列建立索引，因此对于少量特定条件的数据的访问，数据库可以有很高的效率，较低的延迟。由于数据的访问延迟较高，决定了 Hive 不适合在线数据查询。

7. 执行延迟高。

Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高，另外一个导致 Hive 执行延迟高的因素是 MapReduce 框架。由于 MapReduce 本身具有较高的延迟，因此在利用 MapReduce 执行 Hive 查询时，也会有较高的延迟。相对来说，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库处理能力的时候，Hive 的并行计算优势就能够显现出来了。

8. 可扩展性高。

由于 Hive 是建立在 Hadoop 之上的，因此 Hive 的可扩展性和 Hadoop 的可扩展性是一致的。而数据库由于 ACID 语义的严格限制，扩展性非常有限，目前最主流的数据库 Oracle，其集群在理论上的扩展能力也只有 100 台左右。

9. 数据规模大。

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

1.2 Hive 体系架构

Hive 通过给用户提供的一系列交互接口，接收到用户提交的 Hive 脚本后，使用自身的驱动器 Driver，结合元数据 MetaStore，将这些脚本翻译成 MapReduce，并提交到 Hadoop 集群中执行，最后将执行返回的结果输出到用户交互接口。Hive 体系架构如下图所示。

图片描述

由上图可知，Hive 体系架构中主要包括如下组件：Cli、JDBC/ODBC、Web UI、Thrift Server、MetaStore 和 Driver，这些组件可以分为两类：客户端组件，服务端组件。另外，Hive 还需要 Hadoop 的支持，它使用 HDFS 进行存储，使用 MapReduce 进行计算。

  • 客户端组件

作为数据仓库，Hive 充分利用 Hadoop 分布式存储和计算的能力，向用户了提供丰富的编程和命令接口，以支持数据查询、汇总和分析功能。

  1. Cli。

    Cli（Command line interface）是 Hive 命令行接口，是最常用的一种用户接口。Cli 启动时会同时启动一个 Hive 副本。Cli 是和 Hive 交互的最简单也是最常用方式，只需要在一个具备完整 Hive 环境下的 Shell 终端中键入 hive 即可启动服务。用户可以在 Cli 上输入 HiveQL 来执行创建表、更改属性以及查询等操作。

  2. JDBC/ODBC。

    JDBC 是 Java Database Connection 规范，它定义了一系列 Java 访问各类数据库的访问接口，因此 Hive-JDBC 其实本质上扮演了一个协议转换的角色，把 JDBC 标准协议转换为访问 Hive Server 服务的协议。Hive-JDBC 除了扮演网络协议转化的工作，并不承担其它工作，比如 SQL 的合法性校验和解析等。ODBC 是一组对数据库访问的标准 API，它的底层实现源码是采用 C/C++ 编写的。JDBC/ODBC 都是通过 Hive Client 与 Hive Server 保持通讯的，借助 Thrift RPC 协议来实现交互。

  3. Web UI。

    Web UI 是 Hive 的 Web 访问接口，可以通过浏览器来访问 Hive 的服务。

  • 服务端组件

  1. Thrift Server。

    Thrift 是 Facebook 开发的一个软件框架，它用来进行可扩展且跨语言的服务开发，Hive 集成了 Thrift Server 服务，能让不同的编程语言如 Java、Python 等调用 Hive 接口。

  2. MetaStore 服务（MetaStore service）。

    MetaStore 服务组件用于管理 Hive 的元数据，包括：表名、表所属的数据库（默认是 default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等。Hive 元数据默认存储在自带的 Derby 数据库中，但一般推荐使用 MySQL 存储 MetaStore。元数据对于 Hive 十分重要，因此 Hive 支持把 MetaStore 服务独立出来，安装到远程的服务器集群里，从而解耦 Hive 服务和 MetaStore 服务，保证 Hive 运行的健壮性。

  3. 驱动器（Driver）。

    Driver 组件的作用是将用户编写的 HiveQL 语句进行解析、编译、优化，生成执行计划，然后调用底层的 MapReduce 计算框架。Hive 驱动器由四部分组成：

    1. 解析器（Interpreter）：将 SQL 字符串转换成抽象语法树（AST），这一步一般都用第三方工具库完成，例如 antlr（Another Tool for Language Recognition-可以根据输入自动生成语法树并可视化的显示出来的开源语法分析器）；对 AST 进行语法分析，例如表是否存在、字段是否存在、SQL 语义是否有误。
    2. 编译器（Compiler）：将 AST 编译生成逻辑执行计划。
    3. 优化器（Optimizer）：对逻辑执行计划进行优化。
    4. 执行器（Executor）：把逻辑执行计划转换成可以运行的物理计划，对于 Hive 来说，就是 MapReduce。

这里需要补充说明一下 Hive Server 和 Hive Server2 两者的联系和区别。Hive Server 和 Hive Server2 都是基于 Thrift 的，两者都允许远程客户端使用多种编程语言对 Hive 中的数据进行操作。但是官方表示从 Hive0.15 起就不再支持 Hive Server 了，为什么不再支持 Hive Server 了呢？这是因为 Hive Server 不能处理多于一个客户端的并发请求，究其原因是由于 Hive Server 使用 Thrift 接口而导致的限制，不能通过修改 HiveServer 的代码修正。因此在 Hive0.11.0 版本中重写了 Hive Server 代码得到了 Hive Server2，进而解决了该问题。Hive Server2 支持多客户端的并发和认证，为开放 API 客户端如 JDBC、ODBC 提供更好的支持。

第二章 Hive 安装部署

2.1 安装模式（了解）

根据元数据 MetaStore 存储位置的不同，Hive 安装模式共有以下 3 种。

  • 内嵌模式（Embedded MetaStore）。

    内嵌模式是 Hive MetaStore 最简单的部署方式，Hive 服务和 MetaStore 服务在同一个 JVM 中，使用 Hive 内嵌的 Derby 数据库来存储元数据。但这种模式只能接受一个 Hive 会话，即只能面向一个客户端提供服务。Hive 官方并不推荐使用内嵌模式，此模式通常用于开发者调试环境中，真正生产环境中很少使用。Hive 内嵌模式如下图所示。

    图片描述

  • 本地模式（Local MetaStore）。

    该模式下，Hive 服务和 MetaStore 服务仍在同一个 JVM 中，一个 Hive 会话将启动一个这样的 JVM 来提供服务。不同的是，本地模式不使用 Derby，而是使用 MySQL 这样的独立数据库来存储 MetaStore。常见 JDBC 兼容的数据库都可以作为元数据的存储载体。MySQL 可以部署在本地，也可以部署在独立的物理机器上。Hive 本地模式如下图所示。

    图片描述

  • 远程模式（Remote MetaStore）。

    远程模式将“MetaStore 服务”分离出来，成为一个独立的服务，而不是和 Hive 服务运行在同一个 JVM 上。MetaStore 服务可以部署多个，以提高数据仓库可用性。Hive 远程模式如下图所示。

    图片描述

总体而言，从内嵌模式到远程模式，是逐渐分离的。本地模式，将数据分离开，而远程模式将两种服务分离开。

元数据：又称中介数据、中继数据，为描述数据的数据，主要是描述数据属性的信息，用来自持如只是存储位置、历史数据、资源查找、文件记录等功能。

  • Hive Metadata即Hive的元数据。
  • 包含用Hive创建的database、table、表的位置、类型、属性，字段顺序类型等元信息。
  • 元数据存储在关系型数据库中。如hive内置的Derby、或者第三方如MySQL等。
  • Metastore即元数据服务。Metastore服务的作用是管理metadata元数据，对外暴露服务地址，让各种客户端通过连接metastore服务，由metastore再去连接连接MySQL数据库来存取元数据。

2.2 安装 MySQL

1）查询是否安装mysql

[root@node1 software]# rpm -qa|grep mysql

2）下载安装mysql

CentOS7 的 yum 源中默认没有 MySQL，所以没法直接使用 yum 的在线安装，需要到 MySQL 的官网下载 yum repo 配置文件，这里下载的是 MySQL5.7 版本。

[root@node1 software]# wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

如果出现 -bash: wget: 未找到命令。

[root@node1 software]# yum -y install wget

安装

[root@node1 software]# rpm -ivh mysql57-community-release-el7-9.noarch.rpm

执行完成后会在 /etc/yum.repos.d/ 目录下生成两个 repo 文件 mysql-community.repo 和 mysql-community-source.repo。

进入到 /etc/yum.repos.d/ 目录，然后安装 MySQL 的服务器端：

[root@node1 software]# cd /etc/yum.repos.d/
[root@node1 software]# yum -y install mysql-server

如果安装失败，运行如下代码

image-20221025161247135

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
yum -y install mysql-community-server

启动mysql服务

[root@node1 software]# service mysql start

如果启动失败

[root@node1 software]# systemctl start mysqld.service

3）查看初始密码（注：密码为冒号后面的所有字符！）

[root@node1 software]# grep 'temporary password' /var/log/mysqld.log

4）登录mysql

[root@node1 software]# mysql -uroot -p

5）修改密码

修改密码策略
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;
修改密码
mysql> SET PASSWORD = PASSWORD('123456'); 
刷新规则允许外部访问
update mysql.user set host='%' where user='root';
flush privileges;

2.3 安装 Hive

0）hive下载地址：http://archive.apache.org/dist/hive/

1）上传hive安装包和mysql驱动包到/opt/software下

2）把apache-hive-3.1.2-bin.tar.gz解压到/opt下

[root@node1 software]# tar -zxvf apache-hive-3.1.2-bin.tar.gz -C /opt/

3）重命名apache-hive-3.1.2-bin 为hive-3.1.2

[root@node1 opt]# mv apache-hive-3.1.2-bin/ hive-3.1.2

4）添加hive环境变量

[root@node1 opt]# vim /etc/profile.d/bigdata_env.sh
#Hive_HOME
export HIVE_HOME=/opt/hive-3.1.2
export PATH=$PATH:$HIVE_HOME/bin

5）让配置文件生效

[root@node1 opt]# source /etc/profile.d/bigdata_env.sh

6）查看环境变量是否生效

[root@node1 opt]# echo $HIVE_HOME
/opt/hive-3.1.2

显示/opt/hive-3.1.2 代表安装成功，接下完成一些配置即可启动

7）解决日志Jar包冲突

[root@node1 opt]# mv $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.jar $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.bak

8）在$HIVE_HOME/conf目录下新建hive-site.xml文件

[root@node1 opt]# vim $HIVE_HOME/conf/hive-site.xml

添加内容如下内容

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <!-- jdbc连接的URL -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://node1:3306/metastore?useSSL=false</value>
</property>

    <!-- jdbc连接的Driver-->
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
</property>


	<!-- jdbc连接的username-->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>

    <!-- jdbc连接的password -->
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>123456</value>
</property>

    <!-- Hive默认在HDFS的工作目录 -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive/warehouse</value>
    </property>
    
   <!-- Hive元数据存储的验证 -->
    <property>
        <name>hive.metastore.schema.verification</name>
        <value>false</value>
    </property>
   
    <!-- 元数据存储授权  -->
    <property>
        <name>hive.metastore.event.db.notification.api.auth</name>
        <value>false</value>
    </property>
     <!-- 打印表头  -->
    <property>
    <name>hive.cli.print.header</name>
    <value>true</value>
    </property>
       <!-- 打印库名  -->
   <property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>
  </property>
       <!-- 指定hiveserver2连接的host -->
    <property>
        <name>hive.server2.thrift.bind.host</name>
        <value>node1</value>
    </property>
    <!-- 指定hiveserver2连接的端口号 -->
    <property>
        <name>hive.server2.thrift.port</name>
          <value>10000</value>
    </property>
</configuration>

9).将MySQL的JDBC驱动拷贝到Hive的lib目录下

[root@node1 software]# cp /opt/software/mysql-connector-java-5.1.37.jar $HIVE_HOME/lib

2.4启动Hive

2.4.1初始化元数据库

1)登录mysql

[root@node1 software]# mysql -uroot -p123456

2)创建Hive元数据库

mysql> create database metastore;
mysql> quit;

3）初始化元数据库

[root@node1 software]# schematool -initSchema -dbType mysql -verbose
2.4.2启动Hive
[root@node1 software]# hive
2.4.3简单hive操作
hive> show databases;
hive> use default;
hive> show tables;
hive> create table student (id int,name string);
hive> insert into student values(1,"lzh");
hive> select * from student;
2.4.4hive部分交互命令

1）hive -e 不进入hive的交互窗口执行sql语句

[root@node1 ~]# hive -e "select * from student;

2）hive -f 执行脚本中的sql

[root@node1 ~]# vim test.sql
select * from studen;

[root@node1 ~]# hive -f test.sql

2.5JDBC访问Hive

1）在$HADOOP_HOME/etc/hadoop/core-site.xml文件中添加如下配置，如果是完全分布式需要把core-site.xml文件分发

<!-- 配置root允许通过代理访问的主机节点 -->
<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
</property>
<!-- 配置root允许通过代理用户所属组 -->
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
</property>

2）重启hadoop集群

3）启动hiveserver2服务

[root@node1 ~]# hive --service hiveserver2

4）在IDEA中进行连接测试

image-20221026221720817

image-20221026221604289

第三章 Hive 数据类型、文件格式与数据模型

3.1 Hive 数据类型

Hive 数据类型分为两类：基本数据类型和集合数据类型。

  • 基本类型。

又称为原始类型，与大多数关系数据库中的数据类型相同。

Hive 的基本数据类型及说明如下图所示。

Hive数据类型Java数据类型长度例子
Tinyintbyte1byte有符号整数-128~127
smallintshort2byte有符号整数1S
intint4byte有符号整数1
bigintlong8byte有符号整数1L
Booleanboolean布尔类型，true或者falseTRUE FALSE
floatfloat单精度浮点数3.14159
doubledouble双精度浮点数3.14159
stringstring字符系列。可以指定字符集。可以使用单引号或者双引号。‘now is the time’ “for all good men”
timestamp时间类型
binary字节数组

和其它的 SQL 语言一样，这些都是保留字。需要注意的是所有的这些数据类型都是对 Java 中接口的实现，因此这些类型的具体行为细节和 Java 中对应的类型是完全一致的。例如，string 类型实现的是 Java 中的 String，float 实现的是 Java 中的 float 等等。

  • 集合数据类型。

除了基本数据类型，Hive 还提供了 3 种集合数据类型：Array、Map、Struct。所谓集合类型是指该字段可以包含多个值，有时也称复杂数据类型。Hive 集合数据类型及说明如下图所示。具体使用详见后面章节。

图片描述

3.2 Hive 文件格式

Hive 支持多种文件格式，常用的有以下几种：TextFile、SequenceFile、RCFile、ORCFile 和 Parquet。

  • TextFile。

    默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到 HDFS 上不进行处理。源文件可以直接通过 hadoop fs -cat 查看。采用行式存储，数据不做压缩，磁盘开销大，数据解析开销大。可结合 Gzip、Bzip2 等压缩算法（系统自动检查，执行查询时自动解压），但使用压缩方式时，Hive 反而不会对数据进行合并和拆分。生产中用的比较多。

  • SequenceFile。

    SequenceFile 是 Hadoop API 提供的一种二进制文件，其具有使用方便、可分割、可压缩的特点。SequenceFile 将数据以 key-value 键值对的形式序列化到文件中。采用行式存储，比默认的 TextFile 源文件格式的还要大。生产中基本上是不会用。

  • RCFile。

    RCFile 是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一行数据在一个块上，避免读一行数据需要读取多个块。其次，块数据列式存储，有利于数据压缩和快速的列存取。理论上具有高查询效率，但 Hive 官方说效果不明显，只有存储上能省 10% 的空间，所以不好用，生产中用的少。

  • ORCFile。

    属于 RCFile 的升级版，存储方式为列式存储，有多种压缩方式，并且有很高的压缩比，支持多种索引和复杂的数据结构。生产中用的多。

  • Parquet。

    Parquet 采用列式存储，具有高效的压缩方式，不与任何数据处理技术绑定，可用于多种数据处理框架。但是不支持增删改，只支持查询。适用于字段多，无更新、只取部分列的查询,生产中用的多。

通过以上对比，我们可以发现：

  1. 生产中用的最多的是 TextFile、ORCFile 和 Parquet，其余基本不用。
  2. 这三者压缩比由高到低分别是：ORCFile、Parquet 和 TextFile。
  3. 这三者查询速度由高到低分别是：Parquet、ORCFile 和 TextFile。

另外 SequenceFile，RCFile，ORCFile 和 Parquet 格式的表不能直接从本地文件导入数据，数据要先导入到 TextFile 格式的表中，然后再从表中用 insert 导入 SequenceFile，RCFile，ORCFile 和 Parquet 表中。

以上文件格式中涉及到 行式存储与列式存储，如下图所示，这里做个简单的对比：

图片描述

idnameage
1001zangsan18
1002lisi19
1003wangwu30

行：select * from student where id=1001; 查一个数据块

列：select * from student where id=1001; 查了3个数据块

  1. 行式存储一定会把同一行数据存到同一个块中，在 select 查询的时候，是对所有字段的查询，不可以单独查询某一列。
  2. 列式存储同一列数据一定是存储到同一个块中，换句话说就是不同的列可以放到不同块中，在进行 select 查询的时候可以单独查询某一列。

行式存储：

优点：全字段查询比较快。

缺点：当查询一张表里的几个字段的时候，底层依旧是读取所有的字段，这样查询效率降低，并且会造成不必要的资源浪费，而且，生产中很少会出现需要全字段查询的场景。

列式存储：

优点：当查询某个或者某几个字段的时候，只需要查看存储这几个字段的这几个 block 就可以了，大大的减少了数据的查询范围，提高了查询效率。

缺点：当进行全字段查询的时候，数据需要重组，比单独查一行要慢。

3.3 Hive 数据模型

Hive 中所有的数据都存储在 HDFS 中，根据对数据的划分粒度，Hive 包含以下数据模型：表（Table）、分区（Partition）和桶（Bucket）。从表到分区，再到桶，对数据的划分粒度越来越小。

  • 表。

    Hive 的表和关系数据库中的表相同，具有各种关系代数操作。Hive 中有两种表：内部表（Table）和外部表（External Table）。

    • 内部表（Table）。

    Hive 默认创建的表都是内部表，这些表的数据存储在由配置项 hive.metastore.warehouse.dir（例如 /user/hive/warehouse）所定义的 HDFS 目录下，每一个 Table 在该数据仓库目录下都拥有一个对应的子目录。当删除一个内部表时，Hive 会同时删除这个数据目录。内部表不适合和其它工具共享数据。

    • 外部表（External Table）。

    Hive 创建外部表时需要指定数据读取的目录，外部表仅记录数据所在的路径，不对数据的位置做任何改变，而内部表创建时就把数据存放到默认路径下，当删除表时，内部表会将数据和元数据全部删除，而对外部表只删除元数据，数据文件不会删除。外部表和内部表在元数据的组织上是相同的，外部表加载数据和创建表同时完成，并不会移动数据到数据仓库目录中。

  • 分区。

    假设 Hive 中有一个数据库 test，test 中有一个内部 表 student，存储在 /user/hive/warehouse/ test.db/student 目录，现在按照院系来分区，数学系、艺术系、物理系等。那么所有属于同一个院系的学生将会存储在同一个分区中，而一个分区在存储上表示为一个表目录下面的一个子目录。如数学系的学生都存储在目录 /user/hive/warehouse/test.db/student/department=maths 目录下。此例中，department 我们称为分区字段（注意，它不是表的字段），maths 我们称为分区标识。对于数学系学生的查询将在这个子目录下进行，这样比在全表中查询效率更高。分区表分为静态分区表和动态分区表，在建表时都需要规定分区字段，不同的是静态分区表需要手动指定分区标识（导入或插入数据时，需像这样指定 department=maths），动态分区可按插入的数据自动生成分区标识。后面会有示例说明。

    注意：内部表、外部表都可以使用分区。

  • 桶。

    分桶就是将同一个目录下的一个文件拆分成多个文件，每个文件包含一部分数据，方便获取值，提高检索效率。分区产生不同的存储路径，分桶产生不同的数据文件。分区提供一个隔离数据和优化查询的便利方式，但并非所有的数据集都可形成合理的分区；分桶是将数据集分解成更容易管理的若干部分的另一种技术。

    用户可以将表数据按某列的 HASH 函数值分配到桶中。例如，将表再划分为 n 个桶，则将在表目录下产生 n 个文件。对于上面学生表，可先分区，如果将每个分区再划分两个桶，则在每个分区目录下会有两个文件，记录了同一个院系（因为是一个分区）的不同学生。

    Hive 通过某列的 HASH 值取模来决定桶的分配。使用桶分配数据的原因有两个方面：第一，合理设计的 Hash 函数可以将数据均匀地分割，查询时可将查询条件值按同样的 Hash 函数运算得到一个 Hash 值，这样就可以快速定位到某个桶，而不用进行全量数据扫描。第二，分桶使采样过程更高效，从而降低 Hive 的查询时间。

    注意：桶可以在内部表、外部表、分区表上进行。

概言之，表（内部表、外部表）是基本形式，分区和分桶都是按某种规则将表进一步拆分以提高查询效率的手段。

第四章 Hive 函数

4.1 内置运算符

Hive 支持多种内置运算符和内置函数，方便开发人员调用，还可以自定义函数（放到后续章节）来实现特定功能。

内置运算符包括算术运算符、关系运算符、逻辑运算符和复杂运算符（放到后续章节），关于 Hive 内置运算符的说明如下图所示。

图片描述

4.2 内置函数

4.2.1查看系统内置函数
hive (default)> show functions;
4.2.2数学函数
  • round 四舍五入
hive (default)> select round(3.1415926,2);

2 表示保留到 2 位小数，1 表示保留到 1 位小数，0 表示保留到个位，-1 表示保留到十位，-2 表示保留到百位。

  • ceil 向上取整
hive (default)> select ceil(3.14),ceil(-3.14);
  • floor 向下取整
hive (default)> select floor(3.14),floor(-3.14);
  • pow 幂函数
hive (default)> select pow(2,3);
  • abs 绝对值
hive (default)> select abs(3.14),abs(-3.14);

除了上面的例子外，还有以下这些函数，不再一一举例：

取随机数函数: rand

自然指数函数: exp

以 10 为底对数函数: log10

以 2 为底对数函数: log2

对数函数: log

二进制函数: bin

十六进制函数: hex

反转十六进制函数: unhex

进制转换函数: conv

正取余函数: pmod

正弦函数: sin

反正弦函数: asin

余弦函数: cos

反余弦函数: acos

positive 函数: positive

negative 函数: negative

4.2.3类型转换函数
  • cast 基础类型之间强制转换
hive (default)> select cast(1 as float),cast(3.14 as int);
4.2.4日期函数
  • year、month、day、hour、minute、second 从日期中取出相应的年、月、日、时、分、秒
hive (default)> select year("2022-10-24 12:12:14");
  • datediff 两个日期之间间隔的天数
hive (default)> select datediff("2022-10-24 12:12:14","2021-10-24 ");

注意：时间差是用第一个日期减去第二个日期。

  • date_add、date_sub 日期增加、减少天数
hive (default)> select date_add("2022-10-24",2),date_sub("2022-10-24",2);
4.2.5条件函数
  • if 分支
hive (default)> select if(1>2,"对","错");

思考 select if(1>2,"对",if(1=1,666,777));

  • case 分支
hive (default)> select case when 1>0 then 666 when 1=0 then 777 when 1<0 then 888 end;

注意：结尾有个 end。

案例练习：

nameclasssex
宏宇3班男
林刚3班男
天恩4班男
露莎3班女
世香4班女
李蕊4班女

需求：不同班级男女各多少人

结果如下：

class	male	female
3班		2 	 	 1
4班      1    	 2

创建文件并导入数据：

vi stu_sex.txt
宏宇 3班 男
林刚 3班 男
天恩 4班 男
露莎 3班 女
世香 4班 女
李蕊 4班 女

创建hive表

create table stu_sex(
name string, 
class string, 
sex string) 
row format delimited fields terminated by " ";
load data local inpath 'stu_sex.txt' overwrite into table stu_sex;

查询数据

select
 class,
 sum(case sex when "男" then 1 else 0 end ) as male,
 sum(case sex when "女" then 1 else 0 end ) as female
from
 stu_sex
group by
 class;
4.2.6字符函数
  • lower、upper 转小写、大写
hive (default)>select lower("heLLO"),upper("heLLo");
  • length 字符串长度
hive (default)>select length("hello");
  • concat 字符串拼接
hive (default)>select concat("hello","world");
  • substr 求子字符串

substr(a,b)：从字符串 a 中，第 b 位开始取，取右边所有的字符。

substr(a,b,c)：从字符串 a 中，第 b 为开始取，取 c 个字符。

hive (default)>select substr("hello world",3),substr("hello world",3,5);

除了上面的例子外，还有以下这些函数，不再一一举例：

去掉前后空格: trim

字符 ascii 码函数：ascii

带分隔符字符串连接函数：concat_ws

数组转换成字符串的函数：concat_ws

小数位格式化成字符串函数：format_number

字符串截取函数：substring

字符串查找函数：instr

字符串查找函数：locate

字符串格式化函数：printf

字符串转换成 map 函数：str_to_map

base64 解码函数：unbase64(string str)

左边去空格函数：ltrim

右边去空格函数：rtrim

正则表达式替换函数：regexp_replace

正则表达式解析函数：regexp_extract

URL 解析函数：parse_url

json 解析函数：get_json_object

空格字符串函数：space

重复字符串函数：repeat

分割字符串函数: split

集合查找函数: find_in_set

4.2.7聚合函数

聚合函数主要是统计各种指标，包括：行数 count、求和 sum、最小值 min、最大值 max、平均值 avg。

  • count 返回行数

select count(*) from student;

统计表 student 中有多少行。

  • sum 组内某列求和

select sum(maths) from student;

统计表 student 中数学 maths 的总分。

  • min 组内某列最小值

select min(maths) from student;

统计表 student 中数学 maths 的最低分。

  • max 组内某列最大值

select max(maths) from student;

统计表 student 中数学 maths 的最高分。

  • avg 组内某列平均值

select avg(maths) from student;

统计表 student 中数学 maths 的平均分。

通常聚合函数和 group by 分组配合使用，类似于 MySQL 中的用法。

4.2.8.行列转换
  • 行转列

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

注意: CONCAT_WS must be "string or array

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

案例练习：

nameclasssex
宏宇3班男
天恩4班男
林刚3班男
露莎3班女
世香4班女
李蕊4班女

需求：把相同班级相同性别的同学放到一起

结果如下

3班,男	宏宇|林刚
3班,女     露莎
4班,男     天恩
4班,女     世香|李蕊

4.2.5有创建表stu_sex，这里继续使用

需求分析

拼接班级和性别

宏宇	3班,男
林刚	3班,男
天恩	4班,男
露莎	3班,女
世香	4班,女
李蕊	4班,女

SELECT name ,CONCAT_WS(',',class,sex) i_x
FROM stu_sex;

最终sql

SELECT t1.i_x , CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT name ,CONCAT_WS(',',class,sex) i_x
FROM stu_sex
)t1 
GROUP BY t1.i_X;
  • 列转行

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

案例练习：

namelove
ikun唱，跳，rap，篮球
游鑫csgo,耍朋友,睡觉，写代码
天恩看美女，跳舞，csgo，cos

需求：将love中的数据展开

ikun	唱
ikun	跳
ikun	rap
ikun	篮球
......

数据准备

vi love.txt
ikun 唱,跳,rap,篮球
余俊 钓鱼,看书,学习
宏宇 采耳,麻将,敲代码

创建hive表

create table love_info(
    name string, 
    love string)
row format delimited fields terminated by " ";
load data local inpath "love.txt" into table love_info;

结果查询

select explode(split(love,',')) from love_info;

SELECT name,love_name
FROM love_info 
lateral view
explode(split(love,","))love_info_tmp as love_name ;

第五章 Hive DDL 操作

5.1 数据库操作

进行操作之前，首先要启动 Hadoop 集群和 Hive 客户端。

5.1.1创建数据库
hive (default)> create database  test;
OK
Time taken: 0.02 seconds

也可以在创建时加 if not exists ,表示没有则创建，有则不做任何操作。

hive (default)> create database if not exists test;
OK
Time taken: 0.02 seconds

此时查看 HDFS 上多了一个 /user/hive/warehouse/test.db 目录

5.1.2查看数据库
hive (default)> show databases;
OK
database_name
default
test
Time taken: 0.019 seconds, Fetched: 2 row(s)

可以看到有默认的 default 数据库，还有才创建的 t1 数据库。

查看数据库的详细属性信息：

hive (default)> desc database test;

查看创建数据库的详细语句：

hive (default)> show create database test;
OK
createdb_stmt
CREATE DATABASE `test`
LOCATION
  'hdfs://node1:9000/user/hive/warehouse/test.db'
Time taken: 0.029 seconds, Fetched: 3 row(s)

其中 location 是它默认的物理存储位置

5.1.3删除数据库

删除指定数据库test

hive (default)>drop database test;

或者

hive (default)> drop database if  exists test;
OK
Time taken: 0.142 seconds

默认情况下，Hive 不允许删除包含表的数据库，上面之所以可以删除，是因为还没有建表，如果要删除包含表的数据库，有两种解决办法：

第一种：手动删除数据库下所有表，然后删除数据库。 第二种：使用 cascade 关键字，如下：

hive (default)>drop database if exists test cascade;
5.1.4切换数据库
hive (default)> create database t1;
hive (default)> use t1;

5.2 表的操作

确定hadoop集群和hive已启动

5.2.1创建表

创建表的语法如下：

create table student(id int,name string,crateTime string);

1    CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
2        [(col_name data_type [COMMENT col_comment], ...)]
3        [COMMENT table_comment]
4        [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
5        [CLUSTERED BY (col_name, col_name, ...)
6        [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
7        [ROW FORMAT row_format]
8        [STORED AS file_format]
9        [LOCATION hdfs_path]
10       [Like table_name]

注意：该语法中，“[]”内的内容都是可选的，下面详细解释一下各个部分的含义，可以类比 MySQL 的建表语法。

第 1 行：

CREATE TABLE：建表命令。table_name 指定表的名字。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常。

EXTERNAL：关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）。

第 2 行：

col_name 和 data_type 分别指列的名字和数据类型。

COMMENT：为字段添加描述。

第 3 行：

COMMENT：为表添加描述。

第 4 行：

PARTITIONED BY：设定分区字段。

第 5 行：

CLUSTERED BY：该语句声明的字段中相同的内容会被分配到同一个 reduce 处理，并且为分桶提供依据。

第 6 行：

SORTED BY：保证一个 reduce 任务中的数据有序，但不保证全局有序。

第 7 行：行格式的完整语法如下：

ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]：

DELIMITED 和 SERDE 是二选一的。DELIMITED 设定行的分割方式，可以对 FIELDS/COLLECTION ITEMS/MAP KEYS/LINES 按某字符来进行行的划分。SerDe 设定序列化和反序列化方式。可以自定义 SerDe（序列化与反序列化）或者使用自带的 SerDe。也可以为列指定 SerDe。 如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。

第 8 行：

STORED AS SEQUENCEFILE| TEXTFILE| RCFILE| INPUTFORMAT：设定以何种文件格式进行存储。

SEQUENCEFILE：序列化文件。

TEXTFILE：普通的文本文件格式。

RCFILE：行列存储相结合的文件。

INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname：自定义文件格式。

第 9 行：

LOCATION：指定外部表的数据存储路径或者内部表的数据来源。

第 10 行：

LIKE：允许用户复制现有的表结构，但是不复制数据。

Hive 的建表语法非常复杂，更多细节可到官网查询。

默认创建的表都是内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

建表示例：

  • 创建一张内部表，表名为 student，含有 5 个字段：id 学号为整数，name 名字为字符串，sex 性别为字符串字段之间用,分割。注意：HiveQL 以 ; 作为语句结束标志。
create table if not exists student(
id int, name string,sex string
)
row format delimited fields terminated by ','
stored as textfile
  • 创建外部表：
create external table if not exists student_ext(
id int, name string,sex string
)
row format delimited fields terminated by ','
stored as textfile

创建一张外部表，表名为 student_ext，字段含义与 Student 相同，字段之间用 , 分割，外部表的数据位置为 /user/hive/warehouse/student_ext。

5.2.2查看表
  • 查看当前使用的数据库中有哪些表：
hive (t1)> show tables;
OK
tab_name
student
student_ext
Time taken: 0.026 seconds, Fetched: 2 row(s)
  • 查看某个表的信息
hive (t1)> desc student;
OK
col_name	data_type	comment
id          int                 	                    
name        string              	                    
sex         string              	                    
Time taken: 0.055 seconds, Fetched: 3 row(s)
  • 查看表的扩展信息
hive (t1)> desc extended student;
OK
col_name	data_type	comment
id                  	int                 	                    
name                	string              	                    
sex                 	string              	                    
	 	 
Detailed Table Information	Table(tableName:student, dbName:t1, owner:root, createTime:1667222412, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:id, type:int, comment:null), FieldSchema(name:name, type:string, comment:null), FieldSchema(name:sex, type:string, comment:null)], location:hdfs://node1:9000/user/hive/warehouse/student, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=,, field.delim=,}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{totalSize=0, numRows=0, rawDataSize=0, COLUMN_STATS_ACCURATE={\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"id\":\"true\",\"name\":\"true\",\"sex\":\"true\"}}, numFiles=0, transient_lastDdlTime=1667222412, bucketing_version=2}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE, rewriteEnabled:false, catName:hive, ownerType:USER)	
Time taken: 0.082 seconds, Fetched: 5 row(s)
  • 以良好的格式来查看表的扩展信息：
hive (t1)> desc formatted student;
  • 查看表的详细建表语句：
hive (t1)> show create table student;
OK
createtab_stmt
CREATE TABLE `student`(
  `id` int, 
  `name` string, 
  `sex` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
  'field.delim'=',', 
  'serialization.format'=',') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://node1:9000/user/hive/warehouse/student'
TBLPROPERTIES (
  'bucketing_version'='2', 
  'transient_lastDdlTime'='1667222412')
Time taken: 0.213 seconds, Fetched: 18 row(s)
5.2.3修改表
  • 修改表名
hive (t1)> alter table student rename to bigdata_student;
OK
Time taken: 0.147 seconds
  • 增加一个字段：为表 bigdata_student 增加一个字段 age，数据类型为 int。
hive (t1)> alter table bigdata_student add columns (age int);
OK
  • 修改一个字段：
hive (t1)> alter table bigdata_student change name myname string;
OK
Time taken: 0.126 seconds
5.2.4删除表
hive (t1)> drop table bigdata_student;
5.2.5清空表

图片描述

清空表，并不是删除表，只是表的内容没有了，表本身结构还存在。另外，只能清空内部表，不能清空外部表。

第六章 Hive DML 操作(重点)

6.1 装载数据

首先说明一下，对于内部表，load 操作只是单纯的数据移动操作，将数据文件移动到 Hive 表对应的位置；对于外部表，一般不使用 load 来加载数据，而是在定义时指定数据目录 location，如果定义时没有指定该目录，可以使用如下命令来挂载一个目录：

图片描述

然后再往该目录下上传数据即可。

下面我们来讲一讲 load 语句的语法：

hive> load data [local] inpath 'filepath' [overwrite] into table student [partition (partcol1=val1,…)];

（1）load data:表示加载数据

（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表

（3）inpath:表示加载数据的路径

（4）overwrite:表示覆盖表中已有数据，否则表示追加

（5）into table:表示加载到哪张表

（6）student:表示具体的表

（7）partition:表示上传到指定分区

其中：

  • filepath。

可以是相对路径，例如：project/data1。

或绝对路径，例如：/user/hive/project/data1。

或包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1。

  • LOCAL 关键字。

如果指定了 LOCAL，load 命令会去查找本地文件系统中的 filepath。

如果没有指定 LOCAL 关键字，则根据 INPATH 中的 url 查找文件。

  • OVERWRITE 关键字。

如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

下面举例演示：

  • 加载相对路径数据：

先准备一个数据文件 student.data，内容如下：

[root@node1 ~]# vim student.data
1001,张三,male,18
1002,李四,male,19
1003,王五,female,17
1004,赵六,male,20
1005,田七,mele,18

然后创建一张结构上与数据保持一致的内部表 student，如下：

hive (t1)> create table student(id int,name string,sex string,age int) 
row format delimited fields terminated by ',';

然后在 Hive 中导入该数据到 student 表中：

hive (t1)> load data local inpath 'student.data' into table student;

验证数据是否已经进入到表中？

hive (t1)> select * from student;

image-20221101001204371

  • 加载绝对路径数据：

下面以绝对路径的方式再导入一遍：

hive (t1)> load data local inpath "/root/student.data" into table student;

image-20221101001557373

可以看到数据又被导入了一遍，只不过路径写法不太一样而已。

  • 加载 HDFS 全路径数据：

先把 student.data 文件上传到 hdfs 上的 /hive 目录下:

[root@node1 ~]# hadoop fs -mkdir /hive
[root@node1 ~]# hadoop fs -put student.data /hive

然后执行导入：

hive (t1)> load data inpath "hdfs://node1:9000/hive/student.data" into table student;

hive (t1)> select * from student;

image-20221101002014351

可以看到数据第 3 次被导入了。区别是 hdfs 上的数据没有了，可见是采用移动的方式，这是和本地路径不一样的地方，本地路径时还保留原来的数据。

  • 使用 OVERWRITE 关键字：
 hive (t1)> load data local inpath 'student.data' overwrite into table student;
 hive (t1)> select * student;

image-20221101002545725

可见使用 overwrite 关键字后，数据被覆盖写入了，而之前都是增量导入。

6.2 插入数据 insert

重要说明：Hive 中的 insert 和关系型数据库中的 insert 不太一样。Hive 中的 insert 只支持从别的表查询数据后插入到目标表中，而不支持直接插入数据。

  • 将一个表的数据查询出来插入另一个表中：
create table student_male(id int,name string,sex string) row format delimited fields terminated by ',';

然后把 student 这张表中sex为男 的数据查询出来插入到表 student_male 中。

insert into student_male select id,name,sex from student where sex="男";

可以看到 student_male 表中都是性别为男的数据。

6.3 查询数据并导出

首先要启动 Hadoop 集群和 Hive 客户端。

6.3.1insert导出

1）将查询的结果导出到本地

hive (default)> insert overwrite local directory '/opt/export/student'   select * from student;

查看数据

2）将查询的结果导出到HDFS上(没有local)

hive (default)> insert overwrite directory '/export/student' select * from student;

查看数据

6.3.2Hadoop命令导出到本地
hive (default)> dfs -get /user/hive/warehouse/bigdata04.db/student/stu.data /opt/student1.data;
6.3Hive Shell 命令导出

基本语法：（hive -f/-e 执行语句或者脚本 > file）

[root@node1 ~]#  hive -e 'select * from bigdata03.student;' >/opt/export/student/student03.txt;

6.4 静态分区与动态分区

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

Hive 中支持的分区类型有两种：静态分区（static partition）和动态分区（dynamic partition）。以静态分区的方式导入数据时，其分区标识需要手动指定；而以动态分区的方式导入数据时，其分区标识是基于字段的不同值自动建立分区。

首先要启动 Hadoop 集群和 Hive 客户端。

1. 单分区。

创建静态分区表 t1：

图片描述

准备数据 test.txt：

图片描述

向 t1 表中导入 test.txt 文件中的数据：

图片描述

通过 load 语句向 t1 分区表中以静态分区的方式把 /home/shiyanlou/test.txt 内容插入静态分区 department='maths' 中。

查看分区:

图片描述

可以看到有 1 个分区 maths。

查询所有数据：

图片描述

其中第 3 列显示为所有的分区字段值。

查询某个分区的数据：

图片描述

其中第 3 列显示为分区字段值 'maths'。但是该分区字段 department 并不是表 t1 的字段，而是表 t1 下面的一个子目录而已，如果去访问 HDFS 上的路径的话，看到的应该是这样的一个子目录：/user/hive/warehouse/test.db/t1/department=maths，在该目录下才是开先导入的 test.txt 文件。

再导入一遍数据到 chinese 分区：

图片描述

查看分区：

图片描述

可以看到有 2 个分区 maths 和 chinese。

查看所有内容：

图片描述

可以看到同样的数据存在了两份，这是因为我们把相同的数据导入了一个表中的两个分区中。

2. 多分区。

创建多分区表 t2 并向表中导入 test.txt 文件中的数据，分区逻辑是部门分区 department 下有性别分区 sex，表现在文件结构上是部门分区子目录下的性别分区子目录。

图片描述

把相同的数据插入到不同的静态分区中：

图片描述

查看分区：

图片描述

可以看到有 4 个分区。

接下来查询全表数据:

图片描述

我们发现同样的数据存在了 4 份，放在 4 个不同的分区中。如果去访问 HDFS 上的路径的话，看到的应该是诸如这样的子目录：/user/hive/warehouse/test.db/t1/department=maths/sex=M。

注意：对于多分区表，导入数据时必须指定所有的分区字段标识。

在往 Hive 分区表中插入数据时，如果需要创建的分区很多，比如要以表中某个字段进行分区存储，则需要复制粘贴修改很多 HQL 去执行，如此一来效率就会很低。在关系型数据库中，对分区表 insert 数据的时候，数据库会自动根据分区字段的值，将数据插入到相应的分区中。Hive 也提供了一个动态分区功能，可以基于查询结果中字段的值去推断分区的标识，从而建立分区。

1. 多分区（全部为动态分区）。

因为单分区比较简单，这里我们以多分区为例。

创建一个测试用表 t3 并向该表导入数据 /home/shiyanlou/test1.txt：

图片描述 图片描述

创建目标表 t4，该表具有两个分区字段 age 和 gender。

图片描述

先开启动态分区参数设置，不然会报错：

set hive.exec.dynamic.partition=true;

//开启动态分区功能（默认 true，开启）

图片描述

set hive.exec.dynamic.partition.mode=nonstrict;

//设置为非严格模式（默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区）

图片描述

采用动态分区方式将 t3 中的数据加载到目标表 t4：

图片描述

查看分区：

图片描述

可以看到有 5 个分区。

查看所有数据：

图片描述

如果去访问 HDFS 上的路径的话，看到的应该是诸如这样的子目录：/user/hive/warehouse/ test.db/t2/age=20/gender=M。

6.5 查询数据 select

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

首先要启动 Hadoop 集群和 Hive 客户端。

SELECT [ALL | DISTINCT] select_expr, select_expr, …
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list] ]
[LIMIT number]

先准备一张学生表 student:

hive (test)> create table student(s_id string,s_name string,s_sex string,s_birth string) row format delimited fields terminated by ',';

image-20221103222910739

然后准备好数据：student.csv文件

image-20221103222508409

再把数据导入到表中：

hive (test)> load data local inpath "/root/student03.csv" into table student

image-20221103222703138

查看所有数据：

hive (test)> select * from student;

image-20221103224210181

需求一：求班上总人数（提示 count）

需求二：求班上男女各多少人(提示 group by)

需求三：求班上性别人数大于25的性别，并计算出该性别有多少人（提示having）

需求四：查看班上同学的所有信息并按学号大小降序排序（order by）

需求五：查看班上年龄最小的3个同学的信息（limit）

需求一：求班上总人数（提示 count）

hive (test)> select count(*) from student;

image-20221103230538092

  • group by

需求二：求班上男女各多少人(提示 group by)

hive (test)> select s_sex,count(*)num from student group by s_sex;

image-20221103230508861

需求三：求班上性别人数大于10的性别，并计算出该性别有多少人（提示having）

hive (test)> select s_sex,count(*)num from student group by s_sex having num>10;

image-20221103230613227

使用 group by 按照某个字段分组汇总。

可以看到，按照 sex 分组，得到 sex 和计数这两个字段。男 有 43 人，女 有 7 人。分组条件是计数大于 10，所以只有 男 43 这个答案。

  • order by。

Hive 中的 order by 语句和 sql 语言中的定义是一样的，会对查询结果集执行一个全局排序，就是说所有的数据都通过一个 reducer 进行处理，对于大数据集，这个过程效率很低。

需求四：查看班上同学的所有信息并按学号大小降序排序（order by）

image-20221103233135008

hive (test)> select * from student order by s_id desc ;

需求五：查看班上年龄最小的3个同学的信息（limit）

hive (test)> select * from student order by s_birth desc limit 3;

image-20221104000251924

  • sort by。

sort by 只保证每个 reducer 的输出有序，不保证全局有序。

先设置 2 个 reducer：

hive (test)> set mapreduce.job.reduces=2;

然后排序：

hive (test)> select s_name,2022-year(s_birth) as age from student sort by age;

image-20221103234043154

这里需要把 reducer 的数量设置为大于 1（默认为 1），才能体现局部排序。如果等于 1 则和 order by 效果一样。

根据查询结果，我们可以推断前面 由一个 reducer 处理，后面 由另一个 reducer 处理。

  • distribute by。

distribute by 类似 MapReduce 中 partition（自定义分区，注意不是 hive 的分区表），用于控制 mapper 中的输出如何划分到 reducer，也可以理解为控制表中的哪些行被同一个 reducer 处理。使用 distribute by 可以保证在某字段上拥有相同值的行被划分到一个 reducer 中，具体规则是用分区字段的字段值的 hash 码对 reducer 的个数取模，余数相同的分到一个区。通常会配合着 sort by 一起使用，这时 distribute by+sort by 等价于 cluster by，即分区排序。

先设置 2 个 reducer：

hive (test)> set mapreduce.job.reduces=2;

然后排序：

hive (test)> select * from student distribute by s_sex sort by s_birth;

image-20221103235550841

可以看到查询结果按照 sex 分成 2 个区，前 面男生一个分区，后面女生一个区，然后每个区内数据按照 age 排序。

  • cluster by。

cluster by 除了具有 distribute by 的功能外，还会在分区内对该字段进行排序。因此，常常认为 cluster by=distribute by+sort by，即分区排序。

6.6 join

Hive 支持等值连接（equality joins）、外连接（outer joins）和左右连接（left/right joins）。Hive 不支持非等值连接，因为非等值连接非常难转化到 MapReduce 任务。Hive 支持多于 2 个表的连接。

这里准备 4 张表，分别是学生表 student(s_id 学号,s_name 姓名,s_sex 性别,s_birth 年龄)，课程表 course(c_id 课程编号,c_name 课程名称,t_id教师编号)和成绩表score(s_id 学号,s_score 成绩,c_id 课程号)，教师表（t_id 教师编号，t_name 教师名字）再导入相关数据：

create table student(s_id string,s_name string,s_sex string,s_birth string) row format delimited fields terminated by ',';

create table course(c_id string,c_name string,t_id string) row format delimited fields terminated by ',';

create table teacher(t_id string,t_name string) row format delimited fields terminated by ',';

create table score(s_id string,s_score int,c_id string) row format delimited fields terminated by ',';

准备相关数据：

student03.csv

image-20221107201302116

course03.csv

image-20221108231457120

teacher03.csv

image-20221108231519163

score03.csv

image-20221108231615873

然后导入数据：

load data local inpath "/root/student03.csv" overwrite into table student;

load data local inpath "/root/course03.csv" overwrite into table course;

load data local inpath "/root/teacher03.csv" overwrite into table teacher;

load data local inpath "/root/score03.csv" overwrite into table score;

1. 支持 join。

查宏宇的所有成绩

select * from student  JOIN score on student.s_id=score.s_id and student.s_name="罗天恩";

select student.s_name,score.s_score from student join score on student.s_id=score.s_id and student.s_name="林宏宇";

image-20221111140611854

可见 宏宇 这个同学参与了 3门课程， 这3门课的考试都是87。

2. 可以 join 多于 2 个表。

查询 宏宇 的 数据库 成绩：

select * from student 
join score on student.s_id=score.s_id 
join course on score.c_id=course.c_id 
and student.s_name="林宏宇" and course.c_name="数据库";

select student.s_name,course.c_name,score.s_score from student join score on student.s_id=score.s_id join course on score.c_id=course.c_id and student.s_name="林宏宇" and course.c_name="数据库";

image-20221111141621300

结果更为直观了。

挑战练习：查看自己的数据库成绩和授课老师。

select * from student 
join score on student.s_id=score.s_id 
join course on score.c_id=course.c_id
join teacher on course.t_id=teacher.t_id
and student.s_name="罗天恩" and course.c_name="数据库";

3. 支持子查询。

查询 宏宇 的所有已经考了的成绩：

select * from score where s_id in (select s_id from student where s_name="罗天恩");

image-20221111142118155

4. 支持左（left outer join）、右（right outer join）、外连接（full outer join）。

查询所有学生的成绩（暂时没有成绩的学生用 null 来表示其成绩）：

因为咱们班都有成绩 插入一条数据模拟没有成绩的同学

insert into student values(1001,"李明响","男",1999-09-09);

select * from student  left join score on student.s_id=score.s_id;

这里用到了左连接 left outer join，左边的表 student 的数据全部保留，右边的表 score 没有连接的全部都以 NULL 来表示。

image-20221116090649129

5. 支持 cross join。

cross join：返回两个表的笛卡尔积结果（数目为左表乘右表），不需要指定关联键。

6. 支持 left semi join。

left semi join：并不拼接两张表，两个表对 on 的条件字段做交集，返回前面表的记录，相较于其它的方法，这样子 hive 处理速度比较快。

比如如下子查询：

SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B);

select student.s_id,s_name from student where s_id in (select s_id from score );

可以改成使用 left outer join 来完成：

SELECT a.key, a.value FROM a LEFT OUTER JOIN b ON (a.key = b.key) WHERE b.key is not NULL;

SELECT student.s_id,s_name FROM student LEFT OUTER JOIN score ON student.s_id = score.s_id where score.s_id is not NULL;

还可以改成更加高效的 semi 写法(某些情况有用)：

SELECT a.key, a.val FROM a LEFT SEMI JOIN b ON (a.key = b.key);
select * from student LEFT SEMI JOIN score on student.s_Id=score.s_id;

left join 和 left semi join区别

  • LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方都不行。
  • 因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高,。
  • left semi join 是只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表。因为右表只有 join key 参与关联计算了，而left join on 默认是整个关系模型都参与计算了

6.7 WordCount 案例

1. 准备文本内容。

新建一个 /home/shiyanlou/wc.txt 文本，内容如下：

图片描述

2. 新建 Hive 表。

图片描述

没有指定列的分隔符，因为就一列，行数据整体是二维结构中的单元格内容。

3. 加载数据到 Hive 表中。

图片描述

4. 分割文本。

图片描述

这里使用了 Hive 的内置函数 split 来切割每一行的内容，使用正则表达式 \s 空白符（包括空格、tab 缩进、换行等所有的空白）来分割，多一个 \ 是用来转义的。可以看出分割后呈现为字符串数组的形式。

5. 行转列。

上面一步切割完成后单词仍然在一行里面，无法统计，需要行转列。

图片描述

使用 Hive 的内置函数 explode 可以做到行转列，现在所有的单词都是单独占据一行。

6. 统计计数。

图片描述

至此，Hive 已实现 WordCount 计数功能。

第七章 Hive 复杂数据类型

7.1 Array

首先要启动 Hadoop 集群和 Hive 客户端。

图片描述

图片描述

现有数据 a.txt 如下：

图片描述

注意：分隔符为 \t，建议自己输入，直接复制上段代码到文件中，可能会使 \t 变成空格。

其中：

第 1 个字段是人员编号，类型为整数，如 1。

第 2 个字段是人员姓名，类型为字符串，如 zs。

第 3 个字段是人员工作地，类型为 Array，含有多个地址，地址间用逗号分隔，如 guangzhou,xianggang,shenzhen。

现建表如下，其中 a 为表名，id 为人员编号，采用 int 类型；name 为人员姓名，采用 string 类型；address 为人员地址，采用 Array 类型。字段之间用 \t 分割，集合项之间用 , 分割，如 Array 中的各项。

图片描述

导入数据：

图片描述

1. 访问每个人的所有地址：

图片描述

2. 访问每个人的第一个地址：

对于 Array 的使用，主要通过字段的下标来访问，下标是从 0 开始的整数：

图片描述

3. 访问每个人的第二个地址：

图片描述

没有第二个地址的用 NULL 来替代。

4. 访问 'zs' 这个人的第三个地址：

图片描述

7.2 Map

首先要启动 Hadoop 集群和 Hive 客户端。

图片描述

图片描述

现有数据 a.txt 如下：

图片描述

注意：分隔符为 \t，建议自己输入，直接复制上段代码到文件中，可能会使 \t 变成空格。

其中：

第 1 个字段是人员编号，类型为整数，如 1。

第 2 个字段是人员姓名，类型为字符串，如 zs。

第 3 个字段是各科成绩，类型为 Map，key 为字符串类型，表示科目，value 为整数类型，表示成绩，key 与 value 之间用冒号分割，k-v 对之间用逗号分隔，如 chinese:90,maths:80,english:100。

现建表如下，其中 a 为表名，id 为人员编号，采用 int 类型；name 为人员姓名，采用 string 类型；score 为各科成绩，采用 Map 类型。字段之间用 \t 分割,Map 中的各项用逗号分隔，Map 中 key 与 value 之间用 : 分割。

图片描述

导入数据：

图片描述

1. 访问每个人的所有成绩：

图片描述

2. 访问每个人的语文成绩：

图片描述

可见，对于 Map 而言，主要通过 key 来访问对应的 value 值。没有的 key，对应的值为 NULL。

3. 访问每个人的数学成绩：

图片描述

4. 访问 'zs' 这个人的英语成绩：

图片描述

7.3 Struct

首先要启动 Hadoop 集群和 Hive 客户端。

图片描述

图片描述

现有数据 a.txt 如下：

图片描述

注意：分隔符为 \t，建议自己输入，直接复制上段代码到文件中，可能会使 \t 变成空格。

其中：

第 1 个字段是人员编号，类型为整数，如 1。

第 2 个字段是人员姓名，类型为字符串，如 zs。

第 3 个字段是人员信息，类型为 Struct，含有 4 个属性，分别是年龄，类型为整数，性别，类型为字符串，身高，类型为浮点小数，体重，类型为整数，如 30,male,1.75,120。

现建表如下，其中 a 为表名，id 为人员编号，采用 int 类型；name 为人员姓名，采用 string 类型；info 为人员信息，采用 Struct 类型。字段之间用 \t 分割,Struct 中的各项用逗号分隔。

图片描述

导入数据：

图片描述

1. 访问每个人的所有信息：

图片描述

2. 访问每个人的年龄信息：

图片描述

可见，对于 Struct 而言，主要通过 . 来访问对应的属性值。没有的属性，对应的值为 NULL。

3. 访问每个人的性别信息：

图片描述

4. 访问 'zs' 这个人的身高信息：

图片描述

综合练习

1）假设某表有如下一行，我们用JSON格式来表示其数据结构。在Hive下访问的格式为

{
    "name": "天恩",
    "friends": ["天权" , "游鑫"] ,       //列表Array, 
    "score": {                      //键值Map,
        "数据库": 87 ,
        "计算机网络": 87,
        "面向对象":87
    },
    "address": {                      //结构Struct,
        "street": "红星街道" ,
        "city": "泸州" 
    }
}

2）创建测试文件

天恩,天权_游鑫,数据库:87_计算机网络:87_面向对象:87,红星街道_泸州
李明响,刘智豪_杨建清,数据库:0_计算机网络:0_面向对象:0,成华大道_成都

3）Hive上创建测试表test

create table test(
name string,
friends array<string>,
score map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':';

字段解释：

row format delimited fields terminated by ',' -- 列分隔符

collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)

map keys terminated by ':' -- MAP中的key与value的分隔符

4）导入数据

load data local inpath '/root/test.txt' into table test;

5)测试

查询天恩所以在的城市和数据库成绩

第八章 Hive 自定义函数

  • Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。

  • 当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

  • 根据用户自定义函数类别分为以下三种：

（1）UDF（User-Defined-Function）

​ 一进一出

（2）UDAF（User-Defined Aggregation Function）

​ 聚集函数，多进一出

​ 类似于：count/max/min

（3）UDTF（User-Defined Table-Generating Functions）

​ 一进多出

​ 如lateral view explode()

（4）官方文档地址

https://cwiki.apache.org/confluence/display/Hive/HivePlugins

8.1 UDF

UDF(User-Defined Functions，用户定义函数)支持一个输入产生一个输出，需要继承 org.apache.hadoop.hive.ql.exec.UDF 类，重写类中的 evaluate() 方法。这里我们创建一个 ToLower 类，实现把大写字母变成小写字母的功能。

首先打开环境，打开 idea，然后创建一个 maven 项目，然后导入以下依赖包：

image-20221128231256892

image-20221128231828940

导入依赖

<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>3.1.2</version>
</dependency>

image-20221128232517958

然后创建一个类，代码如下所示：

import org.apache.hadoop.hive.ql.exec.UDF;

/**
 * 继承UDF，重写evaluate方法
 */
public class Myudf extends UDF {
    /**
     * 变成小写字符串
     */
    public String evaluate(String s) {
        return "lzh:"+s;
    }
}

image-20221128233238832

将代码打成 jar 包 MyUDF.jar，放到目录 /home/shiyanlou/ 下。

image-20221128233427893

image-20221128233607910

image-20221128233717839

image-20221129000236030

image-20221128233954642

然后启动 Hadoop 和 Hive，使用 add 命令把 tolower.jar 注册到 Hive 中，使用 list jar 命令可以查看所有的已经注册的 jar 文件：

hive> list jar;
hive> add JAR /home/shiyanlou/tolower.jar;
Added [/home/shiyanlou/tolower.jar] to class path
Added resources:[/home/shiyanlou/tolower.jar]
hive> list jar;
/home/shiyanlou/MyUDF.jar

创建临时函数与 MyUDF 类进行一一映射，便于使用时直接调用：

hive> create temporary function myudf as 'MyUDF';

下面就可以直接使用了：

hive>select myudf("hello");
lzh:hello

注销自定义函数：

hive>drop temporary function tolower;

第九章 Hive 调优

9.1 Fetch 抓取

我们都知道 Hive 底层跑的是 MapReduce，其实有些语句并没有执行 MapReduce，大家可能之前没有注意，今天开始我们来讲一讲这一块。

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

我们先来做点准备工作：

1、首先启动 Hadoop 和 Hive（略），然后在 Hive 中创建数据库和表：

hive>create database t1;
hive>use t1;
hive>create table a1(id int,name string,age int) row format delimited fields terminated by ',';

图片描述

2、然后准备数据文件 a.txt：

1,zs,19
2,ls,20
3,ww,21
4,zl,22
5,sq,23

图片描述

3、再把数据文件导入到表中：

hive>load data local inpath '/home/shiyanlou/a.txt' into table a1;

图片描述

从上面截图我们发现语句 select * from a1; 没有跑 MapReduce。

其实在 /opt/hive-2.3.4/conf/hive-default.xml.template 配置文件中有个参数 hive.fetch.task.conversion 默认是 more，表示在全局查找、字段查找、limit 查找时都不跑 MapReduce。

图片描述

图片描述

我们来看看另外两种情况：

hive>select id from a1;
1
2
3
4
5
hive>select id from a1 limit 3;
1
2
3

图片描述

可以看到同样没有跑 MapReduce。

如果我们把这个参数的值改成 none 的话，那么所有的 Hive 语句都会跑 MapReduce，大家可以试一试，这里不再演示。

hive>set hive.fetch.task.conversion=none;
hive>select * from a1;
hive>select id from a1;
hive>select id from a1 limit 3;

9.2 本地模式

Hive 在集群上查询时，默认是在集群上 N 台机器上运行，需要多个机器进行协调运行，这个方式很好地解决了大数据量的查询问题。但是当 Hive 查询处理的数据量比较小时，其实没有必要启动分布式模式去执行，因为以分布式方式执行就涉及到跨网络传输、多节点协调等问题。这个时候可以只使用本地模式来执行 MapReduce，只在一台机器上执行，速度会很快。

**注意：**因为实验楼默认环境是 1 个节点的伪分布式模式，所以本节内容无法进行对比操作，好在作者在自己的虚拟机上虚拟了 3 个节点来演示这节内容。

启动本地模式涉及到三个参数：

1、hive.exec.mode.local.auto。

默认值为 false，表示没有启动本地模式。

2、hive.exec.mode.local.auto.input.files.max。

默认值为 4，表示不启用本地模式的 task 最大个数。

3、hive.exec.mode.local.auto.inputbytes.max。

默认值为 128M，表示不启用本地模式的最大输入文件大小。

那么如果要启动本地模式，需要满足以下的条件：

设置 hive.exec.mode.local.auto 参数值为 true，同时保证 map 任务数不超过 hive.exec.mode.local.auto.input.files.max 的个数并且 map 输入文件大小不超过 hive.exec.mode.local.auto.inputbytes.max 所指定的大小时，才能启动本地模式。

开启本地模式设置：

//开启本地模式
set hive.exec.mode.local.auto=true;

// 设置本地模式的最大输入数据量，当输入数据量小于这个值时采用本地模式的方式，默认为 134217728，即 128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;

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

对比案例：

（1）开启本地模式，并执行查询语句。

hive>set hive.exec.mode.local.auto=true;
hive>select * from emp cluster by deptno;
Time taken: 1.328 seconds, Fetched: 14 row(s)

（2）关闭本地模式，并执行查询语句。

hive>set hive.exec.mode.local.auto=false;
hive>select * from emp cluster by deptno;
Time taken: 20.09 seconds, Fetched: 14 row(s)；

可以看到时间上的巨大差异，大家可以在本地虚拟机里面试一试。

9.3 压缩存储

创建表时，尽量使用 orc、parquet 这些列式存储格式，因为列式存储的表，每一列的数据在物理上是存储在一起的，Hive 查询时会只遍历需要列数据，大大减少处理的数据量。Hive 最终是转为 MapReduce 程序来执行的，而 MapReduce 的性能瓶颈在于网络 IO 和 磁盘 IO，要解决性能瓶颈，最主要的是减少数据量，对数据进行压缩是个好的方式。压缩虽然是减少了数据量，但是压缩过程要消耗 CPU，好在 Hadoop 中，往往性能瓶颈不在于 CPU，CPU 压力并不大，所以压缩充分利用了比较空闲的 CPU。

常用压缩方法对比：

图片描述

压缩性能的比较：

压缩算法原始文件大小压缩文件大小压缩速度解压速度
gzip8.3GB1.8GB17.5MB/s58MB/s
bzip28.3GB1.1GB2.4MB/s9.5MB/s
LZO8.3GB2.9GB49.3MB/s74.6MB/s

各个压缩方式所对应的类：

图片描述

那么如何设置压缩呢？有 2 种方式，一种是 Hive 脚本中设置，另一种是配置文件中设置。

第一种：

set hive.exec.compress.intermediate=true --启用中间数据压缩
set hive.exec.compress.output=true; -- 启用最终数据输出压缩
set mapreduce.output.fileoutputformat.compress=true; --启用reduce输出压缩
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec --设置reduce输出压缩格式
set mapreduce.map.output.compress=true; --启用map输入压缩
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec；-- 设置map输出压缩格式

第二种：

通过在 hive-site.xml 文件中设置启用中间数据压缩，配置文件如下：

<!-- 启用中间数据压缩 -->
<property>
        <name>hive.exec.compress.intermediate</name>
        <value>true</value>
</property>
<property>
        <name>hive.exec.compress.output</name>
        <value>true</value>
</property>
<!-- map输出压缩 -->
<property>
        <name>mapreduce.map.output.compress</name>
        <value>true</value>
</property>
<property>
        <name>mapreduce.map.output.compress.codec</name>
        <value>org.apache.hadoop.io.compress.SnappyCodec</value>
</property>
<!-- reduce输出压缩 -->
<property>
        <name>mapreduce.output.fileoutputformat.compress</name>
        <value>true</value>
</property>
<property>
        <name>mapreduce.output.fileoutputformat.compress.codec</name>
        <value>org.apache.hadoop.io.compress.SnappyCodec</value>
</property>

具体效果大家可以在跑 Hive 时，观察相关日志信息即可。

9.4 表的优化

将 key 相对分散，并且数据量小的表放在 join 的左边，这样可以有效减少内存溢出错误发生的几率。

实际测试发现：新版的 Hive 已经对小表 join 大表和大表 join 小表进行了优化。小表放在左边和右边已经没有明显区别。

案例实操：

  • 需求：测试大表 join 小表和小表 join 大表的效率。
  • 建大表、小表和 join 后表的语句。
hive>create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
hive>create table smalltable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
hive>create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
  • 分别向大表和小表中导入数据。
hive>load data local inpath '/home/shiyanlou/bigtable' into table bigtable;
hive>load data local inpath '/home/shiyanlou/smalltable' into table smalltable;
  • 关闭 map join 功能（默认是打开的）
hive>set hive.auto.convert.join = false;
  • 执行小表 join 大表语句。
hive>insert overwrite table jointable select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from smalltable s left join bigtable b on b.id = s.id;
Time taken: 35.921 seconds
  • 执行大表 join 小表语句。
hive>insert overwrite table jointable select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from bigtable b left join smalltable s on s.id = b.id;
Time taken: 34.196 seconds；

9.5 数据倾斜

  • 什么是数据倾斜？

由于数据分布不均匀，导致数据大量的集中到一点，造成数据热点。

  • Hadoop 框架的特性：
    • 不怕数据大，怕数据倾斜。
    • Jobs 数比较多的作业运行效率相对比较低，如子查询比较多。
    • sum,count,max,min 等聚集函数，通常不会有数据倾斜问题。
  • 主要表现：

任务进度长时间维持在 99% 或者 100% 的附近，查看任务监控页面，发现只有少量 reduce 子任务未完成，因为其处理的数据量和其它的 reduce 差异过大。 单一 reduce 处理的记录数和平均记录数相差太大，通常达到好几倍之多，最长时间远大于平均时长。

  • 容易产生数据倾斜的情况：

    • group by 时维度过小，某值的数量过多，导致某个 reduce 非常耗时。
    • count(distinct)，在数据量大的情况下，容易数据倾斜，因为 count(distinct) 是按 group by 字段分组，按 distinct 字段排序。
    • 小表关联超大表 join，分布不均导致某一个或某几个 reduce 上的数据远远高于平均值。

  • 产生数据倾斜的原因：

    • key 分布不均匀。
    • 业务数据本身的特性。
    • 建表考虑不周全。
    • 某些 HQL 语句本身就存在数据倾斜。

  • 业务场景：

    • 空值产生的数据倾斜。

      • 场景说明：

        在日志中，常会有信息丢失的问题，比如日志中的 user_id，如果取其中的 user_id 和用户表中的 user_id 相关联，就会碰到数据倾斜的问题，因为这些空值都是由一个 reduce 来处理，非常慢。

      • 解决方案：

        解决方案 1：user_id 为空的不参与关联。

        select * from log a join user b on a.user_id is not null and a.user_id = b.user_id
union all
select * from log c where c.user_id is null;

        解决方案 2：赋予空值新的 key 值。

        select * from log a left outer join user b on
case when a.user_id is null then concat('hive',rand()) else a.user_id end = b.user_id;

      • 总结:

        方法 2 比方法 1 效率更好，不但 IO 少了，而且作业数也少了，方案 1 中，log 表读了两次，jobs 肯定是 2，而方案 2 是 1。这个优化适合无效 id（比如-99，’’，null）产生的数据倾斜，把空值的 key 变成一个字符串加上一个随机数，就能把造成数据倾斜的数据分到不同的 reduce 上解决数据倾斜的问题。

        改变之处：使本身为 null 的所有记录不会拥挤在同一个 reduceTask 了，会由于有替代的随机字符串值，而分散到了多个 reduceTask 中了，由于 null 值关联不上，处理后并不影响最终结果。

    • 不同数据类型关联产生数据倾斜。

      • 场景说明：

        用户表中 user_id 字段为 int，log 表中 user_id 为既有 string 也有 int 的类型，当按照两个表的 user_id 进行 join 操作的时候，默认的 hash 操作会按照 int 类型的 id 进行分配，这样就会导致所有的 string 类型的 id 就被分到同一个 reducer 当中。

      • 解决方案：

        把数字类型 id 转换成 string 类型的 id

        select * from user a left outer join log b on b.user_id = cast(a.user_id as string);

    • 大小表关联查询产生数据倾斜。

    注意：使用 map join 解决小表关联大表造成的数据倾斜问题。这个方法使用的频率很高。

    map join 概念：将其中做连接的小表（全量数据）分发到所有 MapTask 端进行 Join，从 而避免了 reduceTask，前提要求是内存足以装下该全量数据。

    以大表 a 和小表 b 为例，所有的 maptask 节点都装载小表 b 的所有数据，然后大表 a 的 一个数据块数据比如说是 a1 去跟 b 全量数据做链接，就省去了 reduce 做汇总的过程。 所以相对来说，在内存允许的条件下使用 map join 比直接使用 MapReduce 效率还高些， 当然这只限于做 join 查询的时候。

    在 hive 中，直接提供了能够在 HQL 语句指定该次查询使用 map join，map join 的用法是在查询/子查询的 SELECT 关键字后面添加/_+ MAPJOIN(tablelist) _/提示优化器转化为 map join。其中 tablelist 可以是一个 表，或以逗号连接的表的列表。tablelist 中的表将会读入内存，通常应该是将小表写在 这里。

    map join 具体用法：

    select /* +mapjoin(a) */ a.id aid, name, age from a join b on a.id = b.id;
select /* +mapjoin(movies) */ a.title, b.rating from movies a join ratings b on a.movieid = b.movieid;

    在 hive0.11 版本以后会自动开启 map join 优化，由两个参数控制：

    set hive.auto.convert.join=true; //设置 map join 优化自动开启
set hive.mapjoin.smalltable.filesize=25000000 //设置小表不超过多大时开启 map join 优化

    如果是大大表关联呢？那就大事化小，小事化了。把大表切分成小表，然后分别 map join。

Hive

评论