How to configure ODBC/JDBC in Viya Marketplace

[fnajera]

Hi team,

We're configuring SAS/ACCESS to ODBC in a SAS Viya Marketplace deployment, in order to connect to Denodo. However, although we modify the /viya-share/access-clients/odbc/odbc.ini and odbcinst, these changes are not reflected in the platform, even when we restart the environment.  Can you please give us a hand? 

We tried to use the JDBC alternative, but it's like if the /data-drivers/jdbc is not mounted correctly in the pods. Because, even when we put the driver in /viya-share/access-clients/jdbc. This is not detected by the platform.

It's important to say, that we are able to connect to Denodo from the Jumpserver, the problem comes when we want to make the drivers and odbc.ini files available to the compute and cas pods.

NOTE: In the official documentation, we don't have an example for ODBC configuration.

Please let us know if you need further information.

Thanks

Fernando.

[fnajera]

Please validate that the files sas-access.properties, and the data-mount-job.yaml files are correctly configured in the marketplace environment, because it seems like it's not working as expected.

[priyhat_sas]

Hi, I am looking at the issue and would like more details.
Are you connecting to Denado using SAS/Access to JDBC and the libname is failing? Would you mind sharing the error details?
Thanks,
Priyha

[fnajera]

Hi priyhat,

Thank you so much for your reply.

We tried the connection using ODBC and JDBC, and in both cases, the process fails. Below details:

a) ODBC

1.- In the NFS, we identify the following path /viya-share/access-clients. In odbc, we modify the odbc.ini and odbcinst.ini, in order to define the path where the Denodo driver is located.

odbcinst.ini

odbc.ini

denodo driver path

2.- Then we restarted the environment using the process documented in the SAS Viya marketplace page.

https://documentation.sas.com/doc/en/viyaakscdc/v_001/viyaakstasks/n08xicw2ywyp24n18938pqkzf9cq.htm

3.- However, when we try to use the DNS from odbc.ini or the DRIVER from the odbcinst.ini. These are not found, because the files are not updated.

4.- As you can see, the values that we defined in the files are not being taken by the application. Even we check these files from sas studio, and we realized that the odbc.ini and the odbcinst.ini, that the compute pod is referencing, have the default values.

b) JDBC

We put the jdbc driver in /viya-share/access-clients/jdbc, but when we try to use it, referencing to /data-drivers/jdbc inside the pod. It's not finding it

Please let me know if you need further information.

Thank you 

Fernando.

[fnajera]

Additional information about the environment variables: ODBCHOME, ODBCINI, ODBCINSTINI, THIRD_PARTY_LIB, LD_LIBRARY_PATH, PATH), where THIRD_PARTY_LIB is missing, and the path where my Denodo driver is located, does not appear in the LD_LIBRARY_PATH

80 %put %quote(%sysget(ODBCHOME));
/opt/sas/viya/home/lib64/accessclients
81 %put %quote(%sysget(ODBCINI));
/opt/sas/viya/home/lib64/accessclients/odbc.ini
82 %put %quote(%sysget(ODBCINST));
/opt/sas/viya/home/lib64/accessclients/odbcinst.ini
83 %put %quote(%sysget(THIRD_PARTY_LIB));

84 %put %quote(%sysget(LD_LIBRARY_PATH));
/opt/sas/viya/home/lib64:/usr/lib/jvm/java-17-openjdk-17.0.11.0.9-2.el8.x86_64/lib/server:/usr/lib/jvm/java-17-openjdk-17.0.11.0.9-2
.el8.x86_64/lib:/opt/sas/viya/home/SASFoundation/sasexe:/opt/sas/viya/home/lib64/accessclients/lib:/access-clients/oracle:/opt/sas/v
iya/home/lib64/accessclients/vertica/lib64:/opt/sas/viya/home/postgresql12/lib64:/opt/sas/viya/home/commonfiles/home_1720195961/SASF
oundation/sasexe:/opt/sas/viya/home/SASFoundation/utilities/bin
85 %put %quote(%sysget(PATH));
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/opt/sas/viya/home/lib64:/opt/sas/viya/home/lib64/accessclients/oracle/
19.22/client64/bin
86 %put %quote(%sysget(ODBCSYSINI));
/opt/sas/viya/home/lib64/accessclients/

In an standard viya deployment, this should be configured in sas-access.properties, maybe you can help us to take a look on this file

[fnajera]

Regarding JDBC, as you can see, the driver is on the correct path, according also to this doc:

https://communities.sas.com/t5/SAS-Communities-Library/JDBC-Drivers-Deployment-Made-Easier-SAS-Viya-...

However, if you use this code, it fails, becuase it cannot find the driver in the /data-drivers/jdbc path. Maybe because it is not mounted in the pod. See the same link above.

options sastrace="d,,," sastraceloc=saslog nostsuffix ;

libname x JDBC driverclass="com.denodo.vdp.jdbc.Driver"
URL="jdbc:vdb://xxxxx:9996/common_sources" user=xxxxx
password="xxxxxx*";

80 options sastrace="d,,," sastraceloc=saslog nostsuffix ;
JDBC: attempting JDBC connection: jdbc:vdb://192.168.2.6:9996/common_sources
/opt/sas/viya/home/SASFoundation/lib/access/hadoop/
/opt/sas/viya/home/SASFoundation/lib/access/hadoop/access-hadoop-hivehelper-2.15.0.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-googledrive-23.0.8669.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-youtubeanalytics-23.0.8669.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-databricks-23.0.8806.0.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-googleanalytics-23.0.8669.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-sparksql-23.0.8705.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-apachehive-23.0.8808.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-facebook-23.0.8669.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-onedrive-23.0.8669.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-odata-23.0.8669.jar
81
82 libname x JDBC driverclass="com.denodo.vdp.jdbc.Driver"
83 URL="jdbc:vdb://xxxxx:9996/common_sources" user=xxxxxx
84 password=XXXXXXXXXXXXXXXXXXXXXXXX;
ERROR: java.lang.ClassNotFoundException: com.denodo.vdp.jdbc.Driver
ERROR: at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:445)
ERROR: at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:592)
ERROR: at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:525)
ERROR: at java.base/java.lang.Class.forName0(Native Method)
ERROR: at java.base/java.lang.Class.forName(Class.java:375)
ERROR: at com.sas.access.hadoop.hive.JdbcHelper.getDriver(JdbcHelper.java:476)
ERROR: Error trying to establish connection: java.lang.ClassNotFoundException: com.denodo.vdp.jdbc.Driver
ERROR: Error in the LIBNAME statement.
85
86 /* region: Generated postamble */
87 /* Close ODS destinations */
88 &graphterm; ;*';*";*/;run;quit;
89 quit;run;
90 ods html5 (id=web) close;
91 ods listing close;
92 %if %sysfunc(fileref(_gsfname)) lt 0 %then %do;
93 filename _gsfname clear;
NOTE: Fileref _GSFNAME has been deassigned.
94 %end;
95 %studio_capture_custom_output;
96 /* endregion */
97

[priyhat_sas]

Thank you for all the details, looking into this and will get back. Thanks.

[fnajera]

Thanks Priyhat, we're looking forward to hearing from you, in order to finish this configuration.