Hive
Hive
Hive是建立在Hadoop上的数据仓库基础构架，它提供了一系列的工具，可以进行数据提取、转化、加载(ETL)。Hive定义了简单的类SQL查询语言，称为HQL，它允许熟悉SQL的用户直接查询Hadoop中的数据。Hive包含SQL解析引擎，它会将SQL语句转译成MR Job，然后在Hadoop中执行
Hive的数据存储基于Hadoop的HDFS。Hive没有专门的数据存储格式，默认可以直接加载文本文件TextFile，还支持SequenceFile、RCFile等
Hive本身不存储或计算数据，最核心的功能是Driver。
Metastore用于存储Hive的元数据，比如表的名字，表的列和分区及属性，表的数据所在目录等。Metastore默认使用内嵌的Derby数据库作为存储引擎，使用Derby时一个目录下只能同时打开一个会话，推荐使用Mysql作为外置存储引擎。
数据库和数据仓库的区别
Hive侧重于查询分析
- 数据库：传统的关系型数据库主要应用在基本的事务处理，例如银行交易；支持增删改查
- 数据仓库：主要做一些复杂的分析操作，侧重决策支持，相对数据库而言，数据仓库分析的数据规模要大得多；只支持查询
数据库与数据仓库的本质区别就是OLTP与OLAP的区别
- OLTP(On-Line Transaction Processing)：操作型处理，称为联机事务处理，也可以称为面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性等问题
- OLAP(On-Line Analytical Processing)：分析型处理，称为联机分析处理，一般针对某些主题历史数据进行分析，支持管理决策
安装Hive
- 解压后进入conf目录，修改配置文件名
- 将hive-defaultxml.template重命名为hive-site.xml
- 将hive-env.sh.template重命名为hive-env.sh
- 将hive-log4j.properties.template重命名为hive-log4j.properties
- 在 hive-env.sh 中配置变量
JAVA_HOMEHIVE_HOMEHADOOP_HOME - 配置 HIVE_HOME/conf 下的 hive-site.xml 文件，不配置的话默认使用内嵌的Derby数据库
<!--判断mysql下是否有这个数据库，没有的话创建--> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://hadoop100:3306/metastore?createDatabaseIfNotExist=true</value> <description>JDBC connect string for a JDBC metastore</description> </property> <!--使用mysql driver驱动，默认是hive内置数据库derby驱动--> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.cj.jdbc.Driver</value> <description>Driver class name for a JDBC metastore</description> </property> <!--mysql账号--> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> <description>username to use against metastore database</description> </property> <!--mysql密码--> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>000000</value> <description>password to use against metastore database</description> </property> <!--显示查询出的数据的字段名称--> <property> <name>hive.cli.print.header</name> <value>true</value> <description>Whether to print the names of the columns in query output.</description> </property> <!--在hive中显示当前所在数据库名称--> <property> <name>hive.cli.print.current.db</name> <value>true</value> <description>Whether to include the current database in the Hive prompt.</description> </property> <!--使得查询单列数据不会执行mapreduce--> <property> <name>hive.fetch.task.conversion</name> <value>more</value> <description> Some select queries can be converted to single FETCH task minimizing latency. Currently the query should be single sourced not having any subquery and should not have any aggregations or distincts (which incurs RS), lateral views and joins. 1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only 2. more : SELECT, FILTER, LIMIT only (TABLESAMPLE, virtual columns) </description> </property>还要配置日志目录，不配置会报错：
hive.querylog.locationhive.exec.local.scratchdirhive.downloaded.resources.dir - 将mysql的驱动jar包放入
HIVE_HOME/lib下，注意要与Mysql的版本匹配 - 在Hadoop集群节点的 core-site.xml 文件中增加以下配置
<property> <name>hadoop.proxyuser.root.hosts</name> <value>*</value> </property> <property> <name>hadoop.proxyuser.root.groups</name> <value>*</value> </property> - 按需修改 hive-log4j.properties
- 安装并配置mysql，创建数据库 metastore
- 注意可能不支持高版本jdk，要求把hive和hadoop的jdk都换为jdk8
命令行操作
运行 bin/hive 启动并进入hive交互界面，然后就可以使用类sql的hql语言：
show tables;
create table t1(id int, name string);
insert into t1(id,name) values(1,"zs");
select * from t1;
drop table t1;或者使用 beeline 命令，一个更轻量级的客户端，使用前要先启动hive服务 hiveserver2，再输入bin/beeline -u jdbc:hive2://localhost:10000 -n root
或再shell中直接使用：hive -e "select * from t1"
set 命令
使用set设置后仅对当前会话有效，在 ~/.hiverc 中配置set命令后永久有效。
例如：set hive.cli.print.current.db = true;可以临时生效，而无需到配置文件中配置。
hive历史命令存储于 ~/.hivehistory
日志
设置conf目录下的 hive-log4j2.properties 和 hive-exec-log4j2.properties
JDBC 操作
先启动 hiveserver2，再在java项目中添加相应版本的依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>3.1.3</version>
</dependency>package org.example.hive;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class HiveJDBC {
public static void main(String[] args) throws Exception {
String jdbcUrl = "jdbc:hive2://192.168.56.152:10000";
try (Connection conn = DriverManager.getConnection(jdbcUrl, "root", "");
Statement statement = conn.createStatement();) {
ResultSet res = statement.executeQuery("select * from t1");
while (res.next()) {
System.out.println("res.getInt(\"id\") = " + res.getInt("id"));
System.out.println("res.getString(\"name\") = " + res.getString("name"));
}
}
}
}
具体操作
类似sql语言
操作数据库
show databases;
-- 默认default，hive.metastore.warehouse.dir default数据库的位置，每个数据库对应一个目录，元信息保存在mysql的DBS中
use default;
create databse mydb1;
create databse mydb2 location '/user/hive/mydb2'; # 将数据库手动指定到默认位置
drop databse mydb2;操作表
当存在无法解析的数据时，不会导致导入和查询的失败，而是将数据显示为 NULL
create table t2(
id int comment 'id'
) comment '用于测试的表'; # 元数据在mysql的TBLS表中
show tables;
desc t2;
show create table t2;
alter table t2 rename to t3;
insert into t3(id) values(1);
alter table t3 add columns (name string);
drop table t3;
-- 加载数据，数据要呈类似csv的格式
load data local inpath '/path/to/data_file'; -- 等价于直接将其上传到dfs
-- 当存在多列数据时要指定列分隔符 行分割符默认'\n' 默认列分隔符'/001' ctrl+v + ctrl+a 得到
create table t4(
id int comment 'ID',
stu_name string comment 'name',
stu_birthday date comment 'birthday',
online boolean comment 'is online'
) row format delimited
fields terminated by '\t' -- 指定列分割符
lines terminated by '\n'; -- 指定行分割符数据类型
基本数据类型
复合数据类型
案例：
综合案例
数据：
1 张三 swing,sing,coding chinese:80,math:90,english:100 bj,sh
2 lisi music,football chinese:89,english:70,math:88 gz,sz表：
create table stu (
id int,
name string,
favors array<string>,
scores map<string, int>,
address struct<home_addr:string, office_addr:string>
) row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';
load data local inpath '/path/to/data_file' into table stu;操作：
select id, name, favors[0], scores['english'], address.home_addr from stu;Hive 表类型
- 内部表
Hive中的默认表类型，表数据默认存储在 warehouse 目录中
在加载数据的过程中， 实际数据会被移动到warehouse目录中
删除表时， 表中的数据和元数据将会被同时删除 - 外部表
建表语句中包含 External 的表叫外部表
外部表在加载数据的时候， 实际数据并不会移动到warehouse 目录中，只是与外部数据建立一个链接(映射关系)
当删除一个外部表时，只删除元数据，不删除表中的数据，仅删除表和数据之间的链接
CREATE EXTERNAL TABLE page_view( -- 使用 EXTERNAL TABLE 声明外部表 viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User', country STRING COMMENT 'country of origination') COMMENT 'This is the staging page view table' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054' STORED AS TEXTFILE LOCATION '<hdfs_location>'; -- 指定数据在hdfs中的存储位置，默认在warehouse。当数据位于warehouse时，即使删除了表数据任然在warehouse保留
例子：使用Flume采集数据，使用hdfs保存数据，再使用Hive建立外部表操作。
内部表外部表可以相互转化
内部表转外部表alter table tblName set tblproperties('external'='true')
外部表转为内部表 - 分区表（内部分区表，外部分区表）
分区可以理解为分类，通过分区把不同类型数据放到不同目录
分区的标准就是指定分区字段，分区字段可以有一个或多个
分区表的意义在于优化查询，查询时尽量利用分区字段，如果不使用分区字段，就会全表扫描，最典型的一个场景就是把天作为分区字段，查询的时候指定天
# 例如数据存储于 /data/logs/20220101 /data/logs/20220102 /data/logs/20220103 ... # 其中日期由某个字段决定，根据这个字段的值存储到不同目录create table partition_1 ( id int, name string )partitioned by (dt string) row format delimited fields terminated by '\t'; -- desc partition_1; +--------------------------+------------+----------+ | col_name | data_type | comment | +--------------------------+------------+----------+ | id | int | | | name | string | | | dt | string | | | | NULL | NULL | | # Partition Information | NULL | NULL | | # col_name | data_type | comment | | dt | string | | +--------------------------+------------+----------+ -- 导入数据 1 张三 2 李四 -- 通过 partition(dt='20220101') 指定分区 load data local inpath '/path/to/data_file' into table partition_1 partition(dt='20220101'); -- 添加删除分区 alter table partition_1 add partition(dt='20220101'); alter table partition_1 drop partition(dt='20220101'); -- 查看分区 show partitions partition_1; -- 将数据按照一级年份，二级学院的层次划分 create table partition_2( id int, name string ) partitioned by (year int, school string) row format delimited fields terminated by '\t'; -- 2020/ -- school01/ -- school02/ -- 2021/ -- school01/ -- school02/ select * from partition_2; -- 全表扫描，没有用到分区 select * from partition_2 where year=2020; -- 使用了分区，大大提高效率外部分区表=外部表+分区表【工作中最常见的表】
create external table partition_1 ( id int, name string )partitioned by (dt string) row format delimited fields terminated by '\t'; -- 1. 加载数据 2. 添加分区（绑定数据和分区的关系） LOAD DATA local inpath '/path/to/file' into table ex_par PARTITION(dt='20220101'); -- 这种方式更常用，Flume上传数据，然后再创建分区并绑定 -- 添加分区并绑定数据与分区的关系 ALTER TABLE ex_par ADD PARTITION(dt='20220101') location '/data/ex_par/dt=20220101'; - 桶表
桶表是对数据进行哈希取值，然后放到不同文件中存储
物理上， 每个桶就是表(或分区） 里的一个文件
桶表的作用是： 1 、 数据抽样 2、 提高某些查询效率， 例如join
create table bucket_tb( id int ) clustered by (id) into 4 buckets; -- 根据id的值分到4个桶中 set hive.enfore.bucketing=true; -- 根据桶的数量产生相应的reduce任务数 -- 保存数据时不能再使用 load file 的方法了 -- 要用 insert into table ... select ... from ...使用
-- 1. 数据抽样，随机取数字 select * from bucket_tb tablesample(bucket 1 out of 4 on id); select ... from tb tablesample(bucket x out of y on id); -- x: 表示取第几桶的数据，x <= y -- y: 将桶中的数据随机分为多少桶，y最好为桶数的倍数 -- 2. join 操作，会产生笛卡尔积。当两种表都是桶表时不会产生中间表 select a.id, a.name, b.addr from a join b on a.id=b.id;
视图
使用视图可以降低查询的复杂度，虚拟表不真实存在
创建视图： CREATE VIEW V1 AS SELECT . . . . . .
create view v1
as
select id, stu_name
from t3;删除视图： DROP VIEW V1
案例
Flume按天把日志数据采集到HDFS中的对应目录中， 使用SQL按天统计每天的相关指标。使用外部分区表和视图实现。
-- 数据格式：Flume收集的json格式日志
{"send_id": 2431, "good_id": 5305, "video_id": 2324, "type": "gift_info", "time": "1494344580"}
{"uid": 2431, "nickname": 5305, "usign": 2324, "type": "user_info", "time": "1494344580"}
{"id":1000, "lat": 2431, "lnt": 5305, "type": "video_info", "time": "1494344580"}
-- 创建外部分区表
create external table ex_par(
log string
) partitioned by (dt string, d_type string)
row format delimited
fields terminated by '\t'
location '/data/dir';
-- 手动创建分区，每天都要执行一次创建不同的dt。写到shell脚本添加到cron，每天执行
alter table ex_par
add if not exists partition(dt='20220101', d_type='giftRecord')
location '/data/20220101/giftRecord';
alter table ex_par
add if not exists partition(dt='20220101', d_type='userinfo')
location '/data/20220101/userinfo';
alter table ex_par
add if not exists partition(dt='20220101', d_type='videoinfo')
location '/data/20220101/videoinfo';
create view gift_record_view
as select
get_json_object(log, '$.send_id') as send_id, -- get_json_object() Hive提供的用于从json中获取对象的函数
get_json_object(log, '$.good_id') as good_id,
get_json_object(log, '$.video_id') as video_id,
dt
from ex_par where d_type='giftRecord'
create view user_info_view
as select
get_json_object(log, '$.uid') as uid,
get_json_object(log, '$.nickname') as nickname,
get_json_object(log, '$.usign') as usign,
dt
from ex_par where d_type='userinfo'
create view video_info_view
as select
get_json_object(log, '$.id') as id,
get_json_object(log, '$.lat') as lat,
get_json_object(log, '$.lnt') as lnt,
dt
from ex_par where d_type='videoinfo'函数
show functions;查看所有函数；desc function FUNC;查看指定名称函数 desc function extended FUNC;查看指定函数扩展信息
分组排序取TopN： ROW_NUMBER() [编号]、rank() [排名，存在并列名次]、 OVER()
select * from (
select *, row_number() over(partition by <field1> order by <field2> desc) as num -- 根据field1分组，再根据field2排序
from <table>;
) s where s.num <= 3; -- 获取每个字段的 top3
行转列，多行数据转为一列数据： CONCAT_WS(separator, [string | array(string)]+) [将数组拼接]、 COLLECT_LIST() 、 COLLECT_SET()
要求实现：
zs swing
zs football
zs sing
zs coding
=>
zs swing,football,sing,coding
-- -----------------------------------------------
select name, concat_ws(',', collect_set(favor)) as favor_list -- 返回zs ["swing","football","sing","coding"]
from student_favors
group by name;
列转行： SPLIT(str, regex)、 EXPLODE(array / map)、 LATERAL VIEW 一般与前两者结合使用
zs swing,football,sing
ls coding,swing
=>
zs swing
zs football
zs sing
ls coding
ls swing
-- -----------------------------------
select name, favor_new from student_favors
lateral view explode(split(faverlist',')) table1 as favor_new; -- 分割产生的虚拟表为 table1，每一行数据命名为 favor_new
排序函数
- ORDER BY：全局排序，只生成一个reduce任务
- SORT BY：如果有多个reduce任务，在每个任务中都进行排序。能保证在reduce中局部有序
select * from tb sort by id; - DISTRIBUTE BY：控制map到reduce是如何划分的，根据指定字段进行分区。一般与 sort by 结合使用，并写在其之前。
select id from tb distribute by id sort by id;支持 desc - CLUSTER BY：等于
distribute by id sort by id的缩写 不支持 desc
分组和去重
- group by 相同的name分到一个reduce任务中，效率更高
select count(*) from (select name from tb group by name) tmp; - distinct 所有数据在一个reduce中处理
select count(distinct name) from tb;
数据倾斜
一条解决数据倾斜的sql，类似 hadoop 中将 key 修改为key_1 key_2 key_3 的解决方法：
-- 解决数据倾斜的问题，特别针对 group by
select a.key, SUM(a.Cnt) AS Cnt
from(
select key, count(*) AS Cnt
from tablename
group by key,
case
when key = 'KEY001' -- 当数据向KEY001倾斜时，即KEY001在全部数据中占有大比率
then Hash(Random()) % 50 -- 当等于KEY001是，还给出一个随机值，让KEY001分到多个组中
else 0
end
) a
group by a.Key;
-- 在内部表中分组得到
KEY001 0
KEY001 1
KEY001 2
KEY001 3
...
KEY002 0
KEY003 0
KEY004 0
...可能触发Hive数据倾斜的情况：
| 关键字 | 情形 | 后果 |
| join | 其中一个表较小， 但是 key 集中 | 分发到某一个或几个 Reduce |
| group by | group by 维度过小， 处理的数量过多 | 处理某值的 reduce 耗时长 |
| count distinct | 某特殊值过多 | 处理此特殊值的 reduce 执行时间长 |
原因：
- key 分布不均匀
- 业务数据本身的特性
- 建表时考虑不周
- 某些 SQL 语句本身就有数据倾斜
表现：
任务进度长时间维持在 99%（或 100% ） ，查看任务监控页面，发现只有少量（ 1 个或几个） reduce 子任务未完成。因为其处理的数据量和其他 reduce 差异过大。单一 reduce 的记录数与平均记录数差异过大，通常可能达到 3 倍甚至更多。最长时长远大于平均时长。
解决方法：
- 调节参数
hive.groupby.skewindata=true有数据倾斜时负载均衡，设置为true时，生成的查询计划会有两个MR job，第一个 job 中 Map 的输出结果集合会随机分布到 Reduce 中，Map 的输出结果集合会随机分不到Reduce 中，每个 Reduce 做部分聚合操作，并输出结果。这样相同 Group by key 可能分到不同的Reduce 中从而达到负载均衡。第二个 MR job 再根据预处理的数据结果按照 Group by key 分布到 Reduce 中，这个过程可以保证相同的 Group by key 被分布到同一个 Reduce 中，最终完成集合操作。 - SQL 语句调节
- 大小表join：
使用 map join 让小的维度表（ 1 000 条以下的记录条数） 先进内存。 在 map 端完成 reduce. - 大表 Join 大表：
把空值的 key 变成一个字符串加上随机数，把倾斜的数据分到不同的 reduce 上，由于 null 值关联不上，处理后并不影响最终结果。
- 大小表join：
- 针对去重求和的需求
采用 sum() group by 的方式来替换 count(distinct) 完成计算。
