HIVE总结
目录
HIVE总结
1.安装hive
2.理解hive工作原理（一定要理解）
3.会创建内部表，会加载数据
4.会创建分区表，会加载数据
5.会创建外部表
6.会创建UDF函数
7.会设置map的个数
8.会设置reduce个数
9.会根据执行计划对sql进行调优
10.会处理数据倾斜问题
11.窗口函数
12.常用函数
13.数据存储方式(行式，列式存储)
1. 行式存储：
2. 列式存储：
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，这些格式可以提高查询性能并减少存储空间占用。通过选择合适的存储格式，可以根据实际需求来优化查询性能和存储效率。