Reply
Champion Alumni
Posts: 196
Registered: ‎11-18-2014

round() function with specified decimal places not working

Hello,

 

I have a small problem with the round function, when I'm adding the nb of decimals, it is not rounding at all.
I saw that there was a problem with the rounding function in https://issues.apache.org/jira/browse/HIVE-4523 ,
just that i'm in hive 1.3.1 CDH 5.3.4 and normally it should be fixed in hive 1.3.0..

Thank you!
 


GHERMAN Alina
Highlighted
Cloudera Employee
Posts: 34
Registered: ‎08-16-2016

Re: round() function with specified decimal places not working

Hey Alina,

I have tried this with CDH5.3.10 (a bit newer than your version) and with the new CDH releases, and round seems to be functioning as expected.

 

Connected to: Apache Hive (version 0.13.1-cdh5.3.10)
Driver: Hive JDBC (version 0.13.1-cdh5.3.10)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 0.13.1-cdh5.3.10 by Apache Hive
0: jdbc:hive2://localhost:10000/default> show databases;
+--------------------------------------------------------------------------------------------------+--+
| database_name |
+--------------------------------------------------------------------------------------------------+--+
| cloudera_manager_metastore_canary_test_db_hive_1_hivemetastore_03567ed21d1b892110ff1cd925ae25bd |
| default |
+--------------------------------------------------------------------------------------------------+--+
2 rows selected (1.916 seconds)
0: jdbc:hive2://localhost:10000/default> use default;
No rows affected (0.127 seconds)
0: jdbc:hive2://localhost:10000/default> show tables;
+------------+--+
| tab_name |
+------------+--+
| sample_07 |
| sample_08 |
+------------+--+
2 rows selected (0.153 seconds)
0: jdbc:hive2://localhost:10000/default> select round((20456079/100000),5);
+------------+--+
| _c0 |
+------------+--+
| 204.56079 |
+------------+--+
1 row selected (30.771 seconds)
0: jdbc:hive2://localhost:10000/default> select round((20456079/100000),1);
+--------+--+
| _c0 |
+--------+--+
| 204.6 |
+--------+--+
1 row selected (23.981 seconds)
0: jdbc:hive2://localhost:10000/default> select round((20456079/100000),3);
+----------+--+
| _c0 |
+----------+--+
| 204.561 |
+----------+--+
1 row selected (23.257 seconds)

0: jdbc:hive2://localhost:10000/default> select round(150,3);
+------+--+
| _c0 |
+------+--+
| 150 |
+------+--+
1 row selected (22.973 seconds)
0: jdbc:hive2://localhost:10000/default>

 

Against CDH5.8, same results. The big difference between the 2 releases is the time of execution. It consistently take 23-30 seconds on CDH5.3.10 where as it takes about 0.1 seconds on CDH5.8.0.

 

It is possible that my enviroment for CDH5.3.10 is not so kosher.

 

I do not have a CDH5.3.4 environment anymore. But let me know if you would like me to try it with CDH5.3.4.

 

Hope this helps.