Using HPL/SQL (to create functions/procedures) on the HDP 2.5 Sandbox

[#1]

My organization wants to test porting over a bunch of SQL Server functions and procedures into HPL-SQL to see if they function properly and what type of development effort we would be looking at to migrate to Hortonworks. I can't seem to figure out how to get HPL/SQL working on the 2.5 sandbox.

1) Is HPL/SQL supported out of the box on the 2.5 sandbox? If not, do you know how I go about setting it up? If so, how do I access it (I've attempted to run "hplsql" command from the command line and it just says the command is not found)?

2) Is there any way to run HPL/SQL to create functions and procedures through the dashboard?

Any assistance is greatly appreciated!

Greg

[#2]

take a look at their documentation http://www.hplsql.org/start specifically for Hortonworks distribution.

[#3]

I've already looked at the documentation, but my questions is related specifically to the sandbox... does HPL/SQL come configured to any degree, or must I install it from scratch? Performing a search of the CentOS file system, I was able to find the source files in a fairly obscure directory, so that leads me to believe it is not installed, however I cannot find any documentation related to the configuration of the sandbox.

I'll try to run through the installation again and post back if I have a question (last time I tried to run it I got hung up on when I ran the HPLSQL bash script as java could find a class, i think it was called "org.apache.hadoop.hplsql", but I can't be sure as the sandbox just ran out of space and I have to re-install it - going to post that question separately as to why the sandbox just seems to keep allocating more and more space the longer it's running without any activity occurring).

I'll post back.

Greg

[#4]

Good Morning @Greg Frair.

I have not started up the sandbox but HPL/SQL should be there available for your use. If you cd to the executable directory you should be able to run hplsql via the command line. You could also add the executable directory to your $PATH variable.

./hplsql -f "query3.hpl"

./hplsql -e "declare ZP char(10); select ZP = min(zipPostal) from customer; print 'Smallest Zip Code is: ' || ZP;"

I have a very simple github repository with sample database backups, flat files, and SQL Server/Hive DDL. It may be helpful for you. It also has a powerpoint that shows the results of executing the sample code on SQL Server and Hive. It also goes into more detail of how to setup and use HPL/SQL (changes to the hplsql and the hplsql-site.xml files). It can be found here:

https://github.com/BillPreachuk/HPLSQL

I hope this helps.

[#5]

Hi @Greg Frair,

HPLSQL support has been added for Hive 2.x provided on the HDP 2.5.0 Sandbox in Technical Preview mode. Both Hive 1.x and 2.x branches were provided together for the 2.5.x release.

The binary can be found at the following location:

/usr/hdp/current/hive-server2-hive2/bin

./hplsql --version
Hive 2.1.0.2.5.0.0-1245
Subversion git://c66-slave-20176e25-2/grid/0/jenkins/workspace/HDP-parallel-centos6/SOURCES/hive2 -r 027527b9c5ce1a3d7d0b6d2e6de2378fb0c39232
Compiled by jenkins on Fri Aug 26 01:40:02 UTC 2016
From source with checksum 293c871560185db1cdd81d7d9f11c09d

Regards,

@Darwin

[#6]

Hi bpreachuk,

Thanks very much for that information... I did a "find . -name '*hplsql*' and the following files where found:

Are these the files that I should be using? Should I copy them to another directory and then add that directory to the $PATH variable?

Thanks in advance,

Greg

[#7]

Hi @Greg Frair. Given what @dtraver has mentioned above:

You should be able to run hplsql commands from within that directory.

/usr/hdp/current/hive-server2-hive2/bin/hplsql -e " <sql commands>"

or

/usr/hdp/current/hive-server2-hive2/bin/hplsql -f "<script filename>"

[#8]

Hi @Greg Frair,

Those paths are due to the 'find' command being run from the HDP Sandbox Docker host (instead of the container itself), hence the paths referencing Docker overlayFS.

To check the ssh port redirections for the Docker container:

docker port sandbox | grep 22
e.g.
docker port sandbox | grep 22
1220/tcp -> 0.0.0.0:1220
22/tcp -> 0.0.0.0:2222

To access the docker container itself (redirection forward for ssh should be 2222)

ssh root@localhost -p 2222

Once inside of the docker 'sandbox' container, the binaries should be located at:

/usr/hdp/current/hive-server2-hive2/bin

which is a symlink to /usr/hdp/2.5.0.0-1245/hive2/bin

Regards,

@Darwin