Issue when running sql with elasticsearch

Plugins SQL
1

Hi,

First of all I want to apologize for this question, I am a novice with opendistro-sql-jdbc and ElasticSearch.
I am using the following version: opendistro-sql-jdbc: 0.7.0.0
ES Cluster: AWS ElasticSearch, version 6.8.
Exception thrown:
Exception in thread “main” java.sql.SQLException: Error executing query
at com.amazon.opendistroforelasticsearch.jdbc.StatementImpl.executeQueryRequest(StatementImpl.java:84)
at com.amazon.opendistroforelasticsearch.jdbc.StatementImpl.executeQueryX(StatementImpl.java:61)
at com.amazon.opendistroforelasticsearch.jdbc.StatementImpl.executeQuery(StatementImpl.java:54)
at com.test.RunJDBC.run(RunJDBC.java:37)
at com.test.RunJDBC.main(RunJDBC.java:18)
Caused by: com.amazon.opendistroforelasticsearch.jdbc.protocol.http.HttpException: HTTP Code: 503. Message: Service Unavailable. Raw response received: {
“error”: {
“reason”: “There was internal problem at backend”,
“details”: “class com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr cannot be cast to class com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr (com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr and com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr are in unnamed module of loader java.net.FactoryURLClassLoader @4086d8fb)”,
“type”: “ClassCastException”
},
“status”: 503
}
at com.amazon.opendistroforelasticsearch.jdbc.protocol.http.JsonHttpResponseHandler.checkResponseForErrors(JsonHttpResponseHandler.java:83)
at com.amazon.opendistroforelasticsearch.jdbc.protocol.http.JsonHttpResponseHandler.handleResponse(JsonHttpResponseHandler.java:52)
at com.amazon.opendistroforelasticsearch.jdbc.protocol.http.JsonHttpResponseHandler.handleResponse(JsonHttpResponseHandler.java:45)
at com.amazon.opendistroforelasticsearch.jdbc.protocol.http.JsonHttpProtocol.execute(JsonHttpProtocol.java:88)
at com.amazon.opendistroforelasticsearch.jdbc.StatementImpl.executeQueryRequest(StatementImpl.java:72)
… 4 more

Question is: What is the issue? What should I do to fix it?

2

Hi @Deevvi it looks like some errors occurred due to incorrect data type. Could you provide some query and data examples so that we can try to reproduce this issue locally?

3

Hi!

Thank you @chloe-zh for looking into this!
Query I run is SELECT id FROM <index_name>
Mapping for index is:
document
id → keyword

4

Hi @Deevvi Im not able to reproduce the same issue, and it looks fine with the jdbc driver v0.7 connected my Amazon Elasticsearch Service v6.8 instance. The test data looks like:

PUT testdata/testdata/_mapping
{
  "properties": {
    "id": {
      "type": "keyword"
    },
    "name": {
      "type": "text"
    }
  }
}

PUT testdata/_bulk
{"index":{"_type": "testdata", "_id":"1"}}
{"id": "11", "name": "amy"}
{"index":{"_type": "testdata", "_id":"2"}}
{"id": "12", "name": "bob"}

And the jdbc works fine to resolve the keyword type.