5 ms·

SQL interface for Elasticsearch

76 points by taf2 11y ago

jermo 11y ago
No joins. I wonder why you wouldn't use PrestoDB to connect to Elastic Search. It provides you with an SQL engine and you just need to write a connector that knows how to get data. Similar thing has been done in Crate.io.
- rmsaksida 11y ago
  It does have joins. https://github.com/NLPchina/elasticsearch-sql/blob/5cd6ab63919bd4f366fc721638b31a54e3555b5c/src/test/java/org/nlpcn/es4sql/JoinTests.java https://github.com/NLPchina/elasticsearch-sql/blob/5cd6ab639...
  - deleted 11y ago
    [deleted]
  - lobster_johnson 11y ago
    Interesting — looks like the join isn't pipelined. The entire right-hand side evaluates synchronously. So it has to wait for the entire right-hand result set before it can evaluate the join operator, instead of streaming it concurrently. I'm surprised anyone would do it this way in Java, which has good support for concurrency. Edit: Actually the file you linked to was a test file. Hash join code is here [1], and it uses ES' scrolling feature to incrementally join, though it's not pipelined. Not sure scrolling is entirely appropriate for this; it will potentially hold an unpredictable amount of memory on the server end. [1] https://github.com/NLPchina/elasticsearch-sql/blob/5cd6ab63919bd4f366fc721638b31a54e3555b5c/src/main/java/org/elasticsearch/plugin/nlpcn/HashJoinElasticExecutor.java https://github.com/NLPchina/elasticsearch-sql/blob/5cd6ab639...
- gdulli 11y ago
  Joins are absent from the documented list of features and examples on the initial README but there's "limited support" for joins as described here: https://github.com/NLPchina/elasticsearch-sql/wiki/Join https://github.com/NLPchina/elasticsearch-sql/wiki/Join
- joefkelley 11y ago
  Don't have much experience with Presto, but I have used Hive to query Elastic Search. It works very very well for full-table-scan analytics-type queries. I expect PrestoDB would be similar. But when it comes to queries about smaller and smaller pieces of the full dataset, it becomes less and less likely that these types of connectors perform well. Predicate pushdown is rarely well-implemented in these types of "run SQL against any big data" systems (Hive, Presto, Impala, SparkSQL, etc). A simple "select * where id = 1234" will often do a full scan and filter within the query engine, rather than push the point lookup into ES.
  - rxin 11y ago
    Actually Spark SQL's data source API has a very expressive predicate pushdown interface and most data sources implement them. id = 1234 should not do a full scan.
  - threeseed 11y ago
    As mentioned Spark SQL has good predicate push down support. The ElasticSearch, Cassandra, HBase, MongoDB adapters all support it.
johnnymonster 11y ago
I don't get the point of this. Do people not want to learn ES so bad that they will use something like this without understanding how to build an ES query object? All this plugin does is convert sql to an ES query...
- dragonwriter 11y ago
  Not having to learn a different query language for every product is exactly the point of SQL, and naturally also the point of pretty much every "SQL interface for X".
  - threeseed 11y ago
    If only this was the case. Except that every database has their own flavour of SQL with plenty of proprietary extensions. Not to mention each database will support a different subset of SQL.
    - dragonwriter 11y ago
      > Except that every database has their own flavour of SQL with plenty of proprietary extensions. Not to mention each database will support a different subset of SQL. Its a lot easier to become familiar with a new SQL dialect than a new query language, especially since a lot of basic querying will be the same between different dialects.
      - threeseed 11y ago
        Learning the basics is easy with any query language eg. MongoDB/ES JSON. It's when you start writing more advanced queries that you realise that SQLs "write once, run anywhere" premise is an illusion.
    - hobs 11y ago
      Dont know why anyone is downvoting this, this is absolutely true, and its even worse because the subset might work differently with the same keywords due to the underlying optimizer differences. This makes code you wrote in two different engines with the same syntax run vastly differently. Only if you are writing basic queries is the ansi sql promise realized.
rxin 11y ago
If you use Spark SQL to query ElasticSearch as a data source, it already has better SQL support with joins and you can push predicates down into ES.
amai 11y ago
If you want real SQL on top of Elasticsearch have a look at https://crate.io/ https://crate.io/ (https://crate.io/blog/sql-for-elasticsearch/ https://crate.io/blog/sql-for-elasticsearch/). P.S.: I'm not affiliated with that company.

76 points by taf2 11y ago

jermo 11y ago

No joins. I wonder why you wouldn't use PrestoDB to connect to Elastic Search. It provides you with an SQL engine and you just need to write a connector that knows how to get data. Similar thing has been done in Crate.io.

rmsaksida 11y ago
It does have joins. https://github.com/NLPchina/elasticsearch-sql/blob/5cd6ab63919bd4f366fc721638b31a54e3555b5c/src/test/java/org/nlpcn/es4sql/JoinTests.java https://github.com/NLPchina/elasticsearch-sql/blob/5cd6ab639...
- deleted 11y ago
  [deleted]
- lobster_johnson 11y ago
  Interesting — looks like the join isn't pipelined. The entire right-hand side evaluates synchronously. So it has to wait for the entire right-hand result set before it can evaluate the join operator, instead of streaming it concurrently. I'm surprised anyone would do it this way in Java, which has good support for concurrency. Edit: Actually the file you linked to was a test file. Hash join code is here [1], and it uses ES' scrolling feature to incrementally join, though it's not pipelined. Not sure scrolling is entirely appropriate for this; it will potentially hold an unpredictable amount of memory on the server end. [1] https://github.com/NLPchina/elasticsearch-sql/blob/5cd6ab63919bd4f366fc721638b31a54e3555b5c/src/main/java/org/elasticsearch/plugin/nlpcn/HashJoinElasticExecutor.java https://github.com/NLPchina/elasticsearch-sql/blob/5cd6ab639...
gdulli 11y ago
Joins are absent from the documented list of features and examples on the initial README but there's "limited support" for joins as described here: https://github.com/NLPchina/elasticsearch-sql/wiki/Join https://github.com/NLPchina/elasticsearch-sql/wiki/Join
joefkelley 11y ago
Don't have much experience with Presto, but I have used Hive to query Elastic Search. It works very very well for full-table-scan analytics-type queries. I expect PrestoDB would be similar. But when it comes to queries about smaller and smaller pieces of the full dataset, it becomes less and less likely that these types of connectors perform well. Predicate pushdown is rarely well-implemented in these types of "run SQL against any big data" systems (Hive, Presto, Impala, SparkSQL, etc). A simple "select * where id = 1234" will often do a full scan and filter within the query engine, rather than push the point lookup into ES.
- rxin 11y ago
  Actually Spark SQL's data source API has a very expressive predicate pushdown interface and most data sources implement them. id = 1234 should not do a full scan.
- threeseed 11y ago
  As mentioned Spark SQL has good predicate push down support. The ElasticSearch, Cassandra, HBase, MongoDB adapters all support it.

johnnymonster 11y ago

I don't get the point of this. Do people not want to learn ES so bad that they will use something like this without understanding how to build an ES query object? All this plugin does is convert sql to an ES query...

dragonwriter 11y ago
Not having to learn a different query language for every product is exactly the point of SQL, and naturally also the point of pretty much every "SQL interface for X".
- threeseed 11y ago
  If only this was the case. Except that every database has their own flavour of SQL with plenty of proprietary extensions. Not to mention each database will support a different subset of SQL.
  - dragonwriter 11y ago
    > Except that every database has their own flavour of SQL with plenty of proprietary extensions. Not to mention each database will support a different subset of SQL. Its a lot easier to become familiar with a new SQL dialect than a new query language, especially since a lot of basic querying will be the same between different dialects.
    - threeseed 11y ago
      Learning the basics is easy with any query language eg. MongoDB/ES JSON. It's when you start writing more advanced queries that you realise that SQLs "write once, run anywhere" premise is an illusion.
  - hobs 11y ago
    Dont know why anyone is downvoting this, this is absolutely true, and its even worse because the subset might work differently with the same keywords due to the underlying optimizer differences. This makes code you wrote in two different engines with the same syntax run vastly differently. Only if you are writing basic queries is the ansi sql promise realized.

rxin 11y ago

If you use Spark SQL to query ElasticSearch as a data source, it already has better SQL support with joins and you can push predicates down into ES.

amai 11y ago

If you want real SQL on top of Elasticsearch have a look at https://crate.io/ https://crate.io/ (https://crate.io/blog/sql-for-elasticsearch/ https://crate.io/blog/sql-for-elasticsearch/). P.S.: I'm not affiliated with that company.