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

Hive: Can't get the md5 value

Rising Star

I get the following error. Any suggestions?

Hive Version: 1.5.0

hive> select md5(name) from account limit 1;

FAILED: SemanticException [Error 10011]: Line 1:7 Invalid function 'md5'

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Misc.Functions

1 ACCEPTED SOLUTION

Accepted Solutions

I am not aware of Hive Version 1.5.0 (do you mean Hive View?)

Anyhow, it works on Hive 1.2.1 (as of HDP 2.5)

$ beeline -u "jdbc:hive2://192.168.124.145:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2" -n hive
Connecting to jdbc:hive2://192.168.124.145:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2
Connected to: Apache Hive (version 1.2.1000.2.5.0.0-1245)
Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.2.1000.2.5.0.0-1245 by Apache Hive

0: jdbc:hive2://192.168.124.145:2181/> select dept_name, md5(dept_name) from departments limit 1;
+-------------------+-----------------------------------+--+
|     dept_name     |                _c1                |
+-------------------+-----------------------------------+--+
| Customer Service  | d5552e0564007d93ff5937a9cb3bc491  |
+-------------------+-----------------------------------+--+
1 row selected (0.337 seconds)

and on Hive 2.1 (TP in HDP 2.5)

$ beeline -u "jdbc:hive2://192.168.124.145:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2-hive2" -n hive
Connecting to jdbc:hive2://192.168.124.145:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2-hive2
Connected to: Apache Hive (version 2.1.0.2.5.0.0-1245)
Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.2.1000.2.5.0.0-1245 by Apache Hive

0: jdbc:hive2://192.168.124.145:2181/> select dept_name, md5(dept_name) from departments limit 1;
+-------------------+-----------------------------------+--+
|     dept_name     |                c1                 |
+-------------------+-----------------------------------+--+
| Customer Service  | d5552e0564007d93ff5937a9cb3bc491  |
+-------------------+-----------------------------------+--+
1 row selected (6.083 seconds)

View solution in original post

2 REPLIES 2

I am not aware of Hive Version 1.5.0 (do you mean Hive View?)

Anyhow, it works on Hive 1.2.1 (as of HDP 2.5)

$ beeline -u "jdbc:hive2://192.168.124.145:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2" -n hive
Connecting to jdbc:hive2://192.168.124.145:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2
Connected to: Apache Hive (version 1.2.1000.2.5.0.0-1245)
Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.2.1000.2.5.0.0-1245 by Apache Hive

0: jdbc:hive2://192.168.124.145:2181/> select dept_name, md5(dept_name) from departments limit 1;
+-------------------+-----------------------------------+--+
|     dept_name     |                _c1                |
+-------------------+-----------------------------------+--+
| Customer Service  | d5552e0564007d93ff5937a9cb3bc491  |
+-------------------+-----------------------------------+--+
1 row selected (0.337 seconds)

and on Hive 2.1 (TP in HDP 2.5)

$ beeline -u "jdbc:hive2://192.168.124.145:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2-hive2" -n hive
Connecting to jdbc:hive2://192.168.124.145:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2-hive2
Connected to: Apache Hive (version 2.1.0.2.5.0.0-1245)
Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.2.1000.2.5.0.0-1245 by Apache Hive

0: jdbc:hive2://192.168.124.145:2181/> select dept_name, md5(dept_name) from departments limit 1;
+-------------------+-----------------------------------+--+
|     dept_name     |                c1                 |
+-------------------+-----------------------------------+--+
| Customer Service  | d5552e0564007d93ff5937a9cb3bc491  |
+-------------------+-----------------------------------+--+
1 row selected (6.083 seconds)

View solution in original post

Rising Star

You were right, it was Hive view version, my bad The Hive version is 1.2.1000.2.4.3.0-227 and we are still on HDP 2.4 stack. It explains why the MD5 won't work.