Hive的表操作4

Hive系列

注：大家觉得博客好的话，别忘了点赞收藏呀，本人每周都会更新关于人工智能和大数据相关的内容，内容多为原创，Python Java Scala SQL 代码，CV NLP 推荐系统等，Spark Flink Kafka Hbase Hive Flume等等~写的都是纯干货，各种顶会的论文解读，一起进步。
今天继续和大家分享一下Hive的表操作
#博学谷IT学习技术支持

---

---

前言

1、Hive是数仓管理工具,用来管理数仓
2、Hive可以将数仓存在HDFS上的文件变成一张张的表
3、Hive提供一种HiveSQL可以表进行分析处理
4、HiveSQL底层默认是MapReduce，以后可以换成其他的引擎（Spark），我们写HiveSQL会去匹配底层的MR模板，匹配上则执行，否则不能执行

---

一、内置函数

1.数学函数

-- 四舍五入函数
select round(3.1415926,4); -- 四舍五入 保留4位小数 3.1416

-- 获取 [1,100]之间的随机数
select `floor`(rand() * 100) + 1; 

-- 向下取整
select floor(2.8999); -- 2

2.字符串函数

-- 字符串拼接
select concat(rand(),'-',sid) as sid, sname from student;
select concat(rand(),'-',sid) as sid, sname from student;


-- 字符串拼接，带分隔符
select concat_ws('-','2022','10','15');
select log10(100)


-- 字符串截取
select substr('2022-12-23 10:13:45',1,4); -- 2022
select substr('2022-12-23 10:13:45',6,6); -- 12


-- 字符串替换
select regexp_replace('foobar', 'oo|ar', '');


-- 解析URL
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'PATH');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY','k1');
select parse_url('http://www.facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY','k2');


-- 字符串切割
select split('2022-12-23','-');

3.日期函数

-- 获取当前的时间
select `current_date`(); -- 2022-10-25

-- 通过unix_timestamp和from_unixtime两个日期函，将不规则的时间格式转换成规则的格式
select from_unixtime(unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss'),'yyyy-MM-dd HH:mm:ss');

-- 将日期转为指定的格式
select date_format('2022-1-1 1:1:1','yyyy-MM-dd HH:mm:ss') -- 2022-01-01 01:01:01


select to_date('2022-01-01 01:01:01'); -- 年月日
select year('2022-01-01 01:01:01');  -- 年
select month('2022-01-01 01:01:01'); -- 月
select day('2022-01-01 01:01:01');   -- 日
select hour('2022-01-01 01:01:01');  -- 小时
select minute('2022-01-01 01:01:01'); -- 分钟
select second('2022-01-01 01:01:01'); -- 秒

select quarter('2022-10-15 01:01:01');      -- 季度
select weekofyear('2022-10-15 01:01:01');   -- 获取今年的第几周
select `dayofweek`('2022-10-16 01:01:01');  -- 获取今天是周几(1-7)

select date_add('2022-10-15',10); --  将日期向后推10天
select date_add('2022-10-15',-10);--  将日期向前推10天
select date_sub('2022-10-15',10); --   将日期向前推10天

select abs(datediff('2022-02-13','2022-10-15')); -- 求日期之间的差值

二、 条件函数

1.If 语句

-- 标记每个学生的及格和不及格的情况
select *, if(sscore >= 60,'及格','不及格') as flag from score;

-- 同时计算及格和不及格的平均分
select avg(if(sscore>=60,sscore,null)) as avg1,avg(if(sscore<60,sscore,null)) as avg2 from score

2.Case When 语句

方式1

/*
 订单号      金额         支付方式
 order_id amount payment_type    payment_name
 1001       50     1              支付宝支付
 1002       80     2              微信支付
 1003       60     3              余额支付
 1004       40     4              货到付款
 */

select
    case payment_type
        when 1
            then '支付宝支付'
        when 2
            then '微信支付'
        when 3
            then '余额支付'
        else '货到付款'
    end as payment_name;

方式2，方式2经常用到。

select
    *,
    case when sscore >= 90 and sscore <= 100
            then '优秀'
        when  sscore >= 80
            then '良好'
        when sscore >= 60
            then '一般'
        else
             '及格'
    end as flag
from score;

三、 类型降转函数

select cast(12.35 as int);
select cast('123' as int);
select cast('2020-12-05' as date);

四、 行转列

-- 1、准备数据
20	SMITH   
30	ALLEN   
30	WARD    
20	JONES   
30	MARTIN  
30	BLAKE   
10	CLARK   
20	SCOTT   
10	KING    
30	TURNER  
20	ADAMS   
30	JAMES   
20	FORD    
10	MILLER  

-- 2、创建表
create table emp(
deptno int,
ename string
) row format delimited fields terminated by '\t';

-- 3、加载数据
load data local inpath "/export/data/hivedatas/emp.txt" into table emp;

-- 4、实现功能

-- collect_set(去重)/collect_list(不去重) 可以将每一组的ename存入一个集合中（set集合，list集合）
select deptno, collect_list(ename) from emp group by deptno;

select deptno,concat_ws("|",collect_set(ename)) as ems from emp group by deptno;
 
select deptno, collect_set(ename)[0] from emp group by deptno; -- 用索引访问

---

总结

今天继续和大家分享一下Hive的表操作4。