question Re: Hive in built function greatest is not working in Archives of Support Questions (Read Only)

Hive in built function greatest is not working

2hanu_valluri — Tue, 30 Jan 2018 07:21:49 GMT

I have a requirement to calculate greatest value in multiple columns.

Input:

name	sub1	sub2	sub3
st1	10	12	15
st2	50	30	35
st3	15	18	13

expected output:

name	max_sub
st1	15
st2	50
st3	18

When I try to use greatest function as below, I'm getting

Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: Error evaluating greatest(sub1,sub2,sub3)

select name,greatest(sub1,sub2,sub3) from students;

any other functions or simple udf available?

Note: I cant' use CASE statement as I have to consider around 15 columns.

Re: Hive in built function greatest is not working

rtrivedi — Tue, 30 Jan 2018 15:43:31 GMT

@Hanu V

Can you attach a desc extended for your table. Greatest should work provided all the columns have the same datatype and are only primitive types.

Re: Hive in built function greatest is not working

2hanu_valluri — Tue, 30 Jan 2018 23:22:42 GMT

@rtrivedi

Attached the screenshot FYR.

greatest.jpg

Re: Hive in built function greatest is not working

eberezitsky — Tue, 30 Jan 2018 23:41:31 GMT

@Hanu V

To work around your issue, you can explicitly cast to string or int (or whatever your data type should be).

I could reproduce your case:

select greatest(month, billed) from sales limit 5;
Error: java.io.IOException: org.apache.hadoop.hive.ql.metadata.HiveException: Error evaluating greatest(month,billed) (state=,code=0)

with workaround:

select greatest(int(month), int(billed)) from sales limit 5;
+------+--+
| _c0  |
+------+--+
| 8    |
| 8    |
| 8    |
| 8    |
| 2    |
+------+--+

select greatest(string(month), string(billed)) from sales limit 5;
+------+--+
| _c0  |
+------+--+
| 8    |
| 8    |
| 8    |
| 8    |
| 2    |
+------+--+

Re: Hive in built function greatest is not working

nramanaiah — Wed, 31 Jan 2018 15:25:52 GMT

@Hanu V

This is related to HIVE-12082. This issue exist in Hive1

1) You can either use Hive2 (Interactive HS2)

2) Workaround using explicitly cast to int.

select name, greatest(int(sub1),int(sub2),int(sub3)) from students;

3) Use orc format

Re: Hive in built function greatest is not working

2hanu_valluri — Thu, 01 Feb 2018 03:14:30 GMT

Thanks @Naresh P R. Can you please let me know where can I track all the issues like this?

Thanks in advance!

Re: Hive in built function greatest is not working

nramanaiah — Thu, 01 Feb 2018 13:19:50 GMT

You can check for the relevant issue in HIVE Jira