Hive----编号函数 自定义函数 集合类型 表的优化 数据倾斜

一.编号函数

1. row_number()  --仅仅定义组内的数据进行编号   1 2 3 4

孙悟空	语文	87
孙悟空	数学	95
娜娜	英语	84
宋宋	语文	64
孙悟空	英语	68
宋宋	英语	84
婷婷	语文	65
娜娜	语文	94
宋宋	数学	86
婷婷	数学	85
娜娜	数学	56
婷婷	英语	78

select
*,
row_number() over(partition by subject order by score desc) --分组倒序 row_number:编号
from
tb_sub

+--------------+-----------------+---------------+----------------------+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | row_number_window_0  |
+--------------+-----------------+---------------+----------------------+
| 孙悟空          | 数学              | 95.0          | 1                    |
| 宋宋           | 数学              | 86.0          | 2                    |
| 婷婷           | 数学              | 85.0          | 3                    |
| 娜娜           | 数学              | 56.0          | 4                    |
| 宋宋           | 英语              | 84.0          | 1                    |
| 娜娜           | 英语              | 84.0          | 2                    |
| 婷婷           | 英语              | 78.0          | 3                    |
| 孙悟空          | 英语              | 68.0          | 4                    |
| 娜娜           | 语文              | 94.0          | 1                    |
| 孙悟空          | 语文              | 87.0          | 2                    |
| 婷婷           | 语文              | 65.0          | 3                    |
| 宋宋           | 语文              | 64.0          | 4                    |
+--------------+-----------------+---------------+----------------------+

2. rank()  --编号的时候 排序字段会参与编号相同的排序字段 标号一致, 总标号不变

select
*,
rank() over(partition by subject order by score desc) --分组倒序 rank() 分数相同并列排名
from                                                    --总编号不变
tb_sub

+--------------+-----------------+---------------+----------------+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | rank_window_0  |
+--------------+-----------------+---------------+----------------+
| 孙悟空          | 数学              | 95.0          | 1              |
| 宋宋           | 数学              | 86.0          | 2              |
| 婷婷           | 数学              | 85.0          | 3              |
| 娜娜           | 数学              | 56.0          | 4              |
| 宋宋           | 英语              | 84.0          | 1              |
| 娜娜           | 英语              | 84.0          | 1              |
| 婷婷           | 英语              | 78.0          | 3              |
| 孙悟空          | 英语              | 68.0          | 4              |
| 娜娜           | 语文              | 94.0          | 1              |
| 孙悟空          | 语文              | 87.0          | 2              |
| 婷婷           | 语文              | 65.0          | 3              |
| 宋宋           | 语文              | 64.0          | 4              |
+--------------+-----------------+---------------+----------------+

3. dense_rank()  --编号的时候 排序字段会参与编号相同的排序字段标号一致 , 总标号有可能变小

select
*,
dense_rank() over(partition by subject order by score desc) --分组倒序 
from                       --dense-rank() 成绩相同并列排名,第3人实为第2名,总编号可能会改变
tb_sub

+--------------+-----------------+---------------+----------------------+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | dense_rank_window_0  |
+--------------+-----------------+---------------+----------------------+
| 孙悟空          | 数学              | 95.0          | 1                    |
| 宋宋           | 数学              | 86.0          | 2                    |
| 婷婷           | 数学              | 85.0          | 3                    |
| 娜娜           | 数学              | 56.0          | 4                    |
| 宋宋           | 英语              | 84.0          | 1                    |
| 娜娜           | 英语              | 84.0          | 1                    |
| 婷婷           | 英语              | 78.0          | 2                    |
| 孙悟空          | 英语              | 68.0          | 3                    |
| 娜娜           | 语文              | 94.0          | 1                    |
| 孙悟空          | 语文              | 87.0          | 2                    |
| 婷婷           | 语文              | 65.0          | 3                    |
| 宋宋           | 语文              | 64.0          | 4                    |
+--------------+-----------------+---------------+----------------------+

二. 自定义函数

编程步骤:

1. 创建maven工程

2. 导入依赖

 

<dependencies>

      <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->

      <dependency>

          <groupId>org.apache.hive</groupId>

          <artifactId>hive-exec</artifactId>

          <version>1.2.1</version>

      </dependency>

</dependencies>

 

3. 编写 类 继承 UDF 类

4. 重写方法 evaluate 支持重载

package cn.doit.com;

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

public class Lower extends UDF {
    public String evaluate (final String s){
        if (s == null){
            return null;
        }
        return s.toLowerCase();
    }
}

5. 打包上传到HDFS

hdfs dfs -put udf.jar /

6. 在hive的命令行窗口创建函数

1)add jar /udf.jar
2)reate function sayHello as 'cn.doit.com.Lower' using jar 'hdfs://linux01:8020/udf.jar'

6. 注意: UDF必须要有返回类型，可以返回null，但是返回类型不能为void；

三. 集合数据类型

struct    --类似于java对象 pojo类 
array     --array中的数据为相同类型，例如，假如array A中元素['a','b','c']，则A[1]的值为'b' 
map       --键值对

--数据
--string   array<string>    map<string ,int>   struct<street:string, city:string>
benben,fengjie_furong,xiaoben:18_daben:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

--建表
create  table  tb_user(
name  string ,
friends  array<string> ,
children map<string ,int> ,
 <street:string , city:string>
)
row format  delimited fields terminated by  ',' 
collection items terminated by  '_'   --一个字段项目分隔符
map  keys terminated by  ':'   --k,v分割符
lines terminated by  '\n';     --换行符切割
load  data local  inpath "/doit19/collection/" into  table tb_user ;

数组 
--friends[index]

select friends[0] as a , friends[1] as b  from tb_user;  --取值   as a别名,可不写
+----------+---------+
|    a     |    b    |
+----------+---------+
| fengjie  | furong  |
| caicai   | susu    |
+----------+---------+
--长度 size[arr]
select size(friends) from tb_user;   
select friends[if(2>size(friends) , 0 , 2)] from tb_user;  --防止角标越界 参数二:索引0 参数三:索引2
+------+
| _c0  |
+------+
| 2    |
| 2    |
+------+                    
+----------+
|   _c0    |
+----------+
| fengjie  |
| caicai   |
+----------+

map集合
map(k1,v1,k2,v2);
map_keys(map);
map_values(map);
size(map);

select children['xiaoben'] from tb_user; --根据K获取map的V值
select map('k1','v1','k2','v2');   --创建map集合 kv要成对,偶数
select map_keys(children) from tb_user; --获取集合中所有的key
select map_values(children) from tb_user;  --获取集合中所有的value
select size(children) from tb_user;  --获取map集合的长度
select explode(children) from tb_user;  --炸裂开map集合
+----------------+--------+
|      key       | value  |
+----------------+--------+
| xiaoben        | 18     |
| daben          | 19     |
| xiao yang      | 18     |
| xiaoxiao yang  | 19     |
+----------------+--------+

struct结构体

select address.street , address.city from tb_user;   --结构体获取属性
+----------------+----------+
|     street     |   city   |
+----------------+----------+
| hui long guan  | beijing  |
| chao yang      | beijing  |
+----------------+----------+

四. 功能扩展使用反射调用java类的方法

反射 reflect 函数 

reflect(calss(类名) , methodName(方法名) , args...(参数) )

1.编写java程序

2.打包

3.上传到linux系统

4.add jar /test.jar   将jar包添加到 lib的目录下

5.查询

select reflect( 'cn.doit.demo.Test1' , 'test1' , 'hello' , 23);

6. 企业级表的优化

1) 使用分区表和分桶表

2) 对SQL语句的优化  count(1)比count(*)效率高  select 字段

3) 使用特殊的存储方式   列式存储 格式 ORC parquet(常用)

4) 小表 join 大表 [MR 程序中的map端join]

5) 开启 map 端聚合

6) 避免count(distinct cloumn)

7) 避免笛卡尔积

8) 适当的调整 maptask 的个数和 reducetask的个数

9) 测试数据在本地运行 

set mapreduce.framework.name=local

7.数据倾斜解决方法

1. 合理设置Map数量

2. 小文件进行合并

3. 复杂文件增加Map数量

4. 合理设置reduce数量

5. 避免shuffer