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,beijincreate 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-09create 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)*yy: 本次查询记录数多表查询（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,小米,c2hive有别于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,小米,c2union查询
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;
