一、Hive简介
是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据表，提供类SQL查阅功能。
本质是将SQL转换为MapReduce任务进行运算，Hive可以说就是一个MapReduce的客户端。
二、Hive架构
与Hadoop的关系
与传统数据库的对比
三、安装
1）单用户模式（mysql）
1、安装mysql并查看版本
mysql --version
2、下载对应版本的mysql驱动包
下载地址：downloads.mysql.com/archives/c-…
找到对应版本的驱动包mysql-connector-java-5.1.49.tar.gz
3、hive配置文件
hive-site.xml
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<!-- 配置hdfs存储目录 -->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive_remote/warehouse</value>
</property>
<!-- 本地模式 -->
<property>
<name>hive.metastore.local</name>
<value>true</value>
</property>
<!-- 所连接的 MySQL 数据库的地址，hive_local是数据库，程序会自动创建，自定义就行 -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://ip:3306/hive_local?createDatabaseIfNotExist=true&useSSL=false&serverTimezone=Asia/Shanghai</value>
</property>
<!-- MySQL 驱动 -->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<!-- mysql连接用户 -->
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<!-- mysql连接密码 -->
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
<!--元数据是否校验-->
<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
</property>
<property>
<name>system:user.name</name>
<value>root</value>
<description>user name</description>
</property>
</configuration>
hive-env.sh
export HADOOP_HOME=/opt/apache/hadoop
export HIVE_CONF_DIR=/opt/apache/hive/conf
export HIV_AUX_JARS_PATH=/opt/apache/hive/lib
3、编辑Dockerfile文件
在上次(hadoop部署)编写的Dockerfile中添加如下内容：
# 配置Hive
ENV HIVE_VERSION 3.1.3
ADD apache-hive-${HIVE_VERSION}-bin.tar.gz /opt/apache/
ENV HIVE_HOME=/opt/apache/hive
ENV PATH=$HIVE_HOME/bin:$PATH
RUN ln -s /opt/apache/apache-hive-${HIVE_VERSION}-bin ${HIVE_HOME}
ENV HIVE_CONF_DIR=${HIVE_HOME}/conf \
HIV_AUX_JARS_PATH=${HIVE_HOME}/lib
# 把对应的驱动包copy到hive lib目录下
ADD mysql-connector-java-5.1.49.tar.gz /opt/apache/
RUN cp /opt/apache/mysql-connector-java-5.1.49/mysql-connector-java-5.1.49-bin.jar ${HIV_AUX_JARS_PATH}
RUN cp /opt/apache/mysql-connector-java-5.1.49/mysql-connector-java-5.1.49.jar ${HIV_AUX_JARS_PATH}/
COPY config/hive-config/* ${HIVE_CONF_DIR}/
4、构建镜像
docker build -t hadoop:v1 . --no-cache
参数解释 -t：指定镜像名称 . ：当前目录Dockerfile -f：指定Dockerfile路径 --no-cache：不缓存
5、启动服务
docker-compose -f docker-compose.yaml up -d
6、初始化元数据
成功启动服务之后，进入任意一个docker容器内，执行如下命令：
$ schematool -initSchema -dbType mysql --verbose
然后查看mysql，发现多了一个数据库hive_local，就是在配置文件中连接的那个
这个库里有74张表
7、
bash-4.2$ ./hive/bin/hive -e "show databases;"
2）local模式（内嵌derby）
不需要安装mysql，修改配置文件hive-site.xml即可
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:derby:;databaseName=metastore_db;create=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>org.apache.derby.jdbc.EmbeddedDriver</value>
</property>
<property>
<name>hive.metastore.local</name>
<value>true</value>
</property>
<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
</property>
<!-- 数据在hdfs中的存储位置 -->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<property>
<name>datanucleus.schema.autoCreateAll</name>
<value>true</value>
</property>
</configuration>
执行 ./bin/hive
发现当前目录下多了两个文件
【注意】使用derby存储方式时，运行hive会在当前目录生成一个derby文件和一个metastore_db目录。
3）多用户模式（mysql）
该模式下就是客户端和服务端在不同的节点上，因此需要单独启动metastore服务。该模式需要hive.metastore.local设置为false，并将hive.metastore.uris设置为metastore服务器URI，如有多个metastore服务器，URI之间用逗号分隔。
参考文章： 大数据Hadoop之——数据仓库Hive (baidu.com)
四、Hive的交互方式
1、命令行bin/hive
bin/hive -e show databases # 从命令行执行指定的HQL
bin/hive -f HQL脚本
bin/hive -hiveconf x=y # 设置hive运行时候的参数配置
2、sql语句或sql脚本
一般在生产环境中使用sql脚本文件
五、Hive的基本操作
1、数据库操作
create database if not exists myhive; # 创建数据库
use myhive;
hive的库和表存放位置模式是由hive-site.xml中的属性指定的 <name>hive.metastore.warehouse.dir</name> <value>/user/hive_remote/warehouse</value>
create database if not exists myhive2 location '/myhive2'; # 创建数据库并指定位置
create database foo with dbproperties ('owner'='chl', 'data'='20240420'); # 创建数据库的一些描述性键值对信息
describe database extended foo; # 查看数据库键值对信息
alter database foo set dbproperties ('owner'='chl'); # 修改数据库键值对信息
desc database extended foo; # 查看数据库更多详细信息
drop database hive_test; # 删除一个空的数据库
drop database hive_test cascade; # 强制删除数据库，包含数据库下的表（一般不使用）
2、数据表操作
create [external] table [if not exists] table_name (
col_name data_type [comment '字段描述信息']
col_name data_type [comment '字段描述信息'])
[comment '表的描述']
[partitioned by (col_name date_type, ...)] # 表分区，在hdfs上的存储分文件，col_name可以指定多个，比如year,month
[clustered by (col_name, col_name, ...) into num_buckets buckets] # 分桶，指定的col_name相同时，数据会保存到同一个文件,mapreduce中的分区
[sorted by (col_name [asc|desc],...)]
[row format row_format]
[storted as...]
[location '指定表的路径']
external 创建一个外部表，删除表的时候，内部表的元数据和数据会被一起删除，外部表只删除元数据不删除数据
comment 注释，默认不能使用中文
partitioned by 表示使用表分区，一个表可以有一个或多个分区，每个分区单独在一个目录下（即分文件夹存储）
clustered by 表示分桶，对于每一个表分文件，hive进一步组织成桶，是mapreduce中的分区
sorted by 指定排序字段和排序规则
row format 指定表文件中字段的文件分隔符
storted as 指定表文件的存储格式（TEXTFILE、SEQUENCEFILE）
location 指定表文件的存储路径(一般是hdfs路径)
create table if not exists stu2(id int, name string) row format delimited fields terminated by '\t'; # 创建数据表并指定字段间的分隔符
create table if not exists stu2(id int, name string) row format delimited fields terminated by '\t' location '/user/stu2'; # 创建数据表并指定文件的存放路径
create table stu3 as select * from stu2; # 根据查询结果建表，复制表结构和表内容
create table stu4 like stu2; # 根据已存在的表结构创建表
desc formatted stu2; # 查看表详情
drop table stu4; # 删除表（移动到Trash）
外部表（共享）
create external table teacher (t_id string, t_name string) row format delimited fields terminated by '\t'; # 创建外部表
create external table student (s_id string, s_name string, s_sex string, s_virth string) row format delimited fields terminated by '\t'; # 创建外部表
load data local inpath '/opt/apache/hivedatas/teacher.csv' into table teacher; # 从本地目录加载数据到teacher表中
load data local inpath '/opt/apache/hivedatas/student.csv' into table student; # 从本地目录加载数据到student表中
load data local inpath '/opt/apache/hivedatas/student.csv overwrite into table student; # 加载数据并覆盖已有数据
load data inpath '/hivedatas/student.csv' into table student; # 从hdfs目录加载数据到student表中（剪切）
cat /opt/apache/hivedatas/teacher.csv:
1 zhangsan
2 lisi
3 wangwu
cat /opt/apache/hivedatas/student.csv
01 xiaoming 1900-01-01 m
02 xiaozhou 1991-02-02 w
03 xiaowang 1992-03-03 m
3、分区表操作
把大文件切分成一个个小地文件，比如按照年、月、日切分，存放在不同的文件夹中(partitioned by)
create table score (s_id string, c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by '\t'; # 创建分区表
create table score2 (s_id string, c_id string, s_score int) partitioned by (year string, month string, day string) row format delimited fields terminated by '\t'; # 创建多分区表
load data local inpath '/opt/apache/hivedatas/score.csv' into table score partition (month='202404'); # 加载数据到分区表
load data local inpath '/opt/apache/hivedatas/score.csv' into table score2 partition (year='2024',month='04',day='01'); # 加载数据到多分区表
select * from score where month='202403' union all select * from score where month='202404'; # 多分区表联合查询
show partitions score; # 查看表分区
alter table score add partition(month='202405'); # 添加分区
alter table score drop partition(month='202405'); # 删除分区
cat /opt/apache/hivedatas/score.csv
01 01 80
01 02 90
02 02 99
02 01 70
03 01 50
03 02 75
4、分桶表操作
分桶，就是将数据按照指定的字段划分到多个文件当中去，分桶就是MapReduce中的分区
set hive.enforce.bucketing=true; # 开启hive的分桶功能
set mapreduce.job.reduces=3; # 设置reduce个数
create table course (c_id string, c_name string, t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t'; # 创建分桶表
分桶表加载数据：（需要走mapreduce）
1.创建普通表
create table course_common (c_id string, c_name string, t_id string) row format delimited fields terminated by '\t';
2.普通表中加载数据
load data local inpath '/opt/apache/hivedatas/course.csv' into table course_common;
3.通过insert overwrite给桶表中加载数据
insert overwrite table course select * from course_common cluster by(c_id);
cat /opt/apache/hivedatas/course.csv
03 English 03
02 Chinese 01
01 Math 02
5、修改表结构
alter table old_name rename to new_name # 重命名
alter table score5 add columns (mycol string, mysco int); # 增加列
alter table score5 change columns mysco mysconew int; # 修改列
6、表查询
select [all | distinct] select_expr,select_expr...
from table_reference
where where_condition
group by col_list [having condition]
[cluster by col_list | [distribute by col_list] [sort by |order by]]
[limit number]
order by 全局排序，因此只有一个reducer
sort by 不是全局排序，在数据进入reducer前完成排序，因此只能保证每个reducer的输出有序(mapred.reduce.tasks>1时)
distribute by 根据指定字段将数据分到不同的reducer
cluster by 除了具有distribute by的功能外，还会对该字段进行排序
因此，如果distribute和sort字段是同一个时，cluster by = distribute by + sort by
常用函数
count()、max()、min()、sum()、avg()
select count(1) from t;
比较运算符
select * from t where s_id is null;
select * from t where s_id in (80,90);
select * from t where a<=>b;
like和rlike
% 代表零个或多个字符
_代表一个字符
select * from t where s_score like '8%';
select * from t where s_score like '_9';
select * from t where s_id rlike '[1]'; #正则， 等同于 like '%1%'
逻辑运算符
AND OR NOT
分组group by
通常和聚合函数一起用
select s_id, avg(s_score) from t group by s_id;
having语句
针对查询结果中的列发挥作用，用于筛选数据，用于group by
select s_id, avg(s_score) avgscore from t group by s_id having avgscore > 85;
join语句
只支持等值连接，不支持非等值连接
select s.s_id, s.score, stu.s_name from score s join student stu on s.s_id = stu.s_id;
内连接（只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来）
select * from teacher t inner join course c on t.t_id = c.c_id;
左外连接（join操作符左边表中所有记录将会被返回）
select * from teacher t left join course c on t.t_id = c.c_id;
右外连接（join操作符右边表中所有记录将会被返回）
select * from teacher t right join course c on t.t_id = c.c_id;
多表连接（连接n个表，至少需要n-1个连接条件）
select * from teacher t
left join course c
on t.t_id = c.t_id
left join score s
on s.c_id = c.c_id
left join student stu
on s.s_id = stu.s_id;
hive会对每对join连接对象启动一个MapReduce
order by语句
全局排序，一个reduce
select * from student s left join score sco on s.s_id = sco.s_id order by sco.s_score DESC|ASC;
多个列排序
select s_id,avg(s_score) avg from score group by s_id order by s_id, avg;
sort by语句
每个MapReduce内部进行排序，对全局结果集来说不是排序
set mapreduce.job.reduces=3; #设置
set mapreduce.job.reduces; #查看
select * from score sort by s_score;
将查询结果导入到文件中(本地文件)
insert overwrite local directory '/opt/hivedatas/sort' select * from score sort by s_score;
分区排序 distribute by
进行分区，结合sort by使用，且 distribute by要写在sort by之前
set mapreduce.job.reduces=7;
insert overwrite local directory '/opt/hivedatas/sort' select * from score distribute by s_id sort by s_score; #先按s_id进行分区，在按照s_score进行排序
cluster by
当distribute by和sort by字段是同一个时，cluster by = distribute by + sort by
六、Hive参数配置方式
1）配置文件
hive-site.xml hive-default.xml
2）命令行参数
作用域一次会话 bin/hive -hiveconf hove.root.logger=INFO,console
3）参数声明
在HQL中使用set关键字设定参数，作用于一次会话 set mapred.reduce.tasks=100
七、Hive内置函数
show functions; # 查看
desc functions upper;
desc functions extended upper;
使用
select concat("ab", "cd") # abcd
select concat_ws(",", "ab", "cd") # ab,cd
select cast(1.5 as int) # 1
select get_json_object('{"name":"jack", "age":"20"}', '$.name') # jack
select parse_url('http://facebook.com/path1/a.index?k1=v1&k2=v2#Red1', 'HOST') # facebook.com
select parse_url('http://facebook.com/path1/a.index?k1=v1&k2=v2#Red1', 'PATH') # /path1/a.index
select parse_url('http://facebook.com/path1/a.index?k1=v1&k2=v2#Red1', 'QUERY') # k1=v1&k2=v2
select parse_url('http://facebook.com/path1/a.index?k1=v1&k2=v2#Red1', 'QUERY', 'k2') # v2
select explode() #
八、Hive的压缩
压缩节省我们的MapReduce处理的网络带宽
bin/hadoop checknative # 查看支持的压缩方式
Snappy google.github.io/snappy/
在mapred-site.xml文件中进行配置
map输出阶段压缩（减少job中map和reduce task间数据传输量量）
set hive.exex.compress.intermediate=true; # 开启hive中间传输数据压缩功能
set mapreduce.map.output.compress=true; # 开启mapreduce中map输出压缩功能
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec; # 设置mapreduce中map输出语句的压缩方式
select count(1) from score; # 执行查询语句
reduce输出阶段压缩（hive将输出写入到表中时，输出内容同样可以压缩）
set hive.exex.compress.output=true; # 开启hive最终输出数据压缩功能
set mapreduce.output.fileoutputformat.compress=true; # 开启mapreduce最终输出压缩功能
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec; # 开启mapreduce最终输出压缩方式
set mapreduce.output.fileoutputformat.compress.type=BLOCK; # 设置mapreduce最终输出压缩为块压缩
insert overwrite local directory '/opt/hivedatas/snappy' select * from score distribute by s_id sort by s_id desc; # 测试输出结果是否是压缩文件
九、Hive的数据存储格式
行式存储（TEXTFILE和SEQUENCEFILE）
列式存储（ORC和PARQUET） （实际应用中使用的多，行查询效率低，尽量少使用select *）
TEXTFILE：默认，数据不做压缩，磁盘开销大，可结合Gzip、Bzip使用
ORC： stripe组成
数据压缩和文件存储格式结合
存储文件的压缩比：ORC>Parquet>testFile
存储文件的查询速度：ORC>testFile>Parquet
在实际项目开发中，hive表的数据存储格式一般选择：orc或parquet，压缩方式一般选择snappy
