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

Where is the HPLSQL functionality?


We've got the new HDP 2.6 installed and we're in the process of testing it. I'm attempting to create some basic functions in hplsql but am stymied by the limited/nonexistant support of basic functions. I'm hoping somebody can tell me that it's a configuration issue. Here's an example:

$ hplsql -e "create function myfunc(in mydate string) returns integer as begin return date_format(mydate, 'u') + 1; end; declare dy integer; dy := myfunc(current_date); print dy;"

date_format is a hive function, I've tried using mod, pmod, and many other basic functions here and get the same thing. Running this produces:

Ln:1 Function not found: date_format or

Ln:1 Function not found: mod or

Ln:1 Function not found: pmod

If you only have access to the functions listed in, then calling this PL/SQL compatible or any other kind of compatible is disingenuous at best. So I believe we've missed a step in configuration. Can anyone point us in the right direction?




Re: Where is the HPLSQL functionality?

Super Guru

Re: Where is the HPLSQL functionality?


Thanks @Timothy Spann, but in the example that @bpreachuk provided, if he were to have used any simple hive function call such as sin, cos, tan, least, greatest, quarter, weekofyear or anything else in the Hive manual it would fail. At least in our current system it does. We can't be the only ones this is a problem with, so I have to believe it's configured incorrectly, but I can't find any examples to prove it, just looking for one. Can anyone use the weekofyear(string date) return integer function from within hplsql?

Re: Where is the HPLSQL functionality?

Expert Contributor

@Jim Dolan can you try the steps in this link. Also i would check if the hadoop_home variable is set along with may hadoop_classpath.

Re: Where is the HPLSQL functionality?


@Karthik Narayanan, I'll pass this over to the people that did the installation. I checked my session though and found no hadoop_home set and none established in the bash script that kicks off hplsql. The hadoop_classpath is set within that script. The title of the article by @Yogeshprabhu was How to configure hplsql and execute the function. Any idea what function he was referring to? All available functions? Do you have an example of it working?