1. 首页
  2. 文章中心
  3. Other

Hive----关联查询 排序 系统函数 分桶表 行列转换 窗口函数

54 阅读 0 评论
我是靠谱客的博主 羞涩小猫咪，最近开发中收集的这篇文章主要介绍Hive----关联查询 排序 系统函数 分桶表 行列转换 窗口函数，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一.关联查询

  1. join on --可以多表连接
  2. left join on --左连接
  3. right join on --右连接
  4. left semi on --根据连接的条件返回主表的内容 right不能和semi一块使用,只能左连 (类似于子查询)
  5. union all --联合查询,连接两个查询的结果集 要求字段个数和数据类型一致 all(不会去重)
  6. union --去重 (union all 和 union返回结果是两个表的结合)
  7. full join --两个表的拼接(包括null值)
--join的多表连接第一种方式
a
join
b
join
c
join
d
on
a.id=b.id and b.id=c.id and
c.id=d.id ;
--join的多表连接第二种方式
a
join
b
on
a.id = b.id)t
join
c
on
t.id = c.id)t2
join
d
on
t2.id = d.id

--left semi on
返回主表的内容 right不能和semi一块使用,只能左连
select
*
from
tb_x
left semi join
--右连接不可以用semi
tb_y
on tb_x.id = tb_y.id ;

--union all联合查询,连接两个查询的结果集 要求字段个数和数据类型一致 all(不会去重)
select
*
from
tb_a
where id >=2
union
all
select
*
from
tb_a
where id<=2;

--union去重
select
*
from
tb_y
union
select
*
from
tb_y ;

--错误示范 列数不同
select
* , name as neme2
-- 3列
from
tb_x
where id >=2
union
all
select
*
--2列
不能union
from
tb_y
where id<=2;

--full join
两个表的拼接(包括null值)
select
*
from
tb_a
full join
tb_b
on
tb_a.id=tb_b.id;

二.排序

--在执行SQL的时候默认是一个reducetesk 
set mapreduce.job.reduces=n;
-- 配置reduce的个数 
set mapreduce.job.reduces;
-- 查看配置结果
  1. order by --全局最终结果排序
select * from tb_a order by id
--不写默认升序(asc)
select * from tb_a order by id desc
--倒序
  1. distribute by --指定分区字段 分区
    与sort结合使用
select * from tb_x distribute by name; --指定分区字段
分区
  1. sort by --区内数据排序
select *
from tb_x
distribute by
name
sort by name
desc; --和distribute by联合使用的时候sort在后
  1. cluster by --当分区字段和排序字段相同 并且是升序的时候使用cluster by 替换 distribute by sort by
select *
from tb_x
distribute by
name
sort by name
desc;
select *
from tb_x
cluster by
name ; --使用cluster代替distrubute和sort,只能升序使用

三.系统函数详解

  1. array(ele1,ele2…) --创建数组 数组是hive中的一种集合类型 和 java中的数组一样
select array(1,2,3,4) ;
select array(log_id,url,ct) from tb_log;
select array(lig_id,url,ct)[0] from tb_log; --索引0id的数组
  1. array_contains(arr,element) --给我一个数组,判断element在arr中存不存在,返回true或false
select array_contains(array(1,2,3,4) , 1);
--存在true
select array_contains(array(1,2,3,4) , 5);
--不存在false
  1. upper() --小写转大写
select upper('a,b,c') ;
--A,B,C
  1. lower() --大写转小写
select lower('A,B,C') ;
--a,b,c
  1. split(str , sqe) --切割
select split('hello_tom_jim_city','_');
--["hello","tom","jim","city"]
select split('hello_tom_jim_city','_')[0]
--索引0 hello
  1. trim() --去除首尾空格
select trim('
hello
');
--两边空格没有了
(hello)
  1. uuid() --生成一个随机的字符串
select uuid();
-- 67e80de6-5fd6-40af-87e2-bbd6e16ec81d
  1. replace(字符串 , 要替换掉的子串 , 替换掉的新的子串) --替换字符串
select('hello' , 'll' , 'LL');
--hello-->heLLo
select(uuid() , '-', ' ');
--5a461055 3889 4443 8663 0bc9ac7a7792
-替换成了空格
  1. substr(str , 起始位置(偏移量) [, 长度]) --偏移量从1开始包括开始 长度可省略
    substring(str , 起始位置(偏移量) [, 长度]) --作用和substr一样,用哪个都可以
    substring_index(str , ‘分隔符’ , 长度) --指定一个分隔符,取一定的长度返回 ,长度不可省略
select substr('hello' , 2);
--ello
select substr('hello' , 2 , 3)
-- ell
select substring_index('a-b-c' , '-' , 1);
--a
select substring_index('a-b-c' , '-' , 2);
--a-b
a JSS
b JSS-XZS
c JSS-XZS-FX
--表连接的时候可以用substring_index来切割
  1. select trunc(‘2020-12-03’,‘MM’); --当前月的第一天
    select trunc(‘2020-12-03’,‘Q’); --当前季度的第一天
    select trunc(‘2020-12-03’,‘YEAR’); --当前年的第一天
    select date_sub(‘2020-12-03’,1); --当前天减一天
    select date_add(‘2020-12-03’,1); --当前天加一天
    select datediff(‘2020-12-03’,‘2020-11-03’); --两天相差的天数
select trunc('2020-12-03','MM'); --当前月的第一天
select trunc('2020-12-03','Q');
--当前季度的第一天
select trunc('2020-12-03','YEAR');
--当前年的第一天
select date_sub('2020-12-03',1);
--当前天减一天
select date_add('2020-12-03',1);
--当前天加一天
select datediff('2020-12-03','2020-11-03'); --两天相差的天数
  1. json_tuple --解析Bean
select
json_tuple(data , 'movie' , 'rate' , 'timeStamp','uid')
--顺序一致
as (mid , rate , ctime ,uid) --别名
from
tb_m ;

四.分桶表和抽样查询
分区表是将数据分文件夹管理 , 减少数据扫描的文件范围 直接从对应文件夹中读取数据
分桶表是对join 对查询的优化 将数据按照指定的字段的规则分文件

--分桶步骤
1 创建普通表
导入数据
create table tb_stu(
id int,
name string)
row format delimited fields terminated by 't';
load data local inpath "/doit19/fentong/" into
table tb_stu;
2 创建分桶表
create table buck_stu(
id int,
name string)
clustered by(id) --分桶字段
into 3 buckets
--分3个桶
row format delimited fields terminated by 't';
3 开启分桶功能
set hive.enforce.bucketing=true;
-- 开启分桶
set mapreduce.job.reduces=-1;
4
使用insert
into的方式导入数据 到 分桶表中
insert into table buck_stu
select id, name from tb_stu;
5.抽样查询
select * from
buck_stu tablesample(bucket 1 out of 3 on id);
--查询3个分桶中的第1个数据
select * from
buck_stu tablesample(bucket 2 out of 3 on id);
--查询3个分桶中的第2个数据
select * from
buck_stu tablesample(bucket 3 out of 3 on id);
--查询3个分桶中的第3个数据

五.行转列 将多行的数据合并到一列中
collect_set – 去重
collect_list --list不去重 这两个都是聚合函数 将收集的多行数据聚集成一个数组集合
concat 拼接 --参数是可变参数 拼接字符串
concat_ws --参数一 拼接符 参数二 可变个数的字符串 数组

慧慧	处女座	B
老娜	射手座	A
奔奔	处女座	B
gangge	白羊座	A
taoge	射手座	A

collect_set – 去重

select
collect_set(name)
from
tb_teacher
group by xz,xx;
+-----------------+
| ["慧慧","奔奔"]
|
| ["老娜","taoge"] |
| ["gangge"]
|
+-----------------+

collect_list --list不去重 这两个都是聚合函数 将收集的多行数据聚集成一个数组集合

select
collect_list(name)
--多行数据聚集到一个数组
from
tb_teacher
group by xz,xx;
+-----------------+
| ["慧慧","奔奔"]
|
| ["老娜","taoge"] |
| ["gangge"]
|
+-----------------+

concat(str1,str2…strN) 拼接 --参数是可变参数 拼接字符串

select concat('a','b','c');
--abc
select concat('a' , ',' , 'b' , 'c');
--a,b,c
select
concat(xz ,"," , xx) ,
--把星座和血型用','拼接起来
collect_list(name)
from
tb_teacher
group by
xz, xx
;
+--------+-----------------+
| 处女座,B
| ["慧慧","奔奔"] |
| 射手座,A
| ["老娜","taoge"] |
| 白羊座,A
| ["gangge"]
|
+--------+-----------------+

concat_ws(分隔符 , [string | array(string)] +) --参数一 拼接符 参数二 可变个数的字符串 数组

select concat_ws(',' , 'a','b','c');
--a,b,c
select
concat(xz ,"," , xx) as xax,
concat_ws("|",collect_list(name)) as names
--把数组以 | 分割
from
tb_teacher
group by
xz, xx
;
+--------+-----------+
|
xax
|
names
|
+--------+-----------+
| 处女座,B
| 慧慧|奔奔 |
| 射手座,A
| 老娜|taoge|
| 白羊座,A
| gangge
|
+--------+-----------+

六.列转行
explode --炸裂函数
lateral view --侧窗口函数 类似于隐式join

《八佰》
战争,动作,抗日,剧情
《姜子牙》	动画,神话,科幻,动作,伦理
《战狼2》	战争,动作,灾难

--explode:炸裂函数
select
explode(split(leixing,','))
-- 将切割好的数据炸裂开
from
tb_movie2;
+------+
| col
|
+------+
| 战争
|
| 动作
|
| 抗日
|
| 剧情
|
| 动画
|
| 神话
|
| 科幻
|
| 动作
|
| 伦理
|
| 战争
|
| 动作
|
| 灾难
|
+------+
select
name,
lx
from
tb_movie2
lateral view
--侧窗口函数,连接两表
explode(split(leixing,',')) t
as
lx ;
--t是虚拟表的别名
lx是虚拟表字段的别名
+--------+-----+
|
name
| lx
|
+--------+-----+
| 《八佰》
| 战争
|
| 《八佰》
| 动作
|
| 《八佰》
| 抗日
|
| 《八佰》
| 剧情
|
| 《姜子牙》
| 动画
|
| 《姜子牙》
| 神话
|
| 《姜子牙》
| 科幻
|
| 《姜子牙》
| 动作
|
| 《姜子牙》
| 伦理
|
| 《战狼2》
| 战争
|
| 《战狼2》
| 动作
|
| 《战狼2》
| 灾难
|
+--------+-----+

七.窗口函数
over(partition by 字段) --窗口函数 数据窗口的大小可能会随着行的变化而变化
在进行分组聚合以后,如果还想操作集合以前的数据 就可以使用窗口函数

select
* ,
count(1) over(partition by name),
--指定前面聚合函数窗口的大小

sum(money) over(partition by ctime)
from
--指定窗口可以与前面的函数不一致,尽量不要指定多个窗口
tb_orders

over(partition by name order by ctime) --同时可以区内排序

select
*,
sum(money) over(partition by name order by ctime)
--区内排序
from
tb_orders

rows between unbounded preceding and current row --起始行到当前行
46 = 46
46+55=101
46+55+10=111
46+55+10+42=156

select
*,
sum(money) over(partition by name rows between unbounded preceding and current row)
from
tb_orders;
+-----------------+------------------+------------------+---------------+
| tb_orders.name
| tb_orders.ctime
| tb_orders.money
| sum_window_0
|
+-----------------+------------------+------------------+---------------+
| jack
| 2020-01-05
| 46.0
| 46.0
|
| jack
| 2020-01-08
| 55.0
| 101.0
|
| jack
| 2020-01-01
| 10.0
| 111.0
|
| jack
| 2020-04-06
| 42.0
| 153.0
|
| jack
| 2020-02-03
| 23.0
| 176.0
|
| mart
| 2020-04-13
| 94.0
| 94.0
|
| mart
| 2020-04-11
| 75.0
| 169.0
|
| mart
| 2020-04-09
| 68.0
| 237.0
|
| mart
| 2020-04-08
| 62.0
| 299.0
|
| neil
| 2020-05-10
| 12.0
| 12.0
|
| neil
| 2020-06-12
| 80.0
| 92.0
|
| tony
| 2020-01-04
| 29.0
| 29.0
|
| tony
| 2020-01-02
| 15.0
| 44.0
|
| tony
| 2020-01-07
| 50.0
| 94.0
|
+-----------------+------------------+------------------+---------------+

rows between 1 preceding and current row --上一行和当前行
10=10
10+46=56
46+55=101
55+23=78
23+42=65

select
*,
sum(money) over(partition by name order by ctime rows between 1 preceding and current row)
from
tb_orders;
+-----------------+------------------+------------------+---------------+
| tb_orders.name
| tb_orders.ctime
| tb_orders.money
| sum_window_0
|
+-----------------+------------------+------------------+---------------+
| jack
| 2020-01-01
| 10.0
| 10.0
|
| jack
| 2020-01-05
| 46.0
| 56.0
|
| jack
| 2020-01-08
| 55.0
| 101.0
|
| jack
| 2020-02-03
| 23.0
| 78.0
|
| jack
| 2020-04-06
| 42.0
| 65.0
|
| mart
| 2020-04-08
| 62.0
| 62.0
|
| mart
| 2020-04-09
| 68.0
| 130.0
|
| mart
| 2020-04-11
| 75.0
| 143.0
|
| mart
| 2020-04-13
| 94.0
| 169.0
|
| neil
| 2020-05-10
| 12.0
| 12.0
|
| neil
| 2020-06-12
| 80.0
| 92.0
|
| tony
| 2020-01-02
| 15.0
| 15.0
|
| tony
| 2020-01-04
| 29.0
| 44.0
|
| tony
| 2020-01-07
| 50.0
| 79.0
|
+-----------------+------------------+------------------+---------------+

rows between 1 preceding and 1 following --上一行 当前行 和下一行
10+46=56
10+46+55=111
10+46+55+23=124

select
*,
sum(money) over(partition by name order by ctime rows between 1 preceding and 1 following)
from
tb_orders;
+-----------------+------------------+------------------+---------------+
| tb_orders.name
| tb_orders.ctime
| tb_orders.money
| sum_window_0
|
+-----------------+------------------+------------------+---------------+
| jack
| 2020-01-01
| 10.0
| 56.0
|
| jack
| 2020-01-05
| 46.0
| 111.0
|
| jack
| 2020-01-08
| 55.0
| 124.0
|
| jack
| 2020-02-03
| 23.0
| 120.0
|
| jack
| 2020-04-06
| 42.0
| 65.0
|
| mart
| 2020-04-08
| 62.0
| 130.0
|
| mart
| 2020-04-09
| 68.0
| 205.0
|
| mart
| 2020-04-11
| 75.0
| 237.0
|
| mart
| 2020-04-13
| 94.0
| 169.0
|
| neil
| 2020-05-10
| 12.0
| 92.0
|
| neil
| 2020-06-12
| 80.0
| 92.0
|
| tony
| 2020-01-02
| 15.0
| 44.0
|
| tony
| 2020-01-04
| 29.0
| 94.0
|
| tony
| 2020-01-07
| 50.0
| 79.0
|
+-----------------+------------------+------------------+---------------+

lag(col,n) --往前第n行数据 lag 参数一 字段 n行 (通俗讲是将上一行数据移到下一行来)
lag(col ,n,[可省略 默认是null,可随意,])

select
*,
lag(ctime , 1) over(partition by name order by ctime)
from
tb_orders
+-----------------+------------------+------------------+---------------+
| tb_orders.name
| tb_orders.ctime
| tb_orders.money
| lag_window_0
|
+-----------------+------------------+------------------+---------------+
| jack
| 2020-01-01
| 10.0
| 我的第一次
|
| jack
| 2020-01-05
| 46.0
| 2020-01-01
|
| jack
| 2020-01-08
| 55.0
| 2020-01-05
|
| jack
| 2020-02-03
| 23.0
| 2020-01-08
|
| jack
| 2020-04-06
| 42.0
| 2020-02-03
|
| mart
| 2020-04-08
| 62.0
| 我的第一次
|
| mart
| 2020-04-09
| 68.0
| 2020-04-08
|
| mart
| 2020-04-11
| 75.0
| 2020-04-09
|
| mart
| 2020-04-13
| 94.0
| 2020-04-11
|
| neil
| 2020-05-10
| 12.0
| 我的第一次
|
| neil
| 2020-06-12
| 80.0
| 2020-05-10
|
| tony
| 2020-01-02
| 15.0
| 我的第一次
|
| tony
| 2020-01-04
| 29.0
| 2020-01-02
|
| tony
| 2020-01-07
| 50.0
| 2020-01-04
|
+-----------------+------------------+------------------+---------------
--lag(ctime , 3 , '我的第一次')
+-----------------+------------------+------------------+---------------+
| tb_orders.name
| tb_orders.ctime
| tb_orders.money
| lag_window_0
|
+-----------------+------------------+------------------+---------------+
| jack
| 2020-01-01
| 10.0
| 我的第一次
|
| jack
| 2020-01-05
| 46.0
| 我的第一次
|
| jack
| 2020-01-08
| 55.0
| 我的第一次
|
| jack
| 2020-02-03
| 23.0
| 2020-01-01
|
| jack
| 2020-04-06
| 42.0
| 2020-01-05
|
| mart
| 2020-04-08
| 62.0
| 我的第一次
|
| mart
| 2020-04-09
| 68.0
| 我的第一次
|
| mart
| 2020-04-11
| 75.0
| 我的第一次
|
| mart
| 2020-04-13
| 94.0
| 2020-04-08
|
| neil
| 2020-05-10
| 12.0
| 我的第一次
|
| neil
| 2020-06-12
| 80.0
| 我的第一次
|
| tony
| 2020-01-02
| 15.0
| 我的第一次
|
| tony
| 2020-01-04
| 29.0
| 我的第一次
|
| tony
| 2020-01-07
| 50.0
| 我的第一次
|
+-----------------+------------------+------------------+---------------+

lead(col ,n) --往后第n行数据 lag 参数一字段 n行

select
*,
lead(ctime , 1 , '我的最后一次') over(partition by name order by ctime)
from
tb_orders
+-----------------+------------------+------------------+----------------+
| tb_orders.name
| tb_orders.ctime
| tb_orders.money
| lead_window_0
|
+-----------------+------------------+------------------+----------------+
| jack
| 2020-01-01
| 10.0
| 2020-01-05
|
| jack
| 2020-01-05
| 46.0
| 2020-01-08
|
| jack
| 2020-01-08
| 55.0
| 2020-02-03
|
| jack
| 2020-02-03
| 23.0
| 2020-04-06
|
| jack
| 2020-04-06
| 42.0
| 我的最后一次
|
| mart
| 2020-04-08
| 62.0
| 2020-04-09
|
| mart
| 2020-04-09
| 68.0
| 2020-04-11
|
| mart
| 2020-04-11
| 75.0
| 2020-04-13
|
| mart
| 2020-04-13
| 94.0
| 我的最后一次
|
| neil
| 2020-05-10
| 12.0
| 2020-06-12
|
| neil
| 2020-06-12
| 80.0
| 我的最后一次
|
| tony
| 2020-01-02
| 15.0
| 2020-01-04
|
| tony
| 2020-01-04
| 29.0
| 2020-01-07
|
| tony
| 2020-01-07
| 50.0
| 我的最后一次
|
+-----------------+------------------+------------------+----------------+

ntile(n) --数据分组n组 , 各个组有编号，编号从1开始 n必须为int类型 列如分5组就是20%

select
*,
ntile(5) over(order by ctime) as num
from
tb_orders
+-----------------+------------------+------------------+------+
| tb_orders.name
| tb_orders.ctime
| tb_orders.money
| num
|
+-----------------+------------------+------------------+------+
| jack
| 2020-01-01
| 10.0
| 1
|
| tony
| 2020-01-02
| 15.0
| 1
|
| tony
| 2020-01-04
| 29.0
| 1
|
| jack
| 2020-01-05
| 46.0
| 2
|
| tony
| 2020-01-07
| 50.0
| 2
|
| jack
| 2020-01-08
| 55.0
| 2
|
| jack
| 2020-02-03
| 23.0
| 3
|
| jack
| 2020-04-06
| 42.0
| 3
|
| mart
| 2020-04-08
| 62.0
| 3
|
| mart
| 2020-04-09
| 68.0
| 4
|
| mart
| 2020-04-11
| 75.0
| 4
|
| mart
| 2020-04-13
| 94.0
| 4
|
| neil
| 2020-05-10
| 12.0
| 5
|
| neil
| 2020-06-12
| 80.0
| 5
|
+-----------------+------------------+------------------+------+

最后

以上就是羞涩小猫咪为你收集整理的Hive----关联查询 排序 系统函数 分桶表 行列转换 窗口函数的全部内容，希望文章能够帮你解决Hive----关联查询 排序 系统函数 分桶表 行列转换 窗口函数所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(36)

相关文章

mysql和hive实现关联查询
mysql和hive实现关联查询
十一.SparkSQL之mysql和hive关联表操作
十一.SparkSQL之mysql和hive关联表操作
HIVE SQL 关联查找总结
HIVE SQL 关联查找总结
Hive的三种连接查询方式
Hive的三种连接查询方式
Hive----关联查询 排序 系统函数 分桶表 行列转换 窗口函数
Hive----关联查询 排序 系统函数 分桶表 行列转换 窗口函数
hive与mysql关联_Hive安装以及和Mysql关联
hive与mysql关联_Hive安装以及和Mysql关联
Hadoop笔记之八——Hive安装及关联mysql 1、下载hive压缩包 2、解压hive 3、安装mysql4、Hive关联mysql5、HDFS配置 6、简单使用：
Hadoop笔记之八——Hive安装及关联mysql 1、下载hive压缩包 2、解压hive 3、安装mysql4、Hive关联mysql5、HDFS配置 6、简单使用：
【Hive六】Hive调优小结Hive调优
【Hive六】Hive调优小结Hive调优

评论列表共有 0 条评论

立即
投稿
返回
顶部