Hive详解(超详细)

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--3tinyint/smallint/int都可以转化为float--4boolean类型不可以转换为其他任何类型

2° 手动操作

cast(valueastype)

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

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

--2. date类型的数据，只能在date/timestamp/string之前转换
cast(dateasdate)--返回date类型
cast(timestampasdate)--timestamp是依赖时区的
cast(string asdate)--string必须为yyyy-mm-dd的格式
cast(dateastimestamp)
cast(dateas string)--date转换为yyyy-mm-dd的字符串--3. cast可嵌套使用select(cast(cast(col as string)asdouble))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 Bor--如果A或B或两者都是true 返回true,否则false||--类似A or Bnot!--!A, 类似于 not A

5. 集合运算

union--并集，有去重效果unionall--并集，不去重--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 notin(select product_id from hive_4_product_2);--对称差SELECT*FROM hive_4_product_1
 WHERE product_id NOTIN(SELECT product_id FROM hive_4_product_2)UNIONSELECT*FROM hive_4_product_2
 WHERE product_id NOTIN(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.xS 是一个结构它返回S的s字段

三、常用shell命令

--启动hive
$ bin/hive

--启动元数据服务
$ hive --service metastore--查看数据库showdatabase;--打开默认数据库usedefault;--退出hive
quit;exit;--显示default数据库中的表showtables;--查看表结构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;--设置参数setkey=value;set mapred.reduce.tasks     --设置reduce个数(默认-1)
mapred.map.tasks        --设置提交Job的map端个数set hive.exec.dynamic.partition=true;-- 开启动态分区功能，默认：falseset 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，不允许笛卡尔积。默认是：nostrictset hive.execution.engine=tez;--设置计算引擎，默认mrset 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_DYNAMICset 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][groupby col_list][having col_list][window ]--窗口函数专用[orderby col_list][cluster by col_list |[distribute by col_list][sort by col_list]][limit[offset,]rows];

1) 排序部分详解

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

2. 运行顺序

fromjoinwheregroupbyhavingselectdistinctorderby| 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
limit10;--多个字段 要用|分开

4. 连接查询

join--内连接leftouterjoin--左外连接rightouterjoin--右外连接fullouterjoin--全外连接left semi join--左半连接--左半连接例子(下面两段sql等效)--eg01selectuser.id,user.name fromuserleft semi join post 
on(user.id=post.uid);--eg02select id,name fromuserwhere 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, ...)];
*/createdatabase db_name;createdatabaseifnotexists db_name;--当数据库不存在时创建createdatabaseifnotexists db_name comment'this is test db';--创建是添加备注createdatabase db_name location '/hive/db/db_name';createdatabase db_name with dbproperties('name'='dachun','date'='20200202');--设置数据库属性，添加了<创建人,创建时间>

2) 查询数据库

showdatabases;showdatabaseslike'db_hive*';descdatabase db_hive;--显示数据库信息descdatabaseextended db_hive;--显示数据库详细信息use db_hive;--切换到指定数据库showcreatedatabase db_hive;--查看创建数据库的语句describedatabase db_test;--显示数据库中文件目录位置路径

3) 修改数据库

--基础语法alterdatabase<database_name>set dbproperties ('<property_name>'='<property_value>',..);----alterdatabase db_hive set dbproperties ('owner'='senfos.w','date'='2022-11-6');--添加创建人,创建时间alterdatabase db_hive set location '/hive/db/db_hive';--设置路径

4) 删除数据库

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

2. 对table操作

1) 创建表

1° 基本语法

--基本语法CREATE[EXTERNAL]TABLE[IFNOTEXISTS] table_name
[(col_name data_type [COMMENT col_comment],...)][COMMENT table_comment][PARTITIONED BY(col_name data_type [COMMENT col_comment],...)][CLUSTEREDBY(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 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；
用户可以用 IFNOTEXISTS 选项来忽略这个异常。
-->（2）EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实
际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外
部表只删除元数据，不删除数据。
-->（3）COMMENT：为表和列添加注释。-->（4）PARTITIONED BY 创建分区表-->（5）CLUSTERED BY 创建分桶表-->（6）SORTED BY 不常用，对桶中的一个或多个列另外排序-->（7）ROW FORMAT 
DELIMITED [FIELDSTERMINATEDBYchar][COLLECTION ITEMS TERMINATEDBYchar][MAP KEYSTERMINATEDBYchar][LINESTERMINATEDBYchar]| 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所定义的目录的子目录下createtableifnotexists student(
  id int, 
  name string
)row format delimited fieldsterminatedby'\t'
stored as textfile
location '/user/hive/warehouse/student';--根据查询结果创建表(包括数据和表结构)createtableifnotexists student2 asselect id, name from student;--根据已经存在的表结构创建表(仅表结构)createtableifnotexists 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> 内外部表互相转换altertable student2 set tblproperties('EXTERNAL'='TRUE');--修改内部表 student2 为外部表altertable student2 set tblproperties('EXTERNAL'='FALSE');--修改外部表 student2 为内部表desc formatted student2;--查看表类型

2) 查询表

--查询表的类型desc formatted student2;--查看表showtables;--查看表结构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.重命名altertable table_name renameto new_table_name;--2.修改列----基本语法ALTERTABLE 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后面一列
*/----egALTERTABLE test_table CHANGE col1 col2 STRING COMMENT'The datatype of col2 is STRING'AFTER col3;
hive>ALTERTABLE employee CHANGE name ename String;
hive>ALTERTABLE employee CHANGE salary salary Double;--3.增加/更新列----基本语法ALTERTABLE table_name ADD|REPLACECOLUMNS(col_name data_type [CONMMENT col_comment],...);/*
ADD COLUMNS允许用户在当前列的末尾，分区列之前添加新的列，REPLACE COLUMNS允许用户更新列，更新的过程是先删除当前的列，然后在加入新的列
*/----egALTERTABLE employee ADDCOLUMNS(dept STRING COMMENT'Department name');ALTERTABLE employee REPLACECOLUMNS( eid INT empid Int, ename STRING name String);--4.增加表的属性/SerDE属性----表的属性基本语法ALTERTABLE table_name SET TBLPEOPERTIES table_properties;/*
table_properties:
(property_name=property_value,property_name=property_value, ...)
*/----eg----SerDE属性ALTERTABLE table_name SET SERDE serde_class_name
[WHIT SERDEPROPERTIES serde_properties];ALTERTABLE table_name SET SERDEPROPERTIES serde_properties;/*
serde_properties的结构为(property_name=property_value,property_name=property_value, ...)
*/--5.修改表文件格式和组织----基本语法ALTERTABLE table_name SET FILEFORMAT file_format;ALTERTABLE table_name CLUSTEREDBY(col_name, col_name,...)[SORTED By(col_name,...)]INTO num_buckets BUCKETS;

4) 修改表分区

1° 分区类别

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

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

2° 查看分区

show partitions 表名;

3° 添加分区

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

4° 分区名称修改

altertable part1 partition(dt='2019-09-10')renametopartition(dt='2019-09-14');

5° 修改分区路径

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

6° 删除分区

altertable part1 droppartition(dt='2019-09-14');altertable part1 droppartition(dt='2019-09-12'),partition(dt='2019-09-13');

5) 删除表

droptable 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. 基本格式loaddata[local] inpath '数据的 path'[overwrite]intotable
student [partition(partcol1=val1,…)];--2. 加载本地数据到student表loaddatalocal inpath
'/opt/module/hive/datas/student.txt'intotabledefault.student;--3. 加载HDFS数据loaddata inpath '/user/atguigu/hive/student.txt'
overwrite intotabledefault.student;

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

importtable student2 from'/user/hive/warehouse/export/student';

export tabledefault.student to'/user/hive/warehouse/export/student';

4. insert

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

5. udpate/delete

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

八、函数

(一)、内置函数

show functions;--查看系统自带的函数descfunction upper;--显示自带的函数的用法descfunctionextended 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,intstart[,int len])/substring(string A,intstart[,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；否则返回valueFalseOrNullselectCOALESCE(null,'100','50')from tableName;--return 100, 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULLcase a when b then c [when d then e]*[else f]end--如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回fcasewhen 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([partitionby<分组字段>]orderby<排序字段>[rowsbetween 开始位置 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(partitionby deptno orderby sal desc) my_window_name as sum_sal,max(sal)over(partitionby deptno orderby sal desc) my_window_name as max_sal,min(sal)over(partitionby deptno orderby sal desc) my_window_name as min_sal,avg(sal)over(partitionby deptno orderby sal desc) my_window_name as avg_sal
from scott.emp
window my_window_name as(partitionby deptno orderby sal desc)

b. 排序类窗口函数

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

c. 位移类窗口函数

lag( exp_str,offset,defval)over(partitionby..orderby …)
lead(exp_str,offset,defval)over(partitionby..orderby …)

• 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(ORDERBY score desc)

e. 头尾窗口函数

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

eg:
selectdistinct deptno,
       first_value(sal)over(partitionby deptno orderby 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
fromdefault.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;

第三步：创建函数

createtemporaryfunction fun_demo as'com.qf.hive.fun_demo';# 创建一个临时函数fun_demo--查看函数是否加入show functions ;

第四步：使用自定义函数

select maxInt(mgr)from emp;

九、SQL优化

--查看执行计划explainselect deptno,avg(sal) avg_sal from emp groupby
deptno;

1. 常见优化策略

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

......

2. 优化实现

1) map端连接

--1. 简介/*
map-join会把小表全部加载到内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map端是进行了join操作，省去了reduce运行的时间
*/--2. 使用方式SELECT/*+ MAPJOIN(b) */ a.key, a.valueFROM 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)
*/