3 ms·

I really wish they'd included the ElasticSearch SQL in 6.0, but I guess that feature still isn't fully baked. Even if it didn't have the full power of the Elas

by jdhawk 9y ago

I really wish they'd included the ElasticSearch SQL in 6.0, but I guess that feature still isn't fully baked. Even if it didn't have the full power of the Elastic JSON Queries, for simple SELECT COUNT() ..GROUP BY, it would have been a nice addition...oh well, back to counting open and closed brackets...

djhworld 9y ago
We're just in the process of looking into ElasticSearch for an analytics use case, most of the queries we will be doing are simple group by aggregations with count/sums etc A SQL interface would help a lot, even better if it came with a JDBC driver
- alexott 9y ago
  There is some ES integration in the Apache Calcite. And it has JDBC driver... P.S. Writing this level of SQL for ES that you describe isn't very difficult - in my project we got working in implementation in 2-3 weeks. Take Calcite. (Recommended but complex, imho), Facebook's Presto SQL parser (not recommended, but simpler)
praseodym 9y ago
Agreed! Using something like elasticsearch-dsl for Python [1] makes writing queries a little more bearable, but it's not a good fit for manual ad-hoc querying (e.g. some analysis I'd be doing from Kibana). https://github.com/elastic/elasticsearch-dsl-py https://github.com/elastic/elasticsearch-dsl-py
yehosef 9y ago
check out dremio.com - It's superduper Apache Drill with elasticsearch support (ie SQL with real joins.)
- CSDude 9y ago
  Dremio seemed very promising, but it failed more than half of my queries with ES
  - yehosef 9y ago
    You should reach out to them or post on their forums- they are supportive and I know ES is one of their priorities. I'd be interested to hear about what worked and what didn't. It's also important to try the 1.2 version. I had played with 1.1 and there were problems (not failures - just inefficient ES queries.)
rpedela 9y ago
Never used it but seems promising: https://github.com/NLPchina/elasticsearch-sql https://github.com/NLPchina/elasticsearch-sql
- CSDude 9y ago
  It works fine in most cases, you can opt for csv output and you get a tabular format as well, but not all queries return out what you expect, i.e ordering with group by's can be challenging
laichzeit0 9y ago
Yeah, interesting some companies have already created their own DSL that looks like SQL for ES, e.g. AppDynamics https://docs.appdynamics.com/m/mobile.action#page/42584918 https://docs.appdynamics.com/m/mobile.action#page/42584918 The data is mainly used for dashboards (which is Highcharts) so the aggregation functions map to something called a “series”, which is what you’d expect if you’ve ever used Highcharts. Anyway I think it’s quite cool how they did it.

by jdhawk 9y ago

djhworld 9y ago

We're just in the process of looking into ElasticSearch for an analytics use case, most of the queries we will be doing are simple group by aggregations with count/sums etc A SQL interface would help a lot, even better if it came with a JDBC driver

alexott 9y ago
There is some ES integration in the Apache Calcite. And it has JDBC driver... P.S. Writing this level of SQL for ES that you describe isn't very difficult - in my project we got working in implementation in 2-3 weeks. Take Calcite. (Recommended but complex, imho), Facebook's Presto SQL parser (not recommended, but simpler)

praseodym 9y ago

Agreed! Using something like elasticsearch-dsl for Python [1] makes writing queries a little more bearable, but it's not a good fit for manual ad-hoc querying (e.g. some analysis I'd be doing from Kibana). https://github.com/elastic/elasticsearch-dsl-py https://github.com/elastic/elasticsearch-dsl-py

yehosef 9y ago

check out dremio.com - It's superduper Apache Drill with elasticsearch support (ie SQL with real joins.)

CSDude 9y ago
Dremio seemed very promising, but it failed more than half of my queries with ES
- yehosef 9y ago
  You should reach out to them or post on their forums- they are supportive and I know ES is one of their priorities. I'd be interested to hear about what worked and what didn't. It's also important to try the 1.2 version. I had played with 1.1 and there were problems (not failures - just inefficient ES queries.)

rpedela 9y ago

Never used it but seems promising: https://github.com/NLPchina/elasticsearch-sql https://github.com/NLPchina/elasticsearch-sql

CSDude 9y ago
It works fine in most cases, you can opt for csv output and you get a tabular format as well, but not all queries return out what you expect, i.e ordering with group by's can be challenging

laichzeit0 9y ago

Yeah, interesting some companies have already created their own DSL that looks like SQL for ES, e.g. AppDynamics https://docs.appdynamics.com/m/mobile.action#page/42584918 https://docs.appdynamics.com/m/mobile.action#page/42584918 The data is mainly used for dashboards (which is Highcharts) so the aggregation functions map to something called a “series”, which is what you’d expect if you’ve ever used Highcharts. Anyway I think it’s quite cool how they did it.