Hive是否支持选择? [英] Does Hive support Select in Select?
问题描述
插入表格webmap
选择一个.res引用,b.res资源,
(从weblog中选择count(ip),其中resource = a.res和referer = b.res)从顶部权重
返回一个联接toprefres b;
我在hive-0.10.0-cdh4.5.0中运行该程序,并得到以下错误:
失败:ParseException行3:1无法识别接近'select''count''('in expression specification
如果select中支持Hive选项, / p>
我终于明白了答案:
insert into table webmap
从$ b $中选择refres.reso,refres.refe,count(ip)权重weblog join(从顶部选择a.res refe,b.res res)返回
trimrslash(weblog.resource)= refres.reso和trimrslash(weblog.referrer)= concat(http://dongxicheng.org,refres.refe)
group by refres.reso,refres.refe;
这对我的需求来说非常完美!
据我所知,这种相关的子查询在Hive中不受支持,我从以下文章中证实了我的想法:
$ b Hive column as a subquery select
然而我认为是,你可以修改你的查询以实现您想要获得的内容:
插入表格webmap
选择a.res引用,b。 res资源,
count(weblog.ip)权重$ b $ from toprefres a
加入toprefres b
加入weblog ON weblog.resource = a.res
和weblog.referer = b.res;
还要注意的是在那里没有ON子句可能会导致创建Cartesian产品的交叉连接并花更多时间。如果可能的话,尝试优化。
Is the code bellow in Hive possible, wich some change?:
insert into table webmap
select a.res reference, b.res resource,
(select count(ip) from weblog where resource=a.res and referer=b.res) weight
from toprefres a join toprefres b;
I run that in hive-0.10.0-cdh4.5.0 and got the error: FAILED: ParseException line 3:1 cannot recognize input near 'select' 'count' '(' in expression specification
What if select in select in Hive is supported!
I've finally figured out the answer:
insert into table webmap
select refres.reso,refres.refe, count(ip) weight from
weblog join (select a.res refe, b.res reso from toprefres a join toprefres b) refres on
trimrslash(weblog.resource)=refres.reso and trimrslash(weblog.referrer)=concat("http://dongxicheng.org",refres.refe)
group by refres.reso,refres.refe;
Which is perfect for my need!
As far as I know such corelated subqueries are not supported in Hive. I confirmed my idea from the following post:
Hive column as a subquery select
However what I think is, you can modify your query to achieve what you are trying to get:
insert into table webmap
select a.res reference, b.res resource,
count(weblog.ip) weight
from toprefres a
join toprefres b
join weblog ON weblog.resource=a.res
and weblog.referer=b.res;
Also noted that there in no ON clause in your which might result in a cross join creating a Cartesian product and taking more time. If possible try to optimize that.
这篇关于Hive是否支持选择?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!