Hive技术

Hive简介

是facebook开源，捐献给了apache组织，作为apache组织的顶级项目(hive.apache.org)
是一个离线数仓
是一个基于大数据技术的数据仓库(DataWareHouse)技术
需要安装MySQL，将MetaStore元数据信息存入MySQL中

metastore（mysql）中：
存储的元数据信息是，表名—》对应的HDFS目录，列名（字段名）—》对应的HDFS文件中的哪一列

在Hive中创建的数据库，创建的表，向表中添加的数据，最终都是存储在HDFS中的，所以Hive中的表对应HDFS中的目录(文件夹)，Hive表中的数据对应HDFS中目录下的文件

Hive SQL（HQL）执行流程：HQL转换成MR程序执行

Hive的安装

提前安装好MySQL
百度搜索Apache归档，搜索Hive，下载相应版本的hive的tar.gz包

Hive客户端工具

第一种方式：

# 本地模式启动 【管理员模式】
# 启动hive服务器，同时进入hive的客户端。只能通过本地方式访问。
[root@hadoop10 ~]# hive
Logging initialized using configuration in jar:file:/.../hive1.2.1/lib/hive-common-1.2.1.jar!/hive-log4j.properties
hive>

第二种方式：

# 启动hive的服务器，可以允许远程连接方式访问。
// 前台启动
[root@hadoop10 ~]# hiveserver2 
// 后台启动
[root@hadoop10 ~]# hiveserver2 &

# 启动客户端--beeline客户端
[root@hadoop10 ~]# beeline  
beeline> !connect jdbc:hive2://hadoop10:10000
回车输入mysql用户名
回车输入mysql密码

第三种方式：—DBeaver客户端(图形化界面)

# 1: 解压
# 2: 准备dbeaver连接hive的依赖jar
	hadoop-common-2.9.2
	hive-jdbc-1.2.1-standalone
# 3:启动

第四种：—JDBC Java代码的方式操作Hive

<!-- 导入依赖 -->
<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>1.2.1</version>
</dependency>
<dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-common</artifactId>
    <version>2.9.2</version>
</dependency>

//JDBC操作Hive
public static void main(String[] args) throws Exception {
    BasicConfigurator.configure();//开启日志
    //加载hive驱动
    Class.forName("org.apache.hive.jdbc.HiveDriver");
    //连接hive数据库
    Connection conn = DriverManager.getConnection("jdbc:hive2://hadoop10:10000/aaa","root","admins");
    String sql = "select * from t_user1";
    PreparedStatement pstm = conn.prepareStatement(sql);
    ResultSet rs = pstm.executeQuery();
    while(rs.next()){
        String id = rs.getString("id");
        String name = rs.getString("name");
        int age = rs.getInt("age");
        System.out.println(id+":"+name+":"+age);
    }
    rs.close();
    pstm.close();
    conn.close();
}

Hive语法

一、数据类型
四类：
primitive
array
map
struct

primitive（原始类型）：

Hive数据类型	字节	备注
TINYINT	1	byte整型
SMALLINT	2	short整型
INT	4	int整型
BIGINT	8	long整型
BOOLEAN		boolean布尔型
FLOAT	4	float浮点型
DOUBLE	8	double浮点型
STRING		字符串无限制
VARCHAR		字符串varchar(4000)最长4000
CHAR		字符串char(20)定长20
BINARY		二进制类型
TIMESTAMP		时间戳类型(更精确)
DATE		日期类型

array（数组类型）：

 # 建表
create table t_tab(
	score array<float>，
    字段名 array<泛型>
);

map（key-value类型）：MAP<primitive_type,data_type>

# 建表
create table t_tab(
	score map<string,float>
);

struct（结构体类型）：STRUCT<col_name:data_type,…>

# 建表
create table t_tab(
	info struct<name:string,age:int,sex:char(1)>，
    列名 struct<属性名:类型,属性名:类型>
);

创建表

自定义分隔符[重要]

create table t_person(
    id string,
    name string,
    salary double,
    birthday date,
    sex char(1),
    hobbies array<string>,
    cards map<string,string>,
    addr struct<city:string,zipCode:string>
) row format delimited							-- 开始自定义分隔符
fields 				terminated by ','			-- 列与列之间的分隔符
collection items 	terminated by '-'			-- 数组、struct的属性、map的kv和kv之间的分隔符
map keys 			terminated by '|'			-- map的k与v之间的分隔符
lines 				terminated by '\n';			-- 行数据(行与行)之间的分隔符 ps：默认就是'\n' 所以可以不写

row(行) format(格式化) delimited(限定) ：所以这句话理解为：每一行数据格式化的规则限定
fields(列) terminated(结束，停止) by ‘,’ ：每一列以什么结束，所以是定义列与列之间的分隔符
collection items 、 map keys，如果定义的表中没有这两个类型的字段，则可以不写

JSON分隔符

本地json文件：

{"id":1,"name":"zhangsan","sex":0,"birth":"1991-02-08"}
{"id":2,"name":"lisi","sex":1,"birth":"1991-02-08"}0000

一、添加格式解析器的jar(本地客户端命令)

# 在hive的客户端执行(临时添加jar到hive的classpath，有效期本链接内)
add jar /你的hive目录/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar

# 补充：永久添加，Hive服务器级别有效。
1. 将需要添加到hive的classpath的jar，拷贝到hive下的auxlib目录下，
2. 重启hiveserver即可。

二、建表

create table t_person2(
    id string,
    name string,
    sex char(1),
    birth date
)row format serde 'org.apache.hive.hcatalog.data.JsonSerDe';

三、加载本地数据

load data local inpath '/.../person.json' into table t_person2;

正则分隔符

数据：access.log

INFO 192.168.1.1 2019-10-19 QQ com.baizhi.service.IUserService#login
INFO 192.168.1.1 2019-10-19 QQ com.baizhi.service.IUserService#login
ERROR 192.168.1.3 2019-10-19 QQ com.baizhi.service.IUserService#save
WARN 192.168.1.2 2019-10-19 QQ com.baizhi.service.IUserService#login
DEBUG 192.168.1.3 2019-10-19 QQ com.baizhi.service.IUserService#login
ERROR 192.168.1.1 2019-10-19 QQ com.baizhi.service.IUserService#register

建表语句

create table t_access(
    level string,
    ip string,
    log_time date,
    app string,
    service string,
    method string
)row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'					--正则表达式的格式转化类
with serdeproperties("input.regex"="(.*)\\s(.*)\\s(.*)\\s(.*)\\s(.*)#(.*)");	--(.*) 表示任意字符 \\s表示空格

导入数据

load data local inpath '/opt/access.log' into table t_access;

表数据转存或导入

# 1.将文件数据导入hive表中
	load data [local] inpath '文件的路径' [overwrite] into table 表。
	-- local 若写了代表文件路径是Linux路径，不写则默认HDFS路径，
	-- overwrite若写了代表是覆盖表，不写则默认追加数据
	# 小细节：load后，hdfs上的文件会被移动到hive默认的目录下，原目录下的文件就没有了
	# 		  如果想让源文件依然在，则用第四种方法较好
# 2.直接将查询结果，放入一个新创建的表中。(执行查询的创建)
	create table 表名 as select语句...
		1. 执行select语句
		2. 创建一个新的表，将查询结果存入表中。
		3. 查询出来的表有几个字段，新创建的表就有几个字段
# 3.将查询结果，导入已经存在表。
	insert into 表 
	select语句...
	
	insert overwrite table 表 
	select语句...
# 4.将HDFS中已经存在文件，导入新建的hive表中
	create table Xxx(
		...
	)row format delimited 
    fields terminated by ','
    location 'hdfs的表数据对应的目录'

HQL语法

执行顺序：

from --> where --> group by --> having --> select --> order by --> limit

select hobbies[0],card['key'],addr.city from t_person;
//第一个是数组类型，第二个是map类型，第三个是结构体类型（对象类型）

select * from t_person limit 5; //top5  hive中limit 后只能写一个数字，也就是只能从0开始查前n条，不能自定义起始下标

select distinct sex,addr.city from t_person; //去重
select distinct(sex) from t_person;

函数

可以通过show functions语句，查询展示所有函数

如何看参数是形参还是实参？
例如 select length(name) from t_user;
①定义函数时，参数为形参，调用函数时，参数为实参
②name是一个变量，将t_user表中每一行中name字段的值赋给name，所以为实参

单行函数

一进一出(一对一)

部分单行函数：

array_contains(列,值)
--数组字段是否包含某个某个值,返回值为true/false

length(列)

concat(列)

to_data()

year()

month()

date_add()

聚合函数（组函数）

多进一出(多对一)

部分聚合函数：

count()
--若括号中写的列名，当这一列中有空值时，则不统计此行
max()

min()

avg()

sum()

炸裂函数

一进多出(一对多)

--写法一：炸裂函数写在select后面
select explode(hobbies) hobby from t_person;

--写法二：炸裂函数写在from后面
select * from t_person t1 lateral view explode(hobbies) t2 as hobby;
--先查t_person表，起个别名t1，lateral view是用来连接上炸裂函数的结果，
--t2是给炸裂结果起个别名，as hobby 是给炸裂结果包含的那一列起个别名

lateral view
为指定表的边缘拼接一个列（炸裂结果）
像是个表连接，但不是表连接（类似表连接）
语法：from 表 lateral view explode(数组字段) 炸裂结果别名 as 字段名；

分组

分组后，select 后面只能写分组条件中的列，还有组函数
还可以这样写：
select id , name , 1 , ‘a’ from t_person;
其中id和name是变量，1和 ‘a’ 是常量
常量都有哪些： 数字， 字符串

相关关键字：
group by
having

子查询

表嵌套查询

select distinct t.hobby from
(select explode(hobbies) as hobby from t_person) t ;
--给表起别名的t必须写

行列相转

一、collect_list
是一个组函数，分组之后，可以将属于该组的数据的某列值存储在一个集合中（数组）

select username,collect_list(video_name)
from t_video group by username;

二、collect_set
也是一个组函数，作用等同于collection_list，
但是，合并后的数组中不能有重复元素

select username,collect_set(video_name)
from t_video group by username;

三、concat_ws
是一个单行函数，将一个数组中的元素，通过指定分隔符拼接成字符串
因为MySQL不支持数组，所以有时候Hive需要将数组转成字符串

select id,name,concat_ws('_',hobbies)
from t_person;

select username,concat_ws(’,’,collect_set(video_name))
from t_video group by username;
–疑问：select后不是只能写组函数嘛，concat_ws是一个单行函数为什么可以写呢？
–原因：collect_set是一个组函数，group by username 分组后，
–username 分组后由多个变成一个，但video_name或者别的字段没有被分组就会有多个，
–经过collect_set（多对一）处理后，由多行变成一行，
–再经过concat_ws(单行函数一对一)处理，相当于传给它一个值，它再返回一个值，也是OK的
–所以说这个地方没有问题，是因为在内部已经用组函数collect_set将数据处理成一行数据了

全局排序和局部排序

• 全局排序 —— order by
  若是全局排序则只有一个reduce，那么这一个reduceTask就会拿到所有的mapTask的数据，
  最终再排序则是对所有数据进行排序

使用order by就代表只有一个reduce，就算设置了多个reduce也没有意义

• 局部排序 —— sort by
  若是局部排序则有多个reduce，每个reduceTask拿到对应mapTask的数据，进行局部排序

set mapreduce.job.reduce = 1 (默认) 若为1，那么只有一个reduce，则sort by 和order by没有区别

set mapreduce.job.reduce = 2
sort by是指对分区后的每一个分区内进行排序，是一部分数据的排序，所以是局部排序

将SQL的执行结果写出到本地文件
SQL：insert overwrite local directory ‘/tmp/data/sortby’ select * from t_person sort by salary desc;
由于向文件中输出数据时，没有指定分隔符，它也是有默认分隔符的，不过不太常用(^A)类似这种的组合键

• distribute by （分区，只是分区，不是分区排序）
  sort by 一定需要和 distribute by 配合使用
  若不写distribute by，那么会根据底层逻辑分区，若写了distribute by那么我们可以自定义分区，
  比如根据性别分区，将男性分入一个reduceTask，女性分入一个reduceTask，分别排序

select * from t_person distribute by sex sort by salary desc;
select * from t_person distribute by sex order by salary desc;--意义不大，最终还是全局排序

Hive中表分类

管理表(Managed_Table)

由Hive全权管理的表

所谓的管理表指hive是否具备数据的管理权限，如果该表是管理表，当用户删除表的同时，hive也会将表所对应的数据删除，因此在生产环境下，为了防止误操作，带来数据损失，一般考虑将表修改为非管理表-外部表

总结：Hive的管理，表结构，hdfs中表的数据文件，都归Hive全权管理。---- hive删除管理表，HDFS对应文件也会被删除。
缺点：数据不安全。

外部表(External_Table)

如果该表是外部表，删除表的同时，只会删除mysql中元数据信息，不会删除HDFS的目录和文件数据
主要看两种表：TBLS表 、SDS表

--# 创建外部表
--1. 准备数据文件personout.txt
--2. 上传至hdfs中，该数据文件必须被放在一个单独的文件夹内。该文件夹内的数据文件被作为表数据
--3. 创建表: create external table
--	 在最后使用location 指定hdfs中数据文件所在的文件夹即可。
--建表语法和建管理表相比多了一个单词，create external table 表名
create external table t_personout(
    id int,
    name string,
    salary double,
    birthday date,
    sex char(1),
    hobbies array<string>,
    cards map<string,string>,
    addr struct<city:string,zipCode:string>
)row format delimited
fields terminated by ',' --列的分割
collection items terminated by '-'--数组 struct的属性 map的kv和kv之间
map keys terminated by '|'
lines terminated by '\n'
location '/file';--改变这张表的数据存储位置，写在哪儿就是哪儿，不再是默认的位置

①外部表删除后，不可以再执行外部表的select
外部表删除后，HDFS还有对应的目录和数据文件，若不想要了，则通过
②hdfs dfs -rm -r /…/t_personout
③如果是误删除了外部表，HDFS还有对应的目录和数据文件，怎么办
只需要重新运行建表语句即可，不需要重新导入数据，可以直接查询

Hive可以先有数据，再有表，通过建表罩在已经存在的数据上

疑问：通过Hive建出来的表和数据，数据文件的后缀是啥？
答：.txt是可以的

分区表（可以是管理表，也可以是外部表）

将表按照某个列的一定规则进行分区存放，减少海量数据情况下的数据检索范围，提高查询效率；

举例：电影表、用户表

分区方案：按照用户区域、电影类型

应用：依据实际业务功能，拿查询条件的列作为分区列来进行分区，缩小MapReduce的扫描范围，提高MapReduce的执行效率，

总结：

table中的多个分区的数据是分区管理

1：删除数据按照分区删除。如果删除某个分区，则将分区对应的数据也删除(外部表，数据删除，数据文件依然在)。

2：查询统计，多个分区被一个表管理起来。

   select * from 表 where 分区字段为条件。

--创建分区表
create external table t_user_part(
	id string,
	name string,
	birth date,
	salary double
)partitioned by(country string,city string)--指定分区列,按照国家和城市分区。
row format delimited
fields terminated by ',' 
lines terminated by '\n';

<!--
0.是不是分区表就看有没有 partitioned by()
1.分区字段会在表目录下创建成子目录,多个分区条件则是多层目录
2.分区指定的字段其实也是表中的字段，所以t_user_part表中有6个字段
3.分区表的优势：如果查询条件中包含分区字段，底层只会检索该分区目录下的数据，提高查询效率
4.通过普通字段查询依然可以检索所有分区下的数据，但是分区表的优势没有体现出来
-->




--导入数据
# 导入china和bj的数据
load data local inpath "/opt/bj.txt" into table t_user_part partition(country='china',city='bj');
# 导入china和heb的数据
load data local inpath "/opt/tj.txt" into table t_user_part partition(country='china',city='tj');

--查看分区信息
show partitions t_user_part;

--使用分区查询：本质上只要
select * from t_user_part where city = 'bj'

表分类

1. 管理表
   hive中table数据和hdfs数据文件都是被hive管理。
2. 外部表–常用–hdfs文件安全。
   hive的table数据，如果删除hive中的table，外部hdfs的数据文件依旧保留。
3. 分区表–重要。
   将table按照不同分区管理。
   好处：如果where条件中有分区字段，则Hive会自动对分区内的数据进行检索(不再扫描其他分区数据)，提高hive的查询
   效率。

用户自定义函数

查询所有系统函数
show functions；
查看某个函数的详细使用信息
desc function 函数名;

1. 首先要导入Hive依赖

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

2. 配置maven的打包环境

<properties>
    <!--解决编码的GBK的问题-->
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<build>
		<!-- 打包名字 -->
        <finalName>funcHello</finalName>
</build>

用户自定义函数(UDF)

UDF(User-Defined-Function)

3. 定义一个类继承UDF
   定义一个或多个叫evaluate的方法，在方法内部写自定义函数的实现

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

public class HelloUDF extends UDF {
    // 方法名必须叫evaluate
    public String evaluate(String s1,String s2){   //这里参数就是自定义函数的参数个数以及类型
        return s1 + "-" + s2;
    }
}

4. 接下来打包，上传到Linux中，导入到函数库中

# 在hive命令中执行
add jar /opt/data/funcHello.jar; # hive session级别的添加
delete jar /opt/data/funcHello.jar; # 如果重写，记得删除，删除的是当前环境下的jar

-- 创建的函数分为临时函数和永久函数
create [temporary] function hello as "function.HelloUDF"; # temporary是会话级别，也就是临时函数
# 删除导入的函数
drop [temporary] function hello;

5. 查看函数并使用

-- 查看函数
desc function hello;
desc function extended hello;
-- 使用函数进行查询
select hello(userid,cityname) from logs;

import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.LongWritable;

@UDFType(deterministic = false) //标记该函数不是确定性函数
//输入确定，输出确定的函数，false，因为该函数没有输入，输出结果也会变化。 
public class NumberUDF extends UDF {

	//运行这个select语句，每运行一行数据，调用一次hello方法，但底层中自定义对象只创建一次
    private long index = 0;
    
    public long evaluate(){
        index++;
        return index;
    }
}

@UDFType(deterministic = false) //deterministic = true 是默认值
确定性函数 ：当传入的参数不改变时，返回值也不会改变，就是一个确定性函数
目的：优化，在参数不改变的情况下，直接使用上次的处理结果
不确定性函数 ：当传入的参数有没有改变或者一直没有传参，返回值都可能不相同
目的：在参数不改变的情况下，返回值也不一样

用户自定义聚合函数(UDAF)

UDAF(User- Defined Aggregation Funcation)

用户自定义炸裂函数(UDTF)

UDTF(User-Defined Table-Generating Functions)

需求：由于系统自带的炸裂函数只能处理数组或者map类型的参数，当我们想对字符串进行炸裂时，就需要我们自定义炸裂函数了

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

import java.util.ArrayList;
import java.util.List;

public class MyUDTF extends GenericUDTF {
    private ArrayList<String> outList = new ArrayList<String>();

    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
    	//创建两个List集合，一个存储炸裂后 列的 数据类型，一个存储 列的别名
        //1.定义输出数据的列名和类型
        List<String> fieldNames = new ArrayList<String>();//列名
        List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();//类型
        //2.添加输出数据的列名和类型
        fieldNames.add("hahaha");//列名
        //fieldNames.add("heiheihei");第二列列名
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);//类型
        //fieldOIs.add(PrimitiveObjectInspectorFactory.javaIntObjectInspector);  这个是int类型
        
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }
    /**
     *	这里为什么要定义两个List集合，而不是直接给传两个参数（列类型、列名）呢？
     *	答：因为炸裂函数的执行结果可以包含多列，所以要把所有列的列名起好，把所有列的列类型都要定义好。
     */

	
    @Override
    public void process(Object[] args) throws HiveException { 
	    //调用炸裂函数就会执行这个process方法
	    //形参Object[] 里面存储的是调用炸裂函数时传递的参数
        //1.获取原始数据
        String arg = args[0].toString();
        //2.获取数据传入的第二个参数，此处为分隔符
        String splitKey = args[1].toString();
        //3.将原始数据按照传入的分隔符进行切分
        String[] fields = arg.split(splitKey);
        //4.遍历切分后的结果，并写出
        for (String field : fields) {
            //集合为复用的，首先清空集合
            //也可以在for循环内new一个新集合或者数组
            outList.clear();
            //将每一个单词添加至集合
            outList.add(field);
            //outList.add(field.length()); 每一条数据（每一行内）第二列的值
            //将集合内容写出
            forward(outList);//forward(Object o)是父类的一个方法
            //之所以是List集合，是因为输出的结果可能是多列（炸裂函数的炸裂结果是多列），每一行的元素有多个的情况
            //也可以是数组
        }
    }

    @Override
    public void close() throws HiveException {
    }
    
}