1. 首页
  2. 文章中心
  3. Hive基础

Hive第三天——Hive使用（二）（join语句）Hive第三天——Hive使用

65 阅读 0 评论
我是靠谱客的博主 体贴香菇，最近开发中收集的这篇文章主要介绍Hive第三天——Hive使用（二）（join语句）Hive第三天——Hive使用，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

文章部分参考自：https://blog.csdn.net/qq_23897391/article/details/92636509

Hive第三天——Hive使用

自己的话：黑发不知勤学早，白首方悔读书迟

每天都要保持前进，我势必要有强劲的实力，再跟明天的自己问好。

一、DML

1.插入单条数据到表中

hive> insert into table t_seq values('10','xx','beijing',28);

在这里插入图片描述

hive> select * from t_seq;

在这里插入图片描述

2.多重插入

假如有一个需求： 从test_4中筛选出不同的数据，插入另外两张表中；
（1）错误添加分区：

hive> create table test_4_st_200 like test_4;
hive> alter table test_4_st_200 add partition(condition='lt200');

在这里插入图片描述
添加分区只能是day
（2）正确添加分区插入数据（分别写入）
我们将staylong小于200的数据添加到test_4_st_200 ，day=‘lt200’，这一分区中：

hive> alter table test_4_st_200 add partition(day='lt200');

hive> insert into table test_4_st_200 partition(day='lt200')
select ip,url,staylong from test_4 where staylong<200;

hive> select * from test_4_st_200;

在这里插入图片描述
再将staylong大于200的数据添加到test_4_st_200 ，day=‘gt200’，这一分区中：

hive> insert into table test_4_st_200 partition(day='gt200')
select ip,url,staylong from test_4 where staylong>200;
hive> select * from test_4_st_200;

在这里插入图片描述
（3）一起写入
以上实现方式有一个弊端，两次筛选job，要分别启动两次mr过程，要对同一份源表数据进行两次读取。
如果使用多重插入语法，则可以避免上述弊端，提高效率：源表只要读取一次即可：

hive> from test_4
insert into table test_4_st_200 partition(day='lt200')
select ip,url,staylong where staylong<200
insert into table test_4_st_200 partition(day='gt200')
select ip,url,staylong where staylong>200;

hive> select * from test_4_st_200;

在这里插入图片描述

二、Join语句

JOIN语句可以通过任意个字段关联对多个表进行列连接，有些类似于Excel中的vlookup函数，但功能更加强大，下面我们来详细介绍。

1.内连接 INNER JOIN

当两个表有一个或多个列内容有交集时，INNER JOIN会返回两个表关联上的列的所在行的所有列，有些绕，没看懂的初学者继续往后看，看完例子就懂了……

总之先记住，内连接返回关联的表的交集，如图：

在这里插入图片描述
语法如下：

SELECT a.列名
      ,b.列名
FROM 表名1 a
INNER JOIN 表名2 b
      ON a.列名1 = b.列名1 [and a.列名2=b.列名2]...
[INNER JOIN 表名3 c
      ON a.列名1 = c.列名1 [and a.列名2=c.列名2]...]
...
[WHERE ...
GROUP BY ...
ORDER BY ...];

注意：

  • 用ON连接两个表内容有交集的列；
  • 当有多个列时用and增加关联条件；
  • 由于实际的表名都较长，所以一般会给关联的两个表起较为简单的别名，比如a、b、c等，这样后面在关联时可以用别名代替表名。否则需要写成ON 表名1.列名1 = 表名2.列名1这种格式；
  • 只支持等值连接，即ON后面只能有等式，否则会报错；
  • 可以进行任意个表的关联，在后面加JOIN即可；
  • INNER关键字可以省略，JOIN等同于INNER JOIN；
  • SELECT、WHERE、GROUP BY、ORDER BY等语句后的列名如果是一个表中独有的列名，可以不指定表名或表别名，即不加a.、b.，如果是两个表中都有的列名，则必须指定表名或表别名，否则会报错。不过我建议大家所有的列名都进行表名指定，这样一来防止报错，二来可以提高代码的可读性；
  • 进行多表关联查询时最好给代码加注释，进一步提高代码可读性。

2. 外连接之 LEFT OUTER JOIN

（1）语法

当两个表有一个或多个列内容有交集时，LEFT OUTER JOIN返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。也有些绕，没看懂的初学者继续往后看，看完例子就懂了……

总之先记住：LEFT JOIN产生表1的完全集，而2表中匹配的则有值，没有匹配的则以null值取代。关系如下图：
在这里插入图片描述
语法则和内连接完全一致，只是将INNER JOIN换成LEFT OUTER JOIN即可，其中OUTER同样可以省略，简写为LEFT JOIN。

​with a AS (查询1)
    ,b AS (查询2)
    ,c AS (查询3)
...
SELECT a.列名
      ,b.列名
FROM a
LEFT JOIN b
      ON a.列名1 = b.列名1 [and a.列名2=b.列名2]...
[LEFT JOIN  c
      ON a.列名1 = c.列名1 [and a.列名2=c.列名2]...]
...
[WHERE ...
GROUP BY ...
ORDER BY ...];
（2）举例

从左表中去掉和右表有交集的部分
需求：

数据展现形式：日期、平台、老用户数（当日活跃用户去掉当日新增用户）

日期：20190101-20190102

WITH a
AS (
      --20190101-20190102的平台、活跃用户
      SELECT date_8
            ,platform
            ,user_id
      FROM app.t_od_use_cnt
      WHERE date_8 BETWEEN 20190101 AND 20190102
            AND is_active = 1
      GROUP BY date_8
            ,platform
            ,user_id
      )
      ,b
AS (
      --20190101-20190103的平台、新增用户
      SELECT date_8
            ,platform
            ,user_id
      FROM app.t_od_new_user
      WHERE date_8 BETWEEN 20190101 AND 20190102
      GROUP BY date_8
            ,platform
            ,user_id
      )
      --20190101-20190102的平台、版本、老用户数（当日活跃用户去掉当日新增用户）
SELECT a.date_8
      ,a.platform
      ,count(distinct a.user_id) old_user_num
FROM a
LEFT JOIN b
      ON a.date_8 = b.date_8
            AND a.platform = b.platform
            AND a.user_id = b.user_id
WHERE b.user_id is NULL 
GROUP BY a.date_8
      ,a.platform;

这个脚本逻辑的关键就是最后查询中的 WHERE b.user_id is NULL，因为是左关联，所以先取了左表的全部，这时用WHERE条件限定右表为空，也就是左右表没有关联上的部分。这样就实现了从左表中抛掉左右表重合部分的需求。
运行结果如下：

a.date_8	a.platform	old_user_num
20190101	1	371
20190101	2	352
20190102	1	369
20190102	2	363

3.外连接之 RIGHT OUTER JOIN

语法

RIGHT OUTER JOIN返回右表的全部行和左表满足ON条件的行，如果右表的行在左表中没有匹配，那么这一行左表中对应数据用NULL代替。同样的OUTER在这里可以省略。其实说白了就是把LEFT JOIN的左表右表交换下顺序，然后换成RIGHT JOIN 。所以我个人习惯全都使用LEFT JOIN，RIGHT JOIN在工作中从来没有使用过。由于它的用法和LEFT JOIN完全一致，所以这里就不再举例了。

RIGHT JOIN产生表2的完全集，而1表中匹配的则有值，没有匹配的则以null值取代。关系如下图：
在这里插入图片描述

left outer join 与left join 结果一样
right outer join 与 right join结果一样

4. 外连接之 FULL OUTER JOIN（全连接）

语法

FULL OUTER JOIN 会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替。同理，这里的OUTER 也可以省略。

FULL JOIN产生1和2的并集。但是需要注意的是，对于没有匹配的记录，则会以null做为值。

关系如下图：

在这里插入图片描述
FULL JOIN的应用场景很少

5.总结

下面列出了四种JOIN 类型之间的差异：

  • JOIN: 如果表中有至少一个匹配，则返回行；
  • LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行；
  • RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行；
  • FULL JOIN: 只要其中一个表中存在匹配，就左表和右表所有的行返回行。

最后

以上就是体贴香菇为你收集整理的Hive第三天——Hive使用（二）（join语句）Hive第三天——Hive使用的全部内容，希望文章能够帮你解决Hive第三天——Hive使用（二）（join语句）Hive第三天——Hive使用所遇到的程序开发问题。

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

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

相关文章

Hive sql 七天内连续3天活跃sql查询
Hive sql 七天内连续3天活跃sql查询
Hive计算最大连续登陆天数
Hive计算最大连续登陆天数
Hive SQL写出连续三天都登录的用户题目：建表及数据准备
Hive SQL写出连续三天都登录的用户题目：建表及数据准备
Spark——Spark/Hive SQL计算获取连续登陆N天及以上的用户问题实现方式
Spark——Spark/Hive SQL计算获取连续登陆N天及以上的用户问题实现方式
Hive第三天——Hive使用（二）（join语句）Hive第三天——Hive使用
Hive第三天——Hive使用（二）（join语句）Hive第三天——Hive使用
hive-蚂蚁金服笔试题（2）
hive-蚂蚁金服笔试题（2）
spark ,hive collect_list全局保持顺序Hive中collect_list全局保持顺序
spark ,hive collect_list全局保持顺序Hive中collect_list全局保持顺序
hive sql求连续登陆
hive sql求连续登陆

评论列表共有 0 条评论

立即
投稿
返回
顶部