Hive

1.hive入门

1.1 什么是hive

是由Facebook开源用于解决海量结构化日志的数据统计；

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

本质是：将HQL转化成MR程序

（1）hive处理的数据存储在HDFS；

（2）hive分析数据底层的实现是MR；

（3）执行程序运行在yarn上；

1.2 Hive的优缺点

1.2.1 优点

（1）操作接口采用类sql语法，提供快速开发的能力（简单、容易上手）；

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

（3）hive的执行延迟比较高，因此hive常用于数据分析，对实时性要求不高的场合；

（4）hive优势在于处理大数据，对于处理小数据没有优势，因为hive的执行延迟比较高；

（5）hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数；

1.2.2 缺点

1. hive的HQL表达能力有限

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

（2）数据挖掘方面不擅长；

2. hive的效率比较低

（1）hive自动生成的MR作业，通常情况下不够智能化；

（2）hive调优比较困难，粒度较粗；

1.3 hive架构原理

1. 用户接口：Client

CLI（hive shell）、JDBC/ODBC（java访问hive）、WEBUI（游览器访问hive）

2. 元数据：Metastore

元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否外部表）、表的数据所在目录等；

默认存储在自带的derby数据库中，推荐使用mysql存储Metastore

3. hadoop

使用HDFS进行存储，使用MR进行计算。

4. 驱动器：Drive

（1）解析器（SQL Parser）：将SQL字符串转换为抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。

（2）编译器（Physical Plan）：将AST编译生成逻辑执行计划。

（3）优化器（Query Optimizer）：对逻辑执行计划进行优化。

（4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于hive来说，就是MR/Spark。

2. Hive数据类型

2.1 基本数据类型

红标为常用的数据类型；

对于hive的string类型就相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中对多能存储多少个字符；

2.2 集合数据类型

2.3 类型转换

可以使用cast操作显示进行数据类型转换；

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

3. DDL数据定义

3.1 创建数据库 

创建一个数据库，数据库在HDFS上的默认存储路径是/opt/hive/warehouse/*.db

create database hive_test ;

避免要创建的数据库已经存在的错误，可以增加 if not exists 判断（标准写法）

create database if not exists hive_test ;

 创建一个数据库，指定数据库在HDFS上存放的位置

create database if not exists hive_test location 'hdfs路径' ;

3.2 查询数据库

显示数据库

show databases ;

过滤显示查询的数据库

show databases like 'hive_test*' ;

查看数据库详情

desc databases hive_test ;

切换当前数据库

use 目标数据库名称 ;

3.3 删除数据库

删除空数据库

drop databases 库名 ;

如果删除的数据库不存在，最好采用if exists判断数据库是否存在

drop databases if exists 库名 ; 

如果数据库不为空，可以采用cascade命令，强制删除

drop databases 库名 cascade ;

3.4 创建表

建表语法

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]

字段解释说明

（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

（3）COMMENT：为表和列添加注释。

（4）PARTITIONED BY创建分区表

（5）CLUSTERED BY创建分桶表

（6）SORTED BY不常用

（7）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, …)]

用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。

SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。

（8）STORED AS指定存储文件类型

常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）

如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

（9）LOCATION ：指定表在HDFS上的存储位置。

（10）LIKE允许用户复制现有的表结构，但是不复制数据。

3.4.1 内部表

默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/opt/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

普通创建表

create table if not exists student(
 id int
,name string  
)
row format delimited fields terminated by '\t' ;

根据查询结果创建表（查询的结果会添加到新创建的表中）

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

查询表的类型

desc formatted student ;

3.4.2 外部表

因为表是外部表，所以hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述的元数据信息会被删除掉。

在企业中，大部分都是创建外部表，内部表容易被误删，只有自己使用的临时表才创建为内部表

管理表和外部表的使用场景：

  每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过select + insert进入内部表。

3.4.3 管理表与外部表的互相转换

修改内部表students为外部表

alter table student set tblproperties('EXTERNAL' = 'TRUE') ;

修改外部表student为内部表

alter table student set tblproperties('EXTERNAL' = 'FALSE') ;

注：('EXTERNAL' = 'TRUE')和('EXTERNAL' = 'FALSE')为固定写法，区分大小写。

3.5 分区表（partition）

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

3.5.1 建立一级分区表

create table 分区表名称 (
字段名称1 数据类型，
字段名称2 数据类型，
......
字段名称n 数据类型
)
partitioned by（分区字段名称1 数据类型）
row format delimited
fields terminated by '分割符'；
collection items terminated by '分割符'
map keys terminated by '分割符'
lines terminated by '分割符'

例：

create table student (
 id int
,name string 
)
partitioned by (month string)
row format delimited fields terminated by '\t' 
;

3.5.2 建立二级分区表

create table 分区表名称 (
字段名称1 数据类型，
字段名称2 数据类型，
......
字段名称n 数据类型
)
partitioned by（分区字段名称1 数据类型，分区字段名称2 数据类型）
row format delimited
fields terminated by '分割符'
；

 列：

create table student (
 id int 
,name string 
)
partitioned by (month string, day string)
row format delimited fields terminated by '\t'
;

3.5.3 加载数据到分区表

1. 加载数据到一级分区表

load data local inpath '本地路径文件' into table 分区表名 partition(分区字段名称1=字段值)；

例：

load data local inpath '/opt/student.txt' into table student partition(month = 20230510) ;

2. 加载数据到二级分区表

load data local inpath '本地路径文件' into table 分区表名 partition(分区字段名称1=字段值，分区字段名称2=字段值)；

例：

load data local inpath '/opt/student.txt' into table sutdent partiton(month='20230510',day=13) ;

3.5.4 分区的增删改查

1. 分区查询

select 需要查询字段 from 表名 where 分区字段名称=字段值

 

2. 增加分区

（1）增加单个分区

alter table 表名 add partition(分区字段='分区字段值') ;

（2）同时创建多个分区

alter table 表名 add partition(分区字段='分区字段值1') partition(分区字段='分区字段值2') ;

 

3. 删除分区

（1）删除单个分区

alter table 表名 drop partition(分区字段='分区字段值') ;

（2）删除多个分区

alter table 表名 drop partition(分区字段='分区字段值1'), partition(分区字段='分区字段值2') ;

 

4. 查询表中分区

show partitions 表名 ;

 

5. 查看分区表结构

desc formatted dept_partition ;

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

1. 上传数据后修复

先上传数据到分区表，然后通过select命令查看

select * from dept_partition2 where month='201709' and day='12';

并没有查询到刚上传的数据，然后执行修复命令

msck repair table dept_partition2;

 

2. 上传数据后添加分区

上传数据

dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=11;

执行添加分区

alter table dept_partition2 add partition(month='201709', day='11');

 

3. 创建文件夹后 load 数据到分区

创建目录

 dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=10;

上传数据

load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');

3.5.6 load加载数据不指定分区

不指定分区，会产生一个默认分区，后续有不指定分区的都放默认分区。

3.6 修改表

3.6.1 重命名表/增加/修改/替换列信息

1. 重命名表

ALTER TABLE table_name RENAME TO new_table_name ;

 

2. 增加和替换列

ALTER TABLE table_name ADD | REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ......) ;

注：ADD是代表新增一字段，字段位置在所有列后面（partition列前），REPLACE则是表示替换表中所有字段。

例：

-- 添加列
alter table student add columns(couse string) ;
-- 替换列
alter table student replace columns(id2 int, name2 string, couse2 string) ;

 

3. 更新列

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST | AFTER column_name] ;

例： 

alter table student change column id id2 int ;

3.6.2 删除表

drop table 表名 ;

注意：外部表不能简单的通过这个命令删除，这个命令只能删除外部表的元数据，没有办法删除HDFS上面的数据，如果需要将外部表彻底删除，有以下方法：

1. 转换为内部表再删除

ALTER TABLE table_name SET TBLPROPERTIES('EXTERNAL' = 'FALSE') ;
drop table table_name ;

2.删除元数据，然后使用HDFS删除数据

4. DML数据操作

4.1 数据导入

4.1.1 向表中装在数据（Load）

load data [local] inpath '路劲' [overwrite] into table 表名 [partition (partcol1=val1,......)] ; 

1. load data：表示加载数据；

2. local：表示从本地加载数据到hive表；否则从HDFS加载数据到hive表；

3. inpath：表示加载数据的路劲；

4. overwrite：表示覆盖表中的已有数据，否则表示追加；

5. into table：表示加载到哪张表；

6. partition：表示上传到指定分区；

4.1.2 通过查询语句向表中插入数据（insert）

基本插入：

-- 1
insert into table sutdent partition(month='202305') values(12,'wl') ;
-- 2
insert overwrite table student partition(month='202305') select id, name from student1 where month='202305' ;

多插入：

from table_name 
  insert overwrite table partition_name partition(month='202305')
  select id, name where month='202305'
  insert overwrite table partition_name partition(month='202305')
  select id, name where month='202305'
;

4.1.3 查询语句中创建表并加载数据（as select）

根据查询结果创建表（查询的结果会添加到新创建的表中）

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

4.1.4 创建表时通过location指定加载数据路劲

创建表，并指定在hdfs上的位置

create table if not exists student5(
id int, name string)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';

上传数据到hdfs上

dfs -put /opt/datas/student.txt /opt/hive/warehouse/student ;

4.1.5 import数据到指定hive表中

import table student partition(month='202305') from '/opt/hive/warehouse/export/student' ;

注：先用export导出后，再将数据导入

4.2 数据导出

4.2.1 insert导出

1. 将查询的结果导出到本地

insert overwrite local directory '/opt/datas' select * from table_name ;

2. 将查询的结果格式化导出到本地

insert overwrite local directory '/opt/datas/'
row format delimited
fields terminated by '|'
select * from table_name 
;

3. 将查询的结果导出到HDFS上（没有local）

insert overwrite directory '/opt/datas'
row format delimited 
fields terminated by '|'
select * from table_name
;

4.2.2 hadoop命令导出到本地

dfs -get /opt/hive/student.txt /opt/datas/student/student.txt ;

4.2.3 hive shell命令导出

基本语法：（hive -f/-e 执行语句或者脚本 > file）

hive -e 'select * from hivetest.student ;' > '/opt/datas/student.txt' ;

注：需要在shell窗口执行，需要库名.表名，需要本地夹存在。

4.2.4 export导出到hdfs上

export table hivetest.student to '/opt/datas' ;

5. 查询

查询语句语法：

[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available
 starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

5.1 基本查询（select...from）

select * from emp;

5.2 having语句

having与where不同点

1. where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。

2. where后面不能写分组函数，having后面可以写分组函数。

3. having只用于group by分组统计语句。

例：求每个部门平均薪水大于20k的部门

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

5.3 join语句

1. 内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

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

2. 左外连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

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

3. 右外连接

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

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

4. 满外连接

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

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

注：连接谓词不支持or！

5.4 排序

5.4.1 全局排序（order by）

order by：全局排序，只有一个reducer

ASC：升序（默认）；DESC：降序。

order by 和数据库中的Order by 功能一致，但是在hive.mapred.mode=strict模式下必须指定limit否则执行会报错。

原因：在order by状态下所有数据会到一台服务器进行reduce操作也即只有一个reduce，如果在数据量大的情况下会出现无法输出结果的情况，如果进行limit n，数据量小了，只有一个reduce也可以处理过来。

5.4.2 每个MR内部排序（sort by）

sort by：每个reduce内部进行排序，对全局结果集来说不是排序。

不是全局排序，其在数据进如入reduce前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reduce的输出有序，不保证全局排序。

sort by不受hive.mapred.mode是否为strict，nostrict的影响。

使用sort by的数据只能保证在同一reduce中的数据可以按指定字段排序。

使用sort by你可以指定执行的reduce个数（set mapred.reduce.tasks=），对输出的数据在执行归并排序，既可以得到全部结果。

注意：可以用limit子句大大减少数据量，使用limit n后，传输到reduce端（单机）的数据记录减少，否则由于数据过大可能出不了结果。

1. 设置reduce个数

set mapreduce.job.reduces=3 ;

2. 查看设置reduce个数

set mapreduce.job.reduces ;

3. 根据部门编码降序查看员工信息

select * from table_name sort by dep_id ;

对于全局结果来说并没有排序，只是对每个reduce的结果进行了排序；

5.4.3 分区排序（distribute by）

类似MR中的partition，进行分区，结合sort by使用。

注意：hive要求distribute by语句要写在sort by语句之前。

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

在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by和sort by常见使用场景有：

（1）map、reduce输出文件大小不均；

（2）小文件过多、文件超大。

例：

要求学生的成绩按照专业进行排序，

select * from student 
distribute by major sort by grade ;

如果reduce的数量小于专业的数量，就会出现一个reduce存在多个专业的情况，这时候，如果还是以上写法，就会乱序：即：多个专业分到一个分区，且直接按照分数排序；

对于这种情况，我们sort by先按照专业排序，在分数排序：

select * from student 
distribute by major sort by major, grade ;

5.4.4 cluster by 

等价于distribute by + sort by 

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

cluster by排序只能是升序排序，不能指定排序规则为ASC或者DESC；

例：两种方法等价

select * from student distribute by grade sort by grade ;
select * from student cluster by grade ;

5.5 分桶（buckets）及抽样查询

5.5.1 分通表数据存储

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

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过得要确定合适的划分大小这一疑虑。

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

设置分桶属性：

set hive.enforce.bucketing=true ;

创建分通表，将表分为8个桶：

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

导入数据到分桶表，通过子查询的方式：

insert into table student
select id, name from stu1 ;

注：分桶表只能通过insert插入数据，load读取数据是无效的。

5.5.2 分桶抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果，hive可以通过对表进行抽样来满足这个需求：

select * from student tablesample(
  bucket 1 out of 4 on id
) ;

注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y ON 分桶字段)。

  x：从几桶开始取数据；

  y：可以通过y和定义的分桶数推导出此次需要取得数据的桶数。桶数推导规则：桶数=定义的n桶/y

而y只能是定义的桶数n的倍数或者因子。

注：这里的x不能大于y。

抽样查询常用的写法：

select * from stu tablesample(bucket 1 out of 4 on id) ;
select * from stu tablesample(bucket 1 out of 4 on id) where name='xx' ;
-- 行数
select * from stu tablesample(3 rows) ; 
-- 数量的百分比
select * from stu tablesample(30 percent) ;
-- B K M G T 具体数量
select * from stu tablesample(6B) ; 
-- rand()随机抽样
select * from stu tablesample(bucket 1 out of 4 on rand()) ;
-- 随机抽3行数据
select * from stu order by rand() limit 3 ;

 5.5 其他常用查询函数

5.5.1 空字段赋值

NVL：给值未NULL的数据赋值，它的格式是NVL(string_name, replace,replace_with)。它的功能是如果string_name为NULL，则NVL函数返回replace_with的值，如果两个参数都为NULL，则返回NULL。

例：grade字段存在NULL值，将NULL修改为1

select id，name, nvl(grade,1) from student ;

5.5.2 行转列

CONCAT_WS(separator, str1, str2, str3, ......)：它是一个特殊形式的CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是NULL，返回值也将未NULL。这个函数会跳过分隔符参数后的任何NULL和空字符串。分隔符将被加到被连接的字符串之间；

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

5.5.3 列转行

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

LATERAL VIEW udtf(expression) tableAlias AS columnAlias

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

5.5.4 窗口函数

1. 相关函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化；

CURRENT ROW：当前行；

n PRECEDING：往前n行数据；

n FOLLOWING：往后n行数据；

UNBOUNDED：起点，UNBOUNDED PRECEDING表示从前面的起点，UNBOUNDED FOLLOWING表示到后面的终点；

LAG(col, n)：往前第n行数据；

LEAD(col, n)：往后第n行数据；

NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行NTILE返回此行所属的组的编号。注：n必须为int类型。

5.5.5 Rank

1. 函数说明

RANK()排序相同时会重复，总数不会变；

DENSE_RANK()排序相同时会重复，总数会减少；

ROW_NUMBER()：会根据顺序计算；