Opendistro Sql JDBC driver

Plugins SQL
1

Hi Everyone,

I hope Opendistro sql jdbc driver is free .Please help to suggest how we can install and use this driver.
Also I need to apply joins on multiple (two) indexes with common field and create new index etc.So for that do i need to install sql jdbc driver before that?

Thanks
Sarvendra

2

Yes, Opendistro SQL JDBC Driver is free and open source under Apache License 2.0.
Please refer the documentation about how to use and install it.
You can query data with SQL without JDBC driver by sending request to _opendistro/_sql URI. More example in here.

3

@penghuo Thanks for reply.
i am trying to import index table present in opendsitro in some third party application using below connection url but getting error

jdbc:elasticsearch://https://192.168.105.15:9200
username- admin
password- admin

ERROR MESSAGE: SQLException: Connection error com.amazonaws.opendistro.elasticsearch.sql.jdbc.shadow.org.apache.http.conn.HttpHostConnectException: Connect to 192.168.105.15:9200 [/192.168.105.15] failed: Connection refused: connect Failed to connect and read from SQL database with connection_url: jdbc:elasticsearch://192.168.105.15:9200

The connection url mentioned in your documention is below .What is property-key values here.Please confirm if my above connection url is correct?

jdbc:elasticsearch://https://192.168.105.15:9200/[/context-path]?[property-key=value]&[property-key2=value2]..&[property-keyN=valueN]

4

@penghuo
Also in the documentation link(GitHub - amazon-archives/sql-jdbc: 🔍 Open Distro for Elasticsearch JDBC Driver) you shared there is no link to download jdbc jar file.

I have downloaded jar file from link(Download opendistro-sql-jdbc JAR file with all dependencies).Is this correct one?

5

Try to reproduce the issue.

  1. Follow the link to setup and the cluster
  2. Try to connect the Elasticsearch with following sample code. It works successfully.
        String url = "jdbc:elasticsearch://https://127.0.0.1:9200";
        Properties properties = new Properties();
        properties.put("trustSelfSigned", "true");
        properties.put("user", "admin");
        properties.put("password", "admin");

        Connection con = DriverManager.getConnection(url, properties);
        Statement st = con.createStatement();
6

Hi @sarvendras, here is the official site: Redirecting to /for-elasticsearch/. You can also find our JDBC driver in Maven repository to download there or add it to your Maven/Gradle project: https://mvnrepository.com/artifact/com.amazon.opendistroforelasticsearch.client/opendistro-sql-jdbc

7

@daichen Thanks a lot for your kind reply…

now connection is successful with third party application using below connection url.Elasticsearch index table is imported but one issue is still there that only schema of index is imported but records(rows) of the index are not imported…

jdbc:elasticsearch://https://127.0.0.1:9200?trustSelfSigned=true

Thanks
Sarvendra

8

Hi @sarvendras, I’m not sure in which application/database tool (such as DBVisualizer) you’re using our JDBC driver. But you should be able to query your data if you’ve connected and see index schema. Please refer to its docs to see if any miss.

9

Hi @penghuo,

Does Open Distro SQL JDBC work with regular ElasticSearch v7.4 as well?
I am wondering if I can use this for AWS’s ElasticSearch service.

Thanks.

10

Yes, Open Distro SQL JDBC works with Elasticsearch 7.4 with Open Distro SQL Plugin installed.

11

@daichen using JDBC driver can we import complete data of ES index table or we can only see schema only in external application?