12 ms·
Elasticsearch SQL
- xnx 4y agoThe next step is realizing that an SQL database is probably more than enough for most Elasticsearch use cases. See https://news.ycombinator.com/item?id=32059566 https://news.ycombinator.com/item?id=32059566
- coding123 4y agoI haven't used it but you could technically do this over spark in a bunch of round-about ways. But this would be more direct. more right out of the box.
- alexott 4y agoAs I remember it was introduced in 2018th: https://www.elastic.co/blog/an-introduction-to-elasticsearch-sql-with-practical-examples-part-1 https://www.elastic.co/blog/an-introduction-to-elasticsearch..., although there was an open source extension for that… Although I wasn’t very hard to implement it for a subset of queries like group by, etc. - primary problem was a good OSS sql parser. We used Presto back in 2016th for that
- synthc 4y agoSeems like all the NoSQL databases eventually implement SQL, maybe we should use EventuallySQL instead NoSQL
- datalopers 4y agoMongoDB took about 8 years to fully implement SQL features but they forced it into their god-awful syntax. At least Elastic stuck with SQL?
- jd_mongodb 4y agoMongoDB has an SQL interface these days if you need it https://www.mongodb.com/atlas/sql https://www.mongodb.com/atlas/sql
- tofuahdude 4y agoThat is absolutely hilarious
- chickenpotpie 4y agoA reminder that NoSQL stands for "not only SQL"
- ralusek 4y agoIn a practical sense what it really means is "non-RDBMS." Even more specifically what it means is "any of the database technologies that were created or popularized in the period following RDBMS dominance in order to circumvent the performance limitations RDBMS technologies encountered at internet scale." SQL just, unfortunately, was treated somewhat synonymously with RDBMS.
- jhardy54 4y agoThat’s a backronym. > NoSQL (originally referring to "non-SQL" or "non-relational") […] NoSQL systems are also sometimes called Not only SQL […] > — https://en.m.wikipedia.org/wiki/NoSQL https://en.m.wikipedia.org/wiki/NoSQL
- tofuahdude 4y agothats the joke!
- TotoHorner 4y agoWhat other prominent NoSQL databases eventually implemented SQL for queries?
- jedberg 4y agoCassandra added CQL which is very similar to but not exactly SQL.
- TotoHorner 4y agoAh interesting. Thanks.
- DeathArrow 4y agoCockroachDB
- vvern 4y agoI don't think it's fair to say it's a NoSQL database. It's very much a transactional, consistent SQL database.
- robertlagrant 4y agoMicrosoft's CosmosDB (their DocumentDB clone) has a SQL-like (yes) interface.
- wizwit999 4y agoEven DynamoDb has one https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html https://docs.aws.amazon.com/amazondynamodb/latest/developerg...
- nl 4y agoHadoop (and any other Spark data source) via SparkSQL
- abraxas 4y agoI’m conflicted on efforts like this. On the one hand SQL is for better or worse the Lingua Franca of database interfaces. On the other hand these half hearted, castrated implementations rarely work with tools geared towards working with true relational stores. The only real gain here is the surface level familiarity and of course the big fat check mark that the marketing team can put on the brochure to say “why, yes of course we support SQL!”
- showerst 4y agoHaving spent entirely too much of my life working with Elastic's annoyingly verbose and confusing JSON query syntax, the SQL is a big upgrade here even if it's not 1:1 with relational DBs.
- HyperSane 4y agoAnything is better than Elasticsearch's terrible JSON query language. I actually try to avoid it because of how painful querying it is.
- bearjaws 4y agoI look forward to a future where everything is SQL at this point. It seems we have spent a significant amount of effort avoiding it, but it time and time again is the best way to query your data. I recently started using Steampipe and it has made my life about 30x better not having to deal with the AWS or Slack API. Essentially a tool to let you query their APIs using SQL, behind the scenes it boots up a Postgres database and essentially does ETL into it. It doesn't do any updates to resources though, so back to the API again... https://steampipe.io/ https://steampipe.io/
- Beltalowda 4y agoThe biggest upshot of SQL is that it makes migrating from one system to the next a lot easier. I can write something against SQLite or PostgreSQL, and when it comes time to move to $something_else for a particular aspect for some reason I ... just can, without having to rewrite the lot. Of course, not all is perfect because no one actually supports ANSI SQL, and everyone has their own extensions and incompatibilities so you'll probably have to at least tweak some things. Besides, string concatenation isn't exactly a fantastic way to communicate with a database in the first place. But at this point it's pretty much the only thing we've got that's even vaguely universal.
- chasil 4y agoThere is plenty of lock-in with commercial databases, although the problem is improving. SQL/PSM originated with Oracle, and the ADA syntax is slowly creeping into many database products (but notably not Microsoft/Sybase). If you use PSM, then a number of database products are off the table. https://en.wikipedia.org/wiki/SQL/PSM https://en.wikipedia.org/wiki/SQL/PSM
- ilyt 4y ago> The biggest upshot of SQL is that it makes migrating from one system to the next a lot easier. I can write something against SQLite or PostgreSQL, and when it comes time to move to $something_else for a particular aspect for some reason I ... just can, without having to rewrite the lot. Meanwhile no matter how simple query I write I always manage to hit some difference between MySQL/SQLite/PostgreSQL... just recently I discovered SQLite/PostgreSQL have EXCEPT but MySQL does not. MySQL in particular is usually the problem
- cyberCleve 4y agoAs I recall, this is severely limited in that it does not correctly support sub-searches or pivoting between different indexes. Any news on if this has been improved?
- gavinray 4y agoJust as an FYI for folks, you could query Elasticsearch via SQL with Apache Calcite: https://calcite.apache.org/docs/elasticsearch_adapter.html https://calcite.apache.org/docs/elasticsearch_adapter.html
- pebcakID10T 4y agoIt's pretty cool for light work, but I imagine you would be better off doing native ES queries so when you need more complex queries, weighted searches, and optimizations, you will be in a better place to do that.
- jefurii 4y agoWhy the f*** couldn't they just build Elasticsearch with a SQL query language from the start? I hate their JSON query syntax and now that this is out I feel like I wasted all the time I spent making my applications work with it.
- fiedzia 4y agoYou can use ES as data storage, and in that case SQL makes sense. It was originally created as full-text search utility, and for that part SQL is not very useful.
- jrochkind1 4y agoI guess one question, then, is why the f** you couldn't just have used an rdmbs instead of ES from the start....
- deleted 4y ago[deleted]
- jefurii 4y agoWasn't my decision to go with it.
- eurasiantiger 4y agoBecause it’s Lucene under the hood.
- Thristle 4y agoThis was introduced in 6.x or even before that...
- bitsondatadev 4y agoTrino is a nice query engine solution if you want to not just run SQL on Elasticsearch but also want to be able to join data from Elasticsearch with data in other systems Trino supports. It also supports raw elasticsearch queries that are serialized back into Trino data types. https://trino.io/docs/current/connector/elasticsearch.html https://trino.io/docs/current/connector/elasticsearch.html
- anirudha81 4y agohttps://github.com/opensearch-project/sql/blob/2.x/docs/user/index.rst https://github.com/opensearch-project/sql/blob/2.x/docs/user... sql support as per MySQL Grammer. Please see the features and upcoming support for streaming sql with materialized views with s3 support
- anirudha81 4y agoI agree sql is almost always better, but heard that many new students and programmers would rather have a piped Grammer. Eg. Unixish https://github.com/opensearch-project/sql/blob/2.x/docs/user/ppl/index.rst https://github.com/opensearch-project/sql/blob/2.x/docs/user...
- snthpy 4y agoHave a look at PRQL (www.prql-lang.org)! That's exactly what it's aiming to provide.
- anirudha81 4y agowe looked at it recently, alas; we start the effort much before prql came up
- snthpy 4y agoWell PRQL sits on top of SQL so building a good SQL interface is the first step to supporting PRQL at this point. You can then easily add PRQL on top.
- starik36 4y agoI spent a ridiculous amount of time trying to get their ODBC driver to work as a linked server from SQL Server. I opened tickets and tested various versions. Finally I just gave up because I moved to a company that wasn't using ES. I wonder if they ever got the ODBC driver to work.
- amyjess 4y agoI would like SQL better if jOOQ-style libraries were common across every major language. A standardized query language is good, but passing in queries as strings will never not feel wrong to me.
- vladsanchez 4y agoLOL, it only took them 8yrs!? Been waiting for it too long, already abandoned ES! Thanks but, no thanks!