Hive的基本操作
一、回顾
-
MapReduce中补充
- 分片的规则:决定了Map个数,取决于输入类、
- 默认输入类:TextInputFormat
- 输入的文件是否大于128的1.1倍
- 如果大于,128M一个块对应一个分片
- 如果小于,整体作为一个分片
- 分片的方法:getSplits
- 默认输入类:TextInputFormat
- MapReduce Join
- Reduce Join
- 特点:通过让两份数据经过shuffle,将相同的关联字段进行分组,在reduce中实现join
- 场景:大数据 join 大数据
- Map Join
- 特点:join发生在Map 端,不用经过shuffle,将小数据放入分布式缓存中,与大数据的每个部分进行join
- 场景:小数据 join 大数据
- Reduce Join
- 分片的规则:决定了Map个数,取决于输入类、
-
YARN
- 架构以及Mapreduce程序在YARN上运行的流程
- YARN的调度机制
- FIFO
- 单队列,内部还是FIFO
- 问题:不能实现多个程序的并行,导致资源浪费
- Mapreduce v1中默认的通过4个Slot管理资源
- Capacity:容量调度
- Apache版本的Hadoop默认的调度机制
- 多队列,队列内部还是FIFO
- 允许动态资源抢占
- Fair :公平调度
- CDH版本的默认调度机制
- 多队列,每个队列内部公平共享资源
- 允许动态资源抢占,提供了权重机制
- FIFO
-
Hive
-
功能
- 功能一:将HDFS文件映射成Hive中表的数据
- 功能二:将HQL语句转换为分布式计算的程序
- 默认转换为Mapreduce程序:hive.default.engine=mr
- spark, tez
-
重点思考:这两个功能底层是如何设计实现的?
-
功能一如何能实现的?
-
构建HDFS文件与Hive表的映射索引
-
例如:select * from tbname
-
通过记录表与文件的关系
-
当对表进行操作时,就根据映射关系,处理对应的文件
-
在hive中创建表
create table tbname( 根据文件的内容来定义字段 ) row format delimited fields terminated by ',';
-
将文件与表进行关联
load data inpath 'filePath' into table tbname;
-
元数据中记录了文件与表的映射关系
-
测试
use db_test; create table tb_lianjia( xiaoqu string, huxing string, area double, region string, floor string, chaoxiang string, tprice int, sprice int, builddate string ) row format delimited fields terminated by ','; load data inpath '/secondhouse.csv' into table tb_lianjia;
-
观察元数据:MySQL中hivemetadata数据库中
-
DBS:记录了Hive中所有数据库的信息
-
TBLS:记录了Hive中所有表的信息
-
SDS:记录了HIve中的表与HDFS的映射关系
-
当用户提交SQL语句,对表进行操作时,通过映射关系,将表对应的HDFS中的目录下的所有文件进行处理
-
观察HDFS
- 数据仓库的目录
-
数据库目录
-
表的目录
-
数据文件
-
-
-
-
功能二 :将HQL语句转换为MapReduce程序?
-
SQL
select 1 from 2 where 3 group by 4 having 5 order by 6 limit 7
- 1-结果的格式,决定了列
- 列的过滤
- 2-需要读取的数据源
- emp
- empno,ename,salary,deptno
- dept
- deptno ,dname
- 表/子查询【临时表】:结果中用到的列都来自于某一张表
- 查询所有员工编号大于100的员工编号和名称
- join:如果结果中的列来自于不同的表
- 查询所有员工编号大于100的员工名称和部门名称
- emp
- 3-决定了过滤条件,保留哪些行
- where empno > 100
- 4-按照哪个字段分组
- 关键字:每、各个‘不同、每个
- 5-决定了过滤条件,保留哪些行
- 与where的区别
- 过滤的条件是在分组前就有还是分组后才产生的
- 统计每个部门的平均薪资,返回平均薪资大于5000并且部门名称不为销售部的部门信息
- where dname != 销售部
- having avg_sal > 5000 and dname != 销售部
- 6-决定了按照哪列排序
- 关键字:最高、最低、升序,降序,前几,后几
- 7-限制输出
- limit M ,N
- M :起始位置,默认为0,第一条
- limit M ,N
- 1-结果的格式,决定了列
-
Mapreduce
Input:输入 2 Map:基本一对一的处理,列,和行过滤 1,3 Shuffle:分组,排序 4,6 Mapreduce SQL Reduce:聚合 函数,5、7 Output 打印 -
需求:id,name,age
-
HDFS文件
1 laoda 20 2 laoer 22 3 laosan 24
-
Hive表
create table person( id int, name string, age int ) row format delimited fields terminated by '\t';
-
关联
load data inpath 'filePath' into table person;
-
SQL:select id,name from person where age > 20;
-
Input:根据表名访问元数据,找到这张表对应的HDFS文件
- 将这个表的目录下的所有文件作为Mapreduce的输入
-
Map
-
map
String[] splits = value.toString.split("\t") id = splits[0] name = split[1] age = split[2] if(age > 20){ context.write(id,name) }else{ return; }
-
-
-
-
-
-
应用场景
- 构建数据仓库:目前Hive在工作中的主要使用场景
- 通过功能一来实现的
- 管理模式:以表的形式来进行管理,通过SQL对数据进行处理
- 利用Hive实现数据的处理转换分析:目前很多的公司都选用别的工具来实现
- 通过功能二来实现的
- 工作中能够使用的替代品
- SparkSQL
- Spark是一个全场景的分布式计算平台
- SparkCore:MapReduce
- 离线代码开发计算
- SparkSQL:Hive
- 离线交互式计算
- SparkStreaming:Storm / Flink
- 准实时计算,性能要求高的场景下,需要选用Storm、Flink
- SparkCore:MapReduce
- Spark是一个全场景的分布式计算平台
- Impala
- Presto
- ……
- 基于分布式内存的SQL计算引擎
- SparkSQL
- 构建数据仓库:目前Hive在工作中的主要使用场景
-
Hive中的架构
- CS:客户端服务端模式
- Client:客户端
- 提供与用户交互的接口
- 将请求提交给服务端
- 将结果返回给用户
- Server:服务端
- 处理客户端的请求
- Client:客户端
- Hive客户端:与用户交互,提供SQL接口
- Hive服务端:解析SQL,将解析好的任务提交给Hadoop
- 连接器:负责管理所有的客户端连接
- JDBC
- url:指定服务端地址
- username
- password
- JDBC
- 解析器:解析SQL,验证语法,解析为语法树
- 逻辑计划:明确了用户想要实现的结果
- 优化器:根据用户的结果,自动选择一种最优的方式来实现
- 物理计划:真正要执行的计划
- 执行器:提交物理计划执行
- 连接器:负责管理所有的客户端连接
- 元数据:负责存储整个Hive中所有关键性的信息
- Hadoop:负责实现Hive服务端提交的任务
- 存储任务:HDFS
- 计算任务:MapReduce + YARN
- CS:客户端服务端模式
-
如果是hdfs的读写操作就把转发给分布式存储:HDFS
如果是分析统计类的程序我们可以将sql提交给MapReduce+yarn
二、课程目标
- Hive元数据
- 存储:MySQL
- 默认存储
- 共享元数据
- 存储:MySQL
- Hive的使用
- 客户端、服务端
- 命令、配置
- SQL语句【重点练习,记住语法】
- DDL:数据库与表的管理
- DML:load、insert
- DQL:基本与MySQL一致
- 表的分类
三、Hive的元数据服务
1、元数据存储
- 内容
- Hive中运行时需要用到的所有信息
- 数据库
- 表
- 列
- 函数
- ……
- Hive中记录表与HDFS的映射关系:SDS
- Hive中运行时需要用到的所有信息
- 位置
- 默认:derby文本型数据库
- 缺点:元数据只有由一个进程进行管理访问,不能共享
- 存储的文本数据库不安全
- 缺点:元数据只有由一个进程进行管理访问,不能共享
- 自定义:将元数据存储在RDBMS中,MySQL
- 默认:derby文本型数据库
2、元数据共享
- 为什么要实现元数据共享?
- 目前在工作中已经很少使用Hive来做分析计算,由SparkSQL,Impala来实现分析计算
- 问题:SparkSQL和Impala如何知道Hive中哪些表,以及这些表对应HDFS文件?
- 解决:将Hive的元数据共享给SparkSQL或者Impala即可
- 问题:SparkSQL以及Impala不会解析元数据,只知道元数据存储的位置,不会解析元数据的内容?
- 解决:在Hive中专门构建一个服务用于提供元数据的读写请求,负责帮所有需要操作元数据的客户端进行解析
3、元数据服务
-
元数据服务:metastore
- 只要开启了元数据服务,所有需要访问元数据的进程都需要访问metastore服务
- 注意事项:服务进程启动的顺序,只要配置了metastore服务
- 先启动metastore服务
- 然后启动hive服务端
- 再启动hive客户端
-
配置并开启元数据服务
-
配置:修改hive-site.xml
<property> <name>hive.metastore.uris</name> <value>thrift://node3:9083</value> </property>
-
启动
bin/hive --service metastore
-
检查端口
netstat -atunlp | grep 9083
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JFuAd76f-1615444813605)(Day10_Hive的基本操作.assets/image-20201105113140129.png)]
-
4、测试
四、Hive客户端
1、Hive shell
- 命令:hive
- 特点:客户端与服务端一体的命令
- 应用:不适合于作为实现与用户的交互客户端
- 主要用于实现自动化Hive任务的开发
- 需求:每天的0点一多,所有的程序按照先后顺序自动化运行
2、Beeline
-
命令:beeline
-
特点:纯客户端,通用性的JDBC客户端
-
应用:交互性相对比较友好,一般用于测试开发的交互
-
使用
-
启动
-
先启动元数据服务:开放9083端口
-
然后启动Hive的服务端:hiveserver2:10000
bin/hiveserver2
-
检查端口
netstat -atunlp | grep 10000
-
-
最后启动Hive的客户端:beeline
-
-
-
启动方式
-
方式一
-
先进入beeline命令行
beeline
-
构建连接
!connect jdbc:hive2://node3:10000
-
输入用户名和密码
root 123456
-
进入命令行
-
-
方式二
beeline -u jdbc:hive2://node3:10000 -n root -p 123456
-
退出
!quit
-
-
问题
-
问题一:报找不到jar包
-
原因:lib目录下没有这个jar包,beeline不需要这个jar包
- 这个jar包以前的确会在lib目录下,但是后来在2.x版本中将这个jar包放入了jdbc目录
-
解决:将这个jar包从jdbc目录复制到lib目录
cd /export/servers/hive-2.1.0-bin cp jdbc/hive-jdbc-2.1.0-standalone.jar lib/
-
-
问题二:root用户无法访问Hive
-
原因:Hive默认的权限检查不允许root用户访问Hive以及Hadoop
-
解决:将root用户配置成Hadoop的代理用户
-
step1:先关闭hdfs和yarn
-
step2:修改配置文件core-site.xml,配置Hadoop代理用户访问
cd /export/servers/hadoop-2.7.5/etc/hadoop/ vim core-site.xml
<property> <name>hadoop.proxyuser.root.hosts</name> <value>*</value> </property> <property> <name>hadoop.proxyuser.root.groups</name> <value>*</value> </property>
-
step3:分发
scp core-site.xml node2:$PWD scp core-site.xml node3:$PWD
-
-
step4:重启HDFS和yarn
start-dfs.sh start-yarn.sh
-
-
3、JDBC
- 类似于MySQL的JDBC
- 区别:驱动类,url不一样
- 示例:https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-JDBC
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
public class HiveJdbcClient {
private static String driverName = "org.apache.hive.jdbc.HiveDriver";
/**
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.exit(1);
}
//replace "hive" here with the name of the user the queries should run as
Connection con = DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "hive", "");
Statement stmt = con.createStatement();
String tableName = "testHiveDriverTable";
stmt.execute("drop table if exists " + tableName);
stmt.execute("create table " + tableName + " (key int, value string)");
// show tables
String sql = "show tables '" + tableName + "'";
System.out.println("Running: " + sql);
ResultSet res = stmt.executeQuery(sql);
if (res.next()) {
System.out.println(res.getString(1));
}
// describe table
sql = "describe " + tableName;
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(res.getString(1) + "\t" + res.getString(2));
}
// load data into table
// NOTE: filepath has to be local to the hive server
// NOTE: /tmp/a.txt is a ctrl-A separated file with two fields per line
String filepath = "/tmp/a.txt";
sql = "load data local inpath '" + filepath + "' into table " + tableName;
System.out.println("Running: " + sql);
stmt.execute(sql);
// select * query
sql = "select * from " + tableName;
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(String.valueOf(res.getInt(1)) + "\t" + res.getString(2));
}
// regular hive query
sql = "select count(1) from " + tableName;
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(res.getString(1));
}
}
}
五、Hive常用操作配置
1、启动脚本
-
创建日志存储目录
mkdir /export/servers/hive-2.1.0-bin/logs
-
构建metastore脚本
#!/bin/bash #HIVE_HOME HIVE_HOME=/export/server/hive-2.1.0-bin #run metastore $HIVE_HOME/bin/hive --service metastore >> $HIVE_HOME/logs/metastore.log 2>&1 &
-
构建HIveserver2脚本
#!/bin/bash #HIVE_HOME HIVE_HOME=/export/server/hive-2.1.0-bin #run hiveserver2 $HIVE_HOME/bin/hiveserver2 >> $HIVE_HOME/logs/hiveserver2.log 2>&1 &
-
构建beeline脚本
#!/bin/bash #HIVE_HOME HIVE_HOME=/export/server/hive-2.1.0-bin #run beeline $HIVE_HOME/bin/beeline -u jdbc:hive2://node3:10000 -n root -p 123456
2、SQL脚本
-
需求:每天0点以后,自动化的执行SQL语句实现对昨天的数据的处理
-
问题1:如何能实现自动化?
-
Linux:Crontab,定时去执行某个Linux的命令
* * * * * command 分钟 小时 日 月 周几 命令 5 00 * * * /bin/bash job.sh
-
工作中:任务流调度工具:Oozie、Azkaban、AirFlow
- 设计每个程序执行的时间以及顺序
-
-
问题2:怎么在脚本中运行程序
-
job.sh
#!/bin/bash
-
Spark、Mapreduce程序
#!/bin/bash yarn jar wordcount.jar mainclas args() spark-submit jar
-
Hive的SQL语句:如何能让SQL语句在Linux命令中通过命令来执行
Unrecognized option: -h usage: hive -d,--define <key=value> Variable subsitution to apply to hive commands. e.g. -d A=B or --define A=B --database <databasename> Specify the database to use -e <quoted-query-string> SQL from command line -f <filename> SQL from files -H,--help Print help information --hiveconf <property=value> Use value for given property --hivevar <key=value> Variable subsitution to apply to hive commands. e.g. --hivevar A=B -i <filename> Initialization SQL file -S,--silent Silent mode in interactive shell -v,--verbose Verbose mode (echo executed SQL to the console)
-
方式一:-e
-
功能:直接可以在Linux命令行运行SQL语句
-
举例
hive -e "select * from db_test.words"
-
应用:一般用于运行简单的SQL语句
-
-
方式二:-f
-
功能:运行一个SQL文件
-
举个栗子
-
编辑SQL文件:vim /export/data/hive.sql
show databases; use db_test; select * from words; select xiaoqu,tprice,sprice,region from tb_lianjia limit 10;
-
运行SQL文件
hive -f /export/data/hive.sql
-
-
应用:工作中最常用的方式
-
-
-
–database:指定连接哪个数据库,默认连接default数据库
-
–hiveconf:用于定义Hive中的属性或者变量
-
功能:一般用于临时修改属性或者在shell脚本中传递变量给SQL语句
-
举个栗子
-
在shell脚本中通过hive -f运行SQL文件,怎么动态的传递变量给SQL语句
-
脚本
#!/bin/bash #获取昨天的日期 #每天处理昨天的数据,昨天的数据文件是包含日期的 #/logs/:一天一个文件:2020-01-01.log、 2020-01-02.log/……2020-11-04.log yesterday=`date -d '-1 day' +%Y-%m-%d` echo $yesterday #运行SQL对昨天的数据进行处理 #hive -e "select count(*) from tbname where daystr='${yesterday}' " #--hiveconf:在hive中定义了一个变量yesterday hive --hiveconf yester=$yesterday -f /export/data/hive.sql
-
SQL文件
--在SQL语句中应用Hive中变量的值 select count(*) from tbname where daystr='${hiveconf:yester};
注意:写脚本不要用touch创建新文件,编码格式不适合linux;
-
-
-
3、命令
-
dfs:用于直接在Hive的命令行操作HDFS
-
set:用于查看或者临时的修改某个属性的值
-
Hive中本地模式:不提交给YARN,直接在当前机器启动一个JVM进程来运行Mapreduce
- hive.exec.mode.local.auto
-
-
本地模式的限制
-
MapTask的个数不能超过4个
-
处理的数据不能超过128M
-
ReduceTask的个数不能超过1个
-
-
4、日志配置
-
重命名日志配置文件
cd /export/server/hive-2.1.0-bin/conf/ mv hive-log4j2.properties.template hive-log4j2.properties
-
修改配置
vim hive-log4j2.properties #修改第24行 property.hive.log.dir = /export/server/hive-2.1.0-bin/logs
-
重启Hive的服务端
六、Hive的HQL
1、DDL:数据定义语言
-
数据库
-
列举
show databases;
-
创建
create database [if not exists ] dbname [comment] [location]
-
comment:给数据库加上注释,描述这个数据库的功能
-
location:用于手动指定Hive数据库在HDFS中的对应的目录位置
- 默认指定:在hive中创建的所有数据库默认会在/user/hive/warehouse目录下创建对应的目录
- 手动指定:手动指定这个数据库目录的位置
-
举个栗子
create database db_emp1; create database if not exists db_emp1; create database db_emp2 location '/testdb';
-
-
切换
use dbname;
-
删除
drop database dbname [cascade];
- cascade:用于删除非空数据库
-
-
表的管理
-
列举
show tables;
-
创建
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name( col1 type1 comment, col2 type2 comment, …… colN type1 comment ) [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] --分区表 [CLUSTERED BY (col_name) [SORTED BY (col_name ] INTO N BUCKETS] --分桶表 [ROW FORMAT row_format] --指定文件的分隔符 row format delimited fields terminated by '\001' --指定列的分隔符,默认为\001 lines terminated by '\n' --指定行的分隔符,默认为\n [STORED AS file_format] --指定文件存储类型 [LOCATION hdfs_path] --手动指定表对应的HDFS目录,默认表目录创建在数据库目录下
-
方式一:普通方式
create table tbname( 字段 );
-
创建员工表
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
create database db_emp; use db_emp; create table tb_emp( empno string, ename string, job string, managerid string, hiredate string, salary double, jiangjin double, deptno string ) row format delimited fields terminated by '\t'; load data local inpath '/export/data/emp.txt' into table tb_emp;
-
创建部门表
10,ACCOUNTING,NEW YORK
create table tb_dept( deptno string, dname string, loc string ) row format delimited fields terminated by ','; load data local inpath '/export/data/dept.txt' into table tb_dept;
-
-
方式二:将Select语句的结果保存到一张新表中
-
关键字:as
-
语法
create table newTablename as select ……
create table tb_emp_as as select empno,ename,salary,deptno from tb_emp;
-
-
方式三:复制表的结构到一张新表中
-
关键字:like
-
语法
create table newTablename like oldTableName;
create table tb_emp_like like tb_emp;
-
注意:只复制表结构,不复制数据内容
-
-
-
删除表
drop table tbname;
-
查看表
#查看表的结构 desc tbname; #查看表的元数据 desc formatted tbname;
-
清空表
truncat tbname;
2、DML:数据操作语言
-
MySQL:增删改:行级的操作
-
insert
insert into tbname[(col1,col2……)] values (v1,v2……)
-
delete
delete from tbname where ……
-
update
update tbname set col = newValue where ……
-
replace:插入更新
-
-
Hive中的DML的特征
- Hive是否支持MySQL中的insert、update、delete的功能与语法?
- 支持的,但是如果要是用这样的语法,对表的要求比较高
- 必须让表满足事务的支持
- 表必须为分桶表,要求表的文件数据类型必须为orc类型
- ……
- 工作中的业务场景中:几乎不会出现行级的事务需求,上面的功能几乎不会用到
- 修改
- 删除
- Hive是否支持MySQL中的insert、update、delete的功能与语法?
-
load:用于将一个文件关联到Hive的表中
-
语法
load data [local] inpath 'filepath' [overwrite] into table tbname;
-
选项
- local
- 加了:从本地上传一份文件数据到hive表对应的HDFS目录中
- 不加:从HDFS移动这个数据文件到hive表对应的目录中
- overwrite:表示是否覆盖表中的内容
- local
-
-
insert:将Select语句的结果保存到已存在的表中或者文件系统中
-
保存到已存在的表中
insert <overwrite | into> table tbname select …… from …… | from …… insert <overwrite | into> table tbname select ……
insert into table tb_emp_like select * from tb_emp limit 3;
-
保存到文件系统中
insert overwrite [local] directory 'path' select ……
insert overwrite local directory '/export/data/insert' select empno,ename,salary,deptno from tb_emp; insert overwrite local directory '/export/data/insert' row format delimited fields terminated by '\t' select empno,ename,salary,deptno from tb_emp;
-
3、DQL:数据查询语言
- 基本与MySQL的语法是一致的
七、表的分类
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name(
1、管理表/内部表
-
默认创建的表的类型:create table ……
-
特点
- 如果创建表的客户端断开连接,表依旧存在,不会自动删除
- 删除表时,会删除元数据,同时会删除数据
2、临时表:TEMPORARY
-
关键字:temporary
-
特点:表是临时存在,如果客户端一段断开,表会自动被删除,类似于ZK中的临时节点
-
应用:一般用于在做复杂分析处理的时候,存储临时中间结果
-
使用
create temporary table tb_emp_tmp( empno string, ename string, job string, managerid string, hiredate string, salary double, jiangjin double, deptno string ) row format delimited fields terminated by '\t'; load data local inpath '/export/data/emp.txt' into table tb_emp_tmp;
3、外部表:EXTERNAL
-
关键字:external
-
工作中主要使用大部分表的类型都是外部表
-
特点:在删除表时,只删除元数据,数据是不会被删除(保证数据的安全、如果两个团队用)
-
应用:删除表的时候不影响数据文件
-
举例:现在有一份数据文件,要基于这个文件做三件事情
-
数据分析师:构建一张表,做数据分析
create external table tb_emp_ext1( empno string, ename string, job string, managerid string, hiredate string, salary double, jiangjin double, deptno string ) row format delimited fields terminated by '\t' location '/user/hive/warehouse/db_emp.db/tb_emp';
(共用同一份数据)
-
算法工程师:构建一张表,做用户画像
create external table tb_emp_ext2( empno string, ename string, job string, managerid string, hiredate string, salary double, jiangjin double, deptno string ) row format delimited fields terminated by '\t' location '/user/hive/warehouse/db_emp.db/tb_emp';
-
用完了,将表删除(只删除元数据,不删除数据)
drop table tb_emp_ext2;
-
-
-
-
使用
create external table tb_emp_ext( empno string, ename string, job string, managerid string, hiredate string, salary double, jiangjin double, deptno string ) row format delimited fields terminated by '\t'; load data local inpath '/export/data/emp.txt' into table tb_emp_ext;