Hive进阶

HiveSQL实现MR WordCount

• 数据

  A
  A B
  A B C
  A B C D
  A B C D E

• MR处理过程

• splite

• map

• shuffle

• reduce

• Hive处理过程

• 1、建表-行读取功能

  create external table wc
  (
  line string
  )
  location '/usr/';

• 插入知识复习-location

  适用的场景（多使用在外部表的场景下）

  当数据已经存在hdfs上，并且不能随意移动这个数据，这个数据也不能被修改想使用这个数据的时候，只能指定location，建表的时候也可以使用location

• 2、建表-统计功能

  create table wc_result
  (
  word string,
  ct int
  )

• 3、MR逻辑

  select splite(line,' ') from wc
  result：
  ["A","B"]
  ["A","B","C"]
  ["A","B","C","D"]

  [explode(ARRAY) 列表中的每个元素生成一行]

  select explode(splite(line,' ')) from wc
  result：
  A
  B
  A
  B
  C

  [word是别名]
  from (select explode(split(line,' ')) word from wc) t
  insert into wc_result
  select word,count(word) group by word;

Hive动态分区

• VS静态分区

  静态分区时，文件中没有分区字段值；是将整个文件视为一个分区，通过load命令中指定该文件所属的分区；

  动态分区时，文件中含有对应的分区字段值，通过from…insert…select…语句，将查询结果写入表中，并实现分区的存放。

• 数据

  1,小明1,12,man,lol-book-movie,beijing:shangxuetang-shanghai-pudong
  2,小明2,13,woman,lol-book-movie,beijing:shangxeutang-shanghai-pudong

• 原始数据表

  create table psn11
  (
  id int,
  name string,
  age int,
  gender string,
  likes array,
  address map<string,string>
  )
  row format delimited
  fields terminated by ','
  collection items terminated by '-'
  map keys terminated by ':';

• 分区表

  create table psn22
  (
  id int,
  name string,
  likes array,
  address map<string,string>
  )
  partitioned by (age int, gender string)
  row format delimited
  fields terminated by ','
  collection items terminated by '-'
  map keys terminated by ':';

• 数据导入

  from psn11
  insert into psn22 partition(age,gender)
  select id,name,likes,age,gender

Hive分桶

• VS分区

  分区是指按照数据表的某列或某些列分为多个区，区从形式上可以理解为文件夹，比如我们要收集某个大型网站的日志数据，一个网站每天的日志数据存在同一张表上，由于每天会生成大量的日志，导致数据表的内容巨大，在查询时进行全表扫描耗费的资源非常多。
  那其实这个情况下，我们可以按照日期对数据表进行分区，不同日期的数据存放在不同的分区，在查询时只要指定分区字段的值就可以直接从该分区查找

  分桶分桶是相对分区进行更细粒度的划分。
  分桶将整个数据内容安装某列属性值得hash值进行区分，如要按照name属性分为3个桶，就是对name属性值的hash值对3取摸，按照取模结果对数据分桶。
  如取模结果为0的数据记录存放到一个文件，取模为1的数据存放到一个文件，取模为2的数据存放到一个文件

• 引入：数据抽样

• 举例-原始数据表

  CREATE TABLE psn31( id INT, name STRING, age INT)
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

• 举例-原始数据

  1,tom,11
  2,cat,22
  3,dog,33
  4,hive,44
  5,hbase,55
  6,mr,66
  7,alice,77
  8,scala,88

• 举例-分桶表

  CREATE TABLE psnbucket( id INT, name STRING, age INT)
  CLUSTERED BY (age) INTO 4 BUCKETS
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

• 举例-加载数据

  insert into table psnbucket select id, name, age from psn31;

• 举例-抽样查询

  select id, name, age from psnbucket tablesample(bucket 2 out of 4 on age);

• 举例-查询结果

  3,dog,33 33%4=1
  7,alice,77 77%4=1

Hive中四种排序

Order By - 对于查询结果做全排序，只允许有一个reduce处理（当数据量较大时，应慎用。严格模式下，必须结合limit来使用）

sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。

Distribute By - 分区排序，经常和Sort By结合使用

Cluster By - 相当于 Sort By + Distribute By（Cluster By不能通过asc、desc的方式指定排序规则；
可通过 distribute by column sort by column asc|desc 的方式）

• sort by && Distribute By

  sort by是局部排序。相比order  by的懒惰糊涂，sort by正好相反，它不但非常勤快，而且具备分身功能。sort by会根据数据量的大小启动一到多个reducer来干活，并且，它会在进入reduce之前为每个reducer都产生一个排序文件。这样的好处是提高了全局排序的效率。

• 举例

• 说明

  以上栗子为在根据年份和气温对气象数据进行排序时，我们希望看到同一年的数据被放到同一个reducer中去处理。因而，这个结果也肯定是全局排序的。特别的，因为distribute  by  通常和sort  by 一起用，所以当distribute by 遇上 sort by时，distribute by要放在前面，这个不难理解，因为要先通过distribute by 将待处理的数据从map端做分发，这样，sort by 这个擅长局部排序的才能去放开的干活。不然要是没有distribute  by的分发，那么sort  by 将要处理全部的数据，即全局排序，这不是sort  by的活，这样做只能拖慢集群工作效率。

Hive优缺点

• hive的优点(1)简单容易上手：提供了类SQL查询语言HQL(2)可扩展：为超大数据集设计了计算/扩展能力（MR作为计算引擎，HDFS作为存储系统）一般情况下不需要重启服务Hive可以自由的扩展集群的规模。(3)提供统一的元数据管理(4)延展性：Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数(5)容错：良好的容错性，节点出现问题SQL仍可完成执行

• hive的缺点（局限性）(1)hive的HQL表达能力有限1)迭代式算法无法表达，比如pagerank2)数据挖掘方面，比如kmeans(2)hive的效率比较低1)hive自动生成的mapreduce作业，通常情况下不够智能化2)hive调优比较困难，粒度较粗3)hive可控性差

Hive窗口函数

• https://zhuanlan.zhihu.com/p/115923678

Hive排名函数

• RANK() 排序相同时会重复，总数不会变

• DENSE_RANK() 排序相同时会重复，总数会减少

• ROW_NUMBER() 会根据顺序计算

• 这三个函数常常和开窗函数结合在一起使用

  RANK() DENSE_RANK() ROW_NUMBER()
  80 1 1 1
  80 1 1 2
  78 3 2 3
  76 4 3 4