Hive数据连接与函数（2）

xiuxiuhh

已于 2023-06-10 09:12:11 修改

阅读量418

点赞数

文章标签： hive 数据仓库 json

于 2023-05-05 21:31:55 首次发布

本文链接：https://blog.csdn.net/xiuxiuhh/article/details/130509062

版权

1 数据连接

内连接：保留左表和右表连接成功的数据信息,连接未成功则不保留该数据

select * from hive_day03.orders as o join users u on o.userid = u.userid;

左连接：保留左表中全部的数据记录,与右表中与左表连接成功的记录

select * from hive_day03.users u right join orders o on o.userid = u.userid;

右连接：保留右表中全部的数据记录,与左表中与右表连接成功的记录

select * from hive_day03.orders o left join users u on o.userid = u.userid;

左半连接：执行内连接,保留左右两个表匹配成功的数据记录, 但是仅返回左表的数据信息

左半连接，由于获取的数据量较小，在内存中占用的空间就小，数据移动速度快，计算效率会有所提高；相比于内连接,效率一定会提高,数据量越大提升越明显

-- 左半连接:执行内连接保留左边
select * from hive_day03.orders o left semi join users u on o.userid = u.userid;

全连接：左表和右表中的数据全部保留,未连接成功的部分补充null值即可

select * from hive_day03.orders o full join users u on o.userid = u.userid;

交叉连接：笛卡尔积, 左表的数据依次与右表的每一条数据进行匹配无论是否成功都保留,不足位置补充null

select * from hive_day03.orders o cross join users u on o.userid = u.userid;

2 hive数据查询

桶内查询

hive中的排序方式有两种:

全局排序: order by 将所有的数据查询出来,从头到尾的排序
桶内排序: cluster by / sort by 将数据按桶进行提取,以桶为单位,在桶的内部进行排序

思考: 全局排序效率高,还是桶内排序效率高?

桶内排序效率高, 在现实开发中我们一般避免全局排序,如果非要全局排序,一般会增加limit关键字限制范围

查询时分桶排序

注意:一定先修改分桶数量,后续操作才能有分桶效果set mapreduce.job.reduces = 3;

查询时分桶排序,其实是在查询过程中根据规则重新计算一遍分桶,将数据放入不同的虚拟桶中,对于原数据无影响
select 触发的任何指令都不会修改原来的数据内容
读取分桶表数据时仅需要一个reduce任务, 因为桶已经分好,不需要重新计算
分桶排序时,需要多个reduce任务,因为需要重新计算有多少个reduce任务就分为多少个桶

-- 创建表时进行桶内排序规则的指定
create table hive_day03.students_sort(
    s_id int,
    s_name string,
    gender string,
    age int,
    course string
)clustered by (s_id) sorted by (s_id) into 3 buckets
row format delimited fields terminated by ',';
-- 表中加载数据
load data local inpath '/root/hive_data/students.txt' into table hive_day03.students_sort;
-- 查看表数据
select * from hive_day03.students_sort;
-- 查询时分桶并排序，不一定非要查询分桶表
-- 此时没有设定reduce task 的数量，进行分桶排序后，进行分桶排序后，只能按照一个桶进排序
-- select * from hive_day03.students cluster by s_id;
--  如果要指定分桶的数量，则需要修改reducetask数量
-- mapreduce.job.reduce 参数默认值为-1，根据情况自行判断reducetask数量
-- 当值为-1时，向分桶表中插入数据，会自动开启与分桶数量相同的reductask
-- 当值为-1时，按照分桶规则读取数据时，会始终开启1个reductask
-- 修改reduce任务数量可以按照该数量进行分桶
set mapreduce.job.reduce=3;
select * from hive_day03.students cluster by s_id;
select * from hive_day03.students_sort cluster by s_id;

-- cluster by 可以进行分桶也可以进行排序，但是分桶和排序必须是同一个字段，且该字段排序仅可以升序
-- 如果想要根据不同的字段进行分桶排序，或者降序排序，该如何？
-- distribute by 分桶 + sort by 排序
select * from hive_day03.students distribute by s_id sort by age desc ;

基本查询

Hive中使用基本查询SELECT、WHERE、GROUP BY、聚合函数、HAVING、JOIN和普通的SQL语句几乎没有区别
hive的JOIN查询和排序查询相比普通sql新增了部分功能

RLIKE 正则匹配

什么是正则表达式
正则表达式就是一种规则的集合。按照一定的规则去判断字符串是否符合该规则的表达式
RLIKE的作用
可以基于正则表达式，对数据内容进行匹配

使用like时匹配效率低且不灵活，一般都会使用rlike通过正则表达式进行匹配

-- 找到 xx 省 xx 市 xx 区的数据
-- like
select * from hive_day03.orders where useraddress like '__省 __市 __区';
-- rlike
select * from hive_day03.orders where useraddress rlike '.{2}省 .{2}市 .{2}区';

-- 找到上海市和北京市的所有订单
-- like
select * from hive_day03.orders where useraddress like '%上海%' or useraddress like '%北京%';
-- rlike
select * from hive_day03.orders where useraddress rlike '.*上海|北京.*';

-- 查找姓张、王、邓的用户
-- like
select * from hive_day03.orders where username like '张%' or username like '王%' or username like '邓%';
-- rlike 在正则匹配中如果需要使用\，一定要使用\\否则不转义
select * from hive_day03.orders where username rlike '[张王邓]\\S{2}';

UNION联合

UNION关键字的作用是？
将多个SELECT的结果集合并成一个
多个SELECT的结果集需要架构一致，否则无法合并
自带去重效果，如果无需去重，需要使用 UNION ALL
UNION用在何处：可以用在任何需要SELECT发挥的地方（包括子查询、ISNERT SELECT等）

-- 将周杰轮和林均街的数据合并到一起纵向连接 5条数据
select * from hive_day03.course where s_name='周杰轮'
union
select * from hive_day03.course where s_name='林均街';
-- 将周杰轮和林均街的数据合并到一起纵向连接  4条数据 union会自动去重,union 等价于  union distinct

注意

union 等价于union distinct 在纵向合并时会自动去重

-- 结果4条数据 union会自动去重,union 等价于  union distinct
select * from hive_day03.course where s_name='周杰轮'
union
select * from hive_day03.course where s_name='周杰轮';
-- 若不希望自动去重应该用 union all；结果8条数据
select * from hive_day03.course where s_name='周杰轮'
union all
select * from hive_day03.course where s_name='周杰轮';

union纵向合并后会根据自己的规则重排数据顺序,不是上边就在上边下边就在下边
union 联合查询时上下两个表的列数必须相同

select id,c_name from hive_day03.course where s_name='周杰轮'
union
select * from hive_day03.course where s_name='周杰轮';

提示报错：org.apache.hadoop.hive.ql.parse.SemanticException:Schema of both sides of union should match.
并集两边的模式应该匹配，上下两个表查询字段数量不同所以报错。

union 联合查询时上下两张表同列字段类型必须相同

select id,c_name from hive_day03.course where s_name='周杰轮'
union all
select c_name,id from hive_day03.course where s_name='周杰轮';

提示报错：org.apache.hadoop.hive.ql.parse.SemanticException:3:22 Schema of both sides of union should match: Column id is of type int on first table and type string on second table. Error encountered near token 'course'，
列id在第一个表上的类型为int，在第二个表上为string。上下两张表同列字段类型不同所以报错

Sampling采样（了解）

为什么需要抽样？
大数据体系下，表内容一般偏大，小操作也要很久
所以如果想要简单看看数据，可以通过抽样快速查看
TABLESAMPLE函数的使用
桶抽样方式，TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))，
推荐使用，完全随机，速度略慢块抽样，使用分桶表可以加速
Virtual Columns 虚拟列
语法：SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))
y 表示将表数据随机划分成y份（y个桶），从总共的桶里一共取出几桶数据。eg: 一共100条数据分成10桶 y=4 我们一共获取多少条采样数据? 10 * 10/4 = 25条
x 表示从y里面随机抽取x份数据作为取样，从第几桶数据开始采样。eg: 一共10桶数据 x=2 y=5 一共取 10/5 2桶数据, 次数从2 7桶
colname 表示随机的依据基于某个列的值
rand() 表示随机的依据基于整行

总共抽取的数据=总桶数 / y * 每个桶的数量 x
在开发中每个桶的数据量不一样多, 所以在使用时不一定获取的数据就是均分数据,而是一个范围

-------------------------------Sampling采样----------------------------
-- 数据准备 分五个桶
create table hive_day03.students_buck_5(
    s_id int,
    s_name string,
    gender string,
    age int,
    course string
)clustered by (s_id) sorted by (s_id) into 5 buckets
row format delimited fields terminated by ',';
-- 表中加载数据
load data local inpath '/root/hive_data/students.txt' into table hive_day03.students_buck_5;
-- 查看表数据
select * from hive_day03.students_buck_5;
-- 分桶采样，根据指定字段采样
select * from hive_day03.students_buck_5 tablesample ( bucket 1 out of 5 on s_id);
select * from hive_day03.students_buck_5 tablesample ( bucket 1 out of 2 on s_id);
select * from hive_day03.students_buck_5 tablesample ( bucket 2 out of 5 on gender);
-- 随机采样,推荐使用,随机采样数据代表性更强
select * from hive_day03.students_buck_5 tablesample ( bucket 1 out of 2 on rand());

virtual columns 虚拟列(了解）

实际上不存在,但是hive可以帮我们映射出来的列,就是虚拟列

select *,
    INPUT__FILE__NAME,  -- 显示映射该数据记录的文件所在位置
    BLOCK__OFFSET__INSIDE__FILE -- 显示行偏移量
from hive_day03.course;
-- 如果要使用 ROW__OFFSET__INSIDE__BLOCK 必须先开启一下配置 用的极少
set hive.exec.rowoffset = true;
SELECT * ,INPUT__FILE__NAME, --显示映射该数据记录的文件所在位置
       BLOCK__OFFSET__INSIDE__FILE,--显示行偏移量
       ROW__OFFSET__INSIDE__BLOCK   -- 显示块偏移量,但是如果仅有一个块,则都是0
FROM hive_day03.course;

3 HIVE函数

Hive的函数共计有上百种，挑选练习一些常用的，更多详细的函数使用可以参阅：官方文档(https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-MathematicalFunctions)

什么是函数?

将一些特定的功能封装起来,当我们想要使用该功能的时候,通过特定的字符组合集合调用这种方式就是函数。eg: sum 代表的是求和功能, count 代表的是计数功能

Hive的函数分为两大类：内置函数（Built-in Functions）、用户定义函数（User-Defined Functions）：

3.1 用户自定义函数（User-Defined Functions）

自己定义封装的功能就是自定义函数

本来，UDF 、UDAF、UDTF3个标准是针对用户自定义函数分类的；但是，现在可以将这个分类标准扩大到hive中所有的函数，包括内置函数和自定义函数；
1、UDF（User-Defined-Function）普通函数: 一进一出输入一行数据输出一行数据
eg: split、hash、round、rand、split
2、UDAF (User-Defined Aggregation Function）聚合函数: 多进一出输入多行输出一行
eg: count、sum、max、min、avg、lead、lag
3、UDTF（User-Defined Table-Generating Functions）高阶函数也称表生成函数: 一进多出输入一行输出多行。eg: explode、join_touple…

最开始UDF,UDAF,UDTF是对于自定义函数的分类规则,后来逐步推广到所有的函数中

3.2 内置函数（Built-in Functions）

系统定义好的,我们可以直接通过sql指令调用的功能就是内置函数
eg：查看函数的使用方法

 -- 查看所有的内置函数
show functions ;
-- 查看函数的使用方法
-- 详细查询
desc function extended round;

在这里插入图片描述
字符串相关函数

-- 1.conncat(字符串1,字符串2...)连接多个字符串
select concat('hello','world!');
select concat('hello ','world!!!','And ','hive')

-- 2.concat_ws()连接多个字符串并使用指定分隔符连接
-- 查询函数使用方法
desc function extended concat_ws;
-- 尝试使用
select concat_ws('&&' ,'hive','hello','hhh');

-- 3.length查询字符串的长度
select length('hive');
select char_length('hello world');
select character_length('hello world');

-- 4.upper和lower大小写转换
select upper('hello world');
select lower('HELLO world');

-- 5.trim去除字符串左右两侧的空白
select  '   hello   ';
select length('   hello   ');
select trim('   hello   ');
select length(trim('   hello   '));

-- 6.split字符串拆分,结果是一个数组类型数据
-- 格式:split(被拆分的字符串,分隔符)
select split('hello,world,!!!,and,hive',',');
select split('hello,world,!!!,and,hive',',')[2];

-- 7.substr获取字符串中的一部分信息
-- 格式:subste(被截取的字符串,开始截取的索引位置,截取的数据长度)
select substr('海燕呀,你可长点心吧',0,3);
-- 注意,此函数截取数据时索引查询从1开始计数
select substr('海燕呀,你可长点心吧',7,3);   --长点心
-- 完全等价
select substring('海燕呀,你可长点心吧',7,3);   --长点心

-- 8.regexp_replace 正则替换 D非数值;d数值
select  regexp_replace('100-200','\\d+','num');

-- 9.解析URL
desc function extended parse_url;

select parse_url('http://node1:9870/explorer.html#/', 'HOST');
select parse_url('http://node1:9870/explorer.html#/', 'PATH');
select parse_url('https://mp.csdn.net/mp_blog/manage/article?spm=3001.5298', 'QUERY');
select parse_url('https://mp.csdn.net/mp_blog/manage/article?username=zhangsan&age=10', 'QUERY', 'username');

-- 10.get_json_object解析json数据
-- json数据学名:JAVASCRIP对象简谱
-- 主要负责数据流传输工作,一般开发中前后的交互或者服务器间的交互使用最多的就是JSON数据
-- json 数据就类似于 array 和  map 的嵌套
/*
json_str:{"name": "小明",
	"age": 18,
	"hobby": ["eat","drink","play"],
	"score": {"Chinese": 18,"Math": 21,"English": 33}
}
 */
-- 获取学员姓名
select get_json_object('{
	"name": "小明",
	"age": 18,
	"hobby": [
		"eat",
		"drink",
		"play"
	],
	"score": {
		"Chinese": 18,
		"Math": 21,
		"English": 33
	}
}', '$.name');

-- 获取该学员的第二个兴趣爱好
select get_json_object('{
	"name": "小明",
	"age": 18,
	"hobby": [
		"eat",
		"drink",
		"play"
	],
	"score": {
		"Chinese": 18,
		"Math": 21,
		"English": 33
	}
}', '$.hobby[1]');

-- 获取 学员的英语成绩
select get_json_object('{
	"name": "小明",
	"age": 18,
	"hobby": [
		"eat",
		"drink",
		"play"
	],
	"score": {
		"Chinese": 18,
		"Math": 21,
		"English": 33
	}
}', '$.score.English');

json格式转换器：https://www.bejson.com/

时间函数

-- 1.查看当前日期
select current_date(); --获取当前日期
select `current_timestamp`(); --获取当前时间
-- 2.to_date 将数据转换为日期字符串 格式需要遵循yyyy-mm-dd
select to_date('2022年5月1日'); --数据格式不正确会返回null
select to_date('2022-05-01');
select to_date('2022-05-01 19:59:59');
select to_date('2022-05-01 23');

-- 3.获取时间类型数据的一部分维度
select year('2022-05-01 19:59:59');
select quarter('2022-05-01 19:59:59');--季度
select month('2022-05-01 19:59:59');
select weekofyear('2022-05-01 19:59:59'); --一年的第几周
select `dayofweek`('2022-05-01 19:59:59'); --计算周几,周日是第一天
select day ('2022-05-01 19:59:59');  -- 当月第几天
select hour ('2022-05-01 19:59:59');
select minute ('2022-05-01 19:59:59');
select second ('2022-05-01 19:59:59');

-- 4.datediff获取两个日期间相差几天
-- 时间差=前面日期-后面日期
select datediff('2022-05-01 19:59:59',`current_timestamp`());

-- 5.时间偏移
-- 指定日期加一天
select date_add(`current_date`(),1);
select date_add(`current_date`(),-1);
-- 指定日期减一天
select date_sub(`current_date`(),1);
select date_sub(`current_date`(),-1);

-- 6.将时间转换为时间戳
-- 将时间转换为时间戳
-- 时间戳:从1970.1.1 0点到现在的毫秒值
select `current_timestamp`();
select unix_timestamp(); --当前时间戳
select unix_timestamp('2022-05-01 19:59:59'); --获取指定时间的时间戳
 
-- 7.将时间戳转换为时间类型数据
select from_unixtime(1651435199);

-- 8.时间类型格式化
select date_format('2022-05-01 19:59:59','yyyy年MM月dd日');
select date_format('2022-05-01 19:59:59','yyyy年MM月dd日 HH时mm分ss秒');
select date_format(`current_timestamp`(),'yyyy年MM月dd日 HH时mm分ss秒');

数学函数

-- 1.保留N位小鼠,并四舍五入
select round(3.1415926,3); --3.142
select round(3.14,3); --3.140
select round(2.57,5); --2.57000
-- 2.rand()获取从0到1的随机数
select rand();
-- rand()中填写固定值,则每次获取的数据都是相同,这个数字被称为随机数种子
select rand(10000);