Hive详解(超详细)

  Hive 是基于Hadoop的 一个数据仓库工具，将结构化的数据文件映射为一张表，并提供了sql的curd的功能。

    同时，hive的本质是将HQL转换为MapReduce程序，在hadoop中进行伪数据库的操作。(数据存储在HDFS/计算在MapReduce/执行在Yarn)

---

一、数据类型

1. 基本数据类型

--1. 数字类型
* 整型
>> - tinyint
>> - smallint
>> - int
>> - bigint
>>
* 布尔型
>> - boolean
>>
* 浮点型
>> - float
>> - double
>> - deicimal
> 

--2. 时间类型
> timestamp
> date

--3. 字符类型
> string
> varchar
> char
> binary (字节数组)

2. 集合数据类型

--array
eg: array<string>

--map
eg: map<string, int>

--struct
eg: struct<street:string, city:string>

--union
--在有限取值范围内的一个值
eg: uniontype<int,double,array<string>,struct<a:int,b:string>>

二、运算部分

1. 数据类型转换

1° 隐式转换

--1
任何整数类型都可以隐式地转化为一个范围更广的类型
--2
所有整数类型/float/string类型都可以隐式转换为double
--3
tinyint/smallint/int都可以转化为float
--4
boolean类型不可以转换为其他任何类型

2° 手动操作

cast(value as type)

使用cast操作进行数据类型转换，如果强制转换失败，表达式返回null。  

--1. 浮点型转换为整型
该过程的内部操作是通过round()或者floor()函数来实现的，而不是cast

--2. date类型的数据，只能在date/timestamp/string之前转换
cast(date as date)      --返回date类型
cast(timestamp as date) --timestamp是依赖时区的
cast(string as date)    --string必须为yyyy-mm-dd的格式
cast(date as timestamp)
cast(date as string)    --date转换为yyyy-mm-dd的字符串

--3. cast可嵌套使用
select (cast(cast(col as string) as double)) from tablename;

2. 关系运算符

=               --等号
<>              --不等
< <=            --小于(等于)
> >=            --大于(等于)
is (not) null   --(不)为空

--模糊关系
(not) A like B            --B中占位符：_(一位数) %(不确定位数)
rlike           --可使用java中的任意正则表达式(eg: rlike '^\\d+$' --是否全为数字)
regexp          --功能与rlike相同

3. 算术运算符

+
-
*
/
%       --取余
&       --按位与
|       --按位或
^       --按位异或
~       --按位非

4. 逻辑运算符

A and B     --如果A和B都是true,否则false
&&          --类似于A and B
or          --如果A或B或两者都是true 返回true,否则false
||          --类似A or B
not
!           --!A, 类似于 not A

5. 集合运算

union       --并集，有去重效果
union all   --并集，不去重

--hive中不支持表的减法运算，我们一般通过(not) in 来实现差集/补集/交集...
--差集
set hive.mapred.mode=nonstrict;
set hive.strict.checks.cartesian.product = false; ###设置非严格模式
##求product_1中特有的
 select * 
  from hive_4_product_1
 where product_id not in (select product_id from hive_4_product_2);

--对称差
SELECT * 
  FROM hive_4_product_1
 WHERE product_id NOT IN (SELECT product_id FROM hive_4_product_2)
UNION
SELECT * 
  FROM hive_4_product_2
 WHERE product_id NOT IN (SELECT product_id FROM hive_4_product_1)

--交集
 SELECT * 
  FROM hive_4_product_1
 WHERE product_id IN (SELECT product_id FROM hive_4_product_2)

6. 复杂的运算符

运算符	操作	描述
A[n]	A是一个数组，n是一个int	它返回数组A的第n个元素，第一个元素的索引0。
M[key]	M 是一个 Map<K, V> 并 key 的类型为K	它返回对应于映射中关键字的值。
S.x	S 是一个结构	它返回S的s字段

三、常用shell命令

--启动hive
$ bin/hive

--启动元数据服务
$ hive --service metastore

--查看数据库
show database;

--打开默认数据库
use default;

--退出hive
quit;
exit;

--显示default数据库中的表
show tables;

--查看表结构
desc tablename;

--不进入hive，执行sql语句
$ hive -e "select * from tablename";

--执行文件中的sql语句
$ hive -f ./hive.sql

--执行文件中的sql语句并将结果写入到文件中
$ hive -f ./hive.sql > ./hive_data.txt

--查看hdfs文件系统
hive> dfs -ls /;

--查看当前所有的配置信息
set;

--常用交互命令查看
hive -help

--查看hive中执行过的所有历史命令
cat .hivehistory

四、常见配置文件及参数设置

1. 常见配置文件

--启动文件(./hive/bin/)

--配置文件(./hive/conf/)
hive-default.xml    --默认配置文件
hive-site.xml       --用户自定义配置文件(自定义文件会覆盖默认的)

--日志文件(./hive/logs/)

2. 常见参数设置

--查看参数
set;
set -v;
--设置参数
set key = value;

set mapred.reduce.tasks     --设置reduce个数(默认-1)
mapred.map.tasks        --设置提交Job的map端个数
set hive.exec.dynamic.partition=true;-- 开启动态分区功能，默认：false
set hive.exec.dynamic.partition.mode=nonstrict;-- 对动态分区模式不做限制（strict模式必须指定一个分区字段（有多个分区字段）为静态分区） 
set hive.exec.parallel=true;    --是否开启 map/reduce job的并发提交(默认false,开启默认8)
set hive.exec.parallel.thread.number=16;    --设置并行数
set hive.support.quoted.identifiers=None; --开启排除某列
set hive.mapred.mode=strict;    --strict，不允许笛卡尔积。默认是：nostrict
set hive.execution.engine=tez;      --设置计算引擎，默认mr
set tez.grouping.min-size=556000000;  --最小556M，合并map端小文件
set tez.grouping.max-size=3221225472; --最大556M，可增加处理Map数量
set tez.queue.name=HIGH_BIE_DYNAMIC;  -- 设置tez引擎使用的队列为HIGH_BIE_DYNAMIC
set hive.map.aggr=true; -- map端聚合
set auto.convert.join=true;     --是否根据输入小表的大小，自动将 Reduce 端的 Common Join 转化为 Map Join，从而加快大表关联小表的 Join 速度。 默认：false。

...
...

五、DQL

1. 通用模板

select [all | distinct] select_expr, select_expr, ...
from table_reference
[where where_condition]
[group by col_list]
[having col_list]
[window ]   --窗口函数专用
[order by col_list]
[cluster by col_list | [distribute by col_list] [sort by col_list]]
[limit [offset,] rows];

1) 排序部分详解

order by            --全局排序，只有一个reduce
sort by             --针对每个reduce产生一个排序文件，在reduce内部进行排序。
distribute by       --将特定的行发送到特定的reduce中，便于后续的聚合与排序操作。一般结合sort by 使用，使分区数据有序且要写在sort by之前
cluster by          --当distribute by和sort by为同一个字段时，可以使用cluster by简化语法。但cluster by只能是升序，不能指定排序规则

eg:
-- 语法上是等价的
select * from emp distribute by deptno sort by deptno; 
select * from emp cluster by deptno;

--小结
order by    --执行全局排序，效率低。生产环境中慎用
sort by     --使数据局部有序(在reduce内部有序)
distribute by --按照指定的条件将数据分组，常与sort by联用，使数据局部有序
cluster by  --当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法

2. 运行顺序

from
join
where
group by
having
select
distinct
order by | cluster by | (distribute by/sort by)
limit

3. 经典查询

--select除了某些字段之外的剩余所有字段
set hive.support.quoted.identifiers=None; --开启排除某列
select `(rank|inc_day)?+.+`
from tmp_dm_icsm.tmp_cost_task_dim_tableau_wild_oylz
limit 10; --多个字段 要用|分开

4. 连接查询

join                --内连接
left outer join     --左外连接
right outer join    --右外连接
full outer join     --全外连接
left semi join      --左半连接

--左半连接例子(下面两段sql等效)
--eg01
select user.id,user.name from user
left semi join post 
on (user.id=post.uid);
--eg02
select id,name from user
where id in
(select uid from post);

六、DDL

数据定义语言:常用的有CREATE和DROP，用于在数据库中创建新表或删除表，以及为表加入索引等

1. 对database操作

1) 创建数据库

--创建数据库
/*
--基础语法
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
*/
create database db_name;
create database if not exists db_name;  --当数据库不存在时创建
create database if not exists db_name comment 'this is test db';    --创建是添加备注
create database db_name location '/hive/db/db_name';
create database db_name with dbproperties('name'='dachun','date'='20200202');   --设置数据库属性，添加了<创建人,创建时间>

2) 查询数据库

show databases;
show databases like 'db_hive*';
desc database db_hive;    --显示数据库信息
desc database extended db_hive;   --显示数据库详细信息
use db_hive;    --切换到指定数据库
show create database db_hive;    --查看创建数据库的语句
describe database db_test;   --显示数据库中文件目录位置路径

3) 修改数据库

--基础语法
alter database <database_name> set dbproperties ('<property_name>'='<property_value>',..);

----
alter database db_hive set dbproperties ('owner'='senfos.w','date'='2022-11-6');  --添加创建人,创建时间
alter database db_hive set location '/hive/db/db_hive';   --设置路径

4) 删除数据库

--基础语法
drop (database|schema) [if exists] database_name [restrict|cascade];  --RESTRICT(限制)|CASCADE(级联)，默认为限制类别

2. 对table操作

1) 创建表

1° 基本语法

--基本语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

-------------
-->（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；
用户可以用 IF NOT EXISTS 选项来忽略这个异常。
-->（2）EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实
际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外
部表只删除元数据，不删除数据。
-->（3）COMMENT：为表和列添加注释。
-->（4）PARTITIONED BY 创建分区表
-->（5）CLUSTERED BY 创建分桶表
-->（6）SORTED BY 不常用，对桶中的一个或多个列另外排序
-->（7）ROW FORMAT 
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
 [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
 | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, 
property_name=property_value, ...)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW 
FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需
要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表
的具体的列的数据。
SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化。
-->（8）STORED AS 指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列
式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED 
AS SEQUENCEFILE。
-->（9）LOCATION ：指定表在 HDFS 上的存储位置。
-->（10）AS：后跟查询语句，根据查询结果创建表。
-->（11）LIKE 允许用户复制现有的表结构，但是不复制数据。

2° 常用语句

--默认创建管理表(内部表)
--存储位置由配置项hive.metastore.warehouse.dir所定义的目录的子目录下
create table if not exists student(
  id int, 
  name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';

--根据查询结果创建表(包括数据和表结构)
create table if not exists student2 as select id, name from student;

--根据已经存在的表结构创建表(仅表结构)
create table if not exists student3 like student;

3° 内部表和外部表

--内外部表区别
--1> 
external修饰的是内部表（managed table），被external修饰的为外部表.
--2>
内部表数据由Hive自身管理，外部表数据由HDFS管理
--3>
内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）
--4>
删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除
--5>
对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE table_name;）
--6> 使用场景
每天将收集到的网站日志定期流入 HDFS 文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过 SELECT+INSERT 进入内部表。
--7> 内外部表互相转换
alter table student2 set tblproperties('EXTERNAL'='TRUE');    --修改内部表 student2 为外部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');   --修改外部表 student2 为内部表
 desc formatted student2;   --查看表类型

2) 查询表

--查询表的类型
desc formatted student2;

--查看表
show tables;

--查看表结构
desc table_name;

--查看分区信息
show partitions table_name;

--根据分区查询数据
select table_coulm 
from table_name 
where partition_name = '2014-02-25';

--查看hdfs文件信息
dfs -ls /user/hive/warehouse/table02;

3) 修改表

--1.重命名
alter table table_name rename to new_table_name;

--2.修改列
----基本语法
ALTER TABLE table_name CHANGE
[CLOUMN] col_old_name col_new_name column_type
[CONMMENT col_conmment]
[FIRST|AFTER column_name];
/*
这个命令可以修改表的列名，数据类型，列注释和列所在的位置顺序，FIRST将列放在第一列，AFTER col_name将列放在col_name后面一列
*/
----eg
ALTER TABLE test_table CHANGE col1 col2 STRING COMMENT 'The datatype of col2 is STRING' AFTER col3;
hive> ALTER TABLE employee CHANGE name ename String;
hive> ALTER TABLE employee CHANGE salary salary Double;

--3.增加/更新列
----基本语法
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [CONMMENT col_comment], ...);
/*
ADD COLUMNS允许用户在当前列的末尾，分区列之前添加新的列，REPLACE COLUMNS允许用户更新列，更新的过程是先删除当前的列，然后在加入新的列
*/
----eg
ALTER TABLE employee ADD COLUMNS (dept STRING COMMENT 'Department name');
ALTER TABLE employee REPLACE COLUMNS ( eid INT empid Int, ename STRING name String);

--4.增加表的属性/SerDE属性
----表的属性基本语法
ALTER TABLE table_name SET TBLPEOPERTIES table_properties;
/*
table_properties:
(property_name=property_value,property_name=property_value, ...)
*/
----eg

----SerDE属性
ALTER TABLE table_name SET SERDE serde_class_name
[WHIT SERDEPROPERTIES serde_properties];

ALTER TABLE table_name SET SERDEPROPERTIES serde_properties;
/*
serde_properties的结构为(property_name=property_value,property_name=property_value, ...)
*/

--5.修改表文件格式和组织
----基本语法
ALTER TABLE table_name SET FILEFORMAT file_format;
ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...)
[SORTED By (col_name, ...)] INTO num_buckets BUCKETS;

4) 修改表分区

1° 分区类别

/*
静态分区：加载数据到指定分区的值。
动态分区：数据未知，根据分区的值来确定需要创建的分区。
混合分区：静态和动态都有。

注意：
strict:严格模式必须至少一个静态分区
nostrict：可以所有的都为动态分区，但是建议尽量评估动态分区的数量
*/
set hive.exec.dynamic.partition=true
set hive.exec.dynamic.partition.mode=strict/nonstrict
set hive.exec.max.dynamic.partitions=1000
set hive.exec.max.dynamic.partitions.pernode=100

2° 查看分区

show partitions 表名;

3° 添加分区

alter table part1 add partition(dt='2019-09-10');
alter table part1 add partition(dt='2019-09-13') partition(dt='2019-09-12');
alter table part1 add partition(dt='2019-09-11') location  '/user/hive/warehouse/qf1704.db/part1/dt=2019-09-10';

4° 分区名称修改

alter table part1 partition(dt='2019-09-10') rename to partition(dt='2019-09-14');

5° 修改分区路径

--正确使用，绝对路径
alter table part1 partition(dt='2019-09-14') set location 'hdfs://hadoo01:9000/user/hive/warehouse/qf24.db/part1/dt=2019-09-09';  

6° 删除分区

alter table part1 drop partition(dt='2019-09-14');
alter table part1 drop partition(dt='2019-09-12'),partition(dt='2019-09-13');

5) 删除表

drop table emp;

七、DML

1. hive中常用的存储格式

--常用的三种
textFile、ORCFile、Parquet

--差异描述
textFile : hive默认的存储格式 ，默认为行存储
ORCFile ：ORC数据压缩率比较高，通过采用数据按照行分块，每个块按照列存储，其中每个存储都一个索引
Parquet ：Parquet具有很好的压缩性能，可以减少大量的表扫描和反序列化的时间

--指定方式
创建表时通过stored as 关键字指定表的存储格式，默认为textFile

--性能对比
通过对比这三种磁盘占用：orc<parquet<textfile
通过对比这三种格式查询时间：orc<parquet<textfile

2. load

--1. 基本格式
load data [local] inpath '数据的 path' [overwrite] into table
student [partition (partcol1=val1,…)];

--2. 加载本地数据到student表
load data local inpath
'/opt/module/hive/datas/student.txt' into table default.student;

--3. 加载HDFS数据
load data inpath '/user/atguigu/hive/student.txt'
overwrite into table default.student;

3. import/export(导入/导出)

import table student2 from '/user/hive/warehouse/export/student';

export table default.student to '/user/hive/warehouse/export/student';

4. insert

--1
insert into table student_par values(1,'wangwu'),(2,'zhaoliu');
--2
insert overwrite table student_par select id, name from student where month='201709';
--3
 from student
 insert overwrite table student partition(month='201707')
 select id, name where month='201709'
 insert overwrite table student partition(month='201706')
 select id, name where month='201709';
--insert into：以追加数据的方式插入到表或分区，原有数据不会删除
--insert overwrite：会覆盖表中已存在的数据

5. udpate/delete

1. update性能太差，且使用时对表结构有特殊要求，基本不使用
2. delete不存在，删除部分数据需通过alter+drop删除部分表表空间的内容

八、函数

(一)、内置函数

show functions;             --查看系统自带的函数
desc function upper;        --显示自带的函数的用法
desc function extended upper;   --详细显示自带的函数的用法

1. 数字函数

abs                         --返回数值a的绝对值
conv(BIGINT num, int from_base, int to_base)    --将数值num从from_base进制转化到to_base进制
round(double a[, int d])    --四舍五入(指定精度)取整
floor(double a)             --向下取整
ceil(double a)              --向上取整(ceiling同功能)
rand(),rand(int seed)       --返回一个0到1范围内的随机数。指定seed时，相同seed获取到相同的随机值
exp(double a)               --返回自然对数e的a次方
log(double base, double a)  --返回以base为底的a的对数
pow(double a, double p)     --返回a的p次幂
power(double a, double p)   --返回a的p次幂,与pow功能相同
sqrt(double a)              --返回a的平方根

2. 日期函数

from_unixtime(bigint unixtime[, string format])   --转化UNIX时间戳到当前时区的时间格式
unix_timestamp()    --获得当前时区的UNIX时间戳
unix_timestamp(string date)   --日期转UNIX时间戳函数
unix_timestamp(string date, string pattern)   --指定格式日期转UNIX时间戳函数
to_date(string timestamp[, string pattern])   --日期时间转日期函数/指定日期格式
year/month/day/hour/minute/second/weekofyear<返回日期在当前的周数>(string date)
datediff(string enddate, string startdate)    --返回结束日期减去开始日期的天数
date_add(string startdate, int days)    --返回开始日期startdate增加days天后的日期
date_sub (string startdate, int days)   --返回开始日期startdate减少days天后的日期

3. 字符函数

length()    --计算字符串长度
reverse()   --字符串反转
concat(string A, string B…)     --字符串连接
concat_ws(string SEP, string A, string B…)  --带分隔符字符串连接函数
substr(string A, int start[, int len])/substring(string A, int start[, int len])  --字符串截取
upper()/ucase()     --字符串转大写
lower()/lcase()     --字符串转小写
trim()      --去除字符串两边的空格
ltrim()     --去除左边的空格
rtrim()     --去除右边的空格
lpad(string str, int len, string pad)/rpad()   --将str进行用pad进行左/右补足到len位
split(string str, string pat)   --按照pat字符串分割str，会返回分割后的字符串数组
regexp_replace()    --正则表达式替换函数
regexp_extract()    --正则表达式解析函数

4. 条件函数

if(boolean testCondition, T valueTrue, T valueFalseOrNull)  --当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull
select COALESCE(null,'100','50') from tableName;  --return 100, 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
case a when b then c [when d then e]* [else f] end  --如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
case when a then b [when c then d]* [else e] end  --如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e

5. 聚合函数

count(distinct/all)
sum()
avg()
min()
max()

6. 窗口函数

--基础语法
<窗口函数> over ([partition by <分组字段>]  order by <排序字段> [rows between 开始位置 and 结束位置])

/*
针对窗口大小除了使用rows之外还可以通过以下方式：
order by 字段名 range/rows 边界规则0/between 边界规则1 and 边界规则2

注：默认框架将采用 RANGE UNBOUNDED PRECEDING AND CURRENT ROW（表示当前行以及一直到第一行的数据）

边界规则：
current row     --当前行
n preceding     --往前n行数据，包含当前行
UNBOUNDED PRECEDING   --一直到第一条记录，包含当前行
n FOLLOWING   --往后n行数据，包含当前行
UNBOUNDED FOLLOWING   --一直到最后一条记录，包含当前行

PS: RANGE 只支持使用 UNBOUNDED 和 CURRENT ROW 窗口框架分隔符。

eg:
1.第一行到当前行
ORDER BY score desc rows UNBOUNDED PRECEDING
 
2.第一行到前一行(不含当前行)
ORDER BY score desc rows between UNBOUNDED PRECEDING and 1 PRECEDING 
 
3.第一行到后一行(包含当前行)
ORDER BY score desc rows between UNBOUNDED PRECEDING and 1 FOLLOWING
 
4.当前行到最后一行
ORDER BY score desc rows between CURRENT ROW and UNBOUNDED FOLLOWING 
注意，这种写法是错误的
ORDER BY score desc rows UNBOUNDED FOLLOWING -- 错误示范
 
5.前一行到最后一行(包含当前行)
ORDER BY score desc rows between 1 PRECEDING and UNBOUNDED FOLLOWING
 
6.后一行到最后一行(不含当前行)
ORDER BY score desc rows between 1 FOLLOWING and UNBOUNDED FOLLOWING
 
7.前一行到后一行(包含当前行) 
ORDER BY score desc rows between 1 PRECEDING and 1 FOLLOWING
*/

a. 聚合类窗口函数

count() over()
sum() over()
avg() over()
min() over()
max() over()

eg:
select
    deptno,
    sum(sal) over (partition by deptno order by sal desc) my_window_name as sum_sal,
    max(sal) over (partition by deptno order by sal desc) my_window_name as max_sal,
    min(sal) over (partition by deptno order by sal desc) my_window_name as min_sal,
    avg(sal) over (partition by deptno order by sal desc) my_window_name as avg_sal
from scott.emp
window my_window_name as (partition by deptno order by sal desc)

b. 排序类窗口函数

row_number()over()	--不计算相同数据，排名依次往后。1234
rank()over()	--计算想要的数据，相同的数据排名一样，并空出后继续排名。1224
dense_rank()over()--计算相同的数据，相同的数据排名一样，但是不空出后续排名。1223

c. 位移类窗口函数

lag( exp_str,offset,defval) over(partition by .. order by …)
lead(exp_str,offset,defval) over(partition by .. order by …)

• exp_str 是字段名
• Offset 是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，offset 为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）。
• Defval 默认值，当两个函数取 上N 或者 下N 个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag() 函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。

d. 分布类窗口函数

percent_rank() over()
/*
percent_rank()函数将某个数值在数据集中的排位作为数据集的百分比值返回，此处的百分比值的范围为 0 到 1。此函数可用于计算值在数据集内的相对位置。
*/
cume_dist() over()
/*
如果按升序排列，则统计：小于等于当前值的行数/总行数。
如果是降序排列，则统计：大于等于当前值的行数/总行数。
*/
--常配合rank() 函数使用
--eg:
SELECT
    uid,
    score,
    rank() OVER my_window_name AS rank_num,
    PERCENT_RANK() OVER my_window_name AS prk
FROM exam_record
WINDOW my_window_name AS (ORDER BY score desc)

e. 头尾窗口函数

first_value() over()   --获取当前字段的第一个值
last_value() over()    --获取当前字段的最后一个值

eg:
select distinct deptno,
       first_value(sal) over(partition by deptno order by sal desc) as max_sal
from scott.emp;

7. 表生成函数explode(行转列)

explode(col)  --将hive一列中复杂的array或map结构拆分成多行，形成一列

lateral view
--用法：lateral view udtf(expression) tablealias as columnalias
--用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

--eg:
select class,student_name
from default.classinfo
lateral view explode(split(student,',')) t as student_name;

8. reflect函数

--可以支持在sql中调用java中的自带函数

--eg: 判断是否为数字
/*
使用apache commons中的函数，commons下的jar已经包含在hadoop的classpath中，所以可以直接使用
*/
select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123");

(二)、自定义函数

--自定义函数包括三种：UDF、UDAF、UDTF
UDF: 一进一出
UDAF: 多进一出
UDTF: 一进多出

第一步：继承和重写

--1. 继承org.apache.hadoop.hive...
----UDF
org.apache.hadoop.hive.ql.UDF
--UDAF
org.apache.hadoop.hive.ql.exec.UDAF
org.apache.hadoop.hive.ql.exec.UDAFEvaluator
--UDTF
org.apache.hadoop.hive.ql.udf.generic.GenericUDF

--2. 重写方法
----UDF
evaluate()
----UDAF
Evaluator 需要实现 init、iterate、terminatePartial、merge、terminate 这几个函数
----UDTF
initlizer()
getdisplay()
evaluate()

第二步：打包加载

add jar /hivedata/udf.jar;

第三步：创建函数

create temporary function fun_demo as 'com.qf.hive.fun_demo'; # 创建一个临时函数fun_demo

--查看函数是否加入
show functions ;

第四步：使用自定义函数

select maxInt(mgr) from emp;

九、SQL优化

--查看执行计划
explain select deptno, avg(sal) avg_sal from emp group by
deptno;

1. 常见优化策略

1. map端连接
2. 合理设置maptask
3. 合理设置reducetask
4. 小文件合并
5. 学会使用分区/分桶
6. 注意负载均衡，避免数据倾斜(尽量避免使用count(distinct))
7. 注意join/group by的使用
8. 通过查看执行计划分析优化方案

...
...

2. 优化实现

1) map端连接

--1. 简介
/*
map-join会把小表全部加载到内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map端是进行了join操作，省去了reduce运行的时间
*/

--2. 使用方式
SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM a JOIN b 
ON a.key = b.key;

--3. 涉及参数
/*
1、小表自动选择Mapjoin
set hive.auto.convert.join=true;
默认值：false。该参数为true时，Hive自动对左边的表统计量，若是小表就加入内存，即对小表使用Map join
2、小表阀值
set hive.mapjoin.smalltable.filesize=25000000;
默认值：25M
hive.smalltable.filesize (replaced by hive.mapjoin.smalltable.filesize in Hive 0.8.1)
*/