hive上处理array数据 hive array 函数

一、Hive中的特殊数据类型

1、array类型

• 创建数据
  vim /export/datas/array.txt

zhangsan	beijing,shanghai,tianjin
wangwu	shanghai,chengdu,wuhan,haerbin

• 创建表
  create database db_complex;
  use db_complex;

create table if not exists complex_array(
name string,
work_locations array<string>
)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',';

COLLECTION ITEMS TERMINATED BY ','：指定集合中每个元素的分隔符

• 加载数据
  load data local inpath ‘/export/datas/array.txt’ into table complex_array;
• 使用
  –统计每个用户工作过的城市个数
  select name,size(work_locations) as numb from complex_array;
  –取出数组中单独的元素
  select name,work_locations[0],work_locations[1] from complex_array;

2、map类型

• 创建数据
  vim /export/datas/map.txt

1,zhangsan,唱歌:非常喜欢-跳舞:喜欢-游泳:一般般
2,lisi,打游戏:非常喜欢-篮球:不喜欢

• 创建表
  create table if not exists complex_map(
  id int,
  name string,
  hobby map<string,string>
  )
  row format delimited fields terminated by ‘,’
  COLLECTION ITEMS TERMINATED BY ‘-’ MAP KEYS TERMINATED BY ‘:’;

COLLECTION ITEMS TERMINATED BY '-' ：指定Map集合中keyvalue对的分隔符
MAP KEYS TERMINATED BY ':'; ：指定Map集合中key和value之间的分隔符

• 加载数据
  load data local inpath ‘/export/datas/map.txt’ into table complex_map;
• 统计分析
  –统计每个人有几个兴趣爱好
  select name,size(hobby) as numb from complex_map;
  –取出每个人对唱歌的喜好程度
  select name,hobby[“唱歌”] as deep from complex_map;

二、Hive中使用正则加载数据

1、应用场景

• 列的分隔符不固定，一行数据中出现了多个列的分隔符【几乎很少，半结构化数据，跟数据生成格式有关】
  name-age,sex phone address
• 列的分隔符固定，但是字段中包含了列的分割符
  2019-08-28 00:03:00 tom

2、实现案例

• 创建数据
  vim /export/datas/regex.txt

2019-08-28 00:03:00 tom
2019-08-28 10:00:00 frank
2019-08-28 11:00:00 jack
2019-08-29 00:13:23 tom
2019-08-29 10:00:00 frank
2019-08-30 10:00:00 tom
2019-08-30 12:00:00 jack

• 创建表，加载数据

• 普通创建
  create table logindate1(
  longintime string,
  name string
  )
  row format delimited fields terminated by ’ ';

load data local inpath '/export/datas/regex.txt' into table logindate1;

select * from logindate1;

• 问题：数据产生了错位，没有按照预想结果产生
• 原因：数据的某个字段中包含了列的分割符
• 解决：Hive运行自定义加载方式，常见的方式：通过正则加载数据

• 正则加载： http://tool.chinaz.com/regex/
  create table logindate2(
  longintime string,
  name string
  )
  ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.RegexSerDe’
  WITH SERDEPROPERTIES (
  “input.regex” = “([^}]) ([^ ])”
  );

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'：通过正则来匹配每个字段
"input.regex" = "“：指定每个字段的正则表达式


load data local inpath '/export/datas/regex.txt' into table logindate2;

三、Hive中的内置函数：show functions;

1、字符串函数

• desc function extended substr;

• 查看函数具体的用法

• substring/substr：截取字符串
• split：分割字符串
• instr：查找字符串
• lower：转换小写
• upper：转换大写
• reverse：反转
• regexp_replace：正则替换
• concat_ws/concat：字符串拼接
• ……

2、日期函数

• year

• select year(concat_ws(" ",longintime,name)) as daystr from logindate1;

• month
• day

• select day(concat_ws(" ",longintime,name)) as daystr from logindate1;

• hour
• date_add

• 取当前时间后面的时间

• date_sub【比较常用】

• 取当前时间前面的时间
• 每天要处理昨天的数据

• 昨天产生了多少条数据
  select count(*) from tb where day = 昨天的日期是动态的

select date_sub(date(concat_ws(" ",longintime,name)),1) as yesterday from logindate1;

• 日期格式转换函数

• 标准日期格式：用于取年、月、日、小时做分组

• 每个小时
• 每年
• 每月
• 每天

• 时间戳：用于计算时间差
• unix_timestamp：将标准日期格式转换为时间戳

• select unix_timestamp(“2019-08-28 00:03:00”);
• 时间戳类型可以用于计算时间差

• from_unixtime将时间戳，转成标准日期格式

• SELECT from_unixtime(1566921780, ‘yyyy-MM-dd HH:mm:ss’);
• SELECT from_unixtime(1566921780, ‘dd/MMM/yyyy:HH:mm:ss’);

四、Hive中的UDF函数

1、UDF

• User Define Function：用户自定义函数
• 应用场景：

• 当Hive 中的函数无法满足用户的需求，用户可以自己开发代码，添加函数
• 类似于Java中如果没有方法能解决需求，可以自定义一个方法
• 例如：需要做日期的转化

• 24/Dec/2019:15:54:58 数据中的日期格式
• |
• | SimpleDateFormat
• |
• 2019-12-24 15:54:58 转换为标准日期格式

• 分类

• UDF：一对一的函数【工作中用的最多的自定义函数】

• 输入一个，返回一个

• UDAF：多对一的函数：聚合函数

• 输入多个，返回一个
• max 、min、sum

• UDTF：一对多的函数

• 输入一个，返回多个
• explode

• 实现一个UDF

• 开发一个类，继承UDF的类

• 重载一个或者多个evaluate的方法
• 在evaluate方法中实现数据的转换：推荐使用Hadoop的序列化类型来实现
  select transdate(“24/Dec/2019:15:54:58”);

• 将函数打成jar包，添加到Hive的环境变量中

• 临时的将jar包放入环境变量
  –在hive的命令行中执行
  add jar /export/datas/udf.jar;
• 查询资料实现怎么永久的将jar包放入Hive的环境变量？

• 推荐的方案

• 在hive中创建auxlib或者将jar包的地址添加到hive的配置文件中

• 将自己开发的 udf的类注册成为Hive中的一个函数
  –创建一个临时函数,只对当前会话生效，如果关闭了客户端，该函数自动消失，下一次打开，需要重新创建
  CREATE TEMPORARY FUNCTION function_name AS class_name;
  –创建一个永久性的 函数，该函数会永久性存在，这个就要求jar包必须永久存在hive环境变量中
  CREATE FUNCTION [db_name.]function_name AS class_name
  [USING JAR|FILE|ARCHIVE ‘file_uri’ [, JAR|FILE|ARCHIVE ‘file_uri’] ];

||
创建一个临时函数
create temporary function transdate as 'cn.itcast.hive.udf.udf.UserUDF';
||
select  transdate("24/Dec/2019:15:54:58");
||
从Hive环境变量中找到这个类，根据参数来调用该类的evaluate方法

创建永久函数：存在bug，show functions里面很多情况下会不显示，但是用的时候可以直接使用

CREATE FUNCTION [db_name.]function_name AS class_name USING JAR ‘jar_hdfs_path’;

jar_hdfs_path：hdfs上的jar包地址

• 总结：

• 写开发一个类：继承UDF类，才能让Hive 识别
• 在类中药实现至少一个叫做evaluate的方法，在该方法中实现数据处理逻辑
• 将该类的文件打成jar包，添加到Hive的环境变量中
• 将该类注册成为hive中的 一个函数
• 可以调用 该函数

2、UDAF

• 自定义聚合函数：适合于多对一的场景
• 分析统计中的聚合：count、sum、avg、max、min
• 如何实现UDAF

• 开发一个类：继承UDAF
• 实现的方法

• init：实现数据的初始化
• iterate：迭代计算，真正实现聚合的方法
• terminatePartial：局部聚合的方法，分布式计算中会调用的方法，得到局部结果
• merge：实现聚合的 方法本质上要调用iterate
• terminate：返回最终结果的方法

• 求最大值的实现

• 临时的将jar包放入环境变量
  add jar /export/datas/udf.jar;
• 将函数进行注册
  create temporary function usermax as ‘cn.itcast.hive.udf.udaf.UserUDAF’;
• 查找int列的最大值
  select usermax(cast(empno as int)) from tb_emp;

cast:强制类型转换
cast(col as type)

3、UDTF

• 应用场景一对多：一行或者一列变成了多行或者多列

• Hive中自带的udtf函数：explode

• 在hive中实现wordcount

• 创建一张表
  create table words(word string);
• 加载数据
  load data local inpath ‘/export/datas/word.txt’ into table words;
• 实现wordcount
  –将一行多个单词变成一行一个单词
  select split(word," “) as wordarray from words;
  –将数组中的每个元素变成一行
  select explode(split(word,” ")) as wordarray from words;

• 实现需求：将原始数据进行转换

• 原始数据：name=zhangsan&age=18&sex=male

• 一行一列

• 转换结果：三行两列
  name zhangsan
  age 18
  sex male
• 实现自定义

• 继承GenericUDTF类
• 实现方法

• initialize：初始化的方法
• process：真正实现转换的 方法
• close：关闭资源的方法

• 将jar包添加到hive的环境变量中
• 创建一个临时函数
  create temporary function transmap as ‘cn.itcast.hive.udf.udtf.UserUDTF’;
• 测试使用
  select transmap(“name=zhangsan&age=18&sex=male”) as (colkey,colvalue);
• 注意事项

• 自定义udtf只能直接在select语句中使用
• 不可以跟其他字段放在一起用
• 不能嵌套使用
• 不能与group by/cluseter by/sort by /distribute by一起使用

• 能与order by 一起使用

4、侧视图：lateral view

• Hive中的另外一个常用的udtf函数：parse_url_tuple

• 功能：解析url字符串
• url：http://facebook.com/path/p1.php?query=1&name=zhangsan

• HOST：facebook.com
• PATH：/path/p1.php
• QUERY：query=1&name=zhangsan

• 测试
  select parse_url_tuple("http://facebook.com/path/p1.php?query=1&name=zhangsan ",“HOST”,“PATH”,“QUERY”,“QUERY:name”) as (host,path,query,qurey_name);

• 准备数据
  vim /export/datas/lateral.txt

1	http://facebook.com/path/p1.php?query=1
2	http://www.baidu.com/news/index.jsp?uuid=frank
3	http://www.jd.com/index?source=baidu

• 创建表
  create table tb_url(
  id int,
  url string
  ) row format delimited fields terminated by “\t”;

load data local inpath '/export/datas/lateral.txt' into table tb_url;

• 需求：获取每个id对应的域名

• 解析每个id的域名
  select parse_url_tuple(url,“HOST”) as host from tb_url;
• 还要显示对应的id：期望结果有2列

• 第一列是id
• 第二列是id对应的域名

• 实现：理解UDTF：将UDTF的结果变成表【只读的表叫视图】

• lateral view：侧视图

• view：就是视图，是一种只读的表，工作中MySQL等数据库中比较常见

• 功能：只读，为了数据安全
• 举例：

• 表：用户表：用户id、名称、密码、手机号、国家、省份、城市、年龄
• 需求：希望让数据分析师对不同年龄段的用户的国家进行分析

• 18 - 25 : 50%中国，50%其他国家
• ……

• 基于用户表构建视图：create view userview as select 用户id、国家、年龄 from 用户表

• 使用：当做表来使用即可

• 功能：一般与UDTF连用，将UDTF的结果变成一个视图[表]
• 用法：
  select a.,b. from tableA a lateral view udtf(args) b as col1,col2……
  | | |
  原始表 视图的名称 视图的列名
• 实现上面的需求
  select a.id ,b.host from tb_url a lateral view parse_url_tuple(url,“HOST”) b as host;

五、Hive中的行列转换

1、explode函数

2、行转列

• 多行转多列

• 准备数据
  vim /export/datas/r2c1.txt

a	c	1
a	d	2
a	e	3
b	c	4
b	d	5
b	e	6

• 期望结果
  ±------±—±---±—±-+
  | col1 | c | d | e |
  ±------±—±---±—±-+
  | a | 1 | 2 | 3 |
  | b | 4 | 5 | 6 |
  ±------±—±---±—±-+
• 代码实现

• 创建表
  create table row2col1(
  col1 string,
  col2 string,
  col3 int
  )
  row format delimited fields terminated by ‘\t’;
• 加载数据
  load data local inpath ‘/export/datas/r2c1.txt’ into table row2col1;
• SQL实现
  select
  col1 as col1,
  max(case col2 when ‘c’ then col3 else 0 end) as c,
  max(case col2 when ‘d’ then col3 else 0 end) as d,
  max(case col2 when ‘e’ then col3 else 0 end) as e
  from
  row2col1
  group by
  col1;

• 多行转单列

• 准备数据
  vim /export/datas/r2c2.txt

a	b	1
a	b	2
a	b	3
c	d	4
c	d	5
c	d	6

• 期望结果
  ±------±------±-------±-+
  | col1 | col2 | col3 |
  ±------±------±-------±-+
  | a | b | 1,2,3 |
  | c | d | 4,5,6 |
  ±------±------±-------±-+
• 代码实现

• 创建表
  create table row2col2(
  col1 string,
  col2 string,
  col3 int
  )row format delimited fields terminated by ‘\t’;
• 加载数据
  load data local inpath ‘/export/datas/r2c2.txt’ into table row2col2;
• SQL实现
  select
  col1,
  col2,
  concat_ws(’,’, collect_set(cast(col3 as string))) as col3
  from
  row2col2
  group by
  col1, col2;

3、列转行

• 多列转多行

• 准备数据
  vim /export/datas/c2r1.txt

a       1       2       3
b       4       5       6

• 期望结果
  ±----------±----------±----------±-+
  | col1 | col2 | col3 |
  ±----------±----------±----------±-+
  | a | c | 1 |
  | b | c | 4 |
  | a | d | 2 |
  | b | d | 5 |
  | a | e | 3 |
  | b | e | 6 |
  ±----------±----------±----------±-+
• 代码实现

• 创建表
  create table col2row1(
  col1 string,
  col2 int,
  col3 int,
  col4 int
  )row format delimited fields terminated by ‘\t’;
• 加载数据
  load data local inpath ‘/export/datas/c2r1.txt’ into table col2row1;
• SQL实现
  select col1, ‘c’ as col2, col2 as col3 from col2row1
  UNION ALL
  select col1, ‘d’ as col2, col3 as col3 from col2row1
  UNION ALL
  select col1, ‘e’ as col2, col4 as col3 from col2row1;

• 单列转多行

• 准备数据
  vim /export/datas/c2r2.txt

a	b	1,2,3
c	d	4,5,6

• 期望结果
  ±------±------±------±-+
  | col1 | col2 | col3 |
  ±------±------±------±-+
  | a | b | 1 |
  | a | b | 2 |
  | a | b | 3 |
  | c | d | 4 |
  | c | d | 5 |
  | c | d | 6 |
  ±------±------±------±-+
• 代码实现

• 创建表
  create table col2row2(
  col1 string,
  col2 string,
  col3 string
  )row format delimited fields terminated by ‘\t’;
• 加载数据
  load data local inpath ‘/export/datas/c2r2.txt’ into table col2row2;
• SQL实现
  select
  col1,
  col2,
  lv.col3 as col3
  from
  col2row2
  lateral view
  explode(split(col3, ‘,’)) lv as col3;

三、Hive中的行列转换

1、explode函数

• 类别：UDTF
• 功能：将一个集合类型array或者map进行展开

• explode(arry)：将数组中的每个元素变成一行

explode(map)：每一对keyvalue变成一行，key是一列，value是一列

• SQL转换
  select explode(hobby) from complex_map;
• 结果
• 
  

• 与侧视图连用
  select a.id,b.* from complex_map a lateral view explode(hobby) b as key,value;

2、行转列

• 多行转多列

• 准备数据
  vim /export/datas/r2c1.txt

a	c	1
a	d	2
a	e	3
b	c	4
b	d	5
b	e	6

• 期望结果
  ±------±—±---±—±-+
  | col1 | c | d | e |
  ±------±—±---±—±-+
  | a | 1 | 2 | 3 |
  | b | 4 | 5 | 6 |
  ±------±—±---±—±-+
• 代码实现

• 创建表
  create table row2col1(
  col1 string,
  col2 string,
  col3 int
  )
  row format delimited fields terminated by ‘\t’;
• 加载数据
  load data local inpath ‘/export/datas/r2c1.txt’ into table row2col1;
• SQL实现
  set hive.exec.mode.local.auto=true;//开启本地模式
  select
  col1 as col1,
  max(case col2 when ‘c’ then col3 else 0 end) as c,
  max(case col2 when ‘d’ then col3 else 0 end) as d,
  max(case col2 when ‘e’ then col3 else 0 end) as e
  from
  row2col1
  group by
  col1;

实现的过程：
对a进行分组：
col1	col2	col3
a		c		1
a		d		2
a		e		3
结果：
		c			d			e
a   	1,0,0		0,2,0		0,0,3
max聚合每个判断的结果
		c			d			e
a   	1			2			3

对b进行分组
col1 col2 col3
b c 4
b d 5
b e 6
结果
c d e
b 4,0,0 0,5,0 0,0,6

max聚合每个判断的结果
c d e
b 4 5 6

• case when的用法

• SQL中的判断语句：类似于Java中的switch case
• 语法：

• 第一种用法：

• case col
• when value1 then rs1
• when value2 then rs2
• ……
• else rsN
• end

• 第二种用法：

• case
• when col=value1 then rs1
• when col=value2 thne rs2
• ……
• else rsN
• end

• 演示

• select deptno,case deptno when 10 then 0 when 20 then 2 else 1 end as test from db_emp.tb_dept;
• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sCi6Yjrv-1619418374652)(Day46_20200207_Hive中的函数与优化方案（二）.assets/image-20200207102619765.png)]
• select deptno,case when deptno=10 then 0 when deptno=20 then 2 else 1 end as test from db_emp.tb_dept;

• 多行转单列【重要】

• 准备数据
  vim /export/datas/r2c2.txt

a	b	1
a	b	2
a	b	3
c	d	4
c	d	5
c	d	6

• 期望结果
  ±------±------±-------±-+
  | col1 | col2 | col3 |
  ±------±------±-------±-+
  | a | b | 1,2,3 |
  | c | d | 4,5,6 |
  ±------±------±-------±-+
• 代码实现

• 创建表
  create table row2col2(
  col1 string,
  col2 string,
  col3 int
  )row format delimited fields terminated by ‘\t’;
• 加载数据
  load data local inpath ‘/export/datas/r2c2.txt’ into table row2col2;
• SQL实现
  select
  col1,
  col2,
  concat_ws(’,’, collect_set(cast(col3 as string))) as col3
  from
  row2col2
  group by
  col1, col2;

a	b	1
a	b	2
a	b	3
||
按照前两列分组
a	b  1和2和3变成了字符串
|
a	b ["1","2","3"]
|
a	b	1,2,3

• concat_ws

• concat_ws(’,’, collect_set(cast(col3 as string)))
• 用于实现字符串拼接

• concat_ws(分隔符，array)
• concact_ws(分隔符，str1，str2……)

• concat：也用于实现字符串拼接

• 与concat_ws的区别

• concat不能指定结果中每个元素的分隔符
• concat拼接时，只要有一个元素为null，结果就为null
• concat_ws，只有有一个元素不为null，结果就不为null

• collect_set：将某一列的所有元素放入一个数组中

• collect_set(cast(col3 as string))
• 将cast转换以后的所有数据放入一个集合【array】
• collect_list：允许重复的元素
• collect_set：元素不重复的

• cast：强制类型转换，将原表中第三列的值转为字符串类型

• cast(col3 as string)

3、列转行

• 多列转多行

• 准备数据
  vim /export/datas/c2r1.txt

a       1       2       3
b       4       5       6

• 期望结果
  ±----------±----------±----------±-+
  | col1 | col2 | col3 |
  ±----------±----------±----------±-+
  | a | c | 1 |
  | b | c | 4 |
  | a | d | 2 |
  | b | d | 5 |
  | a | e | 3 |
  | b | e | 6 |
  ±----------±----------±----------±-+
• 代码实现

• 创建表
  create table col2row1(
  col1 string,
  col2 int,
  col3 int,
  col4 int
  )row format delimited fields terminated by ‘\t’;
• 加载数据
  load data local inpath ‘/export/datas/c2r1.txt’ into table col2row1;
• SQL实现
  select col1, ‘c’ as col2, col2 as col3 from col2row1
  UNION ALL
  select col1, ‘d’ as col2, col3 as col3 from col2row1
  UNION ALL
  select col1, ‘e’ as col2, col4 as col3 from col2row1;

||
select col1, ‘c’ as col2, col2 as col3 from col2row1
c1 c2 c3 c4
a 1 2 3
b 4 5 6

|
	
	+-------+-------+-------+--+
	| a     | c     | 1     |
	| b     | c     | 4     |
	+-------+-------+-------+--+
	+-------+-------+-------+--+
	| a     | d     | 2     |
	| b     | d     | 5     |
	+-------+-------+-------+--+
	+-------+-------+-------+--+
	| a     | e     | 3     |
	| b     | e     | 6     |
	+-------+-------+-------+--+
	
	```
	
* UNION：实现SQL语句行的拼接

	* union ：要求字段个数是一致的
	* union all：要求字段个数和类型都是一致的

* JOIN：实现是列的拼接

• 单列转多行【重要】

• 准备数据
  vim /export/datas/c2r2.txt

a	b	1,2,3
c	d	4,5,6

• 期望结果
  ±------±------±------±-+
  | col1 | col2 | col3 |
  ±------±------±------±-+
  | a | b | 1 |
  | a | b | 2 |
  | a | b | 3 |
  | c | d | 4 |
  | c | d | 5 |
  | c | d | 6 |
  ±------±------±------±-+
• 代码实现

• 创建表
  create table col2row2(
  col1 string,
  col2 string,
  col3 string
  )row format delimited fields terminated by ‘\t’;
• 加载数据
  load data local inpath ‘/export/datas/c2r2.txt’ into table col2row2;
• SQL实现
  select
  col1,
  col2,
  lv.col3 as col3
  from
  col2row2
  lateral view
  explode(split(col3, ‘,’)) lv as col3;

• 行列转换中的重点：函数的使用，了解了常用函数的功能

六、Hive中的特殊函数与Python处理

1、reflect

• 功能：可以通过实例化对象调用java类的方法，也可以直接调用Java中静态类的方法
• https://cwiki.apache.org/confluence/display/Hive/ReflectUDF
• 要求：该类的方法必须返回一个基础类型或者hive能识别的序列化类型
• 用法：reflect（classname,method,args）
• 测试：

• select reflect(“java.util.UUID”,“randomUUID”);
• select reflect(“java.lang.Math”,“max”,3,2);

2、json处理【重点】

• 创建数据
  vim /export/datas/hivedata.json

{"id": 1701439105,"ids": [2154137571,3889177061],"total_number": 493}
{"id": 1701439106,"ids": [2154137571,3889177061],"total_number": 494}

• 第一种方式：使用hive中的json函数来对json数据进行处理

• 应用场景：整个数据文件中，只有某个字段是json格式的
• get_json_object：从json数据中获取json的字段
• json_tuple：udtf函数，解析json数据
• 创建表
  create table tb_json_test1(
  json string
  );
  load data local inpath ‘/export/datas/hivedata.json’ into table tb_json_test1;
• get_json_object
  select
  get_json_object(t.json,’$.id$.total_number’) as total_number
  from
  tb_json_test1 t;
• json_tuple
  select
  t2.*
  from
  tb_json_test1 t1
  lateral view
  json_tuple(t1.json,‘id’,‘total_number’) t2 as c1,c2;

• 第二种方式：通过json解析工具类直接在加载数据时就解析json的对象，类似于正则加载

• 应用场景：整个数据文件就是json文件
• 先将json的解析加载类，放入hive的lib目录下

• 上传到Hive的lib目录下
• 重启hive服务端才能生效

• 创建表
  create table tb_json_test2 (
  id string,
  ids array,
  total_number int
  )
  ROW FORMAT SERDE ‘org.openx.data.jsonserde.JsonSerDe’
  STORED AS TEXTFILE;

注意：表中字段名称必须与json数据中字段名称一致

• 加载数据
  load data local inpath ‘/export/datas/hivedata.json’ into table tb_json_test2;

3、Python处理

--创建原始数据表：用户id、电影id、用户评分、用户的观影时间
CREATE TABLE u_data (
  userid INT,
  movieid INT,
  rating INT,
  unixtime STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

--加载数据:
load data local inpath '/export/datas/u.data' into table u_data;

--查询数据
select count(*) from u_data;

--创建新表：用户id、电影id、用户评分、用户的时间是周几
CREATE TABLE u_data_new (
  userid INT,
  movieid INT,
  rating INT,
  weekday INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';


--创建Python脚本实现将原始表的时间转为对应的星期几
vim /export/datas/weekday_mapper.py

import sys
import datetime

for line in sys.stdin:
  line = line.strip()
  userid, movieid, rating, unixtime = line.split('\t')
  weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
  print '\t'.join([userid, movieid, rating, str(weekday)])
  
  
--加载python脚本并将数据写入新表
add FILE /export/datas/weekday_mapper.py;

INSERT OVERWRITE TABLE u_data_new
SELECT
  TRANSFORM (userid, movieid, rating, unixtime)
  USING 'python weekday_mapper.py'
  AS (userid, movieid, rating, weekday)
FROM u_data;

--统计每周内每天用户观影的次数
SELECT 
  weekday, 
  COUNT(*)
FROM 
  u_data_new
GROUP BY 
  weekday;

七、窗口函数与分析函数

面试中：发现这道SQL题目需要非常复杂的SQL或者不像正常逻辑的SQL能实现的，就要考虑用窗口或者 分析来做

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics

1、窗口函数

• 语法：
  window(args) over (partition by col order by col )
• 功能：基于数据的分区聚合

• 分组：group by

• 每一组只返回一条，一般跟聚合函数连用

• 分区：

• 将数据按照规则进行区分，原来有多少条，结果还是多少条

• 位置窗口

• LEAD

• 语法：lead(col,偏移量，默认值)
• 向后偏移

select
 empno,
 ename,
 salary,
 deptno,
 LEAD(salary,1,0) over (partition by deptno order by salary desc) as top
from 
 tb_emp;

• LAG

• 语法：lag(col,偏移量，默认值)
• 向前偏移

select
   empno,
   ename,
   salary,
   deptno,
   LAG(salary,1,0) over (partition by deptno order by salary desc) as top
  from 
   tb_emp;

• 给定两列：登录时间和登录姓名

+------------------------+------------------+--+
| logindate2.longintime  | logindate2.name  |
+------------------------+------------------+--+
| 2019-08-28 00:03:00    | tom              |
| 2019-08-28 10:00:00    | frank            |
| 2019-08-28 11:00:00    | jack             |
| 2019-08-29 00:03:00    | tom              |
| 2019-08-29 10:00:00    | frank            |
| 2019-08-30 10:00:00    | tom              |
| 2019-08-30 12:00:00    | jack             |
+------------------------+------------------+--+

• 求连续两天登录的用户的名称：tom和frank

• FIRST_VALUE ：取窗口中第一个值

• 需求：新增一列，显示每个部门薪资最高的员工的编号

• 查询信息

+--------+---------+---------+---------+--+
| empno  |  ename  | salary  | deptno  |
+--------+---------+---------+---------+--+
| 7369   | SMITH   | 800.0   | 20      |
| 7499   | ALLEN   | 1600.0  | 30      |
| 7521   | WARD    | 1250.0  | 30      |
| 7566   | JONES   | 2975.0  | 20      |
| 7654   | MARTIN  | 1250.0  | 30      |
| 7698   | BLAKE   | 2850.0  | 30      |
| 7782   | CLARK   | 2450.0  | 10      |
| 7788   | SCOTT   | 3000.0  | 20      |
| 7839   | KING    | 5000.0  | 10      |
| 7844   | TURNER  | 1500.0  | 30      |
| 7876   | ADAMS   | 1100.0  | 20      |
| 7900   | JAMES   | 950.0   | 30      |
| 7902   | FORD    | 3000.0  | 20      |
| 7934   | MILLER  | 1300.0  | 10      |
+--------+---------+---------+---------+--+

• 根据需求，对每个部门进行分区，按照薪资降序排序

select
 empno,
 ename,
 salary,
 deptno,
 FIRST_VALUE(empno) over (partition by deptno order by salary desc) as top
from 
 tb_emp;

• 结果

+--------+---------+---------+---------+-------+--+
| empno  |  ename  | salary  | deptno  |  top  |
+--------+---------+---------+---------+-------+--+
| 7839   | KING    | 5000.0  | 10      | 7839  |
| 7782   | CLARK   | 2450.0  | 10      | 7839  |
| 7934   | MILLER  | 1300.0  | 10      | 7839  |
| 7788   | SCOTT   | 3000.0  | 20      | 7788  |
| 7902   | FORD    | 3000.0  | 20      | 7788  |
| 7566   | JONES   | 2975.0  | 20      | 7788  |
| 7876   | ADAMS   | 1100.0  | 20      | 7788  |
| 7369   | SMITH   | 800.0   | 20      | 7788  |
| 7698   | BLAKE   | 2850.0  | 30      | 7698  |
| 7499   | ALLEN   | 1600.0  | 30      | 7698  |
| 7844   | TURNER  | 1500.0  | 30      | 7698  |
| 7654   | MARTIN  | 1250.0  | 30      | 7698  |
| 7521   | WARD    | 1250.0  | 30      | 7698  |
| 7900   | JAMES   | 950.0   | 30      | 7698  |
+--------+---------+---------+---------+-------+--+

• LAST_VALUE ：取窗口中最后一个值

• 需求：添加一列，在每个部门的员工信息后面显示每个部门的员工薪资最低的
• 理想实现：

select
  empno,
  ename,
  salary,
  deptno,
  LAST_VALUE(empno) over (partition by deptno order by salary desc) as last
from
  db_emp.tb_emp;

• 上面是不对的，因为默认的窗口是从分区的 第一行到当前行，如果要取最小值，应该从分区的第一行到整个分区的最后一行

select
  empno,
  ename,
  salary,
  deptno,
  LAST_VALUE(empno) over (partition by deptno order by salary desc rows between unbounded preceding and unbounded following) as last
from
  db_emp.tb_emp;

• 聚合窗口

• count、sum、max、min、avg
• sum：其他函数与sum的用法一致
  sum(col1) over (partition by col2 order by col3)

partition by：按照某一列进行分区，这一列的 值相同的行会放到一起
order by：分区内部按照某一列进行排序
	注意：这里的order by与SQL语句中的全局排序的order by没有关系的
	全局排序【order by】和局部排序【sort by】：指的是底层MapReduce的排序

• 需求：

• 原始数据
  ±--------------------±------------------±--------------±-+
  | itcast_f1.cookieid | itcast_f1.daystr | itcast_f1.pv |
  ±--------------------±------------------±--------------±-+
  | cookie1 | 2018-04-10 | 1 |
  | cookie1 | 2018-04-11 | 5 |
  | cookie1 | 2018-04-12 | 7 |
  | cookie1 | 2018-04-13 | 3 |
  | cookie2 | 2018-04-13 | 3 |
  | cookie2 | 2018-04-14 | 2 |
  | cookie2 | 2018-04-15 | 4 |
  | cookie1 | 2018-04-14 | 2 |
  | cookie1 | 2018-04-15 | 4 |
  | cookie1 | 2018-04-16 | 4 |
  | cookie2 | 2018-04-10 | 1 |
  | cookie2 | 2018-04-11 | 5 |
  | cookie2 | 2018-04-12 | 7 |
  ±--------------------±------------------±--------------±-+
• 结果数据：统计每天每个cookie到目前位置的总的pv数
  | cookie1 | 2018-04-10 | 1 | 1
  | cookie1 | 2018-04-11 | 5 | 6
  | cookie1 | 2018-04-12 | 7 | 13
  | cookie1 | 2018-04-13 | 3 | 16
• 使用sum窗口实现：默认的窗口计算是从分区的第一行到当前行
  select
  cookieid,
  daystr,
  pv,
  sum(pv) over (partition by cookieid order by daystr) as current_pv
  from
  itcast_f1;
• 结果数据
  ±----------±------------±----±------------±-+
  | cookieid | daystr | pv | current_pv |
  ±----------±------------±----±------------±-+
  | cookie1 | 2018-04-10 | 1 | 1 |
  | cookie1 | 2018-04-11 | 5 | 6 |
  | cookie1 | 2018-04-12 | 7 | 13 |
  | cookie1 | 2018-04-13 | 3 | 16 |
  | cookie1 | 2018-04-14 | 2 | 18 |
  | cookie1 | 2018-04-15 | 4 | 22 |
  | cookie1 | 2018-04-16 | 4 | 26 |
  | cookie2 | 2018-04-10 | 1 | 1 |
  | cookie2 | 2018-04-11 | 5 | 6 |
  | cookie2 | 2018-04-12 | 7 | 13 |
  | cookie2 | 2018-04-13 | 3 | 16 |
  | cookie2 | 2018-04-14 | 2 | 18 |
  | cookie2 | 2018-04-15 | 4 | 22 |
  ±----------±------------±----±------------±-+
• 如果不指定排序：默认窗口是从分区的第一行到最后一行
  select
  cookieid,
  daystr,
  pv,
  sum(pv) over(partition by cookieid ) as pv2
  from
  itcast_f1;
• 手动指定窗口
  - preceding：往前
  - following：往后
  - current row：当前行
  - unbounded：起点
  - unbounded preceding 表示从前面的起点，分区的第一行
  - unbounded following：表示到后面的终点，分区的最后一行

rows between 起始位置 and 结束位置
rows between unbounded preceding and current row  ：从第一行到当前行
	做分区也做排序的默认窗口
rows between unbounded preceding and unbounded following：从第一行到最后一行
	只做分区不做排序的默认窗口

• 手动实现从第一行到最后一行
  select
  cookieid,
  daystr,
  pv,
  sum(pv) over(partition by cookieid order by daystr rows between unbounded preceding and unbounded following ) as pv2
  from
  itcast_f1;
• 手动实现从第一行到当前行
  select
  cookieid,
  daystr,
  pv,
  sum(pv) over(partition by cookieid rows between unbounded preceding and current row) as pv2
  from
  itcast_f1;
• 手动指定从前N行到当前行：从前3行到当前行
  select
  cookieid,
  daystr,
  pv,
  sum(pv) over(partition by cookieid order by daystr rows between 3 preceding and current row) as pv4
  from
  itcast_f1;
• 手动指定从前N行到后N行：从前3行到后1行
  select
  cookieid,
  daystr,
  pv,
  sum(pv) over(partition by cookieid order by daystr rows between 3 preceding and 1 following) as pv4
  from
  itcast_f1;

2、分析函数：特殊的窗口函数

• 语法：func() over (parititon by col order by col)
• ROW_NUMBER【非常重要的函数：求分区topN】

• 给每个分区内部编号

select
  empno,
  ename,
  salary,
  deptno,
  row_number() over (partition by deptno order by salary desc) as rn
from
  db_emp.tb_emp;

• 结果
  ±-------±--------±--------±--------±----±-+
  | empno | ename | salary | deptno | rn |
  ±-------±--------±--------±--------±----±-+
  | 7839 | KING | 5000.0 | 10 | 1 |
  | 7782 | CLARK | 2450.0 | 10 | 2 |
  | 7934 | MILLER | 1300.0 | 10 | 3 |
  | 7788 | SCOTT | 3000.0 | 20 | 1 |
  | 7902 | FORD | 3000.0 | 20 | 2 |
  | 7566 | JONES | 2975.0 | 20 | 3 |
  | 7876 | ADAMS | 1100.0 | 20 | 4 |
  | 7369 | SMITH | 800.0 | 20 | 5 |
  | 7698 | BLAKE | 2850.0 | 30 | 1 |
  | 7499 | ALLEN | 1600.0 | 30 | 2 |
  | 7844 | TURNER | 1500.0 | 30 | 3 |
  | 7654 | MARTIN | 1250.0 | 30 | 4 |
  | 7521 | WARD | 1250.0 | 30 | 5 |
  | 7900 | JAMES | 950.0 | 30 | 6 |
  ±-------±--------±--------±--------±----±-+
• 需求：求每个部门薪资最高的 前2个人的信息

• RANK

• 功能：用于实现分区内排名编号[会留空位]
• 与row_number的区别：

• row_number：如果排序时数值相同，继续编号
  | 7654 | MARTIN | 1250.0 | 30 | 4 |
  | 7521 | WARD | 1250.0 | 30 | 5 |
• rank：如果排序时数值相同，编号不变，但留下空位
  | 7654 | MARTIN | 1250.0 | 30 | 4 |
  | 7521 | WARD | 1250.0 | 30 | 4 |

select
    empno,
    ename,
    salary,
    deptno,
    rank() over (partition by deptno order by salary desc) as rank
  from
    db_emp.tb_emp;

• 结果

+--------+---------+---------+---------+-------+--+
| empno  |  ename  | salary  | deptno  | rank  |
+--------+---------+---------+---------+-------+--+
| 7839   | KING    | 5000.0  | 10      | 1     |
| 7782   | CLARK   | 2450.0  | 10      | 2     |
| 7934   | MILLER  | 1300.0  | 10      | 3     |
| 7788   | SCOTT   | 3000.0  | 20      | 1     |
| 7902   | FORD    | 3000.0  | 20      | 1     |
| 7566   | JONES   | 2975.0  | 20      | 3     |
| 7876   | ADAMS   | 1100.0  | 20      | 4     |
| 7369   | SMITH   | 800.0   | 20      | 5     |
| 7698   | BLAKE   | 2850.0  | 30      | 1     |
| 7499   | ALLEN   | 1600.0  | 30      | 2     |
| 7844   | TURNER  | 1500.0  | 30      | 3     |
| 7654   | MARTIN  | 1250.0  | 30      | 4     |
| 7521   | WARD    | 1250.0  | 30      | 4     |
| 7900   | JAMES   | 950.0   | 30      | 6     |
+--------+---------+---------+---------+-------+--+

• DENSE_RANK【用的比较多的排名】

• 功能：用于实现分区内排名编号[不留空位]

• 与rank的区别：

• rank：如果排序时数值相同，编号不变，并留下排名空位
• dense_rank：如果排序时数值相同，编号不变，不留空位

select
  empno,
  ename,
  salary,
  deptno,
  DENSE_RANK() over (partition by deptno order by salary desc) as drank
from
  db_emp.tb_emp;

• 结果
  ±-------±--------±--------±--------±-------±-+
  | empno | ename | salary | deptno | drank |
  ±-------±--------±--------±--------±-------±-+
  | 7839 | KING | 5000.0 | 10 | 1 |
  | 7782 | CLARK | 2450.0 | 10 | 2 |
  | 7934 | MILLER | 1300.0 | 10 | 3 |
  | 7788 | SCOTT | 3000.0 | 20 | 1 |
  | 7902 | FORD | 3000.0 | 20 | 1 |
  | 7566 | JONES | 2975.0 | 20 | 2 |
  | 7876 | ADAMS | 1100.0 | 20 | 3 |
  | 7369 | SMITH | 800.0 | 20 | 4 |
  | 7698 | BLAKE | 2850.0 | 30 | 1 |
  | 7499 | ALLEN | 1600.0 | 30 | 2 |
  | 7844 | TURNER | 1500.0 | 30 | 3 |
  | 7654 | MARTIN | 1250.0 | 30 | 4 |
  | 7521 | WARD | 1250.0 | 30 | 4 |
  | 7900 | JAMES | 950.0 | 30 | 5 |
  ±-------±--------±--------±--------±-------±-+

• NTILE

• 对分区内部再次进行均分
• 语法：NTILE(N)，表示将每个分区内部分成N份

select
  empno,
  ename,
  salary,
  deptno,
  NTILE(3) over (partition by deptno order by salary desc) as numb
from
  db_emp.tb_emp;

• 结果：如果不能平均分，那么会优先分配数字小的
  ±-------±--------±--------±--------±------±-+
  | empno | ename | salary | deptno | numb |
  ±-------±--------±--------±--------±------±-+
  | 7839 | KING | 5000.0 | 10 | 1 |
  | 7782 | CLARK | 2450.0 | 10 | 1 |
  | 7934 | MILLER | 1300.0 | 10 | 2 |
  | 7788 | SCOTT | 3000.0 | 20 | 1 |
  | 7902 | FORD | 3000.0 | 20 | 1 |
  | 7566 | JONES | 2975.0 | 20 | 1 |
  | 7876 | ADAMS | 1100.0 | 20 | 2 |
  | 7369 | SMITH | 800.0 | 20 | 2 |
  | 7698 | BLAKE | 2850.0 | 30 | 1 |
  | 7499 | ALLEN | 1600.0 | 30 | 1 |
  | 7844 | TURNER | 1500.0 | 30 | 1 |
  | 7654 | MARTIN | 1250.0 | 30 | 2 |
  | 7521 | WARD | 1250.0 | 30 | 2 |
  | 7900 | JAMES | 950.0 | 30 | 2 |
  ±-------±--------±--------±--------±------±-+

• 分析和窗口函数：实现基于数据分区的排名、统计、取值

• 在数理统计中比较常用的 函数：数据分析
• 窗口函数：lead/lag/first_value/last_value/sum/count/avg/max/min/
• 分析函数：row_number/rank/dense_rank/ntile

• 如果面试题中出现了分区取值，肯定 用窗口或者分析函数

• 给你整个公司所有部门的信息，让你统计每个部门的前3名
• 每个部门要返回三条：不能用分组【每个分组只能返回1条】，用分区

八、Hive中的基础优化

1、参数优化

• fetch task

• Hive的默认计算引擎是MR
• 有的SQL语句走MapReduce，有的不走，为什么？
• 没有走MapReduce的SQL语句走了fetch task【快】
• 由参数决定哪些程序走fetchtask
  
  hive.fetch.task.conversion
  minimal
  
  Some select queries can be converted to single FETCH task minimizing latency.
  Currently the query should be single sourced not having any subquery and should not have
  any aggregations or distincts (which incurs RS), lateral views and joins.
  1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
  2. more : SELECT, FILTER, LIMIT only (TABLESAMPLE, virtual columns)
  
• minimal：select *和对分区表的分区过滤不走MapReduce
• more：简单的select语句【不包含聚合、分组、排序等等】、字段过滤、limit都不走MapReduce
• 工作中建议修改为more的模式

• 严格模式

• 参数决定
  
  hive.mapred.mode
  nonstrict
  The mode in which the Hive operations are being performed.
  In strict mode, some risky queries are not allowed to run. They include:
  Cartesian Product.
  No partition being picked up for a query.
  Comparing bigints and strings.
  Comparing bigints and doubles.
  Orderby without limit.
  
• 默认为非严格模式，工作中建议修改为严格模式
• 严格模式下可以避免一些程序的故障，更加严格代码规范

• 严格模式下，不允许以下代码的产生

• Cartesian Product

• 笛卡尔积：join时不指定关联字段

• 1亿条数据 关联 另外1亿条 = 1亿*1亿

• No partition being picked up for a query.

• 在分区表查询时不指定分区过滤

• Comparing bigints and strings.

• 将bigint类型与字符串类型进行比较

• Comparing bigints and doubles.

• 将bigint类型与double类型进行比较

• Orderby without limit.

• 如果做了全局排序不使用limit输出

• 本地模式：一般用于测试开发

• 如果一个SQL语句处理的数据量不是很大，不需要提交给yarn取运行，直接在当前机器上完成计算
• 参数
  
  hive.exec.mode.local.auto
  false
  Let Hive determine whether to run in local mode automatically
• 开启了本地模式，就不会提交给yarn集群运行，直接在本地做计算
• 要求：以下情况下本地模式才会生效

• 处理的数据不允许超过128M
• 该SQL语句对应的MapReduce的map个数不允许超过4个
• 该SQL语句对应的MapReduce的reduce个数不允许超过1个

• jvm重用

• MapReduce的一个属性优化
• 当我们执行MapReduce程序时，每个Task进程会启动一个JVM，当进程执行完毕，会销毁JVM进程
• 这样做，会导致JVM的资源浪费，JVM在启动时需要消耗大量的时间来申请资源

• 假设有10个Task，每个Task需要一个JVM，总共需要10个JVM

• 每一次资源只够申请1个JVM
• 申请10次

• 如果开启了JVM重用

• 申请1次，10个task都在这一个jvm中运行

• 参数： 临时修改 set mapreduce.job.jvm.numtasks = 10

• 推测执行

• map的推测执行：临时修改，永久设置需要修改配置文件的
  set mapreduce.map.speculative=true;
• reduce的推测执行
  set mapreduce.reduce.speculative=true;
• 假设这个程序有10MapTask ，其中有9个MapTask都已经完成了，剩下最后一个MapTask迟迟不能完成，程序会认为该Task出了问题，如果开启了推测执行，程序会再启动一个MapTask，实现的工作与之前的MapTask一模一样，谁先完成，另外一个会被kill掉
• 设计为了考虑到某些task因为环境问题出故障，可以启动一个备份的task来运行
• 个人经验不建议一定开启

• 如果因为环境问题导致的故障，环境问题一直存储，每次产生的新的task最终都会失败
• 最终导致多个task全部阻塞

• 压缩

• Hive中的压缩就是MapReduce中的压缩

• 先主要配置MapReduce中Map端shuffle输出的数据压缩
  mapreduce.map.output.compress=true;
  mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec

• 可以配置Hive中程序的中间结果压缩
  
  hive.exec.compress.output
  true

<property>
  <name>hive.exec.compress.intermediate</name>
  <value>true</value>
</property>

• combiner：MapReduce中Map端的聚合
  set hive.map.aggr = true;//在底层的Mapreduce程序执行时会开启Combiner
• MapTask的个数：Max（min_split,Min(max_split,block_size)）

• 如何调整Map的个数：修改min_split的大小

• ReduceTask的个数

• 默认为1
• 建议根据数据量做采样测试

2、SQL优化

• join

• Map join ：如果有一张小表要join大表，可以使用map join，将小表放入分布式内存中，与大表的 每个部分进行join

• 小表的数据不超过128M，可以认为这是一个小表

• 小表的大小阈值：set hive.mapjoin.smalltable.filesize=25123456;

• set hive.auto.convert.join = true; 默认为true

• Reduce join：普通的join，大表Join大表
• SMB join：桶表的join，适合于大表join大表，优化的方式

• 两张表都必须为桶表

• 分区裁剪

• 如果做了分区表，在对表进行分析处理时，尽量加上分区字段的过滤
• 先过滤，后处理

• 列裁剪

• 如果处理数据时，先将不需要用到的列过滤掉，然后再处理
• 先过滤，后处理

• count(distinct)

• 在count时，如果结果需要做去重，一定先distinct，然后再聚合
• 先过滤，后处理，不然会导致数据倾斜
• distinct在hive的底层就是分组

3、表优化

• 分区表：尽量根据业务需求将表作分区表，方便以后对分区数据进行处理
• 分桶表：如果后期的需求中，会出现大数据的join，推荐创建分桶表
• 表的数据存储