Support for converting sql to Elasticsearch query on the client side itself

[newUserForTesting]

Is your feature request related to a problem? Please describe.
Our hundreds of Production Clusters are running on OpenDistro version of Elasticsearch. We recently observed the new OpenSearch initiative. We can't wait to upgrade to OpenSearch version!
But since we have lots of existing Production Clusters and enormous amount of data within it, it wouldn't be happening in immediate near future. This means we can't make use of all the fixes(some example includes fuzzy support, querying object in nested structures) which are coming in OpenSearch but not in OpenDistro.

Describe the solution you'd like
A possible solution for the above issue could be that on Application Side (client side), we use https://github.com/opensearch-project/sql which has latest features (and upcoming new features as well), convert our sql query to Elasticsearch query on the client side itself (without hitting Elasticsearch since its still on OpenDistro version), and then use the Elasticsearch query to send request to Elasticsearch. Could someone please guide which methods of this project we can invoke to achieve this? We really appreciate your help!

[chloe-zh]

Hi @newUserForTesting thanks for reaching out to us! Glad to hear that you and your team like our software and would like to migrate your product to OpenSearch.

For the solution you proposed, I'm not sure if this is workable to convert SQL query to Elasticsearch DSL from OpenSearch of the client side, since the DSL generated in the client side would be OpenSearch query DSL but might not be compatible with ES DSL at least at the query API level.

And I guess you might want the direct DSL script that is obtained from our explain endpoint, but actually this script is not exactly the same with the executable query DSL that used in search, but we have also included the in memory execution plan in the explanations.

So for now probably the best way so far to do it is to migrate the entire cluster to OpenSearch if possible. Will discuss it with the team to see what we can do to help and keep you updated. Thanks!

[chloe-zh]

Here is a procedure to upgrade ODFE cluster to OpenSearch: https://opensearch.org/docs/upgrade-to/upgrade-to/ Hope it helps you well!

[Yury-Fridlyand]

@newUserForTesting,
You can use explain request to get DSL query executed on the server without actual execution. Keep in mind, that SQL plugin does post-processing of the results, which is not reflected in the DSL query.