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 每次只能取一个字段数据  ，可以向下一直取值
select
    get_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 *,
       case
           when 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;