HIVE 笔记

最新推荐文章于 2024-10-31 16:17:19 发布

clygm22002

最新推荐文章于 2024-10-31 16:17:19 发布

阅读量109

点赞数

文章标签：大数据

1. 如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务，例如：

SELECT a.val, b.val, c.val FROM a JOIN b

ON (a.key = b.key1) JOIN c

ON (c.key = b.key1)

被转化为单个 map/reduce 任务，因为 join 中只使用了 b.key1 作为 join key。

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1)

JOIN c ON (c.key = b.key2)

而这一 join 被转化为 2 个 map/reduce 任务。因为 b.key1 用于第一次 join 条件，而 b.key2 用于第二次 join。

2. 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 表序列化。

3. Join 发生在 WHERE 子句之前。如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件 —— 或是在 join 子句中写。这里面一个容易混淆的问题是表分区的情况：

SELECT a.val, b.val FROM a

LEFT OUTER JOIN b ON (a.key=b.key)

WHERE a.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 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关了。解决的办法是在 OUTER JOIN 时使用以下语法：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b

ON (a.key=b.key AND

b.ds='2009-07-07' AND

a.ds='2009-07-07')

这一查询的结果是预先在 join 阶段过滤过的，所以不会存在上述问题。这一逻辑也可以应用于 RIGHT 和 FULL 类型的 join 中。

4. LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。Hive 当前没有实现 IN/EXISTS 子查询，所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。
SELECT a.key, a.value

FROM a

WHERE a.key in (SELECT b.key FROM B);

可以被重写为：

SELECT a.key, a.val

FROM a LEFT SEMI JOIN b on (a.key = b.key)

5. SQL标准中，任何对null的操作（数值比较，字符串操作等）结果都为null。Hive对null值处理的逻辑和标准基本一致，除了Join时的特殊逻辑。

这里的特殊逻辑指的是，Hive的Join中，作为Join key的字段比较，null=null是有意义的，且返回值为true。检查以下查询：

select u.uid, count(u.uid)

from t_weblog l join t_user u on (l.uid = u.uid) group by u.uid;

查询中，t_weblog表中uid为空的记录将和t_user表中uid为空的记录做连接，即l.uid = u.uid=null成立。

如果需要与标准一致的语义，我们需要改写查询手动过滤null值的情况：

select u.uid, count(u.uid)

from t_weblog l join t_user u

on (l.uid = u.uid and l.uid is not null and u.uid is not null)

group by u.uid;

实践中，这一语义区别也是经常导致数据倾斜的原因之一。

6. count(distinct ),在数据量大的情况下，效率较低，如果是多count(distinct )效率更低，因为count(distinct)是按group by 字段分组，按distinct字段排序，
一般这种分布方式是很倾斜的，比如男uv,女uv，淘宝一天30亿的pv，如果按性别分组，分配2个reduce,每个reduce处理15亿数据。

7. Order by 能够预期产生完全排序的结果，但是它是通过只用一个reduce来做到这点的。所以对于大规模的数据集它的效率非常低。在很多情况下，并不需要全局排序，此时可以换成Hive的非标准扩展 sort by。Sort by为每个reducer产生一个排序文件。在有些情况下，你需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。Hive的 distribute by 子句可以做这件事

        From record2
         select year , temperature
           distribute by year
s ort by year asc , temperature desc ;

因此，distribute by 经常和 sort by 配合使用。

8. 合并mapreduce操作

Multi-group by是Hive的一个非常好的特性，它使得Hive中利用中间结果变得非常方便。例如：

FROM (SELECT a.status, b.school, b.gender

FROM status_updates a JOIN profiles b
ON (a.userid = b.userid and a.ds='2009-03-20' )

) subq1

INSERT OVERWRITE TABLE gender_summary PARTITION(ds='2009-03-20')

SELECT subq1.gender, COUNT(1) GROUP BY subq1.gender

INSERT OVERWRITE TABLE school_summary PARTITION(ds='2009-03-20')

SELECT subq1.school, COUNT(1) GROUP BY subq1.school

上述查询语句使用了Multi-group by特性连续group by了2次数据，使用不同的group by key。这一特性可以减少一次MapReduce操作。

9. 空值数据倾斜问题：

解决方法1： user_id为空的不参与关联

Select * From log a

Join bmw_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 ：赋与空值分新的key值

Select *

from log a

left outer join bmw_users b

on case when a.user_id is null then concat(‘dp_hive’,rand() ) else a.user_id end = b.user_id;

结论：方法2比方法效率更好，不但io少了，而且作业数也少了。方法1 log读取两次，jobs是2。方法2 job数是1 。这个优化适合无效id(比如-99,’’,null等)产生的倾斜问题。把空值的key变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。附上hadoop通用关联的实现方法（关联通过二次排序实现的，关联的列为parition key,关联的列c1和表的tag组成排序的group key,根据parition key分配reduce。同一reduce内根据group key排序）

10. 不同数据类型关联的数据倾斜
场景：一张表s8的日志，每个商品一条记录，要和商品表关联。但关联却碰到倾斜的问题。s8的日志中有字符串商品id,也有数字的商品id,类型是string的，但商品中的数字id是bigint的。猜测问题的原因是把s8的商品id转成数字id做hash来分配reduce，所以字符串id的s8日志，都到一个reduce上了，解决的方法验证了这个猜测。

解决方法：把数字类型转换成字符串类型

Select * from s8_log a

Left outer join r_auction_auctions b

On a.auction_id = cast(b.auction_id as string);

11. 合并小文件
文件数目过多，会给 HDFS 带来压力，并且会影响处理效率，可以通过合并 Map 和 Reduce 的结果文件来消除这样的影响：

hive.merge.mapfiles = true 是否和并 Map 输出文件，默认为 True

hive.merge.mapredfiles = false 是否合并 Reduce 输出文件，默认为 False

hive.merge.size.per.task = 256*1000*1000 合并文件的大小

1 2 .数据倾斜解决：

原语句：

     from trackinfo a 
   
     left outer join pm_info b 
   
     on (cast(a.ext_field7 as bigint) = b.id) 
    
 如果字段 
    ext_field7为空不需要关联： 
    
       from trackinfo a 
     
       left outer join pm_info b 
     
       on (a.ext_field7 is not null 
     
       and length(a. 
      ext 
      _field7) > 0 
     
       and a.ext_field7 rlike '^[0-9]+$' 
     
       and a.ext_field7 = b.id)

虽然为空的不参与关联但还是会将所有为空的交给一个reduce处理最终（加随机数打散）：

     from trackinfo a 
   
     left outer join pm_info b 
   
     on ( 
   
         case when (a.ext_field7 is not null 
   
             and length(a.ext_field7) > 0 
   
             and a.ext_field7 rlike '^[0-9]+$') 
   
         then 
   
             cast(a.ext_field7 as bigint) 
   
         else 
   
             cast(ceiling(rand() * -65535) as bigint) 
   
         end = b.id 
   
     )