day06-Hive函数补充

最新推荐文章于 2024-10-08 20:59:45 发布

萌神想

最新推荐文章于 2024-10-08 20:59:45 发布

阅读量938

点赞数 27

文章标签： hive hadoop 数据仓库 linux 数据库大数据 sql

本文链接：https://blog.csdn.net/sxwwudi/article/details/142265848

版权

day06-Hive函数补充

一、内置函数

使用函数对字段数据进行处理计算

不同类型的字段数据有不同的处理函数

1-1 字符串函数

字符串拼接

concat
不能指定拼接字符

concat_ws
可以指定拼接字符

select name,gender,concat(name,gender),concat_ws(':',name,gender) from tb_stu6;

在这里插入图片描述

1-2 字符串切割、截取

字符串切割

-- 字符串切割
select split('name:张三',':');

在这里插入图片描述

字符串截取

-- 字符串截取
-- 下标，截取长度（不写则会读到结尾）
select substr('donghualigongdaxue',3,6);

在这里插入图片描述

去除空格

-- 去除空格
select trim('            nihao');

在这里插入图片描述

计算长度

-- 计算长度
select length('donghualigong');

在这里插入图片描述

字符串替换

-- 字符串替换
-- 原始字符串 , 待替换字符 , 替换字符
select replace('2020-10-10','-','/');

在这里插入图片描述

基本运算（加减乘除，取余）

-- as只是换名而已
select 10+5 as jiafa,
10-5 as jianfa,
10*5 as chengfa,
10/5 as chufa,
10%5 as quyu;

在这里插入图片描述

保留小数个数

-- 保留小数个数
select round(3.1415,2),round(3.1415,3);

在这里插入图片描述

取整

-- 取整
select round(3.1415);

在这里插入图片描述

向上取整 ceil
向下取整 floor

-- 向上取整,向下取整
select ceil(3.14),floor(3.14);

在这里插入图片描述

次方计算

-- 次方计算
select pow(2,3);

在这里插入图片描述

1-3 日期时间函数

获取当前日期函数

select current_date();
select current_timestamp();
-- 获取当前日期，当前日期时间
select current_date(),current_timestamp();

在这里插入图片描述

获取当前时间戳
- 从1970-01-01 00：00：00 到现在过去了多少秒

-- 获取当前时间戳
select unix_timestamp();

在这里插入图片描述

日期时间和时间戳之间的转化

-- 日期时间转时间戳
-- 日期时间小于 1970-01-01 00：00：00 时会有负数
select unix_timestamp('1960-01-01 00:00:00'),unix_timestamp('2000-01-01 00:00:00');

在这里插入图片描述

-- 时间戳转为日期时间
select from_unixtime(23154665),from_unixtime(5165156151);

在这里插入图片描述

日期时间的取值

-- 日期时间的取值   日期
select year('2024-9-14 08:43:50');
select month('2024-9-14 08:43:50');
select day('2024-9-14 08:43:50');
select date('2024-9-14 08:43:50');

select year('2024-9-14 08:43:50'),month('2024-9-14 08:43:50'),day('2024-9-14 08:43:50'),date('2024-9-14 08:43:50');

在这里插入图片描述

-- 日期时间的取值   时间
select hour('2024-9-14 08:43:50');
select minute('2024-9-14 08:43:50');
select second('2024-9-14 08:43:50');
-- 一年中的第几周
select weekofyear('2024-9-14 08:43:50');

select hour('2024-9-14 08:43:50'),minute('2024-9-14 08:43:50'),second('2024-9-14 08:43:50'),weekofyear('2024-9-14 08:43:50');

在这里插入图片描述

日期比较

-- 计算两个日期之间相差多少天
select datediff('2023-9-14','2021-10-10');

在这里插入图片描述

日期加减

-- 日期加减
select date_add('2024-9-14',20);
select date_add('2024-9-14',-20);

select date_add('2024-9-14',20),date_add('2024-9-14',-20);

在这里插入图片描述

1-4 条件判断

if 条件判断
- 只能返回两种情况的数据

-- if格式
if(判断条件1 and 判断条件2,条件成立返回的结果,条件失败返回结果)

-- 条件判断
select name,gender,if(gender='男',1,2) from tb_stu7;

在这里插入图片描述

case … when
- 可以返回多种情况的结果

-- 格式
case 
	when 判断条件1 then 结果1
	when 判断条件2 then 结果2
	when 判断条件3 then 结果3
	else 结果4
end as 自定义表名

-- 对成绩划分 - 优良中差
select
    s.s_id,stu.s_name,s.s_score,
    case
        when s_score >=80 then '优'
        when s_score >=70 then '良'
        when s_score >=60 then '中'
        else '差'
    end as data
from score s
join student stu on s.s_id = stu.s_id;

在这里插入图片描述

获取当前在哪个时间段

-- 时间段划分
-- 凌晨：零点到日出前
-- 早晨：日出到9点
-- 上午：9点到11点
-- 中午（又称正午）：11点到1点
-- 下午（包括正午）：12点到日落
-- 傍晚：5:30到7:30
-- 晚上：日落后到12:00
SELECT
    CASE
        WHEN hour(current_timestamp()) = 0 THEN '凌晨'
        WHEN hour(current_timestamp()) >= 1 AND hour(current_timestamp()) < 6 THEN '凌晨'
        WHEN hour(current_timestamp()) >= 6 AND hour(current_timestamp()) < 9 THEN '早晨'
        WHEN hour(current_timestamp()) >= 9 AND hour(current_timestamp()) < 11 THEN '上午'
        WHEN hour(current_timestamp()) >= 11 AND hour(current_timestamp()) <12 THEN '中午'
        WHEN hour(current_timestamp()) >= 12 AND hour(current_timestamp()) < 17 THEN '下午'
        WHEN hour(current_timestamp()) >= 17 AND hour(current_timestamp()) < 18 THEN '傍晚'
        WHEN hour(current_timestamp()) = 18 THEN
            CASE
                WHEN minute(current_timestamp()) < 30 THEN '傍晚'
                ELSE '晚上'
            END
        WHEN hour(current_timestamp()) >= 19 THEN '晚上'
        ELSE '无效时间'  -- 用于处理不在任何时间段内的情况
    END AS current_time_period
LIMIT 1;  -- 使用 LIMIT 1 来只获取一条数据

在这里插入图片描述

二、窗口函数

2-1 基本内容

指定计算的数据范围

格式
- 可以配合聚合函数，查找函数，序号函数一起使用
- 关键字是 over
- 一旦使用窗口函数，就不能和 group by 一起使用
- over 参数可以选择使用

聚合函数 over(partition by 分组字段 order by 排序字段 rows 窗口范围)

基本使用

-- 求所有学生的平均成绩 (下图为没使用over参数的结果)
-- 使用窗口函数  窗口计算的结果可以单独一列展示
select id,name,age,gender,avg(age) over() as avg_data  from tb_stu8;

在这里插入图片描述

partition by
- 可以指定分组字段，进行分组计算
- 也可以指定多个分组字段

-- 不同性别分组后，会按照新的组（这里是男、女两组）进行平均值计算
select id,name,age,gender,avg(age) over(partition by gender) as avg_data  from tb_stu8;

在这里插入图片描述

-- 按照多个组（性别和cls），分别计算每个组的平均值
select id,name,age,gender,avg(age) over(partition by gender,cls) as avg_data  from tb_stu8;

在这里插入图片描述

order by
- 指定排序，默认升序（从小到大）
  - 自带一个窗口计算范围，从当前行（计算数据展示的那一行）开始，向上无限制
  - 注意分组字段，下图为 age 字段，累加时按照 age 列计算，结果展示在 sum_data

-- 根据性别分组，按照id进行排序展示
select id,name,age,gender,sum(age) over(partition by gender order by id desc) as sum_data  from tb_stu8;

在这里插入图片描述

2-2 查找取值函数

可以通过取值函数获取指定行数位置id信息

lead 向上取值，默认时向下一行
lead （字段，指定行数，A）
A 表示指定如果取不到值时使用默认值（你自己设置一个）

lag 向下取值，默认时向上一行
lag （字段，指定行数，A）
A 表示指定如果取不到值时使用默认值（你自己设置一个）

-- 通过取值函数获取指定行数位置
select name,
    -- 向下取值（默认从向下一行）
    lead(name) over () as lead_data,
    -- 向下取两行数据
    lead(name,2) over () as lead_data1,
    -- 指定默认值
    lead(name,2,'ecut') over() as lead_data3,
    -- 向上取值
    lag(name) over () as lag_data,
    -- 向上两行取值
    lag(name,2) over () as lag_data1,
    lag(name,2,'ecut') over () as lag_data3
from tb_stu7;

在这里插入图片描述

2-3 生成序号函数

会对数据先进行排序，然后生成序号

rank

生成不连续序号，序号取决于变动数据时的序号

dense_rank

生成连续序号，数据相同的序号相同
可以用来进行排名

row_number

生成从小到大的序号

-- 生成序号函数
select age,
       rank() over (order by age) as rk,
       dense_rank() over (order by age) as de_rk,
       row_number() over (order by age) as rn
from tb_stu8;

在这里插入图片描述

三、CTE语法

类似子查询，可以将一个查询结果定义成一个临时表（SQL计算完成表会被清除）使用

使用CTE语法实现分布计算

格式

with 表名称 as (查询语句1),
	 表名称 as (查询语句2),
	 ...
select * from 名称

查询学生总成绩前3名的学生

-- 查询学生总成绩前3
with tb1 as (
    -- 第一 计算每个学生的总成绩
    select t1.*,
           s_score,
           sum(s_score) over (partition by t1.s_id) as total_score
    from student t1
             left join score t2 on t1.s_id = t2.s_id),
    tb2 as(
        -- 第二步 按照总成绩进行排序生成序号,对总成绩降序
        select *,dense_rank() over (order by total_score desc ) as rk from tb1
    )
-- 第三步 取出前三名的序号
select * from tb2 where rk <=3;

在这里插入图片描述

四、Hive设置

对hive进行设置，修改hive的服务属性

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

使用set设置hive服务

# 设置本地运行
set hive.exec.mode.local.auto=true;
set hive.exec.mode.local.auto.input.files.max=1;

hive 在在执行计算时，会向 yarn 申请计算字段，申请过车比较耗费时间，会造成计算时间较长

可以设置本地计算，就不需要向 yarn 申请资源

如果使用本地计算需要的本地的资源较多，本地值的是hive服务运行的服务器

没有设置本地计算耗费时间

在这里插入图片描述

设置使用本地计算

set hive.exec.mode.local.auto=true;
set hive.exec.mode.local.auto.input.files.max=1;

在这里插入图片描述

注意一：设置本地计算时要保证本地计算的服务器资源充足

在这里插入图片描述

注意二：hive的set设置只对当前终端有效，一旦关闭当前终端，在重新连接时，设置就会失效

五、补充service2启动失败

启动hive时，必要的两个服务启动时出现问题

在这里插入图片描述

我这里启动了 metastore 和 hiveserver2

可以看到 jps 查询的时候，出现了两个 RunJar

在这里插入图片描述

当我以为启动成功的时候

DataGrip 报错了，显示没有连接上 hive

在这里插入图片描述

可能原因是，上次启动的时候，没有正常关闭 hiveserver2 服务

这时候查询 hiveserver2 的情况（此时我在 hadoop 文件的 bin 目录内）

hdfs dfsadmin -safemode get

在这里插入图片描述

解决方法：关闭安全模式即可

hdfs dfsadmin -safemode leave

之后，杀死 hive 的两个服务

在这里插入图片描述

最后，再启动两个服务就okk了

在这里插入图片描述

萌神想

关注

27
点赞
踩
23

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫