请选择 进入手机版 | 继续访问电脑版

数仓工具—Hive语法之with as和from (4)

[复制链接]
科达工艺 发表于 2021-1-2 12:15:17 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
with as 和 from

with as

在我们先容hive 的时候我们说到了hive 不止实现了尺度的SQL语法,还扩展了许多其特有的语法,还允许用户自界说函数,本日我们就来学习一个hive 的一个扩展语法,with…as也叫做子查询部分,语句允许hive界说一个sql片断,供整个sql使用,这里的使用不但仅指的是像视图一样简化你的SQL 书写,而且还会将这个片断产生的效果集生存在内存中,后续的sql均可以访问这个效果集,左右有点像物化视图大概是暂时表
hive 可以通过with查询来提高查询性能,因为先通过with语法将数据查询到内存,然后背面别的查询可以直接使用
  1. with q1 as (select * from src where key= '5'),q2 as (select * from srcs2 where key = '4')select * from q1 union all select * from q2;
复制代码
实在这个语法它长处比力多
语法规则

  1. with temp as (    select * from table)select * from temp1;
复制代码
固然你也可以同时界说几个,然后使用逗号分隔就可以
  1. with temp1 as (    select * from xxx),temp2 as (    select * from xxx)select * from temp1,temp2;
复制代码
with...as还支持嵌套
  1. with temp2 as (    with temp1 as (        select * from xxx    )    select * from temp1)select * from temp2;
复制代码
固然这种用的也不多,主要照旧前面第一种和第二种
with...as只能在一条sql中使用使用
  1. with temp1 as (    select * from xxx)select * from temp1;select xxx from temp1;
复制代码
这样会报表找不到。
案例

我有一张表,表结构如下
  1. +--------------+------------+----------+|   col_name   | data_type  | comment  |+--------------+------------+----------+| user_id      | bigint     |          || item_id      | bigint     |          || category_id  | bigint     |          || behavior     | string     |          || ts           | int        |          |+--------------+------------+----------+
复制代码
behavior 代表的实用户行为(buy,cart,fav,pv), ts 是时间戳
我想要盘算buy的用户数, cart 的纪录数目,我们看一下平常的写法
  1. select behavior,count(distinct user_id) as userCnt from  user_behavior where behavior='buy' group by behaviorunion all select behavior,count(1) as userCnt from user_behavior where behavior='cart' group by behavior;
复制代码

  1. with tmp as (select user_id,behavior from user_behavior where behavior='buy' or  behavior='cart')select behavior,count(distinct user_id) as userCnt from  tmp where behavior='buy' group by behaviorunion all select behavior,count(1) as userCnt from tmp where behavior='cart' group by behavior;
复制代码

from

hive 可以通过with查询来提高查询性能,因为先通过with语法将数据查询到内存,然后背面别的查询可以直接使用,from 也有雷同的用法,比起with 更加简便,但是背面的子的SQL 必须是insert 才可以,就像下面这样
  1. from         (select user_id,behavior from user_behavior where behavior='buy' or  behavior='cart')tmpINSERT OVERWRITE TABLE ods_dest_data select behavior,count(distinct user_id) as userCnt where behavior='buy' group by behaviorINSERT OVERWRITE TABLE ods_dest_data2 select behavior,count(1) as userCnt  where behavior='cart' group by behavior;
复制代码

所以说如果我们的场景就是一次查询多次插入的话,照旧很有用的
  1. from         ods.user_behaviortmpINSERT OVERWRITE TABLE ods_dest_data select behavior,count(distinct user_id) as userCnt where behavior='buy' group by behaviorINSERT OVERWRITE TABLE ods_dest_data2 select behavior,count(1) as userCnt  where behavior='cart' group by behavior;
复制代码
你甚至可以没有from 背面的子查询,直接从一张已经存在的表里大概视图里数据也是可以的
  1. FROM src  INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100  INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200  INSERT OVERWRITE TABLE dest3 PARTITION(ds=&#39;2008-04-08&#39;, hr=&#39;12&#39;) SELECT src.key WHERE src.key >= 200 and src.key < 300  INSERT OVERWRITE LOCAL DIRECTORY &#39;/tmp/dest4.out&#39; SELECT src.value WHERE src.key >= 300;
复制代码
设置你可以在一句SQL同时插入数据到表里和导出数据
  1. from    (select behavior,count(distinct user_id) as userCnt from user_behavior group by behavior) ainner join    (select behavior,count(distinct user_id) as userCnt from user_behavior group by behavior) bon    a.behavior=b.behaviorINSERT OVERWRITE TABLE ods_dest_data    select a.behavior,a.userCnt where a.behavior=&#39;buy&#39;INSERT OVERWRITE TABLE ods_dest_data    select b.behavior,b.userCnt where b.behavior=&#39;cart&#39;;
复制代码
你也可以写出在from 语句里实现join 的逻辑,然后下面使用join 出来的效果聚集
  1. from    (select behavior,count(distinct user_id) as userCnt from user_behavior group by behavior) a,    (select behavior,count(distinct user_id) as userCnt from user_behavior group by behavior) bINSERT OVERWRITE TABLE ods_dest_data    select a.behavior,a.userCnt where a.behavior=&#39;buy&#39;INSERT OVERWRITE TABLE ods_dest_data2    select b.behavior,b.userCnt where b.behavior=&#39;cart&#39;;
复制代码
也可以写出上面这种多个from 并列的子句
总结


  • 可以简化SQL的写法和视图的作用有点像
  • 可以提高性能——淘汰表的扫描次数(缓存效果集的方式)
  • 当一个查询效果需要多次使用的时候我们就可以使用with as 这种方式,再特殊一点的场景我们也可以实验使用from 开头的这种写法
  • with as 可以替代from 的写法,但是from 开头的这种写法只适合插入的场景

来源:https://blog.csdn.net/king14bhhb/article/details/112058174
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )