HIVE 函数

最新推荐文章于 2024-09-23 15:31:05 发布

zhangyuhang88

最新推荐文章于 2024-09-23 15:31:05 发布

阅读量162

点赞数

文章标签： hive

本文链接：https://blog.csdn.net/zhangyuhang88/article/details/126098224

版权

本文详细介绍了Hive中的系统函数查询、空值替换、分支控制以及行转列和列转行等操作。通过示例展示了如何使用showfunctions查询函数，使用nvl和coalesce处理空值，用if和case语句进行条件判断，以及利用collect_set和explode进行数据转换。此外，还演示了如何利用split和lateral view实现列转行。

摘要由CSDN通过智能技术生成

HIVE 函数

系统自带函数的查询和使用

-- 查询所有系统函数
show functions;

-- 查询包含特定关键字的函数
show functions like "*date*";

-- 查询特定函数的使用方法
desc function 'current_date';
-- 查询特定函数使用的更详细的帮助
desc function extended 'current_date';

中国人可以查百度

空值替换

-- 两个输入
-- nvl(col, default_value) 如果col不为null，返回col，否则返回default_value
select ename, job, nvl(job, "没工作") from emp;

-- 多个输入
-- coalesce(col1, col2, col3, ....) 从左到右找第一个不为null的值
select ename, job, sal, coalesce(job, sal, '啥也没有') from emp;

分支控制

-- 数据准备
create table emp_sex(
    name string,     --姓名
    dept_id string, --部门id
    sex string       --性别
) 
row format delimited fields terminated by "\t";

load data local inpath '/opt/module/hive/datas/emp_sex.txt' 
into table emp_sex;

-- if (boolean, result1, result2) 如果boolean为真，返回result1，否则返回result2
-- 统计emp_sex表各部门男女的人数
SELECT dept_id, 
       count(if(sex='男', name, null)) male,
       count(if(sex='女', name, null)) female
from emp_sex
group by dept_id;

-- case col 
--   when value1 then result1 
--   when value2 then result2 
--   else result3 
--   end
-- 如果col值为value1，返回result1；如果值为value2，返回result2；否则返回result3

-- case when
--   boolean1 then result1
--   boolean2 then result2
--   else result3
--   end
-- 如果boolean1为真，返回result1；如果boolean1为假，boolean2为真，返回result2；否则返回result3

-- 统计emp_sex表各部门男女的人数
SELECT dept_id, 
       count(case sex when '男' then name else null end) male,
       count(case when sex='女' then name else null end) female
from emp_sex
group by dept_id;

行转列和列转行

行转列

-- 数据准备
create table person_info(
    name string,            -- 姓名
    constellation string, -- 星座
    blood_type string      -- 血缘
) 
row format delimited fields terminated by "\t";

load data local inpath "/opt/module/hive/datas/constellation.txt" 
into table person_info;

-- collect_set(col) collect_list(col)
-- 这两个函数都是聚合函数, 将属于同一组的col的值聚合成一个数组，set会去重，list不去重
select constellation, 
       blood_type,
       collect_list(name) names
from person_info
group by constellation, blood_type;

-- 字符串拼接
-- concat(v1, v2, v3, ...) 将输入的多列拼成一列字符串输出v1v2v3...
-- concat_ws(sep, array|v1, v2, v3, ...) 将数组内的多个元素拼成字符串，按照sep分隔
select concat(constellation, ',', blood_type) xzxx,
       concat_ws('|', collect_list(name)) names
from person_info
group by constellation, blood_type;

列转行

-- 数据准备
create table movie_info(
    movie string,     --电影名称
    category string   --电影分类
) 
row format delimited fields terminated by "\t";

load data local inpath "/opt/module/hive/datas/movie.txt" 
into table movie_info;

-- explode(array|map) UDTF函数，可以将一行输入变成多行多列
-- 如果数据的参数是array，结果只有一列；如果输入的参数是map，结果有key，value两列

-- split(str, sep) 将str按照sep分成字符串数组
select explode(split(category, ',')) from movie_info;

-- 列转行：lateral view
-- 将原表和UDTF结合查询
select m.movie,
       tbl.category_id
from movie_info m
lateral view explode(split(category, ',')) tbl as category_id;