Support Questions
Find answers, ask questions, and share your expertise

Hive Query not working

Solved Go to solution

Re: Hive Query not working

Hi @sanjeevan mahajan and @Constantin Stanca. There is a teeny tiny fix needed to Constantin's last query - the inner query needs to state "Group By emp_no" instead of "Group By s.emp_no". This snippet should work:

SELECT e.emp_no, e.birth_date,e.first_name, e.last_name, e.gender, s.min_from_date
FROM employees e,
(SELECT emp_no, min(from_date) as min_from_date 
FROM new2_salaries
GROUP BY emp_no) s
WHERE s.emp_no = e.emp_no;

By the way - going way back to your original query, this snippet should work as well. Note the changes - using double-quotes around the orc.compress parameters, ensuring that all selected (non-aggregated) columns are in the group by statement, and user the inner join syntax to explicitly call out your join and join clause. Personal opinion: It's cleaner to keep your join clauses and the where clauses separated... instead of having the join clauses stuffed into the where clause.

create table employees2_final 
stored as ORC 
tblproperties ("orc.compress"="SNAPPY") 
AS 
SELECT e.emp_no, e.birth_date, e.first_name, e.last_name, e.gender, 
       min(s.from_date) as `hire_date_new`
from new2_salaries s inner join employees e
   on s.emp_no = e.emp_no 
GROUP BY e.emp_no, e.birth_date, e.first_name, e.last_name, e.gender; 

Highlighted

Re: Hive Query not working

@bpreachuk

Thank you for catching my typo. I wrote the query while on the phone. I voted your addition. Thanks again.

Highlighted

Re: Hive Query not working

@bpreachuk and @Constantin Stanca Thanks a lot for resolving this.

Don't have an account?