Hive Invalid function aes_encrypt

Issue:

User unable to use aes_encrypt function with following error:

0: jdbc:hive2://node01.kiosafiy.com:1> SELECT base64(aes_encrypt('ABC', '1234567890123456'));
Error: Error while compiling statement: FAILED: SemanticException [Error 10011]: Invalid function aes_encrypt (state=42000,code=10011)

Caused:

According to [1] aes_encrypt function should be built-in in Hive 1.3.0+ but for some reason it not available by default in CDH6 Distro.

Resolution:

Add aes_encrypt UDF function using this repo [2]. Here is the result:

0: jdbc:hive2://node01.kiosafiy.com:1> select base64(aes_encrypt('ABC', '1234567890123456'));
INFO  : Compiling command(queryId=hive_20210709105720_7d3c119d-891a-4f2b-88ba-c2ae620f342d): select base64(aes_encrypt('ABC', '1234567890123456'))
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:_c0, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=hive_20210709105720_7d3c119d-891a-4f2b-88ba-c2ae620f342d); Time taken: 0.258 seconds
INFO  : Executing command(queryId=hive_20210709105720_7d3c119d-891a-4f2b-88ba-c2ae620f342d): select base64(aes_encrypt('ABC', '1234567890123456'))
INFO  : Completed executing command(queryId=hive_20210709105720_7d3c119d-891a-4f2b-88ba-c2ae620f342d); Time taken: 0.007 seconds
INFO  : OK
+---------------------------+
|            _c0            |
+---------------------------+
| y6Ss+zCYObpCbgfWfyNWTw==  |
+---------------------------+
1 row selected (0.326 seconds)

0: jdbc:hive2://node01.kiosafiy.com:1> select aes_decrypt(unbase64('y6Ss+zCYObpCbgfWfyNWTw=='), '1234567890123456');
INFO  : Compiling command(queryId=hive_20210709110540_4493545c-16a6-451e-8f78-11ae1c5e387b): select aes_decrypt(unbase64('y6Ss+zCYObpCbgfWfyNWTw=='), '1234567890123456')
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:_c0, type:binary, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=hive_20210709110540_4493545c-16a6-451e-8f78-11ae1c5e387b); Time taken: 0.235 seconds
INFO  : Executing command(queryId=hive_20210709110540_4493545c-16a6-451e-8f78-11ae1c5e387b): select aes_decrypt(unbase64('y6Ss+zCYObpCbgfWfyNWTw=='), '1234567890123456')
INFO  : Completed executing command(queryId=hive_20210709110540_4493545c-16a6-451e-8f78-11ae1c5e387b); Time taken: 0.01 seconds
INFO  : OK
+------+
| _c0  |
+------+
| ABC  |
+------+
1 row selected (0.36 seconds)

References:

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

[2] https://github.com/zuowang/aes_deencript