HiveSQL(三)

内容大纲:
1. 掌握Hive的函数
//函数分类：内置函数、用户定义函数
//UDF、UDTF、UDAF

2. Hive函数高阶应用		//面试笔试、开发高频区域
	explode(UDTF)函数功能
	lateral view 侧视图
	行列转换
	json格式数据解析	
	窗口函数（Window function）开窗函数
	分组TopN、级联累加问题、连续登陆
	
3. Hive的性能调优
	hive的数据文件格式 , 数据压缩
		行式存储 
		列式存储（ORC parquet）
	hive通用调优	
		*join优化
		*group by数据倾斜优化
		*task并行度问题
		其他通用调优

********************************* 具体内容 *********************************

Apache Hive-内置运算符(看看,要有印象)
官方链接：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

一些Hive命令:
show functions; //显示所有的函数和运算符
describe function +; //查看运算符或者函数的使用说明
describe function extended +; //使用extended 可以查看更加详细的使用说明

具体分类:
1. 关系运算符
2. 算术运算符
3. 逻辑运算符

参考代码:
-- 1、创建表dual
create table dual(id string);

 -- 2、加载一个文件dual.txt到dual表中
 -- dual.txt只有一行内容：内容为一个空格
 
 load data local inpath '/root/hivedata/dual.txt' into table dual;
 
 -- 3、在select查询语句中使用dual表完成运算符、函数功能测试
 select 1+1 from dual;
 select 1+1;

 //----------------Hive中关系运算符--------------------------
 // is null空值判断
 select 1 from dual where 'itcast' is null;

 // is not null 非空值判断
 select 1 from dual where 'itcast' is not null;

 // like比较： _表示任意单个字符 %表示任意数量字符
 // 否定比较： NOT A like B
 select 1 from dual where 'itcast' like 'it_';
 select 1 from dual where 'itcast' like 'it%';
 select 1 from dual where  'itcast' not like 'hadoo_';
 select 1 from dual where  not 'itcast' like 'hadoo_';

 // rlike：确定字符串是否匹配正则表达式，是regexp_like()的同义词。
 select 1 from dual where 'itcast' rlike '^i.*t$';
 select 1 from dual where '123456' rlike '^\\d+$';  // 判断是否全为数字
 select 1 from dual where '123456aa' rlike '^\\d+$';

 // regexp：功能与rlike相同 用于判断字符串是否匹配正则表达式
 select 1 from dual where 'itcast' regexp '^i.*t$';

 // Hive中算术运算符----------------
 // 取整操作: div  给出将A除以B所得的整数部分。例如17 div 3得出5。
 select 17 div 3;

 // 取余操作: %  也叫做取模mod  A除以B所得的余数部分
 select 17 % 3;

 // 位与操作: &  A和B按位进行与操作的结果。 与表示两个都为1则结果为1
 select 4 & 8 from dual;  // 4转换二进制：0100 8转换二进制：1000
 select 6 & 4 from dual;  // 4转换二进制：0100 6转换二进制：0110

 // 位或操作: |  A和B按位进行或操作的结果  或表示有一个为1则结果为1
 select 4 | 8 from dual;
 select 6 | 4 from dual;

 // 位异或操作: ^ A和B按位进行异或操作的结果 异或表示两者的值不同,则结果为1
 select 4 ^ 8 from dual;
 select 6 ^ 4 from dual;


 // 3、Hive逻辑运算符
 // 与操作: A AND B   如果A和B均为TRUE，则为TRUE，否则为FALSE。如果A或B为NULL，则为NULL。
 select 1 from dual where 3>1 and 2>1;

 // 或操作: A OR B   如果A或B或两者均为TRUE，则为TRUE，否则为FALSE。
 select 1 from dual where 3>1 or 2!=2;

 // 非操作: NOT A 、!A   如果A为FALSE，则为TRUE；如果A为NULL，则为NULL。否则为FALSE。
 select 1 from dual where not 2>1;
 select 1 from dual where !2=1;

 // 在:A IN (val1, val2, ...)  如果A等于任何值，则为TRUE。
 select 1 from dual where 11  in(11,22,33);

 // 不在:A NOT IN (val1, val2, ...) 如果A不等于任何值，则为TRUE
 select 1 from dual where 11 not in(22,33,44);

扩展:
/*
二进制和十进制快速转换法, 8421码表:
二进制数据: 0 0 0 0 0 0 0 0
对应的十进制数据: 128 64 32 16 8 4 2 1

 二进制转十进制案例, 求: 101011的十进制, 其实相当于求 0010 1011的十进制 = 32 + 8 + 2 + 1 = 43
 十进制转二进制案例, 求: 56的二进制, 56 = 32 + 16 + 8 = 0011 1000

 细节: 第1位是符号位, 0:正数, 1:负数,  其它的是数值位.

 0100        4的二进制
 0110        6的二进制

& -----------------
0100 4
*/

Apache Hive-函数的分类(UDF, UDAF, UDTF)

关于Hive的函数的分类.
/*
Hive中的函数主要分为: 内置函数和自定义函数, 但这是早期的分法, 为了更好的划分用户自定义函数, Hive又把函数分为三大类, 分别如下:
内置函数: 系统内置的函数,指的是hive开发好，可以直接上手使用的.

 用户自定义函数:
     UDF:    普通函数, 一进一出.     即: 输入1行, 输出1行.
         select split('aa,bb,cc,dd', ',');   -- 按照,切割,  ["aa","bb","cc","dd"]

     UDAF:   聚合函数, 多进一出.     即: 输入多行, 输出1行.           UDAF(user defined aggregation functions    aggregation:聚集，聚合)
         select count(1) from student;

     UDTF:   表生成函数, 一进多出.   即: 输入1行, 输出多行.          UDTF(User-Defined Table-Generating Functions   表生成函数)
         select explode(array('aa', 'bb', 'cc'));    -- explode()炸裂函数.

后来Hive发现用 UDF, UDAF, UDTF来划分函数非常方便, 于是有了函数扩大化的概念, 即: 本来UDF, UDAF, UDTF是用来划分用户自定义函数的,
现在 UDF, UDAF, UDTF 是用来划分Hive中所有函数的, 包括内置函数和用户自定义函数.

总结:只记UDF,UDAF,UDTF即可.

Apache Hive-常用的内置函数详解
字符串函数:
// 字符串截取函数：substr(str, pos[, len]) 或者 substring(str, pos[, len])
select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数
select substr("angelababy",2,2);

 //正则表达式替换函数：regexp_replace(str, regexp, rep)
 select regexp_replace('100-200', '(\\d+)', 'num'); --正则分组

 //URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数
 select parse_url('http://www.itcast.cn/path/p1.php?query=1', 'HOST');

 //分割字符串函数: split(str, regex)
 select split('apache hive', '\\s+');	//匹配一个或者多个空白符

 //json解析函数：get_json_object(json_txt, path)
 //$表示json对象
 select get_json_object('[{"website":"www.itcast.cn","name":"allenwoon"}, {"website":"cloud.itcast.com","name":"carbondata 中文文档"}]', '$.[1].website');

日期函数
// 获取当前日期: current_date
select current_date();
// 获取当前时间戳: current_timestamp
// 同一查询中对current_timestamp的所有调用均返回相同的值。
select current_timestamp();
// 获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
// 日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
// UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1620723323);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
// 日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
// 日期增加函数: date_add
select date_add('2012-02-28',10);
// 日期减少函数: date_sub
select date_sub('2012-01-1',10);

数字函数:
// 取整函数: round 返回double类型的整数值部分（遵循四舍五入）
select round(3.1415926);
// 指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);
// 向下取整函数: floor
select floor(3.1415926);
select floor(-3.1415926);
// 向上取整函数: ceil
select ceil(3.1415926);
select ceil(-3.1415926);
// 取随机数函数: rand 每次执行都不一样返回一个0到1范围内的随机数
select rand();
// 指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(5);

条件函数:
// if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;

 // 空判断函数: isnull( a )
 select isnull("allen");
 select isnull(null);

 // 非空判断函数: isnotnull ( a )
 select isnotnull("allen");
 select isnotnull(null);

 // 空值转换函数: nvl(T value, T default_value)
 select nvl("allen","itcast");
 select nvl(null,"itcast");

 // 非空查找函数: COALESCE(T v1, T v2, ...)
 // 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
 select COALESCE(null,11,22,33);
 select COALESCE(null,null,null,33);
 select COALESCE(null,null,null);

 // 条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
 select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
 select case sex when '男' then 'male' else 'female' end from student limit 3;

Type Conversion Functions 类型转换函数:
前置知识: Hive中支持类型的隐式转换有限制自动转换不保证成功就显示null
//cast显示类型转换函数

 --任意数据类型之间转换:cast
 select cast(12.14 as bigint);
 select cast(12.14 as string);
 select cast("hello" as int);
 +-------+
 |  _c0  |
 +-------+
 | NULL  |
 +-------+

Data Masking Functions 数据脱敏函数
//mask脱敏掩码处理, 数据脱敏：让敏感数据不敏感, 13455667788 --->134****7788
// mask, 将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
select mask("abc123DEF");
select mask("abc123DEF",'-','.','^'); // 自定义替换的字母

 // mask_first_n(string str[, int n]
 // 对前n个进行脱敏替换
 select mask_first_n("abc123DEF",4);

 // mask_last_n(string str[, int n])
 select mask_last_n("abc123DEF",4);

 // mask_show_first_n(string str[, int n])
 // 除了前n个字符，其余进行掩码处理
 select mask_show_first_n("abc123DEF",4);

 // mask_show_last_n(string str[, int n])
 select mask_show_last_n("abc123DEF",4);

 // mask_hash(string|char|varchar str)
 // 返回字符串的hash编码。
 select mask_hash("abc123DEF");

Misc. Functions 其他杂项函数、加密函数
// 如果你要调用的java方法所在的jar包不是hive自带的可以使用add jar添加进来
// hive调用java方法: java_method(class, method[, arg1[, arg2..]])
select java_method("java.lang.Math","max",11,22);

 // 反射函数: reflect(class, method[, arg1[, arg2..]])
 select reflect("java.lang.Math","max",11,22);

 // 取哈希值函数:hash
 select hash("allen");

 // current_user()、logged_in_user()、current_database()、version()

 // SHA-1加密: sha1(string/binary)
 select sha1("allen");

 // SHA-2家族算法加密：sha2(string/binary, int)  (SHA-224, SHA-256, SHA-384, SHA-512)
 select sha2("allen",224);
 select sha2("allen",512);

 // crc32加密:
 select crc32("allen");

 // MD5加密: md5(string/binary)
 select md5("allen");

Apache Hive-函数高阶应用--explode函数(炸裂函数,爆炸函数)的功能
概述:
explode属于UDTF函数，表生成函数，输入一行数据输出多行数据。
功能:
//explode接收map array类型的参数 ,用于行转--> 列的.
//它只能处理: Array, map类型的数据.

 explode(array(11,22,33))         
 	11
 	22
 	33																		
 select explode(`array`(11,22,33,44,55));
 select explode(`map`("id",10086,"name","allen","age",18));	  
 //一般函数名,关键字会用``标记.

案例:
将NBA总冠军球队数据使用explode进行拆分，并且根据夺冠年份进行倒序排序。

具体实现:
1. step1:建表
create table the_nba_championship(
team_name string,
champion_year array
) row format delimited
fields terminated by ',' collection items terminated by '|';

 2. step2:加载数据文件到表中
 	load data local inpath '/root/hivedata/The_NBA_Championship.txt' into table the_nba_championship;


 3. step4:使用explode函数对champion_year进行拆分 俗称炸开
 	select explode(champion_year) from the_nba_championship;

 4. 想法是正确的 sql执行确实错误的
 	select team_name,explode(champion_year) from the_nba_championship;
 	//错误信息
 	//UDTF's are not supported outside the SELECT clause, nor nested in expressions
 	//UDTF 在 SELECT 子句之外不受支持，也不在表达式中嵌套？？？

如果数据不是map或者array 如何使用explode函数呢？
//想法设法使用split subsrt regex_replace等函数组合使用把数据变成array或者map.

 //参考代码
 select explode(split(champion_year, '|')) from the_nba_championship;
 //split()会将函数切成array类型返回

Apache Hive--函数高阶应用--UDTF函数使用限制、lateral View侧视图功能
原理:
侧视图的原理是将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表

背景:
1. UDTF函数生成的结果可以当成一张虚拟的表，但是无法和原始表进行组合查询,简单的说就是炸裂函数炸裂出来的相当于新表,要么用join联合查,要么侧视图
select name,explode(location) from test_message; //这个sql就是错误的相当于执行组合查询
2. 从理论层面推导，对两份数据进行join就可以了
3. 但是，hive专门推出了lateral view侧视图的语法，满足上述需要。

功能:
把UDTF函数生成的结果和原始表进行关联，便于用户在select时间组合查询, lateral view是UDTf的好基友好搭档，实际中经常配合使用。

语法:
1. lateral view侧视图基本语法如下
select …… from tabelA lateral view UDTF(xxx) 表别名 as 字段别名;
```
 2. 针对上述NBA冠军球队年份排名案例，使用explode函数+lateral view侧视图，可以完美解决
 	select a.team_name ,b.year from the_nba_championship a 
 	lateral view explode(champion_year) b as year;

 3. 根据年份倒序排序
 	select a.team_name ,b.year from the_nba_championship a 
 	lateral view explode(champion_year) b as year
 	order by b.year desc;

 4. 统计每个球队获取总冠军的次数 并且根据倒序排序
 	select a.team_name ,count(*) as nums from the_nba_championship a 
 	lateral view explode(champion_year) b as year
 	group by a.team_name
 	order by nums desc;
```
Apache Hive--函数高阶应用--行列转换--多行转单列（collect、concat_ws）
1. 数据收集函数
  collect_set //把多行数据收集为一行返回set集合去重无序
  collect_list //把多行数据收集为一行返回list集合不去重有序
2. 字符串拼接函数
  concat //直接拼接字符串,采用默认拼接符(即啥都没有,直接拼),拼接什么类型的数据都可以
  concat_ws //指定分隔符拼接,第1个值是分隔符, 之后的都是数据, 必须是string类型或者是 array
  
  select concat("it","cast","And","heima");
  select concat("it","cast","And",null);
  
  select concat_ws("-","itcast","And","heima");
  select concat_ws("-","itcast","And",null);
3. 案例:
  //原表
  +----------------+----------------+----------------+--+
  | row2col2.col1 | row2col2.col2 | row2col2.col3 |
  +----------------+----------------+----------------+--+
  | 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 |
  +-------+-------+--------+--+
4. 具体实现步骤.
  4.1 建表
  create table row2col2(
  col1 string,
  col2 string,
  col3 int
  )row format delimited fields terminated by '\t';
  
  4.2 加载数据到表中
  load data local inpath '/root/hivedata/r2c2.txt' into table row2col2;
  select * from row2col2;
  
  4.3 最终SQL实现
  select
  col1,
  col2,
  concat_ws(',', collect_list(cast(col3 as string))) as col3
  from
  row2col2
  group by
  col1, col2;

Apache Hive--函数高阶应用--行列转换--单列转多行（explode+lateral view）

需求:
1. 原表
+-------+-------+--------+--+
| col1 | col2 | col3 |
+-------+-------+--------+--+
| a | b | 1,2,3 |
| c | d | 4,5,6 |
+-------+-------+--------+--+

 2. 目标表
 	+----------------+----------------+----------------+--+
 	| row2col2.col1  | row2col2.col2  | row2col2.col3  |
 	+----------------+----------------+----------------+--+
 	| a              | b              | 1              |
 	| a              | b              | 2              |
 	| a              | b              | 3              |
 	| c              | d              | 4              |
 	| c              | d              | 5              |
 	| c              | d              | 6              |
 	+----------------+----------------+----------------+--+

具体实现步骤:
1. 创建表
create table col2row2(
col1 string,
col2 string,
col3 string
)row format delimited fields terminated by '\t';

 2. 加载数据
 	load data local inpath '/root/hivedata/c2r2.txt' into table col2row2;
 	//select * from col2row2;
 	//select explode(split(col3,',')) from col2row2;
 
 3. SQL最终实现
 	select
 		col1,
 		col2,
 		lv.col3 as col3
 	from
 		col2row2
 	lateral view explode(split(col3, ',')) lv as col3;

Apache Hive--函数高阶应用--json格式数据处理
解释:
在hive中，没有json类的存在，一般使用 string类型来修饰，叫做json字符串，简称 json串。

在hive中，处理json数据的两种方式 //hive内置了两个用于解析json的函数
json_tuple //是UDTF 表生成函数输入一行，输出多行一次提取读个值可以单独使用也可以配合lateral view侧视图使用
get_json_object //是UDF普通函数，输入一行输出一行一次只能提取一个值多次提取多次使用
//使用[JsonSerDe] 类解析，在加载json数据到表中的时候完成解析动作

案例:
1. 创建表
create table tb_json_test1 (
json string
);

 2. 加载数据
 	load data local inpath '/root/hivedata/device.json' into table tb_json_test1;
 	//select * from tb_json_test1;

 3. get_json_object UDF函数 最大弊端是一次只能解析提取一个字段
 	select
 		--获取设备名称
 		get_json_object(json,"$.device") as device,
 		--获取设备类型
 		get_json_object(json,"$.deviceType") as deviceType,
 		--获取设备信号强度
 		get_json_object(json,"$.signal") as signal,
 		--获取时间
 		get_json_object(json,"$.time") as stime
 	from tb_json_test1;

 4. son_tuple 这是一个UDTF函数 可以一次解析提取多个字段
 	//单独使用 解析所有字段
 	select
 		json_tuple(json,"device","deviceType","signal","time") as (device,deviceType,signal,stime)
 	from tb_json_test1;

 5. 搭配侧视图使用
 	select
 		json,device,deviceType,signal,stime
 	from tb_json_test1
 	lateral view json_tuple(json,"device","deviceType","signal","time") b as device,deviceType,signal,stime;


 6. 方式2： 使用JsonSerDe类在建表的时候解析数据
 	//建表的时候直接使用JsonSerDe解析
 	create table tb_json_test2 (
 	   device string,
 	   deviceType string,
 	   signal double,
 	   `time` string
 	) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE;

 	load data local inpath '/root/hivedata/device.json' into table tb_json_test2;

 	select * from tb_json_test2;	//查询数据.

Apache Hive--窗口函数--如何快速理解感受窗口函数（窗口函数其实可以直接看老师上课的SQL随堂即可）
快速理解窗口函数功能
window function 窗口函数、开窗函数、olap分析函数。
窗口：
可以理解为操作数据的范围，窗口有大有小，本窗口中操作的数据有多有少。
可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行；
而窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。

窗口函数相关概述(重重点,下面这些话学会了,窗口函数就学会了):
/*
窗口函数(下面的背下来):
概述:
特指的是over(), 它一般是用来描述范围的, 即: 局部操作, 从哪里开始, 到哪里结束.
如果不用窗口函数, 则默认操作的是表中所有的数据.
格式:
可以和窗口函数结合使用的函数 over(partition by 分区字段 order by 排序的列 asc/desc rows between ** and **)
细节:
1. 窗口函数一般可以结合聚合函数或者排序函数, 以及其它函数一起使用.
2. 这里的聚合和排序分别指的是:
聚合函数: count(), sum(), max(), min(), avg()
排序函数: row_number(), rank(), dense_rank(), ntile(几分之几)
其它函数: lag(), lead(), first_value(), last_value()
3. 如果不写 partition by, 则: 全局聚合, 如果写了, 则: 局部聚合(分区, 分组聚合)
4. 如果不写 order by, 则: 统计组内所有的数据., 如果写了, 则: 累计统计, 即: 统计从第一行截止到当前行的总数.
5. rows后边的between and 的值可以是如下的这些格式:
preceding：往前
following：往后
current row：当前行
unbounded：起点
unbounded preceding 表示从前面的起点第一行
unbounded following：表示到后面的终点最后一行
6. ntile(数字,表示分成几份) 采用均分策略, 每份之间的差值不超过1, 优先参考最小的那个部分, 即: 7分成3份, 则是: 3, 2, 2
*/

案例:
-- 需求0: 准备数据.
create database day11;
use day11;
-- 1. 创建数据表.
CREATE TABLE employee(
id int,
name string,
deg string,
salary int,
dept string
) row format delimited fields terminated by ',';
```
 //创建网站点击量信息表.
 create table website_pv_info(
    cookieid string,
    createtime string,   --day
    pv int
 ) row format delimited fields terminated by ',';

 //创建网站访问记录信息表
 create table website_url_info (
 	cookieid string,
 	createtime string,  --访问时间
 	url string       --访问页面
 ) row format delimited fields terminated by ',';

 -- 2. 上传源文件到目标表路径下.
 -- load data local inpath 'Linux的路径' into table 表名 partition(分区字段1, 分区字段2=值);

 -- 3. 查看表数据(源数据)
 explain select * from employee;
 select * from website_pv_info;
 select * from website_url_info;

 -- 需求1: 窗口函数 + 聚合函数一起使用, 窗口函数初体验.
 -- 需求: 统计所有员工的工资, 并将该数值显示到每个员工信息的后边.
 -- 写法1: sum()聚合函数 + group by, 只是看看效果, 不是我们要的数据.
 select name, sum(salary) totcal_salary from employee group by name;

 -- 写法2: sum()聚合函数 + over()窗口函数
 select *, sum(salary) over() totcal_salary from employee;   -- 窗口函数相当于给原表"新增一列", 至于该列的内容是什么, 取决于 窗口函数前边写的是啥.
```

Apache Hive--窗口函数--窗口加聚合函数的使用（sum+over）
窗口函数语法
具有 OVER语句的函数叫做窗口函数。

格式:
Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])

名词解释:
1. 其中Function(arg1,..., argn) 可以是下面分类中的任意一个
聚合函数：比如sum max avg等
排序函数：比如rank row_number等
分析函数：比如lead lag first_value等

2. OVER [PARTITION BY <...>] 类似于group by 用于指定分组  每个分组你可以把它叫做窗口
   如果没有PARTITION BY 那么整张表的所有行就是一组

3. [ORDER BY <....>]  用于指定每个分组内的数据排序规则 支持ASC、DESC

4. [<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行

准备数据: //建表加载数据后续练习使用
1. 建表并且加载数据
//创建网站点击量信息表.
create table website_pv_info(
cookieid string,
createtime string, --day
pv int
) row format delimited fields terminated by ',';

	//创建网站访问记录信息表
	create table website_url_info (
		cookieid st ring,
		createtime string,  --访问时间
		url string       --访问页面
	) row format delimited fields terminated by ',';

//加载数据到数据表中
load data local inpath '/root/hivedata/website_pv_info.txt' into table website_pv_info;
load data local inpath '/root/hivedata/website_url_info.txt' into table website_url_info;

//查询表数据.
select * from website_pv_info;
select * from website_url_info;

窗口聚合函数:
语法:
sum|max|min|avg OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])

重点:
	1. 有PARTITION BY 没有PARTITION BY的区别, 有ORDER BY没有ORDER BY的区别.
	2. 有没有partition by 影响的是全局聚合 还是分组之后 每个组内聚合。
	3. 有没有 order by：
		没有order by,聚合的时候是组内所有的数据聚合再一起  全局聚合
		如果有order by，聚合的时候是累加聚合，默认是第一行聚合到当前行。

案例:
	1. 求出每个用户总pv数  sum+group by普通常规聚合操作
		select cookieid,sum(pv) as total_pv from website_pv_info group by cookieid;
		+-----------+-----------+
		| cookieid  | total_pv  |
		+-----------+-----------+
		| cookie1   | 26        |
		| cookie2   | 35        |
		+-----------+-----------+

	2. sum+窗口函数 总共有四种用法 注意是整体聚合 还是累积聚合
		//sum(...) over( )对表所有行求和
		//sum(...) over( order by ... ) 连续累积求和
		//sum(...) over( partition by... ) 同组内所行求和
		//sum(...) over( partition by... order by ... ) 在每个分组内，连续累积求和

		2.1 需求：求出网站总的pv数 所有用户所有访问加起来
			//sum(...) over( )对表所有行求和
			select cookieid,createtime,pv,
				   sum(pv) over() as total_pv  
			from website_pv_info;

		2.2 需求：求出每个用户总pv数
			//sum(...) over( partition by... )，同组内所行求和
			select cookieid,createtime,pv,
				   sum(pv) over(partition by cookieid) as total_pv
			from website_pv_info;

		2.3 需求：求出每个用户截止到当天，累积的总pv数
			//sum(...) over( partition by... order by ... )，在每个分组内，连续累积求和
			select cookieid,createtime,pv,
				   sum(pv) over(partition by cookieid order by createtime) as current_total_pv
			from website_pv_info;
			+-----------+-------------+-----+-------------------+
			| cookieid  | createtime  | pv  | current_total_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  | 2   | 2                 |
			| cookie2   | 2018-04-11  | 3   | 5                 |
			| cookie2   | 2018-04-12  | 5   | 10                |
			| cookie2   | 2018-04-13  | 6   | 16                |
			| cookie2   | 2018-04-14  | 3   | 19                |
			| cookie2   | 2018-04-15  | 9   | 28                |
			| cookie2   | 2018-04-16  | 7   | 35                |
			+-----------+-------------+-----+-------------------+

Apache Hive--窗口函数--窗口聚合函数的使用（window_expression）
window_expression 解释
直译叫做window表达式，通俗叫法称之为window子句。

功能：
控制窗口操作的范围。

语法:
rows between
preceding：往前
following：往后
current row：当前行
unbounded：起点
unbounded preceding 表示从前面的起点第一行
unbounded following：表示到后面的终点最后一行

案例:
1. 默认从第一行到当前行
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1
from website_pv_info;

2. 第一行到当前行 等效于rows between不写 默认就是第一行到当前行
	select cookieid,createtime,pv,
		   sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
	from website_pv_info;

3. 向前3行至当前行
	select cookieid,createtime,pv,
		   sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
	from website_pv_info;

4. 向前3行 向后1行
	select cookieid,createtime,pv,
		   sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
	from website_pv_info;

5. 当前行至最后一行
	select cookieid,createtime,pv,
		   sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
	from website_pv_info;

6. 第一行到最后一行 也就是分组内的所有行
	select cookieid,createtime,pv,
		   sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding  and unbounded following) as pv6
	from website_pv_info;

Apache Hive--窗口函数--窗口排序函数（row_number、rank）
功能：
主要对数据分组排序之后，组内顺序标号。
核心函数：
row_number, rank、dense_rank

适合场景：
分组TopN问题（注意哦不是全局topN）

案例:
SELECT
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM website_pv_info;

需求：找出每个用户访问pv最多的Top3 重复并列的不考虑
SELECT * from
(SELECT
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS seq
FROM website_pv_info) tmp where tmp.seq <4;

ntile函数
功能：
将分组排序之后的数据分成指定的若干个部分（若干个桶）
```
规则：
	尽量平均分配 ，优先满足最小的桶，彼此最多不相差1个。

案例:
	//把每个分组内的数据分为3桶
	SELECT
		cookieid,
		createtime,
		pv,
		NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
	FROM website_pv_info ORDER BY cookieid,createtime;

需求：统计每个用户pv数最多的前3分之1天。
//理解：将数据根据cookieid分 根据pv倒序排序 排序之后分为3个部分 取第一部分
	SELECT * from
		(SELECT
			 cookieid,
			 createtime,
			 pv,
			 NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn
		 FROM website_pv_info
		) tmp 
	where rn =1;
```
Apache Hive--其它窗口函数--窗口偏移函数（lag lead）
//1. LAG 用于统计窗口内往上第n行值
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time
FROM website_url_info;

//2. LEAD 用于统计窗口内往下第n行值
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time
FROM website_url_info;

//3. FIRST_VALUE 取分组内排序后，截止到当前行，第一个值
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
FROM website_url_info;

//4. LAST_VALUE 取分组内排序后，截止到当前行，最后一个值
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
FROM website_url_info;
Hive的调优相关(共11种).
/*
以下所有的内容都是和Hive调优相关, 它的调优方式主要有以下几种:
1. 数据压缩.
  解释:
  1. 默认是不压缩的, 我们可以采用 GZip, Lzo, Zlib, Snappy 等这些压缩协议.
  2. 压缩协议好与不好我们主要看的是3个维度: 压缩比, 压缩速度(写), 解压速度(读取)
  3. 好处: 节省空间, 可以存储更多的文件, 提高磁盘利用率. 弊端: 解压和压缩的时候, 需要消耗一些额外的资源(CPU, 内存, 磁盘) 和时间.
  4. 实际开发中, 推荐使用: snappy协议.
  细节:
  1. Hive的文件压缩, 其实本质上还是对MR的压缩, 分为: Map端压缩和 Reduce端压缩.
  2. Map端压缩的目的是: 降低shuffle 和 Reduce端拉取数据的数量, 减少io(数据传输), 提高效率.
  3. Reduce端压缩目的是: reduce是把数据写到目的地文件的(即: HDFS上), 它的压缩是为了节省空间, 可以存储更多的文件, 提高磁盘利用率.
2. 数据存储. 指的是Hive中数据的存储方式, 主要有两种方式:
  方式1: 行存储.
  TextFile: 默认的.
  SequenceFile:
  方式2: 列存储.
  Orc: FaceBook公司推的.
  Parquet: Twitter 和 Cloudera公司联合推出的.
  实际开发中, 如果你也不清楚用谁, 建议使用: Snappy + Orc, 因为它们更均衡一点.
  
  见图片(2)中图2.
3. Fetch抓取.
  这个设置不用你做, hive默认是开启的, 它的本质是: 能不把HiveSQL转成MR任务执行, 就不转换.
  细节: 即, 哪些HiveSQL不会转成MR任务呢?
  1. select * from 表名.
  2. select 列1,列2... from 表名
  3. select * from 表名 limit 起始索引, 数据条数;
4. 本地模式.
  目的: 如果非得把HiveSQL转成MR任务执行, 能在本地执行, 就不要交给yarn集群运行.
5. join优化, 指的是: join在哪个时间段执行, 例如: Map段, Reduce段.
  情况1: 小表 join 大表, 如果表的文件大小小于等于 25000000, 则是小表, 否则是大表.小表join大表,可以在reduceTask拉取数据前做一个合并操作,类似规约,降低拉取次数,提高效率.
  情况2: 大表 join 大表, 主要是做空值(null)的处理, 是把空值数据给过滤掉, 还是替换成其它的值.
  情况3(看不懂先略过): 分桶表, join查询的时候, on关联条件大多数会写id, 如果是分桶表, 且符合条件(分桶的偶数倍), 可以用分桶字段作为join字段.
  select * from A inner join B on A.分桶字段 = B.分桶字段; 前提是: A的分桶个数是 B的分桶个数的偶数倍, 或者 B的分桶个数是 A的分桶个数的偶数倍.
  
  见图(2)中图3.
6. group by 数据倾斜问题处理.
  如果已经产生数据倾斜了,如何解决它?
  手动开启负载均衡, 它的底层会开启两个MR任务, 第1个MR负责随机打散数据. 第2个MR: 会将第1个MR的ReduceTask当做MapTask, 来拉取, 合并, 处理数据, 获取最终结果.
  
  见图(2)中图4.
7. MapReduce的并行度, 即: 我们开几个MapTask任务, 开几个ReduceTask任务.
  MapTask任务的个数 = Block数据块的个数.
  小文件过多: 我们可以对小文件做局部合并.
  文件过大,或者体量大: 增大Block块的大小(hadoop1.X: 64MB, Hadoop2.X开始: 128MB), 假设Block块的个数从 100 => 50, 那我们MapTask的个数也就从 100 => 50
  ReduceTask任务的个数 = 手动设置(一般是和分区的个数相关)
  回想咱们之前讲解分桶查询的时候, 如果有几个桶, 我们要先设置ReduceTask的个数, 即: set mapreduce.job.reduces = 3;
  细节: 每个任务最大的 reduce 数，默认为 1009, 每个Reduce任务能处理的最大数据量是 256MB.
8. 执行计划, explain, 它可以查看 HiveSQL的执行计划, 即: 具体的执行流程.
  explain + HiveSQL语句即可.
9. 并行执行机制.
  如果大任务拆分成小任务之后, 小任务之间的依赖度比较低, 可以让它们同时执行(并行执行), 可以提高效率.
10. Hive的严格模式
  你一定要把这个严格模式和 DQL动态分区中的那个严格模式区分开.
  这里的严格模式, 写法是: set hive.mapred.mode = strict, 目的是: 禁用某些效率低的SQL语句,
  要求:
  1. 如果是分区表，没有where进行分区裁剪禁止执行
  2、order by语句必须 + limit限制
  
  我们之前学的动态分区的严格模式, 写法是: set hive.exec.dynamic.partition.mode = nonstrict;, 目的是: 在我们往分区表中添加数据的时候, 可以不指定静态分区,
  因为严格模式要求: 动态分区的时候, 必须手动指定至少1个静态分区. partition(year) 动态分区, partition(year='2022') 静态分区.
11. 推测执行机制
  MR程序是可以多任务执行的, 当某个任务执行太慢的时候, 影响整体MR程序执行的进度, 如果开启了推测执行机制, 此时MR程序会再开一个任务, 和那个慢的任务做同样的事儿, 谁先执行完, 用谁的结果.
  例如: MR程序有: MapTask1, MapTask2, MapTask3, 此时MapTask2执行的很慢, Hive会开启一个新的MapTask, 例如: MapTask5, 它和MapTask2做的事儿是一样的, 谁先执行完, 用谁的结果.
  实际开发中, 推测执行一般是关闭的.
总结: (以下的话仅代表我的个人观点), 很多的大数据软件的调优都可以总结为如下的4点:
1. 改硬件.
2. 增大某些设置. 例如: Block块, MapTask数, Reduce数量等...
3. 禁用(或调小)某些设置. 例如: 推测执行等.
4. 减少IO传输. 例如: 数据压缩, 数据存储, join优化.
上述的4点是辅助你理解的, 并不是回答面试题的"标准"答案.企业还是喜欢听前11个的具体内容.
*/