4 ms·

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

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/ https://steampipe.io/

Beltalowda 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. 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 ago
  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 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
- deckard1 4y ago
  > 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 https://youtu.be/MO0r930Sn_8?t=368
yarg 4y ago
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 4y ago
  Is this the part where I mention that trailing commas should be permitted?
  - munk-a 4y ago
    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 4y ago
  It’s because it’s streaming I’d bet so really just a wrapper on; index=blah | fields foo
- snthpy 4y ago
  Have a look at PRQL! prql-lang.org
ignoramous 4y ago
> ...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 4y ago
  What query languages have you found that are better than SQL? [edit] Typo.
  - sqlcommando 4y ago
    https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query 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 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.
  - techdragon 4y ago
    Datalog https://en.wikipedia.org/wiki/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. For anyone with a python background, you can see a good example of (and experiment with) using Datalog style “logic programming” to query existing SQL databases, by checking out the pyDatalog package https://sites.google.com/site/pydatalog/ https://sites.google.com/site/pydatalog/ … keep in mind the level of abstraction in this library, it’s a great place to get familiar with what you could get from Datalog, but working with a database that natively supported Datalog, and had a Datalog query compiler/optimiser would be even more powerful.
munk-a 4y ago
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.
- deeebug 4y ago
  Unsure if it was added recently, but Splunk Query Language does have VAL IN (1,2,3) now -> https://docs.splunk.com/Documentation/Splunk/9.0.1/SearchReference/Search#4._Using_the_IN_operator https://docs.splunk.com/Documentation/Splunk/9.0.1/SearchRef...
  - Philip-J-Fry 4y ago
    Splunk 7 added that from what I can find. 5 years old at least.
    - munk-a 4y ago
      I've been using it for quite a while.
- matthewaveryusa 4y ago
  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.
ransom1538 4y ago
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.
stanislavb 4y ago
And improving your SQL skills is almost always a good choice. No-matter what tech-role you hold at the moment.

Beltalowda 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. 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 ago
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 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
deckard1 4y ago
> 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 https://youtu.be/MO0r930Sn_8?t=368

yarg 4y ago

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 4y ago
Is this the part where I mention that trailing commas should be permitted?
- munk-a 4y ago
  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 4y ago
It’s because it’s streaming I’d bet so really just a wrapper on; index=blah | fields foo
snthpy 4y ago
Have a look at PRQL! prql-lang.org

ignoramous 4y ago

> ...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 4y ago
What query languages have you found that are better than SQL? [edit] Typo.
- sqlcommando 4y ago
  https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query 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 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.
- techdragon 4y ago
  Datalog https://en.wikipedia.org/wiki/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. For anyone with a python background, you can see a good example of (and experiment with) using Datalog style “logic programming” to query existing SQL databases, by checking out the pyDatalog package https://sites.google.com/site/pydatalog/ https://sites.google.com/site/pydatalog/ … keep in mind the level of abstraction in this library, it’s a great place to get familiar with what you could get from Datalog, but working with a database that natively supported Datalog, and had a Datalog query compiler/optimiser would be even more powerful.

munk-a 4y ago

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.

deeebug 4y ago
Unsure if it was added recently, but Splunk Query Language does have VAL IN (1,2,3) now -> https://docs.splunk.com/Documentation/Splunk/9.0.1/SearchReference/Search#4._Using_the_IN_operator https://docs.splunk.com/Documentation/Splunk/9.0.1/SearchRef...
- Philip-J-Fry 4y ago
  Splunk 7 added that from what I can find. 5 years old at least.
  - munk-a 4y ago
    I've been using it for quite a while.
matthewaveryusa 4y ago
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.

ransom1538 4y ago

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.

stanislavb 4y ago

And improving your SQL skills is almost always a good choice. No-matter what tech-role you hold at the moment.