Hadoop的数据仓库框架——Hive & 大数据快速读写——HBase

Hadoop的数据仓库框架——Hive

一、Hive简介

1.1、Hive的简介

在 Facebook Jeff团队所构建的信息平台中，最庞大的组成部分是 Apache Hive(https://hive.apache.org/)。Hive是一个构建在 Hadoop上的数据仓库框架，是应Facebook每天产生的海量网络数据进行管理和（机器）学习的需求而产生和发展的。

什么hive

Hive本身是数据仓库。

数据仓库是为了协助分析报告，支持决策，为需要业务智能的企业提供业务流程的改进和指导，从而节省时间和成本，提高质量。它与数据库系统的区别是，数据库系统可以很好地解决事务处理，实现对数据的“增、删、改、查”操作，而数据仓库则是用来做查询分析的数据库，通常不会用来做单条数据的插入、修改和删除。

Hive作为一个数据仓库工具，非常适合数据的统计分析，它可以将数据文件组成表格并具有完整的类SQL查询功能，还可将类SQL语句自动转换成MapReduce任务来运行。因此，如果使用Hive，可以大幅提高开发效率。

和传统的数据仓库一样，Hive主要用来访问和管理数据。与传统数据仓库较大的区别是，Hive可以处理超大规模的数据，可扩展性和容错性非常强。由于Hive有类SQL 的查询语言，所以学习成本相对比较低。

hive是一个构建在Hadoop上的数据仓库工具(框架)，可以将结构化的数据文件映射成一张数据表，并可以使用类hive的方式来对这样的数据文件进行读，写以及管理（包括元数据）。这套HIVE SQL 简称HQL。hive的执行引擎可以是MR、spark、tez。

如果执行引擎是MapReduce的话，hive会将Hql翻译成MR进行数据的计算。 用户可以使用命令行工具或JDBC驱动程序来连接到hive。

为什么使用hive

因为直接使用MapReduce，需要面临以下问题:

- 人员学习成本高
- 项目周期要求太短
- MapReduce实现复杂查询逻辑开发难度大
1
2
3

Hive的优缺点

1） hive的优点

1. 学习成本低
   提供了类SQL查询语言HQL(简单，容易上手)，避免了直接写MapReduce（适合java语言不好的，hive熟练的人），减少开发人员的学习成本。
2. 可扩展性好
   为超大数据集设计了计算/扩展能力（MR作为计算引擎，HDFS作为存储系统），Hive可以自由的扩展集群的规模，一般情况下不需要重启服务。
3. 适合做离线分析处理(OLAP)
   Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合。
4. 延展性好
   Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。
5. 良好的容错性
   某个数据节点出现问题HQL仍可完成执行。
6. 统计管理
   提供了统一的元数据管理
1
2
3
4
5
6
7
8
9
10
11
12

2） hive的缺点

1. hive的HQL表达能力有限
  - 迭代式算法无法表达，比如PageRank(网页排名)
  - 数据挖掘方面，比如kmeans(k均值聚类算法)
2. hive的效率比较低
  - hive自动生成的mapreduce作业，通常情况下不够智能化
  - hive调优比较困难，粒度较粗
1
2
3
4
5
6

1.2、Hive架构和原理

1.2.1、hive的架构简介

从上图可以看出，Hive的体系结构分为以下几部分:

1. 用户连接接口
	CLI：是指Shell命令行
	JDBC/ODBC:是指Hive的java实现，与传统数据库JDBC类似。
	WebUI:是指可通过浏览器访问Hive。
2. thriftserver：
	hive的可选组件,此组件是一个软件框架服务，允许客户端使用包括Java、C++、Ruby和其他很多种语言，通过编程的方式远程访问Hive。
3. 元数据
	Hive将元数据存储在数据库中，如mysql、derby。Hive中的元数据包括(表名、表所属的数据库名、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等）
4. 驱动器(Driver)
  - 解析器(SQLParser):
 	 将HQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。	
  - 编译器(Compiler)：
  	对hql语句进行词法、语法、语义的编译(需要跟元数据关联)，编译完成后会生成一个执行计划。hive上就是编译成mapreduce的job。
  - 优化器(Optimizer)：
  	将执行计划进行优化，减少不必要的列、使用分区、使用索引等。优化job。
  - 执行器(Executer):
  	将优化后的执行计划提交给hadoop的yarn上执行。提交job。
5. hadoop
	Jobtracker是hadoop1.x中的组件，它的功能相当于:Resourcemanager+AppMaster
	TaskTracker相当于：Nodemanager  +  yarnchild
 
	Hive的数据存储在HDFS中，大部分的查询、计算由MapReduce完成
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

注意：

- 包含*的全表查询，比如select * from table 不会生成MapRedcue任务
- 包含*的limit查询，比如select * from table limit 3 不会生成MapRedcue任务
1
2

1.2.2、hive的工作原理

1. 用户提交查询任务给Driver。
2. 编译器Compiler获得用户的任务计划。
3. 编译器Compiler根据用户任务从MetaStore中得到所需要的Hive的元数据信息。
4. 编译器Compiler得到元数据信息，对任务进行编译，先将HQL转换为抽象语法树，接着把抽象语法树转换成查询语句块，将查询语句块转化为逻辑的查询计划。
5. 把最终的计划提交给Driver。
	到此为止，查询解析和编译完成。
6. Driver将计划Plan提交到Execution Engine，获得元数据信息，接着提交到JobTracker或者Source Manager运行该任务，该任务会直接从HDFS中读取文件并进行相应的操作。
7. 取得并返回执行结果
1
2
3
4
5
6
7
8

1.2.3、hive和hadoop的关系

- hive本身其实没有多少功能，hive就相当于在hadoop上面包了一个壳子，就是对hadoop进行了一次封装。
- hive的存储是基于hdfs/hbase的，hive的计算是基于mapreduce。
参考下图：
1
2
3

1.3、Hive与传统型数据库的区别

1. Hive采用了类SQL的查询语言HQL，因此很容易将Hive理解为数据库。其实从结构上来看，Hive和数据库除了拥有类似的查询语言，再无类似之处。
2. 数据库可以用在OLTP的应用中，但是Hive是为数据仓库而设计的，清楚这一点，有助于从应用角度理解Hive的特性。
3. Hive不适合用于联机事务处理(OLTP)，也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。Hive 的特点是可伸缩（在Hadoop 的集群上动态的添加设备），可扩展、容错、输入格式的松散耦合。Hive 的入口是DRIVER ，执行的SQL语句首先提交到DRIVER驱动，然后调COMPILER解释驱动，最终解释成MapReduce 任务执行，最后将结果返回。
4. MapReduce 开发人员可以把自己写的 Mapper 和 Reducer 作为插件支持 Hive 做更复杂的数据分析。 它与关系型数据库的 SQL 略有不同，但支持了绝大多数的语句（如 DDL、DML）以及常见的聚合函数、连接查询、条件查询等操作。

Hive和数据库的比较如下表：

详情解析

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

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

3、数据格式：Hive中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三个属性：列分隔符(通常为空格、“\t”、“\x001”)、行分隔符(“\n”)以及读取文件数据的方法(Hive中默认有三个文件格式TextFile、SequenceFile以及RCFile)。由于在加载数据的过程中，不需要从用户数据格式到Hive定义的数据格式的转换，因此，Hive在加载的过程中不会对数据本身进行任何修改，而只是将数据内容复制或者移动到相应的HDFS目录中。而在数据库中，不同的数据库有不同的存储引擎，定义了自己的数据格式。所有数据都会按照一定的组织存储，因此，数据库加载数据的过程会比较耗时。

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

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

6、执行：Hive中大多数查询的执行是通过Hadoop提供的MapReduce来实现的(类似select * from tbl的查询不需要MapReduce)。而数据库通常有自己的执行引擎。

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

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

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

Hive和Mysql的比较

- mysql用自己的存储存储引擎，hive使用的hdfs来存储。
- mysql使用自己的执行引擎，而hive使用的是mapreduce来执行。
- mysql使用环境几乎没有限制，hive是基于hadoop的。
- mysql的低延迟，hive是高延迟。
- mysql的handle的数据量较小，而hive的能handle数据量较大。
- mysql的可扩展性较低，而hive的扩展性较高。
- mysql的数据存储格式要求严格，而hive对数据格式不做严格要求。
- mysql可以允许局部数据插入、更新、删除等，而hive不支持局部数据的操作。
1
2
3
4
5
6
7
8

二、Hive的安装部署

Hive常用的安装分三种(注意：Hive会自动监测Hadoop的环境变量，如有就必须启动Hadoop)

先从本地上传Hive安装文件apache-hive-2.1.1-bin.tar.gz到/root/soft

2.1、内嵌模式：

使用hive自带默认元数据库derby来进行存储，通常用于测试

1. 优点：使用简单，不用进行配置
2. 缺点：只支持单session。

2.1.1 安装步骤：

1. 解压hive并配置环境变量

[root@master local]# tar -zxvf apache-hive-2.1.1-bin.tar.gz -C /usr/local
#修改hive安装路径名,方便以后使用
cd /usr/local
[root@master local]# mv apache-hive-2.1.1-bin/ hive


[root@master local]# vim /etc/profile
# 添加如下内容:
export HIVE_HOME=/usr/local/hive
export PATH=$HIVE_HOME/bin:$PATH

#让profile生效
[root@master local]# source /etc/profile
1
2
3
4
5
6
7
8
9
10
11
12
13

2. 配置hive-env.sh

cd /usr/local/hive/conf/
1

如果不存在,就用hive.env.sh.template复制一个

export HIVE_CONF_DIR=/usr/local/hive/conf
export JAVA_HOME=/usr/local/jdk
export HADOOP_HOME=/usr/local/hadoop
export HIVE_AUX_JARS_PATH=/usr/local/hive/lib
1
2
3
4

3. 配置hive-site.xml

hive2.1.1中默认是没有hive-site.xml,可以把conf/hive-default.xml.template拷贝过来使用

[root@master conf]# cp hive-default.xml.template hive-site.xml
[root@master conf]# vim hive-site.xml

把hive-site.xml 中所有包含 ${system:java.io.tmpdir}替换成/usr/local/hive/iotmp.
%s#${system:java.io.tmpdir}#/usr/local/hive/iotmp#g

如果系统默认没有指定系统用户名,那么要把配置${system:user.name}替换成当前用户名root
%s/${system:user.name}/root/g
1
2
3
4
5
6
7
8

扩展：hive-site.xml中有两个重要的配置说明

    <!-- 该参数主要指定Hive的数据存储目录  -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive/warehouse</value>
        <description>location of default database for the warehouse</description>
    </property>

    <!-- 该参数主要指定Hive的临时文件存储目录  -->
    <property>
        <name>hive.exec.scratchdir</name>
        <value>/tmp/hive</value>
        <description>HDFS root scratch dir for Hive jobs which gets created with write all (733) permission. For each connecting user, an HDFS scratch dir: ${hive.exec.scratchdir}/&lt;username&gt; is created, with ${hive.scratch.dir.permission}.
        </description>
    </property>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在linux中新建上面两个目录,并且进行权限赋值（可选操作，hive会自动创建）

[root@master hive] # hdfs dfs -mkdir -p /user/hive/warehouse 
[root@master hive] # hdfs dfs -mkdir -p /tmp/hive/ 
[root@master hive] # hdfs dfs -chmod 750 /user/hive/warehouse 
[root@master hive] # hdfs dfs -chmod 777 /tmp/hive
1
2
3
4

4. 启动hadoop

[root@master hadoop]# start-dfs.sh
[root@master hadoop]# start-yarn.sh
1
2

5. 初始化hive

[root@master hive]# schematool --initSchema -dbType derby
1

6. 启动hive

(注：启动之前要启动hdfs sbin/start-dfs.sh 和yarn sbin/start-yarn.sh )

[root@master hive]# bin/hive

#进入后可以执行下面命令进行操作：
hive>show dataases;  #查看数据库
hive>show tables;   #查看表
1
2
3
4
5

简单hive演示执行

# 创建表
hive> create table dog(id int,name string);
hive> select * from dog;
hive> insert into dog values(1,"wangcai");
hive> desc dog; #查看表结构
hive> quit # 退出
1
2
3
4
5
6

初始化报错

Exception in thread “main“ java.lang.NoSuchMethodError: com.google.common.base.
1

hadoop和hive的两个guava.jar版本不一致

解决方案：

第一步：删除hive里的guava.jar
rm guava-19.0.jar 

第二步：把hadoop里的复制到hive里
cd hadoop/share/hadoop/common/lib
cp -r guava-27.0-jre.jar /usr/local/hive/lib
1
2
3
4
5
6

Exception in thread "main" java.lang.RuntimeException: com.ctc.wstx.exc.WstxParsingException: Illegal character entity: expansion character (code 0x8
1

hive-site.xml配置文件中，3215行有特殊字符

进入hive-site.xml文件，跳转到对应行，删除里面的******特殊字符即可

2.2、本地模式

使用mysql替换derby进行元数据的存储，hive的相关进程都是在同一台机器上，即本地模式。mysql因为是独立的进程，所以mysql可以和hive在同一机器上，也可以在其他机器上。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v8spSx81-1655536742582)(Hive.assets/20191021005723.jpg)]

说明：

通常使用关系型数据库来进行元数据存储（mysql、oracle等执行带jdbc驱动的数据库）

• 优点：支持多session

• 缺点：需要配置、还需要安装mysql等关系型数据库

2.2.1 配置安装mysql

安装MySql服务器

mysql安装的步骤介绍

# 环境准备
# CentOS7中，系统默认采用的数据库是mariadb，这个数据库与MySQL冲突！
# 因此，在安装MySQL之前，需要先将其卸载！
[root@master ~]# rpm -qa | grep mariadb			# 查询是否已经安装了mariadb
mariadb-libs-5.5.64-1.el7.x86_64								# 查询结果。如果没有这个结果，说明没有安装。

# 强制卸载mariadb
# --nodeps: 强制卸载，RPM卸载程序的时候，如果这个程序被其他的程序依赖，是无法卸载的。
#           此时，就需要使用--nodeps，忽略依赖，强制卸载。
# 下面的卸载命令中，卸载的包是上方查询到的包
[root@master ~]# rpm -e mariadb-libs-5.5.64-1.el7.x86_64 --nodeps

# 安装MySQL
# 安装MySQL, 其实就需要安装 mysql-community-server, 但是它依赖其他的包
[root@master mysql]# rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
[root@master mysql]# rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
[root@master mysql]# rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
[root@master mysql]# yum install -y net-tools
[root@master mysql]# rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

# 开启MySQL服务
# 查看MySQL服务的运行状态
[root@master ~]# systemctl status mysqld
# 如果MySQL服务没有开启，则开启
[root@master ~]# systemctl start mysqld

# 登录到MySQL
# 在第一次开启MySQL服务的时候，会自动生成一个随机的密码
[root@master ~]# grep password /var/log/mysqld.log			# 到mysqld.log文件中查找password
2020-12-16T07:47:14.117739Z 1 [Note] A temporary password is generated for root@localhost: pVLJs6&o(QQe
# 使用这个随机密码登录到MySQL
[root@master ~]# mysql -u root -p
pVLJs6&o(QQe		# 这里用自己的密码登录

# 修改MySQL安全等级
# 1. 修改MySQL的密码策略（安全等级）
#    MySQL默认的密码安全等级有点高，在设置密码的时候，必须同时包含大小写字母、数字、特殊字符，以及对位数有要求
show variables like '%validate_password%';				# 查看密码策略
set global validate_password_policy=LOW;				# 修改密码策略等级为LOW
set global validate_password_length=4;					# 密码的最小长度
set global validate_password_mixed_case_count=0;	  	# 设置密码中至少要包含0个大写字母和小写字母
set global validate_password_number_count=0;			# 设置密码中至少要包含0个数字
set global validate_password_special_char_count=0;		# 设置密码中至少要包含0个特殊字符

# 2. 修改密码
alter user root@localhost identified by '123456';

# 3. 远程授权
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

2.2.2、配置hive的环境

1、解压并配置环境变量

2、配置hive的配置文件

cp hive-env.sh.template hive-env.sh

vim hive-env.sh（可以配置jdk、hive的conf路径）
1
2
3

3、在Hive的conf配置hive的自定义配置文件

vim hive-site.xml:添加如下内容
1

**注意：**前三步和内嵌模式一样

4、找到下面四个属性进行修改对应的值。

<!--配置mysql的连接字符串-->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://node2:3306/hive?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<!--配置mysql的连接驱动-->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<!--配置登录mysql的用户-->
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
<description>username to use against metastore database</description>
</property>
<!--配置登录mysql的密码-->
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
<description>password to use against metastore database</description>
</property>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

注意：hive的元数据在mysql库里创建的数据库hive的编码最好设置成latin1.

show variables like 'character%';
1

5、将mysql的驱动包mysql-connector-java-5.1.28-bin.jar上传到$HIVE_HOME/lib下(注意:驱动是jar结尾,不是tar结尾)

6、执行执行先要初始化数据库

[root@master hive]# bin/schematool -initSchema -dbType mysql
1

7、启动hive

[root@master hive]# bin/hive
1

说明： hive命令会默认启动元数据服务项（metastore）

2.3、远程模式

2.3.1 简介

将hive中的相关进程比如hiveserver2或者metastore这样的进程单独开启，使用客户端工具或者命令行进行远程连接这样的服务，即远程模式。客户端可以在任何机器上，只要连接到这个server，就可以进行操作。客户端可以不需要密码。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZfjXkBG3-1655536742584)(Hive.assets/20191021015748.jpg)]

2.3.2、服务端的配置

1) 修改 hive-site.xml

<!--hive仓库在hdfs的位置-->
<property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
    <description>location of default database for the warehouse</description>
</property>

<!-- 该参数主要指定Hive的临时文件存储目录  -->
<property>
    <name>hive.exec.scratchdir</name>
    <value>/tmp/hive</value>
</property>

<!--连接mysql的url地址-->
<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://node2:3306/hive?createDatabaseIfNotExist=true&amp;characterEncoding=latin1</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.exec.local.scratchdir</name>
    <value>/usr/local/hive/iotmp/root</value>
</property>
<!--如果启用了日志功能，则存储操作日志的顶级目录-->
<property>
    <name>hive.server2.logging.operation.log.location</name>
    <value>/usr/local/hive/iotmp/root/operation_logs</value>
</property>
<!--Hive运行时结构化日志文件的位置-->
<property>
    <name>hive.querylog.location</name>
    <value>/usr/local/hive/iotmp/root</value>
</property>
<!--用于在远程文件系统中添加资源的临时本地目录-->
<property>
    <name>hive.downloaded.resources.dir</name>
    <value>/usr/local/hive/iotmp/${hive.session.id}_resources</value>
</property>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

 说明：使用远程模式，需要在hadoop的core-site.xml文件中添加一下属性

<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
</property>
1
2
3
4
5
6
7
8

2.3.3、hive的两种服务说明

第一种服务：hiveserver2

1. 该服务端口号默认是10000
2. 可以单独启动此服务进程，供远程客户端连接；此服务内置metastore服务。
3. 启动方式：
    方法1：
        直接调用hiveserver2。会进入监听状态不退出。
    方法2：
        hive --service hiveserver2 &  # 进入后台启动
    方法3：
        hive --service hiveserver2 >/dev/null 2>&1 &; #信息送入黑洞。
1
2
3
4
5
6
7
8
9

第二种服务：metastore

1. 此服务才是真正连接元数据库的服务进程
2. 也可以让远程客户端连接
3. 启动方式：
	方法1：
		- hive --service metastore &
	方法2：
    	- hive --service metastore 2>&1 >/dev/null &; #信息送入黑洞。
1
2
3
4
5
6
7

2.3.4、客户端连接hiveserver2服务

说明：

1. 连接此服务的hive客户端，只需要配好环境变量即可
2. 只能使用hive中的beeline连接工具进行连接此服务，beeline是hive的一个轻量级的连接客户端工具。
1
2

连接方式：

方式1：
   	step1. beeline 回车
   	step2. !connect jdbc:hive2://ip:10000 回车
   	step3. 输入用户名 回车
   	step4. 输入密码 回车
方法2(直连)：
	1. beeline -u jdbc:hive2://ip:10000 -n 用户名
解析: 
	hive2，是hive的协议名称
	ip:  hiveserver2服务所在的主机IP。
	10000，是hiveserver2的端口号d
1
2
3
4
5
6
7
8
9
10
11

2.3.5、客户端连接metastore服务

注意：想要连接metastore服务的客户端必须配置如下属性和属性值

修改hive-site.xml

<property>
	<name>hive.metastore.uris</name> 
	<value>thrift://ip:9083</value>
</property>

解析：thrift:是协议名称
	 ip为metastore服务所在的主机ip地址
     9083是默认端口号
1
2
3
4
5
6
7
8

三、Hive基本操作-库、表

3.1、规则语法

大小写规则:

1. hive的数据库名、表名都不区分大小写
2. 建议关键字大写

命名规则：

1. 名字不能使用数字开头
2. 不能使用关键字
3. 尽量不使用特殊符号

3.2、库操作语法

3.2.1 创建数据库

创建数据库的本质就是在hive的参数${hive.metastore.warehouse.dir}对应的目录下，创建一个新的目录，此目录的名称为： 库名.db。

注意：在创建库或者表时除了创建目录外，还会在mysql中(元数据库)，添加元数据（描述信息）

hive> create database zoo;
hive> create database if not exists zoo;
hive> create database if not exists qfdb comment 'this is a database of qianfeng';
1
2
3

hive有一个默认的数据库default，如果不明确的说明要使用哪个库，则使用默认数据库。

3.2.2 查看所有数据库：实际就是从元数据库中获取对应的元数据

show databases;
1

3.2.3 切换数据库

use mydb;
1

3.2.4 查看数据库信息

语法1：desc database databaseName;
语法2：desc database extended databaseName;
语法3：describe database extended databaseName;
1
2
3

3.2.5 删除数据库

语法1：drop database databasename;         	  	# 这个只能删除空库
语法2：drop database databasename cascade;    		# 如果不是空库，则可以加cascade强制删除
1
2

3.3、表操作语法

3.3.1、数据类型

Hive的数据类型分为基本数据类型和复杂数据类型，下面是基本数据类型

其中加粗体是重点要掌握的类型

3.3.2、创建表

创建表的本质其实就是在对应的数据库目录下面创建一个子目录，目录名为表名。数据文件就存在这个目录下。

语法1: 
	create table t_user(id int,name string);  

语法2：使用库.表的形式
	create table mydb.t_user(id int,name string);

语法3：指定分隔规则形式
create table if not exists t1(
    uname string comment 'this is name',
    chinese int,
    math int,
    english int
)
comment 'this is my table'
row format delimited   
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

create table if not exists emp(
    eno int,
    ename string,
    job string, 
    mgr int,
    hiredate int,
    salary int,
    comm int,
    deptno int
)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.3.3、查看当前表空间中的所有表名

语法：show tables; 
# 查看另外一个数据库中的表
show tables in zoo;
1
2
3

3.3.4 、查看表结构

desc tableName
desc extended tableName;
describe extended tableName;
1
2
3

3.3.5、修改表结构

- 修改表名
	alter table oldTableName rename to newTableName;

- 修改列名：	和修改字段类型是同一个语法
	alter table tableName change column oldName newName colType;
	alter table tableName change column colName colName colType;

- 修改列的位置:  注意，2.x版本后，必须是相同类型进行移动位置。
	alter table tableName change column colName colName colType after colName1;   
	alter table tableName change column colName colName colType first;

- 增加字段：
	alter table tableName add columns (sex int,...);

- 删除字段：	#注意，2.x版本后，注意类型的问题，替换操作，其实涉及到位置的移动问题。
	alter table tableName replace columns(
        id int,
        name int,
        size int,
        pic string
    );
	注意：实际上是保留小括号内的字段。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3.3.6、删除表

drop table tableName;
1

3.4、数据导入

[root@master hive]# mkdir /hivedata
[root@master hive]# cd /hivedata
[root@master hive]# vim user.txt
-- 加入下面的数据
1,廉德枫
2,刘浩
3,王鑫
4,司翔
1
2
3
4
5
6
7
8

create table t_user(
    id int,
    name string
)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;
1
2
3
4
5
6
7
8

加载数据到Hive，一般分为两种:

• 一种是从本地Linux上加载到Hive中
• 另外一种是从HDFS加载到Hive中

**方法1：**使用hdfs dfs -put将本地文件上传到表目录下

hdfs dfs -put ./user.txt /user/hive/warehouse/mydb1.db/t_user
1

**方法2：**在hive中使用load 命令

load data [local] inpath '文件路径' [overwrite] into table 表名

加载数据时：
1. 最好是写绝对路径，从根开始写。
2. 写相对路径也是可以的，但是一定要记住你登录hive时的位置，从当前位置写相对路径
3. ~在hive中，是相对路径的写法
4. 使用benline工具进行远程登录（客户端与服务端不在同一台机器）时，使用以下语句时：
	load data local inpath '文件路径' [overwrite] into table 表名
	会有一个大坑：local是指服务端的文件系统。
1
2
3
4
5
6
7
8
9

**方法3：**从另外一张表(也可称之为备份表)中动态加载数据

insert into table tableName2 select [.....] from tableName1;


扩展内容：向多张表中插入数据的语法
    from tableName1
    insert into tableName2 select * where 条件
    insert into tableName3 select * where 条件
    .....


create table u2(
    id int,
    name string
)
row format delimited
fields terminated by ',';

insert into table u2 select id,name from u1;

create table u3(
    id int,
    name string
)
row format delimited
fields terminated by ',';
create table u4(
    id int,
    name string
)
row format delimited
fields terminated by ',';

from u2
insert into u3 select *
insert into u4 select id,name;

create table u8(
    id int,
    name string
)
row format delimited
fields terminated by ',';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

注意： tableName2表中的字段个数必须和tableName1表中查询出来的个数相同

**方法4：**克隆表数据

- create table if not exists tableName2 as select [....] from tableName1;
- create table if not exists tableName2 like tableName1 location 'tableName1的存储目录的路径'     # 新表不会产生自己的表目录，因为用的是别的表的路径

扩展内容：只复制表结构
create table if not exists tableName2 like tableName1;
1
2
3
4
5

加载数据的本质：

1. 如果数据在本地，加载数据的本质就是将数据copy到hdfs上的表目录下。
2. 如果数据在hdfs上，加载数据的本质是将数据移动到hdfs的表目录下。

注意：hive使用的是严格的读时模式：加载数据时不检查数据的完整性，读时发现数据不对则使用NULL来代替。

而mysql使用的是写时模式:在写入数据时就进行检查

3.5、案例演示

CREATE TABLE flow(
    id             string COMMENT 'this is id column',
    phonenumber     string,
    mac             string,
    ip               string,
    url              string,
    urltype          string,
    uppacket		 int,
    downpacket       int,
    upflow            int,
    downflow         int,
    issuccess    int
)
COMMENT 'this is log table'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
stored as textfile;

加载数据：
load data local inpath './data/HTTP_20130313143750.dat' into table flow;

1、统计每个电话号码的总流量(M)
select l.phonenumber,
round(sum(l.upflow + l.downflow) / 1024.0,2) as total
from flow l
group by l.phonenumber
;

2、第二个需求，求访问次数排名前3的url：
select l.url url,
count(l.url) as urlcount
from flow l
group by l.url
order by urlcount desc
limit 3
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

3.6、数据导出

3.6.1、hive数据导出分类

1. 从hive表中导出本地文件系统中(目录、文件)
2. 从hive表中导出hdfs文件系统中
3. hive表中导出到其它hive表中

3.6.2、导出到目录下

-- 1. 导出数据到本地文件系统的目录下
insert overwrite local directory '/root/out/00'
select * from student;

-- 2. 导出数据到hdfs的目录下
insert overwrite directory '/root/out/01'
select * from student;

-- 导出的文件中字段默认不分隔。

-- 3. 修改导出后的列与列之间的格式：
insert overwrite local directory '/root/out/01'
row format delimited fields terminated by ','
select * from student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.6.3、直接导入到本地文件系统的文件中：

[root@master ~]# hive -e 'select * from exercise.student' >> /root/out/02;
-- 导出的文件中字段分隔符默认是\t
1
2

四、表类型详解

4.1、表分类

在Hive中，表类型主要分为两种,

第一种：内部表

• 也叫管理表
• 表目录会创建在集群上的 {hive.metastore.warehouse.dir} 下的相应的库对应的目录中。
• 默认创建的表就是内部表

第二种：外部表

• 外部表需要使用关键字"external"，
• 外部表会根据创建表时LOCATION指定的路径来创建目录，
• 如果没有指定LOCATION，则位置跟内部表相同,一般使用的是第三方提供的或者公用的数据。
• 建表语法：必须指定关键字external。

create external table tableName(id int,name string) [location 'path'];

create external table t_user7(
    id int,
    name string
)
row format delimited
fields terminated by ','
location '/publicData'
1
2
3
4
5
6
7
8
9

4.2、内部表和外部表转换

内部表转外部表

alter table tableName set tblproperties('EXTERNAL'='TRUE');
注意：内部表转外部表，true一定要大写;
1
2

外部表转内部表

alter table tableName set tblproperties('EXTERNAL'='false');
说明：false不区分大小
1
2

4.3、两者之间区别

1) 内部表和外部表在创建时的差别

就差两个关键字，EXTERNAL 和 LOCATION

举例：

- 内部表 -- CRAATE TABLE T_INNER(ID INT);
- 外部表 -- CREATE EXTERNAL TABLE T_OUTER(ID  INT) LOCATION 'HDFS:///AA/BB/XX'; 
1
2

2) Hive表创建时要做的两件事：

1. 在hdfs下创建表目录

2. 在元数据库mysql创建相应表的描述数据（元数据）

3) drop时有不同的特性：

1、drop时，元数据都会被清除
2、drop时，内部表的表目录会被删除，但是外部表的表目录不会被删除。

4) 使用场景

内部表: 平时用来测试或者少量数据,并且自己可以随时修改删除数据.

外部表：使用后数据不想被删除的情况使用外部表（推荐使用）所以，整个数据仓库的最底层的表使用外部表

五、Hive Shell技巧

5.1、只执行一次Hive命令

通过shell的参数 -e 可以执行一次就运行完的命令

[root@master hive]#  hive -e "select * from cat"
1

小技巧2：可以通过外部命令快速查询某个变量值:

hive -S -e “set” |grep cli.print

-S 是静默模式， 会省略掉多余的输出

5.2、单独执行一个hive文件

通过参数-f +file文件名就可以，经常用在以后的hive文件单独执行,导入数据场景中

[root@master hive]#  hive -f /path/cat.hive
1

小技巧：在hive的shell内部，可以通过source命令来执行一个hive

5.3、执行Linux命令

在Hive的shell中 加上前缀! 最后以分号；结尾,可以执行linux的命令

hive> !pwd;
1

5.4、执行HDFS命令

用户可以在Hive的shell中执行HDFS的DFS命令，不用敲入前缀hdfs或者hadoop

hive> dfs -ls /tmp
1

5.5、使用历史命令和自动补全

在Hive的Shell操作中可以使用上下箭头查看历史记录

如果忘记了命令可以用tab键进行补全

5.6、显示当前库：

下面是通过配置文件hive-site.xml显示

<property>
    <name>hive.cli.print.current.db</name>
    <value>false</value>
    <description>Whether to include the current database in the Hive prompt.</description>
</property>
持久化设置
1
2
3
4
5
6

5.7、当前session里设置该参数：

hive> set hive.cli.print.current.db=true; # 短暂设置，退出后失效
1

5.8、查看当前参数设置的值：

小技巧1：可以在shell中输入set命令，可以看到hive已经设定好的参数

 hive> set hive.cli.print.current.db;
1

5.9、local模式

hive.exec.mode.local.auto=false (建议打开) 
hive.exec.mode.local.auto.inputbytes.max=134217728
hive.exec.mode.local.auto.input.files.max=4
1
2
3

六、Hive基本查询语法

6.1、基本使用规则

6.1.1、基本查询语句组成

select ..
from ..
	join [tableName] on ..
	where ..
	group by ..
	having ..
	order by ..
	sort by ..
	limit ..
union | union all ...
1
2
3
4
5
6
7
8
9
10

6.1.2、执行顺序

第一步: FROM <left_table>
第二步: ON <join_condition>
第三步: <join_type> JOIN <right_table>
第四步: WHERE <where_condition>
第五步: GROUP BY <group_by_list>
第六步: HAVING <having_condition>
第七步: SELECT
第八步: DISTINCT <select_list>
第九步: ORDER BY <order_by_condition>
第十步: LIMIT <limit_number>

标准hive语句的一些规则：
-1. 列别名的使用，必须完全符合执行顺序，不能提前使用。（mysql除外）
-2. 在分组查询时，select子句中只能含有分组字段和聚合函数，不能有其他普通字段。(mysql除外)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

6.1.3、查询原则

1. 尽量不使用子查询、尽量不使用in 或者not in (可以使用 [not] exists替代)
2. 尽量避免join连接查询，但是通常避免不了
3. 查询永远是小表驱动大表（小表作为驱动表）
   • 注意：内连接时，默认是左表是驱动表，因此左表一定要是小表。
     • 外连接看需求而定。

6.2、常用子句回顾

6.2.1、where语句特点

where后不能使用聚合函数，可以使用子查询，也可以是普通函数。
条件可以是：
1. 关系表达式： =, >,>=,<,<=,!=,<>
2. 连接符号：  or，and, between .. and ..
3. 模糊查询： like   
			 %:通配符
			 _:占位符
4. [not] in
    >all(set)  >any();

注意事项：在hive的where中如果使用了子查询作为条件，等号“=”不好使，需要使用[not] in.
		换句话说，即使子查询返回的是唯一的一个值，也是集合形式。
1
2
3
4
5
6
7
8
9
10
11
12

6.2.2、group by语句特点

group by: 分组，通常和聚合函数搭配使用

查询的字段要么出现在group by 后面，要么出现在聚合函数里面

聚合函数：count(),sum(),max(),min(),avg()

count的执行
1. 执行效果上：
	- count(*)包括了所有的列，相当于行数，在统计结果的时候不会忽略null值
	- count(1)包括了所有列，用1代表行，在统计结果的时候也不会忽略null值
	- count(列名)只包括列名那一列，在统计结果时，会忽略null值

2.执行效率上：
	- 列名为主键，count(列名)会比count(1)快
	- 列名不为主键，count(1)会比count(列名)快
	- 如果表中有多个列并且没有主键，count（1）的效率高于count(*)
	- 如果有主键count(主键)效率是最高的
	- 如果表中只有一个字段count(*)效率最高
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

6.2.3、having子句特点

• 对分组以后的结果集进行过滤。可以使用聚合函数。

6.2.4、order by子句

对查询的数据进行排序。
desc 降序
asc	 升序

语法：
order by colName [desc|asc][,colName [desc|asc]]
1
2
3
4
5
6

6.2.5、limit语句特点

limit ： 从结果集中取数据的条数
将set hive.limit.optimize.enable=true 时，limit限制数据时就不会全盘扫描，而是根据限制的数量进行抽样。

同时还有两个配置项需要注意：
hive.limit.row.max.size        这个是控制最大的抽样数量
hive.limit.optimize.limit.file 这个是抽样的最大文件数量


注意：limit 在mysql中 可以有两个参数 limit [m,] n
	       在hive中，只能有一个参数 limit n;  查询前n条。
	  一般情况下，在使用limit时，都会先order by排序。     
1
2
3
4
5
6
7
8
9
10
11

6.2.6、union | union all

union all：将两个或者多个查询的结果集合并到一起。不去重
union：将两个或者多个查询的结果集合并到一起，去重合并后的数据并排序
union语句字段的个数要求相同，字段的顺序要求相同。
1
2
3

6.3、join连接

6.3.1、join知识点回顾

有的业务所需要的数据，不是在一张表中，通常会存在多张表中，而这些表中通常应该会存在"有关系"的字段。多表查询时，使用关联字段"连接"（join）在一起，组合成一个新的数据集，就是连接查询。

连接查询操作分为两大类：内连接和外连接，而外连接有细分为三种类型。参考下图

1. 内连接:  [inner] join
2. 外连接 (outer join)：（引出一个驱动表的概念：驱动表里的数据全部显示)
  - 左外连接:left [outer] join, 左表是驱动表
  - 右外连接:right [outer] join, 右表是驱动表
  - 全外连接:full [outer] join, hive支持，mysql不支持.两张表里的数据全部显示出来
3. 注意： join连接只支持等值连接  
1
2
3
4
5
6

需要大家注意的是，两张表的关联字段的值往往是不一致的。比如，表 A 包含张三和李四，表 B 包含李四和王五，匹配的只有李四这一条记录。从上图很容易看出，一共有四种处理方式和结果。下图就是四种连接的图示，这张图比上面的维恩图更易懂，也更准确。

上图中，表 A 的记录是 123，表 B 的记录是 ABC，颜色表示匹配关系。返回结果中，如果另一张表没有匹配的记录，则用 null 填充。

笛卡尔积

指的是表 A 和表 B 不存在关联字段，这时表 A（共有 n 条记录）与表 B （共有 m 条记录）连接后，会产生一张包含 n * m 条记录(笛卡尔积)的新表（见下图）。

案例演示：

准备数据
u1文件中的数据如下：
1,a
2,b
3,c
4,d
7,y
8,u

u2文件中的数据如下：
2,bb
3,cc
7,yy
9,pp

create table if not exists u1(
    id int,
    name string
)
row format delimited 
fields terminated by ',';

create table if not exists u2(
    id int,
    name string
)
row format delimited fields terminated by ',';

load data local inpath './data/u1.txt' into table u1;
load data local inpath './data/u2.txt' into table u2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

6.3.2 、left semi join

在hive中，有一种专有的join操作，left semi join，我们称之为半开连接。它是left join的一种优化形式，只能查询左表的信息，主要用于解决hive中左表的数据是否存在的问题。相当于exists关键字的用法。

先回顾exists关键字的用法：exists关键字：满足条件返回true，不满足条件返回false

|empno|ename|job|mgr|hiredate|sal|comm|deptno|

练习:  
查询有领导的员工信息
	  select * from emp where mgr is not null
	  select * from emp A where exists (select 1 from emp B where B.empno = A.mgr )	
	  select * from emp A left semi join emp B where A.mgr = B.empno;
	  
查询有下属的员工信息
      select * from emp A where exists (select 1 from emp B where B.mgr = A.empno )
      
查看有部门的所有员工的信息
      select * from emp A where exists (select 1 from dept B where B.deptno = A.deptno )
1
2
3
4
5
6
7
8
9
10
11
12
13

left semi join的写法。

-- 左外连接，左表中的数据全部返回
select * from u1 left join u2 on u1.id =u2.id;
select * from u1 left outer join u2 on u1.id =u2.id;
-- 左半开连接，只显示左表中满足条件的数据。和exists的逻辑是相同的
select * from u1 left semi join u2 on u1.id =u2.id;
-- exists的写法
select * from u1 where exists (select 1 from u2 where u2.id =u1.id);

--验证left semi join 是否可以显示第二张表的信息：错误写法。
select A.*, B.* from u1 A left semi join u2 B on A.id =B.id;
1
2
3
4
5
6
7
8
9
10

注意： hive中不支持right semi join

6.4、hive日志

Hive中的日志分为两种，一种是系统日志，记录了hive的运行情况，错误状况。Job 日志，记录了Hive 中job的执行的历史过程。

在hive-log4j2.properties记录着日志文件的存储位置

property.hive.log.dir = ${sys:java.io.tmpdir}/${sys:user.name}
property.hive.log.file = hive.log
1
2

6.5、Hql的三种运行方式

6.5.1、在hive的client中运行

1. 本地模式下使用hive进行client
2. 远程模式下使用beeline工具进入client
3. 远程模式下使用hive进入client
1
2
3

6.5.2 、在linux命令行中执行hql

[root@master ~]$ hive -e 'hql query'

案例：
[root@master ~]$ hive --database mydb -e 'select * from studentinfo';
[root@master ~]$ hive --database exercise --hivevar ls=2 --hiveconf tn=student -e 'select * from ${hiveconf:tn} limit ${hivevar:ls}';

参数选项说明: 
-e	用于执行hql语句
-f  用于执行hive脚本文件
-S  静音模式，不显示mapreduce执行过程
-i  启动hive时初始化一个文件
1
2
3
4
5
6
7
8
9
10
11

6.5.3 、在命令行中执行hql文件

[root@master ~]$ hive -f 'hive script'

案例：
[root@master ~]$ vim hfile.hive
use exercise;
select count(*) from student where s_id<5;
[root@master ~]$ hive -f ./hfile.hive或者使用静音模式
[root@master ~]$ hive -S -f ./hfile.hive
1
2
3
4
5
6
7
8

七、数据类型的讲解

在hive中，数据类型分为基础数据类型和复杂数据类型两大类型

7.1、数据类型

示例:

create table if not exists datatype1(
    id1 tinyint,
    id2 smallint,
    id3 int,
    id4 bigint,
    slary float,
    comm double,
    isok boolean,
    content binary,
    dt timestamp
)
row format delimited 
fields terminated by '\t';

233	12	342523	455345345	30000	60000	nihao	helloworld	2017-06-02
126	13	342526	455345346	80000	100000	true	helloworld1	2017-06-02 11:41:30

-timestamp 如果是年月日时分秒的格式，必须是写全，才能映射成功。

load data local inpath './data/datatype.txt' into table datatype1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

扩展） 数据类型转换

自动转换(隐式转换)

• 在做运算时，小范围类型都可以自动转为大范围类型做运算

7.2、复杂数据类型之array

7.2.1、定义格式如下：

create table tableName(
    ......
    colName array<基本类型>
    ......
)

说明：下标从0开始，越界不报错，以null代替
1
2
3
4
5
6
7

7.2.2、案例准备：

zhangsan	78,89,92,96
lisi	67,75,83,94
王五	23,12

create table if not exists arr1(
    name string,
    scores array<String>
)
row format delimited 
fields terminated by '\t';

drop table arr2;
create table if not exists arr2(
    name string,
    scores array<String>
)
row format delimited 
fields terminated by '\t'
collection items terminated by ',';

load data local inpath './data/arr1.txt' into table arr1;
load data local inpath './data/arr1.txt' into table arr2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

7.2.3、查询语句：

select * from arr1;
select name,scores[1] from arr2 where size(scores) > 3;

--统计arr2中的每个人的总成绩
select scores[0]+scores[1]+nvl(scores[2],0)+nvl(scores[3],0) from arr2;
1
2
3
4
5

7.2.4 、想要一种效果：也就是将数组类型的数据元素展开，换句话说，就是列转行

zhangsan	78,89,92,96
lisi	67,75,83,94
王五	23,12
将上述效果转成下面的效果，更方便统计每个人的总成绩。
zhangsan        78
zhangsan        89
zhangsan        92
zhangsan        96
lisi	67
lisi	75
lisi	83
lisi	94
王五	23
王五	12
1
2
3
4
5
6
7
8
9
10
11
12
13
14

7.3、展开函数的使用

7.3.1、简介

• explode：

  展开函数(UDTF函数中的一种)，作用是：接受一个数据行，然后返回产生多个数据行

• lateral view：虚拟表

  会将UDTF函数生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到数据聚合的目的

7.3.2、上案例：

- select explode(scores) score from arr2;

- select name,cj from arr2  lateral view explode(scores) mytable as cj;

- 统计每个学生的总成绩：
select name,sum(cj) as totalscore from arr2 lateral view explode(scores) mytable as cj group by name;
1
2
3
4
5
6

7.3.3、需求：向array字段中动态加载数据，不直接load加载，而是insert。

7.3.3.1、准备数据

create table arr_temp
as
select name,cj from arr2 lateral view explode(scores) score as cj;
1
2
3

7.3.3.2、借助collect_set函数，列转行函数，有去重效果。collect_list函数没有去重效果

drop table arr3;
create table if not exists arr3(
    name string,
    scores array<string>
)
row format delimited 
fields terminated by ' '
collection items terminated by ',';

将数据写成array格式：
insert into arr3
select name,collect_set(cj) from arr_temp group by name;

查询每个人的最后一科的成绩
select name,scores[size(scores)-1] lastsubject from arr3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

7.4、复杂数据类型之map

7.4.1、定义格式如下：

create table tableName(
    .......
    colName map<T,T>
    ......
)
1
2
3
4
5

7.4.2、案例准备：

zhangsan	chinese:90,math:87,english:63,nature:76
lisi	chinese:60,math:30,english:78,nature:0
wangwu	chinese:89,math:25

create table if not exists map1(
    name string,
    score map<string,int>
)
row format delimited 
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';

load data local inpath './data/map1.txt' into table map1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

7.4.3、查询语句：

#查询数学大于35分的学生的英语和自然成绩：
select 
m.name,
m.score['english'] ,
m.score['nature']
from map1 m
where m.score['math'] > 35;

#查看每个人的前两科的成绩总和
select 
m.name,
m.score['chinese']+m.score['math']
from map1 m;
1
2
3
4
5
6
7
8
9
10
11
12
13

7.4.4、展开查询

- 展开效果
zhangsan	chinese		90
zhangsan	math	87
zhangsan	english 	63
zhangsan	nature		76

- explode展开数据：
select explode(score) as (m_object,m_score) from map1;

- 使用lateral view explode 结合查询：
select name,m_object,m_score from map1 lateral view explode(score) score as 
m_object,m_score;


- 统计每个人的总成绩
select name,sum(m_score)
from map1 lateral view explode(score) score as 
m_object,m_score
group by name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

7.4.5、将数据动态写入map字段中

将下面的数据格式
zhangsan        chinese 90
zhangsan        math    87
zhangsan        english 63
zhangsan        nature  76
lisi    chinese 60
lisi    math    30
lisi    english 78
lisi    nature  0
wangwu  chinese 89
wangwu  math    25
wangwu  english 81
wangwu  nature  9
转成：
zhangsan chinese:90,math:87,english:63,nature:76
lisi chinese:60,math:30,english:78,nature:0
wangwu chinese:89,math:25,english:81,nature:9
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

7.4.5.1、准备数据

create table map_temp
as
select name,m_subject,m_score from map1 lateral view explode(score) t1 as m_subject,m_score;
1
2
3

7.4.5.2、开始写：

第一步：将科目和成绩组合在一起，concat
select name,concat(m_subject,':',m_score) as score from map_temp;

第二步: 将所有属于同一个人的数据组合在一起
select name,collect_set(concat(m_subject,":",m_score)) 
from map_temp
group by name;

第三步: 将数组变成一个字符串concat_ws
select name,concat_ws(",",collect_set(concat(m_subject,":",m_score)))
from map_temp
group by name;

第四步: 将字符串转成map 使用函数str_to_map(text, delimiter1, delimiter2)
text：是字符串
delimiter1：多个键值对之间的分隔符
delimiter2：key和value之间的分隔符

select
name,
str_to_map(concat_ws(",",collect_set(concat(m_subject,":",m_score))),',',':')
from map_temp
group by name;

第五步：存储准备的表中
create table map2 as
select
name,
str_to_map(concat_ws(",",collect_set(concat(m_subject,":",m_score))),',',':') score
from map_temp
group by name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

7.5、复杂数据类型 struct

7.5.1、简介

struct类型，类似于java编程语言中对象实例的模板，即类的结构体。如地址类型的结构体:

public class Address{
	String provinces;
	String city;
	String street;
	.......
}
1
2
3
4
5
6

使用struct类型来定义一个字段的类型，语法格式为:

create table tableName(
........
colName struct<subName1:Type,subName2:Type,........>
........
)
1
2
3
4
5

调用语法：

colName.subName
1

7.5.2、案例演示：

1）数据准备

zhangsan	90,87,63,76
lisi	60,30,78,0
wangwu	89,25,81,9

create table if not exists struct1(
    name string,
    score struct<chinese:int,math:int,english:int,natrue:int>
)
row format delimited 
fields terminated by '\t'
collection items terminated by ',';

导入数据：
load data local inpath './data/arr1.txt' into table struct1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

**2）需求：**查询数学大于35分的学生的英语和语文成绩：

select name,
score.english,
score.chinese
from str2
where score.math > 35
;
1
2
3
4
5
6

7.5、综合案例演示

1）数据准备

-- 主管信息表如下：
manager(uid uname belong tax addr)

-- 数据如下：
1	xdd	ll,lw,lg,lc,lz	wx:600,gongjijin:1200,shebao:450	北京,西城,中南海
2	lkq	lw,lg,lc,lz,lq	wx:1000,gongjijin:600,shebao:320	河北,石家庄,中山路
3	zs	lw,lg,lc	    wx:2000,gongjijin:300,shebao:10	    江西,南昌,八一大道

-- 建表语句：
create table manager_t(
    uid int,
    uname string,
    belong array<string>,
    tax map<string,int>,
    addr struct<province:string,city:string,street:string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';


-- 加载数据
load data local inpath './data/manager_t.txt' into table manager_t;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

**2）查询需求：**下属个数大于4个，公积金小于1200，省份在河北的数据

select * from manager_t where size(belong)>4 and tax['gongjijin']<1200 and addr.province='河北';
1

**扩展说明）**嵌套数据类型

举例说明：map<string,array<map<string,struct>>>
嵌套类型：所有元素分隔符都要自己去定义

默认分隔符：^A	对应输入方式:ctrl+V ctrl+A    
1
2
3
4

八、Hive函数

在Hive中，函数主要分两大类型，一种是内置函数，一种是用户自定义函数。

8.1、Hive内置函数

8.1.1、函数查看

show functions;
desc function functionName;
1
2

8.1.2、日期函数

1）当前系统时间函数：current_date()、current_timestamp()、unix_timestamp()

-- 函数1：current_date();		
	当前系统日期		格式："yyyy-MM-dd"
-- 函数2：current_timestamp();	
	当前系统时间戳：	格式:"yyyy-MM-dd HH:mm:ss.ms"
-- 函数3：unix_timestamp();	
	当前系统时间戳	格式：距离1970年1月1日0点的秒数。
1
2
3
4
5
6

2）日期转时间戳函数：unix_timestamp()

格式：unix_timestamp([date[,pattern]])
案例：
select unix_timestamp('1970-01-01 0:0:0'); -- 传入的日期时间是东八区的时间， 返回值是相对于子午线的时间来说的
select unix_timestamp('1970-01-01 8:0:0'); 
select unix_timestamp('0:0:0 1970-01-01',"HH:mm:ss yyyy-MM-dd"); 
select unix_timestamp(current_date());
1
2
3
4
5
6

3）时间戳转日期函数：from_unixtime

语法：from_unixtime(unix_time[,pattern]) 
案例：
select from_unixtime(1574092800); 
select from_unixtime(1574096401,'yyyyMMdd'); 
select from_unixtime(1574096401,'yyyy-MM-dd HH:mm:ss'); 
select from_unixtime(0,'yyyy-MM-dd HH:mm:ss');
select from_unixtime(-28800,'yyyy-MM-dd HH:mm:ss');
1
2
3
4
5
6
7

4）计算时间差函数：datediff()、months_between()

格式：datediff(date1, date2) -- Returns the number of days between date1 and date2
select datediff('2019-11-20','2019-11-01');

格式：months_between(date1, date2) -- returns number of months between dates date1 and date2
select months_between('2019-11-20','2019-11-01');
select months_between('2019-10-30','2019-11-30');
select months_between('2019-10-31','2019-11-30');
select months_between('2019-11-00','2019-11-30');
1
2
3
4
5
6
7
8

5）日期时间分量函数：year()、month()、day()、hour()、minute()、second()

案例：
select year(current_date);
select month(current_date);
select day(current_date);
select year(current_timestamp);
select month(current_timestamp);
select day(current_timestamp);
select hour(current_timestamp);
select minute(current_timestamp);
select second(current_timestamp);

select dayofmonth(current_date);
select weekofyear(current_date)
1
2
3
4
5
6
7
8
9
10
11
12
13

6）日期定位函数：last_day()、next_day()

--月末:
select  last_day(current_date)
--下周
select next_day(current_date,'thursday');
1
2
3
4

7）日期加减函数：date_add()、date_sub()、add_months()

格式：
date_add(start_date, num_days)
date_sub(start_date, num_days)
案例：
select date_add(current_date,1);
select date_sub(current_date,90);
select add_months(current_date,1);
1
2
3
4
5
6
7

定位案例：

--当月第1天: 
select date_sub(current_date,dayofmonth(current_date)-1)
--下个月第1天:
select  add_months(date_sub(current_date,dayofmonth(current_date)-1),1)
1
2
3
4

8. 字符串转日期：to_date()

（字符串必须为：yyyy-MM-dd格式）

select to_date('2017-01-01 12:12:12');
1
2
3

9）日期转字符串(格式化)函数：date_format

select date_format(current_timestamp(),'yyyy-MM-dd HH:mm:ss');
select date_format(current_date(),'yyyyMMdd');
select date_format('2017-01-01','yyyy-MM-dd HH:mm:ss');  
1
2
3

8.1.3、字符串函数

lower--（转小写）
select lower('ABC');

upper--（转大写）
select upper('abc');

length--（字符串长度，字符数）
select length('abc');

concat--（字符串拼接）
select concat("A", 'B');

concat_ws --（指定分隔符）
select concat_ws('-','a' ,'b','c');

substr--（求子串）
select substr('abcde',3);

split(str,regex)--切分字符串，返回数组。
select split("a-b-c-d-e-f","-");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

8.1.4、类型转换函数

cast(value as type) -- 类型转换
select cast('123' as int)+1;
1
2

8.1.5、数学函数

round --四舍五入((42.3 =>42))
select round(42.3);

ceil --向上取整(42.3 =>43)
select ceil(42.3);

floor --向下取整(42.3 =>42)
select floor(42.3);
1
2
3
4
5
6
7
8

8.1.6、其他常用函数

nvl(value,default value):如果value为null,则使用default value，否则使用本身value.

isnull()
isnotnull()

case when  then ....when ...then.. else... end
if(p1,p2,p3)
coalesce(col1,col2,col3...)返回第一个不为空的
1
2
3
4
5
6
7
8

8.2、窗口函数（重点）

8.2.1、窗口函数over简介

先来看一下这个需求：求每个部门的员工信息以及部门的平均工资。在mysql中如何实现呢

SELECT emp.*, avg_sal
FROM emp
	JOIN (
		SELECT deptno
			, round(AVG(ifnull(sal, 0))) AS avg_sal
		FROM emp
		GROUP BY deptno
	) t
	ON emp.deptno = t.deptno
ORDER BY deptno;


select emp.*,(select avg(ifnull(sal,0)) from emp B where B.deptno = A.deptno ) from emp A;
1
2
3
4
5
6
7
8
9
10
11
12
13

通过这个需求我们可以看到，如果要查询详细记录和聚合数据，必须要经过两次查询，比较麻烦。

这个时候，我们使用窗口函数，会方便很多。那么窗口函数是什么呢？

• 窗口函数又名开窗函数，属于分析函数的一种。

• 是一种用于解决复杂报表统计需求的函数。

• 窗口函数常用于计算基于组的某种值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

  简单的说窗口函数对每条详细记录开一个窗口,进行聚合统计的查询

• 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

• 窗口函数一般不单独使用

• 窗口函数内也可以分组和排序

参考下图：

---

**注意：**默认mysql老版本没有支持,在最新的8.0版本中支持, Oracle和Hive中都支持窗口函数

8.2.2、基本案例演示

数据准备(order.txt)

姓名,购买日期,购买数量

saml 2018-01-01,10
tony,2018-01-02,15
saml,2018-02-03,23
tony,2018-01-04,29
saml,2018-01-05,46
saml,2018-04-06,42
tony,2018-01-07,50
saml,2018-01-08,55
mart,2018-04-08,62
mart,2018-04-09,68
neil,2018-05-10,12
mart,2018-04-11,75
neil,2018-06-12,80
mart,2018-04-13,94
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-1. 创建order表:
create table if not exists t_order
(
    name      string,
    orderdate string,
    cost      int
)  row format delimited fields terminated by ',';
-2. 加载数据:
load data local inpath "./data/order.txt" into table t_order;
1
2
3
4
5
6
7
8
9

需求：查询每个订单的信息，以及订单的总数

– 1.不使用窗口函数

-- 查询所有明细
select * from t_order;
# 查询总量
select count(*) from t_order;
1
2
3
4

– 2.使用窗口函数：通常格式为 可用函数+over()函数

select *, count(*) over() from t_order;
1

注意:

窗口函数是针对每一行数据的.

如果over中没有指定参数,默认窗口大小为全部结果集

需求:查询在2018年1月份购买过的顾客购买明细及总人数

select *,count(*) over()
from t_order
where substring(orderdate,1,7) = '2018-01';
1
2
3

8.2.3、distribute by子句

在over窗口中进行分组,对某一字段进行分组统计,窗口大小就是同一个组的所有记录

语法：
over(distribute by colname[,colname.....])
1
2

需求:查看顾客的购买明细及月购买总额

select name, orderdate, cost, sum(cost) over (distribute by month(orderdate)) from t_order;

saml	2018-01-01	10	205
saml	2018-01-08	55	205
tony	2018-01-07	50	205
saml	2018-01-05	46	205
tony	2018-01-04	29	205
tony	2018-01-02	15	205
saml	2018-02-03	23	23
mart	2018-04-13	94	341
saml	2018-04-06	42	341
mart	2018-04-11	75	341
mart	2018-04-09	68	341
mart	2018-04-08	62	341
neil	2018-05-10	12	12
neil	2018-06-12	80	80
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

需求:查看顾客的购买明细及每个顾客的月购买总额

select name, orderdate, cost, sum(cost) over (distribute by name, month(orderdate)) from t_order;

mart    2018-04-13      94      299
mart    2018-04-11      75      299
mart    2018-04-09      68      299
mart    2018-04-08      62      299
neil    2018-05-10      12      12
neil    2018-06-12      80      80
saml    2018-01-01      10      111
saml    2018-01-08      55      111
saml    2018-01-05      46      111
saml    2018-02-03      23      23
saml    2018-04-06      42      42
tony    2018-01-07      50      94
tony    2018-01-04      29      94
tony    2018-01-02      15      94
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

8.2.4、sort by子句

sort by子句会让输入的数据强制排序 （强调：当使用排序时，窗口会在组内逐行变大）

语法： 
over([distribute by colname] [sort by colname [desc|asc]])
1
2

需求:查看顾客的购买明细及每个顾客的月购买总额,并且按照日期降序排序

select name, orderdate, cost, 
sum(cost) over (distribute by name, month(orderdate) sort by orderdate desc)
from t_order;
1
2
3

注意：可以使用partition by + order by 组合来代替distribute by+sort by组合

select name, orderdate, cost, 
sum(cost) over (partition by name, month(orderdate) order by orderdate desc)
from t_order;
1
2
3

注意：也可以在窗口函数中，只写排序，窗口大小是全表记录。

select name, orderdate, cost, 
sum(cost) over (order by orderdate desc)
from t_order;

neil    2018-06-12      80      80				-统计信息会逐行增加
neil    2018-05-10      12      92
mart    2018-04-13      94      186
mart    2018-04-11      75      261
mart    2018-04-09      68      329
mart    2018-04-08      62      391
saml    2018-04-06      42      433
saml    2018-02-03      23      456
saml    2018-01-08      55      511
tony    2018-01-07      50      561
saml    2018-01-05      46      607
tony    2018-01-04      29      636
tony    2018-01-02      15      651
saml    2018-01-01      10      661
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

8.2.5、Window子句

如果要对窗口的结果做更细粒度的划分,那么就使用window子句,常见的有下面几个

PRECEDING：往前 
FOLLOWING：往后 
CURRENT ROW：当前行 
UNBOUNDED：起点，
UNBOUNDED PRECEDING：表示从前面的起点， 
UNBOUNDED FOLLOWING：表示到后面的终点 
1
2
3
4
5
6

一般window子句都是rows开头

案例:

select name,orderdate,cost,
       sum(cost) over() as sample1,--所有行相加
       
       sum(cost) over(partition by name) as sample2,-- 按name分组，组内数据相加
       
       sum(cost) over(partition by name order by orderdate) as sample3,-- 按name分组，组内数据累加
       
       sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,-- 与sample3一样，由起点到当前行的聚合
       
       sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING  and current row) as sample5, -- 当前行和前面一行做聚合
       
       sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING  ) as sample6,-- 当前行和前边一行及后面一行
       
       sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 -- 当前行及后面所有行
       
from t_order;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

需求:查看顾客到目前为止的购买总额

select name,
       t_order.orderdate,
       cost,
       sum(cost)
           over (partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as allCount
from t_order;
1
2
3
4
5
6

mart	2018-04-08	62	62
mart	2018-04-09	68	130
mart	2018-04-11	75	205
mart	2018-04-13	94	299
neil	2018-05-10	12	12
neil	2018-06-12	80	92
saml	2018-01-01	10	10
saml	2018-01-05	46	56
saml	2018-01-08	55	111
saml	2018-02-03	23	134
saml	2018-04-06	42	176
tony	2018-01-02	15	15
tony	2018-01-04	29	44
tony	2018-01-07	50	94
1
2
3
4
5
6
7
8
9
10
11
12
13
14

需求：求每个顾客最近三次的消费总额

select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows between 2 preceding and current row)
from t_order;
1
2
3

8.3、序列函数

8.3.1、NTILE

ntile 是Hive很强大的一个分析函数。可以看成是：它把有序的数据集合 平均分配 到 指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

例子:

select name,orderdate,cost,
       ntile(3) over(partition by name), # 按照name进行分组,在分组内将数据切成3份
from t_order;
1
2
3

mart	2018-04-13	94	1
mart	2018-04-11	75	1
mart	2018-04-09	68	2
mart	2018-04-08	62	3
neil	2018-06-12	80	1
neil	2018-05-10	12	2
saml	2018-01-01	10	1
saml	2018-01-08	55	1
saml	2018-04-06	42	2
saml	2018-01-05	46	2
saml	2018-02-03	23	3
tony	2018-01-07	50	1
tony	2018-01-02	15	2
tony	2018-01-04	29	3
1
2
3
4
5
6
7
8
9
10
11
12
13
14

8.3.2 、LAG和LEAD函数

• lag返回当前数据行的前第n行的数据
• lead返回当前数据行的后第n行的数据

需求:查询顾客上次购买的时间

select name,orderdate,cost,
       lag(orderdate,1) over(partition by name order by orderdate) as time1
from t_order;


lag(colName,n[,default value]): 取字段的前第n个值。如果为null,显示默认值

select name,orderdate,cost,
       lag(orderdate,1,'1990-01-01') over(partition by name order by orderdate ) as time1
from t_order;
1
2
3
4
5
6
7
8
9
10

取得顾客下次购买的时间

select name,orderdate,cost,
       lead(orderdate,1) over(partition by name order by orderdate ) as time1
from t_order;
1
2
3

案例：求5分钟内点击100次的用户

dt 					id 	url
2019-08-22 19:00:01,1,www.baidu.com
2019-08-22 19:01:01,1,www.baidu.com
2019-08-22 19:02:01,1,www.baidu.com
2019-08-22 19:03:01,1,www.baidu.com


select id,dt,lag(dt,100) over(partition by id order by dt) 
from tablename where  dt-lag(dt,100) over(partition by id order by dt)>=5分钟
1
2
3
4
5
6
7
8
9

8.3.3、first_value和last_value

• first_value 取分组内排序后，截止到当前行，第一个值
• last_value 分组内排序后，截止到当前行，最后一个值

案例:

select name,orderdate,cost,
   first_value(orderdate) over(partition by name order by orderdate) as time1,
   last_value(orderdate) over(partition by name order by orderdate) as time2
from t_order;


select name,orderdate,cost,
   first_value(orderdate) over(partition by name order by orderdate) as time1,
   first_value(orderdate) over(partition by name order by orderdate desc) as time2
from t_order;
1
2
3
4
5
6
7
8
9
10

8.4、排名函数

第一种函数：row_number从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列

效果如下：
98		1
97		2
97		3
96		4
95		5
95		6

没有并列名次情况，顺序递增
1
2
3
4
5
6
7
8
9

第二种函数：RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位

效果如下：
98		1
97		2
97		2
96		4
95		5
95		5
94		7

有并列名次情况，顺序跳跃递增
1
2
3
4
5
6
7
8
9
10

第三种函数：DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

效果如下：
98		1
97		2
97		2
96		3
95		4
95		4
94		5

有并列名次情况，顺序递增
1
2
3
4
5
6
7
8
9
10

准备数据

userid	classno	score
1 gp1808 80   
2 gp1808 92
3 gp1808 84
4 gp1808 86
5 gp1808 88
6 gp1808 70
7 gp1808 98
8 gp1808 84
9 gp1808 86
10 gp1807 90
11 gp1807 92
12 gp1807 84
13 gp1807 86
14 gp1807 88
15 gp1807 80
16 gp1807 92
17 gp1807 84
18 gp1807 86
19 gp1805 80
20 gp1805 92
21 gp1805 94
22 gp1805 86
23 gp1805 88
24 gp1805 80
25 gp1805 92
26 gp1805 94
27 gp1805 86
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

create table if not exists stu_score(
    userid int,
    classno string,
    score int
)
row format delimited 
fields terminated by ' ';

load data local inpath './data/stu_score.txt' overwrite into table stu_score;
1
2
3
4
5
6
7
8
9

需求1：对每次考试按照考试成绩倒序

select *, row_number() over(partition by classno order by score desc) rn1 from stu_score;

select *, rank() over(partition by classno order by score desc) rn2 from stu_score;

select *, dense_rank() over(distribute by classno sort by score desc) rn3 from stu_score;

select *, dense_rank() over(order by score desc) `全年级排名` from stu_score;
1
2
3
4
5
6
7

需求2：获取每次考试的排名情况

select *,
-- 没有并列，相同名次依顺序排
row_number() over(distribute by classno sort by score desc) rn1,
-- rank()：有并列，相同名次空位
rank() over(distribute by classno sort by score desc) rn2,
-- dense_rank()：有并列，相同名次不空位
dense_rank() over(distribute by classno sort by score desc) rn3
from stu_score;
1
2
3
4
5
6
7
8

需求3：求每个班级的前三名

select *
from
(
select *,
row_number() over(partition by classno order by score desc) rn1
from stu_score
) A 
where rn1 < 4;
1
2
3
4
5
6
7
8

8.5、自定义函数

8.5.1、自定义函数介绍

hive的内置函数满足不了所有的业务需求。hive提供很多的模块可以自定义功能，比如：自定义函数、serde、输入输出格式等。而自定义函数可以分为以下三类：

1）UDF：user defined function

用户自定义函数，一对一的输入输出 （最常用的）。
1

2）UDAF：user defined aggregation function

用户自定义聚合函数，多对一的输入输出,比如：count sum max。
1

3. UDTF：user defined table-generate function

用户自定义表生产函数 一对多的输入输出，比如：lateral view explode
1

8.5.2、自定义UDF函数案例

1）准备工作和注意事项

在pom.xml，加入以下maven的依赖包

<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>2.1.1</version>
</dependency>
1
2
3
4
5

定义UDF函数要注意下面几点:

1. 继承 org.apache.hadoop.hive.ql.exec.UDF
2. 编写 evaluate() ，这个方法不是由接口定义的,因为它可接受的参数的个数,数据类型都是不确定的。Hive会检查UDF，看能否找到和函数调用相匹配的evaluate()方法

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

public class ConcatString extends UDF {
    public String evaluate(String str) {
        return str + "!";
    }
}
1
2
3
4
5
6
7

打包

8.5.3、函数加载方式

第一种：命令加载 (只针对当前session有效)

1. 将编写好的UDF打包并上传到服务器，将jar包添加到hive的classpath中
	hive> add jar /data/first.jar;
2. 创建一个自定义的临时函数名
	hive> create temporary function myConcatStr as 'com.su.hive.udf.FirstUDF'; 
3. 查看我们创建的自定义函数，
	hive> show functions;
4.在hive中使用函数进行功能测试 
	hive> select myupper('a'); 
5. 如何删除自定义函数？在删除一个自定义函数的时候一定要确定该函数没有调用
	hive> drop temporary function if exists myConcatStr;
1
2
3
4
5
6
7
8
9
10

第二种方式：启动参数加载 (只对当前session有效)：

1. 将编写好的自定义函数上传到服务器

2. 写一个配置文件，将添加函数的语句写入配置文件中，hive在启动的时候加载这个配置文件
[root@master ~]# vim $HIVE_HOME/conf/hive-init
文件中的内容如下
add jar /data/first.jar;
create temporary function myUpper as 'com.qf.hive.udf.FirstUDF'; 

3. 启动hive时
[root@master ~]# hive -i $HIVE_HOME/conf/hive-init
1
2
3
4
5
6
7
8
9
10

第三种方式：配置文件加载 (只要用hive命令行启动都会加载函数)

1、将编写好的自定函数上传到服务器
2、在hive的安装目录下的bin目录中创建一个文件，文件名为.hiverc
[root@master hive]#  vim $HIVE_HOME/bin/.hiverc

3、将添加函数的语句写入这文件中 

add jar /data/first.jar;
create temporary function myUpper as 'com.qf.hive.udf.FirstUDF'; 

4、直接启动hive
1
2
3
4
5
6
7
8
9
10

案例2-把生日转换成年纪

1. 思路分析：

1. age=当前年-生日的年份
2. 判断月份，当前月份小于生日月份，age-1
3. 月份相等，判断当前的日期，如果日期小于生日日期，age-1
1
2
3

2. 代码实现：

public class Birthday2Age extends UDF {
    public int evaluate(String birth) {
        // 1、判断参数
        if (StringUtils.isEmpty(birth)) {
            return -1;
        }
        // 拆分生日，获取年月日
        String[] birthdays = birth.split("-");
        // 得到生日年月日
        int birthYear = Integer.parseInt(birthdays[0]);
        int birthMonth = Integer.parseInt(birthdays[1]);
        int birthDay = Integer.parseInt(birthdays[2]);
        // 获取当前时间
        Calendar calendar = Calendar.getInstance();
        int nowYear = calendar.get(Calendar.YEAR);
        int nowMonth = calendar.get(Calendar.MONTH) + 1;
        int nowDay = calendar.get(Calendar.DAY_OF_MONTH);
        // 计算年龄
        int age = nowYear - birthYear;
        // 判断月份和日期
        if (nowMonth < birthMonth) {
            age -= 1;
        } else if (nowMonth == birthMonth && nowDay < birthDay) {
            age -= 1;
        }
        return age;
    }
    public static void main(String[] args) {
        System.out.println(new Birthday2Age().evaluate("1980-03-31"));
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

3. 测试：

1. 导入自定义函数
add jar ./data/birth.jar; 
create temporary function birth2age as 'com.qf.bigdata.hiveudf.Birthday2Age';
2. 测试
1
2
3
4

案例3-根据key获取value

1. 数据准备：

sex=1&hight=180&weight=100&sal=2000000

输入:sal
输出:2000000
1
2
3
4

2. 实现思路：

1. 将这种输入字符串转换成json格式的字符串{sex:1,hight:180,weight:100,sal:2000000}
2. 构建一个json的解析器
3. 根据key获取value
1
2
3

3. 代码如下：

public class Key2Value extends UDF {
    public String evaluate(String str, String key) throws JSONException {
        // 1、判断参数
        if (StringUtils.isEmpty(str) || StringUtils.isEmpty(key)) {
            return null;
        }
        // 将str转换成json格式
        // sex=1&hight=180&weight=130&sal=28000
        // {'sex':1,'hight':180,'weight':130,'sal':28000}
        String s1 = str.replace("&", ",");
        String s2 = s1.replace("=", ":");
        String s3 = "{" + s2 + "}";
        // 使用尽送对象解析json串
        JSONObject jo = new JSONObject(s3);
        return jo.get(key).toString();
    }
    public static void main(String[] args) throws JSONException {
        System.out.println(new Key2Value().evaluate("sex=1&hight=180&weight=130&sal=28000&faceId=189", "faceId"));
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

案例4-正则表达式解析日志

1. 数组准备：

220.181.108.151 - - [31/Jan/2012:00:02:32 +0800] "GET /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP/1.1" 200 8784 "-" "Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)" 

输出内容： 220.181.108.151 20120131 120232 GET /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP 200 Mozilla

String reg ="^([0-9.]+\d+) - - \[(.+ \+\d+)\] .+(GET|POST) (.+) (HTTP)\S+ (\d+).+\\"(\w+).+$";
1
2
3
4
5

2. 解决思路：

1. 定义一个正则表达式、获取匹配器
2. 匹配输入的字符串，将字符串分组
3. 如果能够匹配的上，将需要的字符串分组拼接起来，返回
1
2
3

3. 代码实现：

public class LogParser extends UDF {
    public String evaluate(String log) throws ParseException, Exception {
        // 1
        if (StringUtils.isEmpty(log)) {
            return null;
        }
        // 220.181.108.151 - - [31/Jan/2012:00:02:32 +0800] \"GET
        // /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP/1.1\" 200 8784
        // \"-\" \"Mozilla/5.0 (compatible; Baiduspider/2.0;
        // +http://www.baidu.com/search/spider.html)\"
        // 220.181.108.151 20120131 120232 GET
        // /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP 200 Mozilla
        // 定义一个正则表达式
        String reg = "^([0-9.]+\\d+) - - \\[(.* \\+\\d+)\\] .+(GET|POST) (.+) (HTTP)\\S+ (\\d+) .+\\\"(\\w+).+$";
        // 获取一个模式匹配器
        Pattern pattern = Pattern.compile(reg);
        // 匹配结果
        Matcher matcher = pattern.matcher(log);
        //
        StringBuffer sb = new StringBuffer();
        // 判断数据是否匹配上
        if (matcher.find()) {
            // 先获取匹配的段数
            int count = matcher.groupCount();
            // 循环获取每段的内容，并且将内容拼接起来
            for (int i = 1; i <= count; i++) {
                // 判断字段是否是时间字段，如果是，则做时间格式转换
                if (i == 2) {
                    // 定义一个时间格式来解析当前时间
                    Date d = new SimpleDateFormat("dd/MMM/yyyy:HH:mm:ss Z", Locale.ENGLISH).parse(matcher.group(i));
                    // 定义输出的时间格式
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd hhmmss");
                    // 将时间格式转换成输出格式，并输出
                    sb.append(sdf.format(d) + "\t");
                } else {
                    sb.append(matcher.group(i) + "\t");
                }
            }
        }
        return sb.toString();
    }

    public static void main(String[] args) throws Exception {
        System.out.println(new LogParser().evaluate(
                "220.181.108.151 - - [31/Jan/2012:00:02:32 +0800] \\\"GET /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP/1.1\\\" 200 8784 \\\"-\\\" \\\"Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)\\\""));
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

create table log100(
log string
);

load data local inpath './data/access.log' into table log100;
1
2
3
4
5

案例5-Json数据解析UDF开发

1） 数据准备：

有原始json数据如下：
{"movie":"1193","rate":"5","datetime":"978300760","uid":"1"}
{"movie":"661","rate":"3","datetime":"978302109","uid":"1"}
{"movie":"914","rate":"3","datetime":"978301968","uid":"1"}
{"movie":"3408","rate":"4","datetime":"978300275","uid":"1"}
{"movie":"2355","rate":"5","datetime":"978824291","uid":"1"}
{"movie":"1197","rate":"3","datetime":"978302268","uid":"1"}
{"movie":"1287","rate":"5","datetime":"978302039","uid":"1"}

参考数据文件：rating.json

最终形成hive表中这样的数据
movie   rate    datetime   uid
1197    3       978302268    1
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2） 将原始数据导入到hive库，先创建一个单字段的表

create table if not exists movie_json
(json string); 

load data local inpath './data/rating.json' into table moive_json;
1
2
3
4

3）写一个自定义函数，利用自定义函数解析json，将json串解析一个以\t分割的字符串，并上传使用

public class JsonParser extends UDF{
    private Logger logger = Logger.getLogger(JsonParser.class);
    public String evaluate(String json) {
        //判断传入参数
        if(Strings.isNullOrEmpty(json)){
            return null;
        }
        ObjectMapper objectMapper = new ObjectMapper();
        try {
            RateBean bean = objectMapper.readValue(json, RateBean.class);
            return bean.toString();
        } catch (IOException e) {
            logger.error("解析json串失败！！",e);
        }
        return null;
    }
    public static void main(String[] args) {
        System.out.println(new JsonParser().evaluate("{\"movie\":\"1193\",\"rate\":\"5\",\"datetime\":\"978300760\",\"uid\":\"1\"}"));
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

RateBean类型：

package com.qf.mr.rate;

import org.apache.hadoop.io.WritableComparable;

import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;

/**
 * 定义一个类型，封装电影评分信息
 * {"movie":"1193","rate":"5","datetime":"978300760","uid":"1"}
 * 属性：movie、rate、datetime、uid
 */

public class RateBean implements WritableComparable<RateBean> {
    private int uid;
    private int movie;
    private int rate;
    private long datetime;

    public RateBean(){

    }

    public RateBean(int uid, int movie, int rate, long datetime) {
        this.uid = uid;
        this.movie = movie;
        this.rate = rate;
        this.datetime = datetime;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public int getMovie() {
        return movie;
    }

    public void setMovie(int movie) {
        this.movie = movie;
    }
    
    public int getRate() {
        return rate;
    }

    public void setRate(int rate) {
        this.rate = rate;
    }

    public long getDatetime() {
        return datetime;
    }

    public void setDatetime(long datetime) {
        this.datetime = datetime;
    }

    /**
     * 定义排序规则：
     * 使用集合来封装每一个uid的所有电影信息
     * 所以只需要使用rate进行降序排序即可。
     * @param o
     * @return
     */
    public int compareTo(RateBean o) {
        return o.rate-this.rate;
    }

    public void write(DataOutput out) throws IOException {
        out.writeInt(uid);
        out.writeInt(movie);
        out.writeInt(rate);
        out.writeLong(datetime);
    }

    public void readFields(DataInput in) throws IOException {
        uid = in.readInt();
        movie = in.readInt();
        rate = in.readInt();
        datetime = in.readLong();
    }

    public String toString(){
        return movie +"\t"+rate+"\t"+datetime+"\t"+uid;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

4. 不使用中间表，直接克隆一张新表进行存储数据

drop table movie_rate;
create table if not exists movie_rate
as
select 
split(jsonParser(json),'\t')[0] as movie,
split(jsonParser(json),'\t')[1] as rate,
split(jsonParser(json),'\t')[2] as times,
split(jsonParser(json),'\t')[3] as uid
from movie_json;
1
2
3
4
5
6
7
8
9

5） 借助中间表的写法

第一步：insert into select func()将数据插入临时表

create table if not exists t_json_tmp
as
select jsonParser(json) as jsonLine from movie_json;
1
2
3

第二步： 用split函数从临时表中解析出字符串中各个字段存入到最终结果表

create table if not exists t_movieRate
as
select 
split(jsonLine,'\t')[0] as movie,
split(jsonLine,'\t')[1] as rate,
split(jsonLine,'\t')[2] as times,
split(jsonLine,'\t')[3] as uid
from t_json_tmp;
1
2
3
4
5
6
7
8

8.5.4、内置的json解析函数：

select get_json_object(json,"$.movie") movie,
get_json_object(json,"$.rate") rate,
get_json_object(json,"$.datetime") datetime,
get_json_object(json,"$.uid") uid
from movie_json;
1
2
3
4
5

九、分区表的相关内容

9.1、分区简介

为什么分区？

Hive中存放的数据往往是以PB为单位的庞大的数据集，海量的数据需要耗费大量的时间去处理，若是每次查询都对全部数据进行检索，效率会极为低下。而且在计多场景下，我们并不需要对全部数据进行检索，因此引入分区和分桶的方法减少每一次扫描总数据量，这种做法可以显著地改善性能。

如何分区

• 根据业务需求而定，不过通常以年、月、日、小时、地区等进行分区。

分区的语法

create table tableName(
    .......
    .......
)
partitioned by (colName colType [comment '...'],...)
1
2
3
4
5

分区的注意事项

• hive的分区名不区分大小写，不支持中文
• hive的分区字段是一个伪字段，但是可以用来进行操作
• 一张表可以有一个或者多个分区，并且分区下面也可以有一个或者多个分区。
• 分区是以字段的形式在表结构中存在，通过describe table命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。

分区的意义

• 让用户在做数据统计的时候缩小数据扫描的范围，在进行select操作时可以指定要统计哪个分区

分区的本质

• 在表的目录或者是分区的目录下在创建目录，分区的目录名为指定字段=值

9.2、分区案例

9.2.1、一级分区的使用

1） 建表语句

create table if not exists part1(
    id int,
    name string,
    age int
)
partitioned by (dt string)
row format delimited 
fields terminated by '\t'
lines terminated by '\n';
1
2
3
4
5
6
7
8
9

2） 加载数据

load data local inpath './data/user1.txt' into table part1 partition(dt='2020-05-05');
load data local inpath './data/user2.txt' into table part1 partition(dt='2020-05-06');
1
2

9.2.2、二级分区的使用

1） 建表语句

create table if not exists part2(
    id int,
    name string,
    age int
)
partitioned by (year string,month string)
row format delimited 
fields terminated by '\t';
1
2
3
4
5
6
7
8

2） 加载数据

load data local inpath './data/user1.txt' into table part2 partition(year='2020',month='03'); 
load data local inpath './data/user2.txt' into table part2 partition(year='2020',month='04');
load data local inpath './data/user2.txt' into table part2 partition(year='2020',month='05');
1
2
3

9.2.3、三级分区的使用

1） 建表语句

create table if not exists part3(
id int,
name string,
age int
)
partitioned by (year string,month string,day string)
row format delimited 
fields terminated by '\t';
1
2
3
4
5
6
7
8

2）加载数据

load data local inpath './data/user1.txt' into table part3 partition(year='2020',month='05',day='01');

load data local inpath './data/user2.txt' into table part3 partition(year='2019',month='12',day='31');  
1
2
3

9.2.4、测试是否区分大小写

在hive中，分区字段名是不区分大小写的，不过字段值是区分大小写的。我们可以来测试一下

1） 建表语句

create table if not exists part4(
    id int,
    name string
)
partitioned by (year string,month string,DAY string)
row format delimited fields terminated by ','
;

--测试字段名的大小写，结果不区分。
1
2
3
4
5
6
7
8
9

2） 加载数据

load data local inpath './data/user1.txt' into table part4 partition(year='2018',month='03',DAy='21');

load data local inpath './data/user2.txt' into table part4 partition(year='2018',month='03',day='AA');

--测试字段值的大小写，结果是区分的。
1
2
3
4
5

9.2.5、查看分区:

语法：
	show partitions tableName
eg:
 	show partitions part4;
1
2
3
4

9.2.6、修改分区：

修改分区 （注意：location后接的hdfs路径需要写成完全路径）

alter table part3 partition(year='2019',month='10',day='23') set location '/user/hive/warehouse/mydb1.db/part1/dt=2018-03-21';    --错误使用

#:修改分区，指的是修改分区字段值对应的映射位置。

alter table part3 partition(year='2020',month='05',day='01') set location 'hdfs://master:8020/user/hive/warehouse/mydb.db/part1/dt=2020-05-05';
1
2
3
4
5

9.2.7、增加分区

1）新增分区（空）

alter table part3 add partition(year='2020',month='05',day='02');

alter table part3 add partition(year='2020',month='05',day='03') partition(year='2020',month='05',day='04');
1
2
3

2）新增分区 (带数据)

alter table part3 add partition(year='2020',month='05',day='05') location '/user/hive/warehouse/mydb.db/part1/dt=2020-05-06';
1

3） 新增多分区

alter table part3 add 
partition(year='2020',month='05',day='06') location '/user/hive/warehouse/mydb.db/part1/dt=2020-05-05'
partition(year='2020',month='05',day='07') location '/user/hive/warehouse/mydb.db/part1/dt=2020-05-06';
1
2
3

9.2.8、删除分区

1）删除单个分区

alter table part3 drop partition(year='2020',month='05',day='07');
1

2）删除多个分区

alter table part3 drop 
partition(year='2020',month='05',day='06'), 
partition(year='2020',month='05',day='06');
1
2
3

3. 测试分区表的分区都被删除的特点

create table if not exists part10(
    id int,
    name string,
    age int
)
partitioned by (year string,month string,day string)
row format delimited 
fields terminated by '\t';

load data local inpath './data/user1.txt' overwrite into table part10
partition(year='2020',month='05',day='06');
load data local inpath './data/user2.txt' overwrite into table part10
partition(year='2020',month='05',day='07');

删除分区:
alter table part10 drop
partition(year='2020',month='05',day='06'),
partition(year='2020',month='05',day='07');

注意:  默认创建分区表时，删除所有分区时，表目录不会被删除。


测试2： 使用location关键字去指定分区对应的位置
alter table part10 add partition(year='2020',month='05',day='08') location '/test/a/b';
alter table part10 add partition(year='2020',month='05',day='09') location '/test/a/c';

alter table part10 drop
partition(year='2020',month='05',day='08'),
partition(year='2020',month='05',day='09');
结论：在删除操作时，对应的目录（最里层）会被删除，上级目录如果没有文件存在，也会被删除，如果有文件存在，则不会被删除。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

9.3、分区类型详解

9.3.1、分区的种类

1. 静态分区：直接加载数据文件到指定的分区，即静态分区表。
2. 动态分区：数据未知，根据分区的值来确定需要创建的分区(分区目录不是指定的，而是根据数据的值自动分配的)
3. 混合分区：静态和动态都有。

9.3.2、分区属性设置

hive.exec.dynamic.partition=true，是否支持动态分区操作
hive.exec.dynamic.partition.mode=strict/nonstrict:  严格模式/非严格模式
hive.exec.max.dynamic.partitions=1000: 总共允许创建的动态分区的最大数量
hive.exec.max.dynamic.partitions.pernode=100:in each mapper/reducer node
1
2
3
4

9.3.3、创建动态分区

1）创建动态分区表

create table dy_part1(
    sid int,
    name string,
    gender string,
    age int,
    academy string
)
partitioned by (dt string)
row format delimited fields terminated by ','
;
1
2
3
4
5
6
7
8
9
10

2）动态分区加载数据

下面方式不要用，因为不是动态加载数据

load data local inpath '/hivedata/user.txt' into table dy_part1 partition(dt='2020-05-06');
1

正确方式：要从别的表中加载数据

**第一步：**先创建临时表：

create table temp_part1(
    sid int,
    name string,
    gender string,
    age int,
    academy string,
    dt string
)
row format delimited 
fields terminated by ','
;
1
2
3
4
5
6
7
8
9
10
11

注意：创建临时表时，必须要有动态分区表中的分区字段。

**第二步：**导入数据到临时表：

95001,李勇,男,20,CS,2017-8-31
95002,刘晨,女,19,IS,2017-8-31
95003,王敏,女,22,MA,2017-8-31
95004,张立,男,19,IS,2017-8-31
95005,刘刚,男,18,MA,2018-8-31
95006,孙庆,男,23,CS,2018-8-31
95007,易思玲,女,19,MA,2018-8-31
95008,李娜,女,18,CS,2018-8-31
95009,梦圆圆,女,18,MA,2018-8-31
95010,孔小涛,男,19,CS,2017-8-31
95011,包小柏,男,18,MA,2019-8-31
95012,孙花,女,20,CS,2017-8-31
95013,冯伟,男,21,CS,2019-8-31
95014,王小丽,女,19,CS,2017-8-31
95015,王君,男,18,MA,2019-8-31
95016,钱国,男,21,MA,2019-8-31
95017,王风娟,女,18,IS,2019-8-31
95018,王一,女,19,IS,2019-8-31
95019,邢小丽,女,19,IS,2018-8-31
95020,赵钱,男,21,IS,2019-8-31
95021,周二,男,17,MA,2018-8-31
95022,郑明,男,20,MA,2018-8-31

load data local inpath './data/student2.txt' into table temp_part1;    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

**第三步：**动态加载到表

insert into dy_part1 partition(dt) select sid,name,gender,age,academy,dt from temp_part1;

注意：严格模式下，给动态分区表导入数据时，分区字段至少要有一个分区字段是静态值
	 非严格模式下,导入数据时，可以不指定静态值。
1
2
3
4

9.3.4、混合分区示例

1） 创建一个分区表：

create table dy_part2(
    id int,
    name string
)
partitioned by (year string,month string,day string)
row format delimited fields terminated by ','
;
1
2
3
4
5
6
7

2） 创建临时表

create table temp_part2(
    id int,
    name string,
    year string,
    month string,
    day string
)
row format delimited fields terminated by ','
;

数据如下：
1,廉德枫,2019,06,25
2,刘浩(小),2019,06,25
3,王鑫,2019,06,25
5,张三,2019,06,26
6,张小三,2019,06,26
7,王小四,2019,06,27
8,夏流,2019,06,27

load data local inpath './data/temp_part2.txt' into table temp_part2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3） 导入数据到分区表

- 错误用法：
	insert into dy_part2 partition (year='2019',month,day) select * from temp_part2;

- 正确用法：
insert into dy_part2 partition (year='2020',month,day) select id,name,month,day from temp_part2;
1
2
3
4
5

4） 分区表注意事项

1. hive的分区使用的是表外字段，分区字段是一个伪列，但是分区字段是可以做查询过滤。
2. 分区字段不建议使用中文
3. 一般不建议使用动态分区，因为动态分区会使用mapreduce来进行查询数据，如果分区数据过多，导致namenode和resourcemanager的性能瓶颈。所以建议在使用动态分区前尽可能预知分区数量。
4. 分区属性的修改都可以修改元数据和hdfs数据内容。

5） Hive分区和Mysql分区的区别

• mysql分区字段用的是表内字段；而hive分区字段采用表外字段。

9.4、分桶

分桶是相对分区进行更细粒度的划分。在分区数量过于庞大以至于可能导致文件系统崩溃时，我们就需要使用分桶来解决问题了。

分桶将整个数据内容按照某列属性值的Hash值进行区分。比如，如要按照ID属性分为4个桶，就是对ID属性值的Hash值对4取模，按照取模结果对数据分桶。例如，取模结果为0的数据记录存放到一个文件中，取模为1的数据存放到一个文件中，取模为2的数据存放到一个文件中。

分桶同样应当在建表时就建立，建表语句与建立分区表类似。我们还是创建表person，其建表语句如下:

CREATE TABLEperson(id INT,
                   name STRING,age INT,
                   fav ARRAY<STRING>,
                   addr MAP<STRING,STRING>)
COMMENT 'This is the person table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY 'lt' PARTITIONED BY(dt STRING) 
CLUSTERED BY (id)into 3 bucketsCOLLECTION ITEMS TERMINATED BY '-'MAP KEYS TERMINATED BY':'
STORED AS TEXTFILE;
1
2
3
4
5
6
7
8

以字段id为分桶依据，将每个分区分成3个桶，导入数据的语句与分区表时没有区别，还是向分区中导入数据。

LOAD DATA LOCAL INPATH 'person.txt' OVERWRITE INTO TABLE person partition(dt='20180315');
1

如果要查询3个桶中第1个桶中的全部数据，可以通过以下查询语句进行查询。

select * from person tablesample (bucket 1 out of 3 on id);
1

具体过程操作步骤如下（相同的步骤不再重复描述）:

输入建表语句如下:

CREATE TABLE person(
    id INT,
    name STRING,>age INT,
    fav ARRAY <STRING> ,
    addr MAP<STRING, STRING>)
COMHENT 'This is the person table' 
PARTITIONED BY (dt STRING)
clustered by (id) into 4 buckets
ROW FORMAT DELIMITED FIELDS TERMINATED BY'\t'
COLLECTION ITEMS TERMINATED BY': ' 
MAP KEYS TERMINATED BY':'
STORED AS TEXTFILE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

向分区导入数据

查询，看分桶内是否成功。

select * from person tablesample(bucket 1 out of 3 on id)
1

十、序列化和反序列化

10.1、文件读取/解析的方式

create table t1(
    id int,
    name string
)
row format delimited
fields terminated by ','
;

0,7369,SMITH,CLERK,7902,1980-12-17,800,null,20

从文件里进行查询时，会不会在内存里产生hive的相关对象啊

select *  from student;
一行记录一行记录的显示。

select过程       磁盘上的数据--->row对象   <------反序列化     InputFormat
insert过程       row对象---->磁盘上的数据  <-----序列化   OutputFormat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

在建表语句中，指定了记录的切分格式以及字段的切分符号。实际上，hive在解析文件的时候，涉及到了两个类型。

1. 一个类用于从文件中读取一条一条的记录（根据记录分隔符确定一条记录） 
	row format: 用于指定使用什么格式解析一行的数据
	delimited : 表示使用 org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe 进行行的内容解析
2. 一个类用于从上面读到的记录中切分出一个一个的字段（根据指定字符作为分隔符）
    fields terminated by: 表示用什么字符进行字段之间的分隔
    lines terminated by:  表示用什么字符进行行之间的分隔
    
强调：hive在select时，这个过程是将字节序列转成hive中的java对象。
     hive在insert时，这个过程是将hive中的java对象转成字节序列。
1
2
3
4
5
6
7
8
9

10.2、Serde简介

10.2.1、概念

- SerDe是“Serializer and Deserializer”的简称。
- Hive使用SerDe（和FileFormat）来读/写表的Row对象。
- HDFS文件-> InputFileFormat -> <key，value> -> Deserializer -> Row对象
- Row对象->Serializer  -> <key，value> -> OutputFileFormat -> HDFS文件

注意，“key”部分在读取时会被忽略，而在写入时始终是常数。基本上Row对象存储在“值”中。
注意，org.apache.hadoop.hive.serde是一个过时的SerDe库。使用最新版本的org.apache.hadoop.hive.serde2。
1
2
3
4
5
6
7

可参考官网：https://cwiki.apache.org/confluence/display/Hive/DeveloperGuide#DeveloperGuide-HiveSerDe

10.2.2、常用的Serde

• csv: 逗号分隔值
• tsv: tab分隔值
• json: json格式的数据
• regexp: 数据需要复合正则表达式

10.3、LazySimpleSerDe

10.3.1、Hive分隔符

• 行分隔符：\n
• 列分隔符：^A

在Hive中，建表时一般用来来指定字段分隔符和列分隔符。一般导入的文本数据字段分隔符多为逗号分隔符或者制表符（但是实际开发中一般不用着这种容易在文本内容中出现的的符号作为分隔符），当然也有一些别的分隔符，也可以自定义分隔符。有时候也会使用hive默认的分隔符来存储数据。在建表时通过下面语法来指定：

ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '  -- 指定列分隔符
LINES TERMINATED BY '\n'                   -- 指定行分隔符
1
2

默认情况下行分隔符是\n, 列分隔符一般是不容易在文本中出现的字符,开发中大部分用TAB,但有时候TAB也有可能重复,这时候可以使用Hive的默认分隔符的.

hive中默认使用^A(ctrl+A)作为列分割符，如果用户需要指定的话，等同于row format delimited fields terminated by '\001'，因为^A八进制编码体现为'\001'.所以如果使用默认的分隔符，可以什么都不加，也可以按照上面的指定加‘\001’为列分隔符，效果一样。

hive默认使用的行分隔符是'\n'分隔符 ，默认是通过row format delimited fields terminated by '\t'这个语句来指定不同的分隔符，但是hive不能够通过LINES TERMINATED BY '$$'来指定行分隔符，目前为止，hive的默认行分隔符仅支持‘\n’字符。
1
2
3
4
5

10.3.2、数据样本

1001^Azs^A23
1002^Alis^A24
1
2

10.3.3、建表

create table if not exists csv1(
    uid int,
    uname string,
    age int
);       -- 格式都没有指定，默认使用的就是LazySimpleSerde,记录分隔符是\n，列分隔符是^A
1
2
3
4
5

10.3.4、导入数据

load data local inpath './data/csv1.txt' overwrite into table csv1;
1

10.4、CSV

10.4.1、分隔符：

列分隔符：逗号

CSV格式的文件也称为逗号分隔值（Comma-Separated Values，CSV，有时也称为字符分隔值，因为分隔字符也可以不是逗号。），其文件以纯文本形式存储表格数据（数字和文本）。CSV文件由任意数目的记录组成，记录间以换行符分隔；每条记录由字段组成，字段间的分隔符是其它字符或字符串，最常见的是逗号或制表符。通常，所有记录都有完全相同的字段序列。

在CSV的Serde中有以下三个默认属性
1. 默认转义字符(DEFAULT_ESCAPE_CHARACTER): 	\ 	<--反斜线
2. 默认引用字符(DEFAULT_QUOTE_CHARACTER):		"	<--双引号    
3. 默认分隔符(DEFAULT_SEPARATOR):			 ，	<-逗号
1
2
3
4

10.4.2、数据样本

1001,zs,23
1002,lis,24
1
2

10.4.3、建表

drop table csv2;
create table if not exists csv2(
    uid int,
    uname string,
    age int
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
;
1
2
3
4
5
6
7
8

10.4.4、导入数据：

load data local inpath './data/csv2.txt' into table csv2;
1

10.4.5、自定义字段分隔符、字段引用字符、转义字符

如果不想使用默认分隔符的话，可以使用自定义分隔符，原理其实就是向底层的API的方法的参数中传入指定分隔值

可以指定字段分隔符、字段引用字符、转义字符

create table csv3(
    uid int,
    uname string,
    age int
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties(
    "separatorChar"="7",
    "qutoeChar"="'",
    "escapeChar"="\\")
stored as textfile
;

load data local inpath './data/csv3.txt' into table csv3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

10.5、json serde ：

10.5.1、说明

• json serde 可以是自己写的jar包也可以是第三方的jar包
• 要把这种jar包添加到hive的class path中
• add jar ./data/json-serde-1.3-jar-with-dependencies.jar;

10.5.2、准备数据

{"uid":"1","uname":"gaoyuanyuan","age":"18"}
{"uid":"2","uname":"gaojianzheng","age":"42"}
1
2

10.5.3、建表语句

drop table json1;
create table if not exists json1(
    uid int,
    uname string,
    age int
)
row format serde 'org.openx.data.jsonserde.JsonSerDe'
stored as textfile
;

drop table json1;
create table if not exists json1(
    uid int,
    uname string,
    age int
)
row format serde 'org.openx.data.jsonserde.JsonSerDe'
with serdeproperties(
    "ignore.malformed.json"="true"     # 忽略格式错误，继续读取
    "mapping.time_stamp"="timeStamp"   #  字段映射
) 
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

10.5.4、导入数据

load data local inpath './data/json1.txt' into table json1;
1

10.6、复杂类型的使用：

10.6.1、数据准备：

zs math:100,98,76 chinese:98,96,100 english:100,99,90  
ls math:60,80,70 chinese:90,66,91 english:50,51,70  


create table t1(
    uname string,
    score map<string,array<int>>,
)
row format delimited 
fields terminated by ' '
map keys terminiated by ':'
collection items terminated by',';

----上述的表语句需要执行map的元素分隔符，还要指定数组的元素分隔符，都用到了collection.因此一个collection无法指定多个分割符。这种情况，只能换思路，使用比较合适的serde去导入数据(jsonSerde最适合)


可以使用编程语言转成下面json字符串  
{"uname":"zs","score":{"math":[100,98,76],"chinese":[98,96,100],"english":[100,99,90]}}  
{"uname":"ls","score":{"math":[60,80,70],"chinese":[90,66,91],"english":[50,51,70]}}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

10.6.2、建表：

create table if not exists complex(
    uname string,
    score map<String,array<int>>
)
row format serde 'org.openx.data.jsonserde.JsonSerDe'
stored as textfile;
1
2
3
4
5
6

10.6.3、导入数据：

load data local inpath './data/json2.txt' into table complex;
1

10.7、Regex Serde

10.7.1、数据准备

01||zhangsan||23
02||lisi||24
1
2

10.7.2、说明

hive默认情况下只支持单字节分隔符，如果数据中的分隔符是多字节的，则hive默认是处理不了的。需要使用正则Serde

10.7.3、建表

create table if not exists t_regex(
    id string,
    uname string,
    age int
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
    'input.regex'='(.*)\\|\\|(.*)\\|\\|(.*)',
    'output.format.string'='%1$s %2$s %3$s'
)
stored as textfile;
1
2
3
4
5
6
7
8
9
10
11

10.7.4、导入数据

load data local inpath './data/regex.txt' into table t_regex;
1

十一、Hive存储格式

11.1、hive的存储格式

hive的存储格式分为两大类：一类纯文本文件，一类是二进制文件存储。

第一类： 纯文本文件存储

textfile: 纯文本文件存储格式，不压缩，也是hive的默认存储格式，磁盘开销大，数据解析开销大
1

第二类：二进制文件存储

- sequencefile:
	会压缩，不能使用load方式加载数据
- parquet:
	会压缩，不能使用load方式加载数据
- rcfile:
	会压缩，不能load。查询性能高，写操作慢，所需内存大，计算量大。此格式为行列混合存储，hive在该格式下，会尽量将附近的行和列的块存储到一起。
- orcfile: rcfile的升级版
1
2
3
4
5
6
7

11.2、存储格式的配置项

<property>
    <name>hive.default.fileformat</name>
    <value>TextFile</value>
    <description>
        Expects one of [textfile, sequencefile, rcfile, orc].
        Default file format for CREATE TABLE statement. Users can explicitly override it by CREATE TABLE ... STORED AS [FORMAT]
    </description>
</property>
1
2
3
4
5
6
7
8

11.3、案例测试：

案例1：textfile

create external table if not exists stocks_1 (
    exchange1 string,
    symbol string,
    ymd string,
    price_open float,
    price_high float,
    price_low float,
    price_close float,
    volume int,
    price_adj_close float
)
row format delimited
fields terminated by ','
stored as textfile;

load data local inpath './data/stocks.csv' into table stocks_1;
在linux的命令行上使用hdfs dfs -put方法去上传到指定目录下。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

案例2： sequencefile

create external table if not exists stocks_seq_1 (
    exchange1 string,
    symbol string,
    ymd string,
    price_open float,
    price_high float,
    price_low float,
    price_close float,
    volume int,
    price_adj_close float
)
row format delimited
fields terminated by ','
stored as sequencefile
;

使用 insert into 的方式加载数据
from stocks_1 insert into stocks_seq_1 select * ;
或者使用克隆的方式：
create table stocks_seq_2 stored as sequencefile as select * from stocks_1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

案例3：parquet

create external table if not exists stocks_parquet (
    exchange1 string,
    symbol string,
    ymd string,
    price_open float,
    price_high float,
    price_low float,
    price_close float,
    volume int,
    price_adj_close float
)
row format delimited
fields terminated by ','
stored as parquet
;

使用 insert into的方式加载数据
from stocks_1 insert into stocks_parquet select * ;
或者使用克隆的方式：
create table stocks_parquet_1 stored as parquet as select * from stocks_1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

案例4：rcfile

create external table if not exists stocks_rcfile (
    exchange1 string,
    symbol string,
    ymd string,
    price_open float,
    price_high float,
    price_low float,
    price_close float,
    volume int,
    price_adj_close float
)
row format delimited
fields terminated by ','
stored as rcfile
;

使用insert into的方式加载数据
from stocks_1 insert into stocks_rcfile select * ;
或者使用克隆的方式：
create table stocks_rcfile_2 stored as rcfile as select * from stocks_1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

案例5：orcfile

create external table if not exists stocks_orcfile (
    exchange1 string,
    symbol string,
    ymd string,
    price_open float,
    price_high float,
    price_low float,
    price_close float,
    volume int,
    price_adj_close float
)
row format delimited
fields terminated by ','
stored as orcfile
;

使用 insert into的方式加载数据
from stocks_1 insert into stocks_orcfile select * ;
或者使用克隆的方式：
create table stocks_orcfile_2 stored as orcfile as select * from stocks_1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

测试性能：

select count(*) from stocks_1;             # 1.469s
select count(*) from stocks_seq_1;         # 4s
select count(*) from stocks_parquet;       # 2.253s      
select count(*) from stocks_rcfile;        # 1.246s
select count(*) from stocks_orcfile;       # 1.348s
比较一下上述五个查询所需要的时间
1
2
3
4
5
6

十二、Hive索引与视图

12.1、索引

12.1.1、概要

• 0.7版本之后hive支持索引
• 优点：提高查询效率，避免全表扫描
• 缺点：冗余存储，加载数据较慢
• 索引文件的特点：索引数据有序，并且数据量较小
• 索引的关键字：index

12.1.2、索引的创建

1. 创建表并加载数据

drop table rate;
create table if not exists rate(
    movie string,
    rate string,
    datetime string,
    uid string
)
row format serde 'org.openx.data.jsonserde.JsonSerDe'
stored as textfile
;

hive> add jar ./data/json-serde-1.3-jar-with-dependencies.jar
hive> load data local inpath './data/rating.json' into table rate;

-- 克隆一个表rate1
create table if not exists rate1
as
select * from rate
;
-- 克隆一个表rate2
create table if not exists rate2
as
select * from rate
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2. 创建索引：

create index index_rate2
on table rate2(uid)
as 'compact' -- 索引文件的存储格式
with deferred rebuild -- 索引能够重建
; 
1
2
3
4
5

3. 修改索引(重建索引)：目的产生索引文件

alter index index_rate2 on rate2 rebuild;
1

4. 查看索引

show index on rate2;
1

5）验证性能

select count(*) from rate1;                   # 1.304s
select count(*) from rate2;                   # 1.278s

select * from rate1 where uid=6040;           # 0.115s
select * from rate2 where uid=6040;		      # 0.069s
1
2
3
4
5

6. 创建联合索引

create index index_rate2_uid_movie
on table rate2(uid,movie)
as 'bitmap'
with deferred rebuild
;

alter index index_rate2_uid_movie on rate2 rebuild;

select * from rate1 where uid=6040 and movie = 2020;
select * from rate2 where uid=6040 and movie = 2020;
1
2
3
4
5
6
7
8
9
10

7. 删除索引

drop index index_rate2 on rate2;
1

12.2、hive的视图

12.2.1、简介

• hive的视图简单理解为逻辑上的表
• hive只支持逻辑视图，不支持物化视图
• 视图存在的意义
  1. 对数据进行局部暴露（涉及隐私的数据不暴露）
  2. 简化复杂查询

12.2.2、创建视图：

create view if not exists v_1 as select uid,movie from rate2 where uid <3 ;
1

12.2.3、查看视图

show tables; 
show create table v_1; 
desc v_1;

对视图进行查询时：只能使用视图中的字段。不可以使用视图中没有的字段。
1
2
3
4
5

视图是否可以克隆？

• 没有必要对视图进行克隆，因为视图没有数据存储
• 修改视图：直接修改元数据（修改元数据中查询语句）
• 先删除再创建就可以

12.2.4、删除视图

drop view if exists v_1;
1

注意：

1. 切忌先删除视图所依赖的表再去查询视图
2. 视图不能用insert into 或者load 加载数据
3. 视图是只读的不能修改其结构、相关属性

十三、Hive的压缩

HQL语句最终会被编译成Hadoop的Mapreduce job，因此hive的压缩设置，实际上就是对底层MR在处理数据时的压缩设置。

13.1、hive在map阶段的压缩

map阶段的设置， 就是在MapReduce的shuffle阶段对mapper产生的中间结果数据压缩 。 在这个阶段，优先选择一个低CPU开销的算法。

<!-- 指定要不要开启中间压缩 -->
<property>
    <name>hive.exec.compress.intermediate</name>
    <value>false</value>
</property>

<!-- 指定中间压缩想要使用的压缩编码器（类文件） -->
<property>
    <name>hive.intermediate.compression.codec</name>
    <value/>
</property>

<!-- 指定压缩编码器中的那种压缩类型 -->
<property>
    <name>hive.intermediate.compression.type</name>
    <value/>
</property>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

13.2、hive在reduce阶段的压缩

即对reduce阶段的输出数据进行压缩设置。

<!-- 指定要不要开启最终压缩。 -->
<property>
    <name>hive.exec.compress.output</name>
    <value>false</value>
</property>

注意:如果开启，默认使用中间压缩配置的压缩编码器和压缩类型。
1
2
3
4
5
6
7

13.3、常用压缩格式

13.4、压缩编码器：

案例测试：

# 开启中间压缩机制
hive (mydb)> set hive.exec.compress.intermediate=true;
# 设置中间压缩编码器
hive (mydb)> set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.DefaultCodec;
# 设置压缩类型
hive (mydb)> set hive.intermediate.compression.type=RECORD;
# 开启reduce端的压缩机制
hive (mydb)> set hive.exec.compress.output=true;
1
2
3
4
5
6
7
8

create external table if not exists stocks_seq_2 (
    exchange1 string,
    symbol string,
    ymd string,
    price_open float,
    price_high float,
    price_low float,
    price_close float,
    volume int,
    price_adj_close float
)
row format delimited
fields terminated by ','
stored as sequencefile;

--动态加载数据：
insert into stocks_seq_2 select * from stocks_1;

验证数据是否变小了..........
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

13.5、知识扩展：

# 什么是可分割
  在考虑如何压缩那些将由MapReduce处理的数据时，考虑压缩格式是否支持分割是很重要的。考虑存储在HDFS中的未压缩的文件，其大小为1GB，HDFS的块大小为128MB，所以该文件将被存储为8块，将此文件用作输入的MapReduce作业会创建1个输人分片（split，也称为“分块”。对于block，我们统一称为“块”。）每个分片都被作为一个独立map任务的输入单独进行处理。 
  现在假设，该文件是一个gzip格式的压缩文件，压缩后的大小为1GB。和前面一样，HDFS将此文件存储为8块。然而，针对每一块创建一个分块是没有用的，因为不可能从gzip数据流中的任意点开始读取，map任务也不可能独立于其他分块只读取一个分块中的数据。gzip格式使用DEFLATE来存储压缩过的数据，DEFLATE将数据作为一系列压缩过的块进行存储。问题是，每块的开始没有指定用户在数据流中任意点定位到下一个块的起始位置，而是其自身与数据流同步。因此，gzip不支持分割(块)机制。 
  在这种情况下，MapReduce不分割gzip格式的文件，因为它知道输入是gzip压缩格式的(通过文件扩展名得知)，而gzip压缩机制不支持分割机制。因此一个map任务将处理16个HDFS块，且大都不是map的本地数据。与此同时，因为map任务少，所以作业分割的粒度不够细，从而导致运行时间变长。
1
2
3
4

十四、Hive企业级调优

14.1、环境方面

可以修改相关服务器的配置、容器的配置、环境搭建

14.2 具体软件配置参数

14.3、代码级别的优化

15.3.1 explain 和 explain extended

explain select * from text1;
explain extended select * from text1;
explain extended
select
d.deptno as deptno,
d.dname as dname
from dept d
union all
select
d.dname as dname,
d.deptno as deptno
from dept d
;
explain ： 只有对hql语句的解释。
explain extended：对hql语句的解释，以及抽象表达式树的生成。

stage 相当于一个job，一个stage可以是limit、也可以是一个子查询、也可以是group by等。
hive默认一次只执行一个stage，但是如果stage之间没有相互依赖，将可以并行执行。
任务越复杂，hql代码越复杂，stage越多，运行的时间一般越长。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

15.3.2 join

hive的查询永远是小表(结果集)驱动大表(结果集)
hive中的on的条件只能是等值连接
注意hive是否配置普通join转换成map端join、以及mapjoin小表文件大小的阀值
1
2
3

15.3.3 limit的优化：

hive.limit.row.max.size=100000 
hive.limit.optimize.limit.file=10 
hive.limit.optimize.enable=false (如果limit较多时建议开启) hive.limit.optimize.fetch.max=50000
1
2
3

15.3.4 本地模式：

hive.exec.mode.local.auto=false (建议打开) 
hive.exec.mode.local.auto.inputbytes.max=134217728
hive.exec.mode.local.auto.input.files.max=4
1
2
3

15.3.5 并行执行：

hive.exec.parallel=false (建议开启)
hive.exec.parallel.thread.number=8
1
2

15.3.6 严格模式：

hive.mapred.mode=nonstrict

Cartesian Product.
No partition being picked up for a query.
Orderby without limit.
Comparing bigints and strings.
Comparing bigints and doubles.
1
2
3
4
5
6
7

15.3.7 mapper和reducer的个数：

不是mapper和redcuer个数越多越好，也不是越少越好。

将小文件合并处理(将输入类设置为:CombineTextInputFormat) 通过配置将小文件合并：
mapred.max.split.size=256000000
mapred.min.split.size.per.node=1
mapred.min.split.size.per.rack=1
hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat

set mapreduce.job.reduces=10
hive.exec.reducers.max=1009
1
2
3
4
5
6
7
8
9
10

15.3.8 配置jvm重用：

set mapreduce.job.jvm.numtasks=1
set mapred.job.reuse.jvm.num.tasks=1
1
2

15.3.9数据倾斜：

数据倾斜：由于key分布不均匀造成的数据向一个方向偏离的现象。 
本身数据就倾斜
join语句容易造成
count(distinct col) 很容易造成倾斜
group by 也可能会造成

找到造成数据倾斜的key，然后再通过hql语句避免。
hive.map.aggr=true
hive.groupby.skewindata=false (建议开启)
hive.optimize.skewjoin=false
1
2
3
4
5
6
7
8
9
10

Whether to enable skew join optimization. 
  The algorithm is as follows: At runtime, detect the keys with a large skew. Instead of
  processing those keys, store them temporarily in an HDFS directory. In a follow-up map-reduce
  job, process those skewed keys. The same key need not be skewed for all the tables, and so,
  the follow-up map-reduce job (for the skewed keys) would be much faster, since it would be a
  map-join.
1
2
3
4
5
6

15.3.10 索引是一种hive的优化：

想要索引在查询时生效，还得设置使用索引：默认是不使用的。

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
SET hive.optimize.index.filter=true;
SET hive.optimize.index.filter.compact.minsize=0;
1
2
3

15.3.11 分区本身就是hive的一种优化

15.3.12 job的数量：

一般是一个查询产生一个job，然后通常情况一个job、可以是一个子查询、一个join、一个group by 、一个limit等一些操作。
1

大数据快速读写——HBase

​ HBase属于列式非关系型数据库（NoSQL），最早起源于Google发布的Bigtable，是由Powerset公司的Chad walters和Jim Kelleman在2006年末发起的。2007年7月，由Mike Cafarella提供代码，形成了一个基本可用的系统。

​ HBase的第一个发布版本是在2007年10月和Hadoop0.15.0捆绑发布的。2010年5月，HBase从Hadoop子项目升级为Apache顶层项目。几年，HBase已然成为一种广泛应用于各种行业生产中的成熟技术，它的用户包括Adobe、StumbleUpon、Twitter和雅虎等公司。

关于NoSQL

随着数据量的增大和对性能提升的要求，同时由于数据格式的多样性，关系型数据库已经无法满足需求，在这种情况下NOSQL应运而生。

什么是NoSQL

​ NoSQL = Not Only SQL，不仅仅是SQL。NoSQL是一个通用术语，即非关系型数据库，它不是以SQL作为其主要访问语言。现在有许多类型的NoSQL数据库，BerkeleyDB就是本地NoSQL数据库的一个示例，而HBase是一个分布式数据库。从技术层面上来说，HBase实际上是一个“数据存储”，而不是“数据库”，因为它缺乏关系型数据库的很多属性，如类型化列、辅助索引、触发器和高级查询语言等。

​ 一个关系型数据库虽然可以很好地被扩展，但仅限于某个点，也就是一个数据库服务器的大小，并且为了实现最佳性能，需要专用的硬件和存储设备。随着互联网的发展，传统的关系型数据库在应付超大规模和高并发的系统上已经显得“力不从心”，非关系型数据库就是在这样的背景下产生的。

NoSQL数据库的分类

NoSQL 数据库共分为4类，分别是键值（Key-Value）存储数据库、列存储数据库、文档型数据库和图形（Graph）数据库，具体介绍如下。

1. 键值（Key-Value）存储数据库

​ 键值存储数据库中的数据是以键值对格式进行存储的，类似于Java中的 Map，其数据库中的表有一个特定的Key键和键所指向的Value值。Key-Value模型的优势在于简单且容易部署，可以将程序中的数据直接映射到数据库中，使程序中的数据和键值存储数据库中的数据存储方式很相近，比如 Redis。

2. 列存储数据库

​ 列存储数据库与传统的关系型数据库不同，关系型数据库按照行进行存储，而列数据库是每一列单独存放，仅仅查询所需要的列，查询速度大幅提高。在HBase中，这些列是由列家族来安排的。

3. 文档型数据库

​ 文档型数据库和键值存储类似。该类型的数据模型是将内容按照某些特定的格式进行存储，如JSON格式和 MongoDB就属于文档型数据库。

4. 图形（Graph）数据库

​ 图形数据库与关系型数据库和列式数据库不同，它是基于灵活的图形模型，并且可以扩展到多个服务器上。另外，由于 NoSQL 数据库并没有标准的查询语言(SQL),所以在进行数据库查询时需要制定数据模型，如 Neo4j。

NoSQL数据库的应用

NoSQL数据库主要适用于以下场景:

• 数据量大、数据模型比较简单。
• 对数据库性能要求较高，需要节省开发成本和维护成本。
• 不需要高度的数据一致性。
• 对于给定key，比较容易映射复杂值的环境，数据之间关系性不强。

关系型数据库与非关系型数据库的区别

关系型数据库与非关系型数据库的区别主要在成本、查询速度、存储数据格式和扩展性4个方面。

• 成本：NoSOL 数据库简单易部署，基本都是开源软件，不需要像使用Oracle那样花费大笔资金购买后使用，相比关系型数据库NoSQL价格便宜。
• 查询速度：NoSQL 数据库将数据存储于缓存中，关系型数据库将数据存储在硬盘中，自然查询速度远不及NoSQL数据库。
• 存储数据格式：NoSOL 的存储格式是key/value形式、文档形式、图片形式等，所以可以存储基础类型及对象或者集合等各种格式，而数据库则只支持基础类型。
• 扩展性：关系型数据库有类似于JOIN这样的多表查询机制的限制导致扩展很艰难。

HBase基础

我们知道HDFS是大型数据集分析处理的文件系统，具有高延迟的特点，它更倾向于读取整个数据集而不是某条记录,因此当处理低延迟的用户请求时, HBase是更好的选择，它能实现某条数据的快速定位，提供实时读写功能。

HBase简介

​ HBase即HadoopDatabase，是一个基于HDFS和zookeeper的列式数据库，是一个高可靠性、高性能、面向列、可伸缩、实时读写的分布式数据库。HBase具有可伸缩性，它自底向上地进行构建，能够简单地通过增加节点来达到线性扩展。

​ HBase利用Hadoop HDFS作为其文件存储系统，利用基于YARN的MapReduce来处理HBase中的海量数据，利用Zookeeper作为其分布式协同服务，主要用来存储非结构化和半结构化的松散数据，也就是列存储的NoSQL数据库。HBase在大数据生态体系中的位置如图所示。

​ HBase并不是关系型数据库，它并没有严格的结构，而且不支持传统的SQL。它可以运用key/value方式进行数据的存储，同时在特定的情况下，能够做RDBMS不能做的事，基于HBase可以在廉价硬件构成的集群上管理超大规模的稀疏表。

​ 当然，HBase也并不是适用于所有情况，首先要确保有足够数据的情况下使用HBase，如果数据达到数亿或者数十亿行，那么HBase是一个很好地选择。如果只有几百万或者几千行的数据，则可以考虑使用传统的关系型数据库，主要原因是在数据量不是足够大时，所有的数据有可能在HBase的单个节点上，而其余的集群可能处于空闲状态。

​ 另外，我们还要考虑的是HBase并没有RDBMS 所提供的索引、事务、高级查询语言等功能，不可以将RDBMS构建的应用程序直接移植到HBase 上。如果一定要将RDBMS移植到HBase上，则需要考虑重新设计。

​ HBase还有一个显著的特点就是它有许多支持线性和模块化扩展的功能。HBase集群可以通过添加商业性服务器的RegionServers 来扩展。例如,如果集群从10个RegionServers扩展到20个RegionServers，那么在存储和处理能力方面的性能也都会倍增。

HBase数据模型

HBase数据模型主要包括行键（Row Key）、列租、时间戳、单元格（Cell）和HLog（WAL log）

1. 行键（Row Key）

​ 首先行键是字节数组，任何的字符串都可以作为行键，只是行键只能存储64KB的字节数。图中的行根据行键进行排序，决定着一行数据，数据按照Row key的字节升序（byte order）排序存储。如果要对表进行访问，则都要通过行键。

2. 列族（Column Family）& 列标签（qualifier）

​ 在HBase表中的每个列都是归属于列族的，列族CF1，CF2和CF3必须作为表模式（schema）定义的一部分预先给出。列名均以列族为前缀，每个“列族”均可以有多个列成员column，通过列标签表示，如course:math，course:english，新的列族成员以后可以动态的根据需要加入。权限控制及存储、调优都是在列族层面进行的；HBase会将同一列族里面的数据存储在同一目录下，在几个文件中保存。

3. 时间戳（Timestamp）

​ 在 HBase中的每个Cell存储单元，会对同一份数据存储多个版本，HBase-引入了时间戳区分每个版本之间的差异，时间戳的类型是64位整型，不同版本的数据按照时间倒序排序，最新的数据版本排在最前面。

​ 时间戳可以由HBase在数据写入的时候自动赋值,这个时候的时间戳是精确到毫秒的当前系统时间，时间戳也可以由客户显式地赋值。

4. 单元格（Cell）

​ 单元格Cell内容是没有解析的字节数组，有行和列的坐标交叉决定，同时单元格是由版本的。单元格中的数据是没有类型的，都是由二进制字节码形式存储。

HBase体系架构及组件

1. 元数据存储Zookeeper

​ 首先来看一下ZooKeeper组件，该组件主要用于存储HBase的schema和Table元数据，它保障在任何时候，集群中只有一个Master，同时Zookeeper用来存贮所有REgion的寻址入口，进行实时监控RegionServer的上线和下线信息，并且实时通知HMaster。

2. 资源分配HMaster

​ HMaster组件类似HDFS中的NameNode，它不存储数据，主要作用是为RegionServer分配Region，同时负责RegionServer的负载均衡，如果发现失效的RegionServer，它会重新分配上面的Region，HMaster组件还会管理用户对Table的增、删、改操作。

3. Region的处理HRegionServer

​ HMaster组件类似HDFS中的NameNode，而HRegionServer组件则类似HDFS中的DataNode。HRegionServer组件负责维护Region，处理对Region的I/O请求，同时，HRegionServer会负责切分在运行过程中变得过大的Region。

4. 保持访问性能Client

​ Client组件主要包含访问HBase的接口，同时维护Cache来加快HBase的访问性能。

5. 分布式存储和负载均衡最小单元Region

​ Region是HBase中分布式存储和负载均衡的最小单元，HBase会自动把表水平划分成多个区域，也就是多个Region。每个Region会保存一个表里面某段连续的数据，每个表一开始只有一个Region，随着数据不断插入，Region会不断增大，当增大到一个阀值的时候，Region就会等分成两个新的Region，这个等分的过程又称之为裂变。

​ 当表中的行不断增多时，就会有越来越多的Region。这样一张完整的表被保存在多个RegionServer上。

6. MemStore与StoreFile组件

​ 一个区域，也就是一个Region往往由多个Store组成，一个Store包括位于内存中的MemStore和位于磁盘的StoreFIle。

HBase执行原理

从图中可以看出，左边的Table包含多个Region，每个Region中又有多个Store，同时，每个Store又由一个memStore和0至多个StoreFile组成。

为了更好地理解Hbase执行原理，结合HBase的体系架构，可以看出StoreFile以HFile的格式保存在HDFS上。

在执行写入操作时，会先将数据写入MemStore中，当MemStore中的数据达到某个阈值时，HRegionServer会启动Flash Cache进程写入StoreFile，每次写入会形成单独的一个StoreFile。当StoreFile文件的数量增长到一定阈值后，系统会进行合并，在合并过程中会进行版本合并和删除工作，形成更大的StoreFile。

当一个Region中所有的StoreFile大小之和超过一定阈值后，会把当前的Region分割为两个，并由HMaster分配到相应的RegionServer服务器上，实现负载均衡。

在检索数据时，会先从MemStore中进行查找，如果从MemStore中找不到数据，会再从StoreFile上查找。

HBase安装

1. 下载HBase

首先从HBase官网http://hbase.apache.org/下载HBase安装包，下载链接为https://hbase.apache.org/downloads.html，并解压HBase安装包。

2. 修改核心配置文件

HBase的核心配置文件是hbase-site.xml，可以搭建HBase单机版、完全分布式、高可用几种方式。根据不同的搭建方式，hbase-site.xml的内容也不一样。

（1） 单机版方式

<property>
    <name>hbase.cluster.distributed</name>
    <value>true</value>
</property>
<property>
    <name>hbase.rootdir</name>
    <value>file:///opt/software/hbase-0.98.12.1-hadoop2</value>
</property>
<property>
    <name>hbase.ZooKeeper.property.dataDir</name>
    <value>/opt/software/2ooKeeper-3.4.6</value>
</property>
1
2
3
4
5
6
7
8
9
10
11
12

（2）完全分布式集群版方式

<property>
    <name>hbase.cluster.distributed</name>
    <value>true</value>
</property>
<property>
    <name>hbase.rootdir</ name>
    <value>hdfs://master:9000/hbase</value>
</property>
<property>
    <name>hbase.zookeeper.quorum</name>
    <value>master,slave</value>
</property>
1
2
3
4
5
6
7
8
9
10
11
12

（3）HDFS高可用集群版方式

<property>
    <name>hbase.rootdir</name>
    <value>hdfs://firsthigh/hbase</value>
</property>
<property>
    <name>hbase.master</name>
    <value>8020</value>
</property>
<property>
    <name>hbase.zookeeper.quorum</name>
    <value>hadoopmaster,hadoop01,hadoop02</value>
</property>
<property>
    <name>hbase.zookeeper.property.clientPort</name>
    <value>2181</value>
</property>
<property>
    <name>hbase.zookeeper.property.dataDir</name>
    <value>/opt/software/zookeeper/conf</value>
</property>
<property>
    <name>hbase.cluster.distributed</name>
    <value>true</value>
</property>
<property>
    <name>hbase.tmp.dir</name>
    <value>/var/hbase/tmp</value>
</property>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

3. 配置regionservers文件

在里面输入主、从节点的主机名称，RegionServer 是HBase集群运行在每个工作节点上的服务，它是整个HBase系统的关键所在，提供了对于 Region的管理和服务。修改如下:

master
slave
1
2

启动ZooKeeper，HDFS 与 YARN服务，命令如下：

zkServer.sh start
start-all.sh
1
2

在这里通过JPS命令确认服务已经正常启动后再启动HBase

4. 启动HBase

启动HBase的命令如下:

start-hbase.sh
1

5. 查看相关服务

启动完HBase之后，可以通过JPS命令查看相关服务是否已经存在，如果启动成功，可以看到以下相关进程，其中HMaster和HRegionServer是不可少的。

10073 DataNode
9800  QuorumPeerMain
80025 HMaster
80152 HRegionServer
10421 NodeManager
10328 ResourceManager
80203 JPS
9983  NameNode
1
2
3
4
5
6
7
8

6. 启动HBase Shell

在启动之后，就可以通过hbase shell命令进入HBase，命令如下：

[root@masterl~]# hbase shell
SLF4J:Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/software/hbase-]
SLF4J:Found binding in [jar:file:/opt/software/hadoop-]
SLF4J:See http: //www.slf4j.org/codes.html#multiple _bin
SLF4]: Actual binding is of type [org.slf4j.impl.Log4jL
HBase SHell ; enter 'help<RETURN>' for list of supported
Type"exit<RETURN>"to leave the HBase Shell
version 1.1.3,r72bc50f5fafeb105b213942bbee3d61ca72498
1
2
3
4
5
6
7
8
9

7. 测试

通过list命令显示相关表，只要不出现异常，则代表 HBase安装成功。

hbase (main) :001:0> list
TABLE
test
1
2
3

8. 通过浏览器查看HBase

在浏览器中通过16010访问HBase主页面。

HBase的Shell操作

HBase Shell是HBase的命名行工具，可以通过它对HBase进行操作。HBase的常用Shell命令如下：

1. 创建表

create '表名称', '列族名称1', '列族名称2', '列族名称N'

hbase (main) :002:0> create 'test3', 'cfl', 'cf2'
1
2
3

2. 删除表

删除表之前,需要先将表进行disable操作，然后再进行drop操作。

disable '表名称'
drop '表名称'
1
2

hbase (main) :007:0> disable 'test3'

hbase(main) :008:0> drop 'test3'
1
2
3

3. 显示所有表

hbase (main) :009:0> list
1

4. 查询数据

scan '表名称'

get '表名称', 'ROW值'
1
2
3

5. 增加数据

put '表名称', 'ROW值', '列族名称:列名', '列值'
1

6. 删除数据

delete '表名称', 'ROW值', '列族名称:列名'
1

7. 修改数据

put '表名称', 'ROW值', '列族名称:列名', '新列值'
1

Java API访问 HBase

/**
 * 创建表
 */
public static void createTable() {
    Configuration conf = HBaseConfiguration.create();
    conf.set("hbase.zookeeper.quorum", "masternode001, slavenode001");
    try {
        // 建立连接
        Connection connection = ConnectionFactory.createConnection(conf);
        // 获取Admin
        Admin admin = connection.getAdmin();
        // 添加表的描述
        HTableDescriptor tableDes = new HTableDescriptor(TableName.valueOf("test11"));
        // 添加列族
        HColumnDescriptor colDesc = new HColumnDescriptor("cf1");
        tableDes.addFamily(colDesc);
        admin.createTable(tableDes);
    } catch (IOException e) {
        e.printStackTrace();
    }
}

/**
 * 插入数据
 */
public static void addData() {
    Configuration conf = HBaseConfiguration.create();
    conf.set("hbase.zookeeper.quorum", "masternode001, slavenode001");
    try {
        // 建立连接
        Connection connection = ConnectionFactory.createConnection(conf);
        // 确定连接的表
        Table table = connection.getTable(TableName.valueOf("test11"));
        Put put = new Put(("110".getBytes()));
        put.addColumn("cf1".getBytes(), "name".getBytes(), "lisa".getBytes());
        table.put(put);
    } catch (IOException e) {
        e.printStackTrace();
    }
}

/**
 * 查询数据
 */
public static void getData() {
    Configuration conf = HBaseConfiguration.create();
    conf.set("hbase.zookeeper.quorum", "masternode001, slavenode001");
    try {
        // 建立连接
        Connection connection = ConnectionFactory.createConnection(conf);
        // 确定连接的表
        Table table = connection.getTable(TableName.valueOf("test11"));
        // 通过Row Key指定行
        Get get = new Get("110".getBytes());
        Result rs = table.get(get);
        // 指定列名，确定cell
        Cell cell = rs.getColumnLatestCell("cf1".getBytes(), "name".getBytes());
        // 输出cell的Row Key和值
        System.out.println(new String(CellUtil.cloneRow(cell), StandardCharsets.UTF_8));
        System.out.println(new String(CellUtil.cloneValue(cell), StandardCharsets.UTF_8));
    } catch (IOException e) {
        e.printStackTrace();
    }
}

/**
 * 查询全部数据
 */
public static void getDataByScan() {
    Configuration conf = HBaseConfiguration.create();
    conf.set("hbase.zookeeper.quorum", "masternode001, slavenode001");
    try {
        // 建立连接
        Connection connection = ConnectionFactory.createConnection(conf);
        // 确定连接的表
        Table table = connection.getTable(TableName.valueOf("test11"));

        Scan scan = new Scan();
        ResultScanner scanner = table.getScanner(scan);
        Iterator<Result> iterator = scanner.iterator();
        while (iterator.hasNext()) {
            Result next = iterator.next();
            Cell cell = rs.getColumnLatestCell("cf1".getBytes(), "name".getBytes());
            System.out.println(new String(CellUtil.cloneRow(cell), StandardCharsets.UTF_8));
            System.out.println(new String(CellUtil.cloneValue(cell), StandardCharsets.UTF_8));
        }

    } catch (IOException e) {
        e.printStackTrace();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

小结：

HBase属于列式数据库，当数据量达到亿级别时可以考虑使用HBase，当数据量上百万或者上千万级别时，使用关系型数据库是一个不错的选择。