hive

调优网站：https://www.cnblogs.com/sandbank/p/6408762.html

第一：Hive和关系型数据库的区别

1.Hive将外部的任务解析成一个MapReduce可执行计划，而启动MapReduce是一个高延迟的一件事，每次提交任务和执行任务都需要消耗很多时间，这也就决定Hive只能处理一些高延迟的应用（如果你想处理低延迟的应用，你可以去考虑一下Hbase）。即：hive不能像传统数据库那样完成实时交互式查询。

2.Hive目前还不支持事务；不能对表数据进行修改（不能更新、删除、插入；只能通过文件追加数据、重新导入数据）；

3.不能对列建立索引（但是Hive支持索引的建立，但是不能提高Hive的查询速度。如果你想提高Hive的查询速度，请学习Hive的分区、桶的应用）。

4.hive将元数据存在关系型数据库中，其数据存储在hdfs中。真实数据存储在hdfs中，因此大部分查询、计算由mapreduce来完成（但是select  * from table不会产生mapreduce任务，hivesql会不会转化成mapreduce取决于是否使用了聚合函数等）

5.hive在新增的时候不检查错误，在查询的时候才检查。

 

第二：hive的概念

1.hive是数据仓库，将元数据存储在关系型数据库中，例如mysql、oracle。hive中的元数据包括表的名字、表的列、分区及其属性、表的属性(是否为外部表等)。

元数据存在metastore中。metastore是服务器，mysql是客户端，客户端通过服务器 matastore server去请求mysql数据库

2.hive中的数据存储在hdfs中，大部分查询和计算由mapreduce来完成（因此不会是实时的查询，因为mr计算比较慢）

3.hive是类似sql的hql语句，主要用于查询分析，生成的查询计划存储在hdfs中，并在随后由mapreduce调用执行。

4.hive是解释器、编译器、优化器

5.编译器将一个hivesql转化成操作符，操作符是hive中最小的处理单元，每个操作符一个hdfs操作或者一个mapreduce操作。这些操作

例如select * from table是一个hdfs操作。select count(1) from table 是一个mapreduce操作。

6、客户端如何访问hive：①hive cli命令行接口（--shell中hive黑窗口）②jdbc查询(启动hiveserver2)③webui

7.单用户模式：只能启动一个--hive

8.为啥会有多个用户：因为多个用户对元数据的存储和管理不同。

9、由于我们使用了mysql当客户端，因此hive创建的表会在mysql客户端中找到。在导入数据的时候要先找到指定目录（/user/hive/warehouse），

这个目录是我们在配置hive的时候配置好的。然后把此目录下的文件数据加载到mysql中。

10.我想在hdfs中删除hive的目录文件，那得先找到对应的用户，加hive用户，才能删除hive的文件。删除后，mysql中对应的表中就无数据了。

11.mysql在创建表的时候，是按照hdfs中文件的数据格式创建的。然后在把hdfs中的文件通过load加载到mysql数据表中。如果表的格式与hdfs文件的格式有些不一样的话，

这些hdfs中的数据照样会被加载到数据表中，但是当查询数据表中的数据的时候会出问题。即读时检查。

 

 

 

第三：hive三种搭建模式

 

1.local模式:此模式连接到一个In-memory 的数据库Derby，一般用于Unit Test

2.单用户模式:用hive的黑窗口直接访问关系型数据库mysql。只能一个用户使用。

3.多用户模式（远程服务器模式）：元数据的管理由matastore（买死到）统一管理，他是个服务器。

  客户端通过服务器 matastore server去请求mysql数据库。服务器统一管理多个用户。

   hive-cli--->matastore server--->关系型数据库mysql

 

 

第三：sql语句

1.内部表的创建并添加数据

(1)在hive命令行窗口中创建表，创建好之后这个表就存在mysql中了，如果没指定具体哪个数据库，那肯定是默认数据库default。

查看表的详细信息可以看到数据的存放目录：hdfs://mycluster/user/hive/warehouse/psn0,其中/user/hive/warehouse是我们自己在配置文件中配置的。

create table psn0 (

id int,

name string,

likes ARRAY<string>,

address MAP<string, string>

) ROW FORMAT DELIMITED ---这是说明定义列是如何分割的（定义读写规则）。

FIELDS TERMINATED BY ',' -----列之间

COLLECTION ITEMS TERMINATED BY '-' -----集合（包括数组和map）元素之间

MAP KEYS TERMINATED BY ':';------map的key之间

(2).添加(加载)数据：准备一些测试数据，使用vim产生data1文件随便存放在虚拟机中的root目录下。

数据在本地：LOAD DATA LOCAL INPATH '/root/data1' INTO TABLE psn0;--（数据文件在本地虚拟机上，数据从本地拷贝到hdfs中的/user/hive/warehouse目录下）

数据在hdfs中：LOAD DATA INPATH '/data1' INTO TABLE psn0;--（文件直接移动到hdfs中的/user/hive/warehouse路径下。相当于剪切）

insert ino value的形式，我们一般不用了，用load。

 

2.外部表

（1），创建表。指定表中数据的存放目录：hdfs://mycluster/psn1

create EXTERNAL table psn1 (

id int,

name string,

likes ARRAY<string>,

address MAP<string, string>

) ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY '-'

MAP KEYS TERMINATED BY ':'

LOCATION '/psn1';---表的存放目录为hdfs中的psn1。（由于是外部表，因为需要自己指定要存到hdfs中的哪个目录中，元数据的存放位置）

LOAD DATA LOCAL INPATH '/root/data1' INTO TABLE psn1;---把/root/data1的数据导入到表psn1中，然后指定了数据仓库在/psn1路径下

 

 

3.内部表和外部表的区别：

当我们drop删除表的时候：外部表删除的时候只是把metastore的元数据删除，不删除hdfs中的表数据(只相当于一个连接、引用)（数据还在hdfs中的hdfs://mycluster/psn1下）。

内部表删除表时，元数据与数据都会被删除。

注意：内部表是hive自身管理(配置文件中配置好了)，所以都删除了。外部表只是相当于一个引用。

 

 

第四：分区（分区字段不在表中存在）

1.创建分区表

create table psn2 (

id int,

name string,

likes ARRAY<string>,

address MAP<string, string>

)

PARTITIONED BY (sex string) ---分区

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY '-'

MAP KEYS TERMINATED BY ':';

 

2.往分区表中导入数据

LOAD DATA LOCAL INPATH '/root/data1' INTO TABLE psn2 partition (sex='man');---把本地root/data1路径下面的sex='man'的数据导入数据库。其实就是导入文件中的部分内容。

LOAD DATA INPATH '/root/data2' INTO TABLE psn2 partition (sex='gril');--把hdfs中的/root/data2目录下的文件移动到hdfs中配置文件配置的指定路径下后将数据保存到mysql数据库

 

中的psn2表中。

 

3.查询psn2表

select *  from psn2 where sex='max'：此时查询不会全表扫描了，只会扫描指定分区的数据了。

即：不会扫描hdfs://mycluster/user/hive/warehouse/psn2路径，而只会把扫描hdfs://mycluster/user/hive/warehouse/psn2/max目录。

 

4.添加和删除分区

ALTER TABLE psn5 ADDPARTITION (sex='man', age=1);---注意:后面要跟所有的分区字段

select *  from psn2 where (sex='man', age=1)---查询的时候，后面也要跟所有的分区字段

ALTER TABLE psn5 DROP PARTITION (age=1);---注意:后面可以不跟所有字段。反正最终就是把age=1的所有数据都删除（可能来自不同的分区）

 

 

 

第五：like、as、insert select from、insert

1.create table psn4 like psn2;----相当于复制表结构，但是无数据。

2.CREATE TABLE tbl1--创建tbl1表，同时将表psn2的查询结果存到表tbl1

   AS

SELECT id, name , likes

FROM psn2;

 

3.我们在hive中的查询结果，都只是在控制台中的，现在这个写法直接把查询结果保存到了结果表中了，其核心还是存在了hdfs中了。

如果要找具体目录：hdfs://mycluster/user/hive/warehouse/page_view1。有了这个目录，我们就可以把数据导入到关系型数据库了。

FROM page_view_stg pvs--不用创建表，给表page_view_stg 中的结果存到page_view1表中

INSERT OVERWRITE TABLE page_view1

       SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.cnt

INSERT OVERWRITE TABLE page_view1

       SELECT pvs.viewTime, pvs.userid, pvs.page_url;

 

3.2

INSERT OVERWRITE TABLE page_view1

       SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.cnt

FROM page_view_stg pvs

 

4.insert into table tab0 values（id，name）

 

 

第六：函数

1、自定义函数包括三种UDF、UDAF、UDTF

2.UDF(User-Defined-Function)：用户自定义函数，一进一出，如trim

3.UDAF(User- Defined Aggregation Funcation) 聚集函数，多进一出，如count，max等

4.UDTF(User-Defined Table-Generating Functions)&#160; 一进多出，一可以是一个数组什么的，就一个函数：explode。如 explode(enjobs)。

explode是将数组一条记录中有多个参数，将参数拆分，每个参数生成一列。例如“爱好”

5.用户自定义函数，即自定义udf函数

6.

显示所有表:show tables

显示表信息（备注等）:desc 表名

显示表信息（更详细）:desc formatted 表名

新增数据:insert into tbl values(1,'xiaoming');

创建表:hive> create table tbl(id int,name string);

查询具体信息：Detailed Table Information

 

 

总结：我们使用hive主要是用于分析。用它替代mapreduce，那么我们就能用sql语句执行复杂业务了。

①那么hive为什么能替代mapreduce呢？

因为hive会将sql语句转化成mapreduce进行计算。

mapreudce计算的时候会去hdfs中找数据，而hive的数据也是存在hsfs中(元数据存在mysql中了，也可以将元数据存在oracle等)

②hive是数据仓库

因为hive不能像数据库那样实时交互式查询，他转成mapreduce是有延时的。

 

 

 

第七：动态分区

1.       动态分区：静态分区在导入表的时候（写load），一个load只能按照后面的条件导入符合条件(分区字段和分区值)的数据。但是如果按年龄分区，load只能导入一点点数据，为了能够导入。

2.       静态分区是人为手动指定，动态分区是根据数据来进行判断

所有数据，引入了动态分区。它自动把文件中的数据

按照创建表时候建立的分区，划分到不同的分区。

注意：load方式要写具体的值partition(sex="man")，因此无法作为动态分区。因此动态分区要使用下面的方式：先把数据导入到普通的表

中，然后在把insert的形式添加到分区表中,insert不用指定分区值。

create table psn20 (---第一步创建普通表（临时表）

id int,

name string,

sex string,

age int,

likes ARRAY<string>,

address MAP<string, string>

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY '-'

MAP KEYS TERMINATED BY ':';

 

LOAD DATA LOCAL INPATH '/root/data3' INTO TABLE psn20;---第二步把文件内容导入到普通表中

create table psn21 (-----第三步创建分区表（正式表，真正要的表）

id int,

name string,

likes ARRAY<string>,

address MAP<string, string>

)

PARTITIONED BY (sex string, age int)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY '-'

MAP KEYS TERMINATED BY ':';

 

from psn20----第四步：把临时表中的数据导入到正式表中。此时配置文件的目录下有了所有的分区。并且psn21表中也包含了所有数据。

insert overwrite table psn21 partition(sex, age) 

select id, name, likes, address, sex, age distribute by sex, age;

 

 

2.动态分区思路：由于load是文件拷贝，所以load形式肯定又会是静态分区了，

产生了分区目录了。所以使用insert的形式完成。

先创建一个没有分区的普通表（带着第二张表的分区字段），然后第二张分区表用from insert。

这样的结果就是一个sql把所有数据都完成了，而且也都进行分区了。

 

 

第八：分桶

分桶：分区是产生多个目录，分桶是产生多个文件。先有分区后有分桶，分桶是分区的细化。

对于hive中每一个表、分区都可以进一步进行分桶，由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中

 

分区是便于查询，分桶是为了数据抽样（把数据分成好多小文件打散了，之后随机抽取），

分桶还可以做join连接，提高查询效率（笛卡尔积，避免全表扫描）。

举例：A表和B表两个表用id做关联，即A.id join b.id，这样需要全表扫描。现在这两个表都用id做分桶（假设A表和B表都分成了1号桶、2号桶、3号桶）。做了分桶之后的效果就是A表中的1

 

号桶的数据去关联B表中1号桶的数据，不用关联整个B表。A表中的2号桶的数据关联B表中2号桶的数据。

 

下面是x与y的使用方法:

x表示从哪个bucket桶开始抽取数据

y必须为该表总bucket桶数的倍数或者因子

现在假设桶的个数是32，x=3，y=8，那么32/8=4,说明我们要从3号桶开始取四个桶的数据。

即3，3+8，3+8+8,3+8+8+8，也就是取第3号、第11号、第19号、第27号桶的个数

 

 

 

第九：lateral view来解决udtf的缺点（udtf只能对一列查询）

lateral view的引入：解决udtf函数的缺点。explode类型的查询只能查询一个字段（因为这个字段在查询出结果的时候会产生多个列），为了能select后面能查询多个列，因此引入lateral

 

view。

lateral  view语法如下：

select explode(likes),id from psn20----这样会报错，因为explode(likes)是一个拆分（一对多），查询的时候只能查询这一个。

正确做法：统计人员表中共有多少种爱好、多少个城市(其实就是起个别名)：

select count(distinct(myCol1)), count(distinct(myCol2)) from psn2

LATERAL VIEW explode(likes) myTable1 AS myCol1

LATERAL VIEW explode(address) myTable2 AS myCol2, myCol3;

 

 

 

第十：view试图和索引：

创建索引后会在table表中生成一个新索引表，此表中无数据。需要重建索引才能生效（生效的时候会转成mapreduce进行产生数据），生效之后里面就有数据了，包括索引名，分区名，桶名

 

，偏移量。

如果table表中又新增了数据，那么又得生效一次。

查看这个表中的索引：show index on 表名

2.创建索引

2.1：create index t1_index on table psn2(name)

as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild

in table t1_index_table;

（as：指定索引器；in table：指定索引表，若不指定默认生成在default__psn2_t1_index__表中）

 

2.2：create index t1_index on table psn2(name)

as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild;

 

2.3：创建好索引后，会产生索引表，索引表中的列包括索引名称、桶的名称、偏移量、分区名称，但是索引表中无数据。

如果想要索引表中有数据，那么就要重建索引。索引也是需要资源的。

重建索引：ALTER INDEX t1_index ON psn2 REBUILD

查询索引:show index on psn2

删除索引:DROP INDEX IF EXISTS t1_index ON psn2;

 

 

第十一：hive的运行方式与权限控制。

1.Hive运行方式：

命令行方式cli：控制台模式

脚本运行方式（实际生产环境中用最多）

JDBC方式：hiveserver2

web GUI接口（hwi、hue等）

2.权限：过

 

第十二：hive优化

1.核心思想：把Hive SQL 当做Mapreduce程序去优化

2.select *  from tab  where id=1 不会转化为mapreduce

3.显示sql执行计划：explain select *  from tab ，可以通过执行计划进行分析。

4.优化1：设置运行方式：测试在本地（数据量小），生产在集群中。开启本地方式：set hive.exec.mode.local.auto=true;

5，优化2：并行计算：最好在集群性能负载不是很高的时候开启。

通过设置以下参数开启并行模式：set hive.exec.parallel=true

一次SQL计算中允许并行执行的job个数的最大值：hive.exec.parallel.thread.number

6.优化3:开启严格模式：set hive.mapred.mode=strict。

默认是非严格模式，严格模式会限制下面的内容:

(1)、对于分区表，必须添加where对于分区字段的条件过滤；

(2)、order by语句必须包含limit输出限制；

(3)、限制执行笛卡尔积的查询。

7.优化4：排序

Order By - 对于查询结果做全排序，只允许有一个reduce处理

（当数据量较大时，应慎用。严格模式下，必须结合limit来使用）

Sort By - 对于单个reduce的数据进行排序

Distribute By - 分区排序，经常和Sort By结合使用

 

8.优化5：join

（1）.将小表放在左边

(2).尽量在map端进行join

自动的mapjoin

通过修改以下配置启用自动的mapjoin：

set hive.auto.convert.join = true;

（该参数为true时，Hive自动对左边的表统计量，如果是小表就加入内存，即对小表使用Map join）

 

相关配置参数：

hive.mapjoin.smalltable.filesize; 

（大表小表判断的阈值，如果表的大小小于该值则会被加载到内存中运行）

hive.ignore.mapjoin.hint；

（默认值：true；是否忽略mapjoin hint 即mapjoin标记）

hive.auto.convert.join.noconditionaltask;

（默认值：true；将普通的join转化为普通的mapjoin时，是否将多个mapjoin转化为一个mapjoin）

hive.auto.convert.join.noconditionaltask.size;

（将多个mapjoin转化为一个mapjoin时，其表的最大值）

 

 

9、优化6：map端聚合

通过设置以下参数开启在Map端的聚合：

set hive.map.aggr=true;

 

相关配置参数：

hive.groupby.mapaggr.checkinterval：

map端group by执行聚合时处理的多少行数据（默认：100000）

hive.map.aggr.hash.min.reduction：

进行聚合的最小比例（预先对100000条数据做聚合，若聚合之后的数据量/100000的值大于该配置0.5，则不会聚合）

hive.map.aggr.hash.percentmemory：

map端聚合使用的内存的最大值

hive.map.aggr.hash.force.flush.memory.threshold：

map端做聚合操作是hash表的最大可用内容，大于该值则会触发flush

hive.groupby.skewindata

是否对GroupBy产生的数据倾斜做优化，默认为false

 

 

10.优化7：控制Hive中Map以及Reduce的数量（用set命令就修改了）

Map数量相关的参数

mapred.max.split.size

一个split的最大值，即每个map处理文件的最大值

mapred.min.split.size.per.node

一个节点上split的最小值

mapred.min.split.size.per.rack

一个机架上split的最小值

 

Reduce数量相关的参数

mapred.reduce.tasks

强制指定reduce任务的数量

hive.exec.reducers.bytes.per.reducer

每个reduce任务处理的数据量

hive.exec.reducers.max

每个任务最大的reduce数

 

11.优化8：JVM重用

适用场景：（1）、小文件个数过多。（2）、task个数过多。

task过多，就会导致每次执行task的时候都会申请资源、释放资源。

通过 set mapred.job.reuse.jvm.num.tasks=n; 来设置卡槽个数。

缺点：设置开启之后，task插槽会一直占用资源，不论是否有task运行，直到所有的task即整个job全部执行完成时，才会释放所有的task插槽资源！