1.Hive的概述

√ 意义：在于大幅度降低工程师学习MapReduce的学习成本，让好用（计算速度快）的MapReduce更方便的使用（使用简单）

√ 基本概念：Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能(HQL)。

√ 本质：其本质是将SQL转换为MapReduce的任务进行运算，底层由HDFS来提供数据的存储，hive可以理解为一个将SQL转换为MapReduce的任务的工具。

√ Hive可以做数据存储,可以做数据分析; Hive的存储依赖于HDFS; Hive的分析依赖于MapReduce.

√ 特点:

1)可扩展: 可以自由地扩展集群的规模,一般不需要重启服务

2)延展性:   支持用户自定义函数,可以根据自己的需求实现自己的函数

3)容错:   良好的容错性,节点出现问题SQL仍可完成执行

2.Hive的架构

元数据:  描述数据(表属性)的数据---表属性包括:表名,字段名,字段类型.


解释器、编译器、优化器、执行器:完成HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS 中，并在随后有MapReduce 调用执行.

===每天练习SQL语句===

3.Hive的使用场景: 海量数据{前提} 离线分析{场景}（关键！）

少量的数据不建议使用Hive（效率低）。

延时性高！---不适合在线数据查询（快速查询）

有索引，但是默认不创建！

没有专门的数据格式；用户可以自定义（三个属性）

加载时，不需要转换格式；不会对数据本身进行修改，甚至不会扫描（相当于拷贝）

不支持对数据的修改和添加，加载时已确定好；

建立在Hadoop之上，Hive的可扩展性和Hadoop的可扩展性是一致的！

===总结：hive具有sql数据库的外表，但应用场景完全不同，hive只适合用来做批量数据统计分析。

4.Hive的数据存储

===数据模型：

DB：数据库

Table：数据表（内部表）

External Table：外部表

Partition：表的分区

Bucket：表的桶

5.Hive的安装部署

===安装hive

/etc/profile文件是系统的核心配置文件，尽量不要修改！------使用/etc/profile.d,添加需要的脚本文件后，source /etc/profile即可

在/etc/profile.d路径下添加一个脚本，将需要添加的配置填写在脚本中

export HIVE_HOME=/export/servers/hive-1.1.0-cdh5.14.0 export PATH=:PATH

最后 source /etc/profile

===安装MySQL【使用yum源】

===修改hive的配置文件【注意修改hive-site.xml中MySQL的位置在哪台机器上】

===上传MySQL的lib驱动包

===使用方式

###配置环境变量后，可以在任意目录下直接使用hive###

1）第一种：Hive交互shell------在hive、bin的目录下使用bin

cd /export/servers/hive-1.1.0-cdh5.14.0

bin/hive

image.png

• 查看所有数据库

hive (default)> show databases;

• 创建一个数据库

hive (default)> create database myhive;

• 使用该数据库并创建数据库表

hive (default)> use myhive;

hive (myhive)> create table test(id int,name string);

以上命令操作完成之后，一定要确认mysql里面出来一个数据库hive

image.png

2）第二种：Hive JDBC 服务【hive的默认端口是10000】

• 启动hiveserver2服务

• 前台启动

cd /export/servers/hive-1.1.0-cdh5.14.0

bin/hive --service hiveserver2

image.png

• 后台启动

cd /export/servers/hive-1.1.0-cdh5.14.0

nohup bin/hive --service hiveserver2 &

image.png

• beeline连接server2

bin/beeline

beeline> !connect jdbc:hive2://node01.hadoop.com:10000

image.png

注意：如果使用beeline方式连接hiveserver2，一定要保证hive在mysql当中的元数据库已经创建成功，不然就会拒绝连接

3）第三种：Hive命令

• 使用 –e 参数来直接执行hql的语句

bin/hive -e "use myhive;select * from test;"

4）第四种：使用 –f  参数通过指定文本文件来执行hql的语句

vim hive.sql

use myhive;select * from test;

bin/hive -f hive.sql

6.Hive基本操作

6.1 创建数据库与创建数据库表

• 创建数据库

创建数据库

create database if not exists myhive;

use myhive;

===说明：hive的表存放位置模式是由hive-site.xml当中的一个属性指定的

<name>hive.metastore.warehouse.dir</name>

<value>/user/hive/warehouse</value>

Hive的数据库、表、分区在HDFS上都是以一个文件夹的方式存在的

【创建数据库或者表时，带“location”，就在指定的位置上创建，不带就是/user/hive/warehouse/中】

• 创建数据库并指定hdfs的存储位置

create database myhive2 location '/myhive2';

• 修改数据库

可以使用alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置

alter database myhive2 set dbproperties('createtime'='20200606');

• 查看数据库的详细信息

查看基本信息

desc database myhive2;

查看数据库更多详细信息

desc database extended myhive2；

• 删除数据库

删除一个空数据库，如果数据库下面有数据表，那么就会报错

drop database myhive2;

强制删除数据库，包含数据库下面的表一起删除

drop database myhive cascade; 不要执行（危险动作）

• 创建数据库表操作

创建数据库表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

[(col_name data_type [COMMENT col_comment], ...)]

[COMMENT table_comment]

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

[CLUSTERED BY (col_name, col_name, ...)

[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

[ROW FORMAT row_format]

[STORED AS file_format]

[LOCATION hdfs_path]

6.2 管理表

• hive建表

use myhive;

create table stu(id int,name string);

insert into stu values (1,"zhangsan");

select * from stu;

• 创建表并且指定字段之间（列）的分隔符

create table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t' stored as textfile location '/user/stu2';

insert into stu2 values (1,"zhangsan");

insert into stu2 values (2,"lisi");

insert into stu2 values (3,"wangwu");

• 根据查询结果创建表

create table stu333 as select * from stu2;

• 根据已经存在的表结构创建表

create table stu4 like stu2;

• 查询表的类型

desc stu2；【信息】

desc formatted stu2；【详细信息】

• 数据加载

建表（案例）：

create table student (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by '\t';

从本地文件系统向表中加载数据【local：Linux系统；没有local，就是hdfs系统（加载数据方式相同）;into之前加overwrite会先将数据清空再添加数据】

load data local inpath '/export/servers/hivedatas/student.csv' into table student_tmp;

6.3 外部表：

• 外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉

===csv文件的默认分隔符是“，”

• 案例===创建外部表并且添加数据

create external table techer (t_id string,t_name string) row format delimited fields terminated by '\t';

create external table student (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by '\t';

• 加载数据同上

从hdfs文件系统向表中加载数据（需要提前将数据上传到hdfs文件系统，其实就是一个移动文件的操作）

cd /export/servers/hivedatas

hdfs dfs -mkdir -p /hivedatas

hdfs dfs -put techer.csv /hivedatas/

load data inpath '/hivedatas/techer.csv' into table techer;

===内部表和外部表的区别：删除表时，内部表的元数据，数据文件同时删除；外部表是删除元数据，真正的数据文件不删除。

6.4 分区【不同的文件夹】表

• 创建表【分区的字段绝对不能出现在表的字段里】

创建分区表语法

create table score(s_id string,c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by '\t';

创建一个表带多个分区

create table score2 (s_id string,c_id string, s_score int) partitioned by (year string,month string,day string) row format delimited fields terminated by '\t';

• 加载数据

加载数据到分区表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');

加载数据到一个多分区的表中去

load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');

• 查询

多分区联合查询使用union all来实现

select * from score where month = '201806' union all select * from score where month = '201806';

• 查看分区

show partitions score;

• 添加分区

添加一个分区

alter table score add partition(month='201805');

同时添加多个分区

alter table score add partition(month='201804') partition(month = '201803');

注意：

1）数据在加载时，工程师必须清楚的知道这个数据是属于哪个分区的

2）添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹

• 删除分区

删除分区

alter table score drop partition(month = '201806');

• 案例练习

6.5 分桶表【减少了JOIN的数据量】

   将数据按照指定的字段进行分成多个桶中去，说白了就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去

***桶可以作用在hive的表之上，还可以作用在hive的分区之上***

===>必须按照的顺序：

开启hive的桶表功能

set hive.enforce.bucketing=true;

设置reduce的个数

set mapreduce.job.reduces=3;

• 使用语法

创建普通表：

create table course_common (c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';

普通表中加载数据

load data local inpath '/export/servers/hivedatas/course.csv' into table course_common;

通过insert overwrite给桶表中加载数据

insert overwrite table course select * from course_common cluster by(c_id);

===分桶的字段必须在已有的字段里！

6.6 修改表

• 表重命名

  基本语法：

  alter table old_table_name rename to new_table_name;

  把表score4修改成score5

  alter table score4 rename to score5;

• 增加/修改列信息

（1）查询表结构

desc score5;

（2）添加列

alter table score5 add columns (mycol string, mysco string);

（3）查询表结构

desc score5;

（4）更新列

alter table score5 change column mysco mysconew int;

（5）查询表结构

desc score5;

• 删除表

drop table score5;

6.7 hive表中加载数据

• 直接向分区表中插入数据

create table score3 like score;

insert into table score3 partition(month ='201807') values ('001','002','100');

• 通过查询插入数据

通过load方式加载数据

load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');

通过查询方式加载数据

create table score4 like score;

insert overwrite table score4 partition(month = '201806') select s_id,c_id,s_score from score;

注: 关键字overwrite 必须要有

• 多插入模式

给score表加载数据

load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');

创建第一部分表：

create table score_first( s_id string,c_id string) partitioned by (month string) row format delimited fields terminated by '\t' ;

创建第二部分表：

create table score_second(c_id string,s_score int) partitioned by (month string) row format delimited fields terminated by '\t';

分别给第一部分与第二部分表加载数据

from score

insert overwrite table score_first partition(month='201806') select s_id,c_id

insert overwrite table score_second partition(month = '201806') select c_id,s_score;

• 查询语句中创建表并且加载数据（as select）

将查询结果保存到一张表中去

create table score5 as select * from score；

• 创建表时通过location制定加载数据路径

1. 创建表，并指定在hdfs上的位置

create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by '\t' location '/myscore6';

2）上传数据到hdfs上

hdfs dfs -mkdir -p /myscore6

hdfs dfs -put score.csv /myscore6;

3）查询数据

select * from score6;

• 清空表数据

只能清空管理表，也就是内部表

truncate table score5;

7.hive查询语法

7.1 select

image.png

注【hive中的排序】：

1）order by 会对输入做全局排序，因此只有一个reducer时，会导致当输入规模较大时，需要较长的计算时间。

2）sort by不是全局排序是输入做全局排序，其在数据进入reducer前完成排序。【reduce内部有序】

3）distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列【distribute by经常和sort by配合使用】。

4）Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序【但是排序只能是倒叙排序，不能指定排序规则为ASC或者DESC】。

===因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by

===分桶表的作用：最大的作用是用来提高join操作的效率；【可以作为优化的选项】

• 全表查询

  select * from score;

• 选择特定列查询

  select s_id ,c_id from score;

• 列别名

  1）重命名一个列。

  2）便于计算。

  3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’

  select s_id as myid ,c_id from score;

7.2 常用函数

1）求总行数（count）

select count(1) from score;

2）求分数的最大值（max）

select max(s_score) from score;

3）求分数的最小值（min）

select min(s_score) from score;

4）求分数的总和（sum）

select sum(s_score) from score;

5）求分数的平均值（avg）

select avg(s_score) from score;

7.3 limit语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

select * from score limit 3;

7.4 WHERE语句

1）使用WHERE 子句，将不满足条件的行过滤掉。

2）WHERE 子句紧随 FROM 子句。

3）案例实操

查询出分数大于60的数据

select * from score where s_score > 60;

7.5 比较运算符（between/in/is null）

2）案例实操

（1）查询分数等于80的所有的数据

select * from score where s_score = 80;

   （2）查询分数在80到100的所有数据

select * from score where s_score between 80 and 100;

   （3）查询成绩为空的所有数据

select * from score where s_score is null;

   （4）查询成绩是80和90的数据

select * from score where s_score in(80,90);

7.6 like和rlike

1）使用LIKE运算选择类似的值

2）选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

4）案例实操

   （1）查找以8开头的所有成绩

select * from score where s_score like '8%';

   （2）查找第二个数值为9的所有成绩数据

select * from score where s_score like '_9%';

   （3）查找成绩中含9的所有成绩数据

select * from score where s_score rlike '[9]';

7.7 逻辑运算符（and/or/not）

案例实操

   （1）查询成绩大于80，并且s_id是01的数据

select * from score where s_score >80 and s_id = '01';

   （2）查询成绩大于80，或者s_id  是01的数

select * from score where s_score > 80 or s_id = '01';

   （3）查询s_id  不是 01和02的学生

select * from score where s_id not in ('01','02');

8.Hive Shell参数
略

9.Hive函数
略

10.hive的数据压缩
略

11.hive的数据存储格式

===行存储的使用场景：只需要找到其中的一个值，其余的值都在相邻的地方，【这个值周边的字段大部分的业务都会使用到】

===列存储的使用场景：对少量字段但是数据条数很多的情况下

===数据格式（4种）}：TEXTFILE（行存） SEQUENCEFILE（行存） ORC（列存） PARQUET（列存）

12.存储和压缩结合

===hive压缩算法和存储格式的选择：

在实际的项目开发当中，hive表的数据存储格式一般选择：orc或parquet。压缩方式一般选择snappy。

13.调优

13.1 fetch抓取（hive可以避免进行MapReduce）

【hive.fetchtask.conversion有三个值：minimal more none】

 hive.fetchtask.conversion对以下三种语句不会转化成MR：

1）select* from table；

2）select字段 from table；

3）select字段from table limit N；

hive.fetchtask.conversion为none时对所有语句都会转化成MR

13.2 本地模式

此方法将MapReduce程序在本地执行，不提交到集群中（任务未分配到集群上运行）。

对于小数据集，使用本地模式执行时间可以明显被缩短。

本地模式设置：

1）开启本地模式：set hive.exec.mode.local.auto=true; 

2）设置local mr接收的的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，默认为134217728，即128M（是否在本地执行的分界点）：set hive.exec.mode.local.auto.inputbytes.max=51234560;

3）//设置local mr接收的文件个数的最大个数，当输入文件个数小于这个值时采用local mr的方式，默认为4（是否在本地执行的分界点）：set hive.exec.mode.local.auto.input.files.max=10;

13.3 Group By

默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。

并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。

==开启Map端局部聚合参数设置【使用场景需要谨慎考虑，不能使用在类似平均数的算法上，此功能会开启多个任务】

（1）开启mapj聚合：是否在Map端进行聚合，默认为True

set hive.map.aggr = true;

（2）设置聚合的数量：在Map端进行聚合操作的条目数目

set hive.groupby.mapaggr.checkinterval = 100000;

（3）数据倾斜时负载均衡：有数据倾斜的时候进行负载均衡（默认是false）

set hive.groupby.skewindata = true;

当选项设定为 true，生成的查询计划会有两个MR Job。

13.4 Count(distinct)

基于语法级别的优化

1）SELECT count(DISTINCT id) FROM bigtable;---效率低下

2）SELECT count(id) FROM (SELECT id FROM bigtable GROUP BY id) a;---效率较高

===在海量数据的前提下，第二种较快！！！

13.5 笛卡尔积

尽量避免无效的on条件或不再使用条件

13.6 使用分区剪裁、列剪裁

在查询语句中，尽量使用哪些列就读取哪些列，业务需要使用哪些分区的数据，就读取哪些分区

关于join的优化：尽量将where条件添加在on后面

尽量优先过滤数据再进行数据的join，尽量避免先join后后过滤

13.7 动态分区的调整

以第一个表的表结构为准，将第一个表的表结构完全复制到第二个表中，第二个表的数据在加载时，就不需要指定分区

开启动态分区参数设置

（1）开启动态分区功能（默认true，开启）

set hive.exec.dynamic.partition=true;

（2）设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）

set hive.exec.dynamic.partition.mode=nonstrict;

（3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。

set hive.exec.max.dynamic.partitions=1000;

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

set hive.exec.max.dynamic.partitions.pernode=100

（5）文件句柄数：整个MR Job中，最大可以创建多少个HDFS文件。

   在linux系统当中，每个linux用户最多可以开启1024个进程，每一个进程最多可以打开2048个文件，即持有2048个文件句柄，下面这个值越大，就可以打开文件句柄越大

set hive.exec.max.created.files=100000;

（6）当有空分区生成时，是否抛出异常。一般不需要设置。

set hive.error.on.empty.partition=false;

13.8 分桶

作用：1）便于数据取样；2）对于join的查询语句有优化的效果

13.9 数据倾斜

13.9.1 map数量

通常情况下，作业会通过input的目录产生一个或者多个map任务。

影响map数量的因素：1）基于数据块的数量；2）文件的数量；

map的数量要按照实际情况定，不是越多越好，也不是越少越好；综合考虑！

在数据分析时，尽量合理的设置map的数量，若一个大文件，字段较少，但是数据量较多，此时map的数量较少，会导致每个map处理的数据很多，效率很差，解决方案就是增加map的数量；

增加map的数量方案：

set mapreduce.job.reduces =10;

create table a_1 as

select * from a

distribute by rand(123);【对随机数取余】

文件数量很多，但每个文件内的数据量较少，此时会开启很多map，map的开销时间远远会高于计算时间。导致数据量较少但时间较长。解决方案---小文件合并！

13.9.2 reduce的数量

1）方法一：直接修改参数：set mapreduce.job.reduces = 15

2）方法二：

（1）每个Reduce处理的数据量默认是256MB

hive.exec.reducers.bytes.per.reducer=256123456

（2）每个任务最大的reduce数，默认为1009

hive.exec.reducers.max=1009

（3）计算reducer数的公式

N=min(参数2，总输入数据量/参数1)

13.10 并行执行

hive将sql转换成mapreduce，程序在执行有多个阶段，可以开启hive的并行执行功能。

set hive.exec.parallel=true; //打开任务并行执行

set hive.exec.parallel.thread.number=16; //设置并行度：同一个sql允许最大并行度，默认为8

13.11 严格模式

Hive提供了一个严格模式，可以防止用户执行“高危”的查询。

严格模式下，一下sql语句不允许执行：

1）用户不允许扫描所有分区

2）使用了order by语句的查询，要求必须使用limit语句

3）限制笛卡尔积的查询

13.12 JVM重用

mapreduce任务在运行时，会开启大量的JVM，默认用完后会自动释放，新的task需要时会重新开启JVM，JVM频繁的开启消耗时间较多。

开启JVM重用，一个JVM结束后不释放，新的task需要时直接使用，这样减少了jvm的开启次数。从而起到了调优的效果。【每个jvm启动的时间大约1s】

开启jvm在MapRed-site.xml中添加参数：

<property>

<name>mapreduce.job.jvm.numtasks</name>

<value>10</value>

<description>How many tasks to run per jvm. If set to -1, there is

no limit.

</description>

</property>

也可以在hive当中通过：set  mapred.job.reuse.jvm.num.tasks=10;这个设置来设置我们的jvm重用

13.13 推测执行【谨慎使用】

开启map推测执行：

Hadoop的mapred-site.xml文件中进行配置：

<property>

<name>mapreduce.map.speculative</name>

<value>true</value>

<description>If true, then multiple instances of some map tasks

           may be executed in parallel.</description>

</property>

<property>

<name>mapreduce.reduce.speculative</name>

<value>true</value>

<description>If true, then multiple instances of some reduce tasks

           may be executed in parallel.</description>

</property>

开启reduce的推测执行：

reduce-side的推测执行：

<property>

<name>hive.mapred.reduce.tasks.speculative.execution</name>

<value>**true**</value>

<description>Whether speculative execution for reducers should be turned on. </description>

</property>