当前位置： 首页 > article >正文

Hive 操作基础（进阶篇✌️）

article 2024/11/8 8:40:56

Hive 进阶操作

分区表

创建分区表

create table score_part(字段名 字段类型,字段名 字段类型 )partitioned by (分区字段 分区类型) 
row format delimited fields terminated by '\t';

创建单极分区表

注意: 分区的列名不能和数据列名相同.分区列会当做虚拟列出现在数据列的后面.
create table order_one_part(id int,pname string,price int,uint int  )partitioned by (year string) 
row format delimited fields terminated by ' ';

---添加数据
load data inpath '/order202251.txt' into table order_one_part partition(year='2022');
load data inpath '/order202351.txt' into table order_one_part partition(year='2023');
load data inpath '/order202352.txt' into table order_one_part partition(year='2023');

-- 查询数据 -- 查询2022年的所有订单
-- 查询2022的数据2023目录中的数据没有参数进来.查询效率高.避免全表扫描.
select * from order_one_part where year='2022'; 
-- 全查
select * from order_one_part; -- 不会提升效率

创建多分区表

-- 注意: 分区的列名不能和数据列名相同.分区列会当做虚拟列出现在数据列的后面.
create table order_multi_part(id int,pname string,price int,uint int  )partitioned by 
(year string,month string,day string) row format delimited fields terminated by ' ';

---添加数据
load data inpath '/order202251.txt' into table order_multi_part partition(year='2022',month='5',day='1');
load data inpath '/order202351.txt' into table order_multi_part partition(year='2023',month='5',day='1');
load data inpath '/order202352.txt' into table order_multi_part partition(year='2023',month='5',day='2');
load data inpath '/order2023415.txt' into table order_multi_part partition(year='2023',month='4',day='15');

-- 查询2023年5月1号中的数据.查询效率高.避免全表扫描.
select * from order_multi_part where year='2023' and month='5' and day='1';

增删改查

添加分区: alter table 表名 add partition(分区字段='分区值');  


删除分区: alter table 表名 drop partition(分区字段='分区值');


修改分区名:  alter table 表名 partition(分区字段='分区值') rename to partition(分区字段='分区值');


查看所有分区: show partitions 表名;

修复分区

msck repair table 表名;

多级分区练习题

已知2023年产生的业务数据,存在文件order51.txt,order52.txt,order415.txt三个

-- 1.要求查询2023年全年数据

-- 2.要求查询2023年每个商品对应的销售额 -- select 后的字段要么在groupby后出现要么在聚合函数内出现

-- 3.要求查询2023年全年商品总销售额

-- 4.要求查询2023年5月的所有商品的所有商品销售情况

-- 5.要求查询2023年5月1号的所有商品信息

-- 6.要求查询2023年5月1号的所有商品的总销量

-- 创建分区表
create table order_multi_part(
    pid int,
    pname string,
    price double,
    quantity int
)partitioned by (year string,month string,day string)
row format delimited
fields terminated by ' ';

-- 加载数据文件
-- 先手动上传order51.txt,order52.txt,order415.txt文件到/binzi目录下
load data inpath '/data/order51.txt' into table order_multi_part partition (year='2023',month='5',day='1');
load data inpath '/data/order52.txt' into table order_multi_part partition (year='2023',month='5',day='2');
load data inpath '/data/order415.txt' into table order_multi_part partition (year='2023',month='4',day='15');

-- 查询表
select * from order_multi_part;
-- 1.要求查询2023年全年数据
select * from order_multi_part where year = '2023';
-- 2.要求查询2023年每个商品对应的销售额
-- select 后的字段要么在groupby后出现要么在聚合函数内出现
select pname,sum(price*quantity) from order_multi_part where year = '2023' group by pname;
-- 3.要求查询2023年全年商品总销售额
select sum(price*quantity) from order_multi_part where year = '2023';
-- 4.要求查询2023年5月的所有商品的所有商品销售情况
select * from order_multi_part where year = '2023' and month = '5';
-- 5.要求查询2023年5月1号的所有商品信息
select * from order_multi_part where year = '2023' and month = '5' and day = '1';
-- 6.要求查询2023年5月1号的所有商品的总销量
select sum(quantity) from order_multi_part where year = '2023' and month = '5' and day = '1';

 分桶

分桶表

分桶可以单独使用也可以在分区表中使用  

创建分桶表:
create table 表名(字段名 字段类型,字段名 字段类型 )
clustered by (分桶字段) [sorted by (桶内排序字段)] into num buckets
row format delimited fields terminated by '分隔符';

注意: 上述格式中num代表分桶的数量(如果没有额外修改reduce数量的话,默认分桶数量和reduce数量一致)

分桶的原理: 首先对分桶字段值进行hash加密,然后对分桶数量进行取模,生成对应分桶文件

Hash加密注意:同样的值被Hash加密后的结果是一致的,也就意味着取模结果是一样的

举例: 如’binzi’ 哈希值是93742710, 无论hash对3取模多少次,结果都是0

分桶的好处: 基于分桶列的特定操作，如：分组、过滤、JOIN、抽样等，均可带来性能提升

分桶字段如果选择重复值较多的字段会造成数据倾斜.应该避免这种分桶操作.

分桶的原理

select hash('字符串') % 桶个数 : 哈希取模法
hash('hello') --> 99162322  --> 计算多少次都是一样的值.拿着这个值对桶个数进行模运算.结果就是桶编号.

分桶且排序

分桶后边加上sorted by 字段.可以对桶内文件进行排序.

create table if not exists course_bucket3(cid int,cname string,sname string)
clustered by(cid) sorted by(cid) into 3 buckets
row format delimited fields terminated by '\t';

load data inpath '/course.txt' into table course_bucket3;--加载数据

select * from course_bucket3 where cid = 1;

HiveSerDe机制

COLLECTION ITEMS TERMINATED BY '分隔符' : 指定集合类型（array）/结构类型(struct)元素的分隔符
MAP KEYS TERMINATED BY '分隔符' :表示映射类型(map)键值对之间用的分隔

复杂类型Array

数据文件👉data-for-array-type.txt

-- 需求:已知data_for_array_type.txt文件,存储了学生以及居住过的城市信息,要求建hive表把对应的数据存储起来
-- zhangsan	beijing,shanghai,tianjin,hangzhou
-- wangwu	changchun,chengdu,wuhan,beijin

create table if not exists table_array(name string,address array<string>)
row format delimited fields terminated by '\t'
collection items terminated by ',';

-- 加载linux上的数据到hive表中 --特点: 加载完毕后linux上的数据依然存在.
load data local inpath '/mnt/data_for_array_type.txt' into table table_array;

select * from table_array;

-- 需求: 查询zhangsan的地址有几个?
select address from table_array where name='zhangsan';
select name, size(address) as addr_size from table_array where name='zhangsan';

-- 需求: 查询zhangsan的第二个地址?
select name,address[1] as addr from table_array where name='zhangsan';

-- 需求: 查询zhangsan是否在tianjin住过?
select * from table_array where array_contains(address,'tianjin');
select name,array_contains(address,'tianjin') `入住记录` from table_array where name = 'zhangsan';

复杂类型Struct

数据文件👉data-for-struct-type.txt

-- 需求: 已知data_for_struct_type.txt文件存储了用户姓名和年龄基本信息,要求建hive表把对应的数据存储起来
-- 1#周杰轮:11:2020-01-09
-- 2#林均杰:16:2020-10-09
-- 3#刘德滑:21:2020-03-05
-- 4#张学油:26:2020-11-09
-- 5#蔡依临:23:2020-12-09

create table table_struct(id int,info struct<name:string,age:int ,birth:string>)
row format delimited fields terminated by '#'
collection items terminated by ':';

-- 加载数据
load data local inpath '/mnt/data_for_struct_type.txt' into table table_struct;

-- 查询所有列
select * from table_struct;

-- 需求: 获取所有的姓名
select info.name from table_struct;

-- 需求: 获取所有的年龄
select info.age from table_struct;

复杂类型Map

数据文件👉data-for-map-type.txt

-- 需求: 查看所有人的father,mother信息
select id, name, info['father'] father, info['mother'] mother, age from test_map;

-- 需求: 查看所有人的家庭相关角色
select id, name, map_keys(info) as relation from test_map;

-- 需求: 查看所有人的家庭相关姓名
select id, name, map_values(info) as relation from test_map;

-- 需求: 查看所有人的家庭相关人员个数
select id,name,size(info) num from test_map;

-- 需求: 查看马大云是否包含brother角色
select * from test_map where array_contains(map_keys(info), 'brother');

Hive 查询语法

一般查询语法

基础查询格式: select distinct 字段名 from 表名;
    			注意: *代表所有字段  distinct去重  as给表或者字段起别名

条件查询格式: select distinct 字段名 from 表名 where 条件;
    			比较运算符: > < >= <= != <>
                逻辑运算符: and or not
                模糊查询: %代表任意0个或者多个字符   _代表任意1个字符
                空判断: 为空is null   不为空is not null
                范围查询: x到y的连续范围:between x and y    x或者y或者z类的非连续范围: in(x,y,z)

排序查询格式:  select distinct 字段名 from 表名 [where 条件] order by 排序字段名 asc|desc ;
    			asc : 升序 默认升序
    			desc: 降序

聚合查询格式: select 聚合函数(字段名) from 表名;
    			聚合函数: 又叫分组函数或者统计函数
    			聚合函数: count()  sum()  avg()  max()  min()

分组查询格式:  select 分组字段名,聚合函数(字段名) from 表名 [where 非聚合条件] group by 分组字段名 [having 聚合条件];
    			注意: 当分组查询的时候,select后的字段名要么在groupby后出现过,要么放在聚合函数内,否则报错
    			where和having区别?
    			区别1: 书写顺序不同,where在group by关键字前,having在group by关键字后
    			区别2: 执行顺序不同,where在分组之前过滤数据,having在分组之后过滤数据
    			区别3: 筛选数据不同,where只能在分组之前过滤非聚合数据,having在分组之后主要过滤聚合数据
    			区别4: 操作对象不同,where底层操作伪表,having底层操作运算区

分页查询格式: select 字段名 from 表名 [ order by 排序字段名 asc|desc] limit x,y;
    			x: 起始索引 默认从0开始,如果x为0可以省略    计算格式: x=(页数-1)*y
    			y: 本次查询记录数

多表查询（join）

数据文件👉categorys.txt   products.txt

-- 1. 准备数据源, 建表, 添加表数据.
-- table1: 商品表
CREATE TABLE product(
   pid string,
   pname string,
   category_id string
 ) row format delimited
fields terminated by ',';

-- table2: 分类表
CREATE TABLE category (
    cid string,
    cname string
) row format delimited
fields terminated by ',';

-- 加载数据
load data local  inpath '/mnt/join/categorys.txt' into table category;
load data local  inpath '/mnt/join/products.txt' into table product;

select * from product;
-- p1,联想,c1
-- p2,小米,c2
-- p3,null,null
select * from category;
-- c1,电脑
-- c2,手机
-- c3,null

-- 多表查询 cross join------交叉连接--------------
select * from product cross join category;
-- p1,联想,c1,c1,电脑
-- p1,联想,c1,c2,手机
-- p1,联想,c1,c3,null
-- p2,小米,c2,c1,电脑
-- p2,小米,c2,c2,手机
-- p2,小米,c2,c3,null
-- p3,null,null,c1,电脑
-- p3,null,null,c2,手机
-- p3,null,null,c3,null

-- 多表查询 inner join------内连接--------------
select * from product inner join category on product.category_id = category.cid;
-- p1,联想,c1,c1,电脑
-- p2,小米,c2,c2,手机

-- 多表查询 left join on------左外连接--------------
select * from product left join category on product.category_id = category.cid;
-- p1,联想,c1,c1,电脑
-- p2,小米,c2,c2,手机
-- p3,null,null,null,null

-- 多表查询 right join on------右外连接--------------
select * from product right join category on product.category_id = category.cid;
-- p1,联想,c1,c1,电脑
-- p2,小米,c2,c2,手机
-- null,null,null,c3,null

-- 多表查询 full join on------全外连接--------------
select * from product full join category on product.category_id = category.cid;
-- p2,小米,c2,c2,手机
-- p1,联想,c1,c1,电脑
-- null,null,null,c3,null
-- p3,null,null,null,null

-- 多表查询 union -----联合查询--------------
select * from product left join category on product.category_id = category.cid
union
select * from product right join category on product.category_id = category.cid;
null,null,null,c3,null
p1,联想,c1,c1,电脑
p2,小米,c2,c2,手机
p3,null,null,null,null

-- 多表查询 left semi join------左半连接--------------
select * from product left semi join category on product.category_id = category.cid;
-- p1,联想,c1
-- p2,小米,c2

hive有别于mysql的join

-- 多表查询 full join on------全外连接--------------
select * from product full join category on product.category_id = category.cid;
-- p2,小米,c2,c2,手机
-- p1,联想,c1,c1,电脑
-- null,null,null,c3,null
-- p3,null,null,null,null

-- 多表查询 left semi join------左半连接----[内连接中左表的数据]----------
select * from product left semi join category on product.category_id = category.cid;
-- p1,联想,c1
-- p2,小米,c2

union查询

union 是上下拼接的连接查询: 要求上下字段和类型要保持一种.
主要实现功能: 把两个select查询结果上下拼接起来.
id name age
id name age 


# 拼接过程中会去除重复.
select id ,name ,age from student where 条件1
union
select id ,name ,age from student where 条件2;

# 拼接过程中不会去重.
select id ,name ,age from student where 条件1
union all
select id ,name ,age from student where 条件2;

hive有别于mysql的排序

set mapreduce.job.reduces:  查看当前设置的reduce数量 默认结果是-1,代表自动匹配reduce数量和桶数量一致
set mapreduce.job.reduces = 数量 : -- 修改reduces数量

cluster by 字段名:  分桶且正序排序   弊端: 分和排序是同一个字段,相对不灵活

distribute by 字段名 sort by 字段名: distribute by负责分,sort by负责排序, 相对比较灵活

order by 字段名: 只能全局排序 

注意: cluster by 和 distribute by 字段名 sort by 字段名 受当前设置的reduces数量影响,但是设置的reduces数量对order by无影响,因为orderby就是全局排序,就是一个reduce

建表的时候指定分桶字段和排序字段: clustered by (字段名) sorted by (字段名) into 桶数量 buckets
	 注意: 如果建表的时候设置了桶数量,那么reduces建议设置值-1或者值大于桶数量

四个By的区别

数据文件👉students.txt

-- 创建表
create table students(
    id int,
    name string,
    gender string,
    age int,
    cls string
)row format delimited
fields terminated by ',';
-- 加载数据
load data inpath '/source/students.txt' into table students;
-- 验证数据
select * from students limit 1;


-- 查询reduces的数量
set mapreduce.job.reduces; -- -1代表根据任务实时改变
-- cluster by 字段名 查询的时候分桶且排序
-- 注意: 如果是1个reduces那么cluster by全局升序排序
select * from students cluster by id;
-- 修改reduces数量为3
set mapreduce.job.reduces=3;
-- 再次使用cluster by查询,查看效果
-- 效果: 如果多个reduces那么cluster by桶内局部排序
select * from students cluster by id;


-- distribute by + sort by
-- 设置reduces的数量为-1
set mapreduce.job.reduces = -1;
-- 默认1个ruduces数量,使用distribute by + sort by查询观察结果
-- 注意: 如果是1个ruduces那么distribute by + sort by全局排序
select * from students distribute by name sort by id desc;
-- 修改reduces数量
set mapreduce.job.reduces = 2;
-- 再次distribute by + sort by查询
-- 效果: 如果多个redueces,那么distribute by 分reduces数量个桶,sort by桶内局部排序
select * from students distribute by name sort by id desc;


-- order by
-- 注意: order by 永远都是全局排序,不受reduces数量影响,每次只用1个reduces
select * from students order by id desc;

http://www.kler.cn/a/384160.html

相关文章：

  • 数据结构——线性表与链表
  • Axure设计之三级联动选择器教程（中继器）
  • 开放寻址法、链式哈希数据结构详细解读
  • 【stm32】RTC时钟的介绍与使用
  • 在docker中搭建redis哨兵环境
  • Unity网络开发基础（part5.网络协议）
  • 基于Python的自然语言处理系列（54）：Neo4j DB QA Chain 实战
  • android gradle list
  • 基于MATLAB的人体行为检测与识别
  • 微服务架构面试内容整理-服务拆分的原则
  • 【React】默认导出和具名导出
  • 机器学习与数据挖掘_使用梯度下降法训练线性回归模型
  • 有什么办法换网络ip动态
  • 算法每日双题精讲——双指针（移动零，复写零）
  • Windows系统服务器怎么设置远程连接？详细步骤
  • Windows下QT调用MinGW编译的OpenCV
  • SIwave：释放 EMI 扫描仪/探测器的强大功能
  • 【CSS】“flex: 1“有什么用？
  • 如何在Linux环境中的Qt项目中使用ActiveMQ-CPP
  • 简单又便宜的实现电脑远程开机唤醒方法
  • 前端 | MYTED单篇TED词汇学习功能优化
  • leetcode 622.设计循环队列
  • DeBiFormer实战：使用DeBiFormer实现图像分类任务（二）
  • 高级 SQL 技巧详解
  • MDC（重要）
  • 物联网核心安全系列——物联网安全需求