HIVE总结
目录
1.安装hive
(会单独出详细教程)
2.理解hive工作原理(一定要理解)
Hive 是一个建立在 Hadoop 之上的数据仓库,它提供了一种类似于 SQL 的查询语言 HiveQL,使用户可以方便地在大规模数据集上执行查询。下面是 Hive 的工作原理概述:
1. 数据存储:
- Hive 使用 Hadoop 分布式文件系统(HDFS)来存储数据。数据以文件的形式存储在 HDFS 上,分布在多个节点上。
- Hive 还支持其他存储系统,如 Amazon S3、Azure Data Lake Storage 等。
2. 元数据存储:
- Hive 使用元数据来描述存储在 HDFS 上的数据,包括表的结构、分区信息、文件位置等。
- 元数据存储在关系型数据库中,比如 MySQL、PostgreSQL 等。
3. HiveQL 解析:
- 当用户提交一个 HiveQL 查询时,Hive 首先会对查询进行解析,识别查询中的表、列、条件等信息。
4. 查询优化:
- 解析完成后,Hive 会对查询进行优化,包括条件下推、列裁剪、分区裁剪等,以提高查询性能。
5. 查询转换:
- 经过优化后,Hive 将查询转换为一系列的 MapReduce、Tez 或 Spark 任务,这些任务在 Hadoop 集群上并行执行。
6. 数据处理:
- 执行阶段涉及数据的读取、过滤、聚合等操作,根据查询需求将数据处理为最终结果。
7. 结果返回:
- 处理完成后,查询结果可以被返回给用户,或者被存储在 HDFS 上的指定位置。
8. 元数据更新:
- 当用户创建、修改或删除表时,Hive 会更新元数据存储中的相关信息,以反映这些变化。
总的来说,Hive 通过将 SQL 查询转换为适合在 Hadoop 上运行的 MapReduce、Tez 或 Spark 任务,从而实现在大规模数据集上进行数据分析和查询的功能。同时,通过元数据管理,使得用户能够方便地对数据仓库进行管理和维护。
3.会创建内部表,会加载数据
创建库 create database 库名;
创建表 create table 表名(列1 数据类型,列2 数据类型......);
create table stu(sid bigint,sname varchar(90),ssex string) row format delimited fields terminated by ',';
create table card(cid bigint,amount decimal(20,3),sid bigint) row format delimited fields terminated by ',';
row format delimited fields terminated by ',':数据文件的分割符
加载数据
load data local inpath '/root/1.txt' into table stu;
hive在做计算时,它数据位置从mysql找,
它的数据在hadoop上
单表查
select * from 表名;
select 列1,列2.... from where 列1=值 and or 列2=值....
select 分组列1,分组列2...,sum|count|avg|max|min(数值列) from 表名 group by 分组列1,分组列2...
select count(1) cnt,ssex from stu group by ssex;
多表查询:
内连接
select stu.sid, stu.sname,card.amount,card.sid from stu inner join card on stu.sid=card.sid;
外:
左外连接
select stu.sid, stu.sname,card.amount,card.sid from stu left join card on stu.sid=card.sid;
右外连接
select stu.sid, stu.sname,card.amount,card.sid from stu right join card on stu.sid=card.sid;
全连接
select stu.sid, stu.sname,card.amount,card.sid from stu full join card on stu.sid=card.sid;
子查询:
1)用查询结果当表
select sum(cnt) from (select count(1) cnt,ssex from stu group by ssex) a;
2)用查询结果当条件
查找姓别人数最多学生信息
select ssex from (select count(1) cnt,ssex from stu group by ssex) a
where a.cnt = (select max(cnt) from ( select count(1) cnt from stu group by ssex))a
4.会创建分区表,会加载数据
咱们做开发通常情况下,是按照某个范围进行数据统计,就像淘宝,每天都产生大量数据,在处理数据,每天每天数据。
1)把所有数据不分区存起来
2020-12-12 张三 399
2020-12-13 张三 500
where dt='2020-12-12'
2)分区存
where dt='2020-12-12'
create table ord_info
(
oid bigint,
odt string,
oamount float
) partitioned by (dt string) row format delimited fields terminated by ',';
1)
insert into ord_info partition(dt='2020-12-12') values(1,'2020-12-12',900);
insert into ord_info partition(dt='2020-12-12') values(2,'2020-12-12',600);
insert into ord_info partition(dt='2020-12-13') values(3,'2020-12-13',800),(4,'2020-12-13',500);
2)加载数据:
load data local inpath '/root/2.txt' into table ord_info partition (dt="2020-12-18");
3)自己创建分区目录
hadoop fs -mkdir /user/hive/warehouse/stu.db/ord_info/dt=2020-12-19
hadoop fs -put 2.txt /user/hive/warehouse/stu.db/ord_info/dt=2020-12-19/
向mysql添加分区
msck repair table ord_info;
分区为什么速度快
5.会创建外部表
create external table stu1(sid bigint,sname varchar(90),ssex string) row format delimited fields terminated by ',';
MANAGED_TABLE
EXTERNAL_TABLE:比较安全
drop table stu1;
drop table stu;
6.会创建UDF函数
相当于mysql 的自定义函数
因为hive的函数不够用,我们可以自定义函数
public class DateUDF extends UDF {
//方法名必须是这个
public String evaluate(long ms)
{
SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
return simpleDateFormat.format(new Date(ms));
}
}
7.会设置map的个数
用几个mapper处理数据:
确定分片
splitSize = max{minSize,min{goalSize,blockSize}}
blockSize:128M
goalSize:set mapred.max.split.size=10
minSize:set mapred.min.split.size:1
select sum(oamount) from ord_info;
8.会设置reduce个数
set mapreduce.job.reduces=2;
set hive.exec.reducers.bytes.per.reducer=10//通过每个reduce处理多大数据来确定reduce数
set hive.exec.reducers.max=10//最大reduce数
9.会根据执行计划对sql进行调优
查询条件先过滤掉数据比较好
explain 查看执行计划
在reduce端过滤
select * from ord_info o1, ord_info o2 where o1.oid=o2.oid and o1.oamount > 100;
//在map过滤
select * from ord_info o1 inner join ord_info o2 on o1.oid=o2.oid and o1.oamount > 100;
explain select * from ord_info o1 inner join ord_info o2 on o1.oid=o2.oid and o1.oamount > 100;
explain select * from ord_info o1, ord_info o2 where o1.oid=o2.oid and o1.oamount > 100;
设置下不能只map端join
reduce端join
set hive.auto.convert.join=false;reduce join
explain select * from ord_info o1 inner join ord_info o2 on o1.oid=o2.oid and o1.oamount > 100;
explain select * from ord_info o1, ord_info o2 where o1.oid=o2.oid and o1.oamount > 100;
10.会处理数据倾斜问题
reduce处理一组一组数据,假设有组2组数据,2个reduce,一个reduce处理一组,假设出了一个reduce长时间运行,一个reduce马上结束
解决方案:
set hive.map.aggr=true 开启map端计算,在一定程度上避免数据倾斜
set hive.groupby.skewindata=true:用多对mapreduce计算,在一定程度上避免数据倾斜
explain select sum(oamount ),dt from ord_info group by dt;
join数据倾斜
set hive.optimize.skewjoin=true;
set hive.skewjoin.key=100000;某个key对应值太多,超过10玩条记录,就分成多个reduce
11.窗口函数
Hive窗口函数是一种强大的功能,用于在查询结果中执行聚合、排序、排名等操作,而无需将数据合并到单个聚合中。窗口函数可以在不破坏查询结果行的情况下,对查询结果集中的特定分区进行计算和操作。:
1. 常见窗口函数:
- `ROW_NUMBER()`:为结果集中的行分配一个唯一的整数序号。
- `RANK()`:为结果集中的行分配一个排名,如果有相同的值,则会跳过相同值个数,下一个值排名则为前一个排名值加上跳过的相同值的个数。
- `DENSE_RANK()`:类似于`RANK()`函数,但是不会跳过排名。
- `NTILE(n)`:将结果集分割成n个大小尽可能相等的分区,并为每个行分配一个分区编号。
2. 示例:
假设我们有一个名为`sales`的表,包含销售数据,其中有列`product_id`表示产品ID,`sales_amount`表示销售金额,`sale_date`表示销售日期。
- 使用`ROW_NUMBER()`函数为每个产品ID按照销售金额降序排名:
```sql
SELECT product_id, sales_amount, sale_date,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sales_amount DESC) AS row_num
FROM sales;
```
- 使用`NTILE()`函数将销售金额分成3个等份,并计算每个产品ID所属的分组:
```sql
SELECT product_id, sales_amount, sale_date,
NTILE(3) OVER (ORDER BY sales_amount) AS group_num
FROM sales;
```
这些示例展示了如何在Hive中使用窗口函数来处理数据,从而实现各种分析和计算需求。通过合理地使用窗口函数,可以在不需要额外的子查询或连接的情况下,对数据进行更加灵活和高效的处理。
12.常用函数
Hive作为一种流行的数据仓库解决方案,提供了丰富的内置函数来支持数据查询、转换和分析。下面是一些Hive中常用的函数:
1. 聚合函数:
- `COUNT()`:统计行数或非NULL值的数量。
- `SUM()`:计算数值列的总和。
- `AVG()`:计算数值列的平均值。
- `MAX()`:找出数值列的最大值。
- `MIN()`:找出数值列的最小值。
2. 字符串函数:
- `CONCAT()`:连接两个或多个字符串。
- `SUBSTRING()`:提取子字符串。
- `UPPER()`和`LOWER()`:将字符串转换为大写或小写。
- `LENGTH()`:计算字符串的长度。
- `TRIM()`:去除字符串两端的空格。
3. 日期函数:
- `YEAR()`、`MONTH()`、`DAY()`:提取日期的年、月、日部分。
- `TO_DATE()`:将字符串转换为日期类型。
- `DATEDIFF()`:计算两个日期之间的天数差。
- `DATE_FORMAT()`:格式化日期输出。
4. 条件函数:
- `CASE WHEN`:类似于SQL中的条件语句,用于根据条件返回不同的值。
- `COALESCE()`:返回参数列表中第一个非NULL值。
5. 类型转换函数:
- `CAST()`:将一个数据类型转换为另一个数据类型。
- `TO_STRING()`、`TO_INT()`、`TO_DOUBLE()`等:将不同类型的数据转换为字符串、整数、双精度浮点数等。
6. 集合函数:
- `COLLECT_SET()`:返回一个去重后的集合。
- `UNION()`、`INTERSECT()`、`EXCEPT()`:集合的并集、交集和差集。
7. 窗口函数:
- `ROW_NUMBER()`、`RANK()`、`DENSE_RANK()`:为结果集中的行分配排名。
- `NTILE(n)`:将结果集分割成n个大小尽可能相等的分区。
这些函数覆盖了Hive中常见的数据处理需求,通过合理利用这些函数,可以编写出高效且功能强大的Hive查询语句。
13.数据存储方式(行式,列式存储)
Hive支持两种主要的数据存储格式:行式存储和列式存储。这两种存储格式在数据的组织方式上有所不同,各自适用于不同的场景和查询需求。
1. 行式存储:
- 特点:行式存储将数据按行存储在文件中,即一行数据保存为一行记录,保持了数据的逻辑顺序。
- 优点:适用于 OLTP(联机事务处理)场景,支持快速的单行读写操作,特别是对于频繁进行更新和插入的操作。
- 缺点:不利于分析型查询,当查询需要涉及大量列时,性能较差,因为每次查询都需要读取整行数据,包含了大量不必要的信息。
2. 列式存储:
- 特点:列式存储将数据按列存储在文件中,即将同一列的数据保存在一起,而不同列的数据分别存储。
- 优点:适用于 OLAP(联机分析处理)场景,能够提供更高的压缩率和查询性能。因为查询通常只涉及少数列,列式存储可以只读取必要的列,减少了IO开销。
- 缺点:不利于频繁的更新和插入操作,因为更新或插入一行数据需要涉及多个列文件的修改。
在Hive中,默认的存储格式是行式存储,通常以文本文件或Parquet/ORC文件的形式存储在Hadoop分布式文件系统(HDFS)上。但是,Hive也支持列式存储格式,如ORC(Optimized Row Columnar)和Parquet,这些格式可以提高查询性能并减少存储空间占用。通过选择合适的存储格式,可以根据实际需求来优化查询性能和存储效率。