Build an ACCESS engine to Denodo

[ferrickc]

Denodo is a data virtualization tool capable of building intricate and efficient data structures connecting disparate data sources. Data virtualization is critical in enterprise environments due to the wide variety of data sources a large organization would possess.

Our organization has recently implemented Denodo, but we are experiencing significant compatibility issues with our SAS 9.4 infrastructure as we are reliant on the generic SAS/ACCESS Interface to JDBC. In troubleshooting sessions with Tech Support, we have resolved one issue only to encounter another, over and over again. It has become clear that the source of these incompatibilities stem from the generic JDBC connector. An ACCESS engine specifically designed to communicate with Denodo would provide a true solution to our technical issues and frustrations. We hope that this connector would be compatible with SAS products including SAS 9.4, SAS Viya 3.5, and newer products.

[VincentRejany]

We have perfomed smoke testing on the Denodo JDBC driver with SAS/ACCESS Interface to JDBC.

https://github.com/sassoftware/sas-access-samples/tree/master/SAS%20Viya/JDBC/Denodo

[ferrickc]

Hi Vincent,

Thank you for your reply. I have looked at the link you shared, and it appears to be a README with documentation about how to configure the JDBC interface in SAS Viya, code to define libraries in SAS Viya, and a brief table of the results of some smoke testing. The links included in the table to the actual tasks must be behind a firewall because I am not able to open them.

I'm not sure how this addresses the concerns I raised. To start, our issue is with connecting SAS 9.4 to Denodo via JDBC, and the seemingly countless small incompatibilities that arise from having to use a generic connector. Everything in the github link is for SAS Viya, not SAS 9.4, though it is not clear which version of SAS Viya was used for the smoke testing.

Even if we disregard the fact that these tests were run on a different platform, I do not think performing smoke testing addresses our concerns. I found the jdbcSmokeTesting_template.sasnb file elsewhere in the repository, which I assume is reflective of the tests run against Denodo. As this file clearly states, "A smoke test is a quick and simple test that verifies that the basic functionality of the connection is working as expected." Looking further at this file, the smoke testing appears to run some very basic commands (e.g., create table mylib.class2 as select * from mylib.class;). I am not surprised to see that the basic tasks included in the smoke testing performed as expected. The issues we are experiencing are considerably more complex than what might be surfaced by running a simple select * on a single demo table of dummy data.

Our request or suggestion is for a custom connector to be developed for connections to Denodo - one that would be compatible with the products we already license from SAS, SAS 9.4 and Viya 3.5. It has been disappointing to see that despite having upgraded to M8, SAS' most up-to-date version of this platform technology, our issues persist. In fact, we discovered several new issues were introduced with the upgrade to M8 due to changes with Java. Additionally, while we would prefer that a custom connector be built to address the many small opportunities for optimization that a custom connector would offer, we would also be encouraged to see enhancements and bug fixes developed for the JDBC interface to address known issues. We have heard in past troubleshooting sessions with SAS support and the developers of the ACCESS interfaces that recent enhancements have been made to the ACCESS Interface to JDBC; however, none of these enhancements or bug fixes are available to us as they are only available for Viya 4.

[VincentRejany]

We have no plans for creating a dedicated SAS/ACCESS Interface to Denodo, as we don't have enough customer demand for justifying this development. Please work with Tech Support for raising the different issues, so the connectivity through SAS/ACCESS Interface to JDBC can be improved. Mind that there will be limitations according to what the Denodo JDBC driver can support.

[ferrickc]

Hi Vincent,

Thank you for the answer regarding a dedicated interface to Denodo.

Our understanding in working with SAS Tech Support and the developers of the ACCESS engines is that bug fixes and other enhancements have been made to the Interface to JDBC that might address some of the issues we are experiencing; however, these updates are only available for Viya 4. As we are currently using SAS 9.4 to connect to Denodo via JDBC, we would at least like to see these enhancements be released for Foundation SAS. Additionally, we have seen several new java-related issues impacting our JDBC connections following an upgrade to SAS 9.4 M8. As this is SAS' latest release of the SAS 9.4 software, we would expect that other products that would be impacted by these changes would also receive hotfixes. What is the best way to request hotfixes for known bugs and/or request that enhancements to the ACCESS Interfaces developed for Viya 4 also be made available for other SAS products?

Thanks,
Corey

[VincentRejany]

Hi Corey
Hotfixes have to be requested by Tech Support, and enhancements through SAS Communities Product Siggestions : https://communities.sas.com/t5/SAS-Product-Suggestions/idb-p/product-suggestions
Vincent

[rhmthomson1]

We currently dont have enough demand for a specific connector for Denodo, however we do still add fixes to JDBC and ODBC for Denodo specific issues.