Issue when integrate opensearch sql-jdbc with apache-drill

Plugins SQL
1

I am using the * opensearch-sql-jdbc-1.1.0.1.jar downloaded from Project Artifacts · OpenSearch. And configured it as a new drill storage plugin to allow apache-drill to run SQL queries against my OpenSearch cluster.

The connection and authentication are fine when I configure the URL, username, and password correctly. But then I got blocked by this error message:

Please retry: Unable to configure data source: Failed to initialize pool: read-only mode can not be disabled.

Below is the screenshot of the apache-drill storage plugin configuration and the error message:

image
image1856×828 87.4 KB

It’s the exception raised from sql-jdbc/src/main/java/org/opensearch/jdbc/ConnectionImpl.java

195     @Override
196     public void setReadOnly(boolean readOnly) throws SQLException {
197         if (!readOnly)
198             throw new SQLNonTransientException("read-only mode can not be disabled.");
199     }

Does anyone have any idea to bypass this and let the apache-drill load the OpenSearch sql-jdbc and talk to OpenSearch?

Thanks!

2

Hi @MartinQ !

Can you set "readonly" : true in your configuration? JDBC driver is currently readonly.

If you do need write support, could you comment on this ticket?

Max

3

Thanks, @MaxKsyunz for the reply. Actually, I don’t need write support for now.

Is it because apache-dirll initializes the OpenSearch sql-jdbc driver with some value other than true to the method setReadOnly(), and then causes that function to throw out an error? I am seeking if there’s a way to work around this from OpenSearch sql-jdbc side.

And I tried with "readOnly": true or "readonly": true with the config, but no luck.

4

I don’t think you can work around this from the driver side, but I’ll investigate.

It will take me a while to get back to you – I am away for a week starting tomorrow.

In the meantime, try raising the question on GitHub.

5

Hi @MaxKsyunz , thanks again for helping with this issue, really appreciated it!

I’ve figured out from the drill source code that the newer version of the release(1.20.0 and above) has the configuration named writable. When I set it to false it forces the connection to OpenSearch with read-only mode, and the connection is successful now.
My version was 1.19.0(which was provided by the official boot start tutorial), and just the next version 1.20.0 has the writable configuration introduced.

Now I’ve unblocked the connection configuration. And when I start issuing the SQL queries, I got a bunch of errors. One thing I would like to confirm with you before I throw out all the errors here is that, is the opensearch-sql-jdbc-1.1.0.1.jar(from the artifacts website) the latest version? From the Github tags, I can see this version number(1.1.0.1) is pretty old compared to the latest tag(1.13.2.0). Do you host the latest jar file for the opensearch-sql-jdbc anywhere else?

Another question was: is there a way to build the opensearch-sql-jdbc jar file from source code? Sorry for this dummy question. I am new to java and gradle. Instead of investing time to learn how they are works, I am wondering if there’s a quick start for me to run a bunch of commands directly against the source code to build the opensearch-sql-jdbc jar file and test it out quickly.

Thank you!

6

Hi @MartinQ! I was away on vacation, I hope the following is still relevant to you.

Yes, it does look like the website does not have the latest JDBC driver. I’ll see if I can find out what’s happening there.

You can get the latest JAR file from GitHub Actions. For example, this one is the latest 1.3.4.0 JDBC driver and this one is the latest build from the main branch.

1 Like
7

Thank you @MaxKsyunz. I will take those JAR files for a quick test and update this thread with the results.

1 Like
8

@MartinQ what were your results?