hive

第一：Hive和关系型数据库的区别
1.Hive将外部的任务解析成一个MapReduce可执行计划，而启动MapReduce是一个高延迟的一件事，每次提交任务和执行任务都需要消耗很多时间，这也就决定Hive只能处理一些高延迟的应用（如果你想处理低延迟的应用，你可以去考虑一下Hbase）。即：hive不能像传统数据库那样完成实时交互式查询。
2.Hive目前还不支持事务；不能对表数据进行修改（不能更新、删除、插入；只能通过文件追加数据、重新导入数据）；
3.不能对列建立索引（但是Hive支持索引的建立，但是不能提高Hive的查询速度。如果你想提高Hive的查询速度，请学习Hive的分区、桶的应用）。
4.hive将元数据存在关系型数据库中，其数据存储在hdfs中。

第二：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命令行接口（--hive黑窗口）②jdbc查询(启动hiveserver2)③webui
7.单用户模式：只能启动一个--hive
8.为啥会有多个用户：因为多个用户对元数据的存储和管理不同。
9、由于我们使用了mysql当客户端，因此hive创建的表会在mysql客户端中找到。在导入数据的时候要先找到指定目录（/user/hive/warehouse），
这个目录是我们在配置hive的时候配置好的。然后把此目录下的文件数据加载到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只能导入一点点数据，为了能够导入所有数据，引入了动态分区。它自动把文件中的数据
按照创建表时候建立的分区，划分到不同的分区。
注意：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插槽资源！