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
Re:Elasticsearch SQL
by ralusek on Monday October 24, 2022 @03:14PM
(#33321089)
In 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.
Re:Elasticsearch SQL
by coding123 on Monday October 24, 2022 @03:02PM
(#33320888)
I 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.
Re:Elasticsearch SQL
by abraxas on Monday October 24, 2022 @03:11PM
(#33321038)
I’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!”
Re:Elasticsearch SQL
by showerst on Monday October 24, 2022 @03:30PM
(#33321315)
Having 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.
Re:Elasticsearch SQL
by bearjaws on Monday October 24, 2022 @03:18PM
(#33321143)
I 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...
Re:Elasticsearch SQL
by Beltalowda on Monday October 24, 2022 @03:27PM
(#33321281)
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.
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.
Re:Elasticsearch SQL
by deckard1 on Monday October 24, 2022 @06:49PM
(#33323674)
> at least tweak some things
That's really understating the problem. Oracle, for example, can be wildly different from MySQL or PostgreSQL. SQLite makes selects extremely cheap, so cheap, that if you try to do a straight migration to a server-based DB it will probably just choke. Assuming any devs on your team realized the benefits of SQLite and took advantage of them.
But it gets worst with SQL on things that are not relational DBs. The exact person that SQL-on-Elasticsearch is designed for is also going to be the person that won't know the idiosyncrasies of Elasticsearch well enough to know when they are in trouble.[1] They will end up trying to get explanations for what is going on in Elasticsearch in terms of what they know (i.e. SQL on a relational DB). The end result will then be confusion and ultimately re-learning Elasticsearch from the ground up.
Re:Elasticsearch SQL
by munk-a on Monday October 24, 2022 @04:24PM
(#33322178)
I'd really like to see trailing commas and I think in most cases they wouldn't actually break with standards requirements. I can accept that I don't have a perfect knowledge of SQL and may be wrong, but I believe `, FROM` `, WHERE` `, LIMIT` would all be pretty easy to detect.
Re:Elasticsearch SQL
by ignoramous on Monday October 24, 2022 @04:16PM
(#33322023)
> ...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.
It isn't that SQL is the best way (that these solutions keep gravitating towards it), it is just that SQL is pretty much the industry standard at this point. And supporting it is a predictable and usual ploy to expand userbase further.
More so for the fact that ClickHouse continues to threaten seemingly a critical portion of the business of both Snowflake and Elastic.
Re:Elasticsearch SQL
by sqlcommando on Monday October 24, 2022 @05:29PM
(#33322889)
https://learn.microsoft.com/en-us/azure/data-explorer/kusto/... gets linked here from time to time, along with this similar one https://github.com/prql/prql. I'm not sure either one claims SQL parity at this point but one thing they each have going for them is queries start with the entities, rather than the columns, which makes type-ahead completion more natural.
I think if you were going to do SQL over you would probably do it that way. SQL is definitely not perfect or optimal, just ubiquitous.
Re:Elasticsearch SQL
by techdragon on Monday October 24, 2022 @09:31PM
(#33325053)
Datalog https://en.wikipedia.org/wiki/Datalog , it’s a prolog “subset” and is a lot like SQL in the “query by way of a serious of desired parameters reducing the result set” and also not at all handcuffed to the relational paradigm… so its basically relevant as a query language anywhere you have data… relational db, graph db, document db. Datalog doesn’t care.
Re:Elasticsearch SQL
by munk-a on Monday October 24, 2022 @04:21PM
(#33322138)
Nothing could quite match my disappointment when I realized that Splunk (a large scale logging storage and retrieval product) decided to create a Splunk Query Language and eschew SQL... except maybe when I realized that they had no equivalent of `val IN(1,2,3...)` instead requiring `val = 1 OR val = 2 OR val = 3 ...`
I have taught several non-techy business analyst type people how to write SQL during my years of employment - it really isn't that hard when most of the time it's just "SELECT what you want FROM where you want it" and teaching more complex concepts when and if they come up.
And when those more complex concepts do come up and are too difficult to teach (or you want to lower the barrier of entry), you can always create a view that reduces the complexity back down to a select from.
Re:Elasticsearch SQL
by ransom1538 on Monday October 24, 2022 @04:30PM
(#33322270)
Put me at any company. Give me a mysql/oracle/etc replica to look at tables with SQL. I will understand your business pretty quickly. Give me a "key/value" store, i know nothing -- until i read the code.
Re:Elasticsearch SQL
by cyberCleve on Monday October 24, 2022 @03:30PM
(#33321326)
As 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?
Re:Elasticsearch SQL
by pebcakID10T on Monday October 24, 2022 @03:54PM
(#33321698)
It'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.
Re:Elasticsearch SQL
by jefurii on Monday October 24, 2022 @04:07PM
(#33321888)
Why 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.
Re:Elasticsearch SQL
by fiedzia on Wednesday October 26, 2022 @08:05AM
(#33342444)
You 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.
Trino 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.
Re:Elasticsearch SQL
by starik36 on Monday October 24, 2022 @06:00PM
(#33323201)
I 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.
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
https://www.mongodb.com/atlas/sql
https://docs.aws.amazon.com/amazondynamodb/latest/developerg...
> 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
SQL just, unfortunately, was treated somewhat synonymously with RDBMS.
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/
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.
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
That's really understating the problem. Oracle, for example, can be wildly different from MySQL or PostgreSQL. SQLite makes selects extremely cheap, so cheap, that if you try to do a straight migration to a server-based DB it will probably just choke. Assuming any devs on your team realized the benefits of SQLite and took advantage of them.
But it gets worst with SQL on things that are not relational DBs. The exact person that SQL-on-Elasticsearch is designed for is also going to be the person that won't know the idiosyncrasies of Elasticsearch well enough to know when they are in trouble.[1] They will end up trying to get explanations for what is going on in Elasticsearch in terms of what they know (i.e. SQL on a relational DB). The end result will then be confusion and ultimately re-learning Elasticsearch from the ground up.
[1] https://youtu.be/MO0r930Sn_8?t=368
> SELECT blah FROM source
vs
> FROM source SELECT blah
Fixing this would permit some very useful developer features.
index=blah | fields foo
prql-lang.org
It isn't that SQL is the best way (that these solutions keep gravitating towards it), it is just that SQL is pretty much the industry standard at this point. And supporting it is a predictable and usual ploy to expand userbase further.
More so for the fact that ClickHouse continues to threaten seemingly a critical portion of the business of both Snowflake and Elastic.
I think if you were going to do SQL over you would probably do it that way. SQL is definitely not perfect or optimal, just ubiquitous.
I have taught several non-techy business analyst type people how to write SQL during my years of employment - it really isn't that hard when most of the time it's just "SELECT what you want FROM where you want it" and teaching more complex concepts when and if they come up.
https://calcite.apache.org/docs/elasticsearch_adapter.html
https://trino.io/docs/current/connector/elasticsearch.html
sql support as per MySQL Grammer.
Please see the features and upcoming support for streaming sql with materialized views with s3 support
https://github.com/opensearch-project/sql/blob/2.x/docs/user...
You can then easily add PRQL on top.
I wonder if they ever got the ODBC driver to work.
A standardized query language is good, but passing in queries as strings will never not feel wrong to me.