[大数据]学数据仓库，看这篇文章就够了

一、数据仓库基础

1、概念

数据库已经在信息技术领域有了广泛的应用，社会生活的各个领域几乎都有各种各样的数据库保存着各种数据。数据仓库作为数据库的一个分支，其概念的提出相对于数据库从时间上就晚得多。

2、特点

• 效率足够高

  数据仓库的分析数据一般按照时间周期分为日、周、月、季、年等类型数据。以日为周期的数据要求的效率最高，要求24小时内用户能看到数据分析结果。

• 数据质量

  数据仓库所提供的各种数据，要求是准确的数据，但由于数据仓库工作流程通常分为多个步骤，包括数据清洗、装载、查询、展现等，如果数据源有脏数据或者代码不严谨，可能导致数据失真，用户看到错误的信息就可能导致错误的决策，从而造成损失。

• 扩展性

  扩展性主要体现在数据建模的合理性上，数据仓库中扩展出一些中间层，使海量数据流有足够的缓冲，不至于因为数据量增大很多就运行不起来。

3、数据模型

数据仓库系统是一个信息提供平台，主要以星状模型和雪花模型进行数据组织，并为用户提供各种手段从数据中获取信息。

星状模型是搭建数据仓库的基本数据模型。在星状模型基础上发展起来的一种新型模型称为雪花模型。雪花模型应用在一些更复杂的场景中。

星状模型是一种由一个中心点向外辐射的模型。例如，对于商品推荐系统，面向的主题就是商品信息。与商品信息相关联的首先是用户信息，用户会购买商品，商品会形成订单，所以与订单信息是相关的。商品进行物流，所以肯定也会有物流信息，另外也会有促销信息。商品是由厂家生产的，所以肯定有厂家信息。这样就以商品信息为核心建立起来一个星状模型，这是一个面向商品信息主题的模型。

雪花模型是基于星状模型发展起来的。在商品信息这个主题的基础上进行扩展，例如以用户信息为核心，用户信息又是一个主题，与用户信息相关的会有用户的家庭信息、用户的地址信息、用户的教育背景信息，另外还有用户的银行信息等。当然，还会有其他信息与用户相关。这是以用户信息为主题来看的，也可以以其他主题来看，比如厂家信息，与厂家信息相关的信息很明显就有厂家的地址信息、员工信息等。所以这个模型就会越扩展越大，从而形成雪花模型。

星状模型和雪花模型是大型数据仓库中最基本的两个模型，用户可以根据自己的实际情况选择一种合适的模型来搭建数据仓库。

以商品推荐系统为例建立星状模型

雪花模型

二、Hive数据仓库

1、概念

Hive是用来开发SQL类型脚本，用于开发MapReduce操作的平台。Hive最初由Facebook开源，用于解决海量结构化日志的数据统计分析。

Hive是建立在Hadoop集群的HDFS上的数据仓库基础框架，其本质是将类SQL语句转换为MapReduce任务运行。可以通过类SQL语句快速实现简单的MapReduce统计计算，十分适合数据仓库的统计分析。

所有Hive处理的数据都存储在HDFS中，Hive在加载数据过程中不会对数据进行任何修改，只是将数据移动或复制到HDFS中Hive设定的目录下。所以Hive不支持对数据的改写和添加，所有数据都是在加载时确定的。

Hive定义了类SQL查询语言，称为HQL（Hive Query Language，Hive查询语言）。Hive是SQL语句解析引擎，Hive将用户的SQL语句通过解释器等工具转换为MapReduce任务，并提交到Hadoop集群上，Hadoop监控任务执行过程，然后返回任务执行结果给用户。Hive不是为联机事务处理（OLTP）而设计的，Hive并不提供实时的查询和基于行级的数据更新操作。

2、优点

• 简单、容易上手，简化了MapReduce应用程序的编写。
• 操作接口采用HQL语法，提供快速开发的能力。
• Hive常用于数据分析，对实时性要求不高。
• Hive常用于处理大数据，对于处理小数据没有优势。
• 用户可以根据需求来实现用户自定义函数。

3、缺点

• Hive的HQL语句表达能力有限

  • 无法表达迭代算法。
  • 不擅长数据挖掘，Hive常用于数据分析。

• Hive的效率比较低

  • Hive自动生成MapReduce任务，不够智能化。
  • Hive调优比较困难。

4、Hive体系结构及执行流程

Hive是为了简化用户编写MapReduce应用程序而生成的一种框架。

4.1、Hive体系结构

在Hive体系结构中，主要包括Hive用户接口、元数据存储（MetaStore）、驱动器（Driver）等。在Hive体系结构中，底层是操作系统，一般会使用Linux操作系统，如CentOS、Ubuntu。

4.2、Hadoop集群

在操作系统之上，就是Hadoop集群。在Hadoop集群中，有名称节点，用来管理整个集群的工作，也有若干个数据节点，用来存储数据。

在Hadoop集群中还有一个JobTracker，负责整个任务的调度。在Hive中执行一条HQL语句，这条语句实际上会被解析成MapReduce任务，并提交到集群上，得到的最终结果会被反馈给客户端，这个工作就是由JobTracker完成的。有了Hadoop集群之后，就可以在其上构建Hive数据仓库。

4.3、驱动器

由于在Hive中需要操作Hadoop集群，所以在Hive体系结构中，会有Hive驱动器。Hive驱动器包括解析器、编译器、优化器和执行器，负责HQL语句的执行过程。Hive执行的HQL语句首先提交给驱动器，然后调用编译器解释驱动，最终解释成MapReduce任务执行，最后将结果返回给客户端。

• 解析器（HQL Parser）：将HQL语句转换成抽象语法树（AST）；对AST进行语法分析，比如表是否存在、字段是否存在、HQL语义是否正确。

• 编译器（Compiler）：将AST编译生成逻辑执行计划。

• 优化器（Query Optimizer）：对逻辑执行计划进行优化。

• 执行器（Execution）：把逻辑执行计划转换成可以运行的MapReduce任务。

4.4、用户接口

用户接口主要有三个：CLI（Command Line Interface）、Thrift Server和HWI（Hive Web Interface），其中最常用的是CLI。

Client是Hive的客户端，Hive启动后，用户连接至HiveServer。客户端可以直接在命令行模式下进行操作。通过命令行，用户可以创建表、执行查询等操作。

Hive提供了Thrift Server，用来提供访问服务。通过这个服务器，可以使用不同的程序语言，如Java、Python，它们都可以连接到Thrift Server上，通过解析器、编译器、优化器和执行器，执行Hadoop集群的操作。

Hive提供了更直观的Web操作控制台，可以执行查询语句和其他命令，这样就可以不用登录到集群中的某台计算机上使用CLI来进行查询工作。

4.5、元数据存储

Hive中的表对应于Hadoop集群中的目录，所以表的存储位置等信息会存储在元数据中。这种信息与表中数据没有任何关系，它反应的是表本身的信息，这种信息称为元数据。Hive中的元数据包括表名、表所属的数据仓库、表的所有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等。

由于Hive的元数据需要不断地更新、修改，而HDFS中的文件是读多改少的，显然不能将Hive的元数据存储在HDFS中。所以Hive将元数据存储在数据库中，这个数据库又称为MetaStore，也可以是MySQL或Oracle或者是Derby数据库。Hive默认采用Derby数据库

4.6、Hive表与元数据的映射关系

在Hive数据仓库中有两个表：一个student表和一个score表，student表对应于HDFS中的/hive/student目录，score表对应于HDFS中的/hive/score目录，student表有sid、sname、dname字段，score表有cname、sid、score字段。元数据被默认保存在Derby数据库中，有一个表用于保存表的信息，如表ID、表的名称、存储位置。student表ID为1，名称为student，存储位置为/hive/student；score表ID为2，名称为score，存储位置为/hive/score，这样保存了表的元数据。同样，把列的信息保存在Derby列信息表中，sid为1，对应表的ID也为1。

4.7、Hive执行流程

Hive通过给用户提供的一系列交互接口，接收用户的指令HQL，然后使用自己的驱动器，结合元数据存储，将这些指令翻译成MapReduce任务，提交到Hadoop集群中执行，最后将执行的结果返回并输出到用户接口。Hive执行流程的大致步骤如下。

• 用户提交查询等任务给驱动器。

• 编译器获得该用户的任务计划Plan。

• 编译器根据用户任务计划去MetaStore中获取需要的Hive元数据信息。

• 编译器得到元数据信息，对任务计划进行编译。先将HQL语句转换为抽象语法树，然后将抽象语法树转换成查询块，将查询块转化为逻辑查询计划，重写逻辑查询计划，将逻辑计划转化为物理计划（MapReduce），最后选择最佳的策略。

• 将最终的计划提交给驱动器。

• 驱动器将计划转交给执行器去执行，获取元数据信息，并提交给JobTracker或者SourceManager执行该任务，任务会直接读取HDFS中的文件进行相应的操作。

• 取得并返回执行结果。

5、Hive数据仓库和数据库比较

Hive数据仓库与传统意义上的数据库是有区别的。一般来说，基于传统方式，可以用Oracle数据库或MySQL数据库来搭建数据仓库，数据仓库中的数据保存在Oracle或MySQL数据库中。

Hive数据仓库和它们是不一样的，Hive数据仓库建立在Hadoop集群的HDFS之上。也就是说，Hive数据仓库中的数据是保存在HDFS上的。Hive数据仓库可以通过ETL的形式来抽取、转换和加载数据。Hive提供了类似SQL的查询语句HQL，可以用select * from表名;来查询到Hive数据仓库中的数据，这与关系型数据库的操作是一样的。

其实从结构上来看，Hive数据仓库和数据库除了拥有类似的查询语言，再无类似之处。下面将从多个方面来阐述Hive数据仓库和数据库的差异。

• 查询语言

  针对Hive特性设计了类SQL的查询语言HQL，因此，熟悉数据库SQL的开发者可以很方便地使用Hive进行开发统计分析。

• 数据存储系统

  Hive数据仓库使用Hadoop集群的HDFS来存储数据，而数据库则将数据保存在本地文件系统中。

• 数据更新

  数据库中的数据通常是需要经常进行修改更新的，因此可以使用INSERT添加数据、使用UPDATE修改更新数据。由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的，因此，Hive数据仓库中不建议对数据进行改写，所有的数据都是在加载时确定好的。

• 数据规模

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

• 执行延迟

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

  Hive数据仓库在查询数据时，由于没有对数据中的某些key建立索引，需要扫描整个表中的所有数据，因此访问延迟较高。由于MapReduce本身具有较高的延迟，因此在利用MapReduce执行Hive查询时，也会有较高的延迟。当数据规模大到超过数据库的处理能力时，Hive数据仓库的并行计算显然能体现出优势。

• 可扩展性

  Hive数据仓库是建立在Hadoop集群之上的，所以Hive数据仓库的可扩展性和Hadoop集群的可扩展性是一致的。而数据库的扩展性非常有限。

• 应用场景

  Hive数据仓库是为海量数据做数据分析设计的，而数据库是为实时查询业务设计的。Hive数据仓库的实时性很差，实时性的差别导致Hive数据仓库的应用场景和数据库有很大不同。

  Hive数据仓库构建在基于静态批处理的Hadoop集群之上，由于Hadoop集群通常都有较高的延迟并且在作业提交和调度时需要大量的开销，因此，Hive数据仓库并不适合那些需要低延迟的应用。它最适合应用在基于大量不可变数据的批处理作业中，例如网络日志分析。

三、Hive环境搭建

1、MySQL安装及配置

Hive元数据默认存储在自带的Derby数据库中。Derby数据库只支持单用户模式，不能并发调用Hive。而MySQL数据库存储元数据支持多用户模式，可以并发调用Hive，因此还需要安装MySQL。

centos7在线安装MySQL

下载并安装MySQL官方的 Yum Repository

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm 
# 安装
yum -y install mysql57-community-release-el7-10.noarch.rpm

安装MySQL服务器

yum -y install mysql-community-server

安装完成后就会覆盖掉之前的mariadb。

MySQL数据库设置

# 启动mysql
systemctl start  mysqld.service
# 查看mysql运行状态
systemctl status mysqld.service

此时MySQL已经开始正常运行，不过要想进入MySQL还得先找出此时root用户的密码，通过如下命令可以在日志文件中找出密码

# 进入数据库
mysql -uroot -p
# 输入上面的初始密码

# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
# 其中‘new password’替换成你要设置的密码，注意:密码设置必须要大小写字母数字和特殊符号,不然不能配置成功

# 也可以用下面方法修改密码策略和密码长度
# 修改密码策略
set global validate_password_policy=0;
# 修改密码长度
set global validate_password_length=6;

开启mysql的远程访问

grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;

flush privileges; 

2、Hive安装及配置

2.1、安装

# 解压
tar -zxvf apache-hive-2.1.0-bin.tar.gz -C /opt/module
# 修改名称
mv apache-hive-2.1.0-bin/ hive

2.2、配置

配置环境变量

sudo vi /etc/profile
export HIVE_HOME=/opt/module/hive
export PATH=$PATH:$HIVE_HOME/bin
source /etc/profile

修改配置文件hive-env.sh

cd conf/
mv hive-env.sh.template hive-env.sh
# 配置HADOOP_HOME路径
export HADOOP_HOME=/opt/module/hadoop
# 配置HIVE_CONF_DIR路径：
export HIVE_CONF_DIR=/opt/module/hive/conf

创建hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?><?xml-stylesheet type="text/xsl" href="configuration.xsl"?><configuration>    <!--连接地址--><property>    <name>javax.jdo.option.ConnectionURL</name>    <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&amp;characterEncoding=utf-8&amp;useSSL=false</value>  </property>    <!--连接驱动-->    <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><!--设置显示表头字段名--> <property>   <name>hive.cli.print.header</name>   <value>true</value> </property>  <property>   <name>hive.cli.print.current.db</name>   <value>true</value> </property></configuration>

将mysql连接驱动上传至hive安装目录下lib目录下

启动hive

# 先启动mysqlsystemctl start  mysqld.service# 启动hdfsstart-hdfs.sh# 启动yarnstart-yarn.sh# 启动hivehive

2.3、log日志配置

Hive的运行日志log默认存放在当前用户名下，可以更改它的存放位置。

• 将Hive的运行日志log存放到/opt/module/hive/logs目录下。

• 修改conf下hive-log4j.properties.template文件名为hive-log4j.properties：

  mv hivelog4j.properties.template hive-log4j.properties

• 在hive-log4j.properties文件中修改log存放位置：

  hive.log.dir=/opt/module/hive/logs

四、Hive基础

1、Hive数据类型

在创建Hive表时，须指定表字段的数据类型。

Hive中的数据类型分为基本数据类型和复杂数据类型。基本数据类型包括数值类型、布尔类型、字符串类型、时间戳类型等。复杂数据类型包括数组（Array）类型、映射（Map）类型和结构体（Struct）类型等。

基本数据类型

Hive基本数据类型中的String类型相当于数据库中的Varchar类型，该数据类型是一个可变长的字符串，理论上可以存储2GB的字符。

Hive中涉及的日期时间有两种类型。第一种是Date类型，Date类型的数据是通常所说的日期，通常用“年、月、日”来表示一个具体的日期。Date类型的格式为YYYY-MM-DD，YYYY表示年，MM表示月，DD表示日。Hive中的Date类型只包括年、月、日，不包括时、分、秒。

第二种是TimeStamp类型，它是与时区无关的类型，也就是说，各个时区、各个地方所表示的值是相等的，是一个从UNIX时代开始的时间偏移量。当前使用的时间戳偏移量都是10位整数，如果遇到13位的时间戳，则表示毫秒数。

如果TimeStamp为浮点数，则表示精确到纳秒，小数点后保留9位。在Hive中提供的TimeStamp可转换为日期，其格式为YYYY-MM-DD HH:MM:SS。

Hive有3种复杂数据类型，包括数组、映射和结构体。Array和Map与Java语言中的Array和Map类似，Struct与C语言中的Struct类似。

Array类型声明格式为Array<data_type>，表示相同数据类型的数据所构成的集合。Array元素的访问通过从0开始的下标实现，例如Array[1]访问的是第2个数组元素。

Map类型通过Map<key, value>来声明，key只能是基本数据类型，value可以是任意数据类型。Map元素的访问使用[]，例如Map[‘key1’]。

Struct类型封装一组有名字的字段，可以包含不同数据类型的元素，其类型可以是任意的基本数据类型。Struct类型更灵活，可以存储多种数据类型的数据。Struct元素的访问使用点运算符。

复杂数据类型

2、Hive数据存储

Hive建表后，表的元数据存储在关系型数据库（如MySQL）中，表的数据内容以文件的形式存储在Hadoop集群的HDFS中。

Hive数据是基于HDFS的，在Hive数据仓库中，用户可以非常自由地组织Hive中的表数据，可以用一个文本文件来存储表中的数据。创建一个表时，可以指明列与列的分隔符。

Hive数据存储结构包括表（Table）、外部表（External）、分区表（Partition）、桶表（Bucket）、视图（View）等。

Hive中的表和数据库中的Table在概念上是类似的，每个表在Hive中都有一个相应的目录存储数据。例如，一个表test在HDFS中的路径为/user/hive/warehouse/test，该路径是在配置文件hive-site.xml中由${hive.metastore.warehouse.dir}指定的数据仓库的目录，所有的表数据（不包括外部表）都保存在这个目录中。

分区表对应于数据库中的Partition列的密集索引，在Hive中，表中的一个分区表对应于表下的一个目录，所有的分区表的数据都存储在对应的目录中。

桶表对指定列计算Hash（哈希）值，根据Hash值切分数据，目的是为了并行计算，每个桶表对应一个切分数据后的文件。

外部表指向已经在HDFS中存在的数据，可以创建分区表。它和表在元数据的组织上是相同的，而实际数据的存储则有较大的差异。外部表创建表和加载数据同时完成，加载的数据并不会移动到数据仓库的目录中，只是与加载数据建立一个链接。当删除外部表时，仅删除该链接。

视图建立在已有表的基础上，是一种虚表，是一个逻辑概念。视图可以简化复杂的查询。

3、Hive存储格式

Hive在创建表时需要指明该表的存储格式，Hive支持的表存储格式主要有TextFile、SequenceFile、ORC、Parquet，其中TextFile为默认格式。

3.1、行式存储和列式存储

行式存储的特点

查询满足条件的一整行数据时，行式存储只需要找到其中一个值，其余的值都在相邻地方。列式存储则需要去每个聚集的字段找到对应的每个字段的值，所以行式存储查询的速度快。

列式存储的特点

列式存储中，每个字段的数据聚集存储，在查询只需要少数几个字段时，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以有针对性地设计更好的压缩算法。

TextFile和SequenceFile的存储格式是基于行式存储的；ORC和Parquet的存储格式是基于列式存储的。

3.2、TextFile格式

TextFile格式是Hive在创建表时默认的存储格式，不压缩数据，磁盘开销大，数据解析开销也较大。可结合Gzip、Bzip2压缩使用，但使用Gzip压缩方式，Hive不会对数据进行分割，所以无法对数据进行并行计算。

3.3、SequenceFile格式

SequenceFile格式是Hadoop用来存储二进制数形式的<key,value>键值对而设计的一种文件格式。在存储结构上，SequenceFile主要由一个头部（Header）后面跟多条记录（Record）组成。

Header主要包含存储压缩算法、用户自定义元数据等信息。此外，还包含一些同步标识，用于快速定位到记录的边界。每条记录以键值对的方式进行存储，用来表示它的字符数组可以一次解析为记录的长度、key的长度、key值和value值，并且value值的结构取决于该记录是否被压缩。SequenceFile格式支持数据压缩，有以下3种类型的压缩。

无压缩类型(None)

如果没有启用压缩（默认设置），那么每个记录就由它的记录长度（字节数）、key的长度、key值和value值组成。

记录压缩类型(Record)

记录压缩类型与无压缩类型基本相同，不同的是，值字节是用定义在头部的编码器来压缩的。

块压缩类型(Block)

块压缩类型一次压缩多个记录，因此它比记录压缩类型更紧凑，所以一般优先选择该类型。

3.4、ORC格式

ORC（Optimized Row Columnar）格式是Hive 0.11版本引入的表存储格式。

每个ORC文件由一个或多个Stripe组成，每个Stripe大小为250MB。每个Stripe由3部分组成，分别是Index Data、Row Data和Stripe Footer。

Index Data

默认为每隔1万行做一个索引。该索引只记录某行的各字段在Row Data中的偏移量（Offset）。

Row Data

其存储的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行编码，分成多个Stream来存储。

Stripe Footer

其存储的是各个Stream的类型、长度等信息。

每个ORC文件还有一个File Footer，这里面存储的是每个Stripe的行数、每列的数据类型信息等。每个ORC文件的尾部是一个PostScript，这里记录了整个文件的压缩类型及File Footer的长度信息等。在读取文件时，会Seek（寻找）到文件尾部读取PostScript，从里面解析得到File Footer长度，再读File Footer，从里面解析得到各个Stripe信息，再读各个Stripe，即从后往前读。

3.5、Parquet格式

Parquet格式文件是以二进制数形式存储的，所以是不可以直接读取的，文件中包含该文件的数据和元数据，因此Parquet格式文件是自解析的。

通常情况下，在存储Parquet数据时会按照Block（块）的大小设置行组的大小。一般情况下，由于每个Map任务处理数据的最小单位是一个Block，这样可以把每个行组由一个Map任务处理，增加了任务执行并行度。

一个Parquet格式文件可以存储多个行组，文件的首位都是该文件的Magic Code，用于校验是否是一个Parquet文件，Footer Length记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每个行组的元数据信息和该文件存储数据的Schema信息。除了文件中每个行组的元数据，每页的开始都会存储该页的元数据。

五、Hive数据定义

1、数据仓库

创建数据仓库

# 创建仓库语法格式CREATE DATABASE [IF EXISTS] <database name> LOCALTION <dir>;# 创建名为hive的数据库create database hive;# 创建一个数据仓库，并存放在HDFS的根目录下：create database if not exists hive2 location '/hive2.db';

查询数据仓库

# 显示数据仓库show databases;# 过滤查询数据仓库show databases like 'hive*';# 查看数据仓库详细信息desc database extended hive;

修改数据仓库

用户可以使用Alter命令修改数据仓库的Dbproperties键值对的属性值，来描述这个数据仓库的属性信息。数据仓库的其他元数据信息都是不可更改的，包括数据仓库名和数据仓库所在存储位置。

alter database hive set dbproperties('createtime'='20210921');

在Hive中查看修改结果：

desc database extended hive;

删除数据仓库

DROP DATABASE [IF EXISTS] database_name [CASCADE];

如果不知道删除的数据仓库是否存在，则使用IF EXISTS判断数据仓库是否存在。如果数据仓库不为空，其中已经有表存在，可以采用CASCADE关键字强制删除。

drop database hive;

2、表

在Hive中，表都在HDFS的相应目录中存储数据。目录的名称是在创建表时自动创建并以表名来命名的，表中的数据都保存在该目录中。而且，数据以文件的形式存储在HDFS中。

表的元数据会存储在数据库中，如Derby数据库或MySQL数据库。

# 创建表语法格式CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 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, ...) INTO num_buckets BUCKETS][SORTED BY (col_name [ASC|DESC], ...)][ROW FORMAT DELIMITED row_format][STORED AS file_format][LOCATION hdfs_path];

① CREATE TABLE，创建一个名字为table_name的表。如果该表已经存在，则抛出异常；可以用IF NOT EXISTS关键字选项来忽略异常。② 使用EXTERNAL关键字可以创建一个外部表，在建表的同时指定实际表数据的存储路径（LOCATION）。创建Hive内部表时，会将数据移动到数据仓库指定的路径；若创建Hive外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除内部表时，内部表的元数据和数据会被一起删除；在删除外部表时，只删除外部表的元数据，但不删除数据。③ (col_name data_type, ...)，创建表时要确定字段名及其数据类型，数据类型可以是基本数据类型，也可以是复杂数据类型。COMMENT为表和字段添加注释描述信息。④ PARTITIONED BY，创建分区表｡⑤ CLUSTERED BY，创建桶表｡⑥ SORTED BY，排序｡⑦ ROW FORMAT DELIMITED，用于指定表中数据行和列的分隔符及复杂数据类型数据的分隔符。这些分隔符必须与表数据中的分隔符完全一致。	[Fields Terminated By Char]，用于指定字段分隔符。	[Collection Items Terminated By Char]，用于指定复杂数据类型Map、Struct和Array的数据分隔符。	[Map Keys Terminated By Char]，用于指定Map中的key与value的分隔符。	[Lines Terminated By Char]，用于指定行分隔符。⑧ STORED AS，指定表文件的存储格式，如TextFile格式、SequenceFile格式、ORC格式和Parquet格式等。如果文件数据是纯文本的，可以使用TextFile格式，这种格式是默认的表文件存储格式。如果数据需要压缩，可以使用SequenceFile格式等。⑨ LOCATION，用于指定所创建表的数据在HDFS中的存储位置。

2.1、内部表

创建

# 创建表create table test(id int,name string)row format delimited fields terminated by '\t';# 导入数据load data local inpath '/home/test.txt' into table test;

创建复杂数据类型内部表

# 数据{    "name": "Steve",    "friends": ["Bill" , "Linus"],  //Array    "children": {                      //Map        "Paul": 18,        "Dennis": 21    }    "address": {                       //Struct        "street": "xidan",        "city": "beijing"     }}# 注意：Array、Map和Struct类型中的元素间关系都用同一个字符表示，这里用“_”。

create table complex(name string,friends array<string>,children map<string, int>,address struct<street:string, city:string>)row format delimited fields terminated by ','collection items terminated by '_'map keys terminated by ':';# 导入文本数据到表complexload data local inpath '/home/complex.txt' into table complex;

2.2、外部表

带EXTERNAL关键字创建的表是外部表。外部表和内部表在元数据的组织上是相同的。

外部表加载的数据文件存储在LOCATION指定的目录下，该表会读取到该目录下的全部文件。创建外部表时，如果没有使用LOCATION指定数据文件存储位置，Hive将在HDFS中的/user/hive/warehouse数据仓库所在目录以外部表的表名创建一个目录，并将属于这个表的数据存放在这里。

删除外部表并不会删除LOCATION指定目录下的表数据文件，只是删除了外部表的元数据信息。

内部表和外部表的使用场景不同。例如，对每天收集到的网站数据，需要做大量的统计数据分析，所以在数据源上可以使用外部表进行存储，以方便数据的共享。在做统计分析时用到的中间表、结果表可以使用内部表，因为这些数据不需要共享，所以使用内部表更为合适。

# 创建外部表create external table if not exists dept(deptno int,dname string,buildingsno int)row format delimited fields terminated by '\t';# 查看表格式化数据desc formatted dept;

2.3、内部表和外部表的转换

内部表和外部表之间可以互相转换，由关键字ALTER…SET设置。

# 语法格式ALTER TABLE table_name SET TBLPROPERTIES('EXTERNAL'='TRUE|FALSE');# 外部表转内部表alter table dept set tblproperties('EXTERNAL'='FALSE');

2.4、分区表

Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。分区表实际上就是对应一个HDFS中的目录，该目录下是该分区所有的数据文件。

在Hive中，一个分区对应于表下的一个子目录，而所有的分区数据都存储在对应的子目录中。

Hive中分区字段不是表中的一个实际的字段，而是一个或者多个伪字段。也就是说，在分区表的数据文件中实际并不保存分区字段的信息与数据。

创建分区表的操作在实际的生产环境中是非常有用的，可以提高查询速度，当数据量非常大时，建立分区表是非常有必要的。

# 创建分区表create table dept_p(deptno int, dname string, buildingsno int)partitioned by (month string)row format delimited fields terminated by '\t';# 加载数据到分区load data local inpath '/home/dept.txt' into table dept_p partition(month='202109');load data local inpath '/home/dept.txt' into table dept_p partition(month='202108');load data local inpath '/home/dept.txt' into table dept_p partition(month='202107');load data local inpath '/home/dept.txt' into table dept_p partition(month='202106');# 查询分区表select deptno,dname,buildingsno from dept_p where month='202109';

# 创建二级分区create table dept_p2(deptno int, dname string, buildingsno int) partitioned by (month string, day string) row format delimited fields terminated by '\t';# 加载数据load data local inpath '/home/dept.txt' into table dept_p2 partition(month='202109', day='18');# 查询select * from dept_p2 where month='202109' and day='18';

把数据直接上传到分区目录中，让分区表和数据产生关联的3种方式

• 上传数据后修复

# 上传数据dfs -mkdir –p /user/hive/warehouse/hive.db/dept_p2/month=202109/day=12; dfs -put /opt/datas/dept.txt /user/hive/warehouse/hive.db/dept_p2/month=202109/day=12;# 查询数据（查询不到刚上传的数据）：select * from dept_p2 where month='202109' and day='12';# 执行修复命令：msck repair table dept_p2;# 再次查询数据：select * from dept_p2 where month='202109' and day='12';

• 上传数据后添加分区

# 上传数据：dfs -mkdir –p /user/hive/warehouse/hive.db/dept_p2/month=202109/day=11;dfs -put /opt/datas/dept.txt /user/hive/warehouse/hive.db/dept_p2/month=202109/day=11;# 执行添加分区字段：alter table dept_p2 add partition(month='202109',day='11');# 查询数据：select * from dept_p2 where month='202109' and day='11';

• 创建目录后加载数据到分区表

# 创建目录：dfs -mkdir –p /user/hive/warehouse/hive.db/dept_p2/month=202109/day=10;# 上传数据：load data local inpath '/home/dept.txt' into table dept_p2 partition(month='202109',day='10');# 查询数据：select * from dept_p2 where month='202109' and day='10';

2.5、桶表

分区提供了一种隔离数据和优化查询的便利方式。不过，并非所有的数据都可形成合理的分区。

桶表是将数据分解成更容易管理的若干部分。桶表是通过对指定字段进行Hash计算来实现的，通过Hash值将一个字段名下的数据切分为若干部分，并使每个部分对应于该字段名下的一个存储文件。

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

分桶字段是表中的字段。Hive将表组织成桶表有以下目的：

• 抽样更高效。当处理大规模的数据集时，在开发、测试阶段将所有的数据全部处理一遍可能不太现实，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

• 更好的查询效率。桶表为表提供了额外的结构，Hive在处理某些查询时利用这个结构，能够有效地提高查询效率。

在建立桶表之前，需要设置hive.enforce.bucketing属性为True，使得Hive能识别到桶表。

# 桶表属性设置set hive.enforce.bucketing=true;set mapreduce.job.reduces=-1;# 创建桶表create table test_b(id int, name string)clustered by(id) into 4 bucketsrow format delimited fields terminated by '\t';

2.6、修改表

# 重命名ALTER TABLE table_name RENAME TO new_table_name;alter table dept_p2 rename to dept_p3;# 增加单个分区alter table dept_p add partition(month='202105');# 同时增加多个分区，多个分区之间使用空格alter table dept_p add partition(month='202104') partition(month='202103');# 删除单个分区alter table dept_p drop partition(month='202103');# 同时删除多个分区，多个分区之间使用逗号alter table dept_p drop partition(month='202104'), partition(month='202105');# 修改列的语法格式：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 ADD|REPLACE COLUMNS(col_name data_type [COMMENT col_comment], …) 关键字ADD表示新增一字段，字段位置在所有列后面（Partition列前）；REPLACE则表示替换表中所有字段。

2.7、删除表

表的删除使用关键字Drop。

当删除一个内部表时，Hive也会删除这个表中的数据，所以内部表不适合和其他工具共享数据。删除外部表并不会删除表数据，只是描述表的元数据信息会被删除。

drop table dept_p2;

删除表中数据Truncate

Truncate仅删除表中数据，保留表的元数据信息。Truncate只能删除内部表中的数据，不能删除外部表中的数据。外部表在删除后，HDFS中的数据还存在，不会被删除。因此要删除外部表数据，可以把外部表转成内部表或者删除HDFS文件。

truncate table test;

2.8、视图

视图在Hive中的用法和在SQL中的用法相同。视图是一种虚表，是一个逻辑概念，可以跨越多个表，操作视图和操作表是完全一样的。视图建立在已有表的基础上，视图并不存储数据。从视图中查询出来的数据，都来自视图所依赖的表，视图赖以建立的表称为基表。可以根据用户的需求创建视图，也可以将任何结果集数据保存为一个视图。可以在视图上执行所有DML操作。视图可以简化复杂的查询。

# 创建视图语法格式CREATE VIEW[IF NOT EXISTS]view_name[(column_name[COMMENT column_comment],...)] [COMMENT table_comment];# 创建视图create view test_view as select * from test;# 查询select * from test_view; # 删除视图drop view test_view;

六、HQL查询

大多数语法跟mysql一致，就不细说了

说一说排序

1、排序

1.1、Order By全局排序

Order By按照一个或多个字段排序。

Hive中的Order By和传统SQL中的Order By一样，对查询结果做全局排序，会新启动一个任务进行排序，把所有数据放到同一个Reduce中进行处理。不管数据有多少，不管文件有多少，都启用一个Reduce进行处理。

数据量大的情况下将会消耗很长时间去执行排序，而且可能不会出结果，因此必须使用关键字Limit指定输出条数。

ASC（Ascend）表示升序（默认），DESC（Descend）表示降序。Order By语句在Select语句的结尾。

select ename,gender,bday,area,score,deptno from emp order by deptno;

1.2、Sort By内部排序

Sort By是内部排序，会在每个Reduce中进行排序，单个Reduce出来的数据是有序的，但不保证全局有序。假设设置了3个Reduce，那么这3个Reduce就会生成3个文件，每个文件都会按Sort By设置的条件排序，但是当这3个文件数据合在一起，就不一定有序了。一般情况下，可以先进行Sort By内部排序，再进行全局排序，这样会提高排序效率。

使用Sort By可以先指定执行的Reduce个数（set mapreduce.job.reduces=<number>），对输出的数据再执行排序，即可以得到全部排序结果。

# 设置Reduce个数为3set mapreduce.job.reduces=3;select empno,ename,bday,area,score,deptno from emp sort by deptno desc;

1.3、Distribute By分区排序

Hive中的Distribute By是控制在Map端如何拆分数据给Reduce端的。按照指定的字段把数据划分到不同的Reduce输出文件中，默认采用Hash算法。对于Distribute By分区排序，一定要多分配Reduce进行处理，否则无法看到Distribute By分区排序的效果。Hive要求Distribute By语句写在Sort By语句之前。

Distribute By和Sort By的使用场景主要包括：

• Map输出的文件大小不均；

• Reduce输出的文件大小不均；

• 小文件过多；

• 文件超大。

set mapreduce.job.reduces=3;select empno,ename,bday,area,score,deptno from emp distribute by deptno sort by empno desc;

1.4、Cluster By排序

Cluster By除具有Distribute By的功能外，还兼具Sort By的功能。当Distribute By和Sort By字段相同时，可以使用Cluster By方式排序。但是只能是升序排序，不能是降序排序。下面两种写法完全等价：

select empno,ename,bday,area,score,deptno from emp cluster by deptno;# 或select empno,ename,bday,area,score,deptno from emp distribute by deptno sort by deptno;

注意：按照部门编号分区，不一定就是固定的数值，可以是200号和300号部门分到一个分区里面。

2、抽样查询

当数据量特别大，对全部数据进行处理存在困难时，抽样查询就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性。Hive支持桶表抽样查询、数据块抽样查询和随机抽样查询。

2.1、桶表抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果，Hive可以通过对桶表进行抽样查询来满足这个需求。

桶表抽样语法格式：

TABLESAMPLE(BUCKET x OUT OF y ON col_name | RAND())

Tablesample语句允许用户编写用于数据抽样而不是整个表的查询，该语句出现在From语句中，可用于桶表中。

桶表编号从1开始，col_name表明抽取样本的字段，可以是非分区字段中的任意一字段，或者使用随机函数Rand()表明在整个行中抽取样本而不是单个字段。在col_name上桶表的行随机进入1到y个桶中，返回属于桶x的行。

y必须是桶表总桶数的倍数或者因子。Hive根据y的大小，决定抽样的比例。x表示从哪个桶开始抽取，如果需要抽取多个桶，以后的桶号为当前桶号加上y。需要注意的是，x的值必须小于或等于y的值，否则出现异常。

# 查询桶表test_b中的数据，抽取桶1数据select id,name from test_b tablesample(bucket 1 out of 4 on id);# 查询桶表test_b中的数据，抽取桶1和桶3数据select id,name from test_b tablesample(bucket 1 out of 2 on id);# 查询桶表test_b中的数据，随机抽取4个桶中的数据select id,name from test_b tablesample(bucket 1 out of 4 on rand());

2.2、数据块抽样查询

Hive提供了一种按照抽样百分比进行抽样查询的方式，这是基于行数的、按照输入路径下的数据块百分比进行抽样的。该方式还允许Hive随机抽取数据总量的百分比或n字节的数据及n行数据。

• 语法格式

SELECT * FROM <Table_Name> TABLESAMPLE(N PERCENT|ByteLengthLiteral|N ROWS);

其中，ByteLengthLiteral的取值为 (Digit)+ (b | B | k | K| m | M| g | G)，表明数据的单位。

• 按数据块百分比抽样

按数据块百分比抽样允许抽取数据行数大小的至少n%作为输入，支持CombineHiveInput Format，而一些特殊的压缩格式是不能够被处理的，如果抽样失败，MapReduce作业的输入将是整个表。由于在HDFS块层级进行抽样，所以抽样粒度为块的大小，例如，如果块大小为256MB，即使输入的n%仅为100MB，也会得到256MB的数据。例如：

select ename, bday, score from emp tablesample(10 percent);

• 按数据大小抽样

按数据大小抽样方式的最小抽样单元是一个HDFS数据块。如果数据大小小于普通的块大小128MB，那么会返回所有的行。例如：

select ename, bday, score from emp tablesample(1b);

• 按数据行数抽样

这种方式可以根据行数来抽样，但要特别注意的是这里指定的行数，是在每个InputSplit中抽样的行数，也就是说，每个Map中都抽样n行。例如：

select ename, bday, score from emp tablesample(8 rows);

2.3、随机抽样查询

使用Rand()函数可以进行随机抽样查询，Limit关键字限制抽样返回的数据条数，其中Rand()函数前的Distribute By和Sort By关键字可以保证数据在Map和Reduce阶段是随机分布的。

例如，随机抽取emp表中的10条数据：

select empno,ename,bday from emp distribute by rand() sort by rand() limit 10; 

七、Hive内置函数

Hive提供了丰富的内置函数，可供用户直接使用。从功能上分类，内置函数主要包括数值计算函数、聚合函数、日期时间函数、条件函数、字符串处理函数等。从参数和返回值个数分类，内置函数又分为一进一出、多进一出和一进多出函数。对于内置函数，要掌握函数名称、参数的数据类型和个数及返回值的数据类型和个数。

1、Hive内置函数

1.1、常见的数值计算函数

1.2、常见的聚合函数

1.3、常见的日期时间函数

# 获取当前时间戳数据select unix_timestamp() utimestamp,from_unixtime(unix_timestamp(),'yyyy-mm-dd hh:mm:ss')  dates; 

1.4、常见的条件函数

select  deptno,  sum(case gender when '男' then 1 else 0 end) male_c,  sum(case gender when '女' then 1 else 0 end) female_cfrom   empgroup by  deptno; 

1.5、常见的字符串处理函数

2、其他常用函数

2.1、空字段赋值函数

NVL函数给字段值为Null的数据赋值，它的语法格式为：

NVL(string, replace_with)

其功能是：如果string为Null，则NVL()函数返回replace_with的值，否则返回string的值；如果两个参数都为Null，则返回Null。coalesce(string, replace_with)函数的功能和NVL()函数完全一样。

2.2、列转行函数

Hive中的表分析函数接受零个或多个输入，然后产生多列或多行输出，Explode()函数就是表分析函数。

Explode()函数以复杂数据类型Array或Map为输入，对数组中的数据进行拆分，返回多行结果，一行一个数组元素值。Explode()函数只是生成了一种数据的展示方式，而无法在表中产生一个其他的列，因此还需要使用LATERAL VIEW来进行处理。

Explode()函数的语法格式：

LATERAL VIEW EXPLODE(col) subView AS sub

在这里LATERAL VIEW是将Explode的结果转换成一个视图subView，在视图中的单列列名定义为sub，然后在查询时引用这个列名就能够查到。

2.3、窗口函数

Hive聚合函数将多行数据按照规则聚合为一行，一般来讲，聚合后的行数是要少于聚合前的行数的。但是有时需要既显示聚合前的数据，又要显示聚合后的数据，这时便引入了窗口函数。

窗口函数Over()用于计算基于组的某种聚合值。它和聚合函数的不同之处在于，对于每个组返回多行，而聚合函数对于每个组只返回一行。窗口函数Over()和聚合函数一起使用，并且放在聚合函数的后面。

窗口函数Over()指定数据窗口大小，这个数据窗口大小会随着行的变化而变化。

窗口函数Over()可以带的参数除Partition By语句、Order By语句和Distribute By语句外，还可以带如下几个参数，这些参数决定了窗口的大小。

• Current Row：当前行。

• n Preceding：往前移动n行数据。

• n Following：往后移动n行数据。

• Unbounded：起点。

• Unbounded Preceding：表示从前面的起点。

• Unbounded Following：表示到后面的终点。

窗口函数Over()使用窗口规范，窗口规范支持以下格式：

• (Rows | Range) Between (Unbounded | [n]) Preceding and ([n] Preceding | Current Row | (Unbounded | [n]) Following)

• (Rows | Range) Between Current Row and (Current Row | (Unbounded | [n]) Following)

• (Rows | Range) Between [n] Following and (Unbounded | [n]) Following

Partition By语句也可以称为查询分区语句，非常类似于Group By语句，都是将数据按照边界值分组的，而Over之前的聚合函数在每个分组之内进行，如果超出了分组，则函数会重新计算。Partition By语句使用一个或者多个原始数据类型的列。

Order By语句会让输入的数据强制排序（窗口函数是HQL语句最后执行的函数，因此可以把HQL结果集想象成输入数据）。Partition By与Order By语句使用一个或者多个数据类型的分区或者排序列。

Lag()和Lead()函数也是常用的窗口函数，可以返回上、下数据行的数据。

函数Lag(col,n,default)用于统计窗口内往上移动第n行数据。第一个参数为字段名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为Null时，取默认值，如不指定，则为Null）。

函数Lead(col,n, default)用于统计窗口内往下移动第n行数据。第一个参数为字段名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为Null时，取默认值，如不指定，则为Null）。

窗口函数Ntile(n)把有序的数据集合平均分配到指定数量的n个切片分组中，将切片号分配给每一行。如果不能平均分配，则优先分配较小编号的切片，并且各个切片中能放的行数最多相差1。然后根据切片号，选取前或后n分之几的数据。这里n必须为Int类型。

数据payment.txt如下：共有3个字段：name、paydate、cost。Linus,2021-04-07,56Linus,2021-04-11,58Allen,2021-05-09,22Linus,2021-04-10,55Allen,2021-06-11,70Linus,2021-07-22,89James,2021-02-01,18Steve,2021-01-12,11James,2021-02-03,23Steve,2021-01-14,21James,2021-01-05,46James,2021-04-16,42Steve,2021-03-07,58James,2021-01-08,55Linus,2021-04-08,66Linus,2021-04-12,68Allen,2021-05-10,12Linus,2021-04-11,65Allen,2021-06-12,80Linus,2021-07-23,99创建表并导入数据create table payment(name string, paydate string,cost int) row format delimited fields terminated by ',';load data local inpath "/home/payment.txt" into table payment;# 查询在2021年4月份购买过的顾客及总人数select name,count(*) over () from payment where substring(paydate,1,7) = '2021-04' group by name;select name,paydate,cost, sum(cost) over() as s1,--所有行相加 sum(cost) over(partition by name) as s2,--按name分组，组内数据相加 sum(cost) over(partition by name order by paydate) as s3,--按name分组，组内数据累加 sum(cost) over(partition by name order by paydate rows between unbounded preceding and current row) as s4 ,--和s3一样,由起点到当前行的聚合 sum(cost) over(partition by name order by paydate rows between 1 preceding and current row) as s5, --当前行和前面一行进行聚合 sum(cost) over(partition by name order by paydate rows between 1 preceding and 1 following) as s6,--当前行和前边一行及后面一行 sum(cost) over(partition by name order by paydate rows between current row and unbounded following) as s7 --当前行及后面所有行 from payment;# 查询顾客上次的购买时间select name,paydate,cost, lag(paydate,1,'1900-01-01') over(partition by name order by paydate ) as time1, lag(paydate,2) over (partition by name order by paydate) as time2 from payment;

2.4、排序函数

Hive常用的排序函数包括Rank()、Dense_Rank()和Row_Number()。排序函数与上节介绍的窗口函数一起使用，并放在窗口函数的前面，生成一定的排序结果。

函数Rank()排序相同时会重复，总数不会变。也就是说，Rank()函数是进行跳跃排序的，Rank()函数生成的序号有可能不连续。例如，有两个第二名时接下来就是第四名。

函数Dense_Rank()排序相同时会重复，总数会减少。也就是说，Dense_Rank()函数是进行连续排序的，即生成的序号是连续的。例如，有两个第二名时仍然跟着第三名。

函数Rank()和函数Dense_Rank()的区别在于排名相等时会不会留下空位。

函数Row_Number()先使用函数Over()中的排序语句对记录进行排序，从1开始，按照顺序，生成分组内记录的序列。函数Row_Number()的值不会重复，当排序的值相同时，按照表中记录的顺序进行排序。

数据score.txt如下：

# 创建表并导入数据create table score(name string,course string, score int) row format delimited fields terminated by "\t";load data local inpath '/home/score.txt' into table score;# 计算每门课程成绩排名select name, course, score,rank() over(partition by course order by score desc) rk,dense_rank() over(partition by course order by score desc) dr,row_number() over(partition by course order by score desc) rnfrom score;

八、Hive数据压缩

压缩数据，可以大量减少磁盘的存储空间。把设置存储格式和压缩数据结合使用，可以最大限度地节省存储空间。

1、数据压缩格式

在Hive中对中间数据或最终数据进行压缩，是提高数据性能的一种手段。压缩数据，可以大量减少磁盘的存储空间，比如基于文本的数据文件，可以将文件压缩40%或更多。同时，压缩后的文件在磁盘间传输时所用的I/O也会大大减少。当然，压缩和解压缩会带来额外的CPU开销，但是可以节省更多的I/O和使用更少的内存开销。

常见的数据压缩格式有Gzip、Bzip2、LZO、LZ4和Snappy等。

可使用以下3种指标对压缩格式进行评价。

• 压缩比：压缩比越高，压缩后文件越小，所以压缩比越高越好。
• 压缩速度：压缩速度越快越好。
• 可分割：已经压缩的格式文件是否可以再分割。可以分割的格式允许单一文件由多个Map程序处理，从而更好地并行化计算和处理。

压缩格式对比

• Bzip2具有最高的压缩比，但会带来更大的CPU开销，Gzip较Bzip2次之。如果基于磁盘利用率和I/O考虑，这两种压缩格式都比较有吸引力。

• LZO和Snappy有更快的解压缩速度，如果更关注压缩、解压缩速度，它们都是不错的选择。LZO和Snappy在压缩数据上的速度大致相当，但Snappy在解压缩速度上较LZO更快。

• Hadoop会将大文件分割成HDFS块（默认128MB）大小的分片，每个分片对应一个Map程序。在这几种压缩格式中，Bzip2、LZO、Snappy压缩是可分割的，Gzip则不支持分割。

Hive表存储格式与压缩相结合

TextFile为默认Hive表存储格式，数据加载速度最快，可以采用Gzip进行压缩，压缩后的文件无法分割，即并行处理。

SequenceFile表存储格式的压缩比最低，查询速度一般，将数据存储到SequenceFile格式的Hive表中，这时数据就会压缩存储，共有3种压缩格式（None、Record、Block），是可分割的文件格式。

ORC表存储格式的压缩比最高，查询速度最快，数据加载速度最慢。ORC格式由于采用列式存储方式，数据加载时性能消耗较大，但是具有较好的压缩比和查询速度。ORC格式支持压缩的3种类型为None、Zlib、Snappy。

在Hive中使用压缩需要灵活的方式，针对不同的应用场景使用不同的压缩方式。如果是数据源，采用ORC+Bzip2或ORC+Gzip的方式，这样可以很大程度上节省磁盘空间；而在计算的过程中，为了不影响执行的速度，可以浪费一些磁盘空间，建议采用ORC+Snappy的方式，这样可以整体提升Hive的执行速度。

2、Hadoop压缩

# 查看hadoop支持的压缩格式hadoop checknative

2.1、MepReduce支持的压缩编码

为了支持多种压缩和解压缩格式，Hadoop引入了编码/解码器

2.2、MepReduce压缩格式参数配置

若在Hadoop中启用压缩，可以在mapred-site.xml等配置文件中配置参数

2.3、Map输出压缩开启

开启Map输出阶段压缩可以减少作业中Map任务和Reduce任务之间的数据传输量，具体配置参数如下。

hive.exec.compress.intermediate：该值默认为False，设置为True时激活中间数据压缩功能。

mapreduce.map.output.compression.codec：Map输出压缩格式的配置参数，可以使用LZO、LZ4或Snappy编/解码器在此阶段压缩数据。SnappyCodec压缩格式会带来很好的压缩性能和较低的CPU开销。HQL语句最终会被编译成Hadoop的MapReduce作业，开启Hive的中间数据压缩功能，就是在MapReduce的Shuffle阶段对Map产生的中间结果进行数据压缩。在这个阶段，优先选择一个低CPU开销的压缩格式。

2.4、Reduce输出压缩开启

当Hive将输出写入表中时，输出内容同样可以进行压缩。控制这个功能的属性为hive.exec.compress.output，该属性的默认值为False，这种情况下输出的就是非压缩的纯文本文件。用户可以通过在查询语句或执行脚本中设置这个值为True，来开启输出结果压缩功能。

例如：

（1）开启Hive最终输出数据压缩功能

set hive.exec.compress.output=true;

（2）开启MapReduce最终输出数据压缩功能

set mapreduce.output.fileoutputformat.compress=true;

（3）设置MapReduce最终数据输出压缩方式为Snappy

set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

九、Hive优化

1、Hive参数优化

1.1、本地模式

Hive在Hadoop集群上查询时，默认是在Hadoop集群上的多台服务器上运行的（需要这些服务器协调运行），这种方式很好地解决了大数据的查询问题。但是当Hive查询处理的数据量比较小时，为查询触发执行任务消耗的时间可能会比实际作业（Job）的执行时间要长得多，在这种情况下，因为以分布式方式执行就涉及跨网络传输、多节点协调、资源调度分配等，消耗资源较大。对于大多数这种情况，Hive可以通过本地模式在单台服务器上处理所有的任务，对于小数据集，执行时间可以明显缩短。

用户可以通过在Hive的hive-site.xml配置文件中配置参数hive.exec.mode.local.auto、hive.exec.mode.local.auto.inputbytes.max和hive.exec.mode.local.auto.input.files.max，来实现本地模式的优化。hive.exec.mode.local.auto的属性值为True时，允许Hive在适当时自动启动本地模式优化。当然，也可以在客户端设置本地模式的相关参数。

hive.exec.mode.local.auto：设置是否开启本地模式，取值为True或False，默认值为False。

1.2、Fetch抓取

Fetch抓取是指在Hive中对某些情况的查询可以不必执行MapReduce程序。例如“Select * From emp;”，在这种情况下，Hive可以简单地读取表emp对应的存储目录下的文件，然后输出查询结果到控制台，效率提高。

在hive-default.xml配置文件中，参数hive.fetch.task.conversion的属性值分别是None、Minimal和More，默认值为More。默认值More表明，在全局查找、字段查找、过滤查找、Limit查找等都不执行MapReduce程序。属性值None表明，执行查询语句，会执行MapReduce程序。

1.3、并行执行

Hive会将一个查询转化成一个或者多个阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的作业可能包含多个阶段，而有些阶段是可以并行执行的，这样可能使得整个作业的执行时间缩短。

在hive-default.xml配置文件中通过设置参数hive.exec.parallel的属性值为True，就可以开启并行执行，默认值为False。通过设置参数hive.exec.parallel.thread.number来设置同一个HQL允许的最大并行度，即同时最多可以执行多少个任务，默认为8。在共享Hadoop集群中，需要注意的是，如果作业中并行阶段增多，那么Hadoop集群利用率就会增加。

启动任务并行执行：

set hive.exec.parallel=true;

同一个HQL允许的最大并行度，即同时最多可以执行多少个任务：

set hive.exec.parallel.thread.number=16;

在资源充足时，参数hive.exec.parallel会让那些存在并行作业的HQL运行得更快，但同时消耗更多的资源。当然，并行执行是在系统资源比较空闲时才有优势，否则，没资源的情况下，并行执行并不能运行起来。

1.4、严格模式

Hive的严格模式可以防止用户执行那些可能意想不到的查询。也就是说，严格模式可以禁止某些查询的执行。

在hive-default.xml配置文件中通过设置参数hive.mapred.mode的属性值，可以开启严格模式。参数值有两个：Strict（严格模式）和Nonstrict（非严格模式，默认值）。

开启严格模式可以禁止3种类型的查询。

• 带有分区表的查询

  如果对一个分区表执行Hive查询，除非Where语句中含有分区字段过滤条件来限制范围，否则不允许执行。也就是说，就是用户不允许扫描所有分区。这种限制的原因是，通常分区表都拥有非常大的数据集，没有进行分区限制的查询可能会消耗巨大资源来处理这个表查询。

• 带有Order By的查询

  对于使用了Order By语句的查询，要求必须使用Limit语句。因为Order By为了执行排序，会将所有的结果数据分发到同一个Reduce中进行处理，所以强制用户使用Limit语句以防止Reduce额外执行很长一段时间。

• 限制笛卡儿积的查询

  进行表连接时，不写关联条件或关联条件失效会导致笛卡儿积。数据量非常大时，笛卡儿积查询会出现不可控的情况，因此严格模式下也不允许执行。例如，当执行下面这个笛卡儿积查询时是被限制执行的：

  set hive.mapred.mode=strict;select e.ename, d.dname from emp e, dept d;FAILED: SemanticException Cartesian products are disabled for safety reasons. If you know what you are doing, please make sure that hive.strict.checks.cartesian. product is set to false and that hive.mapred.mode is not set to 'strict' to enable them.
  

1.5、推测执行

在Hadoop分布式集群环境下，因为程序本身的问题、负载不均衡或者资源分布不均等原因，会造成同一个作业的多个任务之间的运行速度不一致，会拖慢作业的整体执行进度。Hadoop采用了推测执行（Speculative Execution）机制，它能够推测出“拖后腿”的任务，并为这样的任务启动一个备份，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果。也就是说，如果作业中大多数的任务都已经完成了，Hadoop平台会在几个空闲的节点上调度执行剩余任务的拷贝，这个过程称为推测执行。当任务完成时，它会向JobTracker通告。任何一个首先完成的拷贝任务将成为权威拷贝，如果其他拷贝任务还在推测执行中，Hadoop会告诉TaskTracker去终止这些任务并丢弃它们的输出，接着Reduce会从首先完成的Map那里获取输入数据。

推测执行属于Hadoop的MapReduce框架的属性。设置开启推测执行参数在Hadoop的mapred-site.xml文件中进行配置，默认就是开启的状态。

<property>  <name>mapreduce.map.speculative</name>  <value>true</value>  <description>If true, then multiple instances of some map tasks       may be executed in parallel.</description></property>

Hive本身在hive-default.xml文件中也提供了配置项来控制Reduce的推测执行：

<property>   <name>hive.mapred.reduce.tasks.speculative.execution</name>   <value>true</value>   <description>Whether speculative execution for reducers 		should be turned on</description></property>

这个推测执行机制主要用来避免某个Reduce任务的执行环境有问题或者某个Reduce任务执行中发生了反常情况而迟迟不能完成从而拖慢整体进度。但是这个机制在有些情况下也会造成问题，例如，如果一个Reduce程序在相同输入并发执行的情况下会造成冲突，那么推测执行机制可能是一个较大的隐患。

1.6、JVM重用

JVM重用是Hadoop优化的内容，当然对Hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或任务特别多的场景。

Hadoop默认为每个任务（Map 任务或Reduce 任务）启动一个JVM。对于小文件过多的问题，设置了JVM重用。JVM重用可以使得JVM实例在同一个作业中重新使用N次。N的值可以在Hadoop的mapred-site.xml文件中进行配置，通常为10～20。

<property> <name>mapreduce.job.jvm.numtasks</name> <value>10</value> <description>How many tasks to run per jvm. If set to -1, there is no limit.</description></property>

在Hive中可以查看和设置Hadoop中JVM的属性值，默认值为1：

set mapreduce.job.jvm.numtasks=10;

当然，JVM的启动过程也可能会造成相当大的开销，尤其是执行的作业包含成百上千个任务的情况。

2、数据倾斜

由于数据分布不均匀，造成数据大量地集中到一点，形成数据倾斜。查看任务监控页面，发现只有少量Reduce子任务未完成，因为其处理的数据量和其他Reduce差异过大。单一Reduce处理的记录数和平均记录数相差太大，通常达到好几倍之多，最长时间远大于平均时间。具体说来，某个Reduce的数据输入量远远大于其他Reduce的数据输入量，造成数据倾斜，其原因是：

• Key分布不均匀；
• 业务数据本身的特性；
• 建表时考虑不周；
• 某些HQL语句本身就有数据倾斜。

Hadoop框架的特性是不怕数据大，就怕数据倾斜。Hive的执行是分阶段的，Map处理数据量的差异取决于上一个阶段的Reduce输出，所以如何将数据均匀分配到各个Reduce中，就是解决数据倾斜的根本所在。解决数据倾斜的方法包括合理设置Map个数、合并小文件、复杂文件增加Map个数、合理设置Reduce个数等。

2.1、合理设置Map个数

通常情况下，MapReduce作业会通过输入目录产生一个或者多个Map任务。主要决定因素有输入文件总个数、输入文件大小、集群设置的文件块大小等。举例如下：

• 假设输入目录下有1个文件a，大小为780MB，那么Hadoop会将该文件a分割成7个块（6个128MB的块和1个12MB的块），从而产生7个Map。

• 假设输入目录下有3个文件a、b、c，大小分别为10MB、20MB、130MB，那么Hadoop会分割成4个块（10MB、20MB、128MB、2MB），从而产生4个Map。

如果一个任务有很多小文件（远远小于块大小128MB），则每个小文件也会被当作一个块，用一个Map来完成，而一个Map启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的Map个数是受限的。针对这种情况，需要采取的解决方式是减少Map个数，而不是Map越多越好。

是不是保证每个Map处理接近128MB的文件块就高枕无忧了？答案是不一定。比如有一个127MB的文件，正常会用一个Map去完成，但这个文件只有一个或者两个字段，却有几千万条记录，如果Map处理的逻辑比较复杂，用一个Map去做，肯定比较耗时。

针对这个问题，需要采取的解决方式是增加Map个数。问题的关键是要合理设置Map的个数。根据实际情况，控制Map个数需要遵循两个原则：使大数据量利用合适的Map个数；使单个Map处理合适的数据量。

2.2、合并小文件

通常在Map输入端、Map输出端和Reduce输出端容易产生小文件，小文件过多会影响Hive的分析效率。在Map执行前合并小文件，减少Map个数。

hive.merg.mapfiles=true：合并Map输出。

hive.merge.mapredfiles=false：合并Reduce输出。

hive.merge.size.per.task=256000000：合并文件的大小。

hive.mergejob.maponly=true：如果支持CombineHiveInputFormat，则生成只有Map的任务执行Merge。

hive.merge.smallfiles.avgsize=16000000：文件的平均大小，当小于该值时，会启动一个MapReduce任务执行Merge。

参数CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式），参数HiveInputFormat没有对小文件进行合并的功能。

例如：

set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

2.3、复杂文件增加Map个数

当输入文件都很大，任务逻辑复杂，Map执行非常慢时，可以考虑增加Map个数，使得每个Map处理的数据量减少，从而提高任务的执行效率。

增加Map个数的方法：根据公式

computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128MB

调整maxSize最大值。让maxSize最大值低于Blocksize，就可以增加Map个数。

例如：

（1）执行查询

select count(*) from emp;

（2）设置最大切片值为100B

set mapreduce.input.fileinputformat.split.maxsize=100;

select count(*) from emp;

2.4、合理设置Reduce个数

Reduce个数的设定极大影响任务的执行效率。不指定Reduce个数的情况下，Hive会猜测确定一个Reduce个数。基于以下两个参数进行设定。

参数1：hive.exec.reducers.bytes.per.reducer，设置每个Reduce处理的数据量，默认值为256MB。

参数2：hive.exec.reducers.max，设置每个任务最大的Reduce个数，默认值为1009。

计算Reduce个数的公式为

N=min(参数2，总输入数据量/参数1)

即如果Reduce的输入（Map的输出）总大小不超过1GB，那么只会有一个Reduce。

• 设置Reduce个数的方法一

  • 每个Reduce处理的数据量，默认值是256MB

    set hive.exec.reducers.bytes.per.reducer=256000000;

  • 每个任务最大的Reduce个数，默认值为1009

    set hive.exec.reducers.max=1009;

• 设置Reduce个数的方法二

  在Hadoop的mapred-default.xml配置文件中修改，设置每个任务的Reduce个数：

• Reduce个数并不是越多越好

  • 过多地启动和初始化Reduce，也会消耗时间和资源。

  • 有多少个Reduce，就会有多少个输出文件，如果生成了很多个小文件，则也会出现小文件过多的问题。

  在设置Reduce个数时，需要考虑的两个原则：处理大数据量利用合适的Reduce个数；单个Reduce处理数据量大小要合适。

3、HQL优化

3.1、Group By优化

默认情况下，Map阶段同一Key数据分发给一个Reduce。并不是所有的聚合操作都需要在Reduce端完成的，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。在Map端进行部分聚合操作，效率更高但需要更多的内存。

开启Map端聚合需要设置以下参数。

hive.map.aggr：设置在Map端是否进行聚合，取值为True或False，默认值为True。

hive.groupby.mapaggr.checkinterval：在Map端设置聚合操作的条目数目，默认值为100000条。

hive.groupby.skewindata：有数据倾斜时是否进行负载均衡，取值为True或False，默认值为False。

有数据倾斜时进行负载均衡，当参数值设定为True时，生成的查询计划会有两个MapReduce作业。

在第一个MapReduce作业中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的。

第二个MapReduce作业再根据预处理的数据结果按照Group By Key分布到Reduce中，这个过程可以保证相同的Group By Key被分布到同一个Reduce中，完成最终的聚合操作。

3.2、小表、大表Join

Hive在实际的应用过程中，大部分情况都会涉及不同表的连接，例如在进行两个表的Join时，利用MapReduce的思想会消耗大量的内存，浪费磁盘的I/O空间，大幅度地影响性能。

多表连接会转换成多个MapReduce作业，每个MapReduce作业在Hive中称为Join阶段（Stage）。在每个Stage，按照Join顺序中的最后一个表应尽量是大表，因为Join前一阶段生成的数据会存在于Reduce的Buffer中，通过Join最后面的表直接从Reduce的Buffer中读取已经缓冲的中间结果数据（这个中间结果数据可能是Join顺序中前面表连接的结果的Key，数据量相对较小，内存开销就小），这样，与后面的大表进行连接时，只需要从Buffer中读取缓存的Key，与大表中的指定Key进行连接，速度会更快，也可能避免内存缓冲区溢出。

把重复关联键少的表放在Join前面做关联，可以提高Join的效率。因为不论多复杂的Hive查询，最终都要转化成MapReduce作业去执行。而MapReduce对于关联的实现，简单来说，是把关联键和标记在Join左边还是右边的标识位作为组合键（Key），把一条记录及标记在Join左边还是右边的标识位组合起来作为值（Value）。在Reduce的Shuffle阶段，按照组合键的关联键进行主排序，当关联键相同时，再按照标识位进行辅助排序。而在分区段时，只用关联键中的关联键进行分区段，这样关联键相同的记录就会放在同一个Value List中，同时保证了Join左边的表的记录在Value List前面，而Join右边的表的记录在Value List的后面。

例如，A Join B ON (A.id = B.id)，假设A表和B表都有1条id = 3的记录，那么A表这条记录的组合键是(3,0)，B表这条记录的组合键是(3,1)。排序时，可以保证A表的记录在B表的记录的前面。而在Reduce进行处理时，把id=3的记录放在同一个Value List中，形成Key = 3,Value List = [A表id=3的记录,B表id=3的记录]。

3.3、大表Join小表

• 空Key过滤

  表中出现空值或无意义值的情况很常见，比如往往会有一些数据项没有记录，一般视情况会将它置为Null，或者空字符串、-1等。如果缺失的项很多，在做Join时，这些空值就会非常集中，从而拖累进度。有时Join超时是因为某些Key对应的数据太多，而相同Key对应的数据都会发送到相同的Reduce上，从而导致内存不够。因此，若不需要空值数据，就提前用Where语句将其过滤掉。

• 空Key转换

  空值数据若需要保留，可以将空Key用随机赋值方式处理。有时某个Key为空对应的数据很多，而且必须要包含在Join的结果中，此时可以将表中Key为空的字段赋一个随机值，使得数据随机均匀地分不到不同的Reduce上。

  • 不随机分布空Null值

  • 随机分布空Null值

3.4、MapJoin

利用Hive进行Join连接操作，相对于MapReduce有两种执行方案：一种为CommonJoin，另一种为MapJoin。CommonJoin也称为ShuffleJoin，一般在两个表的大小相当，但又不是很大的情况下使用。具体流程就是在Map端进行数据的切分，一个块对应一个Map操作，然后进行Shuffle操作，把对应的块Shuffle到Reduce端去，再逐个进行联合。

MapJoin是指当连接的两个表是一个比较小的表和一个比较大的表时，会把比较小的表直接放到内存中，然后再对比较大的表进行Map操作。Join就发生在Map操作时，每扫描一个大表中的数据，就要去查看小表的数据，哪条与之相符，继而进行连接。这里的Join并不会涉及Reduce操作。

MapJoin还有一个使用场景是能够进行不等条件连接的Join操作。如果将不等条件写在Where语句中，那么MapReduce过程中会进行笛卡儿积Join，运行效率特别低。如果使用MapJoin操作，在Map的过程中就完成了不等值的Join操作，效率会高很多。

如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成CommonJoin，即在Reduce阶段完成Join，在这种情况下容易发生数据倾斜。

与MapJoin有关的参数设置如下。

hive.auto.convert.join：设置是否开启MapJoin，取值为True或False，默认值为True。

hive.mapjoin.t_small.filesize：设置小表的阈值，默认值为25MB，默认值以下认为是小表。

3.5、Count优化

在数据量大的情况下，由于Count（Distinct）操作需要用一个Reduce任务来完成，如果这个Reduce任务需要处理的数据量太大，就会导致整个作业很难完成。

一般使用Count（Distinct）时先进行Group By子查询，然后进行Count计算。这种方法的好处在于，在不同的Reduce，各自进行Count（Distinct）计算，充分发挥Hadoop的优势，然后进行Count计算。

3.6、行/列过滤优化

列过滤优化是指在进行Select查询时，只选择需要的列，如果有，尽量使用分区过滤，尽量少用Select *。

行过滤优化是指在分区查询时，当使用外关联时，如果将副表的过滤条件写在Where语句后面，那么就会先全表关联，之后再过滤。

3.7、动态分区调整优化

之前在第4章创建的分区表都是静态分区表，导入数据时必须知道分区目录。静态分区适用于使用处理时间作为分区字段，但是常常会遇到需要向分区表导入大量数据，而且导入前并不清楚归属的分区目录，这时使用动态分区可以解决以上问题。动态分区可以根据查询得到的数据动态分配到分区目录中。

动态分区可以设置部分分区字段为动态分区字段，也可以允许所有的分区字段都是动态分区字段，这时要设置参数hive.exec.dynamic.partition.mode，它的默认值是Strict，即不允许分区字段全部是动态的，必须指定至少一个分区为静态分区。

与动态分区调整优化有关的参数还有以下几个。

hive.exec.dynamic.partition：是否开启动态分区功能，默认为False，是关闭状态。使用动态分区时，该参数必须设置为True。

hive.exec.max.dynamic.partitions：在所有执行MapReduce的节点上，最多一共可以创建多少个动态分区，默认值为1000。

hive.exec.max.dynamic.partitions.pernode：在每个执行MapReduce的节点上，最多可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如，源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。

hive.exec.max.created.files：整个MapReduce作业中，最多可以创建多少个HDFS文件，默认为100000个。

hive.error.on.empty.partition：当有空分区生成时，是否抛出异常，默认值为False。

3.8、执行计划优化

Hive的Explain命令用于显示HQL查询的执行计划，可以优化业务逻辑，减少作业的数据量。

HQL查询被转化成序列阶段（这是一个有向无环图），这些阶段可能是Map/Reduce阶段，或者是Metastore或文件系统的操作，如移动和重命名的阶段。

HQL查询的执行计划可生成查询的抽象语法树，也可生成执行计划的不同阶段之间的依赖关系。

EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] select_statement

# 查看语句的执行计划explain select empno, bday, score from emp;