4 ms·

Yeah I think the only way you can use plain, vanilla SQL to query a system is IF its a relational database. Most of the examples are not.. they solve different

Yeah I think the only way you can use plain, vanilla SQL to query a system is IF its a relational database. Most of the examples are not.. they solve different problems and have vastly different architectures under the hood. Lucene for example doesn't use SQL because its really solving a different problem - text search. Its a language dedicated to what could only be expressed using something like `LIKE` and regexes in SQL. Same with Splunk, it addresses a different domain and solves different problems that cant be easily expressed in SQL. Same with MongoDB.. its not a relational db. Just because there is a mapping from some SQL queries to some Mongo queries, does not mean they are identical databases. The query language directly impacts the AST generated, which is necessarily tied very closely to the exact capabilities/internals of a system. This post just feels like its based on a cursory glance of what the systems do. If all Lucene had was regexes, it would not perform much better than a SQL database throwing regexes at strings. Its precisely because the query language is finer-grained that it can be better optimized for that usecase.

pk3469 8y ago
Yeah the post seems to confound two mostly unrelated issues. I agree with not reinventing the wheel for relational databases, SQL is fine. But for other domains, the query languages arose out of need, not because someone felt like it. SQL is hot garbage for for non-trivial logic. I can't even imagine what splunk would look like if you had to query it with SQL. If anything, SPL (or rather the piped operators approach) would make a lot more sense as the groundwork for a universal query language that works across many domains.
ddebernardy 8y ago
> Lucene for example doesn't use SQL because its really solving a different problem - text search. Its a language dedicated to what could only be expressed using something like `LIKE` and regexes in SQL. There are much better options than LIKE and regexes in SQL for text search. To mention but two: SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery; https://www.postgresql.org/docs/current/static/textsearch-intro.html https://www.postgresql.org/docs/current/static/textsearch-in... SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE); https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-lan... All main engines support full text search in one way or another. TSQL, Oracle, DB2... even SQLite. What more, these engines all support inverted tree indexes in one form or another - i.e. the same type of index Lucene uses - to make this efficient. This is completely different from the typical LIKE or regex query against a column with a BTree index.
- Xylakant 8y ago
  If you truly believe that MySQLs MATCH comes close to be plethora of different queries you can fire at an ES or Solr server, you’re missing some fundamental part of the picture. Sure, it might have been possible to squeeze Lucenes or elasticsearchs query language into something that looks like SQL, but it would still remain a custom SQL dialect which would require substantial domain knowledge to handle. The fundamental problem in that domain is not the query language, it’s knowing which query on which underlying field definition will produce the desired result. Squeezing the query language into some sql-like shape will provide marginal gain at best.
  - ddebernardy 8y ago
    I can't speak for MySQL, as I haven't used it in a while, but I'd pick a properly configured Postgres full text over ES in a heart beat, if only because it means one less dependency (tree) to worry about. (As a bonus, the beauty of Postgres allows to use ES as an index type if you so wish [0].) Also: > Squeezing the query language into some sql-like shape will provide marginal gain at best. ... is disingenuous at best. In both examples I raised (and in the ones you'll find if you query the others) the engines have functionality to accept user input pretty much as is - meaning as your typical mom would type it in a search field. If you need extra criteria from there and the dialect's full text syntactic features, you can use regular SQL conditions (and joins, and aggregates, etc.) on the subset of records found. The most discriminating criteria/index will be the one related to the search query in most cases. And when not, lucky you - your query planner did not hammer everything with the same sledgehammer, thus demonstrating why you should be using SQL. [0]: https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb
    - smsm42 8y ago
      I'm not sure what "properly configured" means but ES has huge array of options for search which something simple like MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE) can't even begin to coming close to cover. Look at their docs, there are literally hundreds of options, dozens of search modes, dozens of analyzers and filters, scoring configurations, and so on and so forth. I am not familiar specifically with Postgres but I highly doubt Postgres supports all that or even a serious part of that - at least natively. What I see at your zombodb link is just a way to make Postgres proxy requests to ES - complete with ES syntax and/or Query DSL. The only difference is that instead of connecting to ES directly you connect to Postgres and then add "SELECT" thing in front of your ES Query DSL. If it makes something easier, fine, but it's not "Postgres full text over ES".
sixdimensional 8y ago
While everything, including the Wikipedia definition of SQL defines it as tied to relational databases, one of our industry's biggest flaws is not realizing that the basic concept of a Structured Query Language is that it can handle nearly all types of structures, including non-relational ones. Take a look at the most recent versions of the SQL standard, say SQL:2016, and you see it handles document type data (JSON) pretty well too. The syntax isn't what we're used to, but it works pretty well. I know that SQL can do more when you separate its conceptual foundation from relational databases. It's an expression language, an interface language to structured data. I used to work on a platform that could translate SQL into NoSQL databases and SQL databases alike, it involves translating the expression into something that fits the native data model. For something similar look at Apache Drill, Dremio, Hive or PrestoDb and how they hide different types of databases behind interfaces that make them compatible with SQL. I can admit, shoehorning SQL as it is onto a non-relational source (ex. Graph databases or search) isn't totally clean. But I believe it can evolve further as a standard to address that. It requires more work in middleware to interface between SQL expressions and heterogeneous technology. And getting creative with the language constructs themselves. The power of data projection, data shaping and filtering alone, not to mention aggregation, etc. is undeniable in my opinion. Disclaimer: I used to work for a company that built data federation software which supported SQL in front of heterogeneous sources. So what I am saying I know to be factual in most cases (although difficult!).
- smsm42 8y ago
  But why? Yes, you can make all languages start queries with SELECT. In fact, for graph DBs you have SPARQL (which looks exactly like SQL if you squint a bit), and Elastic has its own SQL interface. But what happened to best tool for the job? Why try to shoehorn a relational database language into dealing with data that are not relational tables in their nature, instead of using approaches that work better? I mean, I understand there's a use case where it can be useful, such as SQL query builders or APIs that support only SQL, but in general case when you don't need one interface to rule them all, what's the use of shoehorning everything into SQL?
- TheAceOfHearts 8y ago
  Too bad normal people can't read the SQL standard without spending a huge amount of money. I just looked it up, and it looks like picking up all the ISO/IEC 9075 2016 specs would end up costing CHF1762 ($1819.09 in USD). Developers are also unable to become involved or voice their concerns in any meaningful way without spending an even larger amount of money to participate in their committees. Oh, and AFAIK all development happens behind closed doors, so good luck figuring out why certain design decisions were made.

pk3469 8y ago

Yeah the post seems to confound two mostly unrelated issues. I agree with not reinventing the wheel for relational databases, SQL is fine. But for other domains, the query languages arose out of need, not because someone felt like it. SQL is hot garbage for for non-trivial logic. I can't even imagine what splunk would look like if you had to query it with SQL. If anything, SPL (or rather the piped operators approach) would make a lot more sense as the groundwork for a universal query language that works across many domains.

ddebernardy 8y ago

> Lucene for example doesn't use SQL because its really solving a different problem - text search. Its a language dedicated to what could only be expressed using something like `LIKE` and regexes in SQL. There are much better options than LIKE and regexes in SQL for text search. To mention but two: SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery; https://www.postgresql.org/docs/current/static/textsearch-intro.html https://www.postgresql.org/docs/current/static/textsearch-in... SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE); https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-lan... All main engines support full text search in one way or another. TSQL, Oracle, DB2... even SQLite. What more, these engines all support inverted tree indexes in one form or another - i.e. the same type of index Lucene uses - to make this efficient. This is completely different from the typical LIKE or regex query against a column with a BTree index.

Xylakant 8y ago
If you truly believe that MySQLs MATCH comes close to be plethora of different queries you can fire at an ES or Solr server, you’re missing some fundamental part of the picture. Sure, it might have been possible to squeeze Lucenes or elasticsearchs query language into something that looks like SQL, but it would still remain a custom SQL dialect which would require substantial domain knowledge to handle. The fundamental problem in that domain is not the query language, it’s knowing which query on which underlying field definition will produce the desired result. Squeezing the query language into some sql-like shape will provide marginal gain at best.
- ddebernardy 8y ago
  I can't speak for MySQL, as I haven't used it in a while, but I'd pick a properly configured Postgres full text over ES in a heart beat, if only because it means one less dependency (tree) to worry about. (As a bonus, the beauty of Postgres allows to use ES as an index type if you so wish [0].) Also: > Squeezing the query language into some sql-like shape will provide marginal gain at best. ... is disingenuous at best. In both examples I raised (and in the ones you'll find if you query the others) the engines have functionality to accept user input pretty much as is - meaning as your typical mom would type it in a search field. If you need extra criteria from there and the dialect's full text syntactic features, you can use regular SQL conditions (and joins, and aggregates, etc.) on the subset of records found. The most discriminating criteria/index will be the one related to the search query in most cases. And when not, lucky you - your query planner did not hammer everything with the same sledgehammer, thus demonstrating why you should be using SQL. [0]: https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb
  - smsm42 8y ago
    I'm not sure what "properly configured" means but ES has huge array of options for search which something simple like MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE) can't even begin to coming close to cover. Look at their docs, there are literally hundreds of options, dozens of search modes, dozens of analyzers and filters, scoring configurations, and so on and so forth. I am not familiar specifically with Postgres but I highly doubt Postgres supports all that or even a serious part of that - at least natively. What I see at your zombodb link is just a way to make Postgres proxy requests to ES - complete with ES syntax and/or Query DSL. The only difference is that instead of connecting to ES directly you connect to Postgres and then add "SELECT" thing in front of your ES Query DSL. If it makes something easier, fine, but it's not "Postgres full text over ES".

sixdimensional 8y ago

While everything, including the Wikipedia definition of SQL defines it as tied to relational databases, one of our industry's biggest flaws is not realizing that the basic concept of a Structured Query Language is that it can handle nearly all types of structures, including non-relational ones. Take a look at the most recent versions of the SQL standard, say SQL:2016, and you see it handles document type data (JSON) pretty well too. The syntax isn't what we're used to, but it works pretty well. I know that SQL can do more when you separate its conceptual foundation from relational databases. It's an expression language, an interface language to structured data. I used to work on a platform that could translate SQL into NoSQL databases and SQL databases alike, it involves translating the expression into something that fits the native data model. For something similar look at Apache Drill, Dremio, Hive or PrestoDb and how they hide different types of databases behind interfaces that make them compatible with SQL. I can admit, shoehorning SQL as it is onto a non-relational source (ex. Graph databases or search) isn't totally clean. But I believe it can evolve further as a standard to address that. It requires more work in middleware to interface between SQL expressions and heterogeneous technology. And getting creative with the language constructs themselves. The power of data projection, data shaping and filtering alone, not to mention aggregation, etc. is undeniable in my opinion. Disclaimer: I used to work for a company that built data federation software which supported SQL in front of heterogeneous sources. So what I am saying I know to be factual in most cases (although difficult!).

smsm42 8y ago
But why? Yes, you can make all languages start queries with SELECT. In fact, for graph DBs you have SPARQL (which looks exactly like SQL if you squint a bit), and Elastic has its own SQL interface. But what happened to best tool for the job? Why try to shoehorn a relational database language into dealing with data that are not relational tables in their nature, instead of using approaches that work better? I mean, I understand there's a use case where it can be useful, such as SQL query builders or APIs that support only SQL, but in general case when you don't need one interface to rule them all, what's the use of shoehorning everything into SQL?
TheAceOfHearts 8y ago
Too bad normal people can't read the SQL standard without spending a huge amount of money. I just looked it up, and it looks like picking up all the ISO/IEC 9075 2016 specs would end up costing CHF1762 ($1819.09 in USD). Developers are also unable to become involved or voice their concerns in any meaningful way without spending an even larger amount of money to participate in their committees. Oh, and AFAIK all development happens behind closed doors, so good luck figuring out why certain design decisions were made.