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.
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.
> 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.
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/