hive加强

Hive介绍与实践

查看执行计划

explain select city_code,count(1) as cnt from user where access='WIFI' group by city_code order by cnt desc limit 5;

MR Job的基本过程

• 一个InputSplit输入到map，会运行我们实现的Mapper的处理逻辑，对数据进行映射操作。
• map输出时，会首先将输出中间结果写入到map自带的buffer中（buffer默认大小为100M，可以通过io.sort.mb配置）。
• map自带的buffer使用容量达到一定限制（默认0.80或80%，可以通过io.sort.spill.percent配置），一个后台线程会准备将buffer中的数据写入到磁盘。
• 这个后台线程在将buffer中数据写入磁盘之前，会首先将buffer中的数据进行partition（分区，partition数为Reducer的个数），对于每个的数据会基于Key进行一个in-memory排序。

• 排序后，会检查是否配置了Combiner，如果配置了则直接作用到已排序的每个partition的数据上，对map输出进行化简压缩（这样写入磁盘的数据量就会减少，降低I/O操作开销）。
  

• 现在可以将经过处理的buffer中的数据写入磁盘，生成一个文件（每次buffer容量达到设置的门限，都会对应着一个写入到磁盘的文件）。
  map任务结束之前，会对输出的多个文件进行合并操作，合并成一个文件（若map输出至少3个文件，在多个文件合并后写入之前，如果配置了Combiner，则会运行来化简压缩输出的数据，文件个数可以通过min.num.splits.for.combine配置；如果指定了压缩map输出，这里会根据配置对数据进行压缩写入磁盘），这个文件仍然保持partition和排序的状态。

• reduce阶段，每个reduce任务开始从多个map上拷贝属于自己partition（map阶段已经做好partition，而且每个reduce任务知道应该拷贝哪个partition；拷贝过程是在不同节点之间，Reducer上拷贝线程基于HTTP来通过网络传输数据）。
• 每个reduce任务拷贝的map任务结果的指定partition，也是先将数据放入到自带的一个buffer中（buffer默认大小为Heap内存的70%，可以通过mapred.job.shuffle.input.buffer.percent配置），如果配置了map结果进行压缩，则这时要先将数据解压缩后放入buffer中。
• reduce自带的buffer使用容量达到一定门限（默认0.66或66%，可以通过mapred.job.shuffle.merge.percent配置），或者buffer中存放的map的输出的数量达到一定门限（默认1000，可以通过mapred.inmem.merge.threshold配置），buffer中的数据将会被写入到磁盘中。
• 在将buffer中多个map输出合并写入磁盘之前，如果设置了Combiner，则会化简压缩合并的map输出。
• 当属于该reducer的map输出全部拷贝完成，则会在reducer上生成多个文件，这时开始执行合并操作，并保持每个map输出数据中Key的有序性，将多个文件合并成一个文件（在reduce端可能存在buffer和磁盘上都有数据的情况，这样在buffer中的数据可以减少一定量的I/O写入操作开销）。
• 最后，执行reduce阶段，运行我们实现的Reducer中化简逻辑，最终将结果直接输出到HDFS中（因为Reducer运行在DataNode上，输出结果的第一个replica直接在存储在本地节点上）。

进阶-分区

Partition 对应于数据库的 Partition 列的密集索引。
在 Hive 中，表中的一个 Partition 对应于表下的一个目录，所有的 Partition 的数据都存储在对应的目录中。用户在加载数据的时候必须显示的指定该部分数据放到哪个分区。

使用分区

分区（partition）：hive里分区的概念是根据“分区列”的值对表的数据进行粗略划分的机制，在hive存储上就体现在表的主目录（hive的表实际显示就是一个文件夹）下的一个子目录，这个文件夹的名字就是我们定义的分区的名字，没有实际操作经验的人可能会认为分区列是表的某个字段，其实不是这样，分区列不是表里的某个字段，而是独立的列，我们根据这个列存储表的里的数据文件。使用分区是为了加快数据分区的查询速度而设计的，我们在查询某个具体分区列里的数据时候没必要进行全表扫描。
静态分区：静态分区在加载数据和使用时都需要在sql语句中指定
案例：(p_date=’20120625’)
动态分区：使用动态分区需要设置hive.exec.dynamic.partition参数值为true，默认值为false，在默认情况下，hive会假设主分区时静态分区，副分区使用动态分区；如果想都使用动态分区，需要设置set hive.exec.dynamic.partition.mode=nostrick，默认为strick

静态分区使用

静态分区：静态分区在加载数据和使用时都需要在sql语句中指定
1.建表

CREATE TABLE `user_daily` (    `uid` int,    `city_code` int,    `model` string,    `access` string ) partitioned by (p_date string);

2.插入数据

INSERT OVERWRITE TABLE user_daily PARTITION (p_date='2017-09-13') SELECT * FROM user;

动态分区使用

1.设置参数

set hive.exec.dynamic.partition.mode=nonstrict;

2.插入数据

INSERT OVERWRITE TABLE user_daily PARTITION (p_date) SELECT *,'2017-09-02' FROM userUNION ALLSELECT *,'2017-09-03' FROM user;

基于Partition的操作

查看分区情况

show partitions user_daily;

一般 SELECT 查询会扫描整个表（除非是为了抽样查询）。但是如果一个表使用 PARTITIONED BY 子句建表，查询就可以利用分区剪枝（input pruning）的特性，只扫描一个表中它关心的那一部分。Hive 当前的实现是，只有分区断言出现在离 FROM 子句最近的那个WHERE 子句中，才会启用分区剪枝。例如，以下语句只会读取分区为‘2017-09-03’和‘2017-09-04’的数据:

select count(1)     from user_daily     where p_date>='2017-09-03' and p_date<='2017-09-04';

修改分区

ALTER TABLE user_daily PARTITION (p_date='2017-09-03')     RENAME TO PARTITION (p_date='20170903');

删除分区

alter table user_daily drop partition(p_date='2017-09-04');

进阶：条件判断、汇总

• IF判断：统计WIFI用户占比

select sum(if(access='WIFI',1,0))/count(1) from user;

• case when:统计uid尾数0-3、4-7、8-9用户数

select   case     when uid % 10 in (0, 1, 2, 3) then '0-3'        when uid % 10 in (4, 5, 6, 7) then '4-7'    else '8-9'   end as interval,   count(*) as cntfrom user group by   case     when uid % 10 in (0, 1, 2, 3) then '0-3'        when uid % 10 in (4, 5, 6, 7) then '4-7'    else '8-9'   end;

• 集合collect_list(不去重)、collect_set去重)

 select collect_list(access) from user;  select collect_set(access) from user;

• map：key/value对
  统计user表city_code最多的4个城市的access分布情况，存储为map

select map(city_code,access) from user;

进阶：hive 各种 join

• 左连接 left outer join
  以左边表为准，逐条去右边表找相同字段，如果有多条会依次列出

• 右连接 left outer join
  以左边表为准，逐条去右边表找相同字段，如果有多条会依次列出

• 内连接inner join
  找出左右相同同的记录

• 全连接 full outer join
  包括两个表的join结果，左边在右边中没找到的结果（NULL），

进阶：窗口函数

1、分组topN：每个access下最大city_code的记录

select access,city_code, uidfrom(  select uid, access, city_code,  row_number() over (partition by access order by city_code desc) as row_num  from user) awhere row_num = 1;

2、分组内从起始点到当前累和

select p_date,   sum(cnt) over(order by p_date asc rows between unbounded preceding and current row)from(  select p_date, count(*) as cnt  from user_daily     where p_date between '2017-09-01' and '2017-09-30'  group by p_date)a;