第 4 章 DDL 数据定义
4.1 创建数据库
1）创建一个数据库，数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db。
hive (default)> create datbase db_hive;
2）避免要创建的数据库已经存在错误，增加 if not exist 判断。（标准写法）
hive (default)> create datbase db_hive;
FAILED: Execution Eror, return code 1 from org.apche.hadop.hive.ql.exc.DLTask.
Datbase db_hive already exist
hive (default)> create datbase if not exist db_hive;
3）创建一个数据库，指定数据库在 HDFS 上存放的位置
hive (default)> create datbase db_hive2 location ‘/db_hive2.db’;
4.2 查询数据库
4.21 显示数据库
1．显示数据库
hive> show datbase;
2．过滤显示查询的数据库
hive> show datbase like ‘db_hive*’;
OK
db_hive
db_hive_1
4.2.2 查看数据库详情
1．显示数据库信息
hive> desc datbase db_hive;
OK
db_hive hdfs:/hadop102:90/user/hive/warehouse/db_hive.db atguiguUSER
2．显示数据库详细信息，
desc database extended db_name;
表有差异，库无差异
4.2.3 切换当前数据库
hive (default)> use db_hive;
4.3修改数据库
用户可以使用 ALTER DATBASE 命令为某个数据库的 DBPROPERTIES 设置键-值对
属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数
据库名和数据库所在的目录位置。
alter datbase db_hive set dbproperties(‘createime’= ');
4. 删除数据库
1．删除空数据库
hive>drop datbase db_hive;
2．如果删除的数据库不存在，最好采用 if exist 判断数据库是否存在
hive> drop datbase if exist db_hive2;
3．如果数据库不为空，可以采用 cascade 命令，强制删除
hive> drop datbase db_hive cascade;
4.5 创建表
1．建表语法
CREATE [EXTERNAL] TABLE [IF NOT EXIST] table_name
[(col_name dat_type [COMENT col_coment],… .)]
[COMENT table_coment]
[PARTIONED BY (col_name dat_type [COMENT col_coment], …)]
[CLUSTERD 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]
2．字段解释说明
PARTIONED BY 创建分区表
CLUSTERD BY 创建分桶表
4.51 管理表与外部表
理论
默认创建的表都是管理表，也叫内部表。删除一个管理表时，Hive 也会删除这个表中数据。而表是外部表， Hive 并非认为其完全拥有这份数据。删除该表并不会删除数据，但描述表的元数据信息会被删除掉。
管理表和外部表的使用场景
每天将收集到的网站日志定期流入 HDFS 文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过 SELECT+INSERT进入内部表。
实际操作：
创建外部表
加载数据
删除表后创建表，数据依然存在。因为mysql存储的表数据路径还在，能够匹配上
4.52 管理表与外部表互相转换
只能用单引号，严格区分大小写，如果不是完全符合，那么只会添加 kv 而不生效
（1）查询表的类型
desc formated student2;
Table Type: MANGED_TABLE
（2）修改内部表 student2 为外部表
alter table student2 setblproperties(‘EXTERNAL’=TRUE’);
（3）查询表的类型
desc formated student2;
Table Type: EXTERNAL_TABLE
（4）修改外部表 student2 为内部表
alter table student2 setblproperties(‘EXTERNAL’=FALSE’);
（5）查询表的类型
desc formated student2;
Table Type: MANGED_TABLE 注意：(‘EXTERNAL’=TRUE’)和(‘EXTERNAL’=FALSE’)为固定写法，区分大小写！
4.6分区表
分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区
所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的
数据集。在查询时通过 WHER 子句中的表达式选择查询所需要的指定的分区，这样的查询
效率会提高很多。
4.61 分区表基本操作
1．引入分区表（需要根据日期对日志进行管理）
/user/hive/warehouse/log_partion/20201011/20201011.log
/user/hive/warehouse/log_partion/20201011/20201011.log
/user/hive/warehouse/log_partion/20201011/20201011.log
2．创建分区表语法
create table dept_partion(
deptno int, dname string, loc string
)
partioned by (month string)
row format delimted fields terminated by ‘\t’;
3．加载数据到分区表中
loadat local inpath ‘/usr/local/data/dept.xt’ into table dept_partion partion(month=‘202009’);
loadat local inpath ‘/usr/local/data/dept.xt’ into table dept_partion partion(month=‘202008’);
loadat local inpath ‘/usr/local/data/dept.xt’ into table dept_partion partion(month=‘202010’);
4.查询分区表中数据
单分区查询
select * from dept_partion wher month=‘2020-09’;
多分区联合查询 unio（排序） or in 三种方式
select * from dept_partition where month=‘2020-09’
union
selEct * from dept_partition wher month=‘2020-08’
union
selEct * from dept_partition wher month=‘2020-10’;
5．增加分区
创建单个分区
alter table dept_partition ad d partition(month=‘202001’) ;
同时创建多个分区 用空格分开
alter table dept_partition add partition (month=‘202002’) partion(month=‘202003’);
6．删除分区
删除单个分区
alter table dept_partition drop partition (month=‘202001’);
同时删除多个分区 用逗号分开
alter table dept_partition drop partition (month=‘202003’), partion (month=‘202002’);
7．查看分区表有多少分区
show partitions dept_partition ;
8．查看分区表结构
desc formatted dept_partition;
4.62 分区表注意事项
1．创建二级分区表
create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (month string, day string)
row format delimted fields terminated by '\t';
2．正常的加载数据
（1）加载数据到二级分区表中
load data local inpath '/usr/local/data//dept.xt' into table
dept_partion2 partion(month='202011', day='1');
（2）查询分区数据
select * from dept_partion2 where month=‘202011’ and day=‘1’;
3．把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式
（1）方式一：上传数据后修复(使用很多数据，一次修复，？）
上传数据
dfs -mkdir -p /user/hive/warehouse/dept_partion2/month=201909/day=12;
dfs -put /usr/local/data/dept.txt /user/hive/warehouse/dept_partition2/month=201909/day=12;
查询数据（查询不到刚上传的数据,没有元数据）
执行修复命令
msck repair table dept_partition2;
再次查询数据
select * from dept_partition2 where month=‘201909’ and day=‘12’;
(2)方式二：上传数据后添加分区
上传数据
dfs -mkdir -p /user/hive/warehouse/dept_partion2/month=201909/day=1;
dfs -put /usr/local/data/dept.txt /user/hive/warehouse/dept_partion2/month=201909/day=1;
执行添加分区
alter table dept_partition2 add partion(month='201909',day='1');
查询数据
select * from dept_partition2 where month=‘201909’ and day=‘1’;
（3）方式三：上传数据后 load 数据到分区(表已经存在)
创建目录
dfs -mkdir -p /user/hive/warehouse/dept_partion2/month=201909/day=10;
上传数据
load data local inpath '/usr/local/data/dept.xt' into table dept_partition2 partition(month='201909',day='10');
查询数据
select * from dept_partion2 wher month=‘201909’ and day=‘10’;
4.7 修改表
4.71 重命名表
1．语法
ALTER TABLE table_name RENAME TO new_table_name
2．实操案例
alter table dept_partition2 rename to dept_partition3;
4.72 增加、修改和删除表分区
详见 4.61 分区表基本操作。
4.73 增加/修改/替换列信息
1．语法
更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type
[COMENT col_coment] [FIRST|AFTER column_ame]
增加和替换列
ALTER TABLE table_name AD|REPLACE COLUMNS (col_name dat_type [COMENT
col_coment],...)
注：ADD是代表新增一字段，字段位置在所有列后面(partition 列前)，REPLACE 则是表示替换表中所有字段。
2．实操案例
（1）查询表结构
desc dbname;
（2）添加列
alter table tabname add columns(col_name string);
（3）查询表结构
desc tabname;
（4）更新列
alter table dept_partition change column deptdesc desc int;
（5）查询表结构
desc dept_partion;
（6）替换列(替换的事全部列)
alter table tabnamereplace columns(col_name string, col_name2
string);
（7）查询表结构
desc dept_partion;
4.8 删除表
drop table db_name;
5.DML数据操作
5.1数据导入
5.1.1向表中装载数据（Load）
load data [local] inpath '/usr/local/data/student.txt' [overwrite] into table student
[partition (partcol1=
val1,…)];
overwrite覆盖
5.1.2通过查询语句向表中插入数据（Insert）重要
insert into table tabname(col type) select * from tab_name;
基本模式
insert overwrite table tabname partition(month='') select * from tabname2;
多插入模式(from 先把表名提出去)
from stu
insert overwrite table stu partition(month='')
select * where month=''
insert overwrite table stu partition(month='')
select * where month=''
5.1.3 查询语句总创建表并加载数据（As Select）
create table if not exists stu2 as select id from stu
5.14 创建表时通过 Location 指定加载数据路径
1．创建表，并指定在 hdfs 上的位置
create table if not exist stu5(
id int, name string
)
row format delimted fields terminated by '\t'
location '/user/hive/warehouse/stu5';
2．上传数据到 hdfs 上
dfs -put /usr/local/date/stu.xt
/user/hive/warehouse/stu5;
3．查询数据
select * from stu5;
5.15 Import 数据到指定 Hive 表中
注意：先用 export 导出后，再将数据导入。
import table stu2 partition(month='202009') from
'/user/hive/warehouse/export/stu';
5.2数据导出
5.21 Insert 导出
1．将查询的结果导出到本地
insert overwrite local directory '/usr/local/data/dept'
select * from student;
2．将查询的结果格式化导出到本地
insert overwite local directory '/usr/local/data/dept'
ROW FORMAT DELIMTED FIELDS TERMINATED BY '\t'
3．将查询的结果导出到 HDFS 上(没有 local)
insert overwite directory '/dept'
ROW FORMAT DELIMTED FIELDS TERMINATED BY '\t'
select * from dept;
5.2.2Hadoop 命令导出到本地
dfs -get /dept/000000_0
/usr/local/data/dept/dept.txt;
5.2.3 Hive Shell 命令导出
基本语法：
（hive -f/e 执行语句或者脚本 > file）
bin/hive -e 'select * from dept;' >
/usr/local/data/dept/dept.txt;
5.24 Export 导出到 HDFS 上
export table dept to
'/export';
结合import
5.2.5 Sqoop 导出
mysql hdfs hive互相导
5.3 清除表中的数据Truncate
只能删除管理表，不能删除外部表中数据
truncate table tabname;
主要参考尚硅谷的HIVE教程
6.查询
查询语句语法：
[WITH ComonTableExpresion (, ComonTableExpresion)*] (Note: Only avilable
starting with Hive 0.13.0)
SELECET [AL | DISTINCT] select_expr, select_expr, .
FROM table_refrence
[WHER wher_conditon]
[GROUP BY col_ist]
[ORDER BY col_ist]
[CLUSTER BY col_ist
| [DISTRIBUTE BY col_ist] [SORT BY col_ist]
]
[LIMT number]
语法
select
from
join on
where
group by
order by
having limit
执行顺序
from->join on->where->group by->select|having->order by ->limit
连接查询中能过滤先在连接中过滤
6.1 基本查询（Select…From）
6.1.1 全表和特定列查询
1．全表查询
hive (default)> select * from emp;
2．选择特定列查询
hive (default)> select empno, ename from emp; 注:SQL语言大小写不敏感，关键字不能缩写和分行，各个字句一般分行写，用锁紧提高语句可读性
6.1.2 列别名
1．重命名一个列
2．便于计算
3．紧跟列名，也可以在列名和别名之间加入关键字‘AS’
4．案例实操
查询名称和部门
hive (default)> select ename AS name, deptno dn from emp;
6.13 算术运算符
+，-，*，/，%，&，|，^，~
select sal +1 from emp;
hive (default)> select sal +1 from emp;
6.1.4 常用函数
1．求总行数（count）
hive (default)> select count(*) cnt from emp;
2．求工资的最大值（max）
hive (default)> select max(sal) max_sal from emp;
3．求工资的最小值（min）
hive (default)> select min(sal) min_sal from emp;
4．求工资的总和（sum）
hive (default)> select sum(sal) sum_sal from emp;
5．求工资的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;
6.1.5 Limt 语句
典型的查询会返回多行数据。LIMT 子句用于限制返回的行数。
hive (default)> select * from emp limt 5;
6.2 Where语句
1．使用 WHER 子句，将不满足条件的行过滤掉
2．WHERE 子句紧随 FROM 子句
3．案例实操
查询出薪水大于 10 的所有员工
hive (default)> select * from emp wher sal >10;
6.2.1 比较运算符（Between/In/ Is Null）
1）下面表中描述了谓词操作符，这些操作符同样可以用于 JOIN…ON 和 HAVING 语句中。
=,<==>,<>,!=,is null,is not null,in,like,rlike,and,or,not…
(1)查找以 2 开头薪水的员工信息
hive (default)> select * from emp where sal LIKE ‘2%’;
（2）查找第二个数值为 2 的薪水的员工信息
hive (default)> select * from emp where sal LIKE ‘_2%’;
（3）查找薪水中含有 2 的员工信息
hive (default)> select * from emp where sal RLIKE ‘[2]’;
6.2.3逻辑运算法（and/or/not）
6.3 分组
6.31 Group By 语句
GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，
然后对每个组执行聚合操作。
案例实操：
（1）计算 emp 表每个部门的平均工资
hive (default)> select.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
（2）计算 emp 每个部门中每个岗位的最高薪水
hive (default)> select.deptno, t.job, max(t.sal) max_sal from emp t group by
t.deptno, t.job;
6.3.2 Having 语句
1．having 与 where 不同点
（1）where 针对表中的列发挥作用，查询数据；having 针对查询结果中的列发挥作用，
筛选数据。
（2）where 后面不能写分组函数，而 having 后面可以使用分组函数。
（3）having 只用于 group by 分组统计语句。
2．案例实操
（1）求每个部门的平均薪水大于 20 的部门
求每个部门的平均工资
hive (default)> select deptno, avg(sal) from emp group by deptno;
求每个部门的平均薪水大于 20 的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having
avg_sal > 20;
6.4 Join 语句
6.41 等值 Join
Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接，不支持非等值连接。
案例实操
（1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门编号；
hive (default)> select e.mpno, e.name, d.eptno, d.name from emp e join dept d
on e.deptno = d.eptno;
6.42 表的别名
1．好处
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。
2．案例实操
合并员工表和部门表
hive (default)> select e.mpno, e.name, d.eptno from emp e join dept d on e.deptno
= d.eptno;
6.4.3 内连接
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。两个集合的交集保留。
hive (default)> select e.mpno, e.name, d.eptno from emp e join dept d on e.deptno
= d.eptno;
6.4.4 左外连接
左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。
hive (default)> select e.mpno, e.name, d.eptno from emp e left join dept d on e.deptno
= d.eptno;
6.4.5 右外连接
右外连接：JOIN 操作符右边表中符合 WHER E子句的所有记录将会被返回。
hive (default)> select e.mpno, e.name, d.eptno from emp e right join dept d on e.deptno
= d.eptno;
6.46 满外连接
满外连接：将会返回所有表中符合 WHER 语句条件的所有记录。如果任一表的指定字
段没有符合条件的值的话，那么就使用 NUL 值替代。
hive (default)> select e.mpno, e.name, d.eptno from emp e ful join dept d on e.deptno
= d.eptno;
6.47 多表连接(join on)
注意：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件。
1．创建位置表
create table if not exist default.location(
loc int,
loc_name string
)
row format delimted fields terminated by '\t';
2．导入数据
hive (default)> load data local inpath '/usr/local/data/location.txt' into table
default.location;
3．多表连接查询
hive (default)>SELECT e.name, d.eptno, l. loc_name
FROM emp e
JOIN dept d
ON d.eptno = e.deptno
JOIN location l
ON d.loc = l.oc;
大多数情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。本例中会首
先启动一个 MapReduce job 对表 e 和表 d 进行连接操作，然后会再启动一个 MapReduce job
order by 全局 一个将第一个 MapReduce job 的输出和表 l;进行连接操作。
6.4.8 笛卡尔积
1．笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
2．案例实操
hive (default)> select empno, dname from emp, dept;
6.49 连接谓词中不支持 or
hive (default)> select e.mpno, e.name, d.eptno from emp e join dept d on e.deptno
= d.deptno or e.name=d.ename; --错误
6.5 排序
order by 全局排序，一个map reduce,asc升序 desc降序，可用别名排序
（1）查询员工信息按工资升序排列
hive (default)> select * from emp order by sal;
（2）查询员工信息按工资降序排列
hive (default)> select * from emp order by sal desc;
(3)别名排序
hive (default)> select ename, sal*2 twosal from emp order by twosal;
(4)多个排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal ;
每个 MapReduce 内部排序（Sort By）
sorted by 区内排序，结合distributed by ，多个reduce，根据某个字段分区再排序。
1．设置 reduce 个数
hive (default)> set mapreduce.job.reduces=3;
2．查看设置 reduce 个数
hive (default)> set mapreduce.job.reduces;
3．根据部门编号降序查看员工信息
hive (default)> select * from emp sort by empno desc;
4．将查询结果导入到文件中（按照部门编号降序排序）
hive (default)> insert overwrite local directory '/opt/module/dats/ortby-result'
select * from emp sort by deptno desc;
分区排序（Distribute By）
Distrbute By：类似 MR 中 partion，进行分区，结合 sort by 使用。
注意，Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
对于 distrbute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distrbute by
的效果。
案例实操：
先按照部门编号分区，再按照员工编号降序排序。
hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwite local directory
'/opt/module/dats/distribute-result' select * from emp distribute by deptno sort
by empno desc;
Cluster By
当 distrbute by 和 sorts by 字段相同时，可以使用 cluster by 方式。
cluster by 除了具有 distrbute by 的功能外还兼具 sort by 的功能。但是排序只能是倒序排
序，不能指定排序规则为 ASC 或者 DESC。
1）以下两种写法等价
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一个分区里面去。
6.6分桶
6.6.1分桶表数据存储。
分区针对的数存储路径，放在HDFS某一文件夹下。分桶针对的事数据文件。
1.创建分桶表。
(1).准备数据
（2）创建分桶表
create table stu_buck(id int, name string)
clusterd by(id)
into 4 buckets
row format delimted fields terminated by '\t';
（3）导入数据
（4）.查看
未分桶
2.创建分桶表时，数据通过子查询的方式导入
（1）先建一个普通的 stu 表
create table stu(id int, name string)
row format delimted fields terminated by ‘\t’; （2）向普通的 stu 表中导入数据
loadat local inpath ‘/opt/module/dats/tudent.xt’ into table stu;
（3）清空 stu_buck 表中数据
truncate table stu_buck;
select * from stu_buck;
（4）导入数据到分桶表，通过子查询的方式
insert into table stu_buck
select id, name from stu;
（5）查询
6.6.2 分桶抽样查询
查询表 stu_buck 中的数据。
select * from stu_buck tablesample(bucket 1 out of 4 on id);
注：tablesample 是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
y 必须是 table 总 bucket 数的倍数或者因子。hive 根据 y 的大小，决定抽样的比例。一共四个桶，抽(4/y)个桶数据。从第x,x+y，x+y+y…桶抽
注意：x 的值必须小于等于 y 的值
6.7其他常用查询函数
6.7.1空字段赋值
NVL：给值为 NUL 的数据赋值，它的格式是 NVL( string1, replace_with)。它的功能是
如果 string1 为 NUL，则 NVL 函数返回 replace_with 的值，否则返回 string1 的值，如果两个参数都为 NUlL ，则返回 NULL。
6.7.2时间
只能操作yyyy-MM-dd格式
date_format(字符串，‘yyyy-MM-dd ‘)
date_add(‘2020-09-15’,3)
date_add(‘2020-09-15’,-3)
date_sub(‘2020-09-15’,-3)
date_sub(‘2020-09-15’,3)
datediff两个时间相减，前面减后面，返回天
datediff(时间1，时间2）
regexp_replace(‘2020-9-5’,’/’,’-’)
6.7.3CASE WHEN
求出部門人数
create table emp_sex(
name string,
dept_id int,
sex string)
row format delimted fields terminated by "\t";
loadat local inpath '/usr/local/data/emp_sex.tx' into tablemp_sex;
select
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from
emp_sex
group by
dept_id;
6.7.4行转列
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串
CONCAT_WS(separtor, str1, str2,.)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NUL。这个函数会跳过分隔符参数后的任何 NUL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 array 类型字段。
练习
按照星座排序
1.创建表
create table person_info(
name string,
constelation string,
blod_type string)
row format delimted fields terminated by "\t";
2.加载表
load data local inpath “/usr/local/data/person_info.txt” into table person_info;
3.．按需求查询数据
select
t1.base,
concat_ws('|, collect_set(1.name) name
from
(select
name,
concat(constelation, ", blood_type) base
from
person_info) t1
group by
t1.base;
6.7.5 列转行
1．函数说明
EXPLODE(col)：将 hive 一列中复杂的 ary 或者 map 结构拆分成多行。
LATERAL VIEW侧写
用法：LATERAL VIEW udtf(expresion) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此
基础上可以对拆分后的数据进行聚合。
2．数据准备
3．需求
将电影分类中的数组数据展开。
4．创建本地 movie.txt，导入数据
5.创建 hive 表并导入数据
create table movie_info(
movie string,
category aray<string>)
row format delimted fields terminated by "\t"
colection items terminated by ",;
loadat local inpath "/usr/local/data/movie.txt" into table movie_info;
6．按需求查询数据
select
movie,
category_name
from
movie_info lateral view table_tmp as category_name;
6.74 窗口函数
1．相关函数说明
over()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
over()内使用
currnet row：当前行
n Ppreceding：往前 n 行数据
n folowing：往后 n 行数据
unbound：起点，UNBOUNDE PRECDING 表示从前面的起点，UNBOUNDE
following表示到后面的终点
over()左边使用，隔行操作
lag(col,n)：往前第 n 行数据
lead(col,n)：往后第 n 行数据
ntile(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，
对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。
2．数据准备：name，orderdate，cost
3．需求
（1）查询在 2021 年 4 月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景,要将 cost 按照日期进行累加
（4）查询顾客上次的购买时间
（5）查询前 20%时间的订单信息
4．创建本地 business.txt，导入数据
5．创建 hive 表并导入数据
create table busines(
name string,
orderdate
string,cost int
) ROW FORMAT DELIMTED FIELDS TERMINATED BY ',;
loadat local inpath "/usr/local/data/business.txt" into table business;
6．按需求查询数据
（1）查询在 2021 年 4 月份购买过的顾客及总人数
over()查看分组了多少条记录
select name,count(*) over ()
from busines
where substring(orderdate,17) = '2021-04'
group by name;
（2）查询顾客的购买明细及月购买总额
（3）上述的场景,要将 cost 按照日期进行累加
select orderdate,cost,sum(cost) over(order by orderdate) from business;
（4）查询顾客上次的购买时间
起点到当前行(少用）
over(partition by name order by orderdate rows between unbounded preceding and current row)
添加默认值
lead
（5）查询前 20%时间的订单信息
6.75 Rank
hive案例，蚂蚁森林植物申领统计
假设满足申领条件的用户全部领取沙柳，求领取沙柳前10的用户，以及比后一名多领沙柳数量。
(0)背景
plant_carbon，蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量
table_name: plant_carbon
plant_id plant_name low_carbon
植物编号 植物名 换购植物所需要的碳
user_low_carbon记录了用户每天的蚂蚁森林低碳生活领取的记录流水。
table_name：user_low_carbon
user_id data_dt low_carbon
用户 日期 减少碳排放（g）
1.创建表
create table user_low_carbon(user_id String,data_dt String,low_carbon int) row format delimited fields terminated by '\t';
create table plant_carbon(plant_id string,plant_name String,low_carbon int) row format delimited fields terminated by '\t';
2.加载数据
load data local inpath "/usr/local/data/low_carbon.txt" into table user_low_carbon;
load data local inpath "/usr/local/data/plant_carbon.txt" into table plant_carbon;
(1)统计在1月7日前每个用户总低碳量
(2)取出申领胡杨的条件
(3)取出申领沙柳的条件
(4)计算每个人求出能申领沙柳的棵数
（5）申领沙柳个数排序排序前10，并将下一行数据中心的plant_count放置当前行(ead() over() 函数是跟偏移量相关的两个分析函数，通过这个函数可以在一次查询中取出同一字段的后 N 行的数据 (lead) 作为独立的列, 从而更方便地进行进行数据过滤。)
select
user_id,
plant_count
lead(plant_count,1,'9999-99-99') over(order by plant_count desc) lead_plant_count
from t4
limit 10;
（6）将下一行数据中的plant_count放置当前行,排序
优化
第一步
第 7 章 函数
7.1 系统内置函数
1．查看系统自带的函数
hive> show functions;
2．显示自带的函数的用法
hive> desc function split;
3．详细显示自带的函数的用法
hive> desc function extended split;
7.2 自定义函数
1）Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF来方便的扩展。
2）当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用户自定义函数（UDF：user-defined function）。
3）根据用户自定义函数类别分为以下三种：
(一，多指的是行）
（1）UDF（User-Defined-Function）一进一出
（2）UDAF（User-Defined Agregation Function）聚集函数，多进一出.类似于：count/max/min
（3）UDTF（User-Defined Table-Genrating Functions）一进多出,如 lateral view explore()
4）官方文档地址
https://cwiki.apche.org/confluence/display/Hive/HivePlugins
5）编程步骤：
（1）继承 org.apche.adop.hive.qlUDF
（2）需要实现 evalute 函数；evalute 函数支持重载；
（3）在 hive 的命令行窗口创建函数
a）添加 jar
add jar linux_jar_path
b）创建 function，
create [temporay] function [dbname.]function_ame AS class_name;
（4）在 hive 的命令行窗口删除函数
Drop [temporay] function [if exist] [dbname.]function_name;
6）注意事项
（1）UDF 必须要有返回类型，可以返回 null，但是返回类型不能为 void；
7.3 自定义 UDF 函数
1．创建一个 Maven 工程 Hive
2．导入依赖
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>2.1.0</version>
</dependency>
</dependencies>
3．创建一个类(也可以创建重载方法)
package com.dirac;
import org.apache.hadoop.hive.ql.exec.UDF;
public class MyUDF extends UDF {
public int evaluate(int data){
return data+5;
}
}
4．打成 jar 包上传到服器
5．将 jar 包添加到 hive 的 classpath
add jar /usr/local/data/hive/lib/hive-UDF-1.0-SNAPSHOTjar
6．创建临时函数与开发好的 java class关联
create function addFive as 'com.dirac.hive.MyUDF';
7．即可在 hql 中使用自定义的函数
扫一扫
871
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
