Hive整理篇

你使用过哪些 Hive 函数
（1）普通函数

（2）行转列函数和列转行函数
（1）行转列：把多行转成一列（多行变一行）
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串，如果concat中任意字符串为null，则整个函数的返回结果为null。
CONCAT_WS(separator, str1, str2,…)：一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间。
注意：CONCAT_WS must be "string or array即concat_ws中的参数一定是字符串或字符串数组
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段。
collect_list(col)将所有将结果放入，不去重，返回Array类型字段
（2）列转行：把一列转成多行
EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
LATERAL VIEW：形成一张侧写表，它可以将原本的字段做一个关联。常和 split、explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
（3）窗口函数

Hive内部表、外部表、分区表、分桶表的区别，以及各自的使用场景
内部表
如果Hive中没有特别指定，则默认创建的表都是管理表，也称内部表。由Hive负责管理表中的数据，管理表不共享数据。删除管理表时，会删除管理表中的数据和元数据信息。

绝大多数表都是外部表； 只有自己使用的临时表，才是内部表。

外部表
当一份数据需要被共享时，可以创建一个外部表指向这份数据。外部表数据由HDFS管理。删除该表并不会删除掉原始数据，删除的是表的元数据。当表结构或者分区数发生变化时，需要进行一步修复的操作。

场景：

每天将收集到的网站日志定期流入 HDFS 文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过 SELECT+INSERT 进入内部表。

分区表
分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。 Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

分区表使用的是表外字段，需要指定字段类型，并通过关键字partitioned by(partition_name string)声名。

分桶表
分桶使用的是表内字段，已经知道字段类型，不需要再指定。通过关键字 clustered by(column_name) into n buckets声明。分桶是更细粒度的划分、管理数据，可以对表进行先分区再分桶的划分策略
select * from stu_buck tablesample(bucket 1 out of 4 on
id);

Hive的UDF、UDAF、UDTF函数有什么区别
当 Hive 提供的内置函数无法满足你的业务处理需要时， 此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

（1）UDF（User-Defined-Function）

单行进入，单行输出

（2）UDAF（User-Defined Aggregation Function）

聚集函数，多行进入，单行输出

（3）UDTF（User-Defined Table-Generating Functions）

一进多出

Order By、Sort By、Distrbute By、Cluster By的区别
Order By(全局排序)
order by 会对输入做全局排序，因此只有一个reduce，也正因为只有一个 reducer，所以当输入的数据规模较大时，会导致计算的时间较长。（无论设置的reduce数量为多少，都只会有一个Reducer起作用，这样才能保证全局有序）

如果在HADOOP上进行order by全排序，会导致所有的数据集中在一台reducer节点上，然后进行排序，这样很可能会超过单个节点的磁盘和内存存储能力导致任务失败。

Sort By(分区的排序，即每个reducer有序)
Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序。

Distrbute By(控制进入分区)
在有些情况下，我们需要控制某个特定行应该到哪个 reducer ，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。

distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，余数相同的分到一个区。

Cluster By
当 distribute by 和 sorts by字段相同时，可以使用 cluster by 方式代替。cluster by除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是 升序 排序，不能像distribute by 一样去指定排序的规则为 ASC 或者 DESC 。

动态分区和静态分区的区别及使用场景
静态分区：
定义：对于静态分区，从字面就可以理解：表的分区数量和分区值是固定的。静态分区需要手动指定，列是在编译时期通过用户传递来决定的。

应用场景：需要提前知道所有分区。适用于分区定义得早且数量少的用例，不适用于生产。

动态分区：
定义：是基于查询参数的位置去推断分区的名称，只有在 SQL 执行时才能确定，会根据数据自动的创建新的分区。

应用场景：有很多分区，无法提前预估新分区，动态分区是合适的，一般用于生产环境。

请你说明hql所有的优化方式
（1）小表大表 Join（MapJOIN）/相同的key过多/表连接时引发的数据倾斜

将 key 相对分散，并且数据量小的表放在 join 的左边，可以使用 map join 让小的维度表先进内存。在 map 端完成 join。

在Hive 0.11版本之前，如果想在Map阶段完成join操作，必须使用MAPJOIN来标记显示地启动该优化操作，由于其需要将小表加载进内存所以要注意小表的大小。

如将a表放到Map端内存中执行，在Hive 0.11版本之前需要这样写：

select /* +mapjoin(a) / a.id , a.name, b.age
from a join b
on a.id = b.id;
1
2
3
如果想将多个表放到Map端内存中，只需在mapjoin()中写多个表名称即可，用逗号分隔，如将a表和c表放到Map端内存中，则 / +mapjoin(a,c) */ 。

在Hive 0.11版本及之后，Hive默认启动该优化，也就是不在需要显示的使用MAPJOIN标记，其会在必要的时候触发该优化操作将普通JOIN转换成MapJoin，可以通过以下两个属性来设置该优化的触发时机：

hive.auto.convert.join=true 默认值为true，自动开启MAPJOIN优化。

hive.mapjoin.smalltable.filesize=2500000 默认值为2500000(25M)，通过配置该属性来确定使用该优化的表的大小，如果表的大小小于此值就会被加载进内存中。

（2）大表 Join 大表/空值引发的数据倾斜

注意：空值处理时，在非inner join的时候用，在inner join时在自动进行空值过滤。

有时 join 超时是因为某些 key 对应的数据太多，而相同 key 对应的数据都会发送到相同的 reducer 上，从而导致内存不够。此时我们应该仔细分析这些异常的 key。

异常数据时，空KEY过滤

很多情况下，这些 key 对应的数据是异常数据，我们需要在 SQL 语句中进行过滤。例如 key 对应的字段为空，

insert overwrite table jointable select n.* from (select

• from nullidtable where id is not null) n left join bigtable o on n.id =
  o.id;
  1
  2
  3
  非异常数据时，空key转换

有时虽然某个 key 为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join 的结果中，此时我们可以表 a 中 key 为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的 reducer 上。由于null 值关联不上，处理后并不影响最终结果。

set mapreduce.job.reduces = 5;

insert overwrite table jointable
select n.* from nullidtable n full join bigtable o on
nvl(n.id,rand()) = o.id;
1
2
3
4
5
（3）Group By

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

两个参数：

hive.map.aggr=true：在map中会做部分聚集操作，效率更高但需要更多的内存。
hive.groupby.skewindata=true：数据倾斜时负载均衡，当选项设定为true，生成的查询计划会有两个MRJob。第一个MRJob 中，Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的GroupBy Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MRJob再根据预处理的数据结果按照GroupBy Key分布到Reduce中（这个过程可以保证相同的GroupBy Key被分布到同一个Reduce中），最后完成最终的聚合操作。
由上面可以看出起到至关重要的作用的其实是第二个参数的设置，它使计算变成了两个mapreduce，先在第一个中在 shuffle 过程 partition 时随机给 key 打标记，使每个key 随机均匀分布到各个 reduce 上计算，但是这样只能完成部分计算，因为相同key没有分配到相同reduce上，所以需要第二次的mapreduce,这次就回归正常 shuffle,但是数据分布不均匀的问题在第一次mapreduce已经有了很大的改善，因此基本解决数据倾斜。

（4） Count(Distinct) 去重统计

数据量大的情况下，由于 COUNT DISTINCT 操作需要用一个Reduce Task 来完成，这一个 Reduce 需要处理的数据量太大，就会导致整个 Job 很难完成，一般 COUNT DISTINCT 使用先 GROUP BY 再 COUNT 的方式替换,但是需要注意 group by 造成的数据倾斜问题

（5）笛卡尔积

尽量避免笛卡尔积， join 的时候不加 on 条件，或者无效的 on 条件（比如1=1），比如产生大量数据

（6）行列过滤

列处理：在 SELECT 中，只拿需要的列，如果有分区，尽量使用分区过滤，少用SELECT *。

行处理：当join时，尽量使用谓词下推技术。

通过执行计划(explain)查看，下面两个hql的执行计划是一样的，其中，第一个hql用到了系统的谓词下推优化技术，即当join的连接字段和where后面的条件字段都是一个字段时，Hive在执行时会先根据where后面的过滤条件过滤两个表，然后再进行join。但是，当写的sql比较长时，谓词下推可能会失效。

先关联两张表，再用 where 条件过滤

hive (default)> select o.id from bigtable b
join bigtable o on o.id = b.id
where o.id <= 10;
1
2
3
通过子查询后，再关联表（如果join的连接字段和where后面的条件字段不是一个字段时，可以这样做）

select b.id from bigtable b
join (select id from bigtable where id <= 10) o on b.id = o.id;
1
2
（7）分区

（8）分桶

使用过Hive解析JSON串吗
解析一个josn字符串，可以用：

get_json_object(json_string, ‘$.key’) 一次只能解析json中的一个字段
json_tuple(json_string, k1, k2 …) 一次可以解析json中的多个字段
如果要解析一个json数组，可以：

通过regexp_replace、split、explode将json数组转化为多个json字符串
使用子查询的方式，结合json_tuple或get_json_object函数来解析json里面的字段
select json_tuple(json, ‘website’, ‘name’)
from (select explode(split(regexp_replace(regexp_replace(’[{“website”:“baidu.com”,“name”:“百度”},{“website”:“google.com”,“name”:“谷歌”}]’, ‘\[|\]’,’’),’\}\,\{’,’\}\;\{’),’\;’))
as json) t;
执行上述语句，没有报错，执行结果如下：
www.baidu.com 百度
google.com 谷歌

或者用LATERAL VIEW函数
假设我们有一张用户兴趣爱好表 hobbies_table，它有两列数据，第一列是name，第二列是用户兴趣爱好的id_list，是一个数组，存储兴趣爱好的id值：
name id_list
zhangsan [1,2,3]
lisi [3,4,5]
我们要统计所有兴趣id在所有用户中出现的次数：

1. 对兴趣id进行解析：
   SELECT name, hobby_id
   FROM hobbies_table
   LATERAL VIEW explode(id_list) tmp_table AS hobby_id;
   上述sql执行结果：
   name hobby_id
   zhangsan 1
   zhangsan 2
   zhangsan 3
   lisi 3
   lisi 4
   lisi 5
2. 按照hobby_id进行分组聚合即可：
   SELECT hobby_id ,count(name) client_num
   FROM hobbies_table
   LATERAL VIEW explode(id_list) tmp_table AS hobby_id
   GROUP BY hobby_id;
   结果：
   hobby_id client_num
   1 1
   2 1
   3 2
   4 1
   5 1

数据倾斜问题
看场景！

1. 空值引发的数据倾斜
   实际业务中有些大量的null值或者一些无意义的数据参与到计算作业中，表中有大量的null值，如果表之间进行join操作，就会有shuffle产生，这样所有的null值都会被分配到一个reduce中，必然产生数据倾斜。
   解决方案：
   第一种：可以直接不让null值参与join操作，即不让null值有shuffle阶段
   SELECT *
   FROM log a
   JOIN users b
   ON a.user_id IS NOT NULL
   AND a.user_id = b.user_id
   UNION ALL
   SELECT *
   FROM log a
   WHERE a.user_id IS NULL;
2. 不同数据类型引发的数据倾斜
   对于两个表join，表a中需要join的字段key为int，表b中key字段既有string类型也有int类型。当按照key进行两个表的join操作时，默认的Hash操作会按int型的id来进行分配，这样所有的string类型都被分配成同一个id，结果就是所有的string类型的字段进入到一个reduce中，引发数据倾斜。
   解决方案：
   如果key字段既有string类型也有int类型，默认的hash就都会按int类型来分配，那我们直接把int类型都转为string就好了，这样key字段都为string，hash时就按照string类型分配了：
   SELECT *
   FROM users a
   LEFT JOIN logs b ON a.usr_id = CAST(b.user_id AS string);
3. 表连接时引发的数据倾斜
   两表进行普通的repartition join时，如果表连接的键存在倾斜，那么在 Shuffle 阶段必然会引起数据倾斜。
   解决方案：
   通常做法是将倾斜的数据存到分布式缓存中，分发到各个Map任务所在节点。在Map阶段完成join操作，即MapJoin，这避免了 Shuffle，从而避免了数据倾斜。
   由于其需要将小表加载进内存所以要注意小表的大小。
   select /* +mapjoin(a) / a.id , a.name, b.age
   from a join b
   on a.id = b.id;
   如将a表和c表放到Map端内存中，则 / +mapjoin(a,c) */
   Hive 0.11版本及之后不需要，开启配置hive.auto.convert.join=true 默认值为true，自动开启MAPJOIN优化
4. 确实无法减少数据量引发的数据倾斜
   在一些操作中，我们没有办法减少数据量，如在使用 collect_list 函数时：
   select s_age,collect_list(s_score) list_score
   from student
   group by s_age
   collect_list：将分组中的某列转为一个数组返回。
   注：collect_list输出一个数组，中间结果会放到内存中，所以如果collect_list聚合太多数据，会导致内存溢出。
   解决方案：
   这类问题最直接的方式就是调整reduce所执行的内存大小。
   调整reduce的内存大小使用mapreduce.reduce.memory.mb这个配置。