[HN Gopher] Elasticsearch SQL
___________________________________________________________________
Elasticsearch SQL
Author : vincent_s
Score : 87 points
Date : 2022-10-24 18:29 UTC (4 hours ago)
(HTM) web link (www.elastic.co)
(TXT) w3m dump (www.elastic.co)
| bitsondatadev wrote:
| 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.
|
| https://trino.io/docs/current/connector/elasticsearch.html
| abraxas wrote:
| 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!"
| showerst wrote:
| 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.
| cyberCleve wrote:
| 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?
| Thristle wrote:
| This was introduced in 6.x or even before that...
| alexott wrote:
| As I remember it was introduced in 2018th:
| 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
| anirudha81 wrote:
| 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
| jefurii wrote:
| 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.
| jrochkind1 wrote:
| I guess one question, then, is why the f** you couldn't just
| have used an rdmbs instead of ES from the start....
| [deleted]
| synthc wrote:
| Seems like all the NoSQL databases eventually implement SQL,
| maybe we should use EventuallySQL instead NoSQL
| datalopers wrote:
| MongoDB 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 wrote:
| MongoDB has an SQL interface these days if you need it
|
| https://www.mongodb.com/atlas/sql
| tofuahdude wrote:
| That is absolutely hilarious
| chickenpotpie wrote:
| A reminder that NoSQL stands for "not only SQL"
| ralusek wrote:
| 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.
| jhardy54 wrote:
| That'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
| tofuahdude wrote:
| thats the joke!
| TotoHorner wrote:
| What other prominent NoSQL databases eventually implemented SQL
| for queries?
| DeathArrow wrote:
| CockroachDB
| vvern wrote:
| I don't think it's fair to say it's a NoSQL database. It's
| very much a transactional, consistent SQL database.
| jedberg wrote:
| Cassandra added CQL which is very similar to but not exactly
| SQL.
| TotoHorner wrote:
| Ah interesting. Thanks.
| robertlagrant wrote:
| Microsoft's CosmosDB (their DocumentDB clone) has a SQL-like
| (yes) interface.
| xnx wrote:
| The 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
| anirudha81 wrote:
| I 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...
| bearjaws wrote:
| 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...
|
| https://steampipe.io/
| ransom1538 wrote:
| 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.
| ignoramous wrote:
| > _...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.
| dataminded wrote:
| What query languages have you found that are better than SQL?
|
| [edit] Typo.
| sqlcommando wrote:
| 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.
| Beltalowda wrote:
| 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.
| ilyt wrote:
| chasil wrote:
| There 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
| deckard1 wrote:
| > 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.
|
| [1] https://youtu.be/MO0r930Sn_8?t=368
| stanislavb wrote:
| And improving your SQL skills is almost always a good choice.
| No-matter what tech-role you hold at the moment.
| munk-a wrote:
| 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.
| matthewaveryusa wrote:
| 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.
| deeebug wrote:
| Unsure if it was added recently, but Splunk Query Language
| does have VAL IN (1,2,3) now -> https://docs.splunk.com/Docum
| entation/Splunk/9.0.1/SearchRef...
| Philip-J-Fry wrote:
| Splunk 7 added that from what I can find. 5 years old at
| least.
| munk-a wrote:
| I've been using it for quite a while.
| yarg wrote:
| The syntax is still backward though.
|
| > SELECT blah FROM source
|
| vs
|
| > FROM source SELECT blah
|
| Fixing this would permit some very useful developer features.
| BeefySwain wrote:
| Is this the part where I mention that trailing commas should
| be permitted?
| munk-a wrote:
| 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.
| slickrick216 wrote:
| It's because it's streaming I'd bet so really just a wrapper
| on;
|
| index=blah | fields foo
| coding123 wrote:
| 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.
| starik36 wrote:
| 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.
| gavinray wrote:
| Just as an FYI for folks, you could query Elasticsearch via SQL
| with Apache Calcite:
|
| https://calcite.apache.org/docs/elasticsearch_adapter.html
| pebcakID10T wrote:
| 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.
___________________________________________________________________
(page generated 2022-10-24 23:00 UTC)