《Hive系列》Hive详细入门教程_hive入门教程

目录

1 Hive基本概念

1.1 什么是Hive

• Hive简介

Hive：由FaceBook开源用于解决海量结构化日志的数据统计工具

Hive：基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL的查询功能。

• Hive本质

将HSQL转化成MapReduce程序

Hive处理的数据存储在HDFS
Hive分析数据底层实现的是MR
执行程序运行在Yarn上
1
2
3

1.2 Hive的优缺点

1.2.1 优点

接口采用类SQL语法，提供快速开发的能力
避免了去写MR程序，减少开发人员的学习成本
Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合
Hive的优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高
Hive支持自定义函数，用户可以根据自己的需求来实现自己的函数
1
2
3
4
5

1.2.2 缺点

Hive的HSQL表达能力有限
	迭代算法无法表达
	数据挖掘方面不擅长，由于MR数据处理流程的限制，效率更高的算法却无法实现
Hive的效率 比较低
	Hive自动生成的MR作业，通常情况下不够智能化
	Hive调优比较困难，粒度较粗
1
2
3
4
5
6

1.3 Hive运行机制

	Hive通过用户提供的一系列交互接口，接收到用户的指令（SQL），使用自己的Driver，结合元数据（Metastore），将这些指令翻译
成MR，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。
1
2

1.4 Hive和数据库比较

	由于Hive采用类似SQL的查询语言HQL，因此很容易将Hive理解为数据库。其实从结构来看，Hive 和数据库除了用于类似的查询语言，
再无类似之处。
1
2

1.4.1 数据更新

	由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少。因此，Hive中不建议对数据的改写，所有数据都是在加载的时候
确定好的。而数据库中的数据通常是需要进行修改的，因此可以采用insert into ... values添加数据，使用update ... set修改数据
1
2

1.4.2 执行延迟

	Hive在查询数据的时候，由于没有索引，需要扫描整个表。因此延迟较高。由于Hive底层使用的MR框架，而MR本身具有较高的延迟，因此
在利用MR执行Hive查询的时候，也有较高的延迟。
1
2

1.4.3 数据规模

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

2 Hive安装

2.1 安装JDK

2.2 安装Hadoop

2.3 安装MySQL

2.4 安装Hive

2.4.1 安装

##1. 解压
##2. 改名
[root@hadoop apps]# mv apache-hive-1.2.1-bin/ hive-1.2.1

##3. 配置环境变量
[root@hadoop hive-1.2.1]# vi /etc/profile
## 自定义环境变量
export JAVA_HOME=/opt/apps/jdk1.8.0_45
export HADOOP_HOME=/opt/apps/hadoop-2.8.1
export HIVE_HOME=/opt/apps/hive-1.2.1
export PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$HIVE_HOME/bin
export CLASS_PATH=.:$JAVA_HOME/lib

##4. 启动命令行
[root@hadoop bin]# start-dfs.sh
[root@hadoop bin]# start-yarn.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.4.2 hive远程模式

• hive-site.xml

<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!--
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
-->
<configuration>
  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://hadoop:3306/hive?createDataBaseIfNotExists=true</value>
  </property>
    
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
  </property>

  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
  </property>
   
 <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>111213</value>
  </property>

</configuration>
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

• 上传mysql的jdbc驱动jar包到hive的lib下
• 分配权限

mysql> grant all privileges on . to ‘root’@‘%’ identified by ‘123456’ with grant option;
mysql> flush privileges;
mysql> grant all privileges on . to ‘root’@‘hadoop’ identified by ‘123456’ with grant option;
mysql> flush privileges;

• Navicat新建数据库hive

字符集：latin1

2.4.3 Thrift server

hive --service hiveserver2 &

beeline

!connect jdbc:hive2://hadoop:10000

2.5 Hive日志

Hive日志默认在tmp下，修改日志的路径
[root@hadoop conf]# mv hive-log4j.properties.template hive-log4j.properties
[root@hadoop conf]# vi hive-log4j.properties
hive.log.dir=/opt/apps/hive-1.2.1/logs
1
2
3
4

3 Hive数据类型

3.1 基本数据类型

Hive的String类型相当于数据库的Varchar类型，是一个不可变的字符串

3.2 集合数据类型

3.2.1 Array

-- 表数据
河南 郑州,开封,周口,新乡
浙江 杭州,宁波,台州

--建表
create table t_province(
	province string,
    city array(string)
)
row format delimited
fields terminated by ' '
collection items terminated by ','
;

--导入数据
load data local inpath '/data/province.txt' into table t_province;

--查询数据
select * from province;

河南 ["郑州","开封","周口","新乡"]
浙江 ["杭州","宁波","台州"]

--炸裂函数
select explode(city) from t_province;
郑州
开封
周口
新乡
杭州
宁波
台州
-- lateral  view
-- lateral view用于和split, explode等UDTF一起使用
-- 它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
select province,scity from t_province lateral view explode(city) city as scity;

河南	郑州
河南	开封
河南	周口
河南	新乡
浙江	杭州
浙江	宁波
浙江	台州
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

3.2.2 Map

-- 表数据
河南 郑州:1,开封:1,周口:2,新乡:3
浙江 杭州:1,宁波:2,台州:3
-- 建表
create table t_province(
	province string,
    items map<string,int>
)
row format delimited
fields terminated by ' '
collection items terminated by ','
map keys terminated by ':'
;
-- 导入数据
load data local  inpath '/data/province.txt' into table t_province;

-- 查询数据
select * from t_partition;

河南 {"郑州":1,"开封":1,"周口":2,"新乡":3}
浙江 {"杭州":1,"宁波":2,"台州":3}
-- 炸裂函数
select explode(items) from t_province;

郑州 1
开封 2
周口 3
新乡 4
杭州 1
宁波 2
台州 3
--lateral view explode()
select name,itemname,num from t_province
lateral view explode(items) items as itemname,num;

河南 郑州 1
河南 开封 2
河南 周口 3
河南 新乡 4
浙江 杭州 1
浙江 宁波 2
浙江 台州 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
38
39
40
41
42

3.2.3 Struct

-- 数据
张三 浙江省，杭州市，西湖区
罗某 上海市，浦东区，街道

-- 建表
create table t_province(
	name string,
    addr struct<province:string,city:string,street:string>
)
row format delimited
fields terminated by ' '
collection items terminated by ','
;
-- 导入数据
load data local inpath '/data/province.txt' into table t_province;

-- 查询
select * from t_province;

张三 {"province":"浙江省","city":"杭州市","street":"西湖区"}
罗某 {"province":"上海市","city":"浦东区","street":"街道"}

--查询
select name,addr.province,addr.city,addr.street from t_province;

张三 浙江省 杭州市 西湖区
罗某 上海市 浦东区 街道

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

4 DDL数据（data definition language）数据定义语言

4.1 创建数据库

create database if not exists db_hive ;

默认存储路径是/usr/hive/warehouse/*.db

create database db_hive2 location ‘/db_hive2.db’;

创建一个数据库，指定数据库在HDFS上存储的位置

4.2 查询数据库

show databases;

show databases like ‘db_’;//过滤显示查询的数据库

desc database db_hive;//显示数据库信息

desc database extended db_hive;//显示数据库详细信息

use db_hive;//切换数据库

4.3 删除数据库

drop database if exists db_hive;//判断数据库存在并且删除

drop database db_hive cascade;//强制删除数据库

4.4 创建表

4.4.1 管理表

默认创建的表都是所谓的管理表，也被成为内部表。这种表，Hive会控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项目定义的目录下(/user/hive/warehouse)
1

4.4.2 外部表

外部表表示Hive认为其并非完全拥有这份数据。删除该表不会删除掉这份数据，不过描述表的元数据信息会被删除掉
1

4.4.3 管理表和外部表的应用场景

每天将收集到的网站日志定期流入HDFS文件中。在外部表（原始日志表）的基础上做大量的统计分析。用到的中间表，结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。
1

4.4.4 管理表和外部表的互相转换

desc formatted student;//查询表类型

alter table student set tblproperties(‘EXTERNAL’ = ‘TRUE’);//修改内部表为外部表

alter table student set tblproperties(‘EXTERNAL’ = ‘false’);//修改外部表为内部表

tips:

​ (‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法， 区分大小写！

4.5 修改表

4.5.1 重命名表

alter table table_name rename to new_table_name;

4.5.2 增加、修改、替换列信息

• 更新列

alter table dept change column dept name desc String;//将dept的name改成String类型

• 增加

alter table dept add columns(deptdesc string);

• 替换

alter table dept replace columns(deptno string,dname string,loc string);

• ADD是代表新增一字段，字段位置在所以列后面(partition 列前)
• replace则是表示替换表中所有的字段

5 DML数据操作（data manipulation language）数据操作语言

5.1 数据导入

load data [local] inpath ‘date path’ [overwrite] into table student [partition (partcol1=val1.)]

• 上传数据

hdfs dfs -put /opt/data/student.txt /user/hive;

• 创建表

create table student(id string, name string) row format

delimited fields terminated by ‘\t’;

• 导入数据

load data inpath ‘/user/hive/student.txt’ overwrite into table student;

5.2 通过查询语句向表中插入数据

• 基本插入数据

insert into table student values(1,‘zxy’),(2,‘zhou’)

• 基本模式插入

insert overwrite table student select id,name from students where month = ‘202107’

insert into :以追加的方式插入到表或分区，原有数据不会删除

insert overwrite：会覆盖表中已存在的数据

• 多分区插入模式

from student

insert overwrite table student partition(month = ‘202107’)

select id,name where month = ‘202108’

insert overwrite table studdent partition(month = ‘202106’)

select id,name where month = ‘202109’

5.3 数据导出

5.3.1 Insert导出

• 将查询结果导出到本地

insert overwrite local directory ‘/opt/apps/student’ select * from student;

• 将查询结果格式化导出到本地

insert overwrite local directory ‘/opt/apps/student’

row format delimited

fields terminated by ‘\t’

select * from student;

• 将查询结果导出到HDFS

insert overwrite directory ‘/user/student’

row format delimited

fields terminated by ‘\t’

select * from student;

5.3.2 Hadoop命令导出到本地

• hdfs命令导出

hdfs dfs -get /user/hive/student.txt /opt/apps/student.txt

• hive shell 命令导出

hive -e ‘select * from student;’ > /opt/apps/student/student.txt

• export导出到hdfs上

export table student to ‘/user/hive/student’

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

5.3.3 sqoop导出

5.3.4 清楚表中数据(Truncate)

truncate table student;

注意： Truncate 只能删除管理表， 不能删除外部表中数据

6 查询

6.1 基本查询

6.1.1 tips

SQL语言大小写不敏感

SQL可以写在一行或者多行

关键字不能被缩写也不能分行

各子句一般要分行写

使用缩进提高可读性

6.1.2 别名

重命名一个列

便于计算

紧跟列名，也可以在列名和别名之间加入关键字‘AS’

6.1.3 算术运算符

6.1.4 常用函数

6.1.5 Limit

Limit子句用于限制返回的行数

select * from emp limit 5;

6.1.6 where语句

使用where语句，将不满足条件的过滤掉

where子句紧随from子句

select * from emp where sal in (1500,5000);

6.1.7 Like和RLike

• like

%代表零个或者多个字符

_表示一个字符

• Rlike

RLIKE可以使用正则表达式这个强大的功能

select * from emp where name RLIKE ‘[A]’;

6.2 分组

6.2.1 GROUP BY语句

GROUP BY语句经常和聚合函数一起使用，按照一个或者多个队列结果进行分组，然后对每个组执行聚合操作

6.2.2 Having语句

• having和where的不同点

where后面不能些分组函数，而having后面可以使用分组函数

having只用于group by分组统计语句

6.3 Join语句

1. 尽量避免使用子查询，但是子查询不可避免
2. 尽量避免过多的join查询，但是join查询不可避免
3. 尽量不要使用in或者not in的语法
4. 一定要使用小表驱动大表，因为hive在做查询的时候，首先缓存一部分数据（查看左边的表）。说白了就是数据量小的表尽量先查。

• 表别名的好处

使用别名可以简化查询

使用别名可以提高执行效率

交叉查询:access join: 产生笛卡尔积的查询

inner join/join

left outer join/left join

right outer join/right join

full outer join/full join

left semi join:左半连接

6.3.1 数据

表A：
id name
1	a
2	b
3	c
4	d

表B：
id name
2	b
4 	d
5	e
1
2
3
4
5
6
7
8
9
10
11
12

6.3.2 内连接(inner join)

select

A.id A_id,

A.name A_name,

B.id B_id,

B.name B_name

from

A join B

on A.id=B/.id

6.3.3 左外连接(left join)

select

A.id A_id,

A.name A_name,

B.id B_id,

B.name B_name

from

A left join B

on A.id=B/.id

6.3.4 右外连接(right join)

select

A.id A_id,

A.name A_name,

B.id B_id,

B.name B_name

from

A right join B

on A.id=B/.id

6.3.5 全连接(full join)

select

A.id A_id,

A.name A_name,

B.id B_id,

B.name B_name

from

A join B

on A.id=B/.id

6.3.6 左半连接

select

from

B left semi join A

on A.id=B.id

6.3.7 多表连接

tips:

连接n个表，至少需要n-1个条件

大多数情况下， Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。本例中会首先
启动一个 MapReduce job 对表 e 和表 d 进行连接操作，然后会再启动一个 MapReduce job 将
第一个 MapReduce job 的输出和表 l;进行连接操作。
注意：为什么不是表 d 和表 l 先进行连接操作呢？这是因为 Hive 总是按照从左到右的
顺序执行的。
优化： 当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的
话，那么只会产生一个 MapReduce job。
1
2
3
4
5
6
7

6.3.8 笛卡尔积

笛卡尔积会在下面条件下产生

• 省略连接条件
• 连接条件无效
• 所有表中的所有行互相连接

6.3.9 in

select * from A where A.id in (select B.id from B)

6.3.10 子查询

总体来说，hive的子查询和sql的子查询是一样的。子查询只能出现在from之后或者where之后

子查询对别名的识别特别严格，任何的可以取别名的地方，定义一个别名

-- 在SQL中没错，但是hive中错
select * from A where A.id = (
select B.id from B where B.id = 2
);

--在hive中使用方法
select * from A where A.id in (
select B.id from B where B.id = 2
);
1
2
3
4
5
6
7
8
9

6.4 排序

6.4.1 全局排序(Order by)

全局排序，只有一个Reducer

• 使用OEDER BY子句排序

ASC（ascend）:升序（默认）

DESC（descend）:降序

6.4.2 每个Reduce内部排序（Sort by）

桶内有序，不保证全局有序

sort by : 对于大规模的数据集order by的效率非常低。很多情况下，并不需要全局排序，此时可以使用sort by

sort by为每个reduce产生一个排序文件，每个Reduce内部进行排序，对全局结果集来说不是排序

• 设置reduce个数

set mapreduce.job.reduces=3;

• 查看设置reduce个数

set mapreduce.job.reduces;

6.4.3 分区(Distribute by)

Distribute By： 在有些情况下， 我们需要控制某个特定行应该到哪个 reducer， 通常是为
了进行后续的聚集操作。 distribute by 子句可以做这件事。 distribute by 类似 MR 中 partition
（自定义分区） ，进行分区，结合 sort by 使用。
对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute
by 的效果。
1
2
3
4
5

• tips

distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。

Hive要求distribute by语句要写在sort by语句之前

6.4.4 Cluster By

当distribute by和sort by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by功能外，还具有sort by功能，但是排序只能是升序排序，不能指定排序规则为ASC或者DESC

select * from emp cluster by deptno;

select * from emp distribute by deptno sort by deptno;

7 分区表和分桶表

7.1 分区表

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

7.2 分区表

-创建分区表
create table dept_partition (
	deptno int,
    dname string,
    loc string
)
partitioned by (day string)
row format delimited 
fields terminated by '\t';
- 导入数据
load data local inpath '/opt/apps/dept_20210725.log' into table dept_partition partition(day='20210725')  
load data inpath '/hive/scoures/dept_20210725.log' into table dept_partition partition(day='20210725')
- 查询分区
select * from dept_partition where day='20210725';
- 删除分区
alter table dept_partition drop partition(day='20210725')
- 查看分区表有多少分区
show partitions dept_partition;
- 查看分区表结构
desc formatted depta_partition;
tips:
分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。
加载数据的时候必须指定分区
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

7.3.1 上传数据后修复

• 上传数据

dfs -mkdir -p /user/dept_partition/day=20210725/hour=8

dfs -put /opt/apps/dept_20210725.log /user/dept_partition/day=20210725/hour=8

• 查询数据

select * from dept_partition where day=‘20210725’ and hour=‘8’

查询不到

• 修复数据

msck repair table dept_partition;

• 再次查询数据

select * from dept_partition where day=‘20210725’ and hour=‘8’

7.3.2 上传数据后添加分区

• 上传数据

dfs -mkdir -p /user/dept_partition/day=20210725/hour=8

dfs -put /opt/apps/dept_20210725.log /user/dept_partition/day=20210725/hour=8

• 添加分区

alter table ddept_partition add partition(day=‘20210725’,hour=‘8’)

• 查询数据

select * from dept_partition where day=‘20210725’ and hour =‘8’

7.3.3 创建文件夹后load数据到分区

• 上传数据

dfs -mkdir -p /user/dept_partition/day=20210725/hour=8

• 上传数据

load data local inpath ‘/opt/apps/dept_20210725.log’ into table dept_partiton partition(day=‘20210725’,hour=‘8’)

• 查询数据

select * from dept_partition where day=‘20210725’ and hour =‘8’

7.4 动态分区调整

7.4.1 开启动态分区

1. 静态分区：加载数据的时候指定分区的值
2. 动态分区：数据未知，根据分区的值创建分区
3. 静态+动态

• hive-site.xml

## 开启动态分区功能（默认 true，开启） 
<property>
    <name>hive.exec.dynamic.partition</name>
    <value>true</value>
</property>
##设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区， nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）
<property>
    <name>hive.exec.dynamic.partition.mode</name>
    <value>nonstrict</value>
</property>
##在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000
<property>
    <name>hive.exec.max.dynamic.partitions</name>
    <value>1000</value>
</property>
##在每个执行 MR 的节点上，最大可以创建多少个动态分区。 该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。
<property>
    <name>hive.exec.max.dynamic.partitions.pernode</name>
    <value>100</value>
</property>

##整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000
<property>
    <name>hive.exec.max.created.files</name>
    <value>100000</value>
</property>
## 当有空分区生成时，是否抛出异常。一般不需要设置。默认 false
<property>
    <name>hive.error.on.empty.partition</name>
    <value>false </value>
</property>

<property>
    <name>hive.exec.mode.local.auto.input.files.max</name>
    <value>4</value>
</property>

<property>
    <name>hive.exec.mode.local.auto</name>
    <value>true</value>
</property>

<property>
    <name>hive.exec.mode.local.auto.inputbytes.max</name>
    <value>134217728</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

7.5 分桶

桶是比分区更细粒度的划分。当我们对表中的数据进行join的时候势必回产生笛卡尔积。桶就是可以帮助我们减少笛卡尔积。

分桶表的建立不可以直接导入数据，需要建立中间表

• 设置reduce个数，即为分桶数量

set mapreduce.job.reduces=4

7.5.1 准备数据

1,zxy,man,18,java
2,zxy2,man,18,java
3,zxy3,man,18,swift
4,zxy4,man,18,java
5,zxy5,man,18,objective-c
6,zxy6,man,18,java
7,zxy7,man,18,java
8,zxy8,man,18,c#
1
2
3
4
5
6
7
8

7.5.2 建立中间表

create table stu(
	sno int,
    sname string,
    gender string,
    age int,
    subject string
)
row format delimited
fields terminated by ','
;

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

7.5.3 分桶查询

--方法一
select * from stu cluster by(sno);
--方法二
select * from t_stu distribute by(sno) sort by(sno);

1,zxy,man,18,java
4,zxy4,man,18,java

2,zxy2,man,18,java
5,zxy5,man,18,objective-c

3,zxy3,man,18,swift
6,zxy6,man,18,java

7,zxy7,man,18,java
8,zxy8,man,18,c#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

7.5.4 建立分桶表

create table t_bulk(
	sno int,
    sname string,
    gender string,
    age int,
    subject string
)
clustered by(sno) sorted by(sno) into 4 buckets
row format delimited
fields terminated by ','
;
1
2
3
4
5
6
7
8
9
10
11

7.5.5 导入分桶表

--方法一
insert into t_bulk
select * from stu cluster by(sno);
--方法二
insert into t_bulk distribute by(sno) sort by(sno desc);
1
2
3
4
5

7.5.6 采样查询

--1. 不压缩，不拉伸
select * from t_bulk tablesample(bucket 1 out of 4) -- 查询4桶中的第1桶
select * from t_bulk tablesample(bucket 2 out of 4) -- 查询4桶中的第2桶
-- 查询4桶中的第1、3桶，压缩，查询一半
select * from t_bulk tablesample(bucket 1 out of 2) 
-- 查询4桶中的第1桶一部分，拉伸，查询1/8
select * from t_bulk tablesample(bucket 1 out of 8) 

--2.取多少行
select * from t_bulk limit 4;
select * from t_bulk tablesample(4 rows);

--3.按百分比抽样
select * from t_bulk tablesample(10 percent);

--4. 按照容量大小
select * from t_bulk tablesample(11B); -- 抽样11个字节：K KB B M G T

--5. 抽取随机3行
select * from t_bulk order by rank() limit 3;

--6. 条件抽样
select * from t_source tablesample(bucket 2 out of 4) where sno = 1; -- 查询4桶中的一半并且需要sno=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

7.6 分区分桶表

需要开启非严格模式,若配置文件没有修改，可使用命令行操作

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

7.6.1 中间表

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

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

7.6.2 创建分桶分区表

create table t_part_bulk(
	id int,
    name string
)
partitioned by(sex int)
clustered by(id) into 4 buckets
row format delimited
fields terminated by ' '
;
1
2
3
4
5
6
7
8
9

7.6.3 向表中导入数据

set mapreduce.job.reduces=4;

insert into t_part_bulk partition(sex)
select  id,name,sex from stu cluster by(id)

tips:
1.分桶的桶数和reducer的个数要相同
2.产生的文件是首次运行结果一共的文件数，也就是四个桶产生四个文件，分别放在两个不同的分区目录下
1
2
3
4
5
6
7
8

8 窗口函数

8.1 窗口函数定义

​ 窗口函数又叫做开创函数，他是一种分析的函数，专门可以解决报表类型的需求分析。他专门用于计算基于组的某种聚合。对于每个组返回返回是多行，也就是聚合函数的分组只有显示一行

​ 开窗函数指定了分析函数的窗口大小，窗口的大小会根据行的变化而变化

8.2 Over函数

8.2.1 数据

姓名，购买日期，购买数目
saml,2018-01-01,10
tony,2018-01-02,15
tom,2018-02-03,23
tony,2018-01-04,29
1
2
3
4
5

8.2.2 建表

-- 建表
create table t_over(
	name string,
    date string,
    cnt int
)
row format delimited
fields terminated by ','
;
-- 本地导入数据
load data local inpath '/data/data/t_order.txt' into table zxy.t_order;

-- 查询
select *,count(*) from t_over;
--这种查询方法在hive中是存在语法错误的，因为有聚合函数的时候会进行聚合，如果聚合函数的查询中包含了字段，必须使用group by将这些字段分组才能狗查询

-- 方法一：
select *,count(*) from t_over group by name,date,cnt;

-- 方法二:
select *,count(*) over() from t_over;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

8.3 排名函数

row_number():不存在并列成绩

​ eg:1,2,3,4

rank():有并列成绩，可以不按顺序排名

​ eg:1,2,2,4

dense_rank():有并列，名次相同，有顺序

​ eg:1,2,2,3

8.3.1 建表

create table t_rank(
	sno string,
    clazz string,
    score int
)
row format delimited
fields terrminated by ','
;
1
2
3
4
5
6
7
8

8.3.2 案例

-- 分组排序
select * from t_rank group by clazz order by score; 
-- 该写法完全错误

-- 使用开窗函数
--row_number()
select clazz,sno,score,
row_number() over(distribute by clazz sort by score desc) rank 
from t_rank;
--rank()
select clazz,sno,score,
rank() over(distribute by clazz sort by score desc) rank 
from t_rank;
--dense_rank()
select clazz,sno,score,
dense_rank() over(distribute by clazz sort by score desc) rank 
from t_rank;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

8.4 分区(partition by,distribute by)

select *,count(*) over(partition by name) from t_partition;
select *,count(*) over(distribute by name) from t_partition
1
2

8.5 排序(order by,sort by)

select *,count(*) over(partition by name order by date desc) from t_order;
select *,count(*) over(distribute by name sort by date desc) from t_order;
1
2

8.6 Window子句

窗口更有细粒度的划分：
- preceding:向前
- following:向后
- current row:当前行
- unbounded:起点
- unbounded preceding:表示从前面的起点
- unbounded following:表示到后面的终点
1
2
3
4
5
6
7

8.6.1 举例

select name,date,cnt,
-- 从起点行到当前行
sum(cnt) over(partition by name order by date ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as sum1,
-- 从当前行加上同组的上一行
sum(cnt) over(partition by name order by date ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sum2,
-- 当前行加上同组的上一行，和当前行以及当前行的下一行进行聚合
sum(cnt) over(partition by name order by date ROWS BETWEEN 1 PRECEDING and 1  FOLLOWING ) as sum3,
-- 当前行到终点
sum(cnt) over(partition by name order by date ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sum1,
from 
t_order;
1
2
3
4
5
6
7
8
9
10
11

8.7 ntile函数：将结果分片编号

select
name,
date
cnt,
-- 按照name分组，然后分组内结果切为3份
ntile(3) over(partition by name) 
from 
t_order;
1
2
3
4
5
6
7
8

8.8 lead和lag

lag:返回当前数据行的上一行

lead:返回当前行数据行得下一行

select 
name,
date,
cnt,
lag(orderdate,1,'yyyy-MM-dd') over (partition by name order by date) as times
from t_order;

zzz    2018-04-08      62      yyyy-MM-dd
zzz    2018-04-09      68      2018-04-08
zzz    2018-04-11      75      2018-04-09
zzz    2018-04-13      94      2018-04-11
xxx    2018-05-10      12      yyyy-MM-dd
xxx    2018-06-12      80      2018-05-10
yyy    2018-01-01      10      yyyy-MM-dd
yyy    2018-01-05      46      2018-01-01
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

8.9 first_value和last_value

select
name,
date,
cnt,
first_value(date) over(partition by name order by date) as time1,
last_value(date) over(partition by name order by date) as time2
from 
t_order
1
2
3
4
5
6
7
8

9 其余函数

9.1 数值函数

9.2 数学函数

9.3 条件判断

9.3.1 if(expr1,expr2,expr3)

如果expr1为true，返回expr2，否则返回expr3

-- eg.1
select if(1=1,2,3)
2
-- eg.2
select if(1=2,2,3)
3
1
2
3
4
5
6

9.3.2 case…when

类似于if…else

-- e.g
select
sno,
case score
when 84 then 'good'
when 91 then 'great'
else 'other'
end
from t_edge
1
2
3
4
5
6
7
8
9

9.4 日期函数

9.5 字符串相关函数

9.6 统计函数

9.7 案例小结

9.7.1 单词统计

-- 建表
create table wordcount(
word string
)
row format delimited
fields terminated by ','
;

--本地导入数据
load data local inpath '/data/data/word.txt' into table wordcount;
1
2
3
4
5
6
7
8
9
10

9.7.2 SQL

with t1 as (
select explode(split(word,' ')) word
    from wordcount
)
select t1.word,count(1) cnts 
from t1
group by t1.word
order by cnts desc,tmp.word asc;
1
2
3
4
5
6
7
8

10 自定义函数

10.1 UDF

User Define Function:UDF

10.2 导入依赖

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

10.3 自定义生日转年龄

• 打包hive.jar

package com.zxy.bigdata.Clickhouse.pojo;

import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;

import java.util.Calendar;

/**
 * 生日格式：yyyy-MM-dd
 */
public class Birth2Age extends UDF {
    public int evaluate(String birth) {
        if (StringUtils.isEmpty(birth)) return -1;
        //1.输入年月日
        String[] ymd = birth.split("-");
        int year = Integer.parseInt(ymd[0]);
        int month = Integer.parseInt(ymd[1]);
        int day = Integer.parseInt(ymd[2]);
        //2.当前的日期
        Calendar calendar = Calendar.getInstance();
        int currentYear = calendar.get(Calendar.YEAR);
        int currentMonth = calendar.get(Calendar.MONTH);
        int currentDay = calendar.get(Calendar.DAY_OF_MONTH);
        //3.计算年龄
        int age = currentYear - year;
        if (currentMonth < month) age = age - 1;
        else if (currentMonth == month && currentDay < day) age = age - 1;
        return age;
    }
}
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

• 上传至hdfs

hdfs dfs -put /data/jars/hive.jar /common/jars/

• 新建函数

create function zxy_birth2age as 'com.zxy.bigdata.hive.Birth2Age' using jar 'hdfs://hadoop:90000/common/jars/hive.jar'

select zxy_birth2age('2017-06-25')
OK
4
1
2
3
4
5

11 Hive的JDBC连接

• 依赖

<!-- hive-jdbc -->
<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>1.2.1</version>
</dependency>

<!-- hadoop-common -->
<dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-common</artifactId>
    <version>2.8.1</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13

• 代码

package com.zxy.bigdata.clickhouse.utils

import java.sql.{Connection, DriverManager, ResultSet, Statement}

/**
 * 获取用户连接对象
 * 关闭连接
 **/
object HiveUtils {
    private val MYSQL_DRIVER = "com.mysql.jdbc.Driver"
    private val MYSQL_URL = "jdbc:mysql://192.168.130.111:3306/zxy"
    private val MYSQL_USER = "root"
    private val MYSQL_PWD = "111213"
    
    /**
     * 创建连接对象
     */
    def getHiveConnection():Connection = {
        var connection:Connection = null
        try{
            Class.forName(MYSQL_DRIVER)
            connection = DriverManager.getConnection(MYSQL_URL,MYSQL_USER,MYSQL_PWD)
        }catch {
            case e:Exception => print("hive创建连接异常")
        }
        connection
    }
    
    /**
     * 关闭Connection对象
     */
    def close(connection: Connection):Unit = {
        if(connection != null) connection.close()
    }
}
/**
 * 获取hive连接对象，连接数据库
 * 查询字段并输出
 */
object TestHive{
    def main(args: Array[String]): Unit = {
        val connection: Connection = HiveUtils.getHiveConnection()
        val statement: Statement = connection.createStatement()
        val sql = "select * from zxy.stu"
        val resultSet: ResultSet = statement.executeQuery(sql)
        while (resultSet.next()){
            val id: Int = resultSet.getInt("id")
            val name: String = resultSet.getString("name")
            println(s"id:${id},name:${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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

12 Hive的存储方式解析

12.1 row format delimited

-- row format
设置用什么样的inputformat去读取数据
-- delimited
表示使用默认的TextInputFormat去读取数据，默认识别回车作为换行
-- 表的format类型查看
desc extended t_rank;
1
2
3
4
5
6

12.2 fields terminated by ‘,’

-- fields terminated by ','
表示用什么SerDe类去解析一行中的数据，默认的识别字段是使用^A

- SerDe
- serilizable ：序列化
- deserilizable : 反序列化
1
2
3
4
5
6

12.3 SerDe类型

12.3.1 常用的SerDe

- RegEx : 数据要按照符合你设置的正则表达式方式进行切割
- JSON : json格式的数据
public Person {
    String name;
    int age;
}
new Person("zxy",34)
{"name":"zxy","age":6}

- CSV : 默认逗号作为分割
- TSV ：默认tab作为分割
1
2
3
4
5
6
7
8
9
10
11

12.3.2 CSV测试

• 默认方式存储(逗号)

-- 数据类型
1,z
2,x
3,y
-- 建表
create table t_csv1 (
	sno int,
    sname string
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
stored as textfile;
--导数据
load data local inpath '/data/data/stu.txt' into table t_csv1;
-- 查询
select * from t_csv1;

1 z
2 x
3 y
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

• 修改存储方式(空格)

-- 数据类型
1 z
2 x
3 y
-- 建表
create table t_csv2 (
	sno int,
    sname string
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties (
	"separatorChar" = " ",
    "quoteChar" = "\"",
    "escapeChar" = "\\"
)
stored as textfile;
--导数据
load data local inpath '/data/data/stu.txt' into table t_csv2;
-- 查询
select * from t_csv1;

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

12.3.3 Json测试

• 导入第三方json解析工具

hive (zxy) > add jar /data/jars/json-serde-1.3.9-SNAPSHOT-jar-with-dependencies.jar;

• json解析

-- json类型一
{
   "uid":"1",
   "uname":"zxy",
   "age":"6"
}
-- 建表
create table t_json(
uid int,
    uname string,
    age int
)
row format serde 'org.openx.data.jsonserde.JsonSerDe'
stored as textfile;

-- json类型二
{
"uname":"zxy",
"score":
	{
	   "chinese":[98,87,82],
	   "math":[100,98,76],
	   "english":[100,100,100]
	}
}
-- 建表
create table t_json(
uname string,
    score map<string,array<int>>
)
row format serde 'org.openx.data.jsonserdde.JsonSerDe'
stored as textfile;
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

12.3.4 RegEx

• 数据

1||z
2||x
3||y
1
2
3

• 处理思路

-- 正常的思路
create table t_reg1(
id string,
name string
)
row format delimited
fields terminated by '||';


-- 我们使用正则的serde
create table t_reg1(
id string,
name string
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "(.*)\\|\\|(.*)",
  "output.format.string"='%1$s %2$s'
)
STORED AS TEXTFILE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

12.4 保存类型

12.4.1 不同存储类型

- TEXTFILE:普通文本存储，不压缩，可以直接看，坏处是文件大
- SEQUENCEFILE:Hive为用户提供的一种二进制存储，本身就是压缩，坏处是不能使用load命令
- REFILE：Hive提供的行列混合存储的格式，会将数据的行列存储到一块也是压缩，查询效率比较高，坏处是不能使用load
- ORC：答题的性质和REFILE一样，支持事务，但不能使用用load
1
2
3
4

tips:

不能直接使用load的需要使用查询插入的方法

12.5 Hive数据导出

1.导出到hdfs
insert overwrite directory '/common/hive_table'
row format delimited fields terminated by ',';
2.导出到本地文件系统
insert overwrite local directory '/common/hive_table'
row format delimited fields terminated by ',';
3.hive表-->hive表
1
2
3
4
5
6
7

13 索引和视图

13.1 定义

-- 索引
	在传统的RDBMS中，索引是最基本的优化查询速度的一种方式。但是在hive中其实索引不太实用。
-- 视图
	它是逻辑中的表，不是真正存在或者理解不能保存数据，数据都是依赖于其他的表。不能往视图中年插入数据。
1
2
3
4

13.2 索引测试

-- 建立一张索引
create table t_idx1
as
select * from t_table1;
-- 建立索引
create index idx1 on table t_index1(sno)
as 'compact' with deferred rebuild;
1
2
3
4
5
6
7

13.3 视图测试

create view v1
as
select id,name from stu;
1
2
3

14 Hive命令

14.1 hive -e

执行sql语句

[root@hadoop /]# hive -e 'select * from stu'
1

14.2 hive -f

直接执行hive的脚本文件

[root@hadoop /]# hive -f /opt/scripts/hive.hql
1

目录

1 Hive基本概念
1.1 什么是Hive
1.2 Hive的优缺点
1.3 Hive运行机制
1.4 Hive和数据库比较
2 Hive安装
2.1 安装JDK
2.2 安装Hadoop
2.3 安装MySQL
2.4 安装Hive
2.5 Hive日志
3 Hive数据类型
3.1 基本数据类型
3.2 集合数据类型
4 DDL数据（data definition language）数据定义语言
4.1 创建数据库
4.2 查询数据库
4.3 删除数据库
4.4 创建表
4.5 修改表
5 DML数据操作（data manipulation language）数据操作语言
5.1 数据导入
5.2 通过查询语句向表中插入数据
5.3 数据导出
6 查询
6.1 基本查询
6.2 分组
6.3 Join语句
6.4 排序
7 分区表和分桶表
7.1 分区表
7.2 分区表
7.3 把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式
7.4 动态分区调整
7.5 分桶
7.6 分区分桶表
8 窗口函数
8.1 窗口函数定义
8.2 Over函数
8.3 排名函数
8.4 分区(partition by,distribute by)
8.5 排序(order by,sort by)
8.6 Window子句
8.7 ntile函数：将结果分片编号
8.8 lead和lag
8.9 first_value和last_value
9 其余函数
9.1 数值函数
9.2 数学函数
9.3 条件判断
9.4 日期函数
9.5 字符串相关函数
9.6 统计函数
9.7 案例小结
10 自定义函数
10.1 UDF
10.2 导入依赖
10.3 自定义生日转年龄
11 Hive的JDBC连接
12 Hive的存储方式解析
12.1 row format delimited
12.2 fields terminated by ‘,’
12.3 SerDe类型
12.4 保存类型
12.5 Hive数据导出
13 索引和视图
13.1 定义
13.2 索引测试
13.3 视图测试
14 Hive命令
14.1 hive -e
14.2 hive -f