hive精通必会掌握技能以及实战

Hive中Join电影店铺系统案例和性能优化、Index和Bucket案例实战*/
DROP TABLE users;
DROP TABLE ratings;
DROP TABLE movies;
CREATE TABLE users(UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String) PARTITIONED BY (dt String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH '/root/Documents/data/moviesData/users.dat' INTO TABLE users PARTITION(dt='20161101');
CREATE TABLE ratings(UserID BigInt, MovieID BigInt, Rating Double, Timestamped String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH '/root/Documents/data/moviesData/ratings.dat' INTO TABLE ratings;
CREATE TABLE movies(MovieID BigInt, Title String, Genres String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH '/root/Documents/data/moviesData/movies.dat' INTO TABLE movies;


SELECT  users.UserID, users.Age, users.Gender FROM ratings JOIN users ON (ratings.UserID = users.UserID) WHERE ratings.MovieID = 2116 ;
SELECT  users.UserID, users.Age, users.Gender, movies.Title, movies.Genres FROM ratings JOIN users ON (ratings.UserID = users.UserID)  JOIN movies ON (ratings.MovieID = movies.MovieID)WHERE ratings.MovieID = 2116 ;


SELECT  /*+MAPJOIN(movies)*/ users.UserID, users.Age, users.Gender, movies.Title, movies.Genres FROM ratings JOIN users ON (ratings.UserID = users.UserID)  JOIN movies ON (ratings.MovieID = movies.MovieID)WHERE ratings.MovieID = 2116 ;


Hive中的Array、Map、Struct及自定义数据类型案例实战*/
CREATE TABLE  employee_array (userid INT, name STRING, address STRING, salaries array<INT>, gender String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  COLLECTION ITEMS TERMINATED BY '|'  LINES TERMINATED BY '\n' stored as textfile;
load data local inpath '/root/Documents/data/sql/employee.txt' into table employee_array;
select * from employee_array;
SELECT name, salaries[2] from employee_array;
SELECT userid, size(salaries) as length from employee_array;
SELECT * FROM employee_array WHERE array_contains(salaries, 12000);


0 Hadoop America 1st=5000|2nd=8000|3rd=12000 male
1 Spark America 1st=8000|2nd=10000|3rd=15000 famale
2 Flink America 1st=7000|2nd=8000|3rd=13000 male
3 Hadoop America 1st=9000|2nd=11000|3rd=12000 famale
4 Spark America 1st=10000|2nd=11000|3rd=12000 male
5 Flink America 1st=11000|2nd=12000|3rd=18000 famale
6 Hadoop America 1st=15000|2nd=16000|3rd=19000 male
7 Spark America 1st=18000|2nd=19000|3rd=20000 male
8 Flink America 1st=15000|2nd=16000|3rd=19000 male


CREATE TABLE  employee_map (userid INT, name STRING, address STRING, salaries Map<String,BIGINT>, gender String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  COLLECTION ITEMS TERMINATED BY '|' MAP KEYS TERMINATED BY '=' LINES TERMINATED BY '\n' stored as textfile;
load data local inpath '/root/Documents/data/sql/employee.txt' into table employee_map;
SELECT * FROM employee_map;
SELECT userid,salaries['3rd'] from  employee_map;


0 Hadoop America 5000|8000|12000|level8 male
1 Spark America 8000|10000|15000|level9 famale
2 Flink America 7000|8000|13000|level10 male
3 Hadoop America 9000|11000|12000|level10 famale
4 Spark America 10000|11000|12000|level12 male
5 Flink America 11000|12000|18000|level18 famale
6 Hadoop America 15000|16000|19000|level16 male
7 Spark America 18000|19000|20000|level20 male
8 Flink America 15000|16000|19000|level19 male


CREATE TABLE  employee_struct (userid INT, name STRING, address STRING, salariesandlevel struct<s1:BIGINT,s2:BIGINT,s3:BIGINT,level:String>, gender String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  COLLECTION ITEMS TERMINATED BY '|'  LINES TERMINATED BY '\n' stored as textfile;
load data local inpath '/root/Documents/data/sql/employee.txt' into table employee_struct;
SELECT * FROM employee_struct;
SELECT userid,salariesandlevel.level from employee_struct;






0^^Hadoop^^America^^5000|8000|12000|level8^^male
1^^Spark^^America^^8000|10000|15000|level9^^famale
2^^Flink^^America^^7000|8000|13000|level10^^male
3^^Hadoop^^America^^9000|11000|12000|level10^^famale
4^^Spark^^America^^10000|11000|12000|level12^^male
5^^Flink^^America^^11000|12000|18000|level18^^famale
6^^Hadoop^^America^^15000|16000|19000|level16^^male
7^^Spark^^America^^18000|19000|20000|level20^^male
8^^Flink^^America^^15000|16000|19000|level19^^male



Hive中的内置函数、UDF、UDAF实战*/
package com.dt.spark.hive;


import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;


public final class HiveUDF extends UDF {
  /**
   * 在这个方法中实现任意符合业务处理需求而Hive自带函数又没有提供的功能;
   * @param s
   * @return
   */
  public Text evaluate(final Text s) {
    if (s == null) { return null; }
    return new Text(s.toString().toLowerCase());
  }
}


add jar /root/Documents/data/sql/HadoopApps.jar;
CREATE TEMPORARY FUNCTION tolower AS 'com.dt.spark.hive.HiveUDF';
SELECT tolower(name) FROM employeesforhaving;








package com.dt.spark.hive;


import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;


/**
 * 
 * @author root
 *
 */
public class HiveUDAF extends UDAF {
public static class ConcatUDAFEvaluator implements UDAFEvaluator {
public static class PartialResult {
String result;
String delimiter;
}


private PartialResult partial;


/**
 * 用于初始化工作,例如设计全局变量的初始状态;
 */
public void init() {
partial = null;
}

/**
 * 是数据迭代处理的核心
 * @param value
 * @param deli
 * @return
 */
public boolean iterate(String value, String deli) {


if (value == null) {
return true;
}
if (partial == null) {
partial = new PartialResult();
partial.result = new String("");
if (deli == null || deli.equals("")) {
partial.delimiter = new String(",");
} else {
partial.delimiter = new String(deli);
}


}
if (partial.result.length() > 0) {
partial.result = partial.result.concat(partial.delimiter);
}


partial.result = partial.result.concat(value);


return true;
}


/**
 * 作为分布式计算有很多Mapper会把自己的计算结构传给Reducer,如果说在Mapper中可以继续计算,就会调用terminatePartial来更新当前对象的状态;
 * @return
 */
public PartialResult terminatePartial() {
return partial;
}

/**
 * 在Reducer端负责处理Mapper端传过来的数据,其传入的参数的类型是terminatePartial的返回值的类型;
 * @param other
 * @return
 */
public boolean merge(PartialResult other) {
if (other == null) {
return true;
}
if (partial == null) {
partial = new PartialResult();
partial.result = new String(other.result);
partial.delimiter = new String(other.delimiter);
} else {
if (partial.result.length() > 0) {
partial.result = partial.result.concat(partial.delimiter);
}
partial.result = partial.result.concat(other.result);
}
return true;
}


/**
 * Hive的UDAF最终返回的聚合结果;
 * @return
 */
public String terminate() {
return new String(partial.result);
}
}


// @Override
// public StructType bufferSchema() {
// // TODO Auto-generated method stub
// return null;
// }
//
// @Override
// public DataType dataType() {
// // TODO Auto-generated method stub
// return null;
// }
//
// @Override
// public boolean deterministic() {
// // TODO Auto-generated method stub
// return false;
// }
//
// @Override
// public Object evaluate(Row arg0) {
// // TODO Auto-generated method stub
// return null;
// }
//
// @Override
// public void initialize(MutableAggregationBuffer arg0) {
// // TODO Auto-generated method stub
//
// }
//
// @Override
// public StructType inputSchema() {
// // TODO Auto-generated method stub
// return null;
// }
//
// @Override
// public void merge(MutableAggregationBuffer arg0, Row arg1) {
// // TODO Auto-generated method stub
//
// }
//
// @Override
// public void update(MutableAggregationBuffer arg0, Row arg1) {
// // TODO Auto-generated method stub
//
// }
}


CREATE TEMPORARY FUNCTION connection AS 'com.dt.spark.hive.HiveUDAF';
SELECT connection(name, "****") FROM employeesforhaving;




Hive性能调优之Mapper和Reducer设置、队列设置和并行执行、JVM重用和动态分区、Join调优等*/
EXPLAIN  select * from users join ratings on users.userid = ratings.userid;
EXPLAIN EXTENDED select * from users join ratings on users.userid = ratings.userid;


在现在的Hive中底层运行Mapper和Reducer的时候,Mapper的个数一般是有Splits来确定的,而Reducer的个数一般是1;
set mapred.reduce.tasks=3;
select * from users join ratings on users.userid = ratings.userid;
set mapred.queue.name = hive;


Hive在默认情况下只会在同一个时间执行一个Stage,但是我们可以通过设置hive.exec.parallel为true来让可以并行执行的Stage并行化,从而更好的利用集群的计算资源和加快处理速度;


JVM的重用在Hive性能优化中非常重要, mapred.job.reuse.jvm.num.tasks = 10, 也就是说在这个JVM中可以运行10个任务,例如运行10个Mapper; 如果小文件比较多的话,会对性能有1倍左右的提高;


set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
动态分区使用插入表的数据的时候,作为分区的字段也是需要插入的;


Hive的join操作一般问题都是数据倾斜,尤其是在小表(该小表又比较大)和大表关联的情况下,如果小表比较大,此时你当然可以通过设置参数,对小表进行Broadcast达到Map端操作的目的,但是问题来了:此时容易产生OOM,此外,当小表比较大的时候,网络传输小表的时候也是一个非常大的开销,例如说该小表是1000w条记录,而大表是10亿条记录


基于Hive对电商数据案例分析 */


tbStock.txt:订单的基本信息,包括订单号 交易位置 交易日期等,数据如下所示:
BYSL00000893,ZHAO,2007-8-23
BYSL00000897,ZHAO,2007-8-24
BYSL00000898,ZHAO,2007-8-25


tbStockDetail.txt
订单号，行号，订单产品，有效性，数量，金额
BYSL00000893,0,FS527258160501,-1,268,-268
BYSL00000893,1,FS527258169701,1,268,268


tbDate.txt
日期，年月，年，月，日，  周三，第一周，第一季度，上旬，上半月
2003-1-1,200301,2003,1,1,3,1,1,1,1
2003-1-2,200301,2003,1,2,4,1,1,1,1
2003-1-3,200301,2003,1,3,5,1,1,1,1
2003-1-4,200301,2003,1,4,6,1,1,1,1




CREATE TABLE tbDate(dataID string,theyearmonth string,theyear string,themonth string,thedate string,theweek string,theweeks string,thequot string,  thetenday string ,thehalfmonth string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';


CREATE TABLE tbStock(ordernumber STRING,locatitionid string,dataID string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';


CREATE TABLE tbStockDetail(ordernumber STRING,rownum int,itemid string,qty int,price int ,amout int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';


load data local inpath '/root/Documents/data/sql/sales/tbDate.txt' into table tbDate;
load data local inpath '/root/Documents/data/sql/sales/tbStock.txt'into table tbStock;
load data local inpath '/root/Documents/data/sql/sales/tbStockDetail.txt'into table tbStockDetail;


/**销售金额大于50000*/
SELECT ordernumber, sum(t2.amout) as totalfees FROM tbStock AS t1, tbStockDetail AS t2 WHERE t1.ordernumber = t2.ordernumber GROUP BY t1.ordernumber  HAVING totalfees > 50000;
HMJSL00009024 119058
HMJSL00009957 52422
HMJSL00009958 159126
HMJSL00010598 55828


/**每年每季度销售排名前十名的商品*/
select c.theyear,c.thequot,sum(b.amout) as sumofamount from tbStock a,tbStockDetail b,tbDatE c where a.ordernumber=b.ordernumber and a.dataid=c.dataid group by c.theyear,c.thequot order by sumofamount  desc limit 10;


2008 1 5252819
2007 4 4613093
2007 1 4446088
2006 1 3916638
2008 2 3886470
2007 3 3870558
2007 2 3782235
2006 4 3691314
2005 1 3592007
2005 3 3304243




/**所有的订单系统每年最畅销的产品*/
select distinct e.theyear,e.itemid,f.maxofmount from (select c.theyear,b.itemid,sum(b.amout) as sumofmount from  tbStock a,tbStockDetail b,tbDate c  where a.ordernumber=b.ordernumber and a.dataid=c.dataid group by c.theyear,b.itemid ) e,(select d.theyear, max(d.sumofamount) as maxofmount from (select c.theyear,b.itemid,sum(b.amout) as sumofamount from  tbStock a,tbStockDetail b,tbDate c  where a.ordernumber=b.ordernumber and a.dataid=c.dataid group by c.theyear,b.itemid ) d group by d.theyear ) f where e.theyear=f.theyear and e.sumofmount=f.maxofmount  order by e.theyear;
2004 JY424420810101 53374
2005 24124118880102 56569
2006 JY425468460101 113684
2007 JY425468460101 70226
2008 E2628204040101 97981
2009 YL327439080102 30029
2010 SQ429425090101 4494


/**每年所有订单中最大金额订单的全部销售额*/




SELECT t3.theyear, max(tmp.total) FROM tbDate as t3, (SELECT t1.dataID,t1.ordernumber,sum(t2.amout) as total FROM tbStock t1,tbStockDetail t2 WHERE t1.ordernumber = t2.ordernumber GROUP BY t1.dataID,t1.ordernumber) tmp WHERE t3.dataID = tmp.dataID GROUP BY t3.theyear;
2004 23612
2005 38180
2006 36124
2007 159126
2008 55828
2009 25810
2010 13063


Hive性能调优的本质和源泉彻底解密 

1,历史原因和思维定势:大家学习SQL的时候一般都是就单机DB,这个时候你的性能优化技巧确实主要是SQL语法和参数调优;
2,Hive的核心的性能问题往往是产生在超过规模数据集,例如说100亿条级别的数据集,以及每天处理上千上万个Hive作业的情况下产生的;


Hive性能调优部分要彻底解决的内容;


要从根本上解决和显著的解决实际企业中Hive真正的性能优化问题,必须考虑到底什么是Hive性能的限制,我们按照优先级来说:
第一重要的是:战略性架构
 解决海量数据下大量Job过于频繁的IO问题,而这个问题实质上涉及了架构方面的分表 数据复用 以及分区表等调优的方式;
补充:1,海量的数据中有些数据是高频使用的数据,而有些是很少使用的,如果能够分离成为不同的表,会极大的提升效率;很多的作业可能会有共同点,抽离出来先进行计算并保留计算结果,后面的作业都可以复用;同时,底层的基础功能
也可以先计算,在上层应用的时候直接拿数据结果,而不是每次都重复计算;
2,合理从用静态分区表和动态分区表,可以避免数据全局扫描及计算资源更合理的利用;


3,数据倾斜的一站式解决方案;
第二重要的是:引擎和物理层面,很多内容都是普通Hive使用这不知道的!
从Hive语法和Job内部的角度去进行优化,这要求MapReduce以及Hive如何被翻译成为MapReduce要非常精通;


第三重要的是:一些关键的参数;






归根到底,Hive的性能优化主要考虑的是如何最大化和最有效的使用CPU Memory IO;



Hive性能调优之企业级Mapper和Reducer调优深度细节解密*/


Hive背后的Mapper调优:
1,Mapper数过大,会产生大量小文件,由于Mapper是基于虚拟机的,过多的Mapper创建和初始化及关闭虚拟机都会消耗大量的硬件资源;
Mapper数太小,并发度过小,Job执行时间过长,无法充分利用分布式硬件资源;
2,Mapper数据由什么决定呢?
输入文件数目;
输入文件的大小;
配置参数;
 默认情况下:例如一个文件800M,BLock大小是128M,那么Mapper数目就是7个,6个Mapper处理的数据是 128M, 1个Mapper处理的数据是32M;再例如,一个目录下有三个文件分别大小问5M 10M 150M
 此时会产生4个Mapper,处理的数据分别是5M 10M 128M 22M;
  
 减少Mapper的个数,就要合并小文件,这种小文件有可能是直接来自于数据源的小文件,也可能是Reducer产生的小文件;
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
set hive.merge.mapFiles=true;
set hive.merge.mapredFiles=true;
set hive.merge.size.per.task=256000000
set mapred.max.split.size=256000000
set mapred.min.split.size.per.node=128000000


 增加Mapper的个数,一般是通过控制Hive SQL中上一个Job的Reducer个数来控制的,例如在Join操作的时候会把多个表分解为多个Job;




 set mapred.map.tasks=2;
 set hive.merge.mapFiles=true;
 set hive.merge.mapredFiles=true;
 set hive.merge.size.per.task=256000000


 例如我们有5个300M的文件;按照上面的配置会产生10个Mapper,5个Mapper处理的都是256M的数据,另外5个Mapper处理的都是44M的数据,问题是:大的Mapper会数据倾斜
如何解决,设置set mapred.map.tasks=6,此时根据MapRed的运行机制,会划分6个Mapper,每个Mapper的处理数据的大小是250M, min(1500M/6, 256M) =250M


Hive背后的Reducer调优:
1,Reducer数目过大的话,会产生很多小文件,每个Reducer都会产生一个文件,如果这些小文件是下一个JOB的输入,则会需要对小文件进行合并;同样启动 初始化和销毁Reducer的虚拟机也需要消耗大量的硬件;
  Reducer数据过小的话,Reduce的时间会比较长,也可能会出现数据倾斜;
2,如何控制Reducer的个数呢?
  set hive.exec.reducers.byte.per.reducer=1G
  set hive.exec.reducers.max=999
  Reducer个数=min(999, Reducer的数据输入总量/1G);


  set mapred.reduce.tasks = 10, 默认是1; 如果说当前的Reducer的结果很大,且被接下来多个Job使用其结果,我们该如何设置参数呢?一般都需要调大该参数;


  什么情况下只有一个Reducer?如果不进行Group by但却需要汇总,或者说Order by,当然如果最后Reducer的数据小于默认的1G的话,也会只有一个Reducer;



1,Hive在分布式运行的时候最害怕的是数据倾斜,这是由于分布式系统的特性决定的,因为分布式系统之所以很快是由于作业平均分配给了不同的节点,不同节点同心协力,从而达到更快处理完作业的目的;
顺便说明一下,处理数据倾斜的能力是Hadoop和Spark工程师最核心的竞争力之一;


2,Hive中数据倾斜的原因:
数据在分布式节点上分布不平衡;
join时某些key可能特别大;
groupBy的时候某个Key可能特别多;
count(distinct)有可能出现数据倾斜,因为其内部首先会进行groupBy操作;




3,join,我们希望join时候key是分散,如果一个key的数据量特别大,有可能会出现数据倾斜和OOM,一个核心点是:小表join大表,在reduce阶段左侧的小表会加载进内存,减少OOM的风险;
4,大表join大表的情况:数据倾斜,例如null值,解决办法一般是要打散null值,例如说使用随机数等,如果数据倾斜比较严重,采用这种方式可以提升至少一倍的速度;
5,mapJoin:小表join(超)大表的时候,可以采用mapJoin的方式把小表全部加载到Mapper端的内存中/*+MAPJOIN(table_name)*/;
6,小表join(超)大表的时候,是否会自动进行mapJoin,想进行mapJoin,需要设置:set hive.auto.convert.join=true,Hive在进行join的时候会判断左表的大小来决定是否进行mapJoin:
set hive.mapjoin.smalltable.filesize=128000000;
set hive.mapjoin.cache.numrows=100000;
上述参数可以根据实际的硬件机器的内存进行调整,对性能有至关重要的影响,因为没有了Shuffle;
  对于mapJoin我们能够使用Mapper端JVM中多大的内存呢?
set hive.mapjoin.followby.gby.localtask.max.momery.usage = 0.8
set hive.mapjoin.localtask.max.memory.uage=0.9
7,groupBy,我们可以设置在Mapper端进行部分聚合,最后在Reducer端进行全局聚合
set hive.map.aggr=true;
set hive.groupby.mapaggr.checkinterval=100000


set hive.groupby.skewindata = true 内部会产生两个Job,第一个Job会通过自己的算法打散倾斜的Key并进行聚合操作且保留结果,第二个Job会完成全部的groupBy操作,会产生Mapper-Reducer-Reducer的结构


8, count(distinct),如果某个字段特别多,容易产生数据倾斜,解决思路:
在查询语句中例如对null进行过滤,在结果中加1




9, 笛卡尔积:join时候没有on条件,或者on条件无效,这个时候会使用Reducer进行笛卡尔积的操作;