Writing a generic JDBC connector that supports ANSI SQLs

[olumo]

Hi there,
I am evaluating Dremio and trying to write a generic JDBC connector that will allow me to query relational databases that support ANSI SQLs. In the Data Source creation form, I will have the jdbc url and the driver (which is available in the 3rdParty folder in dremio jars). I will also have the user name and password authentication as a start to see if this works. My questions are:

1. What should the ARP YAML file contain since I want the connector to be generic? (data types, relational algebra, etc.)
2. Is it enough to construct the jdbc connection url from the input field?
3. What is the drawback of having a generic connector?
4. Since I only want to access relational databases that support ANSI SQL, will the predicate pushdown still work in dremio.

Any insights on achieving this will be highly appreciated.

[ben]

Hello @olumo,

Though this may not answer all your questions, we have a tutorial/example of developing a JDBC connector with ARP YAML files: https://www.dremio.com/tutorials/how-to-create-an-arp-connector/

On your last point, many ANSI SQL expressions will get pushed down into the source.

[olumo]

@ben, thanks for the reply. I have already done that tutorial and managed to get the sqlite connector running. However, I am trying to make the connector generic. If I specify only the JDBC driver to be used, can Dremio get the data types and functions from the provided driver?

If the answer to the question above is No, is there a way to write generic ARP file?

[patricker]

@olumo I don’t have a definitive answer to your question, but I have spent a fair amount of time diving into the closed-source innards of the ARP JDBC adapter source code.

Go grab a program like JD-GUI and start digging around the custom ARP adapter that Dremio implemented for MS SQL (which is how they are able to set all the custom character ENCODING stuff), which they defined through Java code instead of through a YAML file.

Of course, you can only use the code you see as a guide, license wise it’s off limits from copy/paste or modification.

The code does not pass many unused classes around. I wanted to run some pre/post query commands, but was locked out because of how it’s coded; and since Dremio will not open source the ARP JDBC driver, there isn’t any path forward for many use cases.

I really like the concept behind the ARP adapter, I just wish it was a lot more flexible (like if it was open source…) Some of my previous posts on this topic:

Confusing ARP JDBC Error Message

I’ve been seeing a JDBC error recently, “Query cancelled by user”. I’ve seen it from both MS SQL and Snowflake. In my query I’m joining several datasets together from different platforms, so Dremio is running parts of the query in parallel, distributing the data across the provisioned cluster, and doing joins. When I look at the log, it looks like what actually happens is that a previous Hash Distribution step fails, and so Dremio properly tells other steps happening in parallel to cancel, bu…

MSSQL Connector Incorrectly Plans/Casts Boolean Operations

If a MS SQL Table contains a bit type column, and you try to query it using column_name = 1 then the query planner translates this to, WHERE [column_name]. This is invalid syntax for MS SQL, so it throws the error: An expression of non-boolean type specified in a context where a condition is expected If I try use Dremi’s internal functions to cast it to integer, CAST(column_name AS int) = 1 I get the error: Cast function cannot convert value of type BOOLEAN to type INTEGER I found that …

Enhancement Request - ARP JDBC

We’ve had a lot of success using the ARP JDBC plugin framework. We’d like to be able to make pre/post SQL calls whenever a connection is retrieved/returned to the connection pool; if we had to override JdbcConf.newPlugin and provide an extended version of JdbcStoragePlugin that would be fine. We’d also like to have access to the querying user at this scope, so we could set session parameters on the database. These querying user details are outside the scope of dataset security, that we could ac…

[olumo]

Apologies for the late reply @patricker. Thanks for your information! I did make some progress to write a generic connector which has input fields such as JDBC class name, JDBC Driver to use (must be present in the 3rdParty folder), user name, and password. It worked for some databases and didn’t work for others. It was a lot of hassle and I came to the conclusion that it’s not a sustainable or scalable way to solve our problem.

In the end, we went with Apache Drill which does what we really needed. We just needed a library that can treat our data sources as relational tables and allows us to do joins across them. Although Apache Drill configurations can be cumbersome, it fits our needs. Dremio comes with a lot of features that we don’t really need (i.e. bloated for our simple use case).

Nevertheless, thanks for the links!