Hive
Hive基本概念
Hive简介
-
什么是Hive
- 将SQL转换为MapReduce的任务的工具，甚至更近一步可以说hive就是一个MapReduce客户端
- 本质是将SQL转换为MapReduce的任务进行运算，底层由HDFS来提供数据存储
-
为什么要使用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应用场景
-
日志分析：大部分互联网公司使用hive进行日志分析，包括百度、淘宝等。
- 统计网站一个时间段内的pv、uv
- 多维度数据分析
-
海量结构化数据离线分析
-
Hive架构
-
架构图
-
架构细节
-
Client
-
连接的方式
- CLI（hive shell）
- JDBC/ODBC(java访问hive)
- WEBUI（浏览器访问hive）
-
-
Metastore
- 元数据
- 一般需要借助于其他的数据载体（数据库）
- 主要用于存放数据库的建表语句等信息
- 推荐使用Mysql数据库存放数据
- 连接数据库需要提供：uri username password driver
-
Driver
- 元数据存储在数据库中，默认存在自带的derby数据库
- （1） 解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如ANTLR；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
- （2） 编译器（Physical Plan）：将AST编译生成逻辑执行计划。
- （3） 优化器（Query Optimizer）：对逻辑执行计划进行优化。
- （4） 执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。
-
数据处理
- Hive的数据存储在HDFS中，计算由MapReduce完成
-
Hive三种交互方式
-
第一种
- shell交互Hive，用命令hive启动一个hive的shell命令行，在命令行中输入sql或者命令来和Hive交互
- 服务端启动metastore服务：nohup hive --service metastore > /dev/null 2>&1 &
- 进入命令:hive
- 退出命令行：quit;
-
第二种
-
Hive启动为一个服务器，对外提供服务，其他机器可以通过客户端通过协议连接到服务器，这是生产环境用法最多的
-
服务端启动hiveserver2服务：
- nohup hive --service metastore > /dev/null 2>&1 &
- nohup hiveserver2 > /dev/null 2>&1 &
-
进入命令
- 1)先执行beeline，在执行! connect jdbc:hive2://node01:10000
- 2)或者直接执行 beeline -u jdbc:hive2://node01:10000 -n root
-
-
第三种
-
使用 –e 参数来直接执行hql的语句
- bin/hive -e “show databases;”
-
Hive元数据
- Hive元数据库中一些重要的表结构及用途，方便Impala、SparkSQL、Hive等组件访问元数据库的理解
Hive基本操作
Hive库的操作
-
创建数据库
-
默认存储路径是/hive/warehouse/*.db
- create database shop;
-
避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）
- create database if not exists shop;
-
-
创建数据库和位置
- create database if not exists school location ‘/school.db’;
-
修改数据库
-
使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值
- alter database school set dbproperties(‘createtime’=‘20201213’);
-
-
数据库详细信息
-
显示数据库（show）
- show databases;
-
可以通过like进行过滤
- show databases like ‘s*’;
-
查看详情（desc）
- desc database school;
-
切换数据库（use）
- use school;
-
-
删除数据库
-
最简写法
- drop database school;
-
如果删除的数据库不存在，最好使用if exists判断数据库是否存在
- drop database if exists school;
-
如果数据库不为空，使用cascade命令进行强制删除
- drop database if exists school cascade;
-
Hive数据类型
-
基础数据类型
- 类似Java
-
复杂数据类型
- Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。
- ARRAY
- MAP
- STRUCT
Hive表操作
-
创建表
-
显示表
- 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);
-
更新列
- alter table test_new change education educationnew string;
-
-
删除表
-
drop table test_new;
- 表和数据都被删除
-
truncate table test_new
- 截断表，不开启事务，不可恢复（清空表）
-
Hive内外部表
-
内部表
- 创建表的时候会在库中默认创建一个同名的文件夹,
- 当load数据的时候会将数据剪切到文件夹中
- 当我们用SQL处理数据的时候默认处理表对应文件夹中的数据,
- 当表被删除的时候数据也会被删除,
- 如果多个业务共同处理相同数据，无法对数据进行共享,
- 三个项目组对数据进行共享,如果-个项目组将数据删除，会有数据不安全的情况
-
外部表
- 外部表一般在创建的时候直接将文件夹路径指向要分析数据的路径
- 当我们删除表与数据映射结构的时候，数据不会被销毁
Hive载入数据
-
加载linux本地数据
- 切记必须和hiveserver2在同一个节点才可以上传
- load data local inpath ‘/root/user.txt’ into table t_user;
-
加载HDFS数据
- load data inpath ‘/yjx/user.txt’ into table t_user;
-
加载并覆盖已有数据
- load data inpath ‘/yjx/user.txt’ overwrite into table t_user;
-
通过查询插入数据
-
将查询结果插入一张表
- insert overwrite table t_user1 select id,uname from t_user;
-
将查询结果一次性存放到多张表
- from t_user
insert overwrite table t_user1 select id,uname
insert overwrite table t_user2 select id,pwd;
- from t_user
-
Hive导出数据
-
将表中的数据备份
-
将查询结果存放到本地
- insert overwrite local directory ‘/root/person_data’ select * from t_person;
-
按照指定的方式将数据输出到本地
//导出查询结果的数据
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
//导出查询结果的数据
insert overwrite directory ‘/yjx/copy/user’
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
select * from t_user;
-
-
直接使用HDFS命令保存表对应的文件夹
- //使用HDFS命令拷贝文件到其他目录
- hdfs dfs -cp /hive/warehouse/t_person/* /yjx/person
-
将表结构和数据同时备份
-
导出
- //导出查询结果的数据
- export table t_person to ‘/yjx/copy’;
-
恢复
- import from ‘/yjx/copy’;
- 需要注意的是：时间不同步，会导致导入导出失败
-
Hive分区表
-
静态动态分区的特点
- 静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。
- 详细来说，静态分区的列是在编译时期通过用户传递来决定的；动态分区只有在SQL执行时才能决定
-
静态分区
-
单分区表
CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
) partitioned by(grade int)
row format delimited fields terminated by ‘,’;–载入数据
load data inpath ‘/yjx/student.txt’ into table t_student partition(grade=1);- 分区的字段不要和表的字段相同。相同会报错error10035
-
多分区表
CREATE TABLE IF NOT EXISTS t_teacher (
tno int,
tname string
) partitioned by(grade int,clazz int)
row format delimited fields terminated by ‘,’;–载入数据
load data inpath ‘/yjx/teacher11.txt’ into table t_teacher
partition(grade=1,class=1);- –注意：前后两个分区的关系为父子关系，也就是grade文件夹下面有多个clazz子文件夹。
-
分区表查询
- select * from t_student where grade = 1 ;
-
查看分区
- show partitions t_student;
-
添加分区
- alter table t_student add partition (day=‘99990102’);
- alter table t_student add partition (day=‘99990103’) location ‘99990103’;
-
删除分区
- alter table salgrade2 drop partition (day=‘99990102’);
-
-
动态分区
-
开启动态分区首先要在hive会话中设置如下的参数
- set hive.exec.dynamic.partition=true;
- set hive.exec.dynamic.partition.mode=nonstrict;
-
如果静态分区的话，我们插入数据必须指定分区的值。
-
如果想要插入多个班级的数据，我要写很多SQL并且执行24次很麻烦。
-
而且静态分区有可能会产生数据错误问题
-
例
–创建分区表
CREATE TABLE IF NOT EXISTS t_student_d (
sno int,
sname string
) partitioned by (grade int,clazz int)
row format delimited fields terminated by ‘,’;
–创建外部表
CREATE EXTERNAL TABLE IF NOT EXISTS t_student_e (
sno int,
sname string,
grade int,
clazz int
)
row format delimited fields terminated by ‘,’
location “/yjx/student”; -
如果使用动态分区，动态分区会根据select的结果自动判断数据应该load到哪儿分区去。
insert overwrite table t_student_d partition (grade,clazz) select * from
t_student_e ;
-
分桶表
-
特点
- 分桶是将数据集分解为更容易管理的若干部分的另-种技术。
- 分桶就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去。
- 计算数据列的hashcode,然后对桶的数据进行取余
- 桶的个数尽可能多的是拥有因数12，方便数据取样计算
-
原理
- Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中
-
优势
- 方便抽样
- 提高join查询效率
-
实战
-
开启分桶功能
- set hive.enforce.bucketing=true;
-
设置Reduce个数我们需要确保reduce 的数量与表中的bucket 数量一致
- set mapreduce.job.reduce=3;
-
例
– 创建表
CREATE TABLE t_citizen_bucket(
idcard int,
pname string,
province int
)clustered by(idcard) sorted by (pname desc) into 16 buckets
row format delimited fields terminated by ‘,’
lines terminated by ‘\n’;
create EXTERNAL table t_citizen(
idcard int,
pname string,
province int
)row format delimited fields terminated by ‘,’
lines terminated by ‘\n’
location ‘/yjx/citizen’;
– 数据导入
for (int i = 1000; i < 10000; i++) {
System.out.println(i + “,” + “admin” + (new Random().nextInt(89999) +-
- “,” + i % 34);
}
– 将外部表的数据导入到分桶表
insert overwrite table t_citizen_bucket select * from t_citizen ;
- “,” + i % 34);
-
-
-
数据抽样算法
-
数据块抽样
-
该方式允许Hive随机抽取N行数据，数据总量的百分比（n百分比）或N字节的数据。
- SELECT * FROM <Table_Name> TABLESAMPLE(N PERCENT|ByteLengthLiteral|N ROWS) s;
-
-
桶表抽样
-
TABLESAMPLE(BUCKET x OUT OF y)
- x表示从哪个bucket开始抽取。
- y必须是table总bucket数的倍数或者因子
- 例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽(64/128=)1/2个bucket的数据取
- select * from t_citizen_bucket tablesample(bucket 1 out of 16 on idcard);
- select * from t_citizen_bucket tablesample(bucket 2 out of 4 on idcard);
-
-
随机抽样
-
使用RAND()函数和LIMIT关键字来获取样例数据，使用DISTRIBUTE和SORT关键字来保证数据是随机分散到mapper和reducer的
- SELECT * FROM <Table_Name> DISTRIBUTE BY RAND() SORT BY RAND() LIMIT ;
- select * from t_citizen_bucket DISTRIBUTE BY RAND() SORT BY RAND() LIMIT 10;
-
-
Hive查询语法
Hive独特的排序
-
全局排序
-
order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间
-
多个列排序
- order by cs,grade;
-
-
局部排序
-
sort by 不是全局排序,其在数据进入reducer前完成排序。
-
如果用sort by进行排序，并且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序，不保证全局有序。
-
步骤
-
设置reduce个数
- set mapreduce.job.reduce=3;
-
查看reduce个数
- set mapreduce.job.reduce;
-
排序
- select * from t_student_d sort by sname;
-
将查询结果导入到文件中
- insert overwrite local directory ‘/root/student’ select * from t_student_d
sort by clazz asc, grade desc;
- insert overwrite local directory ‘/root/student’ select * from t_student_d
-
-
-
分区排序
-
概述
- distribute by（字段）根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
- 类似MR中partition,进行分区，结合sort by使用。（注意：distribute by 要在sort by之前）
- 对于distrbute by 进行测试，一定要多分配reduce进行处理，否则无法看到distribute by的效果
-
步骤
-
设置reduce个数
- set mapreduce.job.reduce=7;
-
排序
- insert overwrite local directory ‘/data/student’ select * from t_student_d
distribute by sname;
- insert overwrite local directory ‘/data/student’ select * from t_student_d
-
-
-
分区并排序
- 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;
Hive内置函数
-
内置函数
-
查看系统自带函数
- show functions;
-
显示自带的函数的用法
- desc function upper;
-
详细显示自带的函数的用法
- desc function extended upper;
-
-
内置函数的分类
-
关系操作符
-
算数操作符
-
逻辑操作符
-
复杂类型构造函数
-
复杂类型操作符
-
数学操作符
-
集合操作符
-
类型转换函数
- 日期函数
-
日期函数
-
条件函数
-
字符串函数
-
-
独树一帜的UDTF
-
explode 可以将一组数组的数据变成一列表
- select explode(split(types,"-")) from t_movie1;
-
lateral view 表生成函数，可以将explode的数据生成一个列表
- select id,name,type from t_movie1,lateral view explode(split(types,"-"))typetable as type;
-
collect_set()和collect_list()都是对列转成行，区别就是list里面可重复而set里面是去重的
-
concat_ws(’:’,collect_set(type)) ‘:’ 表示你合并后用什么分隔，collect_set(stage)表示要合并表中的那一列数据
-
collect_set(stage)表示要合并表中的那一列数据
-
select id,concat_ws(’:’,collect_set(type)) as types from t_movie2 group byid;
-
Hive窗口函数
-
概述
- 普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。
- 简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。
- 开窗函数一般就是说的是over（）函数，其窗口是由一个 OVER 子句 定义的多行记录
- 开窗函数一般分为两类,聚合开窗函数和排序开窗函数
-
聚合开窗函数
-
可将普通列与聚合列放在一起使用
-
rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量
- OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
- CURRENT ROW：当前行
- n PRECEDING：往前n行数据
- n FOLLOWING：往后n行数据
- UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDEDFOLLOWING表示到后面的终点
-
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() 计算给定行的百分比排名。可以用来计算超过了百分之多少的人
自定义函数
-
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()
Hive文件存储格式
文件存储方式
-
行式存储格式，便于按照ID查询
-
特点
- 相关数据保存在一起，数据读取的时候以行为单位读取的
-
优点：这种存储格式简单、方便写入数据 。
-
缺点：不支持压缩、并且不支持列裁剪，数据分析开销较大
-
文件格式：TextFile、SequenceFile
-
-
列式存储格式，只是针对数据的某些字段进行查询
-
特点
- 将不同的列存放在不同的块中
-
优点：支持列裁剪、减少数据查询范围，数据支持压缩，节省空间。
-
缺点：写入数据相对困难，并且查询整行数据时，开销相对较大。
-
文件格式：ORC、PARQUET、RCFILE
-
文件存储格式
-
TextFile
- 行存储
- Hive默认格式，数据不做压缩，磁盘开销大，数据解析开销大
-
SequenceFile
- 行存储
- Hadoop API 提供的一种二进制文件，它将数据以<key,value>的形式序列化到文件中
- 内部使用Hadoop 的标准的Writable 接口实现序列化和反序列化
-
RCFile
- 一种专门面向列的数据格式
- 它遵循“先按列划分，再垂直划分”的设计理念。
- 当查询过程中，针对它并不关心的列时，它会在IO上跳过这些列。
-
ORC
- 并不是一个单纯的列式存储格式，仍然是首先根据行组分割整个表，在每一个行组内进行按列存储
数据倾斜
定义
- 单个节点任务所处理的数据量远大于同类型任务所处理的数据量，导致该节点成为整个作业的瓶颈
本质原因
- 一是任务读取大文件，最常见的就是读取压缩的不可分割的大文件。
- 二是任务需要处理大量相同键的数据
1732
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
