hive简介及使用技巧

1.HIVE简介

​ Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射成一张数据库表，并提供简单的sql查询功能，可以将sql语句转化为mapreduce任务进行运行。hive定义了简单的类sql查询语言，称为HQL，允许熟悉SQL的用户查询数据。同时这个语言也允许熟悉MapReduce的开发者的开发自定义的mapper和reducer来处理内建的mapper和reducer无法处理的复杂的分析工作。

2.HIVE-DDL

• Hive建表(压缩表和非压缩表)

  • 一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录的。
    • 创建表，指定EXTERNAL就是外部表，没有指定就是内部表，内部表在drop的时候会从HDFS上删除数据，而外部表不会删除；
    • 如果不指定数据库，hive会把表创建到default数据库下；

3.HIVE-DML(Data Manipulation Language)数据操作语言

• HQL优化方式及使用技巧

  优化的根本思想在于：

  1. 尽早尽量过滤数据，减少每个阶段的数据量。
  2. 减少job数。
  3. 解决数据倾斜问题。（比如100个机器处理100个任务，但是100个任务都被放到了一台机器上）

• 数据裁剪及job优化

  1. local model:

     select user,item from order_table;
     

     变为下面的代码则不会生成mapreduce程序：

     select * from order_table;
     

  2. 列剪裁：

     2.1hive在读取数据时，可以只读取查询中所需要的列，而忽略其他列：

     select x.a,x.b from
     (select a,b from table1 where e<10) x join
     (select a,b from table2 where e>10) y on x.a=y.a;
     

     此时hive只读取查询逻辑中的真实需要的3列a、b、e,而忽略c,d。这样节省了读取开销，中间表的存储开销和数据整合开销。

     2.2 减少不必要的数据分区

     select count(orderid) from order_table where
     to_date(sale_time)='2014-03-03' and hour(to_date(sale_time))=10;
     

     Explain dependency语法， 可以获取input table 和input partition

     #这里对数据中的分区指定，防止多余的扫描
     select count(orderid) from order_table where
     dt='2014-03-03' and to_date(sale_time)='2014-03-03' and hour(to_date(sale_time))=10;
     

     2.3 利用hive优化机制减少job数量

     不管是外关联outer join 还是内联inner join，如果join的key相同，不论有多少个任务，都会被合并为一个mapreduce任务。

     select a.val,b.val c.val form a join b on (a.key=b.key1) join c on (c.key=b.key1);
     
     #此时数据在这里仅有一个job
     
     select a.val,b.val c.val form a join b on (a.key=b.key1) join c on (c.key=b.key2);
     
     #此时数据在这里有两个job
     

     2.4 job的输入输出优化

     善于使用Muti-insert、union all，不同表的union all相当于multiple inputs,同一个表的union all,相当于map一次输出多条。

     1.insert overwrite table tmp1
       	select... from  a where 条件1；
       insert overwrite table tmp2
       	select... from  a where 条件2；改为
     
     2.from a 
       	insert overwrite table tmp1
       	select...  where 条件1；
       insert overwrite table tmp2
       	select... where 条件2；
     

     这样的写法，仅会扫描数据一次。

     2.5 避免笛卡尔积

     select...
     from all
     left outer join(
     		select *
     		from all_2
     		where(条件1，条件2)
     ) on 条件3
     

     2.6 数据过滤

     在join前必须过滤掉不需要的数据。

     2.7 join操作及优化

     • 小表放前大表放后原则：将条目少的表/子查询放在操作符的左边，因为在reduce阶段，位于join操作符左边的表的内容会被加进内存。可以有效减少oom(即内存溢出)，对于同的key来说，相应的value值小的放前面，值大的放后面。
     • Mapjoin():当小表和大表join时，采用mapjoin,可在map端完成，同时避免发生数据倾斜：

     select /*+MAPJOIN(b)*/a.key,a.value
     from a join b on a.key=b.key
     

     2.8 数据的去重和排序

     尽量避免使用DISTINCT进行排重，特别是大表操作，用Group by代替。

     select distinct key from a#方法1
     
     select key from a group by key
     

     2.9 排序优化

     只有order by 产生的结果是全局有序的，可以根据实际场景选择排序。

     • order by实现全局排序是由一个reduce实现，但是不能并发执行，所以效率偏低；
     • sort by实现部分有序，单个reduce的输出结果是有序的，效率高。

     2.10 数据倾斜

     任务长时间维持在99%左右，查看任务监控页面，发现仅有少量的reduce子任务未完成，因为其处理的reduce与其他的reduce差异量过大。

     2.11 其他小技巧

     • 指定列之间的分割符：fields terminated by ‘\t’

     • group by语法增强，除了可以跟column alias 也可以跟column position

       select f1(col1),f2(col2),f3(col3),count(1) group by 1,2,3;
       

     • Show create table([db_name.]table_name|view_name),可以看到原始表格的创建语句。