Hive connectivity using Knox

[vorkady]

Hi

We are trying to connect Hive2    using SAS access to Hadoop in  our SAS BI environment . I have included the required update on SAS config files and use the below libname syntax to connect Hive server using KNOX method. I am continued to get error below error message. Any suggestion and advise appreciated. 

option set=sslcalistloc="/opt/sas/third_party/xx.crt";

option set=SAS_HADOOP_RESTFUL='1';

option
set=KNOX_GATEWAY_URL="https://xx.devhcloud.xxgc.net:8443/gateway/default";

option sastrace=',,,d' sastraceloc=saslog nostsuffix;

option set=SAS_HADOOP_CONFIG_PATH="/opt/sas/third_party/Hadoop/config";
option set=SAS_HADOOP_JAR_PATH="/opt/sas/third_party/Hadoop/jars/lib";

/*modified code */

libname x hadoop server='xxxxx.devhcloud.bmogc.net' user='xxxxx' password="&wpwd"
uri="jdbc:hive2://xxxxxx.devhcloud.bmogc.net:8443/;ssl=true;
   transportMode=http;httpPath=gateway/default/hive2" schema="default";

Logs:

26 GOPTIONS ACCESSIBLE;
27 libname x hadoop
27 ! server='xxxxx.devhcloud.bmogc.net'
27 ! user='xxxxx' password="&wpwd"
28 Xri="jdbc:hive2://xxxxx.devhcloud.xxogc.net
28 ! :8443/;ssl=true;
4 The SAS System
12:07 Friday, July 13, 2018

29    transportMode=http;httpPath=gateway/default/hive2"
29 ! schema="default";
NOTE 49-169: The meaning of an identifier after a quoted string
might change in a future SAS release. Inserting
white space between a quoted string and the
succeeding identifier is recommended.
ERROR: java.lang.IllegalArgumentException: Illegal character in
path at index 34: hive2://dummyhost:00000/;ssl=true;  
 transportMode=http;httpPath=gateway/default/hive2
ERROR: Error trying to establish connection.
ERROR: Error in the LIBNAME statement.

[JBailey · Accepted solution]

Hi @vorkady

How about....

libname x hadoop uri='jdbc:hive2://cvdcmdlhdpadm08.devhcloud.bmogc.net:8443/default;ssl=true;transportMode=http;httpPath=gateway/default/hive' 
                 server='cvdcmdlhdpadm08.devhcloud.bmogc.net' 
                 user='svorkad' pw="&wpwd" schema="default";

Update: this solved the original issue. There is a secondary security/configuration issue at play here. The LIBNAME statement in this reply is a syntactically correct example.

Best wishes,

Jeff

[vorkady]

Thanks Jeff,

I am now getting different errors , SSL certificate related (I did include certificate path in set statements), Pl find below logs. Any idea?


14:30 Tuesday, July 17, 2018

21 GPATH=&sasworklocation
22 ENCODING=UTF8
23 options(rolap="on")
24 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
25
26 GOPTIONS ACCESSIBLE;
27 libname x hadoop
27 ! uri='jdbc:hive2://xxxxxx.xxxloud.bmogc.net
27 ! :8443/default;
28 ssl=true;transportMode=http;
28 ! httpPath=gateway/default/hive'
4 The SAS System
14:30 Tuesday, July 17, 2018

28 ! server='xxxx.devhcloud.bmogc.net'
29 user='xxxx' pw="&wpwd" schema="default";
NOTE 49-169: The meaning of an identifier after a quoted string
might change in a future SAS release. Inserting
white space between a quoted string and the
succeeding identifier is recommended.
ERROR: java.sql.SQLException: Could not establish connection to
jdbc:hive2://cvdcmdlhdpadm08.devhcloud.bmogc.net:8443/def
ault;ssl=true;transportMode=http;httpPath=gateway/default
/hive: javax.net.ssl.SSLHandshakeException:
sun.security.validator.ValidatorException: PKIX path
building failed:
5 The SAS System
14:30 Tuesday, July 17, 2018

sun.security.provider.certpath.SunCertPathBuilderExceptio
n: unable to find valid certification path to requested
target
ERROR: Error trying to establish connection.
ERROR: Error in the LIBNAME statement.

[JBailey]

Hi @vorkady,

OK, so the original problem problem is solved and now there is an SSL error. That scares me...;)

The sslcalistloc=option is SAS specific, I think. The problem may be that the SAS environment cannot find the certificate for Hortonworks. This usually boils down to a configuration problem with SSL. It may be best to show this to your Hortonworks admin. If they can't help then contact SAS Tech Support. In fact, I would probably start with SAS Tech Support because they are extremely good at this. 

For those looking for examples of SAS/ACCESS Interface to Hadoop LIBNAME statements that will (should) work with Knox the one on my response may be a good starting point. 

Best wishes,

Jeff

[vorkady]

Thanks Jeff. I have already opened SAS track and will contact with the additional details provided by you. Again thanks for your help.

[JBailey]

My pleasure. I think you are close.