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

21–30 of 46 posts

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

#21

Post by hobofan »

snthpy wrote: post #19
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…

Hi, 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 first…

Thanks for the thorough reply! Really glad to see at least one of the solutions working towards it, and I'll definitely check PRQL out again in more depth!

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

#22

Post by carlineng »

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 in scenarios where data pipelines are always a big mess of spaghetti SQL, or they are stuck maintaining complex frameworks that abstract away common logic, but are inscrutable to the average user.

I wrote a longer blog post about this recently: https://carlineng.com/?postid=holy-grail-data-engineering#bl...

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

#24

Post by fooododododo »

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…

Do they ever write plpgsql? If not why not?

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

#25

Post by fooododododo »

hobofan wrote: post #17
hn_throwaway_99 wrote: post #13

Earlier quoted context omitted.

TBH, I don't think your test is very useful in real world environments. That is, you have 2 independent tables, and you're wanting the solution to depend on the fact that there are columns that are named the same across both tables. 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 t…

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

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

#26

Post by hobofan »

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 think your blog post frames the problem very well!

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

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

#27

Post by totalhack »

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.

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

#28

Post by [deleted] »

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…

[deleted]

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

#29

Post by thesz »

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…

> 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 interface table" as a parameter at all.

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

#30

Post by totalhack »

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

Post reply on HN