Hacker News Reader

Comment by madebyherzblut | Hacker News Reader

Slightly off-topic, but I appreciate any pointers: In a project, I want to offer an SQL interface for data analysts similar to Stripe Sigma[1]. The tricky thing is not to parse the query but to map the public schema to the private schema, add authorization, and distribute the query across data stores. So, I am looking for a customizable query parser, planner, and execution engine.

I briefly looked into Apache Calcite and DataFusion[2], but I am unsure if I am on the right track. If someone has any ideas about where to look, please let me know.

[1] https://stripe.com/en-gb/sigma [2] https://github.com/apache/arrow-datafusion

View on HN

For a similar use case, I’ve been considering a combination of s3 exports of db views (mapping private schema to public) + lakeformation governed tables (which allow table, row, and cell level security via iam) + redshift or athena for querying + sts/cognito for authorization to give logged in users a temporary access key id and secret access key. Admittedly an AWS heavy setup, but in my use case that’s an advantage :)

> So, I am looking for a customizable query parser, planner, and execution engine

That looks like an entire DB engine, isn't it?

> map the public schema to the private schema

sounds like view.

It seems like a pretty major project as you describe it.

And if you're set on building an "entire DB engine" then you can just as well go the full mile and go for relational. As opposed to SQL.

I know about TTM etc, but what business value would that bring to be fully relational? Specifically, thanks.

See the "business case for SIRA_PRISE". Imagine how many years of codeshitter-hours you [or, if you're in the DBMS market, your customers] would no longer have to pay for if you could have *ALL* of your [strictly data-related] business rules enforced by a mere *declaration* made by the business analyst c.q. the data administrator (note the absence of 'base' in that term), as opposed to having to rely on those very same codeshitters because there simply ain't no other way for you to enforce same set of said business rules other than to pay that mob for coding all that application-enforced integrity (which they are bound to get wrong because it is fundamentally beyond their ability) or the stored procedures that achieve the same but are still procedural (and is still bound to fail for essentially the same reason).

If you know about TTM, you might also know about "Applied Mathematics for Database Professionals". What I'm referring to is their "execution model 6" becoming possible *WITHOUT* any [procedural form of] coding [by mere programmers].

Another way of saying this is "You can have CREATE ASSERTION if you want to".

Am aware of sira-prise but not very well. Thanks, will read up (any disclaimer needed here; has sira-prise any link to you?)

I'd appreciate you omitting the codeshitter ad-homs, it undermines your case.

Pretty sure no declarative statement can be made efficient automatically so that remains a dream (though one I will need to look at) so it will kill performance. I too hate procedural enforcements but there seems to be no way round them. Have you got a reliable statement anywhere that says efficient 'create assertion' in sql is possible in general?

AMfDbP book - it's on my reading list already. Thanks for the pointer.

Link between me and SIRA_PRISE : I am the author.

codeshitter ad-homs : yeah well I know they are. The fact of the matter is the history between SIRA_PRISE and me (and why I did it in the first place) is now almost 20 yrs old, and I know how it's been received, and that's primarily due to (a) how the codeshitters (and the way how they are subject to the Dunning-Kruger effect) have come to dominate the entire industry and (b) how that economic system I too am bound to operate/survive in prevents the managers who control the system from taking "too much" (say, > 0.01%) risks.

And about "Pretty sure no declarative statement can be made efficient automatically so that remains a dream" : there fucking sure ain't no better way to piss me off, not just to the other side of the planet, but to plain outright Mars or Jupiter. You're just too pretty damn sure of yourself. AM4DP makes +- the same statement as you although the authors there still did manage to *NOT* make the same logically flawed inference from "not anybody knowing today how it can be done" to "cannot be done". You apparently fall into the category of people who do make that inference. The article exposing the exact opposite of your convictions was published in Oracle Users Magazine somewhere around 2013, IIRC. I'd need to look up the exact details by now, but it was already an incredible surprise Oracle User Group even wanted to publish on the solution of a problem that Oracle Corporation itself wasn'y (and still isn't, probably due to the power of certain people within that company who have been labeled 'bean keepers') prepared to invest in.

In fact, the only reason I wrote that article in the first place was Toon Koppelaars using that very word 'dream' (which you also used in your reply) to associate the idea with SQL's CREATE ASSERTION, when I already knew it needn't be a 'dream' no longer ... Well, if anyone just wanted to believe, which they don't, you inluded, apparently ... To Toon's credit, although he also used the adjective 'impossible' to qualify that 'dream', he did manage to end his title phrase with a *question* mark. You ended yours with an exclamation mark.

And just for the record, this statement is *NOT* to be interpreted as "every thinkable constraint can be implemented with sub-millisecond violation detection". Sunt certi denique fines, quos ultra citraque nequit consistere rectum. The "fines" in case being the bounds of what algorithmics as it is known today, can do for us. Constraints like "all the people who obtained a degree in mathematics must be paid in the top 5% of salaries" (if anyone ever wanted to formally declare and enforce any rule like that) inevitably takes us into realms of 2nd-order logic that no known algorithm today can guarantee us execution times like the ones we have grown accustomed to from what is supported by SQL systems these days).

As for "do you have a reliable statement" ... I have two answers but neither have the quality of being dependable on the academic level of meaning you might probably want to attach to the words "reliable" and "dependable". The first answer is "No, because the only other person in the entire world that I'm aware of achieving results in the same area is professor Davide Martinenghi and his PhD thesis on the subject, of which I don't even consider myself capable enough to academically assess the equivalence between his results and mine" and the second answer is "No, because that 'reliable' statement would have to be made by me myself and I have nothing(*) but my own implementation to 'prove' it and I'm very well aware that on an academic level, a seemingly working implementation is not a proof".

(*) I did write a paper on the subject and it's been seen by Chris Date (who by proxy answered that he was 'impressed'), by Hugh Darwen, and by Adrian Hudnott. And I will rather take it to my grave with me than disclose it any further.

And about the *scrutinously detailed subject* of your question whether "efficient 'create assertion' in sql is possible in general" : I have grown convinced that *IN SQL*, it isn't. But the reasons are *ENTIRELY* related to SQL's depending on 3VL. I have grown convinced (in fact, for my own conviction, I think I have sufficiently demonstrated) that in some *other* DMl language that *embraces 2VL*, it is *perfectly feasible*. And in fact, I think SIRA_PRISE itself is its own proof, in that respect : *ALL* the rules that SIRA_PRISE imposes as business rules on its users are implemented as mere declared database constraints on the catalog, and are enforced using the *exact* same machinery that also enforces the *user* declared business rules on the *user* databases. When I showed my system to a former colleague of mine who made his entire career in data [base] administration backed by a degree in mathematics, he merely responded that there just ain't no better POC than that.

Jesus. I feel there may be a touch of dunning-kruger about you. From that book (page xvi)

" Standard SQL is relationally complete in its support for declarative constraints by permitting the inclusion of query expressions in the CHECK clause of a constraint declaration.

We do not yet know—and it is an important and interesting research topic—how to do the kind of optimization that would be needed for the DBMS to work out efficient evaluation strategies along the lines of the authors’ custom-written solutions. "

So it can't. But then you say.

> But the reasons are ENTIRELY related to SQL's depending on 3VL.

Easy to fix. Simply require the assertion to be defined on tables (ok, ok, relvars) with 'not null' on every column, also require a PK to ensure uniqueness, and you're away. Except you aren't.

I'm not going to argue with you. Your knowledge is clearly considerable but it comes with something extra I don't need.

The basic text of the book was written before Lex died (must have been +- 2006), my work in which I cracked that problem saw the light of day after that time. So "we do not yet know ..." was technically correct at the time they wrote it, it no longer would be now.

That other snippet "Standard SQL is relationally complete ... CHECK clause ..." is technically correct, but the standard allows subqueries referencing other tables than the one the CHECK clause is on, but as far as I'm aware no product supports that (and the ones that do leave the user exposed to risk of faulty behaviour). Sadly, such a feature is necessary if we'd want to write, say, an FK constraint in the form of CHECK clause on the referencing table : CHECK (EXISTS (SELECT 1 FROM PARENT WHERE <FK equality tests here> ) ).

With one single exception : you cannot use a CHECK clause to check that a table must be nonempty, because the semantics of the CHECK clause are that it must be satisfied by all rows in the table, and therefore the empty table trivially satisfies any CHECK clause.

Huh! That makes sense, thanks! It's standard univ. quantifier behaviour, I hadn't made the link.

Also that's bloody odd because date or darwen (or both) explicitly called out this behaviour as flawed in SQL when in fact it's obviously logically consistent, and they know logic and SQL, so... why are they critising it? How very strange.

Again, thanks for pointing this out. How did I never realise this?

What they "called out as flawed" was the impossibility to declare any such rule with SQL as it stood. What they were "criticizing" was precisely the fact that a data language that was supposed to be "expressively complete" was *unable* to express such a rule. You can't claim "expressive completeness" for a language if there is demonstrably a case where said language's expressiveness fails to meet the mark. It's not the "behaviour of the language" they were criticizing in all/any of those use cases that were supported by the language as it stood. They were criticizing the fact that there was a use case [and a relatively reasonable one on the face of it] that the language couldn't support.

Reply on news.ycombinator.com