Hive从零基础到入门
Hive的基本概念
Hive简介
Hive本质是将SQL转换为MapReduce的任务进行运算,底层由HDFS来提供数据存储,说白了hive可以 理解为一个将SQL转换为MapReduce的任务的工具,甚至更近一步可以说hive就是一个MapReduce客户端
为什么使用Hive
- 直接不使用hadoop
- 1)人员学习成本太高
- 2)项目要求周期太短
- 3)MapReduce实现复杂查询逻辑开发难度太大
- 为什么要使用Hive
- 1)操作接口采用类SQL语法,提供快速开发能力
- 2)免去了写MapReduce,减少开发人员学历成本
- 3)功能扩展很方便
Hive的特点
- 可扩展性
- Hive可以自由的扩展集群的规模,一般情况下不需要重启服务
- 延伸性
- Hive支持自定义函数,用户可以根据自己的需要来实现自己的函数
- 容错
- 即使节点出现错误,SQL仍然可以完成执行
Hive优缺点
- 优点
- 操作接口采用类SQL语法,提供快速开发的能力(简单、容易上手)。
- 避免了去写MapReduce,减少开发人员的学习成本。
- Hive的执行延迟比较高,因此Hive常用于数据分析,对实时性要求不高的场合。
- Hive优势在于处理大数据,对于处理小数据没有优势,因为Hive的执行延迟比较高。
- Hive支持用户自定义函数,用户可以根据自己的需求来实现自己的函数。
- 集群可自由拓展并且具有良好的容错性,节点出现问题SQL仍可完成执行。
- 缺点
- Hive的HQL表达能力有限
- (1)迭代式算法无法表达
- (2)数据挖掘方面不擅长
- Hive的效率比较低
- (1)Hive自动生成的MapReduce作业,通常情况下不够智能化
- (2)Hive调优比较困难,粒度较粗
Hive和传统数据库对比
查询语言 | HQL | SQL |
数据存储位置 | HDFS | Raw Device或者local FS |
数据格式 | 用户定义 | 系统决定 |
数据更新 | 不支持 | 支持 |
索引 | 0.8之后加入了位图索引 | 复杂的索引 |
执行 | MapReduce | Executor |
执行延迟 | 高 | 低 |
可扩展性 | 高 | 低 |
数据规模 | 大 | 小 |
Hive架构
Client
Hive允许client连接的方式有三个CLI(hive shell)、JDBC/ODBC(java访问hive)、WEBUI(浏览器访问 hive)。JDBC访问时中间件Thrift软件框架,跨语言服务开发。DDL DQL DML,整体仿写一套SQL语句。
- client–需要下载安装包
- JDBC/ODBC 也可以连接到Hive
- 现在主流都在倡导第二种 HiveServer2/beeline
- 做基于用户名和密码安全的一个校验
- Web Gui
- hive给我们提供了一套简单的web页面
- 我们可以通过这套web页面访问hive
- 做的太简陋了
Metastore
元数据包括表名、表所属的数据库(默认是default)、表的拥有者、列/分区字段、表的类型(是否是 外部表)、表的数据所在目录等。
- 一般需要借助于其他的数据载体(数据库)
- 主要用于存放数据库的建表语句等信息
- 推荐使用Mysql数据库存放数据
- 连接数据库需要提供:uri username password drive
Driver
元数据存储在数据库中,默认存在自带的derby数据库(单用户局限性)中,推荐使用Mysql进行存储。
- 解析器(SQL Parser):将SQL字符串转换成抽象语法树AST,这一步一般都用第三方工具库完 成,比如ANTLR;对AST进行语法分析,比如表是否存在、字段是否存在、SQL语义是否有误。
- 编译器(Physical Plan):将AST编译生成逻辑执行计划。
- 优化器(Query Optimizer):对逻辑执行计划进行优化。
- 执行器(Execution):把逻辑执行计划转换成可以运行的物理计划。对于Hive来说,就是 MR/Spark。
数据处理
Hive的数据存储在HDFS中,计算由MapReduce完成。HDFS和MapReduce是源码级别上的整合,两者 结合最佳。解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计 划的生成。
Hive的三种交互方式
shell交互Hive,用命令hive启动一个hive的shell命令行,在命令行中输入sql或者命令来和Hive交互。
服务端启动metastore服务:nohup hive --service metastore > /dev/null 2>&1 &
进入命令:hive
退出命令行:quit;
Hive启动为一个服务器,对外提供服务,其他机器可以通过客户端通过协议连接到服务器,来完成访问 操作,这是生产环境用法最多的
进入命令:1)先执行beeline,在执行! connect jdbc:hive2://xxxxxx:10000
2)或者直接执行 beeline -u jdbc:hive2://xxxxxx:10000 -n root
退出命令行:!exit
使用 –e 参数来直接执行hql的语句
hive -e "show databases;"
使用 –f 参数通过指定文本文件来执行hql的语句
vim hive.sql
use myhive;
select * from test;
保存退出
hive -f hive.sql
- metastore服务实际上就是一种thrift服务,通过它我们可以获取到hive元数据,并且通过thrift获 取原数据的方式,屏蔽了数据库访问需要驱动,url,用户名,密码等等细节。
- HiveServer2(HS2)是一个服务端接口,使远程客户端可以执行对Hive的查询并返回结果。一般 来讲,我们认为HiveServer2是用来提交查询的,也就是用来访问数据的。 而MetaStore才是用来 访问元数据的。
- beeline cli优化了命令行界面
Hive的基本操作
Hive库操作
创建数据库
--1)创建一个数据库,数据库在HDFS上的默认存储路径是/hive/warehouse/*.db。
create database shop;
--2)避免要创建的数据库已经存在错误,增加if not exists判断。(标准写法)
create database if not exists shop;
创建数据库和位置
create database if not exists xxx location '/school.db';
修改数据库
用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值,来描述这个数据 库的属性信息。数据库的其他元数据信息都是不可更改的,包括数据库名和数据库所在的目录位置。
alter database xxx set dbproperties('createtime'='20201213');
数据库详细信息
1)显示数据库(show)
show databases;
2)可以通过like进行过滤
show databases like 's*';
- 查看详情(desc)
desc database xxx;
4)切换数据库(use)
use xxx;
删除数据库
1)最简写法
create database if not exists school location '/school.db';
2)如果删除的数据库不存在,最好使用if exists判断数据库是否存在。否则会报错:
FAILED: SemanticException [Error 10072]: Database does not exist: db_hive
drop database if exists xxx;
3)如果数据库不为空,使用cascade命令进行强制删除。报错信息如下FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)
drop database if exists xxx cascade;
Hive数据类型
基础数据类型
类型 | Java数据类型 | 描述 |
TINYINT | byte | 8位有符号整型。取值范围:-128~127。 |
SMALLINT | short | 16位有符号整型。取值范围:-32768~32767。 |
INT | int | 32位有符号整型。取值范围:-2 31 ~2 31 -1。 |
BIGINT | long | 64位有符号整型。取值范围:-2 63 +1~2 63 -1。 |
BINARY | 二进制数据类型,目前长度限制为8MB。 | |
FLOAT | float | 32位二进制浮点型。 |
DOUBLE | double | 64位二进制浮点型。 |
DECIMAL(precision,scale) | 10进制精确数字类型。precision:表示最多可以表示 多少位的数字。取值范围: 1 <= precision <= 38 。scale:表示小数部分的位数。取值范围: 0 <= scale <= 38 。如果不指定以上两个参数,则默认为 decimal(10,0) 。 | |
VARCHAR(n) | 变长字符类型,n为长度。取值范围:1~65535。 | |
CHAR(n) | 固定长度字符类型,n为长度。最大取值255。长度不 足则会填充空格,但空格不参与比较。 | |
STRING | string | 字符串类型,目前长度限制为8MB。 |
DATE | 日期类型,格式为 yyyy-mm-dd 。取值范围:0000-01- 01~9999-12-31。 | |
DATETIME | 日期时间类型。取值范围:0000-01-01 00:00:00.000~9999-12-31 23.59:59.999,精确到毫秒。 | |
TIMESTAMP | 与时区无关的时间戳类型。取值范围:0000-01-01 00:00:00.000000000~9999-12-31 23.59:59.999999999,精确到纳秒。说明 对于部分时 区相关的函数,例如 cast( as string) ,要求TIMESTAMP按照与当前时区相符的方 式来展现。 | |
BOOLEAN | boolean | BOOLEAN类型。取值:True、False。 |
常见表操作
创建表
- CREATE TABLE
创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项
来忽略这个异常。
- EXTERNAL
关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION)
创建内部表时,会将数据移动到数据仓库指向的路径(默认位置);
创建外部表时,仅记录数据所在的路径,不对数据的位置做任何改变。在
删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
- COMMENT:
为表和列添加注释。
- PARTITIONED BY
创建分区表
- CLUSTERED BY
创建分桶表
- SORTED BY
不常用
- ROW FORMAT
SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称,目的是用于序列化和反序列化。
- STORED AS指定存储文件类型
常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储
格式文件)
如果文件数据是纯文本,可以使用STORED AS TEXTFILE。
如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
- LOCATION
指定表在HDFS上的存储位置。
- LIKE
允许用户复制现有的表结构,但是不复制数据。
显示表
show tables;
show tables like 'u';
desc t_person;
desc formatted t_person;
重命名
内部表(同时修改文件目录)外部表(因为目录是共享的,所以不会修改目录名称)
基本语法
alter table old_table_name rename to new_table_name;
修改列
查询表结构
desc test_new;
添加列
alter table test_new add columns (education string);
查询表结构
desc test_new;
更新列
alter table test_new change education educationnew string;
删除表
drop table test_new;
Hive内外部表
hive内部表
- 当创建好表的时候,HDFS会在当前表所属的库中创建一个文件夹
- 当设置表路径的时候,如果直接指向一个已有的路径,可以直接去使用文件夹中的数据
- 当load数据的时候,就会将数据文件存放到表对应的文件夹中
- 而且数据一旦被load,就不能被修改
- 我们查询数据也是查询文件中的文件,这些数据最终都会存放到HDFS
- 当我们删除表的时候,表对应的文件夹会被删除,同时数据也会被删除
Hive外部表
- 外部表因为是指定其他的hdfs路径的数据加载到表中来,所以hive会认为自己不完全独占这份数据
- 删除hive表的时候,数据仍然保存在hdfs中,不会删除。
Hive载入数据
基本语法
load data [local] inpath 'datapath' [overwrite] into table student
[partition (partcol1=val1,…)];
--load data
加载数据
--[local]
本地,不加Local就是从HDFS,如果是HDFS,将会删除掉原来的数据
--inpath
数据的路径
--'datapath'
具体的路径,要参考本地还是HDFS
--[overwrite]
覆盖
--into table
加入到表
--student
表的名字
--[partition (partcol1=val1,…)]
分区
Hive导出数据
- 将表中的数据备份
- 将查询结果存放到本地
//创建存放数据的目录
mkdir -p /root/yjx
//导出查询结果的数据(导出到Node01上)
insert overwrite local directory '/root/person_data' select * from t_person;
- 按照指定的方式将数据输出到本地
//创建存放数据的目录
mkdir -p /root/yjx
//导出查询结果的数据
insert overwrite local directory '/root/yjx/person'
ROW FORMAT DELIMITED fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n'
select * from t_person;
- 将查询结果输出到HDFS
//创建存放数据的目录
hdfs dfs -mkdir -p /yjx/copy
//导出查询结果的数据
insert overwrite directory '/yjx/copy/user'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
select * from t_user;
- 直接使用HDFS命令保存表对应的文件夹
//创建存放数据的目录
hdfs dfs -mkdir -p /yjx/person
//使用HDFS命令拷贝文件到其他目录
hdfs dfs -cp /hive/warehouse/t_person/* /yjx/person
Hive分区表
- 在大数据中,最常见的一种思想就是分治,我们可以把大的文件切割划分成一个个的小的文件,这 样每次操作一个个小的文件就会很容易了,同样的道理,在hive当中也是支持这种思想的,就是我 们可以把大的数据,按照每天或者每小时切分成一个个小的文件,这样去操作小的文件就会容易很多了。
- 假如现在我们公司一天产生3亿的数据量,那么为了方便管理和查询
- 建立分区(可按日期部门等具体业务分区)。
- 分门别类的管理。
静态分区(SP)static partition
动态分区(DP)
- 动态分区(DP)dynamic partition
- 静态分区与动态分区的主要区别在于静态分区是手动指定,而动态分区是通过数据来进行判断。
- 详细来说,静态分区的列是在编译时期通过用户传递来决定的;动态分区只有在SQL执行时才能决定。
开启动态分区首先要在hive会话中设置如下的参数
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
分桶表
业务场景
- 分区提供了一个隔离数据和优化查询的便利方式,不过并非所有的数据都可形成合理的分区, 尤其是需要确定合适大小的分区划分方式
- 不合理的数据分区划分方式可能导致有的分区数据过多,而某些分区没有什么数据的尴尬情况
- 分桶是将数据集分解为更容易管理的若干部分的另一种技术。
- 分桶就是将数据按照字段进行划分,可以将数据按照字段划分到多个文件当中去。
数据分桶原理
- Hive采用对列值哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
- bucket num = hash_function(bucketing_column) mod num_buckets
- 列的值做哈希取余 决定数据应该存储到哪个桶
数据分桶优势
- 方便抽样
- 使取样(sampling)更高效。在处理大规模数据集时,在开发和修改查询的阶段,如果能在 数据集的一小部分数据上试运行查询,会带来很多方便
- 提高join查询效率
- 获得更高的查询处理效率。桶为表加上了额外的结构,Hive 在处理有些查询时能利用这个结 构。具体而言,连接两个在(包含连接列的)相同列上划分了桶的表,可以使用 Map 端连接 (Map-side join)高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列,如果 对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以,可以大大较少 JOIN的数据量。
Hive查询语法
Hive独特的排序(四个by)
全局排序
- order by 会对输入做全局排序,因此只有一个reducer,会导致当输入规模较大时,需要较长的计 算时间
- 使用 order by子句排序 :ASC(ascend)升序(默认)| DESC(descend)降序
- order by放在select语句的结尾
select * from t_student_d order by sno;
- 按照字段别名排序
select grade,count(sno) cs from t_student_d group by grade order by cs;
- 多个列排序
select grade,count(sno) cs from t_student_d group by grade order by cs,grade;
局部排序
- sort by 不是全局排序,其在数据进入reducer前完成排序。
- 如果用sort by进行排序,并且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出 有序,不保证全局有序。
- 设置reduce个数
set mapreduce.job.reduce=3;
- 查看reduce个数
insert overwrite local directory '/data/student' select * from t_student_d
distribute by sname;
分区排序
- distribute by(字段)根据指定的字段将数据分到不同的reducer,且分发算法是hash散列。
- 类似MR中partition,进行分区,结合sort by使用。(注意:distribute by 要在sort by之前)
- 对于distrbute by 进行测试,一定要多分配reduce进行处理,否则无法看到distribute by的效果。
- 设置reduce个数
分区并排序
cluster by(字段)除了具有Distribute by的功能外,还会对该字段进行排序
cluster by = distribute by + sort by 只能默认升序,不能使用倒序
select * from t_student_d sort cluster by sname;
select * from t_student_d distribute by sname sort by sname;
内置函数的分类
- 关系操作符:包括 = 、 <> 、 <= 、>=等
- 算数操作符:包括 + 、 - 、 *、/等
- 逻辑操作符:包括AND 、 && 、 OR 、 || 等
- 复杂类型构造函数:包括map、struct、create_union等
- 复杂类型操作符:包括A[n]、Map[key]、S.x
- 数学操作符:包括ln(double a)、sqrt(double a)等
- 集合操作符:包括size(Array)、sort_array(Array)等
- 类型转换函数: binary(string|binary)、cast(expr as )
- 日期函数:包括from_unixtime(bigint unixtime[, string format])、unix_timestamp()等
- 条件函数:包括if(boolean testCondition, T valueTrue, T valueFalseOrNull)等
- 字符串函数:包括acat(string|binary A, string|binary B…)等
- 其他:xpath、get_json_objectscii(string str)、con
Hive窗口函数
- 普通的聚合函数每组(Group by)只返回一个值,而开窗函数则可为窗口中的每行都返回一个值。
- 简单理解,就是对查询的结果多出一列,这一列可以是聚合值,也可以是排序值。
- 开窗函数一般就是说的是over()函数,其窗口是由一个 OVER 子句 定义的多行记录
- 开窗函数一般分为两类,聚合开窗函数和排序开窗函数。
聚合开窗函数
sum(求和)min(最小)max(最大)avg(平均值)count(计数)
rows必须跟在Order by 子句之后,对排序的结果进行限制,使用固定的行数来限制分区中
的数据行数量。
- OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。
- CURRENT ROW:当前行
- n PRECEDING:往前n行数据
- n FOLLOWING:往后n行数据
- UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED
- FOLLOWING表示到后面的终点
- LAG(col,n,default_val):往前第n行数据,col是列名,n是往上的行数,当第n行为null的时候取default_val
- LEAD(col,n, default_val):往后第n行数据,col是列名,n是往下的行数,当第n行为null的时候取default_val
- NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。
- cume_dist(),计算某个窗口或分区中某个值的累积分布。假定升序排序,则使用以下公式确定累积分布:
- 小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中,x 等于 order by 子句中指定的列的当前行中的值。
排序开窗函数
- RANK() 排序相同时会重复,总数不会变
- DENSE_RANK() 排序相同时会重复,总数会减少
- ROW_NUMBER() 会根据顺序计算
- percent_rank() 计算给定行的百分比排名。可以用来计算超过了百分之多少的人
- (当前行的rank值-1)/(分组内的总行数-1)
自定义函数
- 官网 https://cwiki.apache.org/confluence/display/Hive/HivePlugins
- Hive自带了一些函数,比如:max/min等,但是数量有限,自己可以通过自定义的UDF来方便的扩展
- 当Hive提供的内置函数无法满足你的业务处理需要的时候,此时可以考虑使用用户自定义函数
- UDF(User-Defined-Function) 单行函数,一进一出
- size/sqrt
- UDAF(User- Defined Aggregation Funcation) 聚集函数,多进一出。
- count/max/min/sum/avg
- UDTF(User-Defined Table-Generating Functions) 一进多出
- lateral view explode()
的值。
排序开窗函数
- RANK() 排序相同时会重复,总数不会变
- DENSE_RANK() 排序相同时会重复,总数会减少
- ROW_NUMBER() 会根据顺序计算
- percent_rank() 计算给定行的百分比排名。可以用来计算超过了百分之多少的人
- (当前行的rank值-1)/(分组内的总行数-1)
自定义函数
- 官网 https://cwiki.apache.org/confluence/display/Hive/HivePlugins
- Hive自带了一些函数,比如:max/min等,但是数量有限,自己可以通过自定义的UDF来方便的扩展
- 当Hive提供的内置函数无法满足你的业务处理需要的时候,此时可以考虑使用用户自定义函数
- UDF(User-Defined-Function) 单行函数,一进一出
- size/sqrt
- UDAF(User- Defined Aggregation Funcation) 聚集函数,多进一出。
- count/max/min/sum/avg
- UDTF(User-Defined Table-Generating Functions) 一进多出
- lateral view explode()