noria
noria copied to clipboard
Document SQL syntax used
I could not find documentation for which exactly SQL syntax is being used/supported?
That is correct — there is no such documentation at the moment. Since this is a research prototype, we sort of just support what the implementation supports, and it's not quite that clearly defined :) In general, most straightforward SQL statements should work, including most aggregations, order+limit, projections, filters, and joins where the join clause is a single column equality.
I think there are two questions here:
- What is the syntax. Is it standard SQL, SQLite dialect, MySQL dialect, etc.? What are case sensitivity rules? What are escaping rules? How are placeholders defined?
?
Or some other character? - What is supported, you listed those, and I had easier time to read about those.
Concretely: what can I put into CREATE TABLE
statement? I see that there is some special syntax to define local views and public views (queries). Is this defined somewhere?
So documentation here is very limited. Maybe this is another reason why it would be useful to switch to sqlparser-rs. Because then you can just delegate the syntax questions to it.
Then probably MySQL adapter would have to be changed a bit so that queries are rewritten from MySQL to internal SQL language. I think that it is visible that current syntax is as-is to make it easier to make MySQL support. But that is also a bit breaking the abstraction.
BTW, in my Rust client applications, do I have to use SQL syntax? Or could I create tables by calling some Rust methods? Because the main motivation for me for this issue was that it looks like when I am building a Rust client application I still have to use SQL (and which SQL) to interact with Noria. It is not that Rust client application would provide programmatic API and then MySQL adapter would map from SQL to that API. Rust client library is using SQL as well. That was a surprise to me. And then it also made it unclear what is this SQL syntax I should be using. Because in examples is not just SQL but also some additional special cases like QUERY
.
Ah, yes, you're right, there is also no documentation of the high-level details about queries. In general though, it is just standard SQL; that's why we didn't think to do it. There are some extensions (such as the QUERY
keyword you point to), but apart from that standard SQL should do just fine. The same thing applies to CREATE TABLE
— you should be able to use standard syntax, though of course Noria will only work with the data types it supports internally (I agree that documenting these would be a good idea!).
As for the Rust interface, the SQL you use to give recipes there should be exactly the same as the MySQL shim. The way to think about this is probably that Noria itself uses SQL, and that the shim is just a way to be compatible with the MySQL wire protocol. We could, in theory, provide a lower-level interface for manually constructing Noria dataflow, but that's pretty difficult to do well since the dataflow is subject to a number of somewhat finicky layout rules that we would somehow need to enforce for user-specified dataflow as well. Our multi-query optimization also relies on SQL, so a manually specified graph would not get the advantages of cross-query combination.
@ms705 I agree with @mitar that we should documented how Noria's SQL syntax translates to Noria's concepts. For example, things like the QUERY
keyword and CREATE VIEW
operations would be handy to have some text about. Listing out what we support in CREATE TABLE
would also be handy for prospective users.
Moving to sqlparser-rs
would be great! It's unlikely that I will have cycles for this in the near future, but I'm happy to help a PR along if you want to look at this, @mitar.
Independently of this, we'll add some documentation on how the VIEW
and QUERY
keywords in Noria recipes work.
BTW, do you care about VIEW
and QUERY
keywords? Couldn't we map them to some existing SQL commands, like CREATE VIEW
and CREATE MATERIALIZED VIEW
? Then probably sqlparser-rs
could be used as-is.
Moving to sqlparser-rs
would probably mean that you would have to transpile some SQL queries in MySQL adapter. Is that something you would care about? Or we just keep continue to pass SQL as-is through, for better or worse?
The MySQL adapter also uses our custom parser at the moment; it would need to likewise move to sqlparser-rs
.
VIEW
and QUERY
are needed because standard CREATE VIEW
(AIUI, at least in MySQL) does not allow for placeholders (?
) in the query.
OK, but I think it will be easier to add to sqlparser-rs
for placeholders inside CREATE VIEW
statements instead of adding special additional keywords?
You are not really passing directly SQL queries to MySQL anyway, no? VIEW
and QUERY
go to Noria, not to MySQL?
That's correct, VIEW
and QUERY
are for Noria recipes only. They're not even part of our custom SQL parser, but parsed separately inside Noria. The MySQL adapter prefixes prepared statements (which can have placeholders) with these keywords when it installs the statements by extending the Noria recipe.
So then I am not sure why it could not then work if Noria would use internally CREATE VIEW
syntax?
It could -- if the parser (whichever is used) supported parameters in CREATE VIEW
statements.
OK, we then understand each other. Good. :-)
I will see if and when I will have time to contribute PRs to this and other ideas I have brought up. I would love to, but at the moment am I a bit swamped with other things. Anyway, it is good to know what should be done. Thanks.
and joins where the join clause is a single column equality.
Meaning JOIN b on a.b_id=b.id AND a.blah=b.something_else
will not work?
@HenkPoley Correct :)