Error Message: TOK_ALLCOLREF is not supported in current context - while Using DISTINCT in HIVE

Question

I'm using the simple command: SELECT DISTINCT * FROM first_working_table; in HIVE 0.11, and I'm receiving the following error message:

FAILED: SemanticException TOK_ALLCOLREF is not supported in current context.

Does anyone know why this is happening? How can we solve it?

Thank you, Gal.

Salami

Answer 1

Hive doesn't support DISTINCT * syntax. You can manually specify every field of the table to get the same result:

SELECT DISTINCT field1, field2, ...., fieldN
  FROM first_working_table

Abrupt

This is the easiest way or can be called as work-around to fix the problem. – Husha

Answer 2

As specified in earlier comment distinct * not supported. Which is true. One trick can be like this.

Distinct * can be used in this fashion:

select distinct * from (
select t1.col1,t1.col2,t1.col3,t2.* from t1,t2
)tbl;

I have used this syntax in Hive 2.x. So I can confirm that this works.

Adularia

Answer 3

There is one trick:

Select distinct * from (select * from tableA)abc

Weird but yes it works!

Foxhole