[BUG] Unable to run queries with DataGrip

[ksco92]

What is the bug?
After setting up the JDBC driver in DataGrip I get inconsistent errors. See reproduction steps for details.

How can one reproduce the bug?
Steps to reproduce the behavior:

• Download the JDBC driver here:
  • https://github.com/opensearch-project/sql-jdbc
• Open your IDE and open the "Database" section
• Click "+" > "Driver"
• Set the name to "OpenSearch"
• On the "Driver Files" section select the JAR you just downloaded
• In the class dropdown select org.opensearch.jdbc.Driver
• Click "Ok"
• Click "+" > "Data source" > "OpenSearch"
• In the "General" tab:
  • Set "Authentication" to "No auth"
  • Hostname to the OS cluster address
• In the "Advanced" tab set the following key/value pairs:
  • auth: AWS_SIGV4
  • region: The region the cluster is on
• Test connection
• Create a test index and put data in it

This is where the behaviors begin:

1234:hadesrodr...s-east-1> select * from hosts_rodrigof_metadata_hosts_2023-04-11
[2023-04-13 11:02:30] java.sql.SQLException
1234:hadesrodr...s-east-1> select * from hosts_rodrigof_metadata_hosts
[2023-04-13 11:08:29] java.sql.SQLException
1234:hadesrodr...s-east-1> select * from "hosts_rodrigof_metadata_hosts"
[2023-04-13 11:08:37] Error executing query
[2023-04-13 11:08:37] HTTP Code: 400. Message: Bad Request. Raw response received: {
[2023-04-13 11:08:37] "error": {
[2023-04-13 11:08:37] "reason": "Error occurred in OpenSearch engine: no such index [\"hosts_rodrigof_metadata_hosts\"]",
[2023-04-13 11:08:37] "details": "org.opensearch.index.IndexNotFoundException: no such index [\"hosts_rodrigof_metadata_hosts\"]\nFor more details, please send request for Json format to see the raw response from OpenSearch engine.",
[2023-04-13 11:08:37] "type": "IndexNotFoundException"
[2023-04-13 11:08:37] },
[2023-04-13 11:08:37] "status": 404
[2023-04-13 11:08:37] }
1234:hadesrodr...s-east-1> select * from "hosts_rodrigof_metadata_hosts_2023-04-11"
[2023-04-13 11:09:05] Error executing query
[2023-04-13 11:09:05] HTTP Code: 400. Message: Bad Request. Raw response received: {
[2023-04-13 11:09:05] "error": {
[2023-04-13 11:09:05] "reason": "Error occurred in OpenSearch engine: no such index [\"hosts_rodrigof_metadata_hosts_2023-04-11\"]",
[2023-04-13 11:09:05] "details": "org.opensearch.index.IndexNotFoundException: no such index [\"hosts_rodrigof_metadata_hosts_2023-04-11\"]\nFor more details, please send request for Json format to see the raw response from OpenSearch engine.",
[2023-04-13 11:09:05] "type": "IndexNotFoundException"
[2023-04-13 11:09:05] },
[2023-04-13 11:09:05] "status": 404
[2023-04-13 11:09:05] }

Where hosts_rodrigof_metadata_hosts_2023-04-11 is an index and hosts_rodrigof_metadata_hosts is an alias. However, using the Elasticsearch plugin, I can successfully run queries via HTTP:

POST /_plugins/_sql/
{
  "query": "select count(*) from hosts_rodrigof_metadata_hosts"
}

Returns: 
{
  "schema": [
    {
      "name": "count(*)",
      "type": "integer"
    }
  ],
  "datarows": [
    [
      12237733
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

Something I have noticed is that the use of * doesn't work, for example:

select * from my_index

Fails with java.sql.SQLException, but:

select col1 from my_index

Does work.

What is the expected behavior?
Queries should run as expected when using the JDBC driver.

What is your host/environment?

• OS: AWS
• Version 2.3
• Plugins: sql

[ksco92]

I have 6 clusters, 3 in us-west-2 and 3 in us-east-1, I have upgraded them all to 2.5. While the connection is successful, the above errors still happen. I also noticed that the driver reports the version of the cluster as OS 7.10

[Yury-Fridlyand]

@ksco92,
I guess version 7.10 is returned, because compatibility mode is on. See pic below from AWS console, from create new cluster menu:

I can't reproduce your error, but I keep playing with DataGrip
There are my Datasource settings:

Driver settings:

Note: I have to set env vars required for AWS authentication prior to running DataGrip:

Please, check your set up to ensure that nothing missed.

[Yury-Fridlyand]

Just discovered that you're using JDBC driver v.1.0. Please, download and try most recent one from maven.

[ksco92]

Hi! Can you please guide me through how you figured this out? I downloaded the driver specifically listed here:

https://opensearch.org/artifacts

And the AWS docs here say that driver version 1.1.0.1 goes with OS 2.5:

https://docs.aws.amazon.com/opensearch-service/latest/developerguide/sql-support.html

It even links to download that version directly:

https://artifacts.opensearch.org/opensearch-clients/jdbc/opensearch-sql-jdbc-1.1.0.1.jar

Does this mean that the version in maven is different and that docs should be corrected?

[Yury-Fridlyand]

Unfortunately, artifacts page still contains older version of JDBC. I'll update it soon.
Sorry about that.
Maven stores most recent version.

[ksco92]

I still seem to be having something weird going on:

Am I missing something embarrassing here?

[ksco92]

I also do have valid creds.

[Yury-Fridlyand]

Driver file on Maven has no embedded dependencies (no shadow jar) and it is supposed that application would download them automatically.
opensearch-sql-jdbc-1.2.0.0.zip
Try renaming this file to jar and using it.

I didn't noticed where I got my jar from, but it has embedded all dependencies in (shadow jar).

[Yury-Fridlyand]

Investigation outcomes:

@
JDBC driver 1.2, OpenSearch 2.5
@
DataGrid enforces driver to use fetchSize > 0

@
All requests to SQL plugin are paginated queries
@
Queries fall back to the legacy engine in the plugin
@
Legacy engine doesn't support queries without FROM clause. Pagination in general has also limited support.

Verdict:

• Support more pagination in SQL plugin -- work in progress, target: 2.8
• Fix in JDBC driver to disallow overwriting fetchSize if given
• Documentation update

[6ecuk]

Hi, just tried latest version opensearch-sql-jdbc-shadow-1.4.0.1.jar downloaded from
https://artifacts.opensearch.org/opensearch-clients/jdbc/opensearch-sql-jdbc-shadow-1.4.0.1.jar
and when try select nested object got an error
<failed to load> org.opensearch.jdbc.StructImpl