CODEWITHSUNDEEP
sqldatabasehadoophive
lalaland
lalaland

Reputation: 341

How to run a subquery in hive

I have this query that I am trying to run in HIVE:

select transaction_date, count(total_distinct) from (
SELECT transaction_date, concat(subid,'**', itemid) as total_distinct
FROM TBL_1
group by transaction_date, subid,itemid
) group by transaction_date

What I am trying to do it get the distinct combination of subid and itemid, but I need the total count per day. When I run the query above, I get this error:

Error while compiling statement: FAILED: ParseException line 6:2 cannot recognize input near 'group' 'by' 'TRANSACTION_DATE' in subquery source

The query looks correct to me though. Has anyone encountered this error?

Upvotes: 1

Views: 381

Answers (1)

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10693

Hive requires subqueries to be aliased, so you need to specify a name for it:

select transaction_date, count(total_distinct) from (
SELECT transaction_date, concat(subid,'**', itemid) as total_distinct
FROM TBL_1
group by transaction_date, subid,itemid
) dummy  -- << note here
group by transaction_date

True, the error message is far from helpful.

Upvotes: 1

Related Questions