1 Hive简介
1.1 Hive系统架构
- Hive是建立在 Hadoop上的数据仓库基础构架,它提供了一系列的工具,可以进行数据提取、转化、加载( ETL )
- Hive定义了简单的类SQL查询语言,称为HQL,它允许熟悉SQL的用户直接查询Hadoop中的数据
- Hive包含SQL解析引擎,它会将SQL语句转译成MR Job,然后在Hadoop中执行
- Hive的数据存储基于Hadoop的HDFS
- Hive没有专门的数据存储格式,默认可以直接加载文本文件TextFile,还支持SequenceFile、RCFile等
1.2 Metastore
- Metastore是Hive元数据的集中存放地
- 元数据包括表的名字,表的列和分区及其属性,表的数据所在目录等
- Metastore默认使用内嵌的Derby数据库作为存储引擎,推荐使用Mysql数据库作为外置存储引擎
1.3 Hive与MySQL对比
Hive | MySQL | |
---|---|---|
数据存储位置 | HDFS | 本地磁盘 |
数据格式 | 用户决定 | 系统决定 |
数据更新 | 不支持 | 支持 |
索引 | 有,但较弱 | 有 |
执行 | MapReduce | Executor |
执行延迟 | 高 | 低 |
可扩展性 | 高 | 低 |
数据规模 | 大 | 小 |
2 Hive安装部署
访问Hive官方网站,下载apache-hive-3.1.2-bin.tar.gz安装包,在/data/soft
目录下解压文件。
2.1 配置hive-env.sh
cd apache-hive-3.1.2-bin/conf/
mv hive-env.sh.template hive-env.sh
在文件末尾添加以下内容:
export JAVA_HOME=/home/gdan/data/jdk-8u131-linux-x64/jdk1.8.0_131
export HIVE_HOME=/home/gdan/data/soft/apache-hive-3.1.2-bin
export HADOOP_HOME=/home/gdan/data/soft/hadoop-3.2.0
再/etc/profile也需要设置hive的环境变量
export HIVE_HOME=/home/gdan/data/soft/apache-hive-3.1.2-bin
export PATH=$HIVE_HOME/bin:$PATH
2.2 安装mysql数据库,并创建hive库
1.安装MySQL 在终端中输入以下命令来安装MySQL:
sudo apt-get update
sudo apt-get install mysql-server
安装过程中会提示您设置MySQL的root用户密码,请根据提示进行设置。
安装时没有提示输入root账户密码,默认是空,可以执行以下命令设置密码为xxxx
:
sudo mysql -u root -p #密码按Enter即可进入mysql shell,空格也可以,普通用户一定sudo
2. 创建hive元数据数据库
create database hive;
2.3 配置hive-site.xml
cd apache-hive-3.1.2-bin/conf/
mv hive-default.xml.template hive-site.xml
在文件中添加以下内容:
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive?serverTimezone=Asia/Shanghai</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.cj.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>admin</value>
</property>
<property>
<name>hive.querylog.location</name>
<value>/data/hive_repo/querylog</value>
</property>
<property>
<name>hive.exec.local.scratchdir</name>
<value>/data/hive_repo/scratchdir</value>
</property>
<property>
<name>hive.downloaded.resources.dir</name>
<value>/data/hive_repo/resources</value>
</property>
给定的代码片段是Hive的配置文件,用于配置连接到MySQL数据库的相关属性。具体解释如下:
javax.jdo.option.ConnectionURL
:指定连接到MySQL数据库的URL。在这个例子中,URL为jdbc:mysql://localhost:3306/hive?serverTimezone=Asia/Shanghai
,表示连接到本地的MySQL数据库,端口为3306,数据库名为hive,使用Asia/Shanghai时区。
javax.jdo.option.ConnectionDriverName
:指定用于连接到Hive数据库的JDBC驱动程序的类名。在这个例子中,驱动程序的类名为com.mysql.cj.jdbc.Driver
,表示使用MySQL的JDBC驱动程序。
javax.jdo.option.ConnectionUserName
:指定连接到Hive数据库时使用的用户名。在这个例子中,用户名为root
。
javax.jdo.option.ConnectionPassword
:指定连接到Hive数据库时使用的密码。在这个例子中,密码为admin
。
hive.querylog.location
:指定查询日志的存储位置。在这个例子中,查询日志存储在/data/hive_repo/querylog
目录中。
hive.exec.local.scratchdir
:指定本地临时文件的存储位置。在这个例子中,临时文件存储在/data/hive_repo/scratchdir
目录中。
hive.downloaded.resources.dir
:指定下载资源的存储位置。在这个例子中,下载的资源存储在/data/hive_repo/resources
目录中。这些属性用于配置连接到Hive数据库的相关参数,并定义了查询日志、临时文件和下载资源的存储位置。
2.4 修改Hadoop的etc/hadoop/core-site.xml文件
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>
给定的代码片段是Hadoop的配置文件,用于配置代理用户的相关属性。具体解释如下:
hadoop.proxyuser.root.hosts
:指定可以通过代理用户root
进行访问的主机列表。在这个例子中,*
表示允许所有主机通过root
用户进行访问。
hadoop.proxyuser.root.groups
:指定可以通过代理用户root
进行访问的用户组列表。在这个例子中,*
表示允许所有用户组通过root
用户进行访问。这些属性用于配置Hadoop代理用户的访问权限,允许指定的主机和用户组通过指定的代理用户进行访问。
2.5 初始化Hive的Metastore
cd /data/soft/apache-hive-3.1.2-bin
bin/schematool -dbType mysql -initSchema
一直出现这种问题
后来发现是环境问题,回去更改 hive-env.sh 文件
再继续执行,又报错了
at [row,col,system-id]: [3215,96
需要删除3215行里面的description,把hive.txn.xlock.iow对应的description标签内容删掉。
又开始报错
此外还需要下载mysql jdbc 的jar包
去官网下载,把jar包放进hive的lib文件夹下
去mysql创建用户
使用的是mysql8.0版本,所以正确代码是:
create user 'dw'@'localhost' identified by '123456'; -- 创建用户
grant all on *.* to 'dw'@'localhost'; -- 将所有数据库的所有表的所有权限赋给datawhale
flush privileges; -- 刷新mysql系统权限关系表
注意不要use hive
检查hive是否成功部署
3 Hive使用
创建数据库
hive
create database hive;
3.1 创建数据表
create table t3_new(
id int comment 'ID',
stu_name string comment 'name' ,
stu_birthday date comment 'birthday',
online boolean comment 'is onlone'
)row format delimited
fields terminated by '\t'
lines terminated by '\n';
hdfs文件系统中的存储位置如下:
"hadoop fs -ls /" 是一个 Hadoop 命令,用于列出 Hadoop 分布式文件系统(HDFS)根目录下的文件和目录。
具体解释如下:
- "hadoop": 这是 Hadoop 命令行工具的名称。
- "fs": 表示要执行与文件系统相关的操作。
- "-ls": 是一个选项,表示要列出指定路径下的文件和目录。
- "/": 这是 HDFS 的根目录路径。
因此,运行 "hadoop fs -ls /" 命令将返回 HDFS 根目录下的所有文件和目录的列表。
3.2 创建带Array的表
create table stu(
id int,
name string,
favors array<string>
)row format delimited
fields terminated by '\t'
collection items terminated by ','
lines terminated by '\n';
3.3 创建带Map的表
create table stu2(
id int,
name string,
scores map<string,int>
)row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';
3.4 加载数据到表中
加载数据到表中属于DML
操作,这里为了方便大家测试,先简单介绍一下加载本地数据到表中的命令,命令如下:
load data local inpath '/home/gdan/data/soft/hivedata/stu3.data' into table emp;
其中stu3.data
的内容如下:
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975.00 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850.00 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450.00 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 1500.00 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100.00 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950.00 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000.00 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300.00 10
3.5 创建桶表
create table bucket_tb(
id int
)clustered by (id) into 4 buckets;
注意:需要从普通表中将数据插入到桶表中
3.6 创建视图
create view v1 as select id,stu_name from t3_new;
又开始报错
网上的教程也是很多坑,之前说把Hadoop的一个jar包放进Hive里面,其实不需要这个操作。
改回来就可以了
3.7 综合案例
案例需求:通过Flume按天将日志数据采集到HDFS中对应目录,使用SQL按天统计每天的相关指标,数据样例如下:
{
"uid": "861848974414839801",
"nickname": "mick",
"usign": "",
"sex": 1,
"birthday": "",
"face": "",
"big_face": "",
"email": "abc@qq.com",
"mobile": "",
"reg_type": "102",
"last_login_time": "1494344580",
"reg_time": "1494344580",
"last_update_time": "1494344580",
"status": "5",
"is_verified": "0",
"verified_info": "",
"is_seller": "0",
"level": 1,
"exp": 0,
"anchor_level": 0,
"anchor_exp": 0,
"os": "android",
"timestamp": 1494344580,
"type": "user_info"
}
解决方法:
- 针对Flume的Source可以使用Exec Source,Channel可以使用基于文件的或者内存的,Sink使用HDFS Sink,在HDFS Sink的Path路径中需要使用
%Y%m%d
获取日期,将每天的日志数据采集到指定的HDFS目录中。 - 需要对按天采集的日志数据建表,由于这份数据可能会被多种计算引擎使用,所以建议使用外部 表。
- 在实际工作中,离线计算的需求大部分都是按天计算的,所以在这里最好在表中增加日期这个分区字段,最终决定使用外部分区表。
- 针对Json数据,先创建外部分区表,再创建视图,解析Json数据中的字段
如果你要重新启动hive的话,需要把hadoop重新启动一下:
1. 创建分区表
CREATE EXTERNAL TABLE ex_par_more_type (
log STRING
)
PARTITIONED BY (dt STRING, d_type STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION '/moreType';
这是一个创建外部表的SQL语句,用于创建名为ex_par_more_type的外部表。该表包含一个名为log的列,并照dt和d_type两个分区进行分区。
以下是对每个部分的解释:
CREATE EXTERNAL TABLE ex_par_more_type
: 创建一个名为ex_par_more_type的外部表。(log string)
: 定义了一个名为log的列,其数据类型为string。PARTITIONED (dt string, d_type string)
: 按dt和d_type两个列进行分区。ROW FORMAT DELIMITED
: 指定行格式为分隔符格式。FIELDS TERMINATED '\t'
: 指定字段之间的隔符为制表符('\t')。LOCATION '/moreType'
: 指定外部表存储位置为/mType。通过执行这个SQL语句,你可以在指定的存储位置中创建一个外部表ex_par_more_type,该表含一个log列,并按照dt和d_type进行分区。
2. 添加分区
alter table ex_par_more_type add partition(dt='20200504',d_type='giftRecord') location '/moreType/20200504/giftRecord';
这个命令是用于在表
ex_par_more_type
中添加一个分区。分区的条件是
dt='20200504'
和d_type='giftRecord'
,并且该分区的数据将存储在路径/moreType/20200504/giftRecord
中。
3. 创建视图
create view gift_record_view as
select
get_json_object(log,'$.send_id') as send_id,
get_json_object(log,'$.good_id') as good_id,
get_json_object(log,'$.video_id') as video_id,
get_json_object(log,'$.gold') as gold,
dt
from ex_par_more_type
where d_type='giftRecord';
4. 创建脚本
#!/bin/bash
# 每天凌晨1点定时添加当天日期的分区
if [ "a$1" = "a" ]
then
dt=`date +%Y%m%d`
else
dt=$1
fi
# 指定添加分区操作
hive -e "
alter table ex_par_more_type add if not exists partition(dt='${dt}',d_type='giftRecord') location '/moreType/${dt}/giftRecord';
alter table ex_par_more_type add if not exists partition(dt='${dt}',d_type='userInfo') location '/moreType/${dt}/userInfo';
alter table ex_par_more_type add if not exists partition(dt='${dt}',d_type='videoInfo') location '/moreType/${dt}/videoInfo';
"
这是一个Bash脚本,用于在每天凌晨1点定时添加当天日期的分区。
脚本首先检查是否提了命令行参数如果没有提供参数,则使用当前日期作为分区的日期。如果提供了参数,则使用该参数作为分区的日期。
接下来,脚本使用Hive命令执行三个ALTER TABLE语句,用于添加分区。这些语句将在名为"ex_par_more_type"表中添加三个分区,分别对应不同的"d_type"值giftRecord、userInfovideoInfo)。每个分区的位置(location)都基于日期动态生成。
这些操作将在H中的"ex_par_more_type"表中添加当天日期的个分区,并指定相应的位置。
5. 配置crontab,每天执行一次
00 01 * * * root /bin/bash /data/soft/hivedata/addPartion.sh >> /data/soft/hivedata/addPartion.log
4 Hive高级函数应用
4.1 分组排序取TopN
使用row_number()
(对数据编号,从1开始)和over()
(把数据划分到一个窗口,加上partition by
,按照字段对数据进行分组,使用order by
按照字段进行排序)
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY sub ORDER BY score DESC) AS num
FROM student_score
) s
WHERE s.num <= 3;
这个查询语句的目的是从名为 "student_score" 的表中选择每个科目("")的前三名学生。使用窗口函数 ROW_NUMBER()
对每个科目的成绩进行降序排列,并为每个分组分配一个行号,外部查询选择行号小等于3的记录即每个科的前三名学生。
- 可以使用
rank()
替代row_number()
,表示上下两条记录的score相等时,记录的行号是一样的,但下一个score值的行号递增N - 可以使用
dense_rank()
替代row_number()
,表示上下两条记录的score相等时,下一个score值的行号递增1
4.2 行转列
concat_ws()
(根据指定的分隔符拼接多个字段的值)、collect_set()
(返回一个set集合,集合汇中的元素不重复) 、collect_list()
(返回一个list集合,集合中的元素会重复)
select name,collect_list(favor) as favor_list from student_favors group by name;
4.3 列转行
split()
(接受一个字符串和切割规则,就类似于java中的split函数,使用切割规则对字符串中的数据进行切割,最终返回一个array数组)explode()
(表示把数组中的每个元素转成一行)lateral view
(可以对数据产生一个支持别名的虚拟表)
select name,favor_new from student_favors_2 lateral view explode(split(favorlist,',')) table1 as favor_new;
4.4 distribute by
ditribute by
:只会根据指定的key对数据进行分区,但是不会排序。- 一般情况下可以和
sort by
结合使用,先对数据分区,再进行排序,两者结合使用的时候,distribute by
必须要写在sort by
之前。
4.5 cluster by
cluster by
:是distribute by
和sort by
的简写形式,即cluster by id
等于distribute by id sort by id
5 应用示例
5.1 解决数据倾斜问题的SQL语句
数据倾斜的根本原因是数据的key分布不均,个别key数据很多,超出了计算节点的计算能力的结果
SELECT a.Key, SUM(a.Cnt) AS Cnt
FROM (
SELECT Key, COUNT(*) AS Cnt
FROM TableName GROUP BY Key,
CASE
WHEN Key = 'KEY001' THEN Hash(Random()) % 50
ELSE 0
END
) a GROUP BY a.Key;
其中CASE WHEN
将数据打散,然后再进行GROUP BY
5.2 解决数据倾斜方案
-
参数调节:hive.groupby.skewindata=true 有数据倾斜的时候进行负载均衡,当选项设定为true,生成的查询计划会有两个MR Job。第一个MR Job中,Map的输出结果集合会随机分布到Reduce中,每个Reduce做部分聚合操作,并输出结果,这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中,从而达到负载均衡的目的;第二个MR Job再根据预处理的数据结果,按照Group By Key分布到Reduce中(这个过程可以保证相同的Group By Key被分布到同一个Reduce中),最后完成最终的聚合操作。
-
SQL语句调节:
- 大小表Join:使用map join让小的维度表(1000条以下的记录条数)先进内存。在map端完成reduce。
- 大表Join大表:把空值的key变成一个字符串加上随机数,把倾斜的数据分到不同的reduce上,由于null值关联不上,处理后并不影响最终结果。
- count distinct大量相同特殊值:在count distinct时,将值为空的情况单独处理,如果是计算count distinct,可以不用处理,直接过滤,在最后结果中加1。如果还有其他计算,需要进行group by,可以先将值为空的记录单独处理,再和其他计算结果进行union。
- 去重求和:采用sum() group by的方式来替换count(distinct)完成计算。