Automatic conversion of SQL query to ElasticSearch Query

Question

I have a service which currently stores data in Oracle DB. I am working on a project where I need to run a set of sql queries to get some aggregated data. I would want to store these queries at one place, which I can iterate over, and get the required data. Say, I have 10 queries today. But, I can keep adding more, without toching the code.

But, tomorrow we would want to switch to ElasticSearch. Is there a way, that i can use the same sql queries to search through even ElasticSearch.

Answer 1

You might want to look at this Elasticsearch plugin which aims at providing an SQL layer on top of Elasticsearch https://github.com/NLPchina/elasticsearch-sql

Answer 2

With Elasticsearch 6.3 released in June 2018, you might not need an "automatic conversion" anymore.

The 6.3 release comes with native SQL support! (still experimental for now)

Have you (or someone you know) ever:

• Said “I know how to do this thing in a SQL statement -- how do I do the same thing in Elasticsearch?”
• Tried to build out full-text search with tokenization, stemming, synonyms, relevance sorting on top of a SQL engine like a relational database?
• Tried to scale out a traditional database to billions of rows?
• Tried to connect a 3rd party tool like a BI system to Elasticsearch?

These are all things which we hope we can make inroads into our new Elasticsearch SQL release.

Our hope is to allow developers, data scientists, and others that are familiar with the SQL language -- but so far unfamiliar with or unable to use the Elasticsearch query language -- to use the speed, scalability, and full-text power that Elasticsearch offers and others have grown to know and love.

If you’re just getting started using this functionality or the power of Elasticsearch that powers it, here are a few things to try:

• SELECT … ORDER BY SCORE() DESC to be able to sort by the relevance of the search results
• Get all of the full-text magic from tokenization to stemming by using the MATCH operator like SELECT … WHERE MATCH(fieldname, 'some text')
• Connect your favorite JDBC-compatible tool to Elasticsearch with our JDBC driver
• Learn how to use the full power of the Elasticsearch DSL by translating a SQL query you know via the translate API

Note that this feature is made available in the “default” (non-OSS-only) distribution of Elasticsearch and the REST API -- including the “translate” functionality and the CLI tool are completely free.