hadoop - hive agg要求分组的列

Question

我有一个基本查询(用模糊的名称重写)，我不明白为什么配置单元要求将case语句中的t2.description列添加到分组依据。我将它们安抚并放入其中，但是当然我会为每一行的该列获取空值...如果我取出case语句并查询原始数据，则会得到所有可爱的描述。只有当我想在case语句中添加一些逻辑时，它才会失败。我是Hive的新手，并且了解它不是ANSI sql，但我没有想到它会如此挑剔。

select
t1.columnid               as column_id,
(case when t2.description in ('description1','description2','description3') then t2.description else null end) as label_description

from table1 t1
left outer join table2 t2 on (t1.inresult = t2.inresult)

group by
t1.columnid

Answer 1

基于Hive的SQL解析器显示的错误日志，通常很难理解实际问题。这里的问题是您选择2列，但仅将GROUP BY应用于一列。要使此查询可执行，您必须执行以下操作之一:

按列1和列2分组

select t1.columnid as column_id, (case when t2.description in ('description1','description2','description3') then t2.description else null end) as label_description from table1 t1 left outer join table2 t2 on (t1.inresult = t2.inresult) GROUP BY t1.columnid, (case when t2.description in ('description1','description2','description3') then t2.description else null end);

不要使用GROUP BY语句

select t1.columnid as column_id, (case when t2.description in ('description1','description2','description3') then t2.description else null end) as label_description from table1 t1 left outer join table2 t2 on (t1.inresult = t2.inresult)

将聚合函数应用于第2列

select t1.columnid as column_id, MIN(case when t2.description in ('description1','description2','description3') then t2.description else null end) as label_description from table1 t1 left outer join table2 t2 on (t1.inresult = t2.inresult) group by t1.columnid

对于配置单元，如果您使用的是GROUP BY，那么您选择的所有列都必须在GROUP BY语句中，或者包装在应用的汇总语句中，例如MAX，MIN或SUM。