Trilogy is intended to provide an accessible but deep alternative to raw SQL. It offers a new-but-inspired-by-SQL syntax that compiles to various dialects of SQL (with DuckDB as the default).
The target audience is people that really like SQL for analytics and data engineering, but want less boilerplate and sharp edges and looser coupling to the DB.
Semantic models can be easily shared, composed and iterated on in an interactive session, preserving the adhoc workflows that make SQL so powerful.
The "higher level" of the language vis-a-vis SQL makes it straightforward to extend into ETL (an experimental basic DBT integration is available), offering potential to optimize a processing graph across intermediate staging nodes automatically.
This higher level of abstraction also offers some nice opportunities for more reliable text to SQL for LLMs. A similarly basic integration is available to demonstrate this, as is a very basic VsCode extension and electron-based IDE.
Tech stack is primarily Python. Open source, MIT license. Github is linked from demo page. Thoughts, feedback, contributions all welcome!
Note: renamed from PreQL (see prior show https://www.hackerneue.com/item?id=40728938) to avoid confusion with the many PreQLs of the world. The `SQL pun` naming space is unfortunately well-explored.
Other SQL replacements (all great, all worth a look!):
PRQL (pipelined SQL alternative, all new syntax) https://www.hackerneue.com/item?id=36866861
Malloy (all new syntax, semantic focus) https://www.hackerneue.com/item?id=30053860
preql (much more ambitious, all new syntax) https://www.hackerneue.com/item?id=26447070
My litmus test:
I have a table "people" with the columns "people.firstname", "people.lastname", and a table "persons" with the columns "persons.firstname", "persons.lastname". I now want to create a query that gives me the "fullname" (".firstname" + " " + ".lastname") of all rows of both tables. If I have to spell out the logic for how to calculate the fullname in the query twice, the test is failed.
(Taking the shortcut of creating the union of both tables first is not allowed, but I can't think of a simple example that enforces that restriction).
For some reason, all of the solutions (PRQL, Malloy, dbt) that try to make SQL more reusable don't really consider this kind of reuse, and with that ultimately fall flat for the use-cases I would typically have for them. Sadly, Trilogy doesn't seem to be any better on that front.
IMO these kinds of "shortcuts based on column naming across tables" usually end in disaster down the road. For example, I've been bitten in the past by "natural joins" when we've wanted to refactor something later.
I definitely agree that I don't want to have to repeat logic within a single table, but the kind of syntactic sugar that is your litmus test is a big foot gun IMO.
> IMO these kinds of "shortcuts based on column naming across tables" usually end in disaster down the road.
I can see that point, and that was not what I wanted to express with my litmus test. It's only supposed to be a litmus test after all. In a proper solution there would be additional things I would be looking for, but so far everything I've seen already fails that "trivial" test.
One could easily re-formulate it, so that in the one tabel the column is named ".firstname", and in the other one it is named ".first_part_of_the_name".
The core point is more that no matter the relational logic you layer on top of a table/view, that logic should be paramterizable by table/view/column names, to be properly relocatable. I'd be happy about suggestions for better examples! Some solutions (I think dbt) do have some relocateability across schemas, but usually in a more singleton-like manner rather than being able to instantiate the logic multiple times.
I can just tell you that I interact with queries that would benefit from such kind of reuse on a daily basis. One common thing would also be mechanisms that you want to reuse across many different tables in your schema. E.g. soft-deletes or historic/snapshot tables. Nowadays those kinds of solutions usually end up being expressed in the ORM/query builder of a programming language (and thus highly fragmented across programming language ecosystems), instead of living on an SQL-like level and being able to mature better.
I am rarely in a position at my client projects where I can employ PL\pgSQL though, so I opt more for out-of-database solutions for composing my queries, as that usually is easier to debug.
But sometimes this decision has been made years ago and it's not realistic to change it now. I've wanted to do this many times, and I've never been the person who created said tables.
Also, certain use cases perform much better if you create temp tables with small subsets of data from the main tables. It sure would be nice to be able to reuse fragments of SQL written against the main tables... if only SQL were better.
I wrote a longer blog post about this recently: https://carlineng.com/?postid=holy-grail-data-engineering#bl...
Seeing that both someone working on PRQL and Malloy replied and to both of you it's an understood pain makes me feel a lot better about the future of these tools! When talking about that with people that are not that deep into the problem it is often hard to transport the difference between this kind of composability vs. the composability that the tools are offering today, and the implications that come with that.
At a past startup I had the fortune to be able to work on a similar system to what I am looking for: Packageable, reusable relation algebra inspired by Substrait. It had the downside though that it was quite tied to RDF and SPARQL in its implementation, and now I'm chasing something similar in the SQL world :D
I work with complex data models and keeping all that structure in my brain takes enough effort that I want to keep my queries as simple as possible because when it's time to debug one there's no way I'm carrying over _any_ memory from when I originally wrote it.
Proper use of SQL inverts control. Instead of parameterizing query by table, you write a query and at the actual use site you join it on the table you need by fields your query provides. VIEWs allows you to not repeat yourself too often.
Best thing is that you do not need to even mention that "abstract interface table" as a parameter at all.
No they don't. They only offer a solution to the problem "many different predicates for a few tables", but don't offer a solution to the problem "a few similar predicates for many different tables", as views as per their declaration are already tied to a single table.
Having this sort of "table polymorphism" is something we've thought a lot about for PRQL and is definitely something we want to get right. That said it's not straightforward but you can do a lot of it already. You can try the following examples for yourself in the PRQL Playground (https://prql-lang.org/playground/).
First a simple example using functions as they are documented:
```prql
let fullname = func firstname lastname -> f"{firstname} {lastname}"
from customers
select full_name=(fullname first_name last_name)
```
Now the example above isn't quite what you're looking for because you still have to specify the columns as function arguments and there really isn't much gained here. It serves to illustrate the principle though as the `fullname` function could be doing something more complicated.
What you want is:
```prql
let add_full_name = func tbl<relation> -> (
from customersadd_full_name
select full_name
```
Now this requires the `<relation>` type annotation which hasn't been documented because it's still quite experimental. However this works right now and can be applied to different tables or relations, for example you could use the same function in the following:
```prql
from i=invoices
join c=customers (==customer_id)
select {c.first_name, c.last_name, i.total}
sort {-total}
add_full_name
select {full_name, total}
```
I'll add some more examples in child comments.
Disclaimer: I'm a PRQL contributor.
```prql
let normalize = func x -> ((x - min x)/((max x) - (min x)) | math.round 2)
from tracks
take 5
derive {ms_norm=(normalize milliseconds), bytes_norm=(normalize bytes)}
select {track_id, ms=milliseconds, ms_norm, bytes, bytes_norm}
```
which produces the following SQL:
```sql
WITH table_0 AS (
)SELECT track_id,
FROM table_0-- Generated by PRQL compiler version:0.13.2 (https://prql-lang.org)
```
key firstname string; key lastname string;
auto full_name <- concat(firstname, ' ', lastname);
datasource people ( firstname:firstname, lastname:lastname ) address people;
datasource persons ( firstname: firstname, last_name:lastname ) address persons;
And a select full_name;
Could resolve from either table.
The missing bit if you're trying to define a universe across both is actually the union construct; right now a concept is assumed to have one cardinality space.
Something like: auto all_first_names <- union(first_name1, first_name2);
There's a coupling between the concept definition both as a function input and as a semantic value. They could be decomposed, but you'd still need to recompose them at some point before running a query.
In particular, I wanted to do this in SQLite recently. I wanted to have one write process which would always remain unblocked. And I also wanted to be able to run certain tasks which would do some temporary/discardable DB manipulations as part of producing an output file. These tasks could open the SQLite DB in read-only mode; load relevant data into temp tables, manipulate that data, and write the output file. Everything would have worked great if only SQL were a more composable language.
https://github.com/totalhack/zillion
Disclaimer: been sidetracked by an acquisition at my day job this year, intend to put more time into this project soon, but I use it in production to great effect.
I'm looking for a more programming language agnostic solution that tools like this (e.g. also PRQL, Malloy) usually offer.
I get why querying gets more attention, insertions are usually straightforward and don’t need much simplification. Updates, on the other hand, can be a bit trickier since they often involve modifying data derived from complex queries. These tools seem geared toward data analysis and not data generation, which is ok: is nice focusing on a single problem and solving it "right".
But! for projects where a single person handles data creation, analysis, and management, it feels cumbersome to use one set of tools for querying ("R" in CRUD) and another for creation, updates, and deletions ("C," "U," and "D"). I think a "SQL replacement" or approach covering all of CRUD could be interesting for projects of any scale. Something that I could pick instead of shopping for ORMs and/or lightweight query generators.
https://github.com/totalhack/zillion
I have nothing against SQL of course. The simplified approach of a UI built on top of zillion or tools like it really enables a whole next level of productivity for business users that are never going to learn SQL, but also need more query flexibility than just "dashboards" without having to wait on a BI team for answers -- I die inside a little bit every time I hear of a company doing this. And as you have noted, I also think text-to-semantic-layer is an interesting approach for involving AI/NLP.
I've been pulled away from this project for some time due to an acquisition at my day job but hoping to get back into it soon!
Anyway; nice one! Will try.
Will take a proper look when I get a chance.
In the meantime, I just wanted to say: nice name! ;-)
0 - https://github.com/cube-js/cube
As I tried to convey, I like SQL a lot - my frustration is more around the lifecycle and maintainability.
Happy to add more ergonomic references in other places, if you have some good examples to reference against?