Created 02-10-2017 10:41 PM
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!
Created 02-11-2017 01:05 AM
take a look at their documentation http://www.hplsql.org/start specifically for Hortonworks distribution.
Created 02-11-2017 06:30 PM
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.
Created 02-13-2017 01:09 PM
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:
I hope this helps.
Created 02-13-2017 01:49 PM
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:
./hplsql --version Hive 184.108.40.206.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
Created on 02-13-2017 03:22 PM - edited 08-18-2019 03:44 AM
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,
Created 02-13-2017 04:01 PM
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>"
/usr/hdp/current/hive-server2-hive2/bin/hplsql -f "<script filename>"
Created 02-13-2017 07:56 PM
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:
which is a symlink to /usr/hdp/220.127.116.11-1245/hive2/bin