Support for Elastic Search Parameter in opendistro-for-elasticsearch / sql-jdbc

[happycujo]

Hi,

You guys have done a really great work with the JDBC connector! It is working really great!

We would like to add to our SQL query some parameters that are specific to Elastic Search. Specifically, we would need to use the ignore_unavailable elastic parameter since we are requesting multiples indices that some may be missing.

As example, the following Elastic Search query should return without error even if the index query-01 is not existing.

{{HOST}}/query-00,query-01/_search?ignore_unavailable=true

Is there a way to do this with opendistro-for-elasticsearch /sql-jdbc connector?

If not, could you consider this as an official feature request? :)

Any help on this would be greatly appreciated!

Regards

[dai-chen]

Hi @happycujo , probably you could try our query with index pattern. Reference: https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/dql/basics.rst#example-2-selecting-from-multiple-indices-by-index-pattern. For example in your case, SELECT * FROM query-0*.

[happycujo]

Hello @dai-chen,

Thanks you very much for your time! Your suggestion is a good idea, but it may not work for our use case.

We named our indices to separate data over time, such as data-hour-0 up to data-hours-24. A user will search between a specific time (for instance, from data-hour-3 to data-hour-12), but we do not want the search to fail if any index in between is not created (data-hour-6 for instance). I am under the impression that if we use wildcard, we will hit indices that are no intended to be searched(0-3, 13-24)

The query in elastic format will look like this:

{{HOST}}/data-hour-3,data-hour-4,data-hour-5,data-hour-6,data-hour-7,data-hour-8,data-hour-9,data-hour-10,data-hour-11,data-hour-12/_search?ignore_unavailable=true

where data-hour-6 is not existing in the Elastic.

Do you think there is any way to use the wildcard to work for our need?

Thanks and regards

[happycujo]

The code seems to indicate that ignore_unavailable is supported using a Hint mechanism in the SQL. However, I could not found documentation actually explaining how to use it.

https://github.com/opendistro-for-elasticsearch/sql/blob/master/src/main/java/com/amazon/opendistroforelasticsearch/sql/domain/hints/

/**
 * Created by Eliran on 29/8/2015.
 */
public enum HintType {
...
    IGNORE_UNAVAILABLE,
...
}

/**
 * Created by Eliran on 5/9/2015.
 */
public class HintFactory {

    private static final String PREFIX = "! ";

    public static Hint getHintFromString(String hintAsString) throws SqlParseException {

        if (hintAsString.startsWith("! IGNORE_UNAVAILABLE")) {
            return new Hint(HintType.IGNORE_UNAVAILABLE, null);
        }

[happycujo]

According to this code, It seems that the hints are retrieved from SQL comment.

https://github.com/alibaba/druid/blob/master/src/main/java/com/alibaba/druid/sql/ast/SQLCommentHint.java

public class SQLCommentHint extends SQLObjectImpl implements SQLHint { I made a sample program to test this, but alas without success:

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

      st.executeQuery("SELECT /*! IGNORE_UNAVAILABLE*/ * FROM data-hour-3,data-hour-3-bad");

      con.close();

      System.out.println("Completed");
    }
    catch(SQLException e)    {
      e.printStackTrace();
    }

The program ended with an exception:

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 OpenDistroTest.main(OpenDistroTest.java:26)
Caused by: com.amazon.opendistroforelasticsearch.jdbc.protocol.http.HttpException: HTTP Code: 400. Message: Bad Request. Raw response received: {
  "error": {
    "reason": "Invalid SQL query",
    **"details": "no such index [data-hour-3-bad]",**
    "type": "IndexNotFoundException"
  },
  "status": 400
}
        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)

My interpretation is likely wrong how to pass hint to opendistro. Any help on how to make this work would be greatly appreciated.

Thanks and regards

[dai-chen]

@happycujo Yeah, the hint comes from our very old code. Because most are related to internal implementation, we didn't cover it in documentation. Let me check if it is still working. Thanks!

[happycujo]

@dai-chen Great, let me know if you can make it work! We would love to use this feature, even if it came from dusty old code! ;)

[dai-chen]

Hi @happycujo I just tried that hint. But unfortunately our code will try to fetch index metadata for verification before sending request with ignore_available option. In this case I think only index pattern I posted can help a little bit. We will think about how to support this feature better later but I'm afraid there won't be improvement for this soon.

[happycujo]

Hi @dai-chen. Thanks for taking the time to make this verification! The result is unfortunate, but i am glad you have taken the time to verify.

As this is a roadmap feature request , do you wish i close this it, or keep it open it for visibility?

[dai-chen]

@happycujo thanks for your understanding! I will take care of this issue.