hive知识点总结
一、hive简介
1、hive是基于hadoop的数据仓库工具，可以将结构化数据文件映射为一张表，提供类sql查询功能
2、hadoop数据存储在hdfs，计算引擎mr，切换方式set hive.execution.engine=mr
3、hive不是数据库，hql执行运行在yarn，hive元数据：Metastore
4、hive元数据包括：表名、表所属数据库、表拥有者、列/分区字段、表类型、表所在目录等，默认存储在自带的derby数据库，推荐存储在mysql
二、hive架构
1、Hive架构
2、一条hql的执行过程
a、解析器：将sql转换成抽象语法树ast
b、编译器：将ast编译成逻辑执行计划
c、优化器：对逻辑执行计划优化
d、执行器：将逻辑计划转换成物理计划，有hive/mr引擎
三、Mapreduce原理
1、MapReduce核心思想
a、分布式的运算程序往往需要分成至少2个阶段。
b、第一个阶段的MapTask并发实例，完全并行运行，互不相干。
c、第二个阶段的ReduceTask并发实例互不相干，但是他们的数据依赖于上一个阶段的所有MapTask并发实例的输出。
d、MapReduce编程模型只能包含一个Map阶段和一个Reduce阶段，如果用户的业务逻辑非常复杂，那就只能多个MapReduce程序，串行运行。
注意：切片是逻辑切片 默认大小=块大小
2、MR流程
3、MapReduce进程
一个完整的MapReduce程序在分布式运行时有三类实例进程：
a、MrAppMaster：负责整个程序的过程调度及状态协调。 一个
b、MapTask：负责Map阶段的整个数据处理流程。多个
c、ReduceTask：负责Reduce阶段的整个数据处理流程。 0~多个
四、hive和数据库比较
1、写时模式和读时模式
数据库是写时模式，可以对列建立索引，查找数据快；
Hive是读时模式，load data快，不需要读取数据进行解析，仅用于文件的复制和移动
2、数据更新
由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的
3、执行延迟
由于MapReduce 本身具有较高的延迟，因此在利用MapReduce 执行Hive查询时，也会有较高的延迟。 Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高
4、执行规模
由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。
五、数据增删改查
1、ddl(增删改查)：create、show、drop、external（外部表）
2、dml（数据导入导出）：load data，insert overwrite，export，location
3、查询：执行顺序
1st) FROM字句:执行顺序为从后往前、从右到左。数据量较大的表尽量放在后面。
2nd) WHERE字句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE字句的最右。
3rd) GROUP BY:执行顺序从右往左分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉
4th) HAVING字句：消耗资源。尽量避免使用，HAVING会在检索出所有记录之后才对结果进行过滤，需要排序等操作。
5th) SELECT字句：少用*号，尽量使用字段名称，oracle在解析的过程中，通过查询数据字典将号依次转换成所有列名，消耗时间。
6th) ORDER BY字句：执行顺序从左到右，消耗资源
多表关联eg:
SELECT e.ename, d.dname, l.loc_name
FROM emp e
JOIN dept d
ON d.deptno = e.deptno
JOIN location l
ON d.loc = l.loc;
大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l进行连接操作。
优化：当对3个或者更多表进行join连接时，**如果每个on子句都使用相同的连接键的话，**那么只会产生一个MapReduce job。
六、Hive函数
快捷命令
show functions：显示当前回话可用函数
desc function +函数名：查看函数用法
desc+表名：查看表名以及表字段
1、简单函数
a、数学函数
round(2.1，n):返回四舍五入值，n未小数位
floor(2.11)：向下取整函数， 如：6.10->6 -3.4->-4
ceil(2.11)：向上取整函数，ceil(6) = ceil(6.1)= ceil(6.9) = 6
rand(),rand(n):随机取数
exp(n):返回e的n幂次方， n可为小数 ，如：select exp(2)
ln(a)，log10(a),log2(a)：以自然数为底的对数，ln(a),a可为小数
log(b,a):以b为底a的对数，如log(2,4)=2
pow(a,b):计算a的b次幂
sqrt(a):计算a的平方根
bin(a):计算二进制a的string类型，a为bigint 类型bin(BIGINT a)
hex(a)：返回值：STRING，计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制 hex(BIGINT a) hex(STRING a) hex(BINARY a)
unhex(str a)：返回值：BINARY
其他：abs取绝对值，三角函数：sin，cos；弧度转换角度：degrees(),角度转换弧度：radians(30)
b、类型转换函数
binary：将输入的值转换成二进制 binary(string|binary)
cast：将expr转换成type类型 如：cast(“1” as BIGINT) 将字符串1转换成了BIGINT类型
c、日期函数
unix_timestamp():显示当前时间戳
unix_timestamp(string date):unix_timestamp(‘yyyy-MM-dd HH:mm:ss’),默认支持转换格式为’yyyy-MM-dd HH:mm:ss’)
unix_timestamp(string date，string pattern):unix_timestamp(‘20111207 13:01:03’,‘yyyyMMdd HH:mm:ss’),自定义格式日期转换为时间戳
from_unixtime(时间戳,‘yyyyMMdd’)：转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式（使用日期函数需要注意当前环境使用的时区），
返回值：string，将时间戳转换为指定格式的日期
例：select from_unixtime(1630307002,'yyyyMMdd');
>>20210830to_date: to_date(string timestamp),返回日期时间字段中的日期部分,返回值为string
year()、month()、day()、hour()、second()：返回日期中的年月日时分秒，返回值为int
weekofyear():返回日期在当前的周数，返回值为int
datediff():datediff(string enddate, string startdate),返回结束日期减去开始日期的天数,返回值为int,日期输入默认格式：yyyy-MM-dd
date_add():返回开始日期startdate增加days天后的日期,date_add(string startdate, int days),输入默认格式：yyyy-MM-dd
date_sub():返回开始日期startdate减少days天后的日期,date_sub (string startdate, int days),输入默认格式：yyyy-MM-dd
last_day(): 返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss），last_day(string date)
next_day():返回当前时间的下一个星期X所对应的日期 如：next_day(‘2015-01-14’, ‘TU’) = 2015-01-20 以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20
quarter:返回当前时间属性哪个季度 ,quarter(date/timestamp/string)
select quarter('2021-08-30'); >>3current_date():返回当前日期，格式为date
current_timestamp:返回当前时间戳，格式为timestamp
add_months():返回当前时间下再增加num_months个月的日期 add_months(string start_date, int num_months),返回值string
d、条件函数
NVL()：如果value值为NULL就返回default_value,否则返回value，nvl(T value, T default_value)
COALESCE():返回第一非null的值，如果全部都为NULL就返回NULL,如COALESCE (NULL,44,55)=44
case when：CASE value WHEN 条件 THEN value1 ELSE value2 END.
isnull(a),isnotnull(a):如果a为null就返回true，否则返回false
concat():对二进制字节码或字符串按次序进行拼接，select congcat（1，2）>>12
concat_ws():concat_ws(string SEP, string A, string B…)，使用指定分隔符拼接,
select concat_ws('--','1','2');>>1--2encode():使用指定的字符集将字符串编码成二进制，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任一输入参数为NULL都将返回NULL
find_in_set(): find_in_set(string str, string strList) , 返回str在strlist第一次出现
select find_in_set('Teacher', remark);get_json_object():
get_json_object(string json_string, string path)
说明：
第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL。
每次只能返回一个数据项。
举例：
data 为 test表中的字段，数据结构如下：
data =
{
"store":
{
"fruit":[{"weight":8,"type":"apple"}, {"weight":9,"type":"pear"}],
"bicycle":{"price":19.95,"color":"red"}
},
"email":"amy@only_for_json_udf_test.net",
"owner":"amy"
}get单层值
hive> select get_json_object(data, '$.owner') from test;
结果：amyget多层值
hive> select get_json_object(data, '$.store.bicycle.price') from test;
结果：19.95get数组
hive> select get_json_object(data, '$.store.fruit[0]') from test;
结果：{"weight":8,"type":"apple"}instr(): 查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的,
例如：select instr('123str','str')>>4length():计算字符串长度
lower():将字符串A的所有字母转换成小写字母
upper():将字符串A中的字母转换成大写字母
regexp_extract():regexp_extract(string subject, string pattern, int index),抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格
regexp_replace():regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT),按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉 如：regexp_replace(“foobar”, “oo|ar”, “”) = ‘fb.’ 注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格
repeat():repeat(string str, int n)输出字符串
例：`select repeat('str',5)；>>'strstrstrstrstr'`lpad():从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分,lpad(string str, int len, string pad)，selectlpad(‘str’,6,‘a’)>>‘straaa’`
rpad():从右边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分,`
select rpad(string str, int len, string pad)`；ltrim()，rtrim():去掉字符串前(后)面的字符串，
例：`select trim(' str')>>str`
例：`select rtrim('str ')>>st`split():按照正则表达式pat来分割字符串str,并将分割后的数组字符串的形式返回,
例：select split('s-t-r','-')>>['s','t','r']；substr():对于字符串A,从start位置开始截取字符串并返回,substr(string|binary A, int start) ,substring(string|binary A, int start)
例：select substr('str1235',2,2)；>>'tr'substring_index()：substring_index（“待截取有用部分的字符串”，“截取数据依据的字符”，截取字符的位置N）,例：SELECT SUBSTRING_INDEX(‘15-151-152-16’,’-’,1); ==>得到结果为： 15
translate():字符串替换，translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)
例：`SELECT translate('string','str','bb')；>>'bbing'`initcap():将字符串A转换第一个字母大写其余字母的字符串小写，
例：SELECT initcap('sStrDDDDing')；>>'Sstrdddding'space():space(n),返回长度为n的空字符串,select length(space(5))>>5 reverse():reverse(string A),返回字符串A的反转结果 ，
例：select reverse('abcedfg');>>'gfdecba'explode():行转列，将输入的一行数组或者map转换成列输出,explode(array (or map))
select explode(split(concat_ws('-','1','2','3','4','5','6','7','8','9'),'-'));
select split(concat_ws('-','1','2','3','4','5','6','7','8','9'),'-')>>["1","2","3","4","5","6","7","8","9"]`;2 、集合函数
集合查找函数: find_in_set
语法: find_in_set(string str, string strList)
返回值: int
说明: 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0
举例： select find_in_set('ab','ef,ab,de');>>2hive提供了复合数据类型：
Structs： structs内部的数据可以通过DOT（.）来存取，例如，表中一列c的类型为STRUCT{a INT; b INT}，我们可以通过c.a来访问域a
Maps（K-V对）：访问指定域可以通过[“指定域名称”]进行，例如，一个Map M包含了一个group-》gid的kv对，gid的值可以通过M[’ group’]来获取
Arrays：array中的数据为相同类型，例如，假如array A中元素[‘a’,‘b’,‘c’]，则A[1]的值为’b’
3、聚合函数
sum(),count(),avg(),min(),max()等
4、特殊函数
开窗函数(重要)
普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING表示到后面的终点
开窗函数一般分为两类,聚合开窗函数和排序开窗函数。
测试数据
create table eo_hadoop_dw_tmp.student_scores(
id int,
studentId int,
language int,
math int,
english int,
classId string,
departmentId string
);
insert into table student_scores values
(1,111,68,69,90,'class1','department1'),
(2,112,73,80,96,'class1','department1'),
(3,113,90,74,75,'class1','department1'),
(4,114,89,94,93,'class1','department1'),
(5,115,99,93,89,'class1','department1'),
(6,121,96,74,79,'class2','department1'),
(7,122,89,86,85,'class2','department1'),
(8,123,70,78,61,'class2','department1'),
(9,124,76,70,76,'class2','department1'),
(10,211,89,93,60,'class1','department2'),
(11,212,76,83,75,'class1','department2'),
(12,213,71,94,90,'class1','department2'),
(13,214,94,94,66,'class1','department2'),
(14,215,84,82,73,'class1','department2'),
(15,216,85,74,93,'class1','department2'),
(16,221,77,99,61,'class2','department2'),
(17,222,80,78,96,'class2','department2'),
(18,223,79,74,96,'class2','department2'),
(19,224,75,80,78,'class2','department2'),
(20,225,82,85,63,'class2','department2');count开窗函数
-- count 开窗函数
select studentId,math,departmentId,classId,
-- 以符合条件的所有行作为窗口
count(math) over() as count1,
-- 以按classId分组的所有行作为窗口
count(math) over(partition by classId) as count2,
-- 以按classId分组、按math排序的所有行作为窗口
count(math) over(partition by classId order by math) as count3,
-- 以按classId分组、按math排序、按 当前行+往前1行+往后2行的行作为窗口
count(math) over(partition by classId order by math rows between 1 preceding and 2 following) as count4
from student_scores where departmentId='department1';
结果
studentid math departmentid classid count1 count2 count3 count4
111 69 department1 class1 9 5 1 3
113 74 department1 class1 9 5 2 4
112 80 department1 class1 9 5 3 4
115 93 department1 class1 9 5 4 3
114 94 department1 class1 9 5 5 2
124 70 department1 class2 9 4 1 3
121 74 department1 class2 9 4 2 4
123 78 department1 class2 9 4 3 3
122 86 department1 class2 9 4 4 2
结果解释:
studentid=115,count1为所有的行数9,count2为分区class1中的行数5,count3为分区class1中math值<=93的行数4,
count4为分区class1中math值向前+1行向后+2行(实际只有1行)的总行数3。sum开窗函数
-- sum开窗函数
select studentId,math,departmentId,classId,
-- 以符合条件的所有行作为窗口
sum(math) over() as sum1,
-- 以按classId分组的所有行作为窗口
sum(math) over(partition by classId) as sum2,
-- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口
sum(math) over(partition by classId order by math) as sum3,
-- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口
sum(math) over(partition by classId order by math rows between 1 preceding and 2 following) as sum4
from student_scores where departmentId='department1';
结果
studentid math departmentid classid sum1 sum2 sum3 sum4
111 69 department1 class1 718 410 69 223
113 74 department1 class1 718 410 143 316
112 80 department1 class1 718 410 223 341
115 93 department1 class1 718 410 316 267
114 94 department1 class1 718 410 410 187
124 70 department1 class2 718 308 70 222
121 74 department1 class2 718 308 144 308
123 78 department1 class2 718 308 222 238
122 86 department1 class2 718 308 308 164min开窗函数
-- min 开窗函数
select studentId,math,departmentId,classId,
-- 以符合条件的所有行作为窗口
min(math) over() as min1,
-- 以按classId分组的所有行作为窗口
min(math) over(partition by classId) as min2,
-- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口
min(math) over(partition by classId order by math) as min3,
-- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口
min(math) over(partition by classId order by math rows between 1 preceding and 2 following) as min4
from student_scores where departmentId='department1';
结果
studentid math departmentid classid min1 min2 min3 min4
111 69 department1 class1 69 69 69 69
113 74 department1 class1 69 69 69 69
112 80 department1 class1 69 69 69 74
115 93 department1 class1 69 69 69 80
114 94 department1 class1 69 69 69 93
124 70 department1 class2 69 70 70 70
121 74 department1 class2 69 70 70 70
123 78 department1 class2 69 70 70 74
122 86 department1 class2 69 70 70 78
结果解释:
同count开窗函数first_value开窗函数
-- first_value 开窗函数
select studentId,math,departmentId,classId,
-- 以符合条件的所有行作为窗口
first_value(math) over() as first_value1,
-- 以按classId分组的所有行作为窗口
first_value(math) over(partition by classId) as first_value2,
-- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口
first_value(math) over(partition by classId order by math) as first_value3,
-- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口
first_value(math) over(partition by classId order by math rows between 1 preceding and 2 following) as first_value4
from student_scores where departmentId='department1';
结果
studentid math departmentid classid first_value1 first_value2 first_value3 first_value4
111 69 department1 class1 69 69 69 69
113 74 department1 class1 69 69 69 69
112 80 department1 class1 69 69 69 74
115 93 department1 class1 69 69 69 80
114 94 department1 class1 69 69 69 93
124 70 department1 class2 69 74 70 70
121 74 department1 class2 69 74 70 70
123 78 department1 class2 69 74 70 74
122 86 department1 class2 69 74 70 78
结果解释:
studentid=124 first_value1:第一个值是69,first_value2:classId=class1分区 math的第一个值是69。lag开窗函数(lead和lag类似) lag(col,n,default) 用于统计窗口内往上第n个值。
col:列名
n:往上第n行
default:往上第n行为NULL时候，取默认值,不指定则取NULL
-- lag 开窗函数
select studentId,math,departmentId,classId,
--窗口内 往上取第二个 取不到时赋默认值60
lag(math,2,60) over(partition by classId order by math) as lag1,
--窗口内 往上取第二个 取不到时赋默认值NULL
lag(math,2) over(partition by classId order by math) as lag2
from student_scores where departmentId='department1';
结果
studentid math departmentid classid lag1 lag2
111 69 department1 class1 60 NULL
113 74 department1 class1 60 NULL
112 80 department1 class1 69 69
115 93 department1 class1 74 74
114 94 department1 class1 80 80
124 70 department1 class2 60 NULL
121 74 department1 class2 60 NULL
123 78 department1 class2 70 70
122 86 department1 class2 74 74
结果解释:
第3行 lag1:窗口内(69 74 80) 当前行80 向上取第二个值为69
倒数第3行 lag2:窗口内(70 74) 当前行74 向上取第二个值为NULLcume_dist开窗函数
计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：
小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。
-- cume_dist 开窗函数
select studentId,math,departmentId,classId,
-- 统计小于等于当前分数的人数占总人数的比例
cume_dist() over(order by math) as cume_dist1,
-- 统计大于等于当前分数的人数占总人数的比例
cume_dist() over(order by math desc) as cume_dist2,
-- 统计分区内小于等于当前分数的人数占总人数的比例
cume_dist() over(partition by classId order by math) as cume_dist3
from student_scores where departmentId='department1';
结果
studentid math departmentid classid cume_dist1 cume_dist2 cume_dist3
111 69 department1 class1 0.1111111111111111 1.0 0.2
113 74 department1 class1 0.4444444444444444 0.7777777777777778 0.4
112 80 department1 class1 0.6666666666666666 0.4444444444444444 0.6
115 93 department1 class1 0.8888888888888888 0.2222222222222222 0.8
114 94 department1 class1 1.0 0.1111111111111111 1.0
124 70 department1 class2 0.2222222222222222 0.8888888888888888 0.25
121 74 department1 class2 0.4444444444444444 0.7777777777777778 0.5
123 78 department1 class2 0.5555555555555556 0.5555555555555556 0.75
122 86 department1 class2 0.7777777777777778 0.3333333333333333 1.0
结果解释:
第三行:
cume_dist1=小于等于80的人数为6/总人数9=0.6666666666666666
cume_dist2=大于等于80的人数为4/总人数9=0.4444444444444444
cume_dist3=分区内小于等于80的人数为3/分区内总人数5=0.6rank开窗函数
rank 开窗函数基于 over 子句中的 order by 确定一组值中一个值的排名。如果存在partition by ,则为每个分区组中的每个值排名。排名可能不是连续的。例如，如果两个行的排名为 1，则下一个排名为 3。
-- rank 开窗函数
select *,
-- 对全部学生按数学分数排序
rank() over(order by math) as rank1,
-- 对院系 按数学分数排序
rank() over(partition by departmentId order by math) as rank2,
-- 对每个院系每个班级 按数学分数排序
rank() over(partition by departmentId,classId order by math) as rank3
from student_scores;
结果
id studentid language math english classid departmentid rank1 rank2 rank3
1 111 68 69 90 class1 department1 1 1 1
3 113 90 74 75 class1 department1 3 3 2
2 112 73 80 96 class1 department1 9 6 3
5 115 99 93 89 class1 department1 15 8 4
4 114 89 94 93 class1 department1 17 9 5
9 124 76 70 76 class2 department1 2 2 1
6 121 96 74 79 class2 department1 3 3 2
8 123 70 78 61 class2 department1 7 5 3
7 122 89 86 85 class2 department1 14 7 4
15 216 85 74 93 class1 department2 3 1 1
14 215 84 82 73 class1 department2 11 5 2
11 212 76 83 75 class1 department2 12 6 3
10 211 89 93 60 class1 department2 15 8 4
12 213 71 94 90 class1 department2 17 9 5
13 214 94 94 66 class1 department2 17 9 5
18 223 79 74 96 class2 department2 3 1 1
17 222 80 78 96 class2 department2 7 3 2
19 224 75 80 78 class2 department2 9 4 3
20 225 82 85 63 class2 department2 13 7 4
16 221 77 99 61 class2 department2 20 11 5dense_rank开窗函数
dense_rank与rank有一点不同,当排名一样的时候,接下来的行是连续的。如两个行的排名为 1，则下一个排名为 2。
-- dense_rank 开窗函数
select *,
-- 对全部学生按数学分数排序
dense_rank() over(order by math) as dense_rank1,
-- 对院系 按数学分数排序
dense_rank() over(partition by departmentId order by math) as dense_rank2,
-- 对每个院系每个班级 按数学分数排序
dense_rank() over(partition by departmentId,classId order by math) as dense_rank3
from student_scores;
结果:
id studentid language math english classid departmentid dense_rank1 dense_rank2 dense_rank3
1 111 68 69 90 class1 department1 1 1 1
3 113 90 74 75 class1 department1 3 3 2
2 112 73 80 96 class1 department1 5 5 3
5 115 99 93 89 class1 department1 10 7 4
4 114 89 94 93 class1 department1 11 8 5
9 124 76 70 76 class2 department1 2 2 1
6 121 96 74 79 class2 department1 3 3 2
8 123 70 78 61 class2 department1 4 4 3
7 122 89 86 85 class2 department1 9 6 4
15 216 85 74 93 class1 department2 3 1 1
14 215 84 82 73 class1 department2 6 4 2
11 212 76 83 75 class1 department2 7 5 3
10 211 89 93 60 class1 department2 10 7 4
12 213 71 94 90 class1 department2 11 8 5
13 214 94 94 66 class1 department2 11 8 5
18 223 79 74 96 class2 department2 3 1 1
17 222 80 78 96 class2 department2 4 2 2
19 224 75 80 78 class2 department2 5 3 3
20 225 82 85 63 class2 department2 8 6 4
16 221 77 99 61 class2 department2 12 9 5ntile开窗函数
将分区中已排序的行划分为大小尽可能相等的指定数量的排名的组，并返回给定行所在的组的排名。
-- ntile 开窗函数
select *,
-- 对分区内的数据分成两组
ntile(2) over(partition by departmentid order by math) as ntile1,
-- 对分区内的数据分成三组
ntile(3) over(partition by departmentid order by math) as ntile2
from student_scores;
结果
id studentid language math english classid departmentid ntile1 ntile2
1 111 68 69 90 class1 department1 1 1
9 124 76 70 76 class2 department1 1 1
6 121 96 74 79 class2 department1 1 1
3 113 90 74 75 class1 department1 1 2
8 123 70 78 61 class2 department1 1 2
2 112 73 80 96 class1 department1 2 2
7 122 89 86 85 class2 department1 2 3
5 115 99 93 89 class1 department1 2 3
4 114 89 94 93 class1 department1 2 3
18 223 79 74 96 class2 department2 1 1
15 216 85 74 93 class1 department2 1 1
17 222 80 78 96 class2 department2 1 1
19 224 75 80 78 class2 department2 1 1
14 215 84 82 73 class1 department2 1 2
11 212 76 83 75 class1 department2 1 2
20 225 82 85 63 class2 department2 2 2
10 211 89 93 60 class1 department2 2 2
12 213 71 94 90 class1 department2 2 3
13 214 94 94 66 class1 department2 2 3
16 221 77 99 61 class2 department2 2 3
结果解释:
第8行
ntile1:对分区的数据均匀分成2组后，当前行的组排名为2
ntile2:对分区的数据均匀分成3组后，当前行的组排名为3row_number开窗函数
从1开始对分区内数据进行排序
-- row_number 开窗函数
select studentid,departmentid,classid,math,
-- 对分区departmentid,classid内的数据按math排序
row_number() over(partition by departmentid,classid order by math) as row_number
from student_scores;
结果
studentid departmentid classid math row_number
111 department1 class1 69 1
113 department1 class1 74 2
112 department1 class1 80 3
115 department1 class1 93 4
114 department1 class1 94 5
124 department1 class2 70 1
121 department1 class2 74 2
123 department1 class2 78 3
122 department1 class2 86 4
216 department2 class1 74 1
215 department2 class1 82 2
212 department2 class1 83 3
211 department2 class1 93 4
213 department2 class1 94 5
214 department2 class1 94 6
223 department2 class2 74 1
222 department2 class2 78 2
224 department2 class2 80 3
225 department2 class2 85 4
221 department2 class2 99 5
结果解释:
同一分区,相同值，不同序。如studentid=213 studentid=214 值都为94 排序为5,6。percent_rank开窗函数
计算给定行的百分比排名。可以用来计算超过了百分之多少的人。如360小助手开机速度超过了百分之多少的人。
(当前行的rank值-1)/(分组内的总行数-1)
-- percent_rank 开窗函数
select studentid,departmentid,classid,math,
row_number() over(partition by departmentid,classid order by math) as row_number,
percent_rank() over(partition by departmentid,classid order by math) as percent_rank
from student_scores;
结果
studentid departmentid classid math row_number percent_rank
111 department1 class1 69 1 0.0
113 department1 class1 74 2 0.25
112 department1 class1 80 3 0.5
115 department1 class1 93 4 0.75
114 department1 class1 94 5 1.0
124 department1 class2 70 1 0.0
121 department1 class2 74 2 0.3333333333333333
123 department1 class2 78 3 0.6666666666666666
122 department1 class2 86 4 1.0
216 department2 class1 74 1 0.0
215 department2 class1 82 2 0.2
212 department2 class1 83 3 0.4
211 department2 class1 93 4 0.6
213 department2 class1 94 5 0.8
214 department2 class1 94 6 0.8
223 department2 class2 74 1 0.0
222 department2 class2 78 2 0.25
224 department2 class2 80 3 0.5
225 department2 class2 85 4 0.75
221 department2 class2 99 5 1.0
结果解释:
studentid=115,percent_rank=(4-1)/(5-1)=0.75
studentid=123,percent_rank=(3-1)/(4-1)=0.6666666666666666
原文链接：https://blog.csdn.net/wangpei1949/article/details/814375745、自定义函数
UDTF，多行转换：lateral view
说明：lateral view用于和json_tuple，parse_url_tuple，split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合
七、数据查询优化
1、小表关联大表
Hive假定查询中最后的一个表是大表。它会将其它表缓存起来，然后扫描最后那个表。因此通常需要将小表放前面，或者标记哪张表是大表
2、使用相同的连接键
当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。
3、尽量尽早地过滤数据
减少每个阶段的数据量,对于分区表要加分区，同时只选择需要使用到的字段。
4、尽量原子化操作
尽量避免一个SQL包含复杂逻辑，可以使用中间表来完成复杂的逻辑。
5、用insert into替换union all
如果union all的部分个数大于2，或者每个union部分数据量大，应该拆成多个insert into 语句，实际测试过程中，执行时间能提升50%。
6、排序功能选择
order by 全局排序，不建议使用，只有一个reduce，效率太低
cluster by 全局降序排列，建议使用
sort by ：reduce内部排序，单个使用意义不大
distribute by 分区排序，保证多个reduce是有序的
可以结合distribute by和sort by 使用进行排序
例：select a,b,c from t distribute by a sort by a asc, b desc7、limit 语句快速出结果
一般情况下，Limit语句还是需要执行整个查询语句，然后再返回部分结果。
有一个配置属性可以开启，避免这种情况—对数据源进行抽样
hive.limit.optimize.enable=true — 开启对数据源进行采样的功能
hive.limit.row.max.size — 设置最小的采样容量
hive.limit.optimize.limit.file — 设置最大的采样样本数
缺点：有可能部分数据永远不会被处理到
8、本地模式
对于小数据集，为查询触发执行任务消耗的时间>实际执行job的时间，因此可以通过本地模式，在单台机器上（或某些时候在单个进程上）处理所有的任务。
set oldjobtracker=${hiveconf:mapred.job.tracker};
set mapred.job.tracker=local;
set marped.tmp.dir=/home/edward/tmp;
set mapred.job.tracker=${oldjobtracker};– 可以通过设置属性hive.exec.mode.local.auto的值为true，来让hve在适当的时候自动启动这个优化，也可以将这个配置写在$HOME/.hiverc文件中。
– 当一个job满足如下条件才能真正使用本地模式：
1.job的输入数据大小必须小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)
2.job的map数必须小于参数：hive.exec.mode.local.auto.tasks.max(默认4)
3.job的reduce数必须为0或者1
可用参数hive.mapred.local.mem(默认0)控制child jvm使用的最大内存数。
9、 并行执行
hive会将一个查询转化为一个或多个阶段，包括：MapReduce阶段、抽样阶段、合并阶段、limit阶段等。默认情况下，一次只执行一个阶段。 不过，如果某些阶段不是互相依赖，是可以并行执行的。
set hive.exec.parallel=true,可以开启并发执行。
set hive.exec.parallel.thread.number=16;同一个sql允许最大并行度，默认为8。会比较耗系统资源。
调整mapper和reducer的个数
10、Map阶段优化
map个数的主要的决定因素有： input的文件总个数，input的文件大小，集群设置的文件块大小（默认128M，不可自定义）。
举例：
a) 假设input目录下有1个文件a,大小为780M,那么hadoop会将该文件a分隔成7个块（6个128m的块和1个12m的块），从而产生7个map数
b) 假设input目录下有3个文件a,b,c,大小分别为10m，20m，130m，那么hadoop会分隔成4个块（10m,20m,128m,2m）,从而产生4个map数
即，如果文件大于块大小(128m),那么会拆分，如果小于块大小，则把该文件当成一个块。
map执行时间：map任务启动和初始化的时间+逻辑处理的时间。
1）减少map数
若有大量小文件（小于128M），会产生多个map，处理方法是：
set mapred.max.split.size=100000000;
set mapred.min.split.size.per.node=100000000;
set mapred.min.split.size.per.rack=100000000;– 前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的）进行合并
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;– 执行前进行小文件合并 2）增加map数，当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。
set mapred.reduce.tasks=?2 Reduce阶段优化
调整方式：
set mapred.reduce.tasks=?
set hive.exec.reducers.bytes.per.reducer = ?一般根据输入文件的总大小,用它的estimation函数来自动计算reduce的个数：reduce个数 = InputFileSize / bytes per reducer
11.严格模式
set hive.marped.mode=strict ------ 防止用户执行那些可能意想不到的不好的影响的查询
– 分区表，必须选定分区范围
– 对于使用order by的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个reducer中进行处理。
– 限制笛卡尔积查询：两张表join时必须有on语句
12.数据倾斜
表现：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。
单一reduce的记录数与平均记录数差异过大，通常可能达到3倍甚至更多。 最长时长远大于平均时长。
原因：
1)、key分布不均匀
2)、业务数据本身的特性
3)、建表时考虑不周
4)、某些SQL语句本身就有数据倾斜
关键词情形后果join其中一个表较小，但是key集中分发到某一个或几个Reduce上的数据远高于平均值join大表与大表，但是分桶的判断字段0值或空值过多这些空值都由一个reduce处理，灰常慢group bygroup by 维度过小，某值的数量过多处理某值的reduce灰常耗时count distinct某特殊值过多处理此特殊值reduce耗时
