hive

Hive访问

--先开启服务
[atguigu@hadoop102 hive]$ nohup hive --service metastore>log.txt 2>&1 &
[atguigu@hadoop102 hive]$ nohup hive --service hiveserver2>log2.txt 2>&1 &

--启动beeline客户端
[atguigu@hadoop102 hive]$ bin/beeline -u jdbc:hive2://hadoop102:10000 -n atguigu
--启动hive客户端
[atguigu@hadoop102 hive]$ bin/hive

Hive常用交互命令

-- 在hive命令行里创建一个表student，并插入1条数据
hive (default)> create table student(id int,name string);
OK

--查看hdfs文件系统（很少用，一般直接网页看）
hive(default)>dfs -ls /;

--查看在hive中输入的所有历史命令
[atguigu@hadoop102 ~]$ cat .hivehistory

脚本中调用hive

--hive -e （不进入hive的交互窗口执行sql语句）
[atguigu@hadoop102 hive]hive -e "select * from student"

--hive -f （执行脚本中sql语句）
[atguigu@hadoop102 hive]hive -f stu.sql

--执行文件中的sql语句并将结果写入文件中
[atguigu@hadoop102 hive]$ hive -f /opt/module/hive/datas/hivef.sql  > /opt/module/datas/hive_result.txt

DDL数据定义语言

4.1 库的DDL

--创建数据库
create database if not exists db_hive
comment "this is my first db"
with dbproperties ("name"="db_hive","owner"="atguigu");
location '/db_hive';

--查询数据库
hive> show databases；
hive> show databases like 'db_hive*';
--简单查看
desc database 数据库名;
--详细查看 （详细查看可以看到库的属性信息，简单查看看不到）
desc database extended 数据库名;
--切换数据库
use 数据库名;
--修改数据库(只能修改数据库的属性信息，别的都无法更改)
alter database db_hive set dbproperties('createtime'='20200624');

--删除数据库(hdfs上对应的目录也会删除，谨慎操作。)
--如果数据库不为空，可以在最后加上cascade强制删除
drop database 数据库名 cascade;
--为了更严谨，我们可以在删除之前判断数据库是否存在
drop database if exists 数据库名 cascade;

4.2 表的DDL

1 创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name          --指定表名  【external 外部表/内部表】
[(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 DELIMITED      --指定hive表在hdfs上存储的原始数据的格式
 [FIELDS TERMINATED BY char]     --每行数据中字段的分隔符    默认分隔符：ascII码表的第一个字符  ^A 
 [COLLECTION ITEMS TERMINATED BY char]   --集合元素分隔符  默认分隔符：ascII码表的第二个字符  ^B
 [MAP KEYS TERMINATED BY char]  --map集合中 key 和 value 的分隔符    ascII码表的第三个字符  ^C
 [LINES TERMINATED BY char]      --每行数据的分隔符     默认值：'\n'
]  
[STORED AS file_format]         --指定hive的数据在hdfs上存储的格式
[LOCATION hdfs_path]            --指定hive数据在hdfs上存储的路径
[TBLPROPERTIES (property_name=property_value, ...)]    --指定表的属性
[AS select_statement]    --按照as后面的查询语句的结果来创建表，复制表结构以及表数据
[LIKE table_name]     --按照like后面的表结构来创建表，只复制表结构，不复制表数据

默认分割符 ^{A：在shell里面vim模式下，按ctrl+v,出来}，再按ctrl+A，出来A

linux查看默认分隔符，用cat -A …

-- 创建管理表
create table student(id int,name string)
row format delimited fields terminated by '\t';
--根据AS select语句查询结构创建表,复制表结构，复制表数据
create table student3 as select * from student;
--根据like 创建表，只复制表结构，不复制表数据
create table student4 like student;

--简单查看表信息
desc 表名;
--详细查看表信息
desc formatted 表名;
--删除管理表(同时会删除hdfs上对应目录的数据，谨慎操作)
drop table student3;

--创建外部表
create external table if not exists test(
id string,up_area string,down_area string,up_time TIMESTAMP)
row format delimited fields terminated by '\t'
location '/company/test';//location只能是个目录
--删除外部表(删除后hdfs中的数据还在，但是metadata中dept的元数据已被删除)
drop table test;

--外、内表的转换（TRUE是外部表，FALSE是内部表）
alter table student set tblproperties('EXTERNAL'='TRUE/FALSE');
--重命名表（如果该表是管理表且创建时指定了目录，会一并修改hdfs上的目录名）
ALTER TABLE table_name RENAME TO new_table_name
--更新列，列名可以随意修改，列的类型只能小改大，不能大改小（遵循自动转换规则）
alter table student CHANGE COLUMN age newage int;
--增加列
alter table student add columns(age int);
--替换列（REPLACE是表示替换表中所有字段）
ALTER TABLE student replace columns(n_name string,n_age double);
--清除表数据（只能truncate 管理表，外部表不行。truncate的本质其实就是删除hdfs上对应路径的数据）
truncate table 表名;

创建外部表时还要

上传数据到HDFS

hive (default)> dfs -put /opt/module/hive/datas/test.txt /company/test;

或者向表中装载数据（Load）

hive (default)> load data local inpath '/opt/module/hive/datas/test.txt' into table dept;

DML数据操作(后面再完善)

5.1 数据导入

--Load
load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
--加载本地文件到hive,覆盖数据
load data local inpath '/opt/module/hive/datas/student.txt' overwrite into table student;
--加载HDFS文件到hive中，追加数据
dfs -put /opt/module/hive/datas/student.txt /user/atguigu;
load data inpath '/user/atguigu/student.txt' into table student;

--Insert
--into是追加插入，overwrite是覆盖插入，此方式一般没人用
insert into/overwrite table student values(1018,'ss18'),(1019,'ss19');
--此方式用的比较多，一般都是查询原始表的数据到临时表，注意select之前不能加as，跟创建表时as select区分开
--注意：通过inset插入数据，数据格式和列的数量要一致才可以。
insert into/overwrite table student2  select * from student where id < 1006;
-- 
create table student3 as  select * from student;

5.2 数据导出

--insert 导出(后面只能跟overwrite,导出路径可以不存在，hive会帮我们创建路径)
insert overwrite local directory '/opt/module/hive/datas/export/student'
select * from student;

insert overwrite local directory '/opt/module/hive/datas/export/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;

--hadoop命令导出
hadoop dfs -get /user/hive/warehouse/student/student.txt
/opt/module/hive/datas/export/student3.txt;
--hive shell 命令导出
hive -e 'select * from db_hive.student' > /opt/module/hive/datas/stu.txt
--export 导出 (只能导出到hdfs上，并且会将元数据和数据一起导出)
export table db_hive.student to '/stu';

查询DQL

语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...     --指定查询字段
  FROM table_reference                                    --从哪个表查询
  [WHERE where_condition]                                --指定where过滤条件
  [GROUP BY col_list]                                   --指定分组条件
  [ORDER BY col_list]                                 --指定排序条件
  [CLUSTER BY col_list    
    | [DISTRIBUTE BY col_list] [SORT BY col_list]        --hive排序四个by
  ]
 [LIMIT number]                      --限制输出结果条数

Like：
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
RLIKE：Java的正则表达式

​ ^代表以什么开头
​ .代表一个任意字符
​ 'A’代表查询含字母A的所有字符串

where A<=>B; --如果A和B都为NULL，则返回TRUE，如果一边为NULL，返回False
--（1）查找名字以A开头的员工信息
hive (default)> select * from emp where ename LIKE 'A%';
hive (default)> select * from emp where ename RLIKE '^A';
--（2）查找名字中第二个字母为A的员工信息
hive (default)> select * from emp where ename LIKE '_A%';
hive (default)> select * from emp where ename RLIKE '^.A';
--（3）查找名字中带有A的员工信息
hive (default)> select * from emp where ename  LIKE '%A%';
hive (default)> select * from emp where ename  RLIKE '[A]';

排序

1 全局排序 order by

全局排序，只有一个Reducer，把所有数据都放在一个分区里面进行排序

--按照部门降序和工资升序排序
select deptno,sal,empno,ename,job from emp order by deptno DESC ,sal asc ;

2 单个reducer排序 sort by

​ 单独使用sort by，只能给每个分区内的数据排序，但不能指定分区，属于随机给数据分配分区

3 分区 distribute by

指定分区字段，一般在sort by之前都会加上 distribute by

Ø distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。

Ø Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

Ø 演示完以后mapreduce.job.reduces的值要设置回-1，否则下面分区or分桶表load跑mr的时候有可能会报错

hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/opt/module/hive/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

4 分区排序 Cluster by

cluster by属于 distribute by + sort by的结合，但是只能是同一个字段，才可以替换。并且只能升序排

select * from emp distribute by deptno sort by deptno;
--上面的sql可以简写成下面的
select * from emp cluster by deptno;

分区表 分桶表

hive里面没有索引机制，每次查询的时候，hive会暴力扫描整张表。

分区表的本质就是分目录，按照业务需求，把数据分成多个目录存储，然后查询的时候就可以通过where条件指定对应的分区

创建分区表语法

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';

分区字段属于分区表的一个伪列，数据里面并没有记录这列的值，分区字段的值体现在分区目录名上面。

--往分区表里正常load数据，一定要指定分区
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');
--分区表查询数据
select * from dept_partition where day = '20200401' or day = '20200402';
select * from dept_partition where day='20200401'
union
select * from dept_partition where day='20200402';

--查看分区表有多少分区
show partitions dept_partition;
--查看分区表信息
desc formatted dept_partition;

--删除分区(会一起删除掉分区内的数据)
alter table dept_partition drop partition (day='20200405');
--删除多个分区 (注意：多个分区间必须有逗号，没有会报错)
alter table dept_partition drop partition(day='20200405'),partition(day='20200406');
--增加分区
alter table dept_partition add partition(day='20200404') ;
--增加多个分区 (注意：多个分区间不能逗号，有会报错)
alter table dept_partition add partition(day='20200405') partition(day='20200406');

二级分区表

--创建二级分区表
create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';

--正常加载数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log' 
into table dept_partition2 
partition(day='20200401',hour='12');

--增加分区
alter table dept_partition2 add partition(day='20200403',hour='01') partition(day='20200403',hour = '02');

--删除分区
alter table dept_partition2 drop partition(day='20200403',hour='01'),partition(day='20200403',hour = '02');

分区表和元数据对应三种方式

--先上传 再修复表
msck repair table dept_partition2;
--先上传数据，后添加分区
hive (default)> alter table dept_partition2 add partition(day='20200401',hour='14');
--创建文件夹后直接load ,load数据的时候直接指定分区字段的值
hive (default)> dfs -mkdir -p
 /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=15;
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 partition(day='20200401',hour='15');

分桶表

--创建分桶表
create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

--查看分桶表信息
desc formatted stu_buck;

函数

常用内置函数

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

--空字段赋值  NVL (将null值转换成我们想要的值)
select sal,comm,sal+ NVL(comm,0) money from emp;
select ename,job,sal,mgr,comm,NVL(comm,mgr) from emp;

-- CASE WHEN THEN ELSE END
select
	dept_id,
	sum(case sex when '男' then 1 else 0 end) man,
	sum(case sex when '女' then 1 else 0 end) woman
from emp_sex
group by dept_id ;

常用函数

常用日期函数
date_format(dt,'yyyy-MM')
date_format() ：格式化日期   日期格式：'yyyy-MM-dd hh:mm:ss'   select date_format('2008-08-08 08:08:08','yyyy-MM-dd hh:mm:ss');  
unix_timestamp：返回当前或指定时间的时间戳，单位为秒	        
	select unix_timestamp(); --1625043327
	select unix_timestamp('2008-08-08 08:08:08'); --1596845288
from_unixtime：将时间戳转为日期格式                 
	select from_unixtime(1596845288); --2008-08-08 08:08:08
	select from_unixtime(1596845288,'yyyy-MM-dd hh:mm:ss'); --2008-08-08 08:08:08
current_date：当前日期                  select current_date();
current_timestamp：当前的日期加时间     select current_timestamp();
to_date：抽取日期部分                   select to_date('2008-08-08 08:08:08');   select to_date(current_timestamp());
year：获取年                            select year(current_timestamp());
month：获取月                           select month(current_timestamp());
day：获取日                             select DAY(current_timestamp());
hour：获取时                            select HOUR(current_timestamp());
minute：获取分                          select minute(current_timestamp());
second：获取秒                          select SECOND(current_timestamp());
weekofyear：当前时间是一年中的第几周    select weekofyear(current_timestamp());  select weekofyear('2020-01-08');
dayofmonth：当前时间是一个月中的第几天  select dayofmonth(current_timestamp());  select dayofmonth('2020-01-08');
months_between： 两个日期间的月份       select months_between('2020-07-29','2020-06-28');
add_months：日期加减月                  select add_months('2020-06-28',1);
datediff：两个日期相差的天数            select datediff('2019-03-01','2019-02-01');   select datediff('2020-03-01','2020-02-01');
date_add：日期加天数                    select date_add('2019-02-28',1);   select date_add('2020-02-28',1);
date_sub：日期减天数                    select date_sub('2019-03-01',1);   select date_sub('2020-03-01',1);
last_day：日期的当月的最后一天          select last_day('2020-02-28');   select last_day('2019-02-28');

next_day函数
	（1）取当前天的下一个周一
hive (gmall)> select next_day('2020-06-14','MO');
2020-06-15
说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）
（2）取当前周的周一
hive (gmall)> select date_add(next_day('2020-06-14','MO'),-7);
2020-06-8
4）last_day函数（求当月最后一天日期）
hive (gmall)> select last_day('2020-06-14');
2020-06-30

常用取整函数
round： 四舍五入     select round(4.5);     
ceil：  向上取整     select ceil(4.5);
floor： 向下取整     select floor(4.5);

常用字符串操作函数
upper： 转大写         select upper('abcDEFg');
lower： 转小写         select lower('abcDEFg');
length： 长度          select length('abcDEFg');
trim：  前后去空格     select length('   abcDEFg    ');  select length(trim('   abcDEFg    '));
lpad： 向左补齐，到指定长度   select lpad('abc',11,'*');
rpad：  向右补齐，到指定长度  select rpad('abc',11,'*');  
substring: 剪切字符串         select substring('abcdefg',1,3);     select rpad(substring('13843838438',1,3),11,'*');
regexp_replace： SELECT regexp_replace('100-200', '(\\d+)', 'num');   select regexp_replace('abc d e f',' ','');
	使用正则表达式匹配目标字符串，匹配成功后替换！

集合操作
size： 集合中元素的个数
map_keys： 返回map中的key
map_values: 返回map中的value         select size(friends),map_keys(children),map_values(children) from person;
array_contains: 判断array中是否包含某个元素     select array_contains(friends,'lili') from person;
sort_array： 将array中的元素排序         select sort_array(split('1,3,4,5,2,6,9',','));   
                                         select sort_array(split('a,d,g,b,c,f,e',','));

5.2 系统函数
5.2.1 nvl函数
1）基本语法
NVL（表达式1，表达式2）
如果1不为空，则取1，否则取2
如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。
该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。
2）案例实操
hive (gmall)> select nvl(1,0);
1
hive (gmall)> select nvl(null,"hello");
hello
5.2.2 日期处理函数（必会项）
1）date_format函数（根据格式整理日期）
hive (gmall)> select date_format('2020-06-14','yyyy-MM');
2020-06
2）date_add函数（加减日期）
hive (gmall)> select date_add('2020-06-14',-1);
2020-06-13
hive (gmall)> select date_add('2020-06-14',1);
2020-06-15

3）next_day函数
（1）取当前天的下一个周一
hive (gmall)> select next_day('2020-06-14','MO');
2020-06-15
hive (gmall)> select next_day('2020-06-15','Tuesday');
2020-06-16
说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）

（2）取当前周的周一（先取下一个周一，再-7），一般拿周一去计算
hive (gmall)> select date_add(next_day('2020-06-14','MO'),-7);
2020-06-8
4）last_day函数（求当月最后一天日期）
hive (gmall)> select last_day('2020-06-14');
2020-06-30                        

2）取出第一个json对象
hive (gmall)>
select get_json_object('[{"name":"大郎","sex":"男","age":"25"},{"name":"西门庆","sex":"男","age":"47"}]','$[0]');
结果是：{"name":"大郎","sex":"男","age":"25"}
3）取出第一个json的age字段的值
hive (gmall)>
SELECT get_json_object('[{"name":"大郎","sex":"男","age":"25"},{"name":"西门庆","sex":"男","age":"47"}]',"$[0].age");
结果是：25
get_json_object(line,'$.common.ar')

union关键字用来连接多个结果集，union和union all的区别在于：

​ union （去重）：对两个结果集进行并集操作，重复数据只显示一次；

​ Union All，对两个结果集进行并集操作，重复数据全部显示。

行转列 多行转一列

CONCAT(string A/col, string B/col…)：拼接函数。返回输入字符串连接后的结果，支持任意个输入字符串。

CONCAT_WS(separator, str1, str2,…)：拼接函数。第一个参数为分隔符。

拼接函数后面只能跟 "string or array"类型

COLLECT_SET(col)：函数只接受基本数据类型，是将某字段的值进行去重汇总，产生array类型字段。

COLLECT_LIST(col)：函数只接受基本数据类型，是将某字段的值进行不去重汇总，产生array类型字段。

需求：把星座和血型一样的人归类到一起

[atguigu@hadoop102 datas]$ vi constellation.txt
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A
苍老师	白羊座	B
------------结果如下-----------
射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋|苍老师
--------------创建hive表并导入数据-----------------
create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive/datas/constellation.txt" into table person_info;
-------------------------------------------------
SELECT 
	t1.c_b,
	concat_ws('|',COLLECT_LIST(t1.name))
FROM 
(
	select 
		name,
		concat_ws(',',constellation,blood_type) c_b 
	from person_info
) t1
group by t1.c_b;

列转行 一列转多行

Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array。

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

lateral view （侧写视图）

需求：将电影分类中的数组数据展开

[atguigu@hadoop102 datas]$ vi movie_info.txt
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难
--------------结果如下-----------------
《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难
--------------创建hive表并导入数据-----------------
create table movie_info(
    movie string, 
    category string) 
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive/datas/movie_info.txt" into table movie_info;
-------------------------------------------------
select
	movie,
	category_name
from movie_info
LATERAL VIEW explode(split(category,',')) tmp as category_name;

窗口函数

相关函数说明

LEAD(col,n, default_val)：往后第n行数据 col 列名 n 往后第几行 默认为1 默认值 默认null

LAG(col,n,default_val)：往前第n行数据 col 列名 n 往前第几行 默认为1 默认值 默认null

FIRST_VALUE （col,true/false） 在当前窗口下的第一个值 如果设置为true，则跳过空值。

FIRST_VALUE （col,true/false） 在当前窗口下的最后一个值 如果设置为true，则跳过空值。

NTILE(n)：平均分n组，从1到n组，每组的编号相同。注意：n必须为int类型。

排名函数

RANK() 排序相同时会重复，会跳号

DENSE_RANK() 排序相同时会重复，不会跳号

ROW_NUMBER() 会根据顺序计算

标准聚合函数：

• COUNT、SUM、MIN、MAX、AVG

分析排名函数

• RANK、ROW_NUMBER、DENSE_RANK、NTILE

窗口函数：（mysql从8.0版本才支持窗口函数）

窗口函数=函数+窗口

窗口：函数在运算时，我们可以指定函数运算的数据范围

2）语法

窗口函数 over([partition by 字段] [order by 字段] [ 窗口语句])

partition by 给查出来的结果集按照某个字段分区，分区以后，开窗的大小最大不会超过分区数据的大小

一旦分区之后，我们必须在单个分区内指定窗口。

order by 给分区内的数据按照某个字段排序

3. 窗口语句

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

两种特殊情况

1、当指定ORDER BY时，WINDOW规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

2、当缺少ORDER BY时，则WINDOW规范默认为ROW BETWEENUND UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING。

都没有时默认：row between unbounded preceding and unbounded following
有order by 时:range between unbounded preceding and current row

以下函数在over（）里面只能分区和排序，不能自定义窗口大小了，也就是不能再写window字句

1、排序分析函数 ，例如： Rank, NTile, (DenseRank, CumeDist, PercentRank) 后面这三个还没用过

2、Lead 和 Lag不能写

窗口需求

需求1 查询在2017年4月份购买过的顾客及总人数

select  
	name,
	count(1) over(rows between UNBOUNDED  PRECEDING and UNBOUNDED FOLLOWING)
from business
where month(orderdate) =4
group by name;

由于窗口语句有两种特殊情况，我们这种刚好符合第二种，因此可以省略掉窗口语句

select  
	name,
	count(1) over()
from business
where month(orderdate) =4
group by name;

需求2 查询顾客的购买明细及月购买总额

SELECT 
	name,
	orderdate,
	cost,
	sum(cost) over(partition by name,month(orderdate) )
from business;

需求3 上述的场景, 将每个顾客的cost按照日期进行累加

SELECT 
	name,
	orderdate,
	cost,
	sum(cost) over(partition by name order by orderdate rows between UNBOUNDED  PRECEDING and CURRENT ROW) cost1,
	sum(cost) over(partition by name order by orderdate) cost2
from business;

需求4 查询顾客购买明细以及上次的购买时间和下次购买时间

select
	name,
	orderdate,
	cost,
	LAG(orderdate,1,'无') over(partition by name order by orderdate) prev_time,
	LEAD(orderdate,1,'无') over(partition by name order by orderdate) next_time
from business;

需求5 查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间

注意：LAST_VALUE和FIRST_VALUE 需要自定义windows字句，否则出现错误

select
	name,
	orderdate,
	cost,
	FIRST_VALUE(orderdate) 
	over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED  PRECEDING and UNBOUNDED FOLLOWING) first_time,
	LAST_VALUE(orderdate) 
	over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED  PRECEDING and UNBOUNDED FOLLOWING) last_time
from business;

需求6 查询前20%时间的订单信息

select
	t1.*
FROM 
(
	select
		name,
		orderdate,
		cost,
		ntile(5) over(order by orderdate ) nsort
	from business
) t1
where t1.nsort = 1;

排名函数

RANK() 排序相同时会重复，会跳号

DENSE_RANK() 排序相同时会重复，不会跳号

ROW_NUMBER() 会根据顺序计算

SELECT 
	name,
	subject,
	score,
	rank() over(PARTITION by subject order by score desc) rp,
	DENSE_RANK() over(PARTITION by subject order by score desc) drp,
	ROW_NUMBER() over(PARTITION by subject order by score desc) rowp
from score;

压缩和存储

9.1压缩编码

压缩格式	算法	文件扩展名	是否可切分
DEFLATE	DEFLATE	.deflate	否
Gzip	DEFLATE	.gz	否
bzip2	bzip2	.bz2	是
LZO	LZO	.lzo	是
Snappy	Snappy	.snappy	否

压缩格式	对应的编码/解码器
DEFLATE	org.apache.hadoop.io.compress.DefaultCodec
gzip	org.apache.hadoop.io.compress.GzipCodec
bzip2	org.apache.hadoop.io.compress.BZip2Codec
LZO	com.hadoop.compression.lzo.LzopCodec
Snappy	org.apache.hadoop.io.compress.SnappyCodec

9.2 Hadoop压缩参数配置

​ 要在Hadoop中启用压缩，可以配置参数（mapred-site.xml文件中）

9.3 开启Map输出阶段压缩（MR引擎）

​ 开启map输出阶段压缩可以减少job中map和Reduce task间数据传输量。具体配置如下：

（1）开启hive中间传输数据压缩功能
hive (default)>set hive.exec.compress.intermediate=true;
（2）开启mapreduce中map输出压缩功能
hive (default)>set mapreduce.map.output.compress=true;
（3）设置mapreduce中map输出数据的压缩方式
hive (default)>set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
（4）执行查询语句
	hive (default)> select count(ename) name from emp;

9.4 开启Reduce输出阶段压缩

​ 当Hive将输出写入到表中时，输出内容同样可以进行压缩。

（1）开启hive最终输出数据压缩功能
hive (default)>set hive.exec.compress.output=true;
（2）开启mapreduce最终输出数据压缩
hive (default)>set mapreduce.output.fileoutputformat.compress=true;
（3）设置mapreduce最终数据输出压缩方式
hive (default)> set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;
（4）设置mapreduce最终数据输出压缩为块压缩（一般不写）
hive (default)> set mapreduce.output.fileoutputformat.compress.type=BLOCK;
（5）测试一下输出结果是否是压缩文件
hive (default)> insert overwrite local directory '/opt/module/hive/datas/compress/' select * from emp distribute by deptno sort by empno desc;

9.5 文件存储格式

​ Hive支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET。

列式存储和行式存储

​ TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；

​ ORC和PARQUET是基于列式存储的。

TextFile格式

Orc格式

​ 默认是每隔1W行做一个索引

Parquet格式

​ Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。

​ 可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。

​ 在Parquet中，有三种类型的页：数据页、字典页和索引页。

存储和压缩结合

ORC存储方式的压缩：

Key	Default	Notes
orc.compress	ZLIB	high level compression (one of NONE, ZLIB, SNAPPY)
orc.compress.size	262,144	number of bytes in each compression chunk
orc.stripe.size	268,435,456	number of bytes in each stripe
orc.row.index.stride	10,000	number of rows between index entries (must be >= 1000)
orc.create.index	true	whether to create row indexes
orc.bloom.filter.columns	“”	comma separated list of column names for which bloom filter should be created
orc.bloom.filter.fpp	0.05	false positive probability for bloom filter (must >0.0 and <1.0)

注意：所有关于ORCFile的参数都是在HQL语句的TBLPROPERTIES字段里面出现

创建一个SNAPPY压缩的ORC存储方式

create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc
tblproperties("orc.compress"="SNAPPY");

创建一个SNAPPY压缩的parquet存储方式

create table log_parquet_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as parquet
tblproperties("parquet.compression"="SNAPPY");

存储方式和压缩总结

在实际的项目开发当中，hive表的数据存储格式一般选择：orc或parquet。压缩方式一般选择snappy，lzo。

Hive调优

本地模式

set hive.exec.mode.local.auto=true;  //开启本地mr
//设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;

表的优化

1、小表、大表Join：数据量小的表放在join的左边

2、大表Join大表：

​ 空KEY过滤

​ 空key转换：为空的字段赋一个随机的值，nvl(n.id,rand())

3、MapJoin

​ 如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理。

开启MapJoin参数设置

（1）设置自动选择Mapjoin

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

（2）大表小表的阈值设置（默认25M以下认为是小表）：

set hive.mapjoin.smalltable.filesize=25000000;