Hive表进阶操作_2

搬砖人_li

已于 2024-09-14 15:17:14 修改

阅读量1.8k

点赞数 55

CC 4.0 BY-SA版权

分类专栏：大数据技术栈专栏文章标签： hive hadoop 数据仓库

于 2024-05-14 11:19:55 首次发布

本文链接：https://blog.csdn.net/m0_63593025/article/details/138845928

大数据技术栈专栏专栏收录该内容

12 篇文章

订阅专栏

Hive表操作

一、官方文档

hive文档: https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

Hadoop官网使用说明文档: https://hadoop.apache.org/docs/stable/hadoop-mapreduce-client/hadoop-mapreduce-client-core/MapReduceTutorial.html
hdfs文档: https://hadoop.apache.org/docs/stable/hadoop-project-dist/hadoop-hdfs/hdfs-default.xml
yarn文档: https://hadoop.apache.org/docs/stable/hadoop-yarn/hadoop-yarn-common/yarn-default.xml
mr文档: https://hadoop.apache.org/docs/stable/hadoop-mapreduce-client/hadoop-mapreduce-client-core/mapred-default.xml

二、分桶表

1、介绍

在这里插入图片描述

分桶表特点: 会产生分桶文件。

效率上注意: 查询数据的时候如果使用了分桶字段那么会提升数据查询效率（数据过滤where、join、分组、抽样查询）；否则会进行全表扫描

分桶与分区的区别: 
	1- 分桶字段必须是原有的字段名称
	2- 分桶产生的是多个文件；而分区产生的是多级目录
	3- 分区和分桶可以同时用在同一张表中。但是只能先分区，再分桶；不能先分桶再分区，因为我们不能在文件中在去建立文件夹

2、重要参数

-- 默认开启,hive2.x版本已经被移除
set hive.enforce.bucketing; -- 查看未定义因为已经被移除
set hive.enforce.bucketing=true; -- 修改

-- 查看reduce数量
-- 参数优先级: set方式 > hive文档 > hadoop文档
set mapreduce.job.reduces; -- 查看默认-1,代表自动根据桶数量匹配reduce数量
set mapreduce.job.reduces=3; -- 设置参数

注意: 如果在SQL文件中设置的参数，那么只针对该会话（session）中的后续执行的SQL语句有效。其他会话中的SQL语句无效。

补充：

如何修改Hive中中文乱码的问题？

https://zhuanlan.zhihu.com/p/226291980

3、基础分桶表

知识点:

语法:
create [external] table [if not exists] 分桶表名称(
	字段名称1 数据类型,
	字段名称2 数据类型
	....
)clustered by (分桶字段名称1,分桶字段名称2...) into 桶的数量 buckets;

示例:

create database day07;

use day07;

-- 创建分桶表
create table course_bucket_tb(
    cid int,
    cname string,
    sname string
)clustered by (cid) into 3 buckets
row format delimited fields terminated by '\t';

-- load方式将数据加载到Hive分桶表中
load data inpath '/dir/course.txt' into table course_bucket_tb;

-- 查询数据
select * from course_bucket_tb;

在这里插入图片描述

4、分桶表排序

知识点:

创建基础分桶表，然后桶内排序。语法: 
create [external] table [if not exists] 分桶表名称(
	字段名称1 数据类型,
	字段名称2 数据类型
	....
)
clustered by (分桶字段名称1,分桶字段名称2...) 
sorted by (排序字段名称1,排序字段名称2...)
into 桶的数量 buckets;

注意：
	1- 不管是clustered by还是sorted by这些字段，都只能去建表语句中选择已有的字段
	2- sorted by中可以按照字段进行升序（asc ascend）或者降序（desc descend）。默认是升序。
	3- clustered by中的字段与sorted by中的字段可以不一样

示例:

use day07;

-- 创建分桶表并且排序
create external table course_bucket_tb_sort(
    cid int,
    cname string,
    sname string
)clustered by (cid) -- 按照cid进行分桶
    sorted by (cid desc) -- 按照cid进行降序排序
    into 3 buckets -- 将数据分到3个桶里面去。也就是在HDFS上会创建3个文件
row format delimited fields terminated by '\t';

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

-- 验证数据
select * from course_bucket_tb_sort;

在这里插入图片描述

5、分桶原理

分桶原理
	1.1- 如果分桶的字段是数值类型，那么直接使用字段字段与桶的数量进行取模运算，得到要放到哪个桶里面去。
	1.2- 如果分桶的字段是字符串类型，那么先将字段值计算出一个Hash哈希值（是一个整数），然后拿着这个Hash值与桶的数量进行取模运算，得到要放到哪个桶里面去。
	取模解释: 也就是取余数。10%3=1

补充: 针对同一个内容，不管计算Hash值多少次，结果都是一样。例如下面world

在这里插入图片描述

6、分区表和分桶表区别

1- 分区表
	创建表的时候使用关键字: partitioned by (字段名称 字段类型)
	分区字段名注意事项: 分区字段不能在建表语句中存在
	分区表好处: 数据查询的时候使用分区字段能够提升数据分析速度，也就是减少了数据扫描
	分区表最直接的效果: 在HDFS下以分区字段和分区值创建了多级目录
	不建议直接上传文件在hdfs表根路径下: 分区表直接不能自动识别HDFS上目录变化，分区信息必须要在MySQL元数据中存在，也就是需要单独使用msck repair修复语句进行修复
	使用load方式加载hdfs中文件: 本质是移动文件到对应分区目录下
	工作中的使用: 使用非常多。一般是按照日期时间进行分区


2- 分桶表
	创建表的时候使用关键字: clustered by (分桶字段名) sorted by (排序字段) into 桶的数量 buckets
	分桶字段名注意事项: 字段只能从建表语句中存在的字段进行挑选
	分桶表好处: 使用分桶字段进行数据查询，例如：过滤、join、抽样查询等能够提升效率
	分桶表最直接的效果: 在HDFS下创建分桶文件
	不建议直接上传文件在hdfs表根路径下: 分桶表可以识别对应的文件中数据，但是并没有分桶的效果，不推荐使用
	使用load方式加载hdfs中文件: 本质是复制文件内容到分桶文件中
	工作中的使用: 使用很少。一般是结合业务进行分桶。例如将学生数据按照性别分成男性和女性俩个桶

三、复杂类型

1、hvie的SerDe机制

在这里插入图片描述

其中ROW FORMAT是语法关键字，DELIMITED和SERDE二选其一。本次我们主要学习DELIMITED关键字相关知识点
如果使用delimited: 表示底层默认使用的Serde类:LazySimpleSerDe类来处理数据。
如果使用serde:表示指定其他的Serde类来处理数据,支持用户自定义SerDe类。

Hive默认的序列化类: LazySimpleSerDe
包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。
在建表的时候可以根据数据的类型特点灵活搭配使用。
COLLECTION ITEMS TERMINATED BY '分隔符' : 指定集合类型（array）/结构类型(struct)元素的分隔符
MAP KEYS TERMINATED BY '分隔符' : 表示映射类型(map)键值对之间用的分隔

在这里插入图片描述

2、复杂类型

复杂类型建表格式: 
...
[row format delimited] # hive的serde机制
    [fields terminated by '字段分隔符'] # 自定义字段分隔符固定格式
    [collection ITEMS terminated by '集合分隔符'] # 自定义array同类型集合和struct不同类型集合
    [map KEYS terminated by '键值对分隔符'] # 自定义map映射kv类型
    [lines terminated by '\n'] # # 默认即可
...;

hive复杂类型: array struct map

array类型: 又叫做数组类型。用来存储相同类型的数据集合
	 建表指定类型:  array<元素的数据类型>
	 取值: 字段名[索引/下标/角标]。索引是从0开始
	 获取长度: size(字段名)
	 判断是否包含某个数据: array_contains(字段名)

struct类型:又叫做结构类型。可以存储不同了类型的数据集合
	 建表指定类型: struct<字段名称1:数据类型,字段名称2:数据类型...>
	 取值: 字段名.key键的名称
	
map类型: 又叫做映射类型。存储的是key-value键值对数据
	建表指定类型: map<key的类型,value的类型>
	取值: 字段名[key的名称]
	获取长度: size(字段名)，实际获取的是key-value键值对的对数
	获取所有key: map_keys(字段名)
	获取所有value: map_values(字段名)
	
注意: 这3个复杂数据类型什么时候需要用到，都需要根据公司里面数据的结构来做决定

在这里插入图片描述

3、array示例

需求: 已知data_for_array_type.txt文件,存储了学生以及居住过的城市信息,要求建hive表把对应的数据存储起来

use day07;

-- 创建表
create table array_tb(
    name string,
    work_location array<string>
)
row format delimited fields terminated by '\t'
collection items terminated by ','; -- 指定array数组中元素间的分隔符


-- load加载数据
load data inpath '/dir/data_for_array_type.txt' into table array_tb;

-- 验证数据
select * from array_tb;

-- array专有的操作
-- 函数：具备特殊功能的代码，例如size
-- size(work_location) 统计数组中有多少个元素。该案例中也就是统计你去多少个城市工作过
select name,size(work_location) as city_cnt from array_tb;

-- 数组字段名称[索引/下标/角标]。索引是从0开始
select name,work_location[-1] from array_tb;
select name,work_location[0] from array_tb; -- 取数组中的第一个元素
select name,work_location[1] from array_tb; -- 取数组中的第二个元素
select name,work_location[10] from array_tb; -- 如果根据索引取不到对应的元素，那么返回的是null空值。null值（你没有去参加考试）和0（参加考试，但是考了0分）是不一样

-- 判断数组中是否存在某个元素/数据
-- array_contains：是一个函数，用来判断元素在数组中是否存在。如果存在返回true；如果不存在返回false
select name,array_contains(work_location,"chengdu") from array_tb;

在这里插入图片描述

4、struct示例

需求: 已知data_for_struct_type.txt文件存储了用户姓名和年龄基本信息,要求建hive表把对应的数据存储起来

use day07;

-- 创建表
create external table singer_struct(
    id int,
    info struct<name:string,num:int>
)row format delimited fields terminated by '#'
collection items terminated by ':';-- 指定struct中元素间的分隔符

-- 加载数据
load data inpath '/dir/data_for_struct_type.txt' into table singer_struct;

-- 验证数据
select * from singer_struct;

-- struct中特有的操作
-- 如果想要看struct中的具体信息，需要通过 struct字段名称.key键
select id,info.name,info.num from singer_struct;
select id,info.name,info.num,info.aaaa from singer_struct;

-- struct中不支持size()函数
-- select id,size(info) from singer_struct;

在这里插入图片描述

原因: 如果访问struct中不存在的key会报如上的问题。

5、map示例

需求: 已知data_for_map_type.txt文件存储了每个学生详细的家庭信息,要求建hive表把对应数据存储起来

use day07;

-- 创建表
create table star_map(
    id int,
    name string,
    family map<string,string>, -- 前面的string是key的数据类型，后面的string是value的数据类型
    age int
)row format delimited fields terminated by ',' -- 指定字段间的分隔符
collection items terminated by '#' -- 指定map中key-value键值对间的分隔符
map keys terminated by ':'; -- 指定key-value键值对里面的分隔符

-- load导入数据到Hive表中
load data inpath '/dir/data_for_map_type.txt' into table star_map;

-- 数据验证
select * from star_map;

-- map数据类型的特殊操作
select id,name,age,family['father'] as father,family['mother'] as mother from star_map;

-- 获取map中所有key的信息
select id,name,age,map_keys(family) as keys from star_map;

-- 获取map中所有key的信息，之后，再通过array获取数据的方式，获取指定索引的元素值
select id,name,age,map_keys(family),map_keys(family)[1] as keys from star_map;


-- 获取map中所有value的信息
select id,name,age,map_values(family) as keys from star_map;
-- 获取map中所有value的信息，之后，再通过array获取数据的方式，获取指定索引的元素值
select id,name,age,map_values(family),map_values(family)[2] as keys from star_map;

-- size函数：在map中，是用来获取key-value键值对的对数
select id,name,age,size(family) from star_map;

-- array_contains函数
select id,name,age,map_keys(family),array_contains(map_keys(family),"brother") from star_map;

在这里插入图片描述

四、Hive乱码解决

1、乱码现象

create database test1 comment "乱码测试";

use test1;

CREATE TABLE orders (
    orderId bigint COMMENT '订单id',
    orderNo string COMMENT '订单编号',
    shopId bigint COMMENT '门店id'
);

在这里插入图片描述

2、处理步骤

注意：推荐大家先将node1虚拟机拍一个快照，拍完后再修改。
在node1上修改hive配置文件

文件路径: /export/server/hive/conf/hive-site.xml

修改内容：&useUnicode=true&characterEncoding=UTF-8

修改截图：

在这里插入图片描述

修改MySQL表：注意，下面的SQL语句，要在node1的MySQL上运行

use hive3;
#修改表字段注解和表注解
alter table DBS modify column `DESC` varchar(256) character set utf8;
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
#修改分区字段注解
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
#修改索引注解
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

在这里插入图片描述

重启Hive的metastore进程

先通过kill -9 杀死metastore进程。然后再通过 nohup hive --service metastore & 重启

验证


drop database test1 cascade;

create database test1 comment "乱码测试";

use test1;

CREATE TABLE orders (
    orderId bigint COMMENT '订单id',
    orderNo string COMMENT '订单编号',
    shopId bigint COMMENT '门店id'
);

在这里插入图片描述

五、select查询

1、类sql基本查询

use day07;

-- 创建演示的表
CREATE TABLE day07.orders (
    orderId bigint COMMENT '订单id',
    orderNo string COMMENT '订单编号',
    shopId bigint COMMENT '门店id',
    userId bigint COMMENT '用户id',
    orderStatus tinyint COMMENT '订单状态 -3:用户拒收 -2:未付款的订单 -1：用户取消 0:待发货 1:配送中 2:用户确认收货',
    goodsMoney double COMMENT '商品金额',
    deliverMoney double COMMENT '运费',
    totalMoney double COMMENT '订单金额（包括运费）',
    realTotalMoney double COMMENT '实际订单金额（折扣后金额）',
    payType tinyint COMMENT '支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他',
    isPay tinyint COMMENT '是否支付 0:未支付 1:已支付',
    userName string COMMENT '收件人姓名',
    userAddress string COMMENT '收件人地址',
    userPhone string COMMENT '收件人电话',
    createTime timestamp COMMENT '下单时间',
    payTime timestamp COMMENT '支付时间',
    totalPayFee int COMMENT '总支付金额'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- load加载数据到表中
load data inpath '/dir/itheima_orders.txt' into table orders;

-- 基础查询语句
select * from orders;
select count(*) as cnt from orders;

-- 查询具体的字段。工作中推荐这样写，可以提升SQL运行效率，列裁剪
select orderId,orderNo from orders;

-- 取别名。as关键字可以省略，一般推荐加上
-- 字段取别名
-- 使用场景：1- 原始字段名称比较长的时候；2- 当多个表同时查询的时候，可能会出现重名
select orderId as oid,orderNo ono from orders;

-- 表取别名
select orderId as oid,orderNo ono from orders as o;
select o.orderId as oid,o.orderNo ono from orders as o;

-- distinct去重
select distinct shopId from orders;

-- 演示where语句
/*
    比较运算符：> < >= <= != <>不等于
    逻辑运算符：and并且 or或者 not取反/非
    模糊查询：%匹配0到多个内容，_匹配仅且一个
    空判断：为空 is null；不为空is not null
    范围查询：
        between 开始 and 结束
        in (x,y,z)
 */
-- 比较运算符
select * from orders where orderId>=1 and orderId<10;
select * from orders where orderId<>1;

-- 逻辑运算符
select * from orders where orderId<1 and orderId>10;
select * from orders where orderId<1 or orderId>10 order by orderId;
select * from orders where not orderId<1;

-- 模糊查询
select * from orders where orderNo like '1%'; -- % 匹配的个数 >=0
select * from orders where userId like '__'; -- _ 匹配的个数 ==1

-- 空判断
select * from orders where userId is null;
select * from orders where userId is not null;

-- 范围查询
select * from orders where userId between 2 and 3; -- 左右都是闭区间 [2,3]。小的放前面，大的放后面
select * from orders where userId between 3 and 2;

select * from orders where userId in (2,4);

-- 通用函数使用
-- 注意：在使用聚合函数的时候，需要把字段（维度字段）放到group by的语句
-- 维度是X轴，指标是Y轴
select userId,max(totalPayFee) as max_value from orders group by userId;

-- having：跟在group by的后面，对分组后的数据进行过滤
select userId,max(totalPayFee) as max_value from orders group by userId having userId=2;

-- 分页
-- limit x,y：注意x和y都是整数。x是从0开始，表示当页的第一条数据的索引；y每页的数据条数
select * from orders order by userId asc limit 0,2;

使用聚合函数的时候容易犯的错误：

在这里插入图片描述

原因: 没有把字段（维度字段）放到group by的语句

2、类sql多表查询

use day07;

CREATE TABLE day07.users (
    userId int,
    loginName string,
    loginSecret int,
    loginPwd string,
    userSex tinyint,
    userName string,
    trueName string,
    brithday date,
    userPhoto string,
    userQQ string,
    userPhone string,
    userScore int,
    userTotalScore int,
    userFrom tinyint,
    userMoney double,
    lockMoney double,
    createTime timestamp,
    payPwd string,
    rechargeMoney double
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- load导入数据
load data inpath '/dir/itheima_users.txt' into table users;

-- 数据验证
select * from users;

-- cross join：交叉查询，会产生笛卡尔积。在工作中，要尽可能避免产生笛卡尔积
select * from users cross join orders;
select count(*) as cnt from users cross join orders;

-- inner join：内连接本质是取两个表的交集
select * from users as u inner join orders as o on u.userId=o.userId;

-- left outer join：左外关联。以左边的表为主表，取匹配上的数据。差集
select * from users as u left outer join orders as o on u.userId=o.userId;

-- right outer join：右外关联。以右边的表为主表，取匹配上的数据。差集
select * from orders as o right outer join users as u on u.userId=o.userId;

-- 子查询
-- 获得最高订单金额的用户ID
-- 1- 获得最高的订单金额；2- 拿着最高金额，作为数据过滤条件，去找到对应的用户
select max(totalMoney) as max_money from orders;
select userId from orders where totalmoney=(select max(totalMoney) as max_money from orders);

3、hive整体语句格式

SELECT [ALL | DISTINCT]字段名称1,字段名称2, ...
FROM 表名称
[WHERE 数据过滤条件]
[GROUP BY 分组字段名称1,分组字段名称2...]
[HAVING 分组之后的数据过滤条件]
[ORDER BY 排序字段名称1,排序字段名称2...]
[CLUSTER  BY 分桶排序的字段名1,分桶排序的字段名2... | [DISTRIBUTE BY 分桶字段名1,分桶字段名2...] [SORT BY 排序的字段名1,排序的字段名2...]]
[LIMIT 分页配置]

4、hive其他join操作

知识点:

在Hive中除了cross join left outer join等这些以外，还有left semi join（左半连接）、full outer join（全外连接）

全外连接: 左表 full outer join 右表 on 关联条件

左半开连接: 左表 left semi join 右表 on 关联条件

示例:

-- 全外连接：full outer join on 大白话解释：左外和右外结果合并
select * from users u full outer join orders o on u.userId = o.userid;
-- 左半连接：left semi join on
select * from users u left semi join orders o on u.userId = o.userid;

hive中所有join的演示：

use day07;
create table tb_1(
    id int,
    name string
)row format delimited fields terminated by ',';


create table tb_2(
    id int,
    name string
)row format delimited fields terminated by ',';


select * from tb_1;
select * from tb_2;

-- cross join：产生笛卡尔积，tb_1 * tb_2。写SQL的时候，尽可能避免
select * from tb_1 cross join tb_2;

-- left outer join：以左表为主，将左表中所有的数据都展示，只展示右表中关联上的内容
select * from tb_1 left outer join tb_2 on tb_1.id=tb_2.id;

-- right outer join：以右表为主，将右表中所有的数据都展示，只展示左表中关联上的内容
select * from tb_1 right outer join tb_2 on tb_1.id=tb_2.id;

-- full outer join：实际是左右join的结果合并。也就是现在没有哪个是主表，地位都是一样的
select * from tb_1 full outer join tb_2 on tb_1.id=tb_2.id;

-- left semi join：左半连接，左右表关联，关联上了以后，只展示左表的数据，右表数据不展示。如果有数据重复，不会去重
select * from tb_1 left semi join tb_2 on tb_1.id=tb_2.id;

-- 没有right semi join。可以交换表的位置然后通过left semi join来实现
-- select * from tb_1 right semi join tb_2 on tb_1.id=tb_2.id;

select * from tb_2 left semi join tb_1 on tb_1.id=tb_2.id;