hive介绍

Hive

1、Hive基本概念

1.1 hive简介

Hive：由Facebook开源用于处理海量结构日志的数据统计工具

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

Hive本质：将HQL转换为MapReduce程序

（1）Hive处理的数据存储在HDFS

（2）Hive分析数据底层的实现是MapReduce

（3）执行程序过程在Yarn

1.2 Hive的优缺点

1.2.1 优点

（1）操作接口采用类SQL语法，提供快速开发的能力（简单，容易上手）。

（2）避免了去写MapReduce，减少开发人员的学习成本。

（3)Hive的中心延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合。

（4）Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高

（5）Hive支持用于自定义函数，用户可以根据自己的需求来实现自己的函数。

1.2.2 缺点

1）Hive的HQL表达能力有限

​ （一）迭代算法无法表达

​ （二）数据挖掘方面不擅长 ，由于MapReduce数据处理流程的限制，效率更高的算法却无法实现。

2）Hive的效率比较低

​ （一） Hive自动生成的MapReduce作业，通常情况下不够智能化

​ （二）Hive调优比较困难，粒度较粗

1.3 Hive架构原理

1）元数据:Metastore

元数据包括：表名、表所属的数据库(默认是default)、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等

默认存储在自带的deby数据库中，推荐使用MySQL存储Metastore

2）Hadoop

使用HDFS进行存储，使用MapReduce进行计算

3）驱动类：Driver

（1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST,这一步一般都用第三方工具库完成，比如antlr:对AST进行语法分析，比如表是否存在，字段是否存在，SQL语义是否有误。

（2）编译器（Physical Plan）：将AST编译生成逻辑执行计划

（3）优化器（Query Optimizer）：对逻辑执行计算进行优化

（4）执行器（Execution）：将逻辑执行计划换成可以运行的物理计划，对于Hive来说，就是MR/Spark。

1.4 Hive与MySQL的的比较

• Hive采用了类SQL的查询语言HQL（hive query language）。除了HQL之外，其余无任何相似的地方。Hive是为了数据仓库设计的。
• 存储位置：Hive在Hadoop上；Mysql将数据存储在设备或本地系统中；
• 数据更新：Hive不支持数据的改写和添加，是在加载的时候就已经确定好了；数据库可以CRUD；
• 索引：Hive无索引，每次扫描所有数据，底层是MR，并行计算，适用于大数据量；MySQL有索引，适合在线查询数据；
• 执行：Hive底层是MarReduce；MySQL底层是执行引擎；
• 可扩展性：Hive：大数据量，慢慢扩去吧；MySQL:相对就很少了

2、Hive数据类型

2.1 基础数据类型

Hive数据类型	java数据类型	长度	例子
TINYINT	byte	1byte有符号整数	20
SMALINT	short	2byte有符号整数	20
INT	int	4byte有符号整数	20
BIGINT	long	8byte有符号整数	20
BOOLEAN	Boolean	布尔类型，true或者false	True、False
FLOAT	float	单精度浮点数	3.14159
DOUBLE	double	双精度浮点数	3.14159
STRING	string	字符串类型，可以指定字符集，可以使用单引号或者双引号	“for the book”‘now is the time’
TIMESTAMP		时间类型
BINARY		字节数组

对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能什么其中最多存储多少个字符，理论山他可以存储2GB的字符数。

2.2 集合数据类型

数据类型	描述	语法示例
STRUCT	和c语言中的struct类似，都可以通过“点”符号访问元素内容。	struct（）例如struct< street:string,city：string>
MAP	MAP是一组键值对元组集合，使用数组表示可以访问数据。	MAP()例如：map<string,int>
ARRAY	数组是一组具有相同类型和名称的变量的集合，这些变量成为数组的元素，每隔数组元素都有一个编号，编号从零开始。	ARRAY（）例如array< string >

Hive有三种复杂数据类型array，map和struct。array和map于java中的array类型允许任何层次的转换。

2.3 类型转化

Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型,TINYINT会自动转换为INT类型，但是Hive不会进行反向转化。例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型。他会返回错误，除非使用CAST操作。

1）隐式类型转换规则如下

（1）任何整数类型都可以隐式转换成为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转为成为BIGINT。

（2）所有整数类型，float和string类型（特值数字类型的字符串）都可以隐式转换成为doule类型

（3）tinyint，smallint，int都可以转换为float

（4）Boolean类型不可以转换为任何其他类型

2）可以使用CAST操作显示进行数据类型转换

例如CAST(‘1’ as int)将字符串’1’转换成为整数1；如果强制类型转换失败，如执行CAST(‘X’ as int),表达式返回值为NULL。

0: jdbc:hive2://hadoop:10000> select CAST('1' as int )+1;
+------+
| _c0  |
+------+
| 2    |
+------+
1 row selected (0.374 seconds)
0: jdbc:hive2://hadoop:10000> select '1'+1;
+------+
| _c0  |
+------+
| 2.0  |
+------+
1 row selected (0.183 seconds)

3、Hive的启动

Hive启动前，要先启动MetaStore和hiveServer2

# 将metastore和server2推到后台日志里面
nohup hive --service metastore>/usr/local/hive-3.1.2/logs/metastore.log 2>&1 &
nohup hive --service hiveserver2>/usr/local/hive-3.1.2/logs/hiverserver2.log 2>&1 &

Hive的启动方式有两种

1、输入hive，即可进入

root@hadoop:/usr/local/hive-3.1.2# hive
Hive Session ID = 7175ed74-27e3-4d99-8d54-209e3af1fc64

Logging initialized using configuration in file:/usr/local/hive-3.1.2/conf/hive-log4j2.properties Async: true
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Hive Session ID = 5188d63f-36db-44d2-87b6-66821ce5fed6
hive (default)> 

2、输入 beeline -u jdbc:hive2://hadoop:10000 -n root进行启动，其中这种方法更加友好，更贴近日常使用

root@hadoop:/usr/local/hive-3.1.2# beeline -u jdbc:hive2://hadoop:10000 -n root
Connecting to jdbc:hive2://hadoop:10000
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.2 by Apache Hive

将数据上传到HDFS中，有两种方法

1、Hive的方式：load data local inpath ‘/usr/local/hive-3.1.2/datas/test.txt’ into table person;

2、Hadoop上传文件方式：hadoop fs -put hadoop fs -put /usr/local/hive-3.1.2/datastest.txt /hive/warehouse/person

select name,friends[0],childrens['xiao song'],address.street from person;
# 字符，数组，map，结构体操作

4、DDL操作

4.1 建库语句

create database [if not exists] --database_name  指定库名
[COMMENT database commment]-- 指定库的描述信息
[Location hdfs path] -- 指定库在hdfs中的对应目录
[WITH DBPROPERTUES] -- 指定库的属性信息

建库操作

create database if not exists mydb
comment 'this is my first db'
location '/mydb'
with dbproperties('dbname'=mydb,'createtime'=2022-12-26)

元数据解释

在Hive中创建的库或者表，在HDFS中都有一个对应的目录，库或者表与目录的映射关系维护到MYSQL中的表中。

建库或者建表的时候，可以通过location指定库和表在HDFS 的映射目录。如果指定，则使用指定的目录；如果不指定，库默认映射的目录是**/hive/warehouse/库名.db**（根据自己配置的目录有关）,表默认映射的目录为 表所在的库映射的目录/表名。

4.2 查询数据库

显示数据库

show databases;

过滤显示查询的数据库

show databases like 'db_hive*';

查看数据库详情

desc database db_hive;  --输入数据库名字

显示数据库详细信息 extended

desc database extended db_hive;

切换当前数据库

user db_hive;

4.3 修改数据库

用户表可以使用 alter database 命令为某个数据库的dbproperties设置键值对属性值，来描述这个给数据库的属性信息。数据库的其他元素据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

alter database db_hive set dbproperties('createtime=20221227');
-- 在Hive中查看修改结果
desc database extended db_hive;

4.4 删除数据库

drop database db_hive;  -- 数据库中不能有表
-- 强制删除，里面有表的话，可以使用cascade
drop database db_hive cascade;

if not exists 建库建表时可以加上 ，表示如果创建的库或者表不存在，那进行创建，如果已经存在，则不创建

if exists 删库删表时可以加上，表示如果删除的库或者表存在。若删除，则不存在，则不删除。

4.5 建表语句

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  -- 指定表名，[EXTERNAL]表示外部表，如果不加，则创建内部表（管理表）
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])] -- 指定列名，列类型，描述列信息
[COMMENT table_comment] -- 指定表的描述信息
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] --指定分区列名 列类型 列描述信息
[CLUSTERED BY (col_name, col_name, ...)-- 指定分桶列名 分桶就是分数据
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] --指定排序列
[ROW FORMAT delimited fields terminated by 分隔符]  -- 指定每行数据中每个元素的分隔符
[collection items terminated by] -- 指定集合的元素分隔符
[map keys terminated by]   --指定map的kv分隔符
[lines terminated by 分隔符]  --指定行分隔符
[STORED AS file_format]		-- 指定表数据的存储格式（textfile，orc,parquet)
[LOCATION hdfs_path]  -- 指定表对应的hdfs的目录
[TBLPROPERTIES (property_name=property_value, ...)]   --指定表的属性

内部表：删除表时候，会将表在HDFS中映射的目录和目录下的数据都删除

外部表（EXTERNAL）：删除表时，只会删除mysql中的元数据信息，不会删除表在HDFS中映射的目录和目录下的数据

查看表的类型

desc formatted db_hive;-- 表名
Table Type:             MANAGED_TABLE
-- 修改内部表 student2 为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');

-- 查询表的类型
hive (default)> desc formatted student2;
Table Type:             EXTERNAL_TABLE

-- 修改外部表 student2 为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');

-- 查询表的类型
hive (default)> desc formatted student2;
Table Type:             MANAGED_TABLE

4.6 修改数据库

给表重新命名

alter table db_hive rename to hive  --前面是旧表名，后面是修改的新表名

增加和替换列

ADD COLUMNS允许用户在当前列的末尾，分区列之前添加新的列，REPLACE COLUMNS允许用户更新列，更新的过程是先删除当前的列，然后在加入新的列。注：只有在使用native的SerDE时才可以这么做。

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [CONMMENT col_comment], ...);

--添加列语句，下面的查询增加了一个列名dept在employee表。
hive> ALTER TABLE employee ADD COLUMNS (dept STRING COMMENT 'Department name');
REPLACE语句

-- 以下从employee表中查询删除的所有列，并使用emp替换列：
-- 注意：新列的类型不能比对应旧列的类型小
hive> ALTER TABLE employee REPLACE COLUMNS ( eid INT empid Int, ename STRING name String);

改变列名/类型/位置/注释

ALTER TABLE table_name CHANGE
[CLOUMN] col_old_name col_new_name column_type
[CONMMENT col_conmment]
[FIRST|AFTER column_name];

这个命令可以修改表的列名，数据类型，列注释和列所在的位置顺序，FIRST将列放在第一列，AFTER col_name将列放在col_name后面一列

ALTER TABLE test_table CHANGE col1 col2 STRING COMMENT 'The datatype of col2 is STRING' AFTER col3;

4.7 删除表

drop table db_hive;

5、DML操作

5.1 数据导入

5.1.1 向表中装载数据（Load）

Load语句可将文件导入到Hive表中。

1）语法

hive> 
load data [local] inpath 'filepath' [overwrite] into table tablename [partition (partcol1=val1, partcol2=val2 ...)];

关键字说明：

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

（2）local ：如果写，表示从本地（Linux）加载数据到Hive表，这是复制操作；如果不写，否则从HDFS加载数据到Hive表，这是个剪切操作。

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

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

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

（6）tablename：表示具体的表

（7）partition：表示上传到指定分区，若目标是分区表，需指定分区。

load data local inpath '/usr/local/hive-3.1.2/datas/test.txt' into table person;

5.1.2 将查询结果插入表中(Insert)

insert插入单条数据

基本不会使用，每次插入一条数据就是跑一次MR，hive的主要使用是用于数据分析

1）语法

insert (into | overwrite) table tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement;

关键字说明：

（1）INTO：将结果追加到目标表

（2）OVERWRITE：用结果覆盖原有数据

--新建一张表
hive (default)> create table student1(
  id int, 
  name string
) 
row format delimited fields terminated by '\t';

-- 根据查询结果插入数据
hive (default)> insert overwrite table student3 values('1001','zhangsan');

hive (defalut)>select id, name from student;

基本模式插入（根据单表查询结果）

也会执行一次mr

hive(default)>insert overwrite table studen_par select id,name ftrom student where month='202212';

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

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

注意：insert不支持插入部分字段

5.1.3 查询语句中创建表并加载数据（As Select)

根据查询结果创建表（查询的结果会添加到新创建的表中）也是需要执行一次mr

create table if not exists student3 as select id,name from student;

5.1.4 创建表时通过Location指定加载数据路径

1)上传数据到HDFS上

hadoop fs -put /usr/local/a.txt /usr/a.txt

2)创建一张表，指定表的路径

create table student(id int, name string )
row format delimited fields terminated by '\t'
location '/usr';

5.1.5 Import 数据到指定Hive表中

注意：先用export导出后，再将数据导入

hive(default)>import table student from '/hive/warehouse/export/student'

5.2 数据导出

5.1.2 Insert导出

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

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

2)将查询的结果格式化导入到本地

hive (default)>insert overwrite local directory '/user/local/student'
row format delimited fields terminated BY '\t' 
select * from student;

5.2.2 Hive shell 命令导出

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

bin/hive -e 'select * from default.student;'>/usr/local/student.txt;

5.2.3 Export 导出到HDFS上

export default.student to '/hive/warehouse/student'

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

Export导出语句可将表的数据和元数据信息一并到处的HDFS路径，Import可将Export导出的内容导入Hive，表的数据和元数据信息都会恢复。Export和Import可用于两个Hive实例之间的数据迁移。

5.3 清除表中数据（Truncate）

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

hive (default)>truncate table student;