Hive入门(四)：HQL查询语句（二）

最新推荐文章于 2024-04-24 18:48:08 发布

weiiL

最新推荐文章于 2024-04-24 18:48:08 发布

阅读量3.4k

点赞数

分类专栏： hive bigdata hadoop 文章标签： hive hql hadoop bigdata

本文链接：https://blog.csdn.net/m0_37324825/article/details/80489872

版权

bigdata 同时被 3 个专栏收录

11 篇文章 0 订阅

订阅专栏

hadoop

8 篇文章 0 订阅

订阅专栏

hive

7 篇文章 0 订阅

订阅专栏

相关内容：
Hive入门(一)：Hive Shell的基本操作
 Hive入门(二)：HQL数据定义
 Hive入门(三)：HQL数据操作——数据装载与保存
 Hive入门(四)：HQL查询语句（一）
Hive入门(四)：HQL查询语句（二）
开始
本篇博文主要以三张表musics，singers和languages表为例，记录HQL的一些查询语句的操作方法。主要包括：
1. 连接：join
2. 排序：sort by和order by
3. 分区：distribute by和cluster by
4. 采样：tablesample
5. 合并：union all和union

准备数据表

数据文件

music表数据music.dat

M-0001,Valder Fields,S-0001,L-0001
M-0002,A Step You Can't Take Back,S-0002,L-0002
M-0003,For You,S-0003,L-0003
M-0004,Life is like a Boat,S-0003,L-0003
M-0005,Fake Song,<unknow>,<unknow>

singer表数据singer.dat

S-0001,Tamas Wells
S-0002,Keira Knightley
S-0003,Rie fu
S-0004,YUI

language表数据language.dat

L-0001,Chinese
L-0002,English
L-0003,Japanese

创建表

创建table的语句。

create table if not exists musics(
  id string,
  name string,
  singerid string,
  languageid string)
row format delimited
fields terminated by ',';

create table if not exists singers(
  id string,
  name string)
row format delimited
fields terminated by ',';

create table if not exists languages(
  id string,
  type string)
row format delimited
fields terminated by ',';

加载表数据

向table加载数据。

hive> load data local inpath '/path/music/music.dat' into table musics;
hive> load data local inpath '/path/music/language.dat' into table languages;
hive> load data local inpath '/path/music/singer.dat' into table singers;

join

inner join

连接musics和singers表。

hive > select m.name,s.name
     > from musics m join singers s on m.singerid = s.id;
OK
m.name  s.name
Valder Fields   Tamas Wells
A Step You Can't Take Back  Keira Knightley
For You Rie fu
Life is like a Boat Rie fu
Time taken: 13.36 seconds, Fetched: 4 row(s)

连接musics，singers和languages表。执行了一个MR job。

hive> select m.name,s.name,l.type
    > from musics m join singers s on m.singerid = s.id join languages l on m.languageid = l.id;
    OK
m.name  s.name  l.type
Valder Fields   Tamas Wells Chinese
A Step You Can't Take Back  Keira Knightley English
For You Rie fu  Japanese
Life is like a Boat Rie fu  Japanese
Time taken: 14.005 seconds, Fetched: 4 row(s)

Hive在执行表连接时，会默认最后那个表最大，将前面的表缓存起来，扫描最后的表。因此为了效率，从左到右表的大小应该逐渐增加。在上述的三张表中，如果是真实的数据，显然三张表的从小到大的顺序应该是languages、singers和musics。但是演示的数据只是非常小的人造数据，因此执行不会有什么区别。
除了按序连接表，还可以通过/*streamtable(m)*/指定最大的驱动表。

hive> select /*+STREAMTABLE (m)*/ m.name,s.name,l.type
    > from musics m join singers s on m.singerid = s.id join languages l on m.languageid = l.id;
    OK
m.name  s.name  l.type
Valder Fields   Tamas Wells Chinese
A Step You Can't Take Back  Keira Knightley English
For You Rie fu  Japanese
Life is like a Boat Rie fu  Japanese
Time taken: 12.206 seconds, Fetched: 4 row(s)

left outer join

左连接。如果A left outer join B，则A中所有记录都会被返回，如果B中无对应的记录，则相应列值为NULL。如下，连接musics和singers，musics中音乐Fake Song对应的musicid为，在singers中无对应记录，所以返回的singer列为NULL。

hive> select  m.name as music, s.name as singer
    > from musics m left outer join singers s on m.singerid = s.id;
    OK
music   singer
Valder Fields   Tamas Wells
A Step You Can't Take Back  Keira Knightley
For You Rie fu
Life is like a Boat Rie fu
Fake Song   NULL
Time taken: 11.699 seconds, Fetched: 5 row(s)

如果添加了where语句，比如where singer则只返回符合where语句条件的musics的记录。比如，只选择日文歌，即languageid为L-0003。语句如下所示，其他语言的歌曲被过滤了。

hive> select  m.name as music, s.name as singer
    > from musics m left outer join singers s on m.singerid = s.id
    > where languageid = 'L-0003';
OK
music   singer
For You Rie fu
Life is like a Boat Rie fu
Time taken: 11.802 seconds, Fetched: 2 row(s)

尝试在on之后添加过滤的语句，发现输出结果如下。将条件改成m.language != ‘L-0002’，也依然输出一样的结果。没有过滤掉musics表中singerid为‘S-0002’的歌曲《A Step You Can’t Take Back》，而是使得其singers表对应的列为NULL。

hive> select  m.name as music, s.name as singer
    > from musics m left outer join singers s on m.singerid = s.id and m.singerid != 'S-0002';
OK
music   singer
Valder Fields   Tamas Wells
A Step You Can't Take Back  NULL
For You Rie fu
Life is like a Boat Rie fu
Fake Song   NULL
Time taken: 11.773 seconds, Fetched: 5 row(s)

将条件改成m.singerid = ‘S-0002’，发现除了符合条件的歌曲《A Step You Can’t Take Back》以外，其他歌曲singer列全为NULL。

hive> select  m.name as music, s.name as singer
    > from musics m left outer join singers s on m.singerid = s.id and m.singerid = 'S-0002';
OK
music   singer
Valder Fields   NULL
A Step You Can't Take Back  Keira Knightley
For You NULL
Life is like a Boat NULL
Fake Song   NULL
Time taken: 11.26 seconds, Fetched: 5 row(s)

这说明不管怎么在on部分设定条件，都不会改变返回的musics表的记录。如果要过滤表的记录，只能在where语句设置，但是where语句是在join完后才执行过滤的，实际上它是大表的过滤。如果想要在join之前就执行过滤，可以通过嵌套子查询的方式。如下：

hive> select  m.name as music, s.name as singer from
    > (select * from musics where singerid = 'S-0002') m
    > left outer join
    > (select * from singers where id = 'S-0002') s
    > on m.singerid = s.id;
OK
music   singer
A Step You Can't Take Back  Keira Knightley
Time taken: 11.818 seconds, Fetched: 1 row(s)

https://www.cnblogs.com/rocky-AGE-24/p/7154499.html

right outer join

返回join右边的表中所有符合条件的记录。左边表若无相应记录，则对应的列用NULL填充。

hive> select  m.name as music, s.name as singer
    > from musics m right outer join singers s on m.singerid = s.id;
OK
music   singer
Valder Fields   Tamas Wells
A Step You Can't Take Back  Keira Knightley
For You Rie fu
Life is like a Boat Rie fu
NULL    YUI
Time taken: 11.576 seconds, Fetched: 5 row(s)

full outer join

返回所有表中所有符合条件的记录。连接的表若无相应记录，则对应的列用NULL填充。

hive> select  m.name as music, s.name as singer
    > from musics m full outer join singers s on m.singerid = s.id;
OK
music   singer
Fake Song   NULL
Valder Fields   Tamas Wells
A Step You Can't Take Back  Keira Knightley
Life is like a Boat Rie fu
For You Rie fu
NULL    YUI

left semi join

left semi join，只会返回左边的记录，但前提是右边的表必须满足on后面定义的判定条件。比如，现在需要查询rie fu这个歌手的所有歌曲。那么可以连接musics和singers这两张表。使用left semi join可以直接将s.name=’Rie fu’的判定条件添加在on后面的部分。

hive> select  m.name as music
    > from musics m left semi join singers s on m.singerid = s.id and s.name = 'Rie fu';
OK
music
For You
Life is like a Boat
Time taken: 12.318 seconds, Fetched: 2 row(s)

如果去掉关于rie fu的判定条件，如下面的语句，查询的目的就变成了：如果musics表中某条记录的singerid存在于singers表中，那么可以返回这个记录。在sql中，这其实相当于in exists的结构，但是hive中并不支持这样的形式。left semi join比inner join更加高效，因为它只要查找到了相应的记录就可以停止扫描，因此不需要扫描整张表。

hive> select  m.name as music
    > from musics m left semi join singers s on m.singerid = s.id;
OK
music
Valder Fields
A Step You Can't Take Back
For You
Life is like a Boat
Time taken: 11.751 seconds, Fetched: 4 row(s)

其实这个on后面的语句可以理解为，如果在singers表中能找到一个记录的id和musics这条记录的singerid相同，那么这个musics记录可以返回。如果说，把on后面的语句设为s.id=’Rie fu’，因为在singers表中，存在Rie fu这位歌手，因此，这条语句一直都是true，musics表的每条记录都可以返回。

笛卡儿积join

笛卡尔积就是将左边表的记录和右边表的记录两两组合。如果左表有m条记录，右表有n条，那么将产生m*n条记录。笛卡儿积会消耗大量的时间。如果设置hive.mapred.mode=strict，Hive会阻止笛卡儿积查询。
如下是一个例子，将singers和languages进行笛卡儿积。

hive> select s.name, l.type
    > from singers s join languages l;
OK
s.name  l.type
Tamas Wells Chinese
Tamas Wells English
Tamas Wells Japanese
Keira Knightley Chinese
Keira Knightley English
Keira Knightley Japanese
Rie fu  Chinese
Rie fu  English
Rie fu  Japanese
YUI Chinese
YUI English
YUI Japanese
Time taken: 11.934 seconds, Fetched: 12 row(s)

map side join

hive.auto.convert.join=true时，可以开启map side join优化。如果没有开启，一般的join方式，会有reduce过程。但是如果进行map side join，则会将小表放在内存里，直接在map端进行连接，无需reduce过程。并不是所有小表都可以进行这个优化，小表的大小由hive.mapjoin.smalltable.filesize决定，其默认值为25000000。
在《Hive编程指南》看到，如果hive.auto.convert.join没有开启，可以通过标记的方式启动map side join。但是添加标记后依然没有启动map side join优化。不知道是不是功能已经彻底废弃了。

hive> select /*+ MAPJOIN (s) */  m.name, s.name
    > from musics m join singers s on m.singerid = s.id;

map side join的优化不支持left outer join和right outer join。对与分桶大表也可以进行支持但是大表必须是按on语句涉及到的字段进行分桶，而且一张表的桶数必须是另一张表的倍数。这个优化的开启需要设置：

hive> set hive.optimize.bucketmapjoin=true

如果涉及到的表的桶数相同，而且数据按桶的键排序，则可以执行一个更快的sort-merge join。但是还要额外设置：

hive> set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveFormat
hive> set hive.optimize.bucketmapjoin.sortedmerge=true

sort by和order by

order by是将所有的记录都按order by后的字段进行排序。sort by是将每个reduce的数据进行排序，即局部地对每个分片的数据排序，而不是全局排序。因为order by全局排序可能会消耗非常长的时间，如果hive.mapred.mode=strict，hive要求order by的语句后面必须加上limit。如下，对musics表中歌名进行降序排序。因为默认为升序asc，因此在order by的语句后加上desc表示降序排列。limit 2表示只返回排名前2的记录。

hive> select name
    > from musics
    > order by name desc;
    > limit 2;
OK
name
Valder Fields
Life is like a Boat
Time taken: 2.131 seconds, Fetched: 2 row(s)

distribute by

因为MR任务可能会包含多个reduce，数据被分成多个分片由不同的reduce处理。distributed by控制了这些map输出的数据是如何划分到reduce的。比如，需要将musics表的相同的音乐语种在一起处理，然后按语种、歌手和歌名依次排序。如果没有distribute by languageid的语句，就无法保证同样的languageid的记录被分到同一个reduce。

hive> select singerid, languageid, name
    > from musics
    > where singerid not like '%unknow%' and languageid not like '%unknow%'
    > distribute by languageid
    > sort by languageid, signerid, name;
OK
singerid    languageid  name
S-0001  L-0001  Valder Fields
S-0002  L-0002  A Step You Can't Take Back
S-0003  L-0003  For You
S-0003  L-0003  Life is like a Boat
Time taken: 2.058 seconds, Fetched: 4 row(s)

cluster by

cluster by col其实相当于distribute by col sort by col asc。col为列名。在下面的语句也就相当于distribute by languageid sort by languageid asc。当然asc是可以省略的。这里加上asc只是为了表示cluster by的结果是升序排列的。

hive> select singerid, languageid, name
    > from musics
    > where singerid not like '%unknow%' and languageid not like '%unknow%'
    > cluster by languageid;
OK
singerid    languageid  name
S-0001  L-0001  Valder Fields
S-0002  L-0002  A Step You Can't Take Back
S-0003  L-0003  Life is like a Boat
S-0003  L-0003  For You
Time taken: 2.019 seconds, Fetched: 4 row(s)

tablesample

tablesample用于对表的数据采样，但是这个采样是将数据分成多个bucket，抽取其中一个bucket。其语句如下：

TABLESAMPLE (BUCKET bucketId OUT OF bucketNum [ON colName])

其中，bucketId为抽取的bucket的编号。bucketNum表示bucket的数量。如果基于某列来分桶，colName就是该列的列名，如果要随机分桶，那么colName可以用rand()来代替。

基于rand()抽样

举一个rand()分桶的例子。如下表示，将musics表中数据随机分为5个bucket，然后抽取编号为2的bucket的数据。因为是随机分的，所以每次执行的结果都不同。

hive> select * from musics tablesample(bucket 2 out of 5 on rand()) s;
OK
s.id    s.name  s.singerid  s.languageid
M-0001  Valder Fields   S-0001  L-0001
M-0003  For You S-0003  L-0003
Time taken: 0.783 seconds, Fetched: 2 row(s)
hive> select * from musics tablesample(bucket 2 out of 5 on rand()) s;
OK
s.id    s.name  s.singerid  s.languageid
M-0002  A Step You Can't Take Back  S-0002  L-0002
Time taken: 0.22 seconds, Fetched: 1 row(s)

基于列名抽样

举一个按列名分桶抽样的例子。对musics表基于singerid分桶，如果把也算上的话，一共有4位歌手。如果分成4个buckets。依次选择各个桶的数据，如下所示。可以发现，每个桶的数据刚好是各个歌手的歌曲记录。这就是按列名singerid分桶的效果。

hive> select * from musics tablesample(bucket 1 out of 4 on singerid) s;
OK
s.id    s.name  s.singerid  s.languageid
M-0002  A Step You Can't Take Back  S-0002  L-0002
Time taken: 0.186 seconds, Fetched: 1 row(s)
hive> select * from musics tablesample(bucket 2 out of 4 on singerid) s;
OK
s.id    s.name  s.singerid  s.languageid
M-0003  For You S-0003  L-0003
M-0004  Life is like a Boat S-0003  L-0003
Time taken: 0.188 seconds, Fetched: 2 row(s)
hive> select * from musics tablesample(bucket 3 out of 4 on singerid) s;
OK
s.id    s.name  s.singerid  s.languageid
M-0005  Fake Song   <unknow>    <unknow>
Time taken: 0.204 seconds, Fetched: 1 row(s)
hive> select * from musics tablesample(bucket 4 out of 4 on singerid) s;
OK
s.id    s.name  s.singerid  s.languageid
M-0001  Valder Fields   S-0001  L-0001
Time taken: 0.193 seconds, Fetched: 1 row(s)

如果表在创建时已经使用cluster by分桶，而且tablesample指定的列正是用于分桶的列，那么在抽样时，可以只涉及到表相应的hash分区的数据，而无需扫描整张表。因为表中的数据已经按列的hash值分割到不同的分区。

基于百分比的数据块抽样

基于数据块抽样，是根据数据数据块大小的百分比进行抽样。比如输入400M，抽样百分比为25%，那么至少要抽样400*25%=100M的数据。但是，因为这个抽样的最小单元是一个HDFS数据块。一般一个HDFS数据块大小为128M，那么返回的会是128M而不是100M。
如果表的数据少于一个HDFS数据块的大小，那么会返回所有的数。如下语句采样百分比为50%，但是会返回表中所有数据。

hive> select * from musics tablesample(0.5 percent) s;

基于数据大小的数据块抽样

其实这个和基于百分比的数据块抽样类似，只是将百分比换成了数据大小。如下，抽样数据大小为10M，但是因为表数据量过小，未达到一个HDFS块大小，会输出全部的记录。

hive> select * from musics tablesample(10M) s;
OK
s.id    s.name  s.singerid  s.languageid
M-0001  Valder Fields   S-0001  L-0001
M-0002  A Step You Can't Take Back  S-0002  L-0002
M-0003  For You S-0003  L-0003
M-0004  Life is like a Boat S-0003  L-0003
M-0005  Fake Song   <unknow>    <unknow>
Time taken: 0.208 seconds, Fetched: 5 row(s)

基于行数的抽样

这种采样的行数，假设行数为r，则其会对每个split的数据都r条数据。如下例子中，因为只存在一个split，则抽取2条数据。

hive> select * from musics tablesample(2 rows) s;
OK
s.id    s.name  s.singerid  s.languageid
M-0001  Valder Fields   S-0001  L-0001
M-0002  A Step You Can't Take Back  S-0002  L-0002
Time taken: 0.255 seconds, Fetched: 2 row(s)

union和union all

union和union all都是将若干表的数据合并。但是，union会去除表数据重合的部分，但是union all会将所有数据完全保存下来。
union的语句如下，合并后数据没有重复。

hive> select *  from
    > ( select * from singers union select * from singers) s;
OK
s.id    s.name
S-0001  Tamas Wells
S-0002  Keira Knightley
S-0003  Rie fu
S-0004  YUI
Time taken: 5.384 seconds, Fetched: 4 row(s)

union all的语句如下，合并后数据是重复的。

hive> select *  from
    > ( select * from singers union all select * from singers) s;
OK
s.id    s.name
S-0001  Tamas Wells
S-0001  Tamas Wells
S-0002  Keira Knightley
S-0002  Keira Knightley
S-0003  Rie fu
S-0003  Rie fu
S-0004  YUI
S-0004  YUI
Time taken: 2.249 seconds, Fetched: 8 row(s)

参考书籍：
《Hive编程指南》

参考文章：
https://blog.csdn.net/skywalker_only/article/details/39370511——数据抽样

weiiL

关注

0
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
Hive入门(四)：HQL查询语句（二）

joinmusic表M-0001,Valder Fields,S-0001,L-0001M-0002,A Step You Can't Take Back,S-0002,L-0002M-0003,For You,S-0003,L-0003M-0004,Life is like a Boat,S-0003,L-0003M-0004,Fake Song,&amp;amp;amp;amp;lt;unknow&amp;amp;amp;amp;gt;,&amp;amp;amp;
复制链接

扫一扫