I'm trying to use the new HPL/SQL to run native SQL stored procedures and functions, but the documentation is very deceiving (at least to me). Can anyone clarify if HPL/SQL is suppose to be included in Hive 2.0 and am I suppose to be able to create procedures and function straight from the Hive dashboard? Please do not simply post a link to the documentation as this is exactly what is confusing to me.
Any assistance is greatly appreciated.
The HPL/SQL functionality seems to come from HIVE-11055 (https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=59690156). If you look at the release notes for HDP 2.5.3, I don't see HIVE-11055 in the list (http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.5.3/bk_release-notes/content/patch_hive.html), but that list seems to focus on Hive 1.2.1. Hive 2.1 is provided as a Technical Preview in HDP 2.5.3 and lists "Stored Procedures Client-Side" (http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.5.0/bk_release-notes/content/tech_previews.html).
I tried to create a basic stored procedure like this in the Sandbox:
CREATE PROCEDURE set_message(IN name STRING, OUT result STRING) BEGIN SET result = 'Hello, ' || name || '!'; END;
However, I couldn't get it to work. I was also unable to find hplsql.jar files within the Sandbox.
I found the hplsql.jar files in the sandbox (in a rather obscure sounding directory, which leads me to believe they may not be installed properly), however I get the same result as you when I run the "CREATE PROCEDURE" code. Guess I'll keep trying to get this working... so frustrating that trying to find this information is so complicated.