Hive总结

第一章 Hive基本概念

1.架构原理

cli:客户端命令行，可以在这里写sql
①.sql parser：sql解析器，得到sql以后，先解析，再通过元数据，可以在mysql中找到对应的元数据关系，这是一个准备阶段；
②.physical plan：编译器，将sql解析成一个mr的执行流程；
③.query optimizer：优化器，由于sql直接生成一个mr任务计划的时候，运行效率不一定高，所以会对sql进行一个内部优化；
④.execution：执行器，开始一个mr任务。

2.Hive和数据库比较

①.查询语言（有类似的地方）
②.数据更新：hive不支持delete和update，而数据库可以使用
因为hive的数据是存储在hdfs上，并且hdfs上的数据不支持随机修改，只支持追加操作。
③.执行延迟
hive没有索引，需要全表扫描，因此延迟比较高；并且hive的底层是mapreduce框架，而mapreduce执行延迟比较搞。
相对而言数据库执行延迟比较低，不过延迟低也只当数据规模比较小时而言。
③.数据规模
由于hive是建立在集群上，并且是用mapreduce进行并行计算，因此可以支持很大规模的数据，而数据库的数据规模就比较了。

第二章 Hive安装

1.Hive元数据

默认保存在内置的derby数据库中，只能开启一个hive客户端；建议设置在mysql数据中，可以支持多个hive客户端同运行
保存了hdfs路径和数据库、表的映射关系，通过db_id关联到数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KrM3I37Q-1626358507733)(F94977E879514606B86F8442D69228BF)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DJJxAMEs-1626358507734)(40A3C0A43C954642A024B90A12742BD5)]

count()执行mr时：
①. 通常情况下count()是直接读取元数据中的数据返回数量；
①. 如果是直接通过hadoop fs -put 文件路径上传的文件，不会修改元数据中的table_params表的数据，此时使用count()时，发现标准的numFils和numRows都没有改变，就直接返回numRows中的值；
②. 如果是使用load导入数据，此时会改变元数据，但本质上load命令还是使用的hadoop fs -put去执行上传文件任务，但是在元数据中的numFiles会改变，而numRows不会改变，所以此时用count()时，会运行mr任务去计算行数并返回。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hqsFKnah-1626358507736)(E8A85052E65D478CB49683D7B66E2DDF)]

第三章 Hive数据类型

1.类型转换

• 隐式类型转换规则如下
  ①.任何整数类型都可以隐式的转换为一个范围更广的类型，如tinyint可以转换成int，int可以转换成bigint；
  ②.所有整数类型、float类型和string类型都可以隐式的转换成double
  ③.tinyint、smallint、int都可以转换为float
  ④.Boolean不可以转换成任何类型

• 可以使用cast操作显示进行数据类型转换
  ①. cast(“1” as int)：将字符"1"转换成int类型1
  ②. 如果转换失败，则返回null值。

第四章 DDL(database define language：数据库定义语言) 数据定义

1.管理表和外部表

-- 创建一个外部表，用关键字external,不加就是创建内部表
create [external] table test(字段名1 字段类型...);

2. 区别

①. 在删除管理表时，会把原始数据删除，即连同hdfs上的文件也会删除
②. 在删除外部表时，只会把表删除，而原始数据不会删除
③. 在元数据中，外部表会被标记成EXTERNAL_TABLE，而内部表会被标记为MANAGED_TABLE
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8UFrOpnf-1626358507737)(4A39A3EA8B71423FAC134E8183A9E4C6)]

3.管理表和外部表的相互转换

-- 内部表转换成外部表
alter table test set tblproperties('EXTERNAL'='TRUE');

-- 外部表转换成内部表
alter table test set tblproperties('EXTERNAL'='FASLE');

-- 注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

4.清空表truncate

只能清空管理表，不能清空外部表中的数据。

第五章 DML(Database manipulation language)

向表中装载数据(load)

load data [local] inpath '数据所在的路径' [overwrite] into table 表名 [partition]

第六章 查询

1.基本查询

-- 全表查询
select * from 表名;

-- 选择特定字段查询
select 字段1, 字段2... from 表名;

-- 列别名
select 表名 as 别名,.... from 表名;

-- 算术运算符
-- +、-、*、/、%(取余)、&(按位取与)、|(按位取或)、^(按位取异或)、~(按位取反)

-- 常用函数
-- 求总行数, count(字段名|1|*)
select count(*) from 表名;

-- 求最大值, max(字段名)
select max(字段名) from 表名;

-- 求最小值, min(字段名)
select min(字段名) from 表名;

-- 求总和, sum(字段名)
select sum(字段名) from 表名;

-- 求平均值, avg(字段名)
select avg(字段名) from 表名;

-- limit语句,典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
select 字段名 from 表名 limit 起始位置, 终点位置

-- where语句
-- 使用where子句，将不满足条件的行过滤掉
-- where子句紧随from子句
select 字段名1,.... from 表名 where 条件1 and 条件2...;

-- 比较运算符between/in/is null
select 字段名1,... from 表名 where 限定条件1 between 字段名1 限定条件2;
select 字段名1,... from 表名 where 字段名 in (限定条件1, 限定条件2);
select 字段名1,... from 表名 where 字段名 is null;

-- like和rlike
-- 使用like运算选择类似的值
-- %表示任意个字符
-- _代表一个字符
select 字段名1,... from 表名 where 字段名1 like '%a%' or 字段名2 like '_a';

-- rlike可以通过正则表达式来匹配

-- 逻辑运算符(AND/OR/NOT)
-- and 逻辑与
-- or 逻辑或
-- not 逻辑否

2.分组

-- group by 通常会和聚合函数一起使用，按照一个或多个队列结果进行分组，然后对每个组执行聚合操作
select avg(字段名) from 表名 group by 字段名;

-- having语句
-- where 后面不能写分组聚合函数，而having后面可以使用分组聚合函数
-- having只能用于group by分组统计语句
select 字段名1,... from 表名 group by 字段名2 having 条件;

3.join: hive中只支持等值连接，不支持非等值连接

• 内连接：只会查出两个表都存在的数据

select * from t1 join t2 on t1.字段名=t2.字段名;

• 左连接：不仅会查出两个表都存在的数据，还会把左表在右表中查询不到的字段置为null

select * from t1 left join t2 on t1.字段名=t2.字段名;

• 右连接：不仅会查出两个表都存在的数据，还会把右表在左表中查询不到的字段置为null

select * from t1 right join t2 on t1.字段名=t2.字段名;

• 表的别名
  ①. 使用别名可以简化查询
  ②. 使用表名前缀可以提高执行效率

4.排序

• 全局排序(order by): 全局排序，只有一个reducer
  ①.默认是升序 asc；
  ②.降序：desc.
  ③.order by 排序时，无论设置了多少个reducer，都只会有一个reducer，这也是和distribute、sort by的不同的地方

select * from t1 order by 字段名;

• 多个列排序：order by 后面有多个字段

select * from t1 order by 字段名1, 字段名2...

• 每个reducer内部排序sort by：sort by为每个reduce产生一个排序文件。每个reducer内部进行排序，但是结果对全局来说不是排序的
  ①. 使用sort by时，是区内有序，并且是随机的分区
  ②. 如果只设置了一个reducer的情况下，排序的结果和order by一致，所以要设置多个reducer才能看出sort by的排序效果
  set mapreduce.job.reduces=3

• 分区distribute by：配合sort by一起使用，可以控制如何分区，相当于mr中的partition
  ①. distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。
  ②. Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
  ③. 需设置多个mapreduce.job.reduces才能显出效果

• 分区排序cluster by：当distribute by和sort by字段相同时，可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

• 四个排序方式的总结：
  ①. order by 全局排序，有且只会有一个reducer，效率比较低；
  ②. sort by 区内排序，单独使用，是随机划分区域进行排序，一般是配合distribute by一起使用；
  ③. 当sort by字段和distribute by字段相同时，会使用cluster by进行排序。

第七章 分区表和分桶表

##分区

1. 分区表

• hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。查询时通过指定的分区，会提高查询效率。

-- 创建分区关键字 partitioned(分区字段)
create table log_par(id int, url string) partitioned(day string)
row format delimited fields terminated by '\t';

-- 加载数据时指定分区，也可以不指定分区，因为自带一个默认分区，但是不建议
load data local inpath '本地数据所在路径' into table log_par partition(day='20210528');

-- 新增一个分区
alter table log_par add partition(day='20210529');

-- 新增多个分区，在写sql时，新增分区之间用空格隔开
alter table log_par add partition(day='20210530') partition(day='20210531');

-- 删除分区
alter table log_par drop partition(day='20210530');

-- 删除多个分区，在写sql时，删除分区之间用逗号隔开
alter table log_par drop partition(day='20210531'), (day='20210529');

-- 查看分区
show partition log_par;

-- 查看分区表结构
desc fromatted log_par;

2. 二级分区

• 当用一个字段分区时，数据量仍然很大时，可以用第二个字段再进一步分区，即二级分区

-- 二级分区和分区的区别在于二级分区是用两个字段进行分区
create table log_par1(id int, url string) partitioned(dat string, hour string)
row format delimited fields terminated by '\t';

-- 其他操作和分区是一样的

• 把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

①. 上传数据后进行修复

msck repair table log_par1;

②. 上传数据后添加分区

alter table log_per1 add partition(day='20210528');

③. 创建文件夹后load数据到分区

load data local inpath '本地数据所在路径' into table log_par1 partition(day='20210528');

3. 动态分区

• 关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置。

• 开启动态分区的参数
  hive.exec.dynamic.partition=true

• 设置为非严格模式(nostrick), 如果设置为严格模式(strick)，则表必须指定至少一个分区为静态分区，而非严格模式则表示所有字段都可以被作为动态分区的分区字段
  hive.exec.dynamic.partition.mode=nonstrict

• 在所有执行MR的节点上，最大一共可以创建多少个动态分区，默认为1000个
  hive.exec.max.dynamic.partitions=1000

• 在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。
  hive.exec.max.dynamic.partitions.pernode=100

• 整个MR job中，最大可以创建多少个hdfs文件，默认100000
  hive.exec.max.created.files=100000

• 当有空分区生成时，是否抛出异常。一般不需要设置。默认为false
  hive.error.on.empty.partition=false

2. 分桶表

• 必须使用字段中的一个字段作为分桶字段

-- 创建表
create table stu_tb(id int, name string)
clustered by(id)
row format delimited fields terminated by '\t';
![image](8AE448DEE0714F53B697ABCD5AD940B4)

• 导入数据

load data local inpath '/opt/module/hive-3.1.2-bin/datas/stuck.txt' into table stu_tb;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aqfOrw4K-1626358507738)(7F4D5ACC96244CF1AD6C14305190F487)]

• 分桶规则

根据结果可知：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

第八章 函数

• UDF：一进一出
• UDAF：多进一出
• UDTF：一进多出
• 常用函数

--常用日期函数
--unix_timestamp:返回当前或指定时间的时间戳	
select unix_timestamp();
select unix_timestamp("2020-10-28",'yyyy-MM-dd');

--from_unixtime：将时间戳转为日期格式
select from_unixtime(1603843200);

--current_date：当前日期
select current_date;

--current_timestamp：当前的日期加时间
select current_timestamp;

--to_date：抽取日期部分
select to_date('2020-10-28 12:12:12');

--year：获取年
select year('2020-10-28 12:12:12');

--month：获取月
select month('2020-10-28 12:12:12');

--day：获取日
select day('2020-10-28 12:12:12');

--hour：获取时
select hour('2020-10-28 12:12:12');

--minute：获取分
select minute('2020-10-28 12:12:12');

--second：获取秒
select second('2020-10-28 12:12:12');

--weekofyear：当前时间是一年中的第几周
select weekofyear('2020-10-28 12:12:12');

--dayofmonth：当前时间是一个月中的第几天
select dayofmonth('2020-10-28 12:12:12');

--months_between： 两个日期间的月份
select months_between('2020-04-01','2020-10-28');

--add_months：日期加减月
select add_months('2020-10-28',-3);

--datediff：两个日期相差的天数
select datediff('2020-11-04','2020-10-28');

--date_add：日期加天数
select date_add('2020-10-28',4);

--date_sub：日期减天数
select date_sub('2020-10-28',-4);

--last_day：日期的当月的最后一天
select last_day('2020-02-30');

--date_format(): 格式化日期
select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');

--常用取整函数
--round： 四舍五入
select round(3.14);
select round(3.54);

--ceil：  向上取整
select ceil(3.14);
select ceil(3.54);

--floor： 向下取整
select floor(3.14);
select floor(3.54);

--常用字符串操作函数
--upper： 转大写
select upper('low');

--lower： 转小写
select lower('low');

--length： 长度
select length("atguigu");

--trim：  前后去空格
select trim(" atguigu ");

--lpad： 向左补齐，到指定长度
select lpad('atguigu',9,'g');

--rpad：  向右补齐，到指定长度
select rpad('atguigu',9,'g');

--regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace('2020/10/25', '/', '-');

--集合操作
--size： 集合中元素的个数
select size(friends) from test3;

--map_keys： 返回map中的key
select map_keys(children) from test3;

--map_values: 返回map中的value
select map_values(children) from test3;

--array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test3;

--sort_array： 将array中的元素排序
select sort_array(friends) from test3;

--grouping set:多维分析

--空字段赋值
--nvl(value, default_value)
--如果value为null值，则将default_value的值返回，否则将value的值返回。

--case when else end
-- 如果在case后面接字段，则可以理解为java中的switch case default语句
-- 如果在case when后面接字段，则可以理解为java中的if elseif else语句

-- 行转列
-- concat(字段1,...) 将所有的字段拼接在一起;
-- concat_ws(分隔符, 字符or数组),一般配合collect_list(字段)和collect_set(字段)一起使用
-- collect_set(字段)：将字段的值去重，然后产生array类型字段
-- collect_list(字段)：将所有字段的值产生一个array类型的字段，不会去重

-- 列转行
-- 用到的函数说明
-- split(str, 分隔符)：将字符串按照分隔符进行切割，转换成array
-- explode(字段)：可以将一行array或在map拆分成多行
-- lateral view：lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
-- 以上函数配合使用
SELECT movie,category_name 
FROM movie_info 
lateral VIEW
explode(split(category,",")) movie_info_tmp  AS category_name ;

--开窗函数
--over():指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
--CURRENT ROW：当前行
--n PRECEDING：往前n行数据
--n FOLLOWING：往后n行数据
--UNBOUNDED：无边界
--	UNBOUNDED PRECEDING 前无边界，表示从前面的起点， 
--    UNBOUNDED FOLLOWING后无边界，表示到后面的终点
--LAG(col,n,default_val)：往前第n行数据
--LEAD(col,n, default_val)：往后第n行数据
--FIRST_VALUE (col,true/false)：当前窗口下的第一个值，第二个参数为true，跳过空值
--LAST_VALUE (col,true/false)：当前窗口下的最后一个值，第二个参数为true，跳过空值
--NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

-- 排序函数
-- rank():排序相同时会重复(考虑并列,会跳号)，总数不会变
-- dense_rank():排序相同时会重复(考虑并列,不跳号)，总数会减少
-- row_number():会根据顺序计算(不考虑并列,不跳号,行号)

第九章 压缩和存储

1.Hadoop的压缩配置

• MR支持的压缩编码
  • [1] deflate，不可切片
  • [2] gzip，不可切片
  • [3] bzip，可切片，压缩率最大，但是速度最慢
  • [4] lzo，可切片，压缩率不高，但是速度最快，hadoop一般会使用这个压缩，但是现在使用snappy去压缩
  • [5] snappy，不可切片，压缩率不高，官方说是在10%，但是实际在30%左右，但是解压缩速度最快，hadoop现在使用这个压缩方式去解压缩

2.文件存储格式

1.列式存储和行式存储

• 行式存储：按行进行存储数据，如mysql，例如select * 时，查询速度会比较快
• 典型的行式存储的类型：
  • [1] TEXTFILE
  • [2] SEQUENCEFILE
• 列式存储：按列进行存储数据，如hive，例如查找某一个字段时，查询速度会比较快，一般不会select *，都会指定字段，所以列式存储用的会比较多
• 典型的列式存储的类型：
  • [1] ORC
  • [2] PARQUET

2. TextFile格式

• 默认格式，数据不做压缩，磁盘和数据解析开销比较大。可结合gzip、bzip2一起使用

3. Orc格式

• 有一个或多个stripe组成，每个stripe和hdfs中的block大小差不多
• 每一个stripe包含多条记录，这些记录按照列式进行独立存储
• 每个stripe中有三部分组成：Index、Row Data、Stripe Footer
  -[1] Index Data：一个轻量级的index，默认是每隔10000行坐一个索引。这里做的索引是各字段在row data中的offset
  -[2] Row Data：存储具体的数据，先取部分，然后进行列式存储。再对列进行编码，分成多个stream存储
  -[3] stripe footer：存的是stream的类型，长度等信息

4.parquet

• 是以二进制存储的
• 不能直接读取
• 存储了文件的数据和元数据
• 是自解析的
  -[1] 行组 row group：每个行组包含一定的行数，在一个hdfs文件至少存储一个行组，类似orc中stripe
  -[2] 列块 column Chunk：在一个行组中每一列保存在一个列块中，行组中的所有列连续的存储在这个行组文件中。一个列块中的值都是相同类型的，不同的列块可能使用不同的算法进行压缩。
  -[3] 页 Page：每一个列块划分为多个页，一个页是最小的编码的单位，在同一个列块的不同页可能使用不同的编码方式。

第十章 企业级调优

1. fetch抓取

• fetch抓取指的是：在hive中对某些情况的查询可以不必经过mapreduce的计算；
• 例如：select * from 表名；
• 在hive的配置文件中，hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。

2. 本地模式

• 大多数hadoop job是需要Hadoop提供的完整的可扩展性来处理大数据集的；
• 但是如果hive的输入数据量非常小时，经过一系列的计算后也可能非常耗时，相比实际job执行的时间来比；
• hive可以通过本地模式在单台机器上处理所有的任务，对于小数据集，执行时间可以明显缩短。
• 可以通过设置hive.exec.mode.local.auto的值为true，来让hive在适当的时候自动启动优化

set hive.exec.mode.local.auto=true;  //开启本地mr
//设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;

3. 表的优化

• 小表、大表的join

  • [1] 可以减少内存溢出的错误
  • [2] 可以使用map join让维度小的表（1000条以下的记录）先进内存，在map端完成reduce
  • [3] 新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。

• 大表join大表

  • [1] 有时候key值异常，导致reduce数据量过大，造成数据倾斜
  • [2] 空key过滤：先将异常key值过滤，比如key值为null较多时，可以先过滤空值，再进行join操作
  • [3] 空key转换：有时候虽然key值为空值，但是相应的其他数据还是需要的，可以将key值赋一个随机值，使得数据均匀地分布到不同的reducer上
  • [4] SMB(sore merge buctet join)：先分桶，再按照分桶的字段进行join

• group by

  • [1] 可以将一部分聚合在map端进行
  • [2] 开启map端聚合参数设置
    • a. 是否在map端进行聚合，默认为true：set hive.map.aggr = true;
    • b. 在 Map 端进行聚合操作的条目数目：set hive.groupby.mapaggr.checkinterval = 100000;
    • c. 有数据倾斜的时候进行负载均衡（默认是 false）：set hive.groupby.skewindata = true;

• count distinct 去重统计

  • [1] 一般数据量大时，不会直接使用count distinct
  • [2] 先用group by去重(用group by的前提时，不会造成数据倾斜)
  • [3] 再用count去求出相应的字段的数量

• 笛卡尔积

• 行列过滤

  • [1] 列处理：只拿需要的列，有分区尽量使用分区过滤，不要使用select *；
  • [2] 行处理：先通过子查询过滤，然后再进行关联查询。如果先使用关联查询再进行wehre条件，则会先过滤全表再进行查询，效率比较低

• 分区

• 分桶

• 合理设置map及reduce的个数

  • [1] 复杂文件增加map数
  • [2] 小文件先进行合并
  • [3] 合理设置reduce的个数

• 并行执行

  • [1] 默认情况下hive一次只会执行一个阶段
  • 通过设置参数hive.exec.parallel为true，就可以开启并行执行

  set hive.exec.parallel=true;            //打开任务并行执行
  set hive.exec.parallel.thread.number=16;  //同一个 sql 允许最大并行度，默认为8。
  

• 严格模式

  • [1] 分区表不使用分区过滤：除非where语句中含有分区字段来过滤条件来限制范围，否则不允许执行：set hive.strict.checks.no.partition.filter=true;
  • [2] 使用order by 没有limit过滤：使用order by时，如果没有limit语句，则不会执行：set hive.strict.checks.orderby.no.limit=true;
  • [3] 笛卡尔积：会限制对笛卡尔积的查询：set hive.strict.checks.cartesian.product=true;

• jvm重用

• 压缩