一、Hive的定义

二、Hive的基本信息配置

三、Hive 数据类型

四、DDL数据定义

1. 创建数据库

1.1 创建数据库的基本语法如下：

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

1.2 可选项含义
[IF NOT EXISTS]：如果不存在。即，如果元数据中如果不存在database_name表名，就直接创建
[COMMENT database_comment]： 给库添加注释
[LOCATION hdfs_path]：指定要创建的库放置在hdfs的位置
[WITH DBPROPERTIES (property_name=property_value, ...)]：添加数据库的其他属性，例如创建人

2. 查询数据库

查询数据库

#显示所有数据库
show databases;
#过滤显示查询的数据库
show databases like 'db_hive*';

#显示数据库信息
desc database db_hive;

#显示数据库详细信息，extended
desc database extended db_hive;

#切换当前数据库
 use db_hive;

3. 修改数据库

修改数据库

#修改数据库的属性
alter database db_hive 
set dbproperties('createtime'='20170830');

4. 删除数据库

删除数据库语法

#删除为空数据库
drop database db_hive;

#删除非空数据库（强制删除）
drop database db_hive cascade;

5. 创建表

5.1 创建表的语法

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

5.2 其中各个关键字的含义表示
EXTERNAL：用于建外部表（外部表删除时不会删除hdfs里面的元数据）
[COMMENT table_comment]：注释
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]：按哪个字段分区
[CLUSTERED BY (col_name, col_name, ...)：按哪个字段分桶
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]：对桶的一个或多个列进行排序
[ROW FORMAT row_format]：
[STORED AS file_format]：
指定存储文件格式：

• SEQUENCEFILE（二进制序列文件）
• TEXTFILE（文本）
• RCFILE（列式存储格式文件）

[LOCATION hdfs_path]：指定表在 HDFS 上的存储位置
[TBLPROPERTIES (property_name=property_value, ...)]：表的其他属性设置
[AS select_statement]：后跟查询语句，根据查询结果创建表

6. 修改表

5.1 更新列语法

#1. 查询表类型
desc formatted student2;
#2. 修改表为内部表(FALSE)/外部表(TRUE)
alter table student2 set tblproperties('EXTERNAL'='TRUE'/'FALSE');
#3. 重命名表
ALTER TABLE table_name RENAME TO new_table_name
#4. 更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name 
column_type [COMMENT col_comment] [FIRST|AFTER column_name]
#5. 增加和替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT 
 col_comment], ...)
#6. 删除表
drop table dept;

五、DML数据操作

1. 数据导入

1.1 数据导入语法

#load方式导入
 load data [local] inpath '数据的 path' [overwrite] into table 
student [partition (partcol1=val1,…)];

#import方式导入
#这里from的地址必须是从表中导出的数据
import table student2 from '/user/hive/warehouse/export/student';

1.2 可选字段含义
[local]： 表示从本地导入
[overwrite]：表示将到导入的表中的数据先清空再导入
[partition (partcol1=val1,…)]：若导入的表是有分区的，选择对应的分区导入

2. 数据导出

2.1 数据导出语法

#Insert 导出
insert overwrite local directory 
'/opt/module/hive/data/export/student'
select * from student;

#将查询的结果格式化导出到本地
insert overwrite local directory 
'/opt/module/hive/data/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;

#Export导出
export table default.student 
to '/user/hive/warehouse/export/student';

#清除表中数据
 truncate table student;

3. 数据查询

3.1 基本查询
3.2 函数
3.3limit语句
3.4 where
3.5 比较运算符
3.6 Like与RLike

3.7 链接

3.7.1 内链接

只有进行连接的两个表中都存在与链接条件匹配的数据才会被保存

select e.empno, e.ename, d.deptno from emp e join dept d 
on e.deptno = d.deptno;

3.7.2 左外链接

JOIN操作符左边表中符合where子句的所有记录都会被返回

select e.empno, e.ename, d.deptno from emp e left join 
dept d on e.deptno = d.deptno;

3.7.3 右外链接

JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回

 select e.empno, e.ename, d.deptno from emp e right join 
dept d on e.deptno = d.deptno;

3.7.4 满外连接

将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字
段没有符合条件的值的话，那么就使用 NULL 值替代。

 select e.empno, e.ename, d.deptno from emp e full join 
dept d on e.deptno = d.deptno;

4. 排序

4.1 全局排序（Order By）

Order By:全局排序，只有一个Reducer

4.1.1 使用Order by子句排序

ASC：升序
DESC：降序
例子：

select * from emp order by sal;

4.2 每个Reducer内部排序（Sort By）

Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排
序，此时可以使用 sort by。

1. 设置reducer个数
2. 查看reduce个数
3. 根据部门编号降序查看员工信息
4. 将查询结果导入到文件中

4.3 分区（Distribute By）

先按照部门编号分区，再按照员工编号降序排序。

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

4.4 Cluster By

当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式

#两种写法等同
select * from emp cluster by deptno;
 select * from emp distribute by deptno sort by deptno;

5. 分区表和分桶表

5.1 分区表

5.1.1 分区表基本操作

1. 引入分区表

2. 创建分区表语法

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

3. 加载数据到分区表

load data local inpath '/opt/module/..txt' into table dept_par partition(day='2021-07-22')

4. 分区查询
   使用where来查询

select * from dept _par where day = '2021-07-22'

5. 新增分区

alter table dept_partition add partition(day='2021-07-23')

6. 删除分区

alter table dept_partition drop partition(day='2021-07-22')

5.1.2 二级分区

1. 创建二级分区

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

2. 正常加载数据

load  data loacl inpath '/opt/...txt' into table dept_par2(day='2021-07-22', hour='12')

3. 查询分区数据

select * from dept_par2 where day='2021-07-22' and hour = '12'

4. 把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式
   1）方式一：上传数据后修复

msck repair table dept_par

2）方式二：上传数据后添加分区

alter table dept_par add partition(day='2022-07-25')

2）方式三：创建文件夹后load数据

5.1.3 动态分区调整

1. 开启同台分区参数设置
2. 设置为非严格模式

set hive.exec.dynamic.patition.mode = nonstrict;

3. 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000

hive.exec.max.dynamic.partitions=1000

4. 在每个执行 MR 的节点上，最大可以创建多少个动态分区

hive.exec.max.dynamic.partitions.pernode=100

5. 整个 MR Job 中，最大可以创建多少个 HDFS 文件

hive.exec.max.created.files=100000

6.当有空分区生成时，是否抛出异常。一般不需要设置，默认false;

hive.error.on.empty.partition=false

5.2 分桶表

分区针对路径
分桶针对数据

1. 创建分桶表语句

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

2. 查看表结构

desc formatted stu_buck;

3. 导入数据到分桶表中

load data inpath '/student.txt' into table stu_buck;

4. 查看创建的分桶表是否分成4个桶

5. 查询分桶的数据

select * from stu_buck;

6. 分桶表操作注意
   1）reduce的个数设置为-1，让job自行决定多少个reduce或将 reduce 的个
   数设置为大于等于分桶表的桶数
   2）从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题
   3）不要使用本地模式
7. insert方式导入分区表

insert into table stu_buck select * from student_insert;

5.3 抽样查询

#语法: TABLESAMPLE(BUCKET x OUT OF y)
 select * from stu_buck tablesample(bucket 1 out of 4 on 
id);

x表示从哪个桶开始找数据
y表示分为多少分
注意：x 的值必须小于等于 y 的值

6. 函数

6.1 系统内置函数

1. 查询系统自带函数

show function

2. 显示自带的函数的用法

desc function upper;

3. 详细显示自带的函数的用法

des funciton extended upper;  

6.2 常用内置函数

6.2.1 空字段赋值

1. 函数说明
   NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如
   果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数
   都为 NULL ，则返回 NULL。
2. 函数用法例子

#1）若查询出的数据为null，就用-1代替
select nvl(name, -1) from test;
#2）若查询出的数据为null，就用id代替
select nvl(name, id) from test;

6.2.2 CASE WHEN THEN ELSE END

1. 函数用法：

select 
       dept_id,
       sum(case set when '男' then 1 else 0 end) maleCount,
       sum(case set when '女' then 1 else 0 end) femaleCount
from 
      emp_sex
group by dept_id;

6.2.3 行转列

1. 相关函数说明
   CONCAT(string A/col, string B/col...):返回输入字符串后的结果，支持任意个输入字符串
   CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参
   数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将
   为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接
   的字符串之间;
   注意：
   CONCAT_WS must be "string or array<string>
   COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重
   汇总，产生 Array 类型字段。

6.2.4 列转行

函数说明
EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此
基础上可以对拆分后的数据进行聚合
例子写法

6.2.5 窗口函数（开窗函数）

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW：当前行
n PRECEDING：往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING 表示到后面的终点
LAG(col,n,default_val)：往前第 n 行数据
LEAD(col,n, default_val)：往后第 n 行数据
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对
于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

例子查询：

select 
name,
orderdate,
cost,
sum(cost) over(partition by name)
from test;


#3. 将每个顾客的cost按照日期进行累加
#在over中加入order by orderdate，可以实现
select 
      name,
      orderdate,
      cost,
      sum(cost) over(partition by name order by orderdate)
 from business;
#4. 查询上一次购买时间
select 
    name,
   orderdate,
   lag(orderdate, 1) over (partition by name order by orderdate)
from business;
#5. 查询前20%时间的订单信息
#5.1 先查询分组
select 
        name, 
        orderdate, 
        cost,
        ntile(5) over(order by orderdate)
from business;
#5.2 查询groupId为1的就行
select 
        name, 
        orderdate, 
        cost
from (
  select 
        name, 
        orderdate, 
        cost,
        ntile(5) over(order by orderdate)
from business
) t1
where groupId = 1

6.2.6 Rank

RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

6.3 自定义函数

根据用户自定义函数类别分为以下三种：

1. UDF
   一进一出
2. UDAF
   聚合含糊，多进一出
3. UDTF
   一进多出
4. 编程步骤
   1）继承Hive提供的类
   org.apache.hadoop.hive.ql.udf.generic.GenericUDF
   org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
   2）实现类中的抽象方法
   3）在 hive 的命令行窗口创建函数
   a. 添加jar

add jar linux_jar_path

b. 创建 function

create [temporary] function [dbname.]function_name AS class_name;

4. 在hive的命令行窗口删除函数语法

drop [temporary] function [if exists] [dbname.]function_name;