Hive笔记

冷酷无情小帅哥

于 2022-02-24 19:47:07 发布

阅读量305

点赞数

分类专栏： Hadoop 文章标签： hive hadoop big data

本文链接：https://blog.csdn.net/weixin_42466752/article/details/123119197

版权

Hadoop 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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/访问

冷酷无情小帅哥

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Hive笔记

Hive1.Hive的定义Hive本质是：将 SQL 转换为 MapReduce 的任务进行运算底层由HDFS来提供数据存储可以将Hive理解为一个：将 SQL 转换为 MapReduce 任务的工具Hive的引擎是可以是MR/Spark/Flink2.Hive元数据Hive的元数据默认存储在自带的 derby 数据库中，生产中多采用MySQL存储元数据。3.Hive数据类型常用类型：BIGINT、DOUBLE、STRING、TIMESTAMP其他类型：TINYIN
复制链接

扫一扫

专栏目录