topic Re: How to configure ODBC/JDBC in Viya Marketplace in SAS Viya (Pay-As-You-Go)

How to configure ODBC/JDBC in Viya Marketplace

fnajera — Wed, 10 Jul 2024 06:27:18 GMT

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.

Re: How to configure ODBC/JDBC in Viya Marketplace

fnajera — Wed, 10 Jul 2024 07:39:48 GMT

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.

Re: How to configure ODBC/JDBC in Viya Marketplace

priyhat_sas — Fri, 12 Jul 2024 18:09:45 GMT

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

Re: How to configure ODBC/JDBC in Viya Marketplace

fnajera — Fri, 12 Jul 2024 22:35:27 GMT

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.

Re: How to configure ODBC/JDBC in Viya Marketplace

fnajera — Fri, 12 Jul 2024 23:55:07 GMT

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

Re: How to configure ODBC/JDBC in Viya Marketplace

fnajera — Sat, 13 Jul 2024 00:14:51 GMT

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-2021-2-4/ta-p/802050

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

Re: How to configure ODBC/JDBC in Viya Marketplace

priyhat_sas — Tue, 16 Jul 2024 17:23:40 GMT

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

Re: How to configure ODBC/JDBC in Viya Marketplace

fnajera — Wed, 17 Jul 2024 04:22:19 GMT

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