day05-Hive语法补充

于 2024-09-13 18:36:33 发布
阅读量524 收藏 4
点赞数 12
文章标签： hive hadoop 数据仓库 大数据 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sxwwudi/article/details/142217557
版权

day05-Hive语法补充

一、表的修改

1-1 alter 关键字

表名和字段的修改

使用 alter 关键字

-- 切换数据库
use ecut;

-- 创建表
create table tb_old(
    id int,
    name string,
    age int,
    gender string
);
  • 修改表名
-- 修改表名
alter table tb_old rename to tb_new;
  • 增加字段
--表中增加字段
alter table tb_new add columns (address string);
  • 字段修改
-- 字段名修改
alter table tb_new change name username string;
  • 字段类型修改
-- 字段类型修改
alter table tb_new change age age string;
  • 字段替换，可以实现删除
-- 字段的替换，可以用来实现删除
alter table tb_new replace columns (
    id int,
    username string,
    gender string,
    address string
);

二、表的数据加载

2-1 方法一：直接插入数据

-- 创建表
create table stu(id int,name string); 

-- 向表中插入数据
insert into stu values(1,'zhangsan’); 
insert into stu values(2,'lisi');

-- 查询数据 
select * from stu;

该方式每次插入都会在表目录中生成对应的数据文件，不推荐使用。

2-2 方法二：load数据加载

  • Load命令用于将外部数据加载到Hive表中
  1. 在Linux虚拟机中创建一个测试文件 stu.txt ，下图为文件内容和文件所在目录

在这里插入图片描述

在这里插入图片描述

  1. 根据文件内容创建对应的表
create table tb_stu7(
    id int,
    name string,
    gender string,
    age int
)
row format delimited fields terminated by ',';
  1. 加载数据
--向表加载数据
load data local inpath '/home/stu.txt' into table tb_stu7;
  1. 查看数据是否加载成功
select * from tb_stu7;

在这里插入图片描述

三、表的基本查询

3-1 SELECT查询

使用 select 完成表的查询计算

  • 格式
select 字段 from 表名
join 关联的表 on 关联的字段
where 根据指定的条件过滤表数据
group by 指定分组的字段
having 对分组计算后的数据进行过滤
order by 指定排序的字段 默认是升序 从小到大排序
limit 指定返回多少条数据进行展示

select 使用

  • 查询指定字段
-- 指定查询字段
select id,name,age,gender from tb_stu8;
  • 对字段修改别名
-- 对字段修改别名
select id,name as username,gender from tb_stu8;

在这里插入图片描述

  • 指定一个常量字段数据
-- 指定一个常量字段数据
select id,name as username,666 as data from tb_stu8;

在这里插入图片描述

  • 数据去重，内部执行计算（耗时较长）
-- 数据的去重展示
select distinct gender from tb_stu8;

在这里插入图片描述

3-2 过滤查询（where）

对表进行过滤

-- 等值判断过滤
select * from tb_stu8 where gender = '男';

-- 大小判断
-- 数据类型数据镜像大小判断
select * from tb_stu8 where age<20;
select * from tb_stu8 where age>20;
select * from tb_stu8 where age <= 20;

-- 空值判断 hive中null代表空值
select * from tb_stu8 where name is not null;
select * from tb_stu8 where name is null;

-- 模糊查询
-- 对字符串数据，按照指定字符内容查询
-- % 表示省略多个字符
-- _ 表示一个字符
select * from tb_stu8 where name like '王%';
select * from tb_stu8 where name like '%丽';
select * from tb_stu8 where name like '%小%';
select * from tb_stu8 where name like '王_';

3-3 范围条件过滤（between、in以及or）

-- 范围条件过滤
-- 对数据类型 int float 的数据进行过滤
-- 将18岁~20岁学生数据查询出来
select * from tb_stu8 where age between 18 and 20;

-- 根据指定的数据内容查询
-- 不限制数据类型
select * from tb_stu8 where age in (18,20);  -- 只查询age为18和20的
-- 不在限定范围内
select * from tb_stu8 where age not in (18,19);
select * from tb_stu8 where cls in ('CS','MA');  -- 只查询cls为CS和MA的

-- 多条件过滤
-- and的多条件过滤 多个条件必须都符合，返回对应的数据
-- 查询性别为男性，而且年龄为19岁的学生数据
select * from tb_stu8 where gender = '男' and age=19;
-- or的多条件
select * from tb_stu8 where gender = '男' or age=19;

3-4 group by使用

对数据进行分组计算

不同性别的年龄平均值

先按照性别进行分组，将相同性别的数据放在一起，然后在对相同分组内的年龄进行计算

分组需要配合聚合方法一起使用

  • 聚合方法
  • sum（计算的字段）-- 求和
  • avg() – 求平均
  • max() – 最大值
  • min() – 最小值
  • count() – 求一组内数据的数量

使用聚合计算的时候，最好只写 group by后面有的列，不然容易报错

-- 不同性别的年龄平均值
-- hive进行计算时，时间较长
-- 1. 需要转化为MapReduce
-- 2. 需要申请计算资源
select gender,avg(age) from tb_stu8 group by gender;

在这里插入图片描述

-- 多字段分组
-- 不同性别不同年级的年龄平均值
-- 分组时，先按照性别分组，然后在性别内，按照年级分组
select gender,cls,avg(age) from tb_stu8 group by gender, cls;

在这里插入图片描述

聚合方法也可以单独使用

不进行分组，对整个表进行计算

-- 求所有学生的平均年龄
select avg(age) from tb_stu8;

在这里插入图片描述

3-5 having 使用

  • 对分组后的数据进行过滤

  • having中使用的过滤条件和where的过滤条件一样

  • HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足

-- 查询以cls分组时，平均年龄大于19岁的信息
select gender,cls,avg(age) as ave_age from tb_stu8
group by gender, cls
having ave_age>19;

在这里插入图片描述

3-6 order by排序

指定排序字段

默认升序，从小到大

-- 默认升序 asc
select * from tb_stu8 order by age;

-- 按照年龄从大到小排序(下图为降序) desc
select * from tb_stu8 order by age desc;

在这里插入图片描述

3-7 limit使用

返回指定条数据

-- 返回前三条数据
select * from tb_stu8 limit 3;

分页展示

select * from tb_stu8 limit 页数,每页展示的数量;

-- 页数 m 表示
-- 每页数量 n 表示
-- 分页展示公式
limit (m-1)*n,n 

每页展示3条数据
第1页展示 
limit 0,3
第2页展示 
limit 3,3
第3页展示 
limit 6,3

-- 每页展示3条数据
-- 第1页展示
select * from tb_stu8 limit 0,3;
-- 第2页展示
select * from tb_stu8 limit 3,3;
-- 第3页展示
select * from tb_stu8 limit 6,3;

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

四、表的关联（join）

多张表数据合并的方式

  • 列合并
    • 要求两张表至少有一列相同的字段，可以使用相同字段作为关联的依据
    • 关键字 join

4-1 数据准备

-- 2024.9.13 
-- 直接复制粘贴到DataGrip里，运行之后就会生成四个表和基本数据了
USE `ecut`;

-- 表格如果存在则删除
DROP TABLE IF EXISTS `course`;
-- 创建course表
CREATE TABLE `course` (
  `c_id` string ,
  `c_name` string ,
  `t_id` string
) ;
-- 向course中插入数据
insert  into `course`(`c_id`,`c_name`,`t_id`) values ('01','语文','02'),('02','数学','01'),('03','英语','03');

-- 如果存在则删除score表
DROP TABLE IF EXISTS `score`;
-- 创建score表
CREATE TABLE `score` (
  `s_id` string ,
  `c_id` string ,
  `s_score` int
) ;
-- 向score表中插入数据
insert  into `score`(`s_id`,`c_id`,`s_score`) values ('01','01',80),('01','02',90),('01','03',99),('02','01',70),('02','02',60),('02','03',80),('03','01',80),('03','02',80),('03','03',80),('04','01',50),('04','02',30),('04','03',20),('05','01',76),('05','02',87),('06','01',31),('03','03',34),('07','02',89),('07','03',98);

-- 如果存在student表则删除
DROP TABLE IF EXISTS `student`;
-- 创建student表
CREATE TABLE `student` (
  `s_id` string,
  `s_name` string ,
  `s_birth` string ,
  `s_sex` string
);
-- 向student表中插入数据
insert  into `student`(`s_id`,`s_name`,`s_birth`,`s_sex`) values ('01','赵雷','1990-01-01','男'),('02','钱电','1990-12-21','男'),('03','孙风','1990-05-20','男'),('04','李云','1990-08-06','男'),('05','周梅','1991-12-01','女'),('06','吴兰','1992-03-01','女'),('07','郑竹','1989-07-01','女'),('08','王菊','1990-01-20','女');

-- 如果存在teacher表则删除
DROP TABLE IF EXISTS `teacher`;
-- 创建teacher表
CREATE TABLE `teacher` (
  `t_id` string ,
  `t_name` string
) ;
-- 向techer表中插入数据
insert  into `teacher`(`t_id`,`t_name`) values ('01','张三'),('02','李四'),('03','王五');

4-2 四张表的关系

查看表结构

在这里插入图片描述

用DataGrip查看，表的关系

在这里插入图片描述

4-3 表关联

内关联 join

找两表相同的字段数据进行展示

学生表和成绩表采用内联表，关联的字段是s_id

  • 学生表中的s_id是 1,2,3,4,5,6,7,8

  • 成绩表中的s_id是 1,2,3,4,5,6,7

内关联之后，只会显示相同字段数据内容，即 1,2,3,4,5,6,7

-- 内关联
select * from student stu join score s on stu.s_id=s.s_id;

在这里插入图片描述

左关联 left join

以 join 左边的表为主表

进行数据查询时，左表数据全显示，右表字段中不存在左表有的字段，则会以 null 代替

左表 学生表中的s_id是 1,2,3,4,5,6,7,8

右表 成绩表中的s_id是 1,2,3,4,5,6,7

左关联后，左表的学生信息全显示，右表 1~7 的数据会显示，但是8的数据会被 null 替换（也就是显示为空）

-- 左关联
select * from student stu left join score s on stu.s_id=s.s_id;

在这里插入图片描述

右关联 right join

与左关联同理，只是主表变为右表，其它规则依旧遵循

-- 右关联
select * from student stu right join score s on stu.s_id = s.s_id;

在这里插入图片描述

4-4 on 实现数据过滤

在关联中 on 可以当作 where 进行使用，对关联的数据进行过滤

-- on实现数据过滤（功能与where一样）
select * from student t1 join score t2 on t1.s_id=t2.s_id and t1.s_sex='女';

在这里插入图片描述

4-5 多表关联

四张表通过相同字段 s_id 进行内关联

-- 四张表关联
select * from student t1
join score t2 on t1.s_id=t2.s_id
join course t3 on t3.c_id = t2.c_id
join teacher t4 on t4.t_id = t3.t_id;

在这里插入图片描述

五、表的合并（union）

行合并

  • 要求两张表字段内容完全一样
  • 关键字 union

表tb_stu7中的数据

在这里插入图片描述

表tb_stu8中的数据

在这里插入图片描述

-- union合并  分别查询两张表的数据，然后进行合并
select * from tb_stu7
union
select * from tb_stu8;

在这里插入图片描述

六、表的子查询

  • 将一个查询后的结果（计算过）作为一个新的表数据再次进行查询计算

  • 将一个查询计算的结果作为另一个查询的条件判断

6-1 查询结果作为新的表数据使用

  • 格式
select * from (select * from 子查询) 子查询的表名（自己起一个）

-- 查询数学平均成绩（子查询）
select t2.c_id,c_name,avg(s_score) as avg_data from student t1
    join score t2 on t1.s_id = t2.s_id
    join course t3 on t2.c_id = t3.c_id and t3.c_name='数学'
group by c_name,t2.c_id) tb_avg

-- 筛选数学成绩大于数学平均成绩的学生信息
select * from 
-- 将刚刚的子查询作为一张表进行使用
(
select t2.c_id,c_name,avg(s_score) as avg_data from student t1
    join score t2 on t1.s_id = t2.s_id
    join course t3 on t2.c_id = t3.c_id and t3.c_name='数学'
group by c_name,t2.c_id) tb_avg
	-- 重新关联学生和成绩表，然后筛选数据
    join score t2 on t2.c_id=tb_avg.c_id
    join student t_stu on t_stu.s_id = t2.s_id
-- 筛选成绩大于平均成绩的学生
where t2.s_score > avg_data;

在这里插入图片描述

6-2 将查询结果作为过滤条件

-- 筛选数学成绩大于平均数学的成绩的学生信息
select * from student
join score s on student.s_id = s.s_id and s.c_id='02'
where s.s_score > (
    -- 计算数学平均成绩
    select avg(s_score) as avg_data from student t1
    join score t2 on t1.s_id = t2.s_id
    join course t3 on t2.c_id = t3.c_id and t3.c_name='数学'
group by c_name
);

在这里插入图片描述

萌神想
关注
【Hive-Hive函数大全】Hive函数的语法使用和举例描述
weixin_53543905的博客
03-03 1049
比如，M 是值为{‘f’ -> ‘foo’, ‘b’ -> ‘bar’, ‘all’ -> ‘foobar’}的 map 类型，那么 M[‘all’]将会返回’foobar’说明：count(*)统计检索出的行的个数，包括 NULL 值的行；比如，A 是个值为[‘foo’, ‘bar’]的数组类型，那么 A[0]将返回’foo’,而 A[1]将返回’bar’partToExtract 的有效值为：HOST， PATH，QUERY，REF，PROTOCOL，AUTHORITY，FILE，USERINFO。
hive常用语法
RangeYan
07-18 684
目录 一、关系运算： 1.等值比较: = 2.不等值比较: <> 3.小于比较: < 4.小于等于比较: <= 5.大于比较: > 6.大于等于比较: >= 7.空值判断: IS NULL 8.非空判断: IS NOTNULL 9. LIKE比较: LIKE 10. JAVA的LIKE操作: RLIKE 11. REGEX...
大数据学习-Hive SQL语法
窦再兴的博客
06-21 772
]，表示可选，如上[LOCATION]表示可写、可不写，表示或，如上ASC | DESC，表示二选一…，表示序列，即未完结，如上表示在SELECT后可以跟多个，以逗号隔开()，表示必填，如上(A | B | C)表示此处必填，填入内容在A、B、C中三选一。
Day1 - Hive基础知识
m0_73450879的博客
03-31 1487
hive对数据库数据表的基本命令、内外部表、静态动态分桶
Day 06-hive介绍与使用
qq_62907049的博客
06-23 839
数据仓库，英文名称为Data Warehouse，可简写为DW。是一个用于存储,分析,报告的数据系统.数据仓库的目的是构建面向分析的集成化数据环境,分析结果为企业提供决策支持.通过上图我们可以发现,数据仓库的定位很明确,就是面向分析,支持分析的的系统,这种系统我们称为OLAP(on-Line Analytic Processing 联机分析处理系统).
day04-HiveSQL
最新发布
sxwwudi的博客
09-13 298
数据 1-2 结构化数据 1-3 映射表 二、Hive架构
Day7----Hive基本操作
m0_66098020的博客
06-12 448
其中：WITH DBPROPERTIES ，用来指定数据属性数据。
Hadoop-2.8.0-Day08-Hive函数与HQL详解-课件与资料.zip
04-30
在这个"Day08-Hive函数与HQL详解"的课程资料中，我们将深入探讨Hive的函数使用以及HQL的相关知识。 Hive的核心功能在于其强大的SQL接口，即HiveQL，它允许用户以结构化查询的方式操作存储在Hadoop HDFS中的非结构化...
Hadoop-2.8.0-Day07-HA-Hive安装部署与HQL-课件与资料.zip
04-30
然后，我们将学习HQL（Hive Query Language），它是Hive提供的SQL-like查询语法。HQL支持常见的SQL操作，如SELECT、FROM、WHERE、GROUP BY等，但也有其独特之处，比如使用PARTITION BY处理分区数据。通过HQL，用户...
HIVE语法.docx
05-03
根据给定文件的信息，我们可以总结出Hive中的关键知识点，主要涵盖Hive的常用函数以及基本的运算操作。Hive是一种基于Hadoop的数据仓库工具，它提供了类SQL的查询语言"HiveQL"来处理存储在Hadoop文件系统中的大规模...
Hive任务优化参数整理
别人笑我太疯癫，我笑他人看不穿。
09-09 962
1.当你的查询数据量较大，此时spark等其他计算引擎会因为自身复杂的执行计划导致计算量很大，再加上计算中的硬性资源消耗，导致需要的资源使你无法接受，此时如果你可以接受较长时间的运行等待，建议使用hive，因为hive底层用的mr，任务分割截止到一次mr，没有那面大的消耗需求，你可以通俗的理解为hive只需要有你数据量大小和跑数据需要的进程类开销既可，它没有spark那种复杂的执行计划，因此它的使用成本是目前大数据计算引擎中最低的一个，单效率也是最慢的，不过对于大任务的处理，更稳定。
【Hadoop|HDFS篇】HDFS的读写流程
2301_80912559的博客
09-06 442
副本存储节点的选择问题：
数据仓库理论知识
m0_68143976的博客
09-06 698
数据仓库（英文：Date Warehouse，简称数仓、DW），是一个用于数据存储、分析、报告的数据系统。数据仓库的建设目的是面向分析的集成化数据环境，其数据来源于不同的外部系统，其结果开放给不同外部应用使用，为企业提供决策支持；
HBase
赞的心路历程
09-09 502
安装包：链接:提取码: 5up7。
flink增量检查点启动恢复的时间是很久的，业务上不能接受，怎么处理
goTsHgo的博客
09-11 619
针对增量检查点恢复时间长的问题，可以从多个方面进行优化，包括合并检查点、提升存储性能、优化 RocksDB 配置、并行化恢复过程等。同时，也可以考虑替代方案，如多活架构、预热恢复、降低状态依赖和改进状态管理策略。此外，在业务层面上，适当调整业务容忍度和提高用户透明度也是有效的应对措施。通过综合应用这些策略，可以有效地降低恢复时间并提高业务的连续性和可靠性。
衡石分析平台使用手册-新手上路介绍
zandy1011的博客
09-06 1184
​以下概括阐述了产品基本理念，这些理念贯穿我们的整个产品开发和设计，并最终体现为产品的功能形态。我们希望衡石分析平台可以成为各个领域的行业人员进行业务分析的称手兵器，简单稳定，容易上手。
【大数据】如何读取多个Excel文件并计算列数据的最大求和值
一个不断前行的程序者
09-09 859
在数据分析和处理中，我们经常需要从多个Excel文件中提取数据并进行计算。本文将带您通过一个实用的Python教程，学习如何读取D盘目录下特定文件夹内的多个Excel文件，并计算特定列数据的最大求和值。
如何确定kafka与zookeeper版本的对应关系
别来沾边儿
09-09 601
真希望官方可以贴心的整理出所有版本和zookeeper的对应关系，从目前kafka发展的趋势来看，都已经移除zookeeper依赖了，这件事也是木戏，哈哈哈；从解压目录我们可以kafka的源码是基于gradle的，我们只需要去gradle的依赖中找找zookeeper依赖的版本即可；搜索zookeeper关键字，可以看到Kafka2.7.2对应的Zookeeper版本为3.5.9。大家可以额外思考一下，如果用更高版本的zookeeper，kafka运行会不会有问题？这嘎嘎一列版本对应关系，哪来的呢？
StreamPark集成k8s运行Flink
weixin_45249411的博客
09-08 547
1.然后在有docekr的机器上登录。2.设置下命名空间(左侧)
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

萌神想

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值