CODEWITHSUNDEEP
sqlhadoopmapreducehive
Pratheesh
Pratheesh

Reputation: 161

Querying in Hive

In SQL we have partion by and group by 

select deptno, count(*) c from emp group by deptno;

DEPTNO C
------ -
10     3
20     5
30     6

select deptno, count(*) over (partition by deptno) c from emp;


DEPTNO C
------ -
10     3
10     3
10     3
20     5
20     5
20     5
20     5
20     5
30     6
30     6
30     6
30     6
30     6
30     6

If we need to practice the same thing in HIVE what will be the option . Do we have the same in Hive? Pls suggest

Thanks in Advance.

Upvotes: 1

Views: 431

Answers (2)

Devan M S
Devan M S

Reputation: 702

same problem ..

I tried Windowing and Analytics Functions. .

FAILED: ParseException line 1:29 missing FROM at '(' near '(' in subquery source line 1:30 cannot recognize input near 'PARTITION' 'BY' 'salary' in subquery source

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294187

Hive supports windowing functions, see Windowing and Analytics Functions. So is really exactly as you posted:

select deptno, count(*) over (partition by deptno) c from emp;

The trick is that you need to be on a Hive that is recent enough to have the windowing functions. The linked page shows these were introduced in Hive 0.11.

As for the first query select deptno, count(*) c from emp group by deptno; that is just an ordinary aggregate, I think was supported in v. -1 ...

Upvotes: 1

Related Questions