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.
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
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() DESCto 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
MATCHoperator likeSELECT … 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.
You could probably make some kind of parser, but then again I don't think that is really even a good idea, even if the parser is well-written. You got to remember Elasticsearch uses inverted indexes since it's based on Lucene. Querying it as you would query a relational database kind of defeats that logic, thus it isn't even clear if it would be of any use to use ElasticSearch, you probably better stick to pure SQL queries.
Also, given you currently have only 10 queries and you already plan on switching to ES, i'd strongly suggest to adapt those 10 requests into proper ES queries, switch to ES, and then only create new requests within the ES logic.
