HiveSQL基础Day03

2024-04-13 05:28
文章标签 基础 day03 hivesql

本文主要是介绍HiveSQL基础Day03，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

回顾总结

hive表的类型 ：内部表和外部表

删除内部表会删除表的所有数据

删除外部表只会删除表的元数据，hdfs上的行数据会保留

表的分区和分桶

本质都是对表数据的拆分存储

分区的方式 是通过创建不同的目录来拆分数据 ，根据数据本身的内容最为目录名

分桶的方式 是通过创建不同的文件来拆分数据 文件名时hash取余的名字

数据拆分后可以提升数据的查询效率

分桶还有特殊使用场景

分桶关联多张表

分桶随机采样

序列化

本质就是对hdfs上的文件数据进行读取和写入

可以通过row format delimited fields terminated by ',' 指定如何读取和写入hdfs上的字段数据

CREATE  [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name(col_name data_type  [COMMENT col_comment])[COMMENT table_comment]hive中的独有语法-- 分区字段指定，分区字段是不能表字段重复[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]-- 分桶字段，需要指定表中存在的字段[CLUSTERED BY (col_name, col_name, ...) INTO num_buckets BUCKETS]            -- 指定分割符   默认的字段分割符\001[ROW FORMAT row_format] -- 指定表的存储目录位置  不指定默认是在对应的数据库目录下创建表目录[LOCATION hdfs_path]

一、内置函数

内置函数时hive中自带的函数方法，用来对不同类型的字段数据进行操作

字符串，数值，条件判断

字符串方法

length
concat
concat_ws
substr
split
regexp_replace
% 
round()
ceil()
floor()
if()
​
case when 条件1  then 结果when 条件2  then 结果when 条件3  then 结果else前面条件都不成返回的结果
end 字段名称
-- 查看hive中的所有内置函数
show functions;
-- 查看函数的具体用户
desc function extended 函数名;

1-1 日期类型操作

-- 获取当前日期
select `current_date`();
-- 获取当前日期时间
select `current_timestamp`();
-- 获取unix时间（时间戳） 从1970年1月1号0时0分0秒 到现在过去了多少秒
select unix_timestamp();
​
​
-- unix时间 和日期时间的转化
-- 日期时间转为unix
select unix_timestamp('2023-10-01 15:30:28');
-- 将unix时间转为日期时间
select from_unixtime(12390886789);
​
-- 年月日的取值
select year('2023-10-01 15:30:28');
select month('2023-10-01 15:30:28');
select day('2023-10-01 15:30:28');
select dayofmonth('2023-10-12 15:30:28');
select dayofweek('2023-10-12 15:30:28');
select hour('2023-10-12 15:30:28');
select minute('2023-10-12 15:30:28');
select second('2023-10-12 15:30:28');
​
-- 时间加减
select date_add('2023-10-12 15:30:28',5);
select date_add('2023-10-12 15:30:28',-5);
​
-- 比较时间相差多少天
select datediff(`current_date`(),'2023-10-12');

1-2 类型转化

-- 字段类型不符合计算需求，可以进行类型转化
-- 隐式转化  hive会自动判断进行转化数据然后计算
select '123'+'456';
-- 手动指定转化
select cast('123' as int) + cast('456' as int);
​
select * from itcast.tb_hero;
desc itcast.tb_hero;
-- 转化只是在计算时进行，并不会改变字段本身类型
select cast(blood as bigint) from itcast.tb_hero;

1-3 字符串数据转json，array，map操作

  • josn字符串操作

    • 数据是一个 "{key:value}" 格式

    • 使用方法取值value

create table tb_order_detail(json_field string
);
​
​
select * from tb_order_detail;
-- 对字段中的json字符串数据进行取值，按照key取value值
-- 方法一  get_json_object 每次只能取一个字段数据  ，可以向下一直取值
selectget_json_object(json_field,'$.orderid') as orderid,get_json_object(json_field,'$.total_price') as total_price,get_json_object(json_field,'$.total_num') as total_num,get_json_object(json_field,'$.goods') as goods,get_json_object(json_field,'$.goods[0]') as good1,get_json_object(json_field,'$.goods[0].name') as good1_name,get_json_object(json_field,'$.goods[1]') as good2
from tb_order_detail;
​
-- json_tuple 一次取多个字段值,不能对嵌套数据往下取值
select json_tuple(json_field,'orderid','total_price','total_num','goods') as(orderid,total_price,total_num,goods) from tb_order_detail
​

将字符串数据切割转为数组数据

create table tb_user(id int,name string,hobby string
)row format delimited fields terminated by ',';
​
select id,name,split(hobby,'-') from tb_user;

将字符串数据切割转为map数据

-- 使用map方法
select `map`('name','张三','age',29);
​
create table tb_hero(id int,name string,blood int,skin string
)row format delimited fields terminated by ',';
-- 西部大镖客:288-大圣娶亲:888-全息碎片:0-至尊宝:888-地狱火:1688 --> {'西部大镖客':288,'大圣娶亲':88}
select id,name,blood,map(split(split(skin,'-')[0],":")[0], cast(split(split(skin,'-')[0],":")[1] as int),split(split(skin,'-')[1],":")[0],cast(split(split(skin,'-')[1],":")[1] as int))from tb_hero;

mysql中的内置函数操作

​
select concat(name,sex) from member;
select concat_ws(',',name,sex) from member;
select substr(s_birth,1,4) from student;
select replace(s_birth,'-','/') from student;
select round(3.1421,2);
select round(3.123);
select pow(2,3);
​
​
​
select current_timestamp;
select current_date;
select unix_timestamp();
select unix_timestamp('2023-10-10 10:10:10');
select from_unixtime(127381923);
​
select date_add('2023-10-10 10:10:10',interval 1 day);
select date_add('2023-10-10 10:10:10',interval 1 month );
select date_add('2023-10-10 10:10:10',interval 1 year );
select date_add('2023-10-10 10:10:10',interval -1 year );
select date_add('2023-10-10 10:10:10',interval -1 hour );
​
select timestampdiff(year ,'2020-02-02','2023-02-02');
select timestampdiff(month ,'2020-02-02','2023-02-02');
select timestampdiff(day ,'2020-02-02','2023-02-02');
select timestampdiff(hour ,'2020-02-02 16:23:12','2023-02-02 15:12:12');
​
​
​
​
select if(s_sex='男',1,2),s_sex from student;
select *,casewhen year(s_birth) between 1980 and 1989 then '80后'when year(s_birth) between 1990 and 1999 then '90后'end
from student;

二、DQL的查询计算(掌握)

对表进行查询计算

select 字段  from 表;
​
select 字段1,字段2,字段3,常量值,内置函数计算 from tb

2-1 单表查询计算

where 的条件过滤
select 字段1,字段2,字段3,常量值,内置函数计算 from tb where 过滤条件

过滤条件，条件成立的将对的行数据返回

  • 比较大小

    • 字段 = 数值 判断字段和数值是否相等

    • 字段 > 数值

    • 字段 < 数值

    • 字段 >= 数值

    • 字段 <= 数值

    • 字段 != 数值

-- 大小比较
-- 年龄大于19岁
select * from tb_stu where age >19;
-- 查询性别为女性的学生信息
select * from tb_stu where gender='女';
-- 查询学科不是IS的学生信息
select * from tb_stu where cls !='IS';

  • 判断空值

    • 字段 is null 字段为空

    • 字段 is not null

-- 空值判断
insert into tb_stu values(9023,null,'男',20,'MA');
select * from tb_stu where name is not null;
select * from tb_stu where name is null;
​
select * from tb_stu where name !=''; -- 空字符过滤是会将null值一起过滤掉
select * from tb_stu where name =''; -- 相等判断是,空字符是不会过滤出null值的

  • 范围判断

    • 字段 between 数值1 and 数值2

      • 字段 >=数值 and 字段 <=数值

    • 字段 in (数值1,数值2....) 字段的值等于任意一个值就返回结果

-- 范围判断
select * from tb_stu where age between 20 and 25;
select * from tb_stu where age in(19,22);
select * from tb_stu where age not in(19,22);

  • 模糊查询

    • 字段 like '% _ 数据' % 可以匹配任意多个 _ 匹配任意一个字符

    • 字段 rlink '正则表达式'

​
create table tb_stu2(id int,name string,gender string,age int,cls string,email string
)row format delimited fields terminated by ',';
​
select * from tb_stu2;
-- like的模糊查询
-- 查询姓名为刘的学生
select * from tb_stu where name like '刘%'; -- % 代表任意多个字符
-- 查询姓名为刘的学生 名字个数时2个字的
select * from tb_stu where name like '刘_';
select * from tb_stu where name like '刘__'; -- 查询三个字的
​
-- rlike 的正则表达式
-- 表的是就是通过不同的符号来表示不同的数据进行匹配
-- \\d 匹配数据的表达式   \\w  匹配字符字母  \\s 匹配空格
select * from tb_stu2;
-- ^ 表是什么开头
select * from tb_stu2 where email rlike '^\\d'; -- 表是以数字开头
select * from tb_stu2 where email rlike '^\\w';
select * from tb_stu2 where email rlike '^\\S';
​
-- ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$
​
select email,split(email,'@')[1] from tb_stu2;
select email,split(split(email,'@')[1],'\\.')[0] from tb_stu2;

  • 与或非

    • 条件1 and 条件2 and 条件3 ... 多个条件都成立，返回对应的行数据

    • 条件1 or 条件2 or 条件3 ... 多个条件满足任意一个，返回对应的行数据

-- 与 多个条件都成立
select * from  tb_stu;
-- 查询性别为男性，学科是is的
select * from  tb_stu where gender='男' and cls = 'IS';
-- 查询性别为男性或学科是is的
select * from  tb_stu where gender='男' or cls = 'IS';
聚合计算 sum，count
select * from tb_stu;
select sum(age) from tb_stu2;
select count(*) from tb_stu where name is not null;
select avg(age) from tb_stu2;
select max(age) from tb_stu;
select min(age) from tb_stu;
分组聚合 group by
select sum(age) from tb_stu group by gender;
select sum(age),gender from tb_stu group by gender;

注意分组后，select 中不能出现非分组字段

分组后过滤 having
select sum(age),gender from tb_stu group by gender having sum(age)> 200;
排序

order by 全局排序

select * from tb_stu order by age; -- 默认是升序 从小到大
select * from tb_stu order by age desc ; -- 降序 从大到小
分页 limit
-- 分页
select * from tb_stu limit 5;
select * from tb_stu limit 10,5; -- 页数 m  每页数量是n   (m-1)*n,n

2-2 多表关联查询

join的列关联

  • 内关联

    • 找关联字段相同的数据

  • 左关联

    • 展示保留左边表的所有数据，右边表有相同数据显示，没有相同数据则为null

  • 右关联

    • 展示保留右边表的所有数据，左边表有相同数据显示，没有相同数据则为null

-- table1: 员工表
CREATE TABLE employee(id int,name string,deg string,salary int,dept string) row format delimited
fields terminated by ',';
​
-- table2:员工家庭住址信息表
CREATE TABLE employee_address (id int,hno string,street string,city string
) row format delimited
fields terminated by ',';
​
-- table3:员工联系方式信息表
CREATE TABLE employee_connection (id int,phno string,email string
) row format delimited
fields terminated by ',';
-- on 当成where使用，进行条件顾虑
select * from employee t1 join  employee_address t2  on  t1.id = t2.id and salary> 30000;
select * from employee t1 left join  employee_address t2  on  t1.id = t2.id;
select * from employee t1 right join  employee_address t2  on  t1.id = t2.id;
-- 实现内关联的效果
select * from employee,employee_address where employee.id = employee_address.id;
union的行关联

将select查询计算后的结果表合并

-- union合并
select 'tb_stu',count(*) from tb_stu where name is not null
union
select 'tb_stu2', count(*) from tb_stu2 where name is not null;-- 保留重复数据
select id,name from tb_stu
union all
select id,name from tb_stu2;

三、窗口聚合

默认没有窗口函数进行计算时全表数据获取计算

根据指定的窗口范围计算数据，将计算结果单独呈现一列展示，不会因为聚合改变行数

聚合使用，取值函数，排序函数 over(partition by 分组字段  order by 排序字段  rows between 起始行 and 结束行)
rows 指定计算的行范围

3-1 聚合窗口

-- 按照性别分组统计年龄和
select sid,sname,age,gander,province,tuition,sum(age) over(partition by gander) from stu;
select sid,sname,age,gander,province,tuition,sum(age) over(partition by province,gander) from stu;
-- order by 排序后会将前面的数据进行累加
select sid,sname,age,gander,province,tuition,sum(tuition) over(partition by gander order by age) from stu;
select sid,sname,age,gander,province,tuition,avg(tuition) over(partition by gander order by age) from stu;

3-2 取值窗口

-- 向上一行取值
select empno,ename,lag(ename) over() from emp;
-- 向上两行行取值
select empno,ename,lag(ename,2) over() from emp;
-- 去不到值给默认值
select empno,ename,lag(ename,2,'itcast') over() from emp;
-- 向下一行取值
select empno,ename,lead(ename) over() from emp;
-- 向下两行行取值
select empno,ename,lead(ename,2) over() from emp;
-- 去不到值给默认值
select empno,ename,lead(ename,2,'itcast') over() from emp;-- 分组后只进行组内查找
select *,lag(ename) over(partition by job) from emp;-- 取第一个值
select *,first_value(ename)over(partition by job order by sal desc ) from emp;

3-3 排序窗口

按照指定字段排序后生成序号

-- 排序
select *,rank() over (order by sal)  as rnk, -- 如果有并列生成序号是不连续的dense_rank()  over (order by sal)  as dernk, -- 生成连续序号row_number() over (order by sal) as rw -- 生成行号
from emp;-- 查找薪资前三
select * from (
select *,dense_rank()  over (order by sal desc )  as dernk -- 生成连续序号
from emp) tb1
where dernk <=3;

这篇关于HiveSQL基础Day03的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/899234。

相关文章

零基础学习Redis(10) -- zset类型命令使用

零基础学习Redis(10) -- zset类型命令使用

zset是有序集合，内部除了存储元素外，还会存储一个score，存储在zset中的元素会按照score的大小升序排列，不同元素的score可以重复，score相同的元素会按照元素的字典序排列。 1. zset常用命令 1.1 zadd  zadd key [NX | XX] [GT | LT]   [CH] [INCR] score member [score member ...]
阅读更多...
【Linux 从基础到进阶】Ansible自动化运维工具使用

【Linux 从基础到进阶】Ansible自动化运维工具使用

Ansible自动化运维工具使用 Ansible 是一款开源的自动化运维工具，采用无代理架构（agentless），基于 SSH 连接进行管理，具有简单易用、灵活强大、可扩展性高等特点。它广泛用于服务器管理、应用部署、配置管理等任务。本文将介绍 Ansible 的安装、基本使用方法及一些实际运维场景中的应用，旨在帮助运维人员快速上手并熟练运用 Ansible。 1. Ansible的核心概念
阅读更多...
AI基础 L9 Local Search II 局部搜索

AI基础 L9 Local Search II 局部搜索

Local Beam search 对于当前的所有k个状态，生成它们的所有可能后继状态。 检查生成的后继状态中是否有任何状态是解决方案。 如果所有后继状态都不是解决方案，则从所有后继状态中选择k个最佳状态。 当达到预设的迭代次数或满足某个终止条件时，算法停止。 — Choose k successors randomly, biased towards good ones — Close
阅读更多...
音视频入门基础：WAV专题（10）——FFmpeg源码中计算WAV音频文件每个packet的pts、dts的实现

音视频入门基础：WAV专题（10）——FFmpeg源码中计算WAV音频文件每个packet的pts、dts的实现

一、引言 从文章《音视频入门基础：WAV专题（6）——通过FFprobe显示WAV音频文件每个数据包的信息》中我们可以知道，通过FFprobe命令可以打印WAV音频文件每个packet（也称为数据包或多媒体包）的信息，这些信息包含该packet的pts、dts： 打印出来的“pts”实际是AVPacket结构体中的成员变量pts，是以AVStream->time_base为单位的显
阅读更多...
C 语言基础之数组

C 语言基础之数组

文章目录 什么是数组数组变量的声明多维数组 什么是数组 数组，顾名思义，就是一组数。 假如班上有 30 个同学，让你编程统计每个人的分数，求最高分、最低分、平均分等。如果不知道数组，你只能这样写代码： int ZhangSan_score = 95;int LiSi_score = 90;......int LiuDong_score = 100;int Zhou
阅读更多...
c++基础版

c++基础版

c++基础版 Windows环境搭建第一个C++程序c++程序运行原理注释常亮字面常亮符号常亮 变量数据类型整型实型常量类型确定char类型字符串布尔类型 控制台输入随机数产生枚举定义数组数组便利 指针基础野指针空指针指针运算动态内存分配 结构体结构体默认值结构体数组结构体指针结构体指针数组函数无返回值函数和void类型地址传递函数传递数组 引用函数引用传参返回指针的正确写法函数返回数组
阅读更多...
【QT】基础入门学习

【QT】基础入门学习

文章目录 浅析Qt应用程序的主函数使用qDebug()函数常用快捷键Qt 编码风格信号槽连接模型实现方案 信号和槽的工作机制Qt对象树机制 浅析Qt应用程序的主函数 #include "mywindow.h"#include <QApplication>// 程序的入口int main(int argc, char *argv[]){// argc是命令行参数个数，argv是
阅读更多...
【MRI基础】TR 和 TE 时间概念

【MRI基础】TR 和 TE 时间概念

重复时间 (TR) 磁共振成像 (MRI) 中的 TR（重复时间，repetition time）是施加于同一切片的连续脉冲序列之间的时间间隔。具体而言，TR 是施加一个 RF（射频）脉冲与施加下一个 RF 脉冲之间的持续时间。TR 以毫秒 (ms) 为单位，主要控制后续脉冲之前的纵向弛豫程度（T1 弛豫），使其成为显著影响 MRI 中的图像对比度和信号特性的重要参数。 回声时间 (TE)
阅读更多...
Java基础回顾系列-第七天-高级编程之IO

Java基础回顾系列-第七天-高级编程之IO

Java基础回顾系列-第七天-高级编程之IO 文件操作字节流与字符流OutputStream字节输出流FileOutputStream InputStream字节输入流FileInputStream Writer字符输出流FileWriter Reader字符输入流字节流与字符流的区别转换流InputStreamReaderOutputStreamWriter 文件复制 字符编码内存操作流(
阅读更多...
Java基础回顾系列-第五天-高级编程之API类库

Java基础回顾系列-第五天-高级编程之API类库

Java基础回顾系列-第五天-高级编程之API类库 Java基础类库StringBufferStringBuilderStringCharSequence接口AutoCloseable接口RuntimeSystemCleaner对象克隆 数字操作类Math数学计算类Random随机数生成类BigInteger/BigDecimal大数字操作类 日期操作类DateSimpleDateForma
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2