HIVE基础

笔记：
1、Hive概念
	1、1 Hive是什么？
	重点记忆：Hive的本质是 --- 将HQL转化成MapReduce程序
	1、2优缺点
	补充：粒度，对于文件（数据）描述的越详细，粒度越细，如文件描述到 年-月-日 时-分-秒，比年-月粒度细
	1、3 Hive 架构原理（可能面试问）
	--- 四个器的作用
```# hive执行顺序

1）SQL的书写顺
SELECT- DISTINCT- FROM- JOIN ON- WHERE- GROUP BY- HIVING- ORDER BY- LIMIT

2）真正执行的顺序：
随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：
FROM- ON- JOIN- WHERE- GROUP BY- HAVING- SELECT- DISTINCT- ORDER BY- LIMIT
3）外连接
外连接确定主从表:  左外连左主右从， 右外连右主左从！
外连接的结果集:  主表取所有，从表取匹配. 主表与从表未匹配的数据通过null来补全.

第一章.hive的基本概念

1.1hive是什么

​ Hive：由 Facebook 开源用于解决海量结构化日志的数据统计工具。

​ hive是一个hadoop的数据仓库工具,它可以将hdfs上的结构化的数据映射成一张表,并提供了类sql(HQL)语法来分析数据

test
    tel        up   down
13855554444	  134   1568
13855554445   134   1566
13855554446   133   1565
13855554447   135   1561
13855554448   137   1568
13855554449   139   1568

按照每一行手机号 求一个上行流量和下行流量的和

select tel,up+down from test;

Hive其实是一个hadoop客户端,它本身不存储任何数据,它的数据存在hdfs上,hive能给这些在hdfs上数据加上元数据,元数据存在关系型数据库里(derby ,一般会选择把元数据存在mysql)

元数据:描述数据的数据(表名,字段名,类型,hdfs的路径)

hive的本质是将hql转化成mapreduce程序

执行程序运行在Yarn上，在启动Hive之前需要起hadoop(yarn)

Hive是写sql的工具,但是依赖hadoop

1.2 hive的优缺点

1.优点:

​ （1）hive提供了类sql语法,提供快速开发的能力（简单、容易上手）

​ （2）避免了去写MapReduce，减少开发人员的学习成本。

​ （3）Hive优势在于处理大数据，支持海量数据的分析与计算。

​ （4）Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

2.缺点:

​ 1）Hive的HQL表达能力有限

​ （1）迭代式算法无法表达

​ （2）数据挖掘方面不擅长，由于MapReduce数据处理流程的限制，效率更高的算法却无法实现。

​ 2）Hive的效率比较低

​ （1）Hive自动生成的MapReduce作业，通常下不够智能化（mr能实现的hive不一定能实现）

​ （2）Hive调优比较困难，粒度比较粗（只能sql上调优，更多调优还是hadoop方面调优）

​ 3）Hive不支持实时查询和行级别更新

（1）hive分析的数据是存储在hdfs上，hdfs不支持随机写，只支持追加写，所以在hive中不能update和delete,能select和insert

1.3 hive的架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5GXpIu9x-1633939708823)(D:\JavaStudy\note\hive\picture\002.jpg)]

-- 1. 用户接口：Client
	CLI（命令行）、JDBC/ODBC(jdbc访问hive)、WEBUI（浏览器访问hive）
-- 2. 元数据：Metastore
	元数据包括：
	   a、表名
	   b、表所属的数据库（默认是default）
	   c、表的拥有者
	   d、列/分区字段
	   e、表的类型（是否是外部表）、
	   f、表的数据所在目录等；
	'默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore'
-- 3. Hadoop
	使用HDFS进行存储，使用MapReduce进行计算。
-- 4. 驱动器：Driver
	1. '解析器'（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，
	   比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
    2. '编译器'（Physical Plan）：将AST编译生成逻辑执行计划。
    3. '优化器'（Query Optimizer）：对逻辑执行计划进行优化。
    4. '执行器'（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。

运行机制

创建的时候,会将你创建表的元数据存在元数据库中,如果hdfs上没有表的文件夹(会在hdfs上创建一个)并且会对应在hdfs上去找对应的数据

查询的时候,会先去找你的元数据，再去找hdfs上的数据,最终翻译mr运行

1.4 Hive和数据库区别

1.hive不是数据库 不是数据库 不是数据库 他们虽然都是用sql开发

数据延迟 数据规模 执行引擎 数据存储

第二章.安装

2.1hive的常用交互命令

常用在脚本里面,跑一些半夜可能需要跑的任务

1 hive -e 能执行一条命令行的sql
hive -e 'select *  from stu'
2. hive -f 能够执行sql文本 
hive -f hive.sql

2.2hive的参数配置方式

hive查看参数的方式

在hive/beeline 使用 set; 能够查看所有的配置项
在hive/beeline 使用 set 参数名 能查看指定参数名的配置项

修改参数配置的方式

1.永久生效

在你的Hive/conf下面所有的文件都可以改hive的参数  hive-site.xml hive-env.sh hive-log4j2.properties

2.临时生效(对单次客户端生效)

hive -hiveconf 参数名=参数值
beeline -u jdbc:hive2://hadoop102:10000 -n atguigu -hiveconf 参数名=参数值

3.临时生效(对单次客户端生效)

在hive/beeline 里面使用 set 参数名=参数值  

他们的优先级是按照 1 2 3 的顺序依次增大的

第三章.hive的数据类型

3.1 基本数据类型

HIVE	MySQL	JAVA	长度	例子
TINYINT	TINYINT	byte	1byte有符号整数	2
SMALINT	SMALINT	short	2byte有符号整数	20
INT	INT	int	4byte有符号整数	20
BIGINT	BIGINT	long	8byte有符号整数	20
BOOLEAN	无	boolean	布尔类型，true或者false	TRUE FALSE
FLOAT	FLOAT	float	单精度浮点数	3.14159
DOUBLE	DOUBLE	double	双精度浮点数	3.14159
STRING	VARCHAR	string	字符系列。可以指定字符集。可以使用单引号或者双引号。	‘now is the time’ “for all good men”
TIMESTAMP	TIMESTAMP		时间类型
BINARY	BINARY		字节数组

对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

做一个数据类型测试

create table person(id int ,name string,weight double,money bigint);
insert into person values(1,'qiangge',80,999999999999);

3.2 集合数据类型

数据类型	描述	语法示例
STRUCT	和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。2	struct()例如struct<street:string, city:string>
MAP	MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素	map()例如map<string, int>
ARRAY	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。	Array()例如array

​ Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

集合数据类型测试

（1）假设某表有如下一行，我们用JSON格式来表示其数据结构。在Hive下访问的格式为

{
    "name": "songsong",
    "friends": ["bingbing" , "lili"],       //列表Array, 
    "children": {                              //键值Map,
        "xiao song": 18,
        "xiaoxiao song": 19
    }
    "address": {                              //结构Struct,
        "street": "hui long guan", 
        "city": "beijing",
        "email":10010
    }
}

2）基于上述数据结构，我们在Hive里创建对应的表，并导入数据。

创建本地测试文件test.txt

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing_10010
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing_10011

注意：MAP，STRUCT和ARRAY里的元素间关系都可以用同一个字符表示，这里用“_”。

（3）Hive上创建测试表test

create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string,city:string,email:int>
)
row format delimited fields terminated by ',' -- 列分隔符
collection items terminated by '_' -- MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':' -- MAP中的key与value的分隔符
lines terminated by '\n'; -- 行分隔符

（4）导入文本数据到测试表

 load data local inpath '/opt/module/hive/datas/test.txt' into table test;

（5）访问三种集合列里的数据，以下分别是ARRAY，MAP，STRUCT的访问方式

select friends[1],children['xiao song'],address.city from test
where name="songsong";
-- 结果
_c0     _c1     city
lili    18      beijing
Time taken: 0.076 seconds, Fetched: 1 row(s)

3.3 Hive 里面的类型转换

​ Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。

1）隐式类型转换规则如下

（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。
（2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
（3）TINYINT、SMALLINT、INT都可以转换为FLOAT。 
（4）BOOLEAN类型不可以转换为任何其它的类型。

2）可以使用CAST操作显示进行数据类型转换

​ 例如CAST(‘1’ AS INT)将把字符串’1’ 转换成整数1；如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。

select '1'+2, cast('1'as int) + 2;
+------+------+--+
| _c0  | _c1  |
+------+------+--+
| 3.0  | 3    |
+------+------+--+

第四章.DDL(数据定义语言)

4.1 库的ddl

4.1.1 创建库的语法

CREATE DATABASE [IF NOT EXISTS] database_name      --创建数据库 if not exists 加强健壮性
[COMMENT database_comment]                         --注释  解释当前干什么事的          
[LOCATION hdfs_path]                               --指定当前库在hdfs上对应的文件夹    
[WITH DBPROPERTIES (property_name=property_value, ...)]; --库的属性和值，但是一点用没有(鸡肋)

4.1.2 增

create database db_hive
comment 'this in my first db'
with dbproperties('dbtype'='hive','owner'='atguigu');

create database db_hive2
location '/db_hive2';

create database db_hive3
location '/dsadsadsasd';

create database if not exists db_hive2
location '/db_hive2';

--在你不指定的location的情况下 默认在你的hdfs/user/hive/warehouse下创建一个以database_name.db名的文件夹 来当做库
--在你指定location的情况下 拿最后一级目录当做库的名字

4.1.3 查

--展示所有的数据库
show databases; 
--模糊展示
show databases like 'db_hive*'
--描述数据库    --不会展示 库的属性 dbproperties
desc database 库名
desc database db_hive; 
--描述数据库详情 --会展示 库的属性  dbproperties parameters
desc database extended 库名;
desc database extended db_hive;

4.1.4 切换数据库

use 库名
use db_hive2;

4.1.5 改

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

alter database db_hive set dbproperties('dbtype'='db');  --修改原来的属性
alter database db_hive set dbproperties('createtime'='2020-08-19');  --增加原来的属性

4.1.6 删

drop database 库名
drop database db_hive2;

drop database if exists db_hive2; --加上 if exists 增加代码的健壮性

drop database db_hive cascade; --强制删除 (当你库下面有表的时候) 慎用(只有你确定所有表都没用的时候)

4.2 表的ddl

4.2.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             --当前表对应的数据的分隔符
 [FIELDS TERMINATED BY char]     --字段分隔符  
  有默认值  对应ascii码表0001号位 ^A  怎么敲出来的ctrl+v ctrl+a
  songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing_10010
  对于上一行数据 分隔符就是 ','
 [COLLECTION ITEMS TERMINATED BY char] --集合元素分隔符（array map struct）
  有默认值  对应ascii码表0010号位 ^B  怎么敲出来的ctrl+v ctrl+b
    bingbing_lili  xiao song:18_xiaoxiao song:19 hui long guan_beijing_10010
  对于上述集合 分隔符是'_'
 [MAP KEYS TERMINATED BY char]         --map的kv分隔符
  有默认值  对应ascii码表0011号位 ^C  怎么敲出来的ctrl+v ctrl+c
  xiao song:18
  对于上述kv而言 分隔符是 ":"
 [LINES TERMINATED BY char]            --各行数据分隔符  
  有默认值'\n'
] 
[STORED AS file_format]     --当前表所对应的数据的类型 textfile
[LOCATION hdfs_path]        --当前表所对应的hdfs路径
[TBLPROPERTIES (property_name=property_value, ...)] --表的属性 有大用
[AS select_statement]       --根据查询结果创建一张表
[LIKE table_name]           --模仿一张表

4.2.2 增

4.2.2.1 增加内部表(管理表)

内部表的含义:hive掌握着表的数据的生命周期,当在Hive里删除表的时候,会一并把hdfs上数据给删了
用的少  1.中间表 2.测试表
--内部表测试
create table student(id int, name string)
row format delimited fields terminated by '\t'
--默认分隔符测试
create table test2(id int, name string)
--根据查询结构创建一张表  它虽然会带表结构和数据 但是分隔符不会带 会使用默认值
create table student2 as select * from student;
--根据查询结构创建一张表  创建一张相同分隔符的
create table student3 row format delimited fields terminated by '\t' as select * from student;
--根据存在的表的结构来创建一张表  拿不到数据 --他的分隔符跟模仿表的是一样的
create table student4 like student;

4.2.2.2 增加外部表

外部表的含义:hive不掌握着表的数据生命周期,当在Hive里删除表的时候,不会一并把hdfs上数据给删了，只会删除元数据
除了上述内部表的情况 全是外部表
create external table if not exists dept(
deptno int,
dname string,
loc int)
row format delimited fields terminated by '\t'
location '/company/dept';

create external table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t'
location '/company/emp/';

4.2.2.3 内部表和外部表相互转换

Table Type:            EXTERNAL_TABLE       
Table Parameters:      EXTERNAL            TRUE 
表是否为内部表还是外部表是由Table Parameters 里面的EXTERNAL属性来控制 包括TRUE和FALSE 都得大写
--内部表转换成外部表
alter table student4 set tblproperties('EXTERNAL'='TRUE');
--外部表转成内部表
alter table emp set tblproperties('EXTERNAL'='FALSE');

4.2.3 查

--展示库下面的所有表
show tables;
--描述表
desc student;
--描述表的详情
desc formatted student;

4.2.4 删

1.删除表
1.1 删除内部表 
drop table student;
1.2 删除外部表
drop table dept;  --只能删除元数据 不能删除hdfs上的数据
1.3 清空表
truncate table student3;
清空外部表测试      --不能清空外部表
truncate table emp;

4.2.5 改

-- 改表名  会连同你的hdfs文件夹名字一起改掉 新表表名不能存在
alter table student3 rename to student2;
-- 更新列  注意改的列的数据类型 只能由小往大改 或者不变
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
alter table stu2 change column id id int;
alter table stu2 change column id id tinyint; --这是错的
alter table stu2 change column id ids bigint;
alter table stu2 change column id idss bigint;
-- 增加列
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...) 
alter table stu2 add columns(weight double,hair bigint);
-- 替换列 针对表操作表示替换表中所有字段
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 
-- 替换之减少列  如果你想替换时候较少列 那么你减少后剩余部分 应该和之前字段 满足类型的大小关系
alter table stu2 replace columns (id bigint , name string);
-- 替换之增加列  增加部分可以没有类型大小的关系 ，如果有对应的部分则满足类型大小的对应关系
alter table stu2 replace columns (id bigint , name string , height double, hair bigint);

​ 注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段，REPLACE使用的时候，字段的类型要跟之前的类型对应上，数量可以减少或者增加，其实就是包含了更新列，增加列，删除列的功能。

第五章.DML（数据操作语言）

5.1 数据导入

5.1.1 load 装载数据

load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
测试表
create table student (id int ,name string) row format delimited fields terminated by '\t';
-- load 数据之追加数据   本地导入 是复制进去的
load data local inpath '/opt/module/hive/datas/student.txt' into table student;
-- load 数据之覆盖数据
load data local inpath '/opt/module/hive/datas/student1.txt' overwrite into table student;
-- load 数据之hdfs导入  hdfs导入时剪切进去的
load data  inpath '/student.txt' into table student;

5.1.2 insert 插入数据

-- 追加插入
insert into table student2 values(1,'banzhang'),(2,'haiwangbin');
-- 覆盖插入
insert overwrite table student values(1,'banzhang'),(2,'haiwangbin');
-- 查询插入  --注意:第一你所插入的表必须存在 然后你查询的字段必须满足目标表的里的字段数
insert into table student  select id,name from student3;
-- 查询覆盖
insert overwrite table student  select id,name from student3;

5.1.3 as select

create table if not exists student3
as select id, name from student;

create as select, insert into table table_name select
-- 这两个就是拿来创建中间表
 

5.1.4 location

create  table if not exists student4(
id int, name string
)
row format delimited fields terminated by '\t'
location '/student4';
-- 指定location 必须是文件夹

5.1.5 Import数据到指定Hive表中

注意：先用export导出后，再将数据导入。并且因为export导出的数据里面包含了元数据，因此import要导入的表不可以存在，否则报错。

import table student6 from '/user/hive/warehouse/export/student'

5.2 数据导出(少)

5.2.1 insert 导出

--无格式导出
insert overwrite local directory '/opt/module/hive/datas/export/student1' select * from student;
--有格式导出
insert overwrite local directory '/opt/module/hive/datas/export/student1' row format delimited fields terminated by '\t' select * from student;
--没有local 写在hdfs上
insert overwrite  directory '/opt/module/hive/datas/export/student1' row format delimited fields terminated by '\t' select * from student;

5.2.2 hadoop 下载

hadoop fs  -get /user/hive/warehouse/student/student.txt
/opt/module/hive/datas/export/student3.txt;

5.2.3 hive 的shell命令

hive -e 'select * from default.student;' > /opt/module/hive/datas/export/student4.txt

5.2.4 export 导出

export table student to '/student';

第六章 查询

6.1 查询简介

SELECT [ALL | DISTINCT] select_expr, select_expr, ...   distinct 对结果集去重
  FROM table_reference                                  从xxx表查询
  [WHERE where_condition]                               过滤条件
  [GROUP BY col_list]                                   以xxx分组
   [HAVING col_list]                                    分组后过滤
  [ORDER BY col_list]                                   全局排序
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]       hive 里的4个by
  ]
 [LIMIT number]                                        限制输出的行数 翻页
 
 
 select
  count(*)
 from  join  where group by having order by limit

 from<join<where<group by <count(*)<having<select<order by <limit sql的执行顺序

数据含义讲解
create table if not exists emp(
empno int,      --员工编号
ename string,   --员工姓名
job string,     --员工职位
mgr int,        --员工领导
hiredate string,--员工的入职日期 
sal double,     --员工的薪资
comm double,    --员工的奖金
deptno int)     --员工的部门编号
row format delimited fields terminated by '\t';

select
 empno id,
 ename name
from emp e

6.2 group by

计算emp表每个部门的平均工资

select
 deptno,
 avg(sal)
from 
  emp
group by
  deptno

计算emp每个部门中每个岗位的最高薪水

select
 deptno,
 job,
 max(sal)
from 
   emp
group by
   deptno,job

求每个部门的平均薪水大于2000的部门

select
 deptno,
 avg(sal) avg_sal
from emp
group by deptno
having avg_sal>2000;

6.3 join 连接多张表

根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；

select
e.deptno
from emp e join dept d
on e.deptno=d.deptno

select
*
from emp e join dept d
on e.deptno!=d.deptno

左外连接

select
*
from emp e left join dept d
on e.deptno=d.deptno


select
e.*,
d.*
from dept d left join emp e
on d.deptno=e.deptno

右外连接
select
*
from emp e right join dept d
on e.deptno=d.deptno


满外连接
select
 e.*,
 d.*
from emp e full join dept d
on e.deptno =d.deptno

--在Mysql里面的实现方式
select
*
from dept d left join emp e
on d.deptno=e.deptno
union
select
*
from dept d right join emp e
on d.deptno=e.deptno

union 竖向拼接两张表  可以将相同数据去重
union all 竖向拼接两张表  直接拼接不去重
union all 效率更高 union往往是我们想要的结果

要 员工姓名，部门名称，位置名称
--多表连接
select
 e.ename,
 d.dname,
 l.loc_name
from emp e join dept d
on e.deptno=d.deptno
join location l
on d.loc=l.loc

select
 e.ename,
 d.dname,
 l.loc_name
from emp e join dept d join location l
on e.deptno=d.deptno and d.loc=l.loc

-- 笛卡尔积(千万注意)
select * from dept join emp;
select * from dept,emp;
select * from dept join emp on 1=1;

6.4排序

order by  全局排序  只会起一个reducer对你结果集进行

--按照人员的薪资排序
select 
*
from emp
order by sal desc

asc 升序 (默认)
desc 倒序

-- 按照部门的人员薪资排序
select
 *
from emp
order by deptno,sal

select
 *
from emp
order by deptno desc ,sal desc

select
ename,
sal,
comm,
sal+comm
from emp;


--distribute by （分区：hash分区） and sort by（区内排序）
insert overwrite local directory '/opt/module/hive/datas/distribute-result'
select
*
from
emp 
distribute by cast(deptno/10 as int) sort by sal desc

-- 当distribute by和sort by字段相同时，可以使用cluster by方式。
--cluster by   分区排序 只能是升序 不能指定排序规则为ASC或者DESC
select * from
emp cluster by deptno;

order by 表示全局排序
distribute by(分区) sort by（区内排序）  他两是在一起使用
cluster by（既分区又排序）  是distribute by sort by 相同字段的时候可以简写  但是用的少

第七章 分区和分桶

7.1分区表

Hive里有个很大毛病 它没有索引 ,它每次扫描都只能扫描全表

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

load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition ;  -- 错误的 (虽然能运行)

load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');

load data local inpath '/opt/module/hive/datas/dept_20200402.log' into table dept_partition partition(day='20200402');

load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition partition(day='20200403');

分区表
其实就是在分文件夹 , 但是他又可以当做一个列来使用，帮助我们定位数据位置,不需要再暴力扫描全表了
创建的时候 分区字段一定不能是表里面存在的列
create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (deptno string)
row format delimited fields terminated by '\t';

7.1.1 增

alter table dept_partition add partition(day = '20200404');
-- （多个分区间用空格）
alter table dept_partition add partition(day = '20200405') partition(day='20200406');

7.1.2 查

show partitions dept_partition;
show partitions dept;  --不能查一个不是分区表的表
desc dept_partition;
desc formatted dept_partition;

7.1.3 删（对于外部表 只能删分区的元数据信息 hdfs文件夹会保留）

alter table dept_partition drop partition(day = '__HIVE_DEFAULT_PARTITION__');
-- （多个分区间用逗号）
alter table dept_partition drop partition(day='20200405'),partition(day='20200406')

7.1.4 二级分区

导入数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401',hour = '13');

load data local inpath '/opt/module/hive/datas/dept_20200402.log' into table
dept_partition2 partition(day='20200401',hour = '14');

load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table
dept_partition2 partition(day='20200402',hour = '13');

--增
alter table dept_partition2 add partition(day = '20200402',hour='13') ;

--删
alter table dept_partition2 drop partition(day = '20200401') ;

7.1.5 让分区表和下面的分区文件夹产生关系三种方式

1.修复(能够自动扫描对应的表的文件夹下面符合规则的文件夹并添加元数据)
msck repair table dept_partition2;
2.手动添加一个分区信息
alter table dept_partition2 add partition(day = '20200404',hour='13') ;
3.load 数据到一个指定分区里面
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200405',hour='13');

7.1.6动态分区（能够根据数据中的最后的列 来放到不同分区目录下）

在hive 2.x版本 动态分区是不能拿load来做  查询后插入
insert into table dept_partition partition(day) select deptno,dname,loc,day from dept1;
在hive 3.x版本 动态分区是直接拿load来做  优化
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition
-- 二级分区
create table dept_partition_dy2(id int) partitioned by (name string,loc int) row format delimited fields terminated by '\t';

load data local inpath '/opt/module/hive/datas/dept.txt' into table dept_partition_dy2;

insert into table dept_partition_dy2 partition(name,loc)  select deptno, dname,loc from dept;
但是要记住 严格模式 是在你指定partition 时候才有效果

7.2分桶（分的是你具体的数据）

​ 分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

分区针对的是数据的存储路径；分桶针对的是数据文件。

分桶规则：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

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

7.2.1创建一个又分区又分桶的表

create table stu_buck_part(id int, name string)
partitioned by (day string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

load data  inpath '/student.txt' into table stu_buck_part partition(day = '20201109')
                                                                    

7.2.2分区和分桶的区别

1. 分区分的是目录  分桶分的是具体的数据
2. 分区字段必不能再创建表的字段里 分桶字段必在创建的字段里

第八章 函数

1. 显示系统自带所有函数
show functions;
2. 描述指定函数的作用
desc function 函数名;
3. 描述函数的详情信息(一般是都是有例子的)
desc function extended 函数名;
4. UDF:一进一出  UDAF：多进一出  UDTF：一进多出
	这里面指的是输入数据的行数

8.1 NVL

1.将奖金为null的人奖金替换0
select ename,comm,nvl(comm,0) from emp;
2.按照奖金和工资的和 降序排序
select
  ename,
  sal,
  comm,
  nvl(comm,0),
  sal+nvl(comm,0) s_n
from emp
  order by s_n desc;


3.当奖金为Null时 用领导id 替代
select
 ename,
 comm,
 sal,
 nvl(comm,nvl(mgr,0))
from emp

8.2 case when

1.不管格式
select
 dept_id,
 sex,
 count(*)
from emp_sex
group by dept_id,sex

+----------+------+------+
| dept_id  | sex  | _c2  |
+----------+------+------+
| A        | 女    | 1    |
| A        | 男    | 2    |
| B        | 女    | 2    |
| B        | 男    | 1    |

2.格式
dept_Id     男       女
A     		2       1
B     		1       2

+----------+------+------+
| dept_id  | sex  | sex  |
+----------+------+------+
| A        | 男    | 男    |
| A        | 男    | 男    |
| B        | 男    | 男    |
| A        | 女    | 女    |
| B        | 女    | 女    |
| B        | 女    | 女    |
+----------+------+------+
select
  dept_id,
  case sex when '男' then 1 else 0 end male,
  case sex when '女' then 1 else 0 end female
from emp_sex
----------+-------+---------+
| dept_id  | male  | female  |
+----------+-------+---------+
| A        | 1     | 0       |
| A        | 1     | 0       |
| B        | 1     | 0       |
| A        | 0     | 1       |
| B        | 0     | 1       |
| B        | 0     | 1       |
+----------+-------+---------+
select
 t1.dept_id,
 sum(t1.male) male,
 sum(t1.female)female
from (
  select
  dept_id,
  case sex when '男' then 1 else 0 end male,
  case sex when '女' then 1 else 0 end female
from emp_sex
)t1
group by t1.dept_id

select
 dept_id,
 sum( case sex when '男' then 1 else 0 end) male,
 sum(case sex when '女' then 1 else 0 end) female
from emp_sex
group by dept_id
-+---------+
| dept_id  | male  | female  |
+----------+-------+---------+
| A        | 2     | 1       |
| B        | 1     | 2       |
+----------+-------+---------+
select
  dept_id,
  sum(if(sex='男',1,0)) male,
  sum(if(sex='女',1,0)) female
from emp_sex
group by dept_id
+----------+-------+---------+
| dept_id  | male  | female  |
+----------+-------+---------+
| A        | 2     | 1       |
| B        | 1     | 2       |
-- 支付
case pay when '支付宝' then 1 when '微信' then 2  when '信用卡 ' then 3 else 0 end

8.3 行转列

1. concat  
-- 回输入字符串连接后的结果，支持任意个输入字符串 传入多列数据，输出一列
select concat(empno,'-',ename,'-',sal,'-',deptno) from emp;

2.concat_ws CONCAT_WS must be "string or array<string>"

select concat_ws('-',cast(empno as string),ename,cast(sal as string)) from emp;
--按组统计 每个组有多少人 分别是谁
select
 deptno,
 count(*),
 collect_list(ename)
from emp
group by deptno
-- 对emp_sex这张表 需要如下统计
a   悟空 大海 凤姐
b   宋宋 婷姐 婷婷

3.collect_set（对结果集去重）
-- 函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段 传入一列多行数据，输出一行
select
 dept_id,
 collect_set(name)
from emp_sex
group by dept_id

4.collect_list（不对结果集去重） 
-- 函数只接受基本数据类型，它的主要作用是将某字段的值进行不去重汇总，产生array类型字段。
--两步
select
 name,
 concat(constellation,',',blood_type) c_b
from person_info

select
 t1.c_b,
 concat_ws("|",collect_list(t1.name))
from (
   select
    name,
    concat(constellation,',',blood_type) c_b
   from person_info 
)t1
group by t1.c_b

-- 一步写完
select
  concat(constellation,',',blood_type) ,
  concat_ws("|",collect_list(name))
from person_info
group by concat(constellation,',',blood_type) 
+--------+----------+
|  _c0   |   _c1    |
+--------+----------+
| 射手座,A  | 大海|凤姐 |
| 白羊座,A  | 孙悟空|猪八戒 |
| 白羊座,B  | 宋宋|苍老师 |
+--------+----------+

8.4 列转行

Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array。
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW

--尝试炸开
select
movie,
EXPLODE(split(category,',')) 
from movie_info
| col  |
+------+
| 悬疑   |
| 动作   |
| 科幻   |
| 剧情   |
| 悬疑   |
| 警匪   |
| 动作   |
| 心理   |
| 剧情   |
| 战争   |
| 动作   |
| 灾难   |

select 
movie
from movie_info
+--------------+
|    movie     |
+--------------+
| 《疑犯追踪》       |
| 《Lie to me》  |
| 《战狼2》        |
+--------------+
--尝试join
select
EXPLODE(split(category,','))  
from movie_info  t1

select 
movie
from movie_info t2

select
  t2.movie,
  t1.*
from(
    select
      EXPLODE(split(category,','))  
    from movie_info 
) t1 right join (
    select 
      movie
    from movie_info
)t2 

|   t2.movie   | t1.category  |
+--------------+--------------+
| 《疑犯追踪》       | 悬疑           |
| 《疑犯追踪》       | 动作           |
| 《疑犯追踪》       | 科幻           |
| 《疑犯追踪》       | 剧情           |
| 《疑犯追踪》       | 悬疑           |
| 《疑犯追踪》       | 警匪           |
| 《疑犯追踪》       | 动作           |
| 《疑犯追踪》       | 心理           |
| 《疑犯追踪》       | 剧情           |
| 《疑犯追踪》       | 战争           |
| 《疑犯追踪》       | 动作           |
| 《疑犯追踪》       | 灾难           |
| 《Lie to me》  | 悬疑           |
| 《Lie to me》  | 动作           |
| 《Lie to me》  | 科幻           |
| 《Lie to me》  | 剧情           |
| 《Lie to me》  | 悬疑           |
| 《Lie to me》  | 警匪           |
| 《Lie to me》  | 动作           |
| 《Lie to me》  | 心理           |
| 《Lie to me》  | 剧情           |
| 《Lie to me》  | 战争           |
| 《Lie to me》  | 动作           |
| 《Lie to me》  | 灾难           |
| 《战狼2》        | 悬疑           |
| 《战狼2》        | 动作           |
| 《战狼2》        | 科幻           |
| 《战狼2》        | 剧情           |
| 《战狼2》        | 悬疑           |
| 《战狼2》        | 警匪           |
| 《战狼2》        | 动作           |
| 《战狼2》        | 心理           |
| 《战狼2》        | 剧情           |
| 《战狼2》        | 战争           |
| 《战狼2》        | 动作           |
| 《战狼2》        | 灾难           |
+--------------+--------------+
--侧写表
select
movie,
category_name
from movie_info
lateral view explode(split(category,",")) tmp as category_name
where category_name='悬疑'
+--------------+----------------+
|    movie     | category_name  |
+--------------+----------------+
| 《疑犯追踪》       | 悬疑             |
| 《Lie to me》  | 悬疑             |
+--------------+----------------+
+--------------+----------------+
|    movie     | category_name  |
+--------------+----------------+
| 《疑犯追踪》       | 悬疑             |
| 《疑犯追踪》       | 动作             |
| 《疑犯追踪》       | 科幻             |
| 《疑犯追踪》       | 剧情             |
| 《Lie to me》  | 悬疑             |
| 《Lie to me》  | 警匪             |
| 《Lie to me》  | 动作             |
| 《Lie to me》  | 心理             |
| 《Lie to me》  | 剧情             |
| 《战狼2》        | 战争             |
| 《战狼2》        | 动作             |
| 《战狼2》        | 灾难             |
+--------------+----------------+

8.5 窗口函数(开窗函数)

1.什么是窗口函数
窗口函数是一个高阶函数  mysql 5.6 5.7 都没有 5.8有 窗口功能收费
oracle 数据库一直有 但是一直收费
hive里面也有窗口 但是hive免费
窗口函数是很吃资源的
2.哪些函数是窗口函数的
Windowing functions
1.lead
2.lag
3.fist_value
4.last_value
聚合函数
1.max
2.sum
3.min
4.avg
5.count
排名分析函数
1.RANK
2.ROW_NUMBER
3.DENSE_RANK
4.NTILE
三.窗口函数的语法  窗口函数()+over()
窗口函数的含义 窗口函数()计算的逻辑 over()表示开窗 并且开窗是限定函数的计算范围
over([partition by ...][order by....]+[窗口子句]) over默认开窗 开一个最大的窗口
partition 表示分类 表示划分一个细窗口,将字段相同数据 进入到同一个窗口里面，并且细窗口之间独立，并且你的窗口子句对细窗口独立生效
order by 表示窗口内按什么排序  如果有over 没有partition 直接最大窗口排序  如果有over 也有partition 每个细窗口内部单独排序
窗口函数是一行一行走的
窗口函数本身也有执行顺序
   1over() 表示开窗 开最大的窗口
   2partition by
   3order by
   4窗口子句  再一次限定范围
   5窗口函数的执行
   1-2-3-4-5
 窗口子句
(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
窗口子句是有默认范围的
When ORDER BY is specified with missing WINDOW clause, the WINDOW specification defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
当over里面有order by 但是没有窗口子句时 默认范围 是上无边界到当前行
当over里面既没有order by 又没有窗口子句时 默认范围 是上无边界到下无边界
并不是所有函数都支持窗口子句
   lead lag rank row_number dense_rank ntile

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

查询在2017年4月份购买过的顾客及总人数
1.需要先过滤出4月份购买的顾客数据
--第一种过滤
 select
 *
 from business
 where month(orderdate)='4';
--第二种过滤 
  select
 *
 from business
 where substring(orderdate,1,7)='2017-04';
--第三种方式
  select
 *
 from business
 where date_format(orderdate,'yyyy-MM')='2017-04';
--需求1 第一种
  select
  name,
  count(*)over(rows between UNBOUNDED PRECEDING and current row)
 from business
 where substring(orderdate,1,7)='2017-04'
 group by name
--需求1 第二种
 select
 name,
 count(*)over()
 from business
 where date_format(orderdate,'yyyy-MM')='2017-04'
 group by name

--需求1 不用窗口函数怎么写(要你所有月份的总人数 和 人)
--两步

select 
 name,
 date_format(orderdate,'yyyy-MM') orderdate
from business;  t1
select
 t1.orderdate,
 collect_list(t1.name),
 size(collect_list(t1.name))
from(
   select 
    name,
    date_format(orderdate,'yyyy-MM') orderdate
   from business
) t1
group by t1.orderdate

--一步
select
 date_format(orderdate,'yyyy-MM'),
 collect_list(name),
 size(collect_list(name))
from business
group by date_format(orderdate,'yyyy-MM')

| t1.orderdate  |       n_c        | p_c  |1
+---------------+------------------+------+
| 2017-01       | ["jack","tony"]  | 2    |
| 2017-02       | ["jack"]         | 1    |
| 2017-04       | ["jack","mart"]  | 2    |
| 2017-05       | ["neil"]         | 1    |
| 2017-06       | ["neil"]         | 1    |
+---------------+------------------+------+

需求1变种1  查询在2017年4月份购买过的顾客及累计人数
select
name,
count(*)over(rows between UNBOUNDED PRECEDING and UNBOUNDED following)
from business
where substring(orderdate,1,7)='2017-04'
group by name
+-------+-----------------+
| mart  | 1               |
| jack  | 2               |
+-------+-----------------+
需求1变种2  查询在2017年4月份购买过的顾客及总人次
select
name,
orderdate,
cost,
count(*)over(rows between UNBOUNDED PRECEDING and current row)
from business
where month(orderdate)='4'

需求1变种3  查询在购买过的顾客及总人次需要明细
select
 name,
 orderdate,
 cost,
 count(*)over(rows between  UNBOUNDED PRECEDING and UNBOUNDED following)
from business
需求1变种4  查询在购买过的顾客及累加人次
select 
 name,
 orderdate,
 cost,
 count(*)over(rows between  UNBOUNDED PRECEDING and current row)
 from business;
需求1变种5  查询在购买过的顾客及总人数/累计人数

select
 name,
 count(*) over(rows between  UNBOUNDED PRECEDING and current row )
from business
group by name

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

select
 name,
 orderdate,
 cost,
 sum(cost)over(partition by name,month(orderdate)rows between  UNBOUNDED PRECEDING and current row)
from business
+-------+-------------+-------+---------------+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+
| jack  | 2017-01-05  | 46    | 111           |
| jack  | 2017-01-08  | 55    | 111           |
| jack  | 2017-01-01  | 10    | 111           |
| jack  | 2017-02-03  | 23    | 23            |
| jack  | 2017-04-06  | 42    | 42            |
| mart  | 2017-04-13  | 94    | 299           |
| mart  | 2017-04-11  | 75    | 299           |
| mart  | 2017-04-09  | 68    | 299           |
| mart  | 2017-04-08  | 62    | 299           |
| neil  | 2017-05-10  | 12    | 12            |
| neil  | 2017-06-12  | 80    | 80            |
| tony  | 2017-01-04  | 29    | 94            |
| tony  | 2017-01-02  | 15    | 94            |
| tony  | 2017-01-07  | 50    | 94            |
+-------+-------------+-------+---------------+


需求2 变种1  查询顾客的购买明细及购买总额

select
 name,
 orderdate,
 cost,
 sum(cost)over(partition by name  rows between UNBOUNDED PRECEDING and UNBOUNDED following )
from business

| jack  | 2017-01-05  | 46    | 176           |
| jack  | 2017-01-08  | 55    | 176           |
| jack  | 2017-01-01  | 10    | 176           |
| jack  | 2017-04-06  | 42    | 176           |
| jack  | 2017-02-03  | 23    | 176           |
| mart  | 2017-04-13  | 94    | 299           |
| mart  | 2017-04-11  | 75    | 299           |
| mart  | 2017-04-09  | 68    | 299           |
| mart  | 2017-04-08  | 62    | 299           |
| neil  | 2017-05-10  | 12    | 92            |
| neil  | 2017-06-12  | 80    | 92            |
| tony  | 2017-01-04  | 29    | 94            |
| tony  | 2017-01-02  | 15    | 94            |
| tony  | 2017-01-07  | 50    | 94            |
+-------+-------------+-------+---------------+

需求2 变种2  查询购买明细和购买总额
select
name,
orderdate,
cost,
sum(cost)over( )
from business;


需求2 变种3  查询购买明细和累加总额
select
name,
orderdate,
cost,
sum(cost)over(rows between UNBOUNDED PRECEDING and current row )
from business;

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

select
 name,
 orderdate,
 cost,
 sum(cost)over(partition by name  order by orderdate rows between UNBOUNDED PRECEDING and current row)
from business

| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+
| jack  | 2017-01-01  | 10    | 10            |
| jack  | 2017-01-05  | 46    | 56            |
| jack  | 2017-01-08  | 55    | 111           |
| jack  | 2017-02-03  | 23    | 134           |
| jack  | 2017-04-06  | 42    | 176           |
| mart  | 2017-04-08  | 62    | 62            |
| mart  | 2017-04-09  | 68    | 130           |
| mart  | 2017-04-11  | 75    | 205           |
| mart  | 2017-04-13  | 94    | 299           |
| neil  | 2017-05-10  | 12    | 12            |
| neil  | 2017-06-12  | 80    | 92            |
| tony  | 2017-01-02  | 15    | 15            |
| tony  | 2017-01-04  | 29    | 44            |
| tony  | 2017-01-07  | 50    | 94            |

--需求3的变种1 直接按照日期将花费进行累加
select
name,
orderdate,
cost,
sum(cost)over( order by orderdate )
from business
--需求3的变种2 将每个顾客的cost按照日期统计总花费
select
name,
orderdate,
cost,
sum(cost)over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED following)
from business
+-------+-------------+-------+---------------+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+
| jack  | 2017-01-01  | 10    | 176           |
| jack  | 2017-01-05  | 46    | 176           |
| jack  | 2017-01-08  | 55    | 176           |
| jack  | 2017-02-03  | 23    | 176           |
| jack  | 2017-04-06  | 42    | 176           |
| mart  | 2017-04-08  | 62    | 299           |
| mart  | 2017-04-09  | 68    | 299           |
| mart  | 2017-04-11  | 75    | 299           |
| mart  | 2017-04-13  | 94    | 299           |
| neil  | 2017-05-10  | 12    | 92            |
| neil  | 2017-06-12  | 80    | 92            |
| tony  | 2017-01-02  | 15    | 94            |
| tony  | 2017-01-04  | 29    | 94            |
| tony  | 2017-01-07  | 50    | 94            |
+-------+-------------+-------+---------------+

--需求3的变种3 每个顾客的cost按照日期求上一次和当前一次消费的和
select
 name,
 orderdate,
 cost,
 sum(cost)over(partition by name order by orderdate rows between 1 PRECEDING and current row)
from business
+-------+-------------+-------+---------------+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+
| jack  | 2017-01-01  | 10    | 10            |
| jack  | 2017-01-05  | 46    | 56            |
| jack  | 2017-01-08  | 55    | 101           |
| jack  | 2017-02-03  | 23    | 78            |
| jack  | 2017-04-06  | 42    | 65            |
| mart  | 2017-04-08  | 62    | 62            |
| mart  | 2017-04-09  | 68    | 130           |
| mart  | 2017-04-11  | 75    | 143           |
| mart  | 2017-04-13  | 94    | 169           |
| neil  | 2017-05-10  | 12    | 12            |
| neil  | 2017-06-12  | 80    | 92            |
| tony  | 2017-01-02  | 15    | 15            |
| tony  | 2017-01-04  | 29    | 44            |
| tony  | 2017-01-07  | 50    | 79            |
+-------+-------------+-------+---------------+
--需求3的变种4 每个顾客的cost按照日期求当前和下一次消费的和
select
name,
orderdate,
cost,
sum(cost)over(partition by name order by orderdate rows between current row and 1 following)
from business

| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+
| jack  | 2017-01-01  | 10    | 56            |
| jack  | 2017-01-05  | 46    | 101           |
| jack  | 2017-01-08  | 55    | 78            |
| jack  | 2017-02-03  | 23    | 65            |
| jack  | 2017-04-06  | 42    | 42            |
| mart  | 2017-04-08  | 62    | 130           |
| mart  | 2017-04-09  | 68    | 143           |
| mart  | 2017-04-11  | 75    | 169           |
| mart  | 2017-04-13  | 94    | 94            |
| neil  | 2017-05-10  | 12    | 92            |
| neil  | 2017-06-12  | 80    | 80            |
| tony  | 2017-01-02  | 15    | 44            |
| tony  | 2017-01-04  | 29    | 79            |
| tony  | 2017-01-07  | 50    | 50            |
+-------+-------------+-------+---------------+
--需求3的变种5 每个顾客的cost按照日期求上一次到下一次消费的和
select
name,
orderdate,
cost,
sum(cost)over(partition by name order by orderdate rows between  1 PRECEDING and 1 following)
from business
+-------+-------------+-------+---------------+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+
| jack  | 2017-01-01  | 10    | 56            |
| jack  | 2017-01-05  | 46    | 111           |
| jack  | 2017-01-08  | 55    | 124           |
| jack  | 2017-02-03  | 23    | 120           |
| jack  | 2017-04-06  | 42    | 65            |
| mart  | 2017-04-08  | 62    | 130           |
| mart  | 2017-04-09  | 68    | 205           |
| mart  | 2017-04-11  | 75    | 237           |
| mart  | 2017-04-13  | 94    | 169           |
| neil  | 2017-05-10  | 12    | 92            |
| neil  | 2017-06-12  | 80    | 92            |
| tony  | 2017-01-02  | 15    | 44            |
| tony  | 2017-01-04  | 29    | 94            |
| tony  | 2017-01-07  | 50    | 79            |
+-------+-------------+-------+---------------+
--需求3的变种5 每个顾客的cost按照日期求上一次和下一次消费的和
select
name,
orderdate,
cost,
sum(cost)over(partition by name order by orderdate rows between  1 PRECEDING and 1 following)-cost
from business

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

select
 name,
 orderdate,
 cost,
 lag(orderdate,1,'0000-00-00')over(partition by name order by orderdate ) prev_time,
 lead(orderdate,1,'9999-99-99')over(partition by name order by orderdate) next_time
from business
| name  |  orderdate  | cost  |  prev_time  |  next_time  |
+-------+-------------+-------+-------------+-------------+
| jack  | 2017-01-01  | 10    | NULL        | 2017-01-05  |
| jack  | 2017-01-05  | 46    | 2017-01-01  | 2017-01-08  |
| jack  | 2017-01-08  | 55    | 2017-01-05  | 2017-02-03  |
| jack  | 2017-02-03  | 23    | 2017-01-08  | 2017-04-06  |
| jack  | 2017-04-06  | 42    | 2017-02-03  | NULL        |
| mart  | 2017-04-08  | 62    | NULL        | 2017-04-09  |
| mart  | 2017-04-09  | 68    | 2017-04-08  | 2017-04-11  |
| mart  | 2017-04-11  | 75    | 2017-04-09  | 2017-04-13  |
| mart  | 2017-04-13  | 94    | 2017-04-11  | NULL        |
| neil  | 2017-05-10  | 12    | NULL        | 2017-06-12  |
| neil  | 2017-06-12  | 80    | 2017-05-10  | NULL        |
| tony  | 2017-01-02  | 15    | NULL        | 2017-01-04  |
| tony  | 2017-01-04  | 29    | 2017-01-02  | 2017-01-07  |
| tony  | 2017-01-07  | 50    | 2017-01-04  | NULL        |
+-------+-------------+-------+-------------+-------------+
select
name,
orderdate,
cost,
lag(orderdate,1,'0000-00-00')over(partition by name order by orderdate) prve_time,
lead(orderdate,1,'9999-99-99')over(partition by name order by orderdate) next_time
from business

| name  |  orderdate  | cost  |  prev_time  |  next_time  |
+-------+-------------+-------+-------------+-------------+
| jack  | 2017-01-01  | 10    | 0000-00-00  | 2017-01-05  |
| jack  | 2017-01-05  | 46    | 2017-01-01  | 2017-01-08  |
| jack  | 2017-01-08  | 55    | 2017-01-05  | 2017-02-03  |
| jack  | 2017-02-03  | 23    | 2017-01-08  | 2017-04-06  |
| jack  | 2017-04-06  | 42    | 2017-02-03  | 9999-99-99  |
| mart  | 2017-04-08  | 62    | 0000-00-00  | 2017-04-09  |
| mart  | 2017-04-09  | 68    | 2017-04-08  | 2017-04-11  |
| mart  | 2017-04-11  | 75    | 2017-04-09  | 2017-04-13  |
| mart  | 2017-04-13  | 94    | 2017-04-11  | 9999-99-99  |
| neil  | 2017-05-10  | 12    | 0000-00-00  | 2017-06-12  |
| neil  | 2017-06-12  | 80    | 2017-05-10  | 9999-99-99  |
| tony  | 2017-01-02  | 15    | 0000-00-00  | 2017-01-04  |
| tony  | 2017-01-04  | 29    | 2017-01-02  | 2017-01-07  |
| tony  | 2017-01-07  | 50    | 2017-01-04  | 9999-99-99  |

课后练习

求每个顾客的购买明细以及上一次购买和下一次购买花费的和
select
  t1.name,
  t1.orderdate,
  t1.cost,
  t1.prev_cost,
  t1.next_cost,
  t1.prev_cost + t1.next_cost sum_cost
from (
  select 
    name,
    orderdate,
    cost,
    lag(cost,1,0) 
      over(partition by name order by orderdate) prev_cost,
    lead(cost,1,0) 
      over(partition by name order by orderdate) next_cost
 from business
) t1;

select
name,
orderdate,
cost,
sum(cost)over(partition by name order by orderdate rows between 1 PRECEDING and 1 following)-cost
from business;

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

select
 name,
 orderdate,
 cost,
 first_value(orderdate)over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED following ) first_value,
 last_value(orderdate)over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED following)
last_value                    
from business

| name  |  orderdate  | cost  | first_order  | last_order  |
+-------+-------------+-------+--------------+-------------+
| jack  | 2017-01-01  | 10    | 2017-01-01   | 2017-01-08  |
| jack  | 2017-01-05  | 46    | 2017-01-01   | 2017-01-08  |
| jack  | 2017-01-08  | 55    | 2017-01-01   | 2017-01-08  |
| jack  | 2017-02-03  | 23    | 2017-02-03   | 2017-02-03  |
| jack  | 2017-04-06  | 42    | 2017-04-06   | 2017-04-06  |
| mart  | 2017-04-08  | 62    | 2017-04-08   | 2017-04-13  |
| mart  | 2017-04-09  | 68    | 2017-04-08   | 2017-04-13  |
| mart  | 2017-04-11  | 75    | 2017-04-08   | 2017-04-13  |
| mart  | 2017-04-13  | 94    | 2017-04-08   | 2017-04-13  |
| neil  | 2017-05-10  | 12    | 2017-05-10   | 2017-05-10  |
| neil  | 2017-06-12  | 80    | 2017-06-12   | 2017-06-12  |
| tony  | 2017-01-02  | 15    | 2017-01-02   | 2017-01-07  |
| tony  | 2017-01-04  | 29    | 2017-01-02   | 2017-01-07  |
| tony  | 2017-01-07  | 50    | 2017-01-02   | 2017-01-07  |

--需求 求每个顾客的第一次购买时间和最后一次购买时间
select
name,
orderdate,
cost,
first_value(orderdate)over(partition by name order by orderdate
rows between UNBOUNDED PRECEDING AND UNBOUNDED following ) first_order,
last_value(orderdate)over(partition by name order by orderdate
rows between UNBOUNDED PRECEDING AND UNBOUNDED following ) last_order
from business

+-------+-------------+-------+--------------+-------------+
| name  |  orderdate  | cost  | first_order  | last_order  |
+-------+-------------+-------+--------------+-------------+
| jack  | 2017-01-01  | 10    | 2017-01-01   | 2017-04-06  |
| jack  | 2017-01-05  | 46    | 2017-01-01   | 2017-04-06  |
| jack  | 2017-01-08  | 55    | 2017-01-01   | 2017-04-06  |
| jack  | 2017-02-03  | 23    | 2017-01-01   | 2017-04-06  |
| jack  | 2017-04-06  | 42    | 2017-01-01   | 2017-04-06  |
| mart  | 2017-04-08  | 62    | 2017-04-08   | 2017-04-13  |
| mart  | 2017-04-09  | 68    | 2017-04-08   | 2017-04-13  |
| mart  | 2017-04-11  | 75    | 2017-04-08   | 2017-04-13  |
| mart  | 2017-04-13  | 94    | 2017-04-08   | 2017-04-13  |
| neil  | 2017-05-10  | 12    | 2017-05-10   | 2017-06-12  |
| neil  | 2017-06-12  | 80    | 2017-05-10   | 2017-06-12  |
| tony  | 2017-01-02  | 15    | 2017-01-02   | 2017-01-07  |
| tony  | 2017-01-04  | 29    | 2017-01-02   | 2017-01-07  |
| tony  | 2017-01-07  | 50    | 2017-01-02   | 2017-01-07  |

8.5.7 查询前20%时间的订单信息

select
 name,
 orderdate,
 cost,
 ntile(5)over(order by orderdate) n_t
from business

select 
 t1.name,
 t1.orderdate,
 t1.cost,
 t1.n_t
from(
select
 name,
 orderdate,
 cost,
 ntile(5)over(order by orderdate) n_t
from business
) t1
where t1.n_t=1

| t1.name  | t1.orderdate  | t1.cost  | t1.group_id  |
+----------+---------------+----------+--------------+
| jack     | 2017-01-01    | 10       | 1            |
| tony     | 2017-01-02    | 15       | 1            |
| tony     | 2017-01-04    | 29       | 1            |

8.5.8计算每门学科成绩排名。

select
 name,
 subject,
 score,
 rank()over(partition by subject order by score desc) r_k,
 dense_rank()over(partition by subject order by score desc) d_r_k,
 row_number()over(partition by subject order by score desc) r_r_k
from score

| name  | subject  | score  | rk  | drk  | rn  |
+-------+----------+--------+-----+------+------+
| 孙悟空 | 数学      | 95     | 1   | 1    | 1    |
| 宋宋   | 数学       | 86     | 2   | 2    | 2   |
| 婷婷   | 数学       | 85     | 3   | 3    | 3   |
| 大海   | 数学       | 56     | 4   | 4    | 4   |
| 宋宋   | 英语       | 84     | 1   | 1    | 1   |
| 大海   | 英语       | 84     | 1   | 1    | 2   |
| 婷婷   | 英语       | 78     | 3   | 2    | 3   |
| 孙悟空  | 英语       | 68     | 4   | 3    | 4  |
| 大海   | 语文       | 94     | 1   | 1    | 1   |
| 孙悟空 | 语文       | 87     | 2   | 2    | 2   |
| 婷婷   | 语文       | 65     | 3   | 3    | 3   |
| 宋宋   | 语文       | 64     | 4   | 4    | 4   |

select
name,
orderdate,
cost,
sum(cost)over( order by month(orderdate)) 
from business;
+-------+-------------+-------+---------------+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+
| jack  | 2017-01-01  | 10    | 205           |
| jack  | 2017-01-08  | 55    | 205           |
| tony  | 2017-01-07  | 50    | 205           |
| jack  | 2017-01-05  | 46    | 205           |
| tony  | 2017-01-04  | 29    | 205           |
| tony  | 2017-01-02  | 15    | 205           |
| jack  | 2017-02-03  | 23    | 228           |
| mart  | 2017-04-13  | 94    | 569           |
| jack  | 2017-04-06  | 42    | 569           |
| mart  | 2017-04-11  | 75    | 569           |
| mart  | 2017-04-09  | 68    | 569           |
| mart  | 2017-04-08  | 62    | 569           |
| neil  | 2017-05-10  | 12    | 581           |
| neil  | 2017-06-12  | 80    | 661           |
+-------+-------------+-------+---------------+

关于 建表语句和排序和窗口函数的容易混淆的语法

一. 建表的时候    
1.partitioned by 表示你创建的表为分区表
2.clustered by 表示你创建表为分桶表
二. 查询语句里排序的四个by
order by 表示全局排序
distribute by 以什么分区 sort by 区内排序字段
cluster by 表示分区排序
三.窗口函数的partition
partition by 表示 更细窗口划分
order by 窗口以什么排序

distribute by sort by  相当于 partition by  order by 

select
 name,
 orderdate,
 cost,
 first_value(orderdate)over(distribute by name,month(orderdate) sort 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

8.5关于创建函数

8.5.1创建临时函数

1.add jar /opt/module/hive/datas/myudf.jar;
2.创建函数
create temporary function my_len as "com.atguigu.udf.MyUDF";
临时函数只对你当前的单次会话生效,并且可以跨库使用
3.删除临时函数
drop  temporary function my_len;

8.5.2创建永久函数

1.add jar /opt/module/hive/datas/myudf.jar;
2.创建函数
create function my_len2 as "com.atguigu.udf.MyUDF";
3.创建真正的永久函数
 create function my_len3 as "com.atguigu.udf.MyUDF" using jar "hdfs://hadoop102:8020/udf/myudf.jar";
4.删除永久函数
drop function my_len2;

第九章压缩存储

文件存储类型 
1.文件分 两种类型  行式存储和列式存储  列存要比行存用的多
2.四种存储类型 TEXTFILE 、SEQUENCEFILE（行存）、ORC、PARQUET（列存）
3.ORC 是 TEXTFILE 的列存 PARQUET 是SEQUENCEFILE 列存

当你在公司里 使用 mr做引擎玩数仓的时候  你文件存储格式 可以为 orc+lzo
当你在公司里 使用 spark做引擎玩数仓的时候  你文件存储格式 可以为 parquet+snappy

y | 2017-01-02 | 15 | 205 |
| jack | 2017-02-03 | 23 | 228 |
| mart | 2017-04-13 | 94 | 569 |
| jack | 2017-04-06 | 42 | 569 |
| mart | 2017-04-11 | 75 | 569 |
| mart | 2017-04-09 | 68 | 569 |
| mart | 2017-04-08 | 62 | 569 |
| neil | 2017-05-10 | 12 | 581 |
| neil | 2017-06-12 | 80 | 661 |
±------±------------±------±--------------+

关于 建表语句和排序和窗口函数的容易混淆的语法

```sql
一. 建表的时候    
1.partitioned by 表示你创建的表为分区表
2.clustered by 表示你创建表为分桶表
二. 查询语句里排序的四个by
order by 表示全局排序
distribute by 以什么分区 sort by 区内排序字段
cluster by 表示分区排序
三.窗口函数的partition
partition by 表示 更细窗口划分
order by 窗口以什么排序

distribute by sort by  相当于 partition by  order by 

select
 name,
 orderdate,
 cost,
 first_value(orderdate)over(distribute by name,month(orderdate) sort 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

8.5关于创建函数

8.5.1创建临时函数

1.add jar /opt/module/hive/datas/myudf.jar;
2.创建函数
create temporary function my_len as "com.atguigu.udf.MyUDF";
临时函数只对你当前的单次会话生效,并且可以跨库使用
3.删除临时函数
drop  temporary function my_len;

8.5.2创建永久函数

1.add jar /opt/module/hive/datas/myudf.jar;
2.创建函数
create function my_len2 as "com.atguigu.udf.MyUDF";
3.创建真正的永久函数
 create function my_len3 as "com.atguigu.udf.MyUDF" using jar "hdfs://hadoop102:8020/udf/myudf.jar";
4.删除永久函数
drop function my_len2;

第九章压缩存储

文件存储类型 
1.文件分 两种类型  行式存储和列式存储  列存要比行存用的多
2.四种存储类型 TEXTFILE 、SEQUENCEFILE（行存）、ORC、PARQUET（列存）
3.ORC 是 TEXTFILE 的列存 PARQUET 是SEQUENCEFILE 列存

当你在公司里 使用 mr做引擎玩数仓的时候  你文件存储格式 可以为 orc+lzo
当你在公司里 使用 spark做引擎玩数仓的时候  你文件存储格式 可以为 parquet+snappy