BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vorkady
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
JBailey
Barite | Level 11

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

 

View solution in original post

5 REPLIES 5
JBailey
Barite | Level 11

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
Obsidian | Level 7

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
Barite | Level 11

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
Obsidian | Level 7
Thanks Jeff. I have already opened SAS track and will contact with the additional details provided by you. Again thanks for your help.
JBailey
Barite | Level 11

My pleasure. I think you are close.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 2845 views
  • 1 like
  • 2 in conversation