4 ms·

> I don't understand why databases like RethinkDB and MongoDB don't just provide a SQL interface. Actually, it's a really good question. I'm one of the ReQL de

> I don't understand why databases like RethinkDB and MongoDB don't just provide a SQL interface. Actually, it's a really good question. I'm one of the ReQL designers at Rethink, and I was the one pushing for no SQL compatibility. Here is some of my reasoning (we could talk about this for days, though): * Even SQL designers would tell you SQL isn't a very good programming language. It even looks like Cobol! Imagine if every language after Cobol decided to be backwards compatible -- what sort of world would we live in? * SQL is really bad for querying hierarchical data with lots of empty columns. The quality of experience you get from learning a new language designed for JSON, far outweighs the downsides of learning it. We're assuming people will be using ReQL fifteen years from now. * Designing a language that embeds into your programming language wasn't possible before -- but it is now. That means no more SQL injection, no more string manipulation, no more heavy ORMs. Well worth it in my opinion. * The chaining paradigm (largely pinoneered and proven by jQuery) is magical for getting people to intuitively understand how to write complex queries. No more StackOverflow questions of "How do I do X in SQL?" because there is now an intuitive consistency to the language. * SQL compatibility is very, very difficult. The standard is hard to implement, and has lots of grey area around the edges. You can go for full bug-by-bug compatibility, which would take decades. Or you can go for basic compatibility, which confuses people. They try to port their application, it works for a while, and then breaks in some grey area in production, which results in a terrible user experience. As an example, I'd ponder on why SQL has both the `where` keyword, and the `having` keyword. This alone wouldn't be enough of a reason to design a new language, of course, but this sort of thing permeates SQL. It's 2014. We can do better.

shawn-butler 13y ago
HAVING specifies a search condition for a group or an aggregate function.
- coffeemug 13y ago
  Sure, but consider how this works in ReQL: r.table('foo').filter(...) r.table('foo').group('category').filter(...) A properly designed language shouldn't have two different keywords for something that does ostensibly the same thing in different contexts. That's a mark of bad language design.
  - deleted 13y ago
    [deleted]
  - boomzilla 13y ago
    In theory, yes. In practice, the best language is one that maps developer thought process into working code in the most natural ways. I don't know the community that ReQL is targeting, but for the current crop of data scientists, SQL is a more natural language in my opinion. (There is a reason why Hive is so popular among big companies, where you'd expect to find big data sets.
  - haberman 13y ago
    I don't think this is a "good language" / "bad language" dichotomy. SQL just sits at a different level of abstraction. From the examples you have posted, it appears that ReQL is a lower-level abstraction that SQL. In SQL you specify what you want logically and the DB turns this into a query plan. It appears that ReQL is more like a query plan itself, where you explicitly specify the data flow from stage to stage of query evaluation. As a more specific example of this, it appears that in many cases in ReQL the user specifies what index should be used in the query itself. SQL is more abstract than this; the idea is for the query planner to figure out what index(ex) should be used.
  - meritt 13y ago
    Are you sure about that? Looking at even your example page [ http://www.rethinkdb.com/docs/sql-to-reql/ http://www.rethinkdb.com/docs/sql-to-reql/ ] this appears to be wrong or at least confusing? It's suggesting: SELECT category, SUM(num_comments) FROM posts GROUP BY category HAVING num_comments > 7 and: r.table("posts") .filter(r.row['num_comments']>7) .group('category') .sum('num_comments') are identical. I don't think they are? Your ReQL to me looks like it's applying WHERE num_comments > 7 and then aggregating that. I mean regardless, your example SQL should be doing the >7 against an aggregate (e..g sum(num_comments)) not a field, that SQL does not work as written.
    - coffeemug 13y ago
      Whoops, that's a great catch -- it's a bug in the docs. Will fix momentarily. Thanks!! (that is indeed embarrassing) In ReQL any command you call after `group` runs on each group. So once you've called `group`, you can run anything you could run on a table on each group and that just works.
josephlord 13y ago
Do you consider Rethink relational (in the Codd sense)? From a quick look at the API I would guess that it qualifies. What I couldn't see at a quick glance was a way to constrain the data in a table or enforce foreign key constraints. I guess those are the trade offs against something like Postgres for schema flexibility and easy distribution. Is that a correct way to view it?
- coffeemug 13y ago
  > Do you consider Rethink relational (in the Codd sense)? Under the hood, yes. How much of that is exposed to the user is debatable. > What I couldn't see at a quick glance was a way to constrain the data in a table or enforce foreign key constraints. I guess those are the trade offs against something like Postgres for schema flexibility and easy distribution. Is that a correct way to view it? That's about right. Some of this is by design (lack of schema enforcement), and we might add features to do that later. There are no architectural constraints preventing us from doing that. Foreign key constraints will probably never be added to Rethink (or at least not for a long, long time) because doing those efficiently in distributed systems is anywhere from hard to impossible.
ashwinp92 13y ago
Although initially it seemed otherwise, your point on having a different interface to confusion between complete SQL compatibility v/s the subset being offered makes a lot of sense. Also personally I would rather prefer an imperative language for tasks where efficiency is the primary concern compared to declarative SQL
AaronFriel 13y ago
I can tell at a glance what `where` and `having` mean - the former is a filter over a result set, and the latter is a filter over a grouping. You're probably right that SQL should have earlier defined this sort of recursion, so that you could sequence groupings and filters more easily, but don't fret: that future is here with common table expressions. Recursion is even supported! I'm reminded of this abomination of a query I wrote in 2010 that produced a numbers table: http://blogs.msdn.com/b/sqlazure/archive/2010/09/16/10063301.aspx http://blogs.msdn.com/b/sqlazure/archive/2010/09/16/10063301... Code: DECLARE @N int = 1000000; WITH RecursiveRowGenerator (Row#, Iteration) AS ( SELECT 1, 1 UNION ALL SELECT Row# + Iteration, Iteration * 2 FROM RecursiveRowGenerator WHERE Iteration * 2 < CEILING(SQRT(@N)) UNION ALL SELECT Row# + (Iteration * 2), Iteration * 2 FROM RecursiveRowGenerator WHERE Iteration * 2 < CEILING(SQRT(@N)) ) , SqrtNRows AS ( SELECT * FROM RecursiveRowGenerator UNION ALL SELECT 0, 0 ) SELECT TOP(@N) 1 + A.Row# * POWER(2,CEILING(LOG(SQRT(@N))/LOG(2))) + B.Row# Row# FROM SqrtNRows A, SqrtNRows B ORDER BY A.Row#, B.Row#;
- jjudge 13y ago
  Wow, that must have felt good to wrap up.
derefr 13y ago
Good arguments, though only the last one seems like a true objection. I think, If I were implementing a database from the ground up, I'd indeed make my own query language--SQL does suck--but I would probably strive to accept SQL as an option, with the proviso that it's "just SQL syntax with ThisDB's semantics." This would let people point their old applications, written under "the database is just an SQL-speaking dumb store for simple CRUD operations" assumptions (e.g. any ActiveRecord app), at the new DB and get somewhat-sensible results out, while still letting them write new applications against the new API. The alternative is forcing any user with existing services to hack up some sort of application-level back-and-forth replication between their old DB and your new DB during the long-and-possibly-indefinite migration period, rather than just migrating in one fell swoop.
dataminded 13y ago
Background: I've done a fair bit of hiring for analysts and have worked in most facets of analytics/DW including vendor evaluation/selection and implementation. I think that the lack of a SQL interface is a big negative. It's really hard to find analyst (not programmers) with good technical skills or train them to proficiency in new technologies. I would have a very hard time selling a platform that existing analysts wouldn't be able to use right away and that few people on the market could work with. Also, designing a new language makes integration with other applications that much harder.
- nXqd 13y ago
  You can find the answer to this in coffeemug' post : "We're assuming people will be using ReQL fifteen years from now."
- coffeemug 13y ago
  That's a great point. Our hypothesis is that great querying capabilities for analysts and great querying capabilities for programmers should look completely different, and be two totally different technologies. We'll eventually ship a solution for analysts, but I don't think a great database product should shoot for a lowest common denominator.
shin_lao 13y ago
It's great to see someone with a lot of enthusiasm, but I submit you think too much like a programmer and not enough like a database administrator. What problem does your product solve that, for example, PostgreSQL doesn't?

shawn-butler 13y ago

HAVING specifies a search condition for a group or an aggregate function.

coffeemug 13y ago
Sure, but consider how this works in ReQL: r.table('foo').filter(...) r.table('foo').group('category').filter(...) A properly designed language shouldn't have two different keywords for something that does ostensibly the same thing in different contexts. That's a mark of bad language design.
- deleted 13y ago
  [deleted]
- boomzilla 13y ago
  In theory, yes. In practice, the best language is one that maps developer thought process into working code in the most natural ways. I don't know the community that ReQL is targeting, but for the current crop of data scientists, SQL is a more natural language in my opinion. (There is a reason why Hive is so popular among big companies, where you'd expect to find big data sets.
- haberman 13y ago
  I don't think this is a "good language" / "bad language" dichotomy. SQL just sits at a different level of abstraction. From the examples you have posted, it appears that ReQL is a lower-level abstraction that SQL. In SQL you specify what you want logically and the DB turns this into a query plan. It appears that ReQL is more like a query plan itself, where you explicitly specify the data flow from stage to stage of query evaluation. As a more specific example of this, it appears that in many cases in ReQL the user specifies what index should be used in the query itself. SQL is more abstract than this; the idea is for the query planner to figure out what index(ex) should be used.
- meritt 13y ago
  Are you sure about that? Looking at even your example page [ http://www.rethinkdb.com/docs/sql-to-reql/ http://www.rethinkdb.com/docs/sql-to-reql/ ] this appears to be wrong or at least confusing? It's suggesting: SELECT category, SUM(num_comments) FROM posts GROUP BY category HAVING num_comments > 7 and: r.table("posts") .filter(r.row['num_comments']>7) .group('category') .sum('num_comments') are identical. I don't think they are? Your ReQL to me looks like it's applying WHERE num_comments > 7 and then aggregating that. I mean regardless, your example SQL should be doing the >7 against an aggregate (e..g sum(num_comments)) not a field, that SQL does not work as written.
  - coffeemug 13y ago
    Whoops, that's a great catch -- it's a bug in the docs. Will fix momentarily. Thanks!! (that is indeed embarrassing) In ReQL any command you call after `group` runs on each group. So once you've called `group`, you can run anything you could run on a table on each group and that just works.

josephlord 13y ago

Do you consider Rethink relational (in the Codd sense)? From a quick look at the API I would guess that it qualifies. What I couldn't see at a quick glance was a way to constrain the data in a table or enforce foreign key constraints. I guess those are the trade offs against something like Postgres for schema flexibility and easy distribution. Is that a correct way to view it?

coffeemug 13y ago
> Do you consider Rethink relational (in the Codd sense)? Under the hood, yes. How much of that is exposed to the user is debatable. > What I couldn't see at a quick glance was a way to constrain the data in a table or enforce foreign key constraints. I guess those are the trade offs against something like Postgres for schema flexibility and easy distribution. Is that a correct way to view it? That's about right. Some of this is by design (lack of schema enforcement), and we might add features to do that later. There are no architectural constraints preventing us from doing that. Foreign key constraints will probably never be added to Rethink (or at least not for a long, long time) because doing those efficiently in distributed systems is anywhere from hard to impossible.

ashwinp92 13y ago

Although initially it seemed otherwise, your point on having a different interface to confusion between complete SQL compatibility v/s the subset being offered makes a lot of sense. Also personally I would rather prefer an imperative language for tasks where efficiency is the primary concern compared to declarative SQL

AaronFriel 13y ago

I can tell at a glance what `where` and `having` mean - the former is a filter over a result set, and the latter is a filter over a grouping. You're probably right that SQL should have earlier defined this sort of recursion, so that you could sequence groupings and filters more easily, but don't fret: that future is here with common table expressions. Recursion is even supported! I'm reminded of this abomination of a query I wrote in 2010 that produced a numbers table: http://blogs.msdn.com/b/sqlazure/archive/2010/09/16/10063301.aspx http://blogs.msdn.com/b/sqlazure/archive/2010/09/16/10063301... Code: DECLARE @N int = 1000000; WITH RecursiveRowGenerator (Row#, Iteration) AS ( SELECT 1, 1 UNION ALL SELECT Row# + Iteration, Iteration * 2 FROM RecursiveRowGenerator WHERE Iteration * 2 < CEILING(SQRT(@N)) UNION ALL SELECT Row# + (Iteration * 2), Iteration * 2 FROM RecursiveRowGenerator WHERE Iteration * 2 < CEILING(SQRT(@N)) ) , SqrtNRows AS ( SELECT * FROM RecursiveRowGenerator UNION ALL SELECT 0, 0 ) SELECT TOP(@N) 1 + A.Row# * POWER(2,CEILING(LOG(SQRT(@N))/LOG(2))) + B.Row# Row# FROM SqrtNRows A, SqrtNRows B ORDER BY A.Row#, B.Row#;

jjudge 13y ago
Wow, that must have felt good to wrap up.

derefr 13y ago

Good arguments, though only the last one seems like a true objection. I think, If I were implementing a database from the ground up, I'd indeed make my own query language--SQL does suck--but I would probably strive to accept SQL as an option, with the proviso that it's "just SQL syntax with ThisDB's semantics." This would let people point their old applications, written under "the database is just an SQL-speaking dumb store for simple CRUD operations" assumptions (e.g. any ActiveRecord app), at the new DB and get somewhat-sensible results out, while still letting them write new applications against the new API. The alternative is forcing any user with existing services to hack up some sort of application-level back-and-forth replication between their old DB and your new DB during the long-and-possibly-indefinite migration period, rather than just migrating in one fell swoop.

dataminded 13y ago

Background: I've done a fair bit of hiring for analysts and have worked in most facets of analytics/DW including vendor evaluation/selection and implementation. I think that the lack of a SQL interface is a big negative. It's really hard to find analyst (not programmers) with good technical skills or train them to proficiency in new technologies. I would have a very hard time selling a platform that existing analysts wouldn't be able to use right away and that few people on the market could work with. Also, designing a new language makes integration with other applications that much harder.

nXqd 13y ago
You can find the answer to this in coffeemug' post : "We're assuming people will be using ReQL fifteen years from now."
coffeemug 13y ago
That's a great point. Our hypothesis is that great querying capabilities for analysts and great querying capabilities for programmers should look completely different, and be two totally different technologies. We'll eventually ship a solution for analysts, but I don't think a great database product should shoot for a lowest common denominator.

shin_lao 13y ago

It's great to see someone with a lot of enthusiasm, but I submit you think too much like a programmer and not enough like a database administrator. What problem does your product solve that, for example, PostgreSQL doesn't?