Hive知识汇总

一、Hive基础

Hive是建立在hadoop上的数据仓库基础架构。
Hive执行入口是Driver，执行的SQL语句首先提交到Drive驱动，然后调用compiler解释驱动，最终解释成MapReduce任务去执行。
Hive的组件：
	 1. Driver组件：该组件包括：Compiler、Optimizer、Executor,它可以将Hive的编译、解析、优化转化为MapReduce任务提交给Hadoop1中的JobTracker或者是Hadoop2中的SourceManager来进行实际的执行相应的任务。
	 2. MetaStore组件：存储着hive的元数据信息，将自己的元数据存储到了关系型数据库当中，支持的数据库主要有：Mysql、Derby、支持把metastore独立出来放在远程的集群上面，使得hive更加健壮。元数据主要包括了表的名称、表的列、分区和属性、表的属性（是不是外部表等等）、表的数据所在的目录。
	 3. 用户接口：CLI（Command Line Interface)(常用的接口：命令行模式）、Client:Hive的客户端用户连接至Hive Server ,在启动Client的时候，需要制定Hive Server所在的节点，并且在该节点上启动Hive Server、WUI:通过浏览器的方式访问Hive。

二、hql到mr的原理

Hive执行原理：

流程大致步骤为：
1. 用户提交查询等任务给Driver。
2. 编译器获得该用户的任务Plan。
3. 编译器Compiler根据用户任务去MetaStore中获取需要的Hive的元数据信息。
4. 编译器Compiler得到元数据信息，对任务进行编译，先将HiveQL转换为抽象语法树，然后将抽象语法树转换成查询块，将查询块转化为逻辑的查询计划，重写逻辑查询计划，将逻辑计划转化为物理的计划（MapReduce）, 最后选择最佳的策略。
5. 将最终的计划提交给Driver。
6. Driver将计划Plan转交给ExecutionEngine去执行，获取元数据信息，提交给JobTracker或者SourceManager执行该任务，任务会直接读取HDFS中文件进行相应的操作。
7. 获取执行的结果。
8. 取得并返回执行结果。

Hive 编译过程：
	1. 将多Multiple join 合并为一个Muti-way join
	 2. 对join、group-by和自定义MR重新划分
	 3. 消减不必要的列
	 4. 在表的扫描操作中使用断言
	 5. 消减不必要的分区
	 6. 抽样查询中，消减不必要的桶
	 7. 优化器还增加了局部聚合操作用于处理大分组聚合和增加再分区操作用于处理不对称的分组聚合

hive中的bucket
• hive中table可以拆分成partition，table和partition可以通过‘CLUSTERED BY’进一步分bucket，bucket中的数据可以通过‘SORT BY’排序。
• create table bucket_user (id int,name string)clustered by (id) into 4 buckets;
• ‘set hive.enforce.bucketing = true’ 可以自动控制上一轮reduce的数量从而适配bucket的个数，当然，用户也可以自主设置mapred.reduce.tasks去适配bucket个数
• Bucket主要作用：
– 数据sampling
– 提升某些查询操作效率，例如mapside join
• 查看sampling数据：
– hive> select * from student tablesample(bucket 1 out of 2 on id);
– tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)
– y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。
分桶表的作用：最大的作用是用来提高join操作的效率

三、各种join原理

join执行原理：

我理解： map阶段正常，shuffle阶段，将共同的key给对应的reduce

INSERT OVERWRITE TABLE pv_users 
SELECT pv.pageid, u.age 
FROM page_view pv 
JOIN user u
ON (pv.userid = u.userid);

group原理

我理解：map阶段正常，reduce阶段，根据map阶段spill的文件，根据key进行reduce

SELECT pageid, age, count(1)
FROM pv_users
GROUP BY pageid, age;

map join：

先将小表读到内存中，mapreduce时，直接读内存关联
– /*+ MAPJOIN(tablelist) */，必须是小表，不要超过1G，或者50万条记录

四、常用函数及语句

多插入模式

Multiple inserts:

FROM from_statement

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1,partcol2=val2 …)] select_statement1

[INSERT OVERWRITE TABLE tablename2 [PARTITION …]select_statement2] …

导出表数据

语法结构

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT … FROM …

multiple inserts:

FROM from_statement

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]…

select特殊点

基本的Select操作

语法结构

SELECT [ALL | DISTINCT] select_expr, select_expr, …

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[CLUSTER BY col_list

|[DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

]

[LIMIT number]

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

2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。

3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。

4、（Cluster by字段) 除了具有Distribute by的功能外，还会对该字段进行排序。

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

join特殊点

join 时，每次 map/reduce 任务的逻辑

reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。例如：

SELECT a.val, b.val, c.val FROM a
JOIN b ON(a.key = b.key1)
JOIN c ON (c.key = b.key1)
所有表都使用同一个 join key（使用 1 次 map/reduce 任务计算）。Reduce 端会缓存 a 表和 b 表的记录，然后每次取得一个 c 表的记录就计算一次 join 结果，类似的还有：
SELECT a.val,b.val, c.val FROM a
JOIN b ON(a.key = b.key1)
JOIN c ON (c.key = b.key2)
这里用了 2 次map/reduce 任务。第一次缓存 a 表，用 b 表序列化；第二次缓存第一次 map/reduce 任务的结果，然后用 c 表序列化。

LEFT，RIGHT 和 FULLOUTER 关键字用于处理 join 中空记录的情况

例如：
SELECT a.val,b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
对应所有 a 表中的记录都有一条记录输出。输出的结果应该是 a.val, b.val，当 a.key=b.key 时，而当 b.key 中找不到等值的 a.key 记录时也会输出:
a.val, NULL
所以 a 表中的所有记录都被保留了；
“a RIGHT OUTER JOIN b”会保留所有 b 表的记录。

Join 发生在 WHERE 子句之前。如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在join 子句中写。这里面一个容易混淆的问题是表分区的情况：
SELECT a.val,b.val FROM a
LEFT OUTER JOINb ON (a.key=b.key)
WHEREa.ds=‘2009-07-07’ AND b.ds=‘2009-07-07’
会 join a 表到 b 表（OUTER JOIN），列出 a.val 和 b.val 的记录。WHERE 从句中可以使用其他列作为过滤条件。但是，如前所述，如果 b 表中找不到对应 a 表的记录，b 表的所有列都会列出 NULL，包括 ds 列。也就是说，join 会过滤 b 表中不能找到匹配a 表 join key 的所有记录。这样的话，LEFTOUTER 就使得查询结果与 WHERE 子句无关了。解决的办法是在 OUTER JOIN 时使用以下语法：
SELECT a.val,b.val FROM a LEFT OUTER JOIN b
ON (a.key=b.keyAND
b.ds=‘2009-07-07’ AND
a.ds=‘2009-07-07’)
这一查询的结果是预先在 join 阶段过滤过的，所以不会存在上述问题。这一逻辑也可以应用于 RIGHT 和 FULL 类型的join 中。

Join 是不能交换位置的。无论是 LEFT 还是RIGHT join，都是左连接的。
SELECT a.val1,a.val2, b.val, c.val FROM a
JOIN b ON(a.key = b.key)
LEFT OUTER JOINc ON (a.key = c.key)
先 join a 表到 b 表，丢弃掉所有 join key 中不匹配的记录，然后用这一中间结果和 c 表做 join。这一表述有一个不太明显的问题，就是当一个 key 在 a 表和 c 表都存在，但是 b 表中不存在的时候：整个记录在第一次 join，即 a JOIN b 的时候都被丢掉了（包括a.val1，a.val2和a.key），然后我们再和c 表 join 的时候，如果 c.key 与 a.key 或 b.key 相等，就会得到这样的结果：NULL, NULL, NULL, c.val

LEFT SEMI JOIN是IN/EXISTS的高效实现

udf&transfrom

Hive的 TRANSFORM 关键字提供了在SQL中调用自写脚本的功能

适合实现Hive中没有的功能又不想写UDF的情况

参考：https://blog.youkuaiyun.com/ForgetThatNight/article/details/80036634

五、优化实例

六、参考文档

https://blog.youkuaiyun.com/ForgetThatNight/article/details/79632364