Hive
1.Hive的定义
-
Hive本质是:将 SQL 转换为 MapReduce 的任务进行运算
-
底层由HDFS来提供数据存储
-
可以将Hive理解为一个:将 SQL 转换为 MapReduce 任务的工具
-
Hive的引擎是可以是MR/Spark/Flink
2.Hive元数据
Hive的元数据默认存储在自带的 derby 数据库中,生产中多采用MySQL存储元数据。
3.Hive数据类型
常用类型:BIGINT、DOUBLE、STRING、TIMESTAMP
其他类型:TINYINT、SMALLINT、INT、BOOLEAN、FLOAT、BINARY
集合数据类型:ARRAY、MAP、STRUCT、UNION
4.Hive默认分隔符
分隔符 | 名称 | 说明 |
---|---|---|
\n | 换行符 | 用于分隔行。每一行是一条记录,使用换行符分割数据 |
^A | < Ctrl >+A | 用于分隔字段。在CREATE TABLE语句中使用八进制编码\001表示 |
^B | < Ctrl >+B | 用于分隔 ARRAY、MAP、STRUCT 中的元素。在CREATE TABLE语句中使用八进制编码\002表示 |
^C | < Ctrl >+C | Map中 key、value之间的分隔符。在CREATE TABLE语句中使用八进制编码\003表示 |
Hive 中没有定义专门的数据格式,数据格式可以由用户指定,用户定义数据格式需要指定三个属性:
- 列分隔符(通常为空格、"\t"、"\x001")
- 行分隔符("\n")
- 读取文件数据的方法
将 Hive 数据导出到本地时,系统默认的分隔符是A、B、^C 这些特殊字符。
使用命令
cat -A file.dat
Hive中数据加载过程采用"读时模式" (schema on read),加载数据时不进行数据格式的校验,读取数据时如果不合法则显示NULL。这种模式的优点是加载数据迅速。
5.HQL操作
数据库操作
建表(内表、外表)
分区表
分桶表
修改表、删表
数据导入导出
增
删
改
查(重点)
函数(重点)
-- 数据库操作
-- 创建数据库。添加备注,指定数据库在存放hdfs位置
create database if not exists ods
comment 'ods层'
location '/user/hive/ods.db';
create database if not exists dwd
comment 'dwd层'
location '/user/hive/dwd.db';
-- 查看数据库
show databases;
desc database ods;
-- 使用数据库
use ods;
-- 删除数据库(少用)cascade表示强制删除非空数据库
drop database databasename cascade;
-- 建表(内表、外表)
-- 默认情况下,创建内部表。如果要创建外部表,需要使用关键字 external
-- 在删除内部表时,表的定义(元数据) 和 数据 同时被删除
-- 在删除外部表时,仅删除表的定义,数据被保留,一般都是建外表
drop table ods.game1_log_player_login;
create external table IF NOT EXISTS ods.game1_log_player_login
(n_uid bigint comment '用户唯一UID',
d_login_in String comment '登入时间',
d_login_out String comment '登出时间',
n_ip bigint comment 'IP地址'
)
COMMENT '游戏1登录日志表'
PARTITIONED by (dt String comment '数据日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ';'
collection items terminated by ','
map keys terminated by ':'
stored as parquet
TBLPROPERTIES ('creator'='lhx', 'crate_time'='2022-02-24')
;
drop table ods.game1_player;
create external table IF NOT EXISTS ods.game1_player
(n_uid bigint comment '用户唯一UID',
d_create String comment '角色创角时间',
n_lv bigint comment '角色游戏内登记',
n_force bigint comment '角色最高战斗力',
s_phone_type String comment '登录机型'
)
COMMENT '游戏1角色表'
PARTITIONED by (dt String comment '数据录入日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ';'
collection items terminated by ','
map keys terminated by ':'
stored as parquet
TBLPROPERTIES ('creator'='lhx', 'crate_time'='2022-02-24')
;
-- 分区表partition
-- 分区字段不是表中已经存在的数据,可以将分区字段看成伪列
-- 增加分区
alter table ods.game1_log_player_login add partition(dt='2022-02-01')
partition(dt='2020-02-02')
partition(dt='2020-02-03')
partition(dt='2020-02-04')
partition(dt='2020-02-05');
-- 查看分区
show partitions ods.game1_log_player_login;
-- 删除分区
alter table ods.game1_log_player_login drop partition(dt='2020-06-03'),
partition(dt='2020-06-04');
-- 分桶表(不常用)
-- 修改表、删表
alter table game1_log_player_login rename to game2_log_player_login;
alter table game1_log_player_login change column n_lv n_lv_max int;
alter table game1_log_player_login change column n_lv_max n_lv_max String;
alter table game1_log_player_login add columns (s_phone_type string);
drop table game1_log_player_login;
-- 数据导入
--load导入数据
--import导入数据
--常用ETL工具或代码实现,详情见Sqoop、DdataX技术栈
-- 增
insert into table game1_log_player_login partition(dt='2022-02-01')
values (001,'2022-02-01 09:01:01', '2022-02-01 09:30:01',3232256121),
(002,'2022-02-01 09:15:01', '2022-02-01 09:45:01',3232256121),
(003,'2022-02-01 09:30:01', '2022-02-01 09:35:01',3232256121),
(001,'2022-02-01 10:01:01', '2022-02-01 11:30:01',3232256121),
(001,'2022-02-01 12:01:01', '2022-02-01 13:30:01',3232256121),
(001,'2022-02-01 16:01:01', '2022-02-01 20:30:01',3232256121);
select * from game1_log_player_login;
-- 删、改
-- Attempt to do update or delete using transaction manager that does not support these operations.
-- 建表的时候需要加上TBLPROPERTIES ("transactional"="true",...)才能实现DML命令(增删改查)
-- HDFS不支持文件的修改
-- 总结:Hadoop内尽量不要改
-- 查(重点)
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 [offset,] rows]
-- 运算符
-- 比较运算符:=;!=;is [not] null;LIKE;between;REGEXP;....
-- 逻辑运算符:and、or、not
-- 关联查询(表连接)
--1. 内连接: [inner] join
--2. 外连接 (outer join)
-- 左外连接。 left [outer] join,左表的数据全部显示
-- 右外连接。 right [outer] join,右表的数据全部显示
-- 全外连接。 full [outer] join,两张表的数据都显示
-- 关联查询注意连接条件,连接条件出错会出现笛卡尔积
-- 排序
--order by。执行全局排序,效率低。生产环境中慎用
--sort by。使数据局部有序(在reduce内部有序)
--distribute by。按照指定的条件将数据分组,常与sort by联用,使数据局部有序
--cluster by。当distribute by 与 sort by是同一个字段时,可使用cluster by简化语法
-- 函数
-- with as 作为子查询,增加代码可读性
-- 查看系统自带函数
show functions;
-- 显示自带函数的用法
desc function to_date;
desc function extended to_date;
-- 日期函数(常用)
-- 当前日期
select current_timestamp();
-- 时间戳转日期
select from_unixtime(1645683780);
select from_unixtime(1645683780, 'yyyyMMdd');
select from_unixtime(1645683780, 'yyyy-MM-dd HH:mm:ss');
-- 日期转时间戳
select unix_timestamp('2022-02-24 14:23:00');
-- 计算时差
select datediff('2020-04-18','2022-02-21');
-- 字符串转时间(字符串必须为:yyyy-MM-dd格式)
select to_date('2022-01-01');
select to_date('2022-01-01 12:12:12');
-- 日期、时间戳、字符串类型格式化输出标准时间格式
select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss');
select date_format(current_date(), 'yyyyMMdd');
select date_format('2022-02-01', 'yyyy-MM-dd HH:mm:ss');
-- 条件函数(常用)
CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
-- 窗口函数(重点)
-- 计算uid对应手机型号的登录次数
select a.n_uid,b.s_phone_type ,count(a.n_uid) over (partition by b.s_phone_type order by b.n_force) login_cnt
from game1_log_player_login a
left join ods.game1_player b
on a.n_uid =b.n_uid ;
-- 排名函数(结合over窗口函数使用)
row_number()。排名顺序增加不会重复;如1、2、3、4、... ...
RANK()。 排名相等会在名次中留下空位;如1、2、2、4、5、... ...
DENSE_RANK()。 排名相等会在名次中不会留下空位 ;如1、2、2、3、4、... ...
-- 序列函数(难点)
lag。返回当前数据行的上一行数据
lead。返回当前数据行的下一行数据
first_value。取分组内排序后,截止到当前行,第一个值
last_value。分组内排序后,截止到当前行,最后一个值
ntile。将分组的数据按照顺序切分成n片,返回当前切片值
-- 自定义函数(难点)
UDF(User Defined Function)。用户自定义函数,一进一出
UDAF(User Defined Aggregation Function)。用户自定义聚集函数,多进一出;类似于:count/max/min
UDTF(User Defined Table-Generating Functions)。用户自定义表生成函数,一进多出;类似于:explode
-- UDF开发实例
--继承org.apache.hadoop.hive.ql.exec.UDF
--需要实现evaluate函数;evaluate函数支持重载
--UDF必须要有返回类型,可以返回null,但是返回类型不能为void
-- 身份证脱敏函数demo
select identifyencrypt('320502202111093517');
UDF开发:
package hive.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
public class identifyencrypt extends UDF{
public Text evaluate(final Text t) {
if (18 == t.getLength()) {
String identify = t.toString();
return new Text(identify.substring(0,4).concat("***********").concat(identify.substring(15,18)));
}
return new Text("不符合格式");
}
}
打包后将jar上传HDFS
在Hive命令行中创建永久函数
create function identifyencrypt as 'hive.udf.identifyencrypt' using jar 'hdfs:/user/hadoop/jar/hive-udf-identifyencrypt.jar';
6.调优思路
-
把Hive的计算引擎MapReduce换掉
-
比较大的表使用分区表,分桶表
-
存储格式用Parquet
-
参数优化(合并多个小文件)
-
在map执行前合并小文件,减少map数
# 缺省参数 set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
-
在Map-Reduce的任务结束时合并小文件
# 在 map-only 任务结束时合并小文件,默认true SET hive.merge.mapfiles = true; # 在 map-reduce 任务结束时合并小文件,默认false SET hive.merge.mapredfiles = true; # 合并文件的大小,默认256M SET hive.merge.size.per.task = 268435456; # 当输出文件的平均大小小于该值时,启动一个独立的map-reduce任务进行文件merge SET hive.merge.smallfiles.avgsize = 16777216;
参数优化需要仔细看参数代表的含义:
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
-
-
查询SQL优化(常用)
7.jdbc连接Hive
-
启动hiveserver2(也可以后台启动加&)
hive --service hiveserver2
-
Linux上beeline工具测试使用jdbc方式连接
$HIVE_HOME/bin/beeline !connect jdbc:hive2://192.168.80.121:10000 root 123456;
-
hiveserver端口号默认是10000使用beeline通过jdbc连接上之后就可以像client一样操作,常用dbeaver操作hive。
-
hiveserver2会同时启动一个webui,端口号默认为10002,可以通过http://localhost:10002/访问
$HIVE_HOME/bin/beeline
!connect jdbc:hive2://192.168.80.121:10000 root 123456;
- hiveserver端口号默认是10000使用beeline通过jdbc连接上之后就可以像client一样操作,常用dbeaver操作hive。
- hiveserver2会同时启动一个webui,端口号默认为10002,可以通过http://localhost:10002/访问