question Re: Hive Query not working in Archives of Support Questions (Read Only)

Hive Query not working

sanjeevanmahaja — Sat, 03 Sep 2016 04:34:38 GMT

> Not sure can use select min(from_date) 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, select min(s.from_date) from new2_salaries s GROUP BY s.emp_no WHERE s.emp_no = e.emp_no as hire_date_new from employees e.

Re: Hive Query not working

dkozlowski — Sat, 03 Sep 2016 04:47:17 GMT

@sanjeevan mahajan

Can you try this out:

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) from new2_salaries s, employees e
WHERE s.emp_no = e.emp_nosel
GROUP BY s.emp_no

Re: Hive Query not working

sanjeevanmahaja — Sat, 03 Sep 2016 04:53:32 GMT

@Daniel Kozlowski : It's still gives an exception "Expression not in Group By key emp_no"

Re: Hive Query not working

sanjeevanmahaja — Sat, 03 Sep 2016 04:55:21 GMT

@Daniel Kozlowski Please see attached screen grab screen-shot-2016-09-02-at-225412.png

Re: Hive Query not working

dkozlowski — Sat, 03 Sep 2016 04:58:43 GMT

@sanjeevan mahajan

How about this:

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) from new2_salaries s, employees e
WHERE s.emp_no = e.emp_nosel
GROUP BY s.emp_no, s.from_date

Re: Hive Query not working

sanjeevanmahaja — Sat, 03 Sep 2016 05:07:43 GMT

@Daniel Kozlowski Still not

Re: Hive Query not working

dkozlowski — Sat, 03 Sep 2016 06:10:14 GMT

@sanjeevan mahajan

I have made quite few attempts - the ones that actually should work. Similar to

SELECT j.* 
FROM new2_salaries j 
WHERE j.from_date = 
(SELECT MIN(j2.from_date) FROM new2_salaries j2 WHERE j.empl_no = j2.empl_no);

Unfortunately, this is not working due to an existing bug: https://issues.apache.org/jira/browse/HIVE-8960

Re: Hive Query not working

pminovic — Sun, 04 Sep 2016 11:50:47 GMT

It doesn't work because, to quote the related wiki page: When using group by clause, the select statement can only include columns included in the group by clause, and aggregate functions on other columns. So, your query will work if you remove "group by" and "min".

Re: Hive Query not working

cstanca — Tue, 06 Sep 2016 10:17:24 GMT

@sanjeevan mahajan

... and to add to what Predrag stated based on the documentation, the same is true for all other databases, including Oracle, PostgreSQL, etc. The query needs to be rewritten to achieve the expected result, first find the min(s.from_date) per s.emp_no and at the second step join with e.emp_no to retrieve the needed other fields, as lookups.

Try this:

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 s.emp_no) s
WHERE s.emp_no = e.emp_no;

If any of the responses to your question addressed the problem don't forget to vote and accept the answer. If you fix the issue on your own, don't forget to post the answer to your own question. A moderator will review it and accept it.

Re: Hive Query not working

sanjeevanmahaja — Tue, 06 Sep 2016 15:21:55 GMT

@ Constantin Stanca : Thanks a lot for the help but Its is still giving semantic exception Invalid table alias or column reference 's': possible column names are emp_no, salary, from_date, to_date

Re: Hive Query not working

bpreachuk — Tue, 06 Sep 2016 19:39:56 GMT

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;

Re: Hive Query not working

sanjeevanmahaja — Tue, 06 Sep 2016 21:30:28 GMT

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

Re: Hive Query not working

cstanca — Tue, 06 Sep 2016 23:38:22 GMT

@bpreachuk

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