Hive笔记整理

第1章 Hive简介

1.1 Hive是什么

​ Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。

​ 本质是：将HQL转化成MapReduce程序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Mh9IO31I-1621844364484)(C:\Users\guanh\AppData\Roaming\Typora\typora-user-images\1599050462293.png)]

​ （1）Hive处理的数据存储在HDFS

​ （2）Hive分析数据底层的实现是MapReduce

​ （3）执行程序运行在Yarn上

• ​ Hive是一种数据仓库软件，使用SQL来促进对分布式设备上存储的大量数据集进行读写和管理

• ​ SQL（结构化查询语句），使用SQL的前提是需要有一张表

• ​ Hive分析数据必须可以映射为一个表结构

• ​ Hive提供了JDBC驱动和命令行工具，让用户连接Hive

• ​ Hive基于Hadoop，用来分析Hadoop上存储的结构化数据

1.2 Hive的特征

• Hive不是一个关系型数据库

• Hive不是基于OLTP(在线事务处理)设计

• Hive不支持实时查询和行级别更新

• Hive在关系型数据库存储元数据，处理的数据存储在Hadoop

• Hive基于OLAP（在线分析处理）设计

• Hive提供了一个类SQL语句，称为HQL，方便查询

• Hive简单，易用，可扩展，可伸缩

1.3 本质

• hive使用HQL（类SQL），分析HDFS上存储的结构化数据

• hive创建的表、库等信息，属于schame(元数据)，这些元数据，存储在RDMS（关系型数据库软件）

• 将HQL转换为MapReduce摸板，对HDFS上的数据进行查询

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

1.4 wordcount例子

1. 将文件先上传到hdfs

2. 需要对数据进行ETL，转为结构化的数据

   hai tao tao xinxin xinxin yuan yuan

   转化为

   hai	1
   tao	1
   tao	1
   xinxin	1
   ixnxin	1
   ...
   

3. 根据数据进行建表

   create table t1(word varchar ,num int)
   

   表的结构存储在hdfs

4. 将数据加载到表中或和表产生关联

5. 编写HQL

   select word,sum(num)
   from t1 
   group by word
   

6. 客户端返回HQL运行的结果

1.5 Hive架构原理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tHjoXLqu-1621844364486)(C:\Users\guanh\AppData\Roaming\Typora\typora-user-images\1599053402763.png)]

第2章 Hive安装

2.1 Hive安装地址

1）Hive官网地址

http://hive.apache.org/

2）文档查看地址

https://cwiki.apache.org/confluence/display/Hive/GettingStarted

3）下载地址

http://archive.apache.org/dist/hive/

4）github地址

https://github.com/apache/hive

2.2 MySql安装

1）检查当前系统是否安装过Mysql

​ rpm -qa|grep mariadb

​ 如果存在，卸载

​ sudo rpm -e --nodeps mariadb-libs

3）解压MySQL安装包

​ mkdir mysql-lib

​ tar -xf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar -C mysql-lib

4）在安装目录下执行rpm安装

​ sudo rpm -ivh mysql-community-common（libs，libs，client，server）

5）删除/etc/my.cnf文件中datadir指向的目录下的所有内容,如果有内容的情况下

6）初始化数据库

​ [atguigu @hadoop102 opt]$ sudo mysqld --initialize --user=mysql

7）查看临时生成的root用户的密码

​ [atguigu @hadoop102 opt]$ sudo cat /var/log/mysqld.log

8）启动MySQL服务

​ [atguigu @hadoop102 opt]$ sudo systemctl start mysqld

…）

[atguigu @hadoop102 opt]$ mysql -uroot -p
Enter password:   输入临时生成的密码
mysql> set password = password("123456");
mysql> update mysql.user set host='%' where user='root';
mysql> flush privileges;

2.2 Hive安装

[atguigu@hadoop102 software]$ tar -zxvf /opt/software/apache-hive-3.1.2-bin.tar.gz -C /opt/module/

[atguigu@hadoop102 software]$ mv /opt/module/apache-hive-3.1.2-bin/ /opt/module/hive

[atguigu@hadoop102 software]$ sudo vim /etc/profile

#HIVE_HOME
export HIVE_HOME=/opt/module/hive
export PATH=$PATH:$HIVE_HOME/bin
[atguigu@hadoop102 bin]$ source /etc/profile

[atguigu@hadoop102 software]$ mv $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.jar $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.bak

2.4 Hive元数据配置到MySql

2.4.1 拷贝驱动

​ [atguigu@hadoop102 software]$ cp /opt/software/mysql-connector-java-5.1.37.jar $HIVE_HOME/lib

2.4.2 配置Metastore到MySql

在$HIVE_HOME/conf目录下新建hive-site.xml文件

[atguigu@hadoop102 conf]$ vim hive-site.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <!-- jdbc连接的URL -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://hadoop102:3306/metastore?useSSL=false</value>
</property>

    <!-- jdbc连接的Driver-->
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name> 
        <value>com.mysql.jdbc.Driver</value>
</property>

	<!-- jdbc连接的username-->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>

    <!-- jdbc连接的password -->
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>123456</value>
    </property>
    <!-- Hive默认在HDFS的工作目录 -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive/warehouse</value>
    </property>
   
    <!-- 指定hiveserver2连接的端口号 -->
    <property>
        <name>hive.server2.thrift.port</name>
        <value>10000</value>
    </property>
    <!-- 指定hiveserver2连接的host -->
    <property>
        <name>hive.server2.thrift.bind.host</name>
        <value>hadoop102</value>
</property>

    <!-- 指定存储元数据要连接的地址 -->
    <property>
        <name>hive.metastore.uris</name>
        <value>thrift://hadoop102:9083</value>
    </property>
    <!-- 元数据存储授权  hive默认不允许去外部存储,所以得关闭授权-->
    <property>
        <name>hive.metastore.event.db.notification.api.auth</name>
        <value>false</value>
</property>
<!-- Hive元数据存储版本的验证-->
    <property>
        <name>hive.metastore.schema.verification</name>
        <value>false</value>
</property>

<!-- hiveserver2的高可用参数，开启此参数可以提高hiveserver2的启动速度 -->
<property>
    <name>hive.server2.active.passive.ha.enable</name>
    <value>true</value>
</property>
</configuration>

2.5 HiveJDBC访问

1）启动beeline客户端

[atguigu@hadoop102 hive]$ bin/beeline -u jdbc:hive2://hadoop102:10000 -n atguigu

2.6 Hive常用交互命令

bin/hive -e "select id from student;"

hive -f hive.sql >> result.txt

2.7 Hive其他命令操作

hive(default)>exit;
hive(default)>quit;

第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（65535byte）类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符

create table person(id int, name string, weight double, money bigint);
insert into table person values(001, 'yuanyuan', 55, 1000000000);

3.2集合数据类型

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


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 '_'
--k:v 分割符
map keys terminated by ':'
lines terminated by '\n';

查songsong的第一个朋友,孩子xiao song 年龄，邮编

select name, friends[0], children['xiao song'], address.email
from test where name='songsong';

查songsong的第一个朋友，第一个孩子，邮编

select name, friends[0], map_keys(children)[0],map_values(children)[0], address.email from test where name='songsong';

3.3 类型转化

• hive最大类型为double,string会自动转化为double(如：‘1.1’+1)

• CAST(‘1.1’ AS INT) 可以将string强制转换为int，CAST(‘1.1’ AS INT) 可以将double强制转换为int

第4章 DDL数据定义

4.1 数据库的DLL

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, ...)];

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

4.1.2查

--展示所有的数据库
show databases;
--模糊查询
show databases like 'db_hive*'；
--描述数据库
desc database 库名；

4.1.3切换数据库

use 库名；

4.1.4改

4.1.5删除

drop database 库名；
drop database if exists 库名;
drop database 库名 cascade； --强制删除

4.2 表的DLL

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] 
 	[COLLECTION ITEMS TERMINATED BY char]
    [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 
   	| SERDE serde_name [WITH SERDEPROPERTIES 				 		(property_name=property_value, property_name=property_value, 	...)
] 
[STORED AS file_format] 	--表对应hdfs路径下文件的存储路径
[LOCATION hdfs_path]	--对于表而言的hdfs路径
[TBLPROPERTIES (property_name=property_value, ...)]	--表的属性
[AS select_statement]	--根据查询结果生成一张表
[LIKE table_name]	--模仿一张表，只有表结构

4.2.1 管理表(内部表)

默认创建的表就是管理表，hive控制着数据的生命周期，当我们删除管理表时，hive也会删除这个表中的数据。

**创建内部表：**中间表和测试表常用

--创建内部表
create table student(id int, name string)
row format delimited fields terminated by '\t'; 

--根据查询结果创建一张表，它使用默认分割符
create table student2 as select * from student;

--根据查询结果创建一张表，指定分隔符
create table student2 
row format delimited fields terminated by '\t'
as select * from student;

--根据存在的表的结构创建表 
create table student3 like student;

查

show tables;
--描述表
desc student;
--表的详情
desc formatted student;

4.2.2 外部表

hive并非认为其完全拥有这份数据，所以删除表时，只会删除表的元数据，不会删除这份数据。

创建外部表：

--创建部门表
create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t'
location '/company/dept';	--指定表在hdfs的位置

--创建员工表
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';

删：

--删除外部表
drop table dept;
--删除内部表
drop table student3;
--清空表,不能清空外部表
truncate table student;

改：

--改表名，会连同hdfs文件名一起改掉
alter table old_name rename to new_name;
--更新列,改的列名只能由小往大改，或者不变
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
--增加和替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

--添加列
alter table dept add columns(deptdesc string);
--更新列
alter table dept change column deptdesc desc string;
--替换列，对应部分应该满足大小关系，不能往小改
alter table dept replace columns(deptno string, dname string, loc string);

4.5.3 转换表（内<=>外）

Table Type: 	EXTERNAL_TABLE  	NULL 
Table Parameters: 	EXTERNAL         TRUE       
--内部表转换为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
alter table student2 set tblproperties('EXTERNAL'='FALSE');

第5章 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/stu.txt' overwrite into table student;
--hdfs导入数据 以剪切的方试导入
load data inpath '/stu.txt' into table student;

5.1.2 insert

--追加插入
insert into table student2 values(1,'taotao'),(2,'yuanyuan');
--覆盖插入
insert overwrite table student2 values(1,'taotao'),(2,'yuanyuan');
--查询插入 插入的表必须存在 查询的列必须和插入的对应
insert into table student2 select id, name from stu;
insert overwrite table student2 select id, name from stu;

5.1.3 as select

--根据查询结果创建一张表，指定分隔符
create table if not exists student3 
row format delimited fields terminated by '\t'
as select * from student;
--create table tab_name as select 和 insert into table tab_naem select 这两个用来创建中间表

5.1.5 Import数据到指定Hive表中

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

--hive (default)>
import table student2  from
 '/user/hive/warehouse/export/student';

5.2数据导出（少）

5.2.1 insert导出

--覆盖的方法导出 【local】没有的情况下为hdfs路径
insert overwrite local directory '/opt/module/hive/datas/export/student' 
row format delimited fields terminated by '\t'
select * from student;

5.2.2 Hadoop命令导出到本地

--hive (default)>
dfs -get /user/hive/warehouse/student/student.txt
/opt/module/hive/datas/export/student3.txt;

5.2.3 Hive Shell 命令导出

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

5.2.4 Export导出到HDFS上

--hive (default)>
export table default.student to '/user/hive/warehouse/export/student';

export和import主要用于两个Hadoop平台集群之间Hive表迁移，不能直接导出到本地。

第6章 查询

6.1 基本查询

查询语句语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...  --[dis]去重
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]	--分组
  [HAVING col_list]		--分组过滤
  [ORDER BY col_list]	--全局排序
  [CLUSTER BY col_list	--hive里的4个by
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number] 	--限制输出的行数（翻页）

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

建表：

--建表语句见4.2.1
--导入数据
load data local inpath '/opt/module/hive/datas/dept.txt' into table
dept;
load data local inpath '/opt/module/hive/datas/emp.txt' into table emp;

6.1.4 常用函数

set hive.exec.mode.local.auto=true; --开启本地模式

select count(*), max(sal), min(sal),sum(sal), avg(sal) from emp;

6.1.5 Limit语句

--典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
select * from emp limit 5;
select * from emp limit 2,3;

6.1.7 比较运算符

操作符	支持的数据类型	描述
A [NOT] BETWEEN B AND C	基本数据类型	如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL	所有数据类型	如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)	所有数据类型	使用 IN运算显示列表中的值
A [NOT] LIKE B	STRING 类型	B是一个SQL下的简单正则表达式，也叫通配符模式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B	STRING 类型	B是基于java的正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

6.2 分组

Group By语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

--计算emp表每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
--计算emp每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal from emp t group by
 t.deptno, t.job;

Having语句

having与where不同点

• where后面不能写分组聚合函数，而having后面可以使用分组聚合函数。
• having只用于group by分组统计语句。

--求每个部门的平均薪水大于2000的部门
select deptno, avg(sal) avg_sal from emp group by deptno  having
 avg_sal > 2000;

6.3 Join语句

Hive支持通常的SQL JOIN语句，但是只支持等值连接，（这个版本）支持非等值连接。

--根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；
select 
  e.empno, 
  e.ename, 
  d.dname 
from emp e join dept d 
on e.deptno = d.deptno;

--左外连接：JOIN操作符左边表中所有的都会保留下来，右表中符合WHERE子句的所有记录将会被返回。
select
 e.empno,
 e.ename, 
 d.deptno 
from emp e left join dept d
on e.deptno = d.deptno;

--右外连接:JOIN操作符右表中所有的都会保留下来，左表中符合WHERE子句的所有记录将会被返回。
select
 e.empno,
 e.ename, 
 d.deptno 
from emp e right join dept d
on e.deptno = d.deptno;

--满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
select
 e.empno,
 e.ename, 
 d.deptno 
from emp e dept d
on e.deptno = d.deptno;

--union 竖向拼接两张表，去重
--union all 竖向拼接两张表,不去重

--多表连接
--注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件

--1）创建位置表
create table if not exists location(
 loc int,
 loc_name string
)
row format delimited fields terminated by '\t';

--2）导入数据
load data local inpath '/opt/module/hive/datas/location.txt' into table location;

--3）多表连接查询
select
 e.ename,
 d.dname, 
 l.loc_name
from emp e
join dept d
on d.deptno = e.deptno
join location l
on d.loc = l.loc;

6.4 排序

6.4.1 全局排序

全局排序，只有一个Reducer

ORDER BY ASC(默认升序)/ DESC(降序)

6.4.4 每个Reduce内部排序（Sort By）

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

sosrt by 为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。

--1）设置reduce个数
set mapreduce.job.reduces=3;
--2) 查看设置reduce个数
set mapreduce.job.reduces;
--3）根据部门编号降序查看员工信息
select * from emp 
sort by deptno desc;
--4）将查询结果导入到文件中（按照部门编号降序排序）
insert overwrite local directory
'/opt/module/hive/datas/sortby-result'
select * from emp
sort by deptno desc;

6.4.5 分区（Distribute By）

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

set mapreduce.job.reduces=3;
--distribute by（分区） and sort by（区内排序）
select *
from emp
distribute by deptno sort by sal;

--cluster by 分区排序（鸡肋）
select *
from emp 
cluster by deptno;

order by --全局排序
distribute by（分区） sort by --（区内排序） 一起使用
cluster by (即分区又排序) --分区、排序字段相同才能用 不能指定排序 用的少

第7章 分区和分桶

7.1 分区表

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

7.1.1 分区表基本操作

--创建分区表
create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';
--注意：分区字段不能是表中已经存在的列 可以将分区字段看作表的伪列。

--必须指定分区 最后的partition不加是错误的 可以运行 系统会默认分区
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';

--增 多分区不能加逗号
alter table  dept_partition add partition(day = '20200404') partition(day = '20200405');

--查 不能查非分区表
show partitions dept_partition;

--删 多分区必须加逗号 外部表只删元数据，文件会保留下来
alter table dept_partition drop partition(day = '20200405'),partition(day = '20200404');

--查看分区表结构
desc formatted dept_partition;

7.1.2 分区表二级分区

--创建二级分区表
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')
	
--查询分区数据
select * from dept_partition2 
where day='20200401' and hour='12';

--增
alter table  dept_partition add partition(day = '20200404', hour='13');

--删 如果二级分区没有了 一级分区也会删除
alter table dept_partition drop partition(day = '20200405'，hour='13' );

把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

--（1）上传数据后修复
dfs -mkdir -p
 /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=13;
hive (default)> dfs -put /opt/module/hive/datas/dept_20200401.log  /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=13;
--查询数据（查询不到刚上传的数据）
select * from dept_partition2 where day='20200401' and hour='13';
--执行修复命令
msck repair table dept_partition2;

--（2）上传数据后添加分区
alter table dept_partition2 
add partition(day='20200401',hour='14');

--（3）创建文件夹后load数据到分区
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 
partition(day='20200401',hour='15');

7.1.3 动态分区调整

对于分区表insert数据的时候，数据库自动会根据分区字段的值，将数据插入到相应分区当中

--开启动态分区功能 默认开启
hive.exec.dynamic.partition=true

--设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区）
hive.exec.dynamic.partition.mode=nonstrict

--创建分区表
create table dept_partition_dy(id int, name string) 
partitioned by (loc int) 
row format delimited fields terminated by '\t';

--设置动态分区
insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;

--二级分区 在严格模式下 加partition必须指定默认分区 不加自动分区
insert into table dept_partition_dy select deptno, dname, loc from dept;

7.2 分桶表

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

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

--可能会出错 会走MR 如果yarn调度的是另一台服务器 可能就会出错
load data local inpath 
'/opt/module/hive/datas/student.txt' 
into table stu_buck;

--创建一个即分区又分桶的表
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';

分桶规则：

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

分区和分桶的区别：

• 分区分的是目录，分桶分的是表里的数据
• 分区字段不能是表里的数据，分桶必须是表里的数据

第8章 函数

8.1 系统内置函数

日期函数：

1） unix_timestamp : 返回当前或指定的时间戳；
SELECT  unix_timestamp("2020-05-02 11:22:00"); ==>1588418520
2） from_unixtime : 将时间戳转化为日期格式
SELECT FROM_unixtime(1588418520); ==> 2020-05-02 11:22:00
3) current_date : 当前日期
4）current_timestamp: 当前日期 + 时间；
5）to_date : 获取日期部分
6）year/month/day/hour/minute/second() : 获取年、月、日、小时、分、秒
7）weekofyear(): 当前时间是一年中的第几周
8）dayofmonth(): 当前时间是一个月中的第几天
9）months_between() : 两个日期间的月份
10) datediff() : 两个日期相差的天数
11) add_months：日期加减月
12) date_add：日期加天数
13) date_sub：日期减天数
14) last_day: 日期的当月的最后一天

取整函数

1) round： 四舍五入
2) ceil：  向上取整
3) floor： 向下取整

字符串函数

1）upper： 转大写
2）lower： 转小写
3）length： 长度
4）trim：  前后去空格
5）lpad： 向左补齐，到指定长度
6）rpad：  向右补齐，到指定长度
7）regexp_replace： SELECT regexp_replace('100-200', '(\\d+)', 'num') ；
	使用正则表达式匹配目标字符串，匹配成功后替换！

集合操作

1） size： 集合中元素的个数
2） map_keys： 返回map中的key
3） map_values: 返回map中的value
4） array_contains: 判断array中是否包含某个元素
5） sort_array： 将array中的元素排序

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

--current_date: 当前日期
select current_date;
--current_timestamp：当前的日期加时间
--datediff(date1,data2) 相差天数
--date_add：日期加天数
select date_add('2018-08-08',2);   --2018-08-10

--date_format : 返回指定格式日期
select date_format ('2018-08-08 08:08:08', 'yyyy/MM/dd');

--round： 四舍五入
select round(3.3);
--floor： 向下取整
select floor(4.8);

substring(orderdate,1,7)='2017-04'

--upper： 转大写  lower： 转小写

--使用正则表达式匹配目标字符串 匹配成功后替换！
SELECT regexp_replace('100-200', '(\\d+)', 'num');
select regexp_replace('100-200', '-', '+');

--size： 集合中元素的个数 每一行数据中的friends集合里的个数
select size(friends) from test;
--map_keys： 返回map中的key
select map_keys(children) from test;
--返回map中的value
select map_values(children) from test;
--array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test;

--sort_array： 将array中的元素排序
select sort_array(array('a','d','c'));

8.2.1 空字段赋值

--NVL( value，default_value)
select comm,nvl(comm, -1) from emp;
select comm, nvl(comm,mgr) from emp;

--按照奖金和工资的和降序排序
select 
ename, 
sal+nvl(comm, 0) scall_comm
from emp
order by scall_comm desc;

8.2.2 CASE WHEN THEN ELSE END

--求出不同部门男女各多少人。结果如下：
dept_Id     男       女
A     		2       1
B     		1       2
--创建hive表并导入数据
create table emp_sex(
name string, 
dept_id string, 
sex string) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/hive/datas/emp_sex.txt' into table emp_sex;

--方法一
select dept_id, 
sum(case sex when '男' then 1 else 0 end) num_m,
sum(case sex when '女' then 1 else 0 end) num_w
from emp_sex
group by dept_id;

--方法二
select dept_id, 
sum(if(sex='男', 1, 0)) num_m,
sum(if(sex='女', 1, 0)) num_w
from emp_sex
group by dept_id;

8.2.3 行转列

--CONCAT(string A/col, string B/col…)
--CONCAT_WS(separator, str1, str2,...) CONCAT_WS must be "string or array<string>"
--COLLECT_SET(col)：基本数据类型，去重汇总，产生array类型字段。
--COLLECT_LIST(col)：基本数据类型，不去重汇总，产生array类型字段

--把星座和血型一样的人归类到一起。结果如下：
射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋|苍老师

select
concat_ws(',', constellation, blood_type),
concat_ws('|',collect_list(name))
from person_info
group by constellation, blood_type;

8.2.4 列转行(一列转多行)

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

表6-7 数据准备
movie	category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼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, cat
from movie_info
lateral view explode(split(category,',')) emp as cat;

select
 class,
 student_name,
 student_score,
 rank() over(partition by class order by student_score desc) as student_rank
from
default.classinfo
lateral view posexplode(split(student,',')) sn as student_index_sn,student_name
lateral view posexplode(split(score,',')) sc as student_index_sc,student_score
where
 student_index_sn = student_index_sc
order by class,student_rank

8.2.5 窗口函数（开窗函数）

1.什么是窗口函数
--窗口函数
LAG(col,n,default_val)：往前第n行数据
LEAD(col,n, default_val)：往后第n行数据
FIRST_VALUE (col,true/false)：当前窗口下的第一个值，第二个参数为true，跳过空值
LAST_VALUE (col,true/false)：当前窗口下的最后一个

--聚合函数
count
sum
min
max
avg
--排名函数
RANK
ROW_NUMBER
DENSE_RANK
NTILE

--相关函数说明
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行数据
FIRST_VALUE (col,true/false)：当前窗口下的第一个值，第二个参数为true，跳过空值 默认范围是 rows between unbounded preceding and current row
LAST_VALUE (col,true/false)：当前窗口下的最后一个值，第二个参数为true，跳过空值
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

--以下函数可以不要窗口子句
rank(),ntile(),denserank(),row_number, lag, lead.

当只有order by 没有窗口子句时，默认范围是 rows between unbounded preceding and current row

--（1）查询在2017年4月份购买过的顾客及总人数
select name,count(*) over()
from business
where substring(orderdate,1,7)='2017-04'
group by name;

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

--求所有月份的总人数 和人
select
  bus.orderdate,
  collect_set(bus.name) n_c,
  size(collect_set(bus.name)) n_s
from (
  select 
    name,
    date_format(orderdate,'yyyy-MM') orderdate,
    cost
  from business
) bus
group by bus.orderdate;

--（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)
from business;

--直接按照日期将花费累加

--每个顾客的cost按照日期求上一次和当前一次消费的和
select
  name,
  orderdate,
  cost,
  sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row)
from business;

--（4）查询顾客购买明细以及上次的购买时间和下次购买时间
select
  name,
  orderdate,
  cost,
  lag(orderdate,1,'0000-00-00') over (partition by name order by orderdate) prev_time,
  lead(orderdate,1,'1970-01-01') over(PARTITION by name order by orderdate) next_time
from business;

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

--（5）查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
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
  f1.name,
  f1.orderdate,
  f1.cost,
  f1.group_id
from  
 (select
  name,
  orderdate,
  cost,
  ntile(5) over(order by orderdate) group_id
  from business) f1
where f1.group_id=1;

8.2.6 Rank(窗口)

函数说明
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) rmp
from score;

8.3 自定义函数

根据用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）
	一进一出
（2）UDAF（User-Defined Aggregation Function）
	用户自定义聚合函数，多进一出
	类似于：count/max/min
（3）UDTF（User-Defined Table-Generating Functions）
	用户自定义表生成函数，一进多出
	如lateral view explode()

官方文档地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins

创建临时函数

（1）打成jar包上传到服务器/opt/module/hive/datas/myudf.jar
（2）将jar包添加到hive的classpath，临时生效
add jar /opt/module/hive/datas/myudf.jar;
（3）创建临时函数与开发好的java class关联
create temporary function my_len as "com.atguigu.hive.udf.MyUDF";
（4）即可在hql中使用自定义的临时函数
select ename,my_len(ename) ename_len from emp;
（5）删除临时函数
drop  temporary function my_len;

创建永久函数

注意 因为add jar 本身也是临时生效,所以在创建永久函数的时候，需要制定路径(并且因为元数据的原因,这个路径还得是hdfs上的路径)
create function my_len2 as "com.atguigu.hive.udf.MyUDF" using jar "hdfs://hadoop112:9820/udf/myudf.jar";

跨库使用要加库名

第9章 压缩和存储

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

开启map输出阶段压缩可以减少job中map和Reduce task间数据传输量

--（1）开启hive中间传输数据压缩功能（hive本身也希望自己控制下压缩）
set hive.exec.compress.intermediate=true;

--（2）开启mapreduce中map输出压缩功能
set mapreduce.map.output.compress=true;

--（3）设置mapreduce中map输出数据的压缩方式
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

--（4）执行查询语句
select count(ename) name from emp;

9.3 开启Reduce输出阶段压缩

--（1）开启hive最终输出数据压缩功能（hive希望能自己控制压缩）
set hive.exec.compress.output=true;
--（2）开启mapreduce最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;

--（3）设置mapreduce最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;

--（5）测试一下输出结果是否是压缩文件
set mapreduce.job.reduces=3;
insert overwrite local directory '/opt/module/hive/datas/compress/' select * from emp  sort by deptno desc;

9.4 文件存储格式

9.4.1 列式存储和行式存储

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qXX55aBS-1621844364488)(C:\Users\guanh\AppData\Roaming\Typora\typora-user-images\1600087300888.png)]

TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；
ORC和PARQUET是基于列式存储的。

1）行存储的特点
查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。
2）列存储的特点
因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。

9.4.2 TextFile格式

默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用，但使用Gzip这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

9.4.3 Orc格式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H8UcXJxD-1621844364489)(C:\Users\guanh\AppData\Roaming\Typora\typora-user-images\1600086656696.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9yXIzzQj-1621844364491)(C:\Users\guanh\AppData\Roaming\Typora\typora-user-images\1600086871292.png)]

9.4.4 Parquet格式

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

9.9.5存储和压缩

使用MR做引擎玩数仓的时候，文件存储格式可以为 orc+lzo

使用spark做引擎的时候，文件存储格式为 parquet+snappy

第10章 企业级调优

见老师文档

.output.compress=true;

–（3）设置mapreduce中map输出数据的压缩方式
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

–（4）执行查询语句
select count(ename) name from emp;

## 9.3 开启Reduce输出阶段压缩

```sql
--（1）开启hive最终输出数据压缩功能（hive希望能自己控制压缩）
set hive.exec.compress.output=true;
--（2）开启mapreduce最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;

--（3）设置mapreduce最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;

--（5）测试一下输出结果是否是压缩文件
set mapreduce.job.reduces=3;
insert overwrite local directory '/opt/module/hive/datas/compress/' select * from emp  sort by deptno desc;

9.4 文件存储格式

9.4.1 列式存储和行式存储

[外链图片转存中…(img-qXX55aBS-1621844364488)]

TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；
ORC和PARQUET是基于列式存储的。

1）行存储的特点
查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。
2）列存储的特点
因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。

9.4.2 TextFile格式

默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用，但使用Gzip这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

9.4.3 Orc格式

[外链图片转存中…(img-H8UcXJxD-1621844364489)]

[外链图片转存中…(img-9yXIzzQj-1621844364491)]

9.4.4 Parquet格式

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

9.9.5存储和压缩

使用MR做引擎玩数仓的时候，文件存储格式可以为 orc+lzo

使用spark做引擎的时候，文件存储格式为 parquet+snappy

第10章 企业级调优

见老师文档