无法将列添加为配置单元查询的一部分

rxztt3cl  于 2021-06-03  发布在  Hadoop
关注(0)|答案(1)|浏览(225)
CREATE EXTERNAL TABLE old_events
(day STRING, foo STRING, count STRING, internal_id STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION '${INPUT}';;

CREATE EXTERNAL TABLE events
(internal_id, foo STRING, count STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION '${OUTPUT}';;

INSERT OVERWRITE TABLE events
SELECT internal_id, e2.foo, count(e1.foo)
FROM old_events e2
LEFT OUTER JOIN old_events e1
ON e1.foo = e2.foo
WHERE e1.event = 'event1'
AND e2.event = 'event2'
GROUP BY e2.foo;

我得到以下错误,如果internal\u id是select子句的一部分失败:语义分析错误:行5:8表达式不在group by key internal\u id中
为了避免这种情况,我将internal\u id添加到groupby列中,并导致以下错误。失败:语义分析出错:在多个表/子查询中找到列内部\u id
我该如何度过这个问题?

zfciruhq

zfciruhq1#

brisi e1和e2都有列内部的\u id,因此您必须指出您想要哪个列,例如:

INSERT OVERWRITE TABLE events
SELECT e2.internal_id, e2.foo, count(e1.foo)
FROM old_events e2
LEFT OUTER JOIN old_events e1
ON e1.foo = e2.foo
AND e1.event = 'event1'
WHERE e2.event = 'event2'
GROUP BY e2.internal_id, e2.foo;

相关问题