Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: Trilogy – A Reusable, Composable SQL Experiment

trilogydata.dev

Post reply on HN
Print view View original

31–40 of 46 posts

Re: Show HN: Trilogy – A Reusable, Composable SQL Experiment

#31

Post by hobofan »

totalhack wrote: post #27
hobofan wrote: post #9

One thing that I am always looking for in a new "reusable", "composable" SQL tool is reuse of the same analytical queries across different source tables. 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…

If I'm understanding your intent correctly, I think you could do that with Zillion. 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.

Sure, I could do that in Zillion the same way I'm currently composing queries in Python at runtime with PyPika.

I'm looking for a more programming language agnostic solution that tools like this (e.g. also PRQL, Malloy) usually offer.

Re: Show HN: Trilogy – A Reusable, Composable SQL Experiment

#32

Post by hobofan »

thesz wrote: post #29
carlineng wrote: post #22

Earlier quoted context omitted.

I agree 100% that this needs to be more of a thing. For data engineers building data pipelines, queries are like functions, and table schemas are like types. There needs to be a way to write a query that runs on an abstract interface, rather than an actual table. To do this, most folks rely on string templating in Python or Jinja, which makes the development process really cumbersome. As a result, most teams end up i…

> There needs to be a way to write a query that runs on an abstract interface, rather than an actual table. 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 interfac…

> VIEWs allows you to not repeat yourself too often.

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.

Re: Show HN: Trilogy – A Reusable, Composable SQL Experiment

#33

Post by hobofan »

fooododododo wrote: post #25
hobofan wrote: post #17

Earlier quoted context omitted.

Oh, I 100% agree with you. > 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…

Curious what your opinion on plpgsql functions is? Could easily solve your initial problem (if I follow). They don't seem to come up much though

I do like PL\pgSQL functions, and I think they can to some extent be used to solve this problem, though I think they are limited in how their internal structure is parameterizable.

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.

Re: Show HN: Trilogy – A Reusable, Composable SQL Experiment

#34

Post by munk-a »

carlineng wrote: post #22
hobofan wrote: post #9

One thing that I am always looking for in a new "reusable", "composable" SQL tool is reuse of the same analytical queries across different source tables. 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…

I agree 100% that this needs to be more of a thing. For data engineers building data pipelines, queries are like functions, and table schemas are like types. There needs to be a way to write a query that runs on an abstract interface, rather than an actual table. To do this, most folks rely on string templating in Python or Jinja, which makes the development process really cumbersome. As a result, most teams end up i…

I have used CTEs with dynamic query stitching to solve this problem (specifically my business operates over two very similar but distinct domains which we keep in separate buckets). If you build the majority of your logic into a CTE that processes named columns coming out of a prior chunk you can swap out what actual columns in the DB are mapped into the columns coming out of that earlier CTE with its definition. It may be possible to make this more magical using pl/pgsql but I've found that dynamic query stitching at the CTE resolution is a level of fiddly-ness I'm comfortable building into resilient products.

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.

Re: Show HN: Trilogy – A Reusable, Composable SQL Experiment

#35

Post by efromvt »

hobofan wrote: post #9

One thing that I am always looking for in a new "reusable", "composable" SQL tool is reuse of the same analytical queries across different source tables. 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…

Hmm - in Trilogy, if both tables had firstname and lastname as concepts bound to them, and you created full name that was the concat of those concepts, you'd only need to define fullname once and the calculation would work against both without any direct binding to either. The unioning is actually the unsupported part right now, though it's planned to be implemented!

Re: Show HN: Trilogy – A Reusable, Composable SQL Experiment

#36

Post by efromvt »

hobofan wrote: post #9

One thing that I am always looking for in a new "reusable", "composable" SQL tool is reuse of the same analytical queries across different source tables. 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…

To expand on previous answer, right now this would be represented as:

key firstname string; key lastname string;

auto full_name 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 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.

Re: Show HN: Trilogy – A Reusable, Composable SQL Experiment

#37

Post by efromvt »

efromvt wrote: post #36
hobofan wrote: post #9

One thing that I am always looking for in a new "reusable", "composable" SQL tool is reuse of the same analytical queries across different source tables. 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…

To expand on previous answer, right now this would be represented as: key firstname string; key lastname string; auto full_name 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…

Here's an example of what this would look like in practice; https://gist.github.com/greenmtnboy/580f479c80e23c5362a70b43...

Re: Show HN: Trilogy – A Reusable, Composable SQL Experiment

#38

Post by efromvt »

totalhack wrote: post #30

Congrats on the launch. I made a tool that has some similar objectives but doesn't present as SQL itself like Trilogy seems to. I'll take a deeper look at Trilogy soon, always interested to see the variety of approaches to this. https://github.com/totalhack/zillion

Oh wow yeah, a lot of parallels - thanks for sharing, I'll take a deeper dive in a bit. I think there's a lot of demand and a lot of space for different solutions; Trilogy definitely aspires to hew closer to standard SQL. (I actually really like SQL for the most part!)

Re: Show HN: Trilogy – A Reusable, Composable SQL Experiment

#40

Post by efromvt »

hipadev23 wrote: post #8
d-lisp wrote: post #6

Earlier quoted context omitted.

I was reading this comment and I have no expertise in sql; would you like to explain why do exemples "look like junior-level bloat" to you ?

Their examples showing why Trilogy is so good are comparing it against poorly written SQL with poorly designed schemas. It reduces my confidence that their tool is actually solving real problems but instead was borne out of frustration in learning SQL and databases.

Any particular examples you have in mind? The demo is just referencing https://github.com/duckdb/duckdb/tree/main/extension/tpcds/d... which I wouldn't regard as a standard of good SQL; (implicit joins, yikes!) - but is a useful capability reference (as is tpc-ds in general).

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?

Post reply on HN