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

The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation

trino.io

Post reply on HN
Print view View original

11–20 of 70 posts

Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation

#11

Post by bitsondatadev »

gavinray wrote: post #8

I recently had to write SQL query generation for AWS Athena, which is based off Presto 0.217 It turns out that the dialect doesn't support LATERAL joins with a LIMIT in them. The below query only works if you remove the LIMIT clause. https://i.stack.imgur.com/rdB1s.png This makes saying things like "Fetch all artists where ..., for each artist fetch their first 3 albums where ..., and for each album fetch the top 10…

Check out this PR. I believe we may have tackled this one but you'd need to try it out on Trino: https://github.com/trinodb/trino/pull/1415

Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation

#13

Post by gavinray »

karma_fountain wrote: post #10
gavinray wrote: post #8

I recently had to write SQL query generation for AWS Athena, which is based off Presto 0.217 It turns out that the dialect doesn't support LATERAL joins with a LIMIT in them. The below query only works if you remove the LIMIT clause. https://i.stack.imgur.com/rdB1s.png This makes saying things like "Fetch all artists where ..., for each artist fetch their first 3 albums where ..., and for each album fetch the top 10…

Is it possible to achieve this with a window function?

I found out it is! Kudos to this kind internet stranger for telling me:

https://stackoverflow.com/a/73129836/13485494

But man is it a huge PITA (especially when doing programmatic code generation of the SQL) compared to LATERAL joins

Someone familiar with the CockroachDB query planner showed me that a window function like this is what Cockroach turns LATERAL joins into for instance:

    demo@127.0.0.1:26257/movr> explain select * from abc, lateral (select * from xyz where x = a limit 2);

    • filter
    │ estimated row count: 1
    │ filter: row_num

Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation

#14

Post by gavinray »

bitsondatadev wrote: post #11
gavinray wrote: post #8

I recently had to write SQL query generation for AWS Athena, which is based off Presto 0.217 It turns out that the dialect doesn't support LATERAL joins with a LIMIT in them. The below query only works if you remove the LIMIT clause. https://i.stack.imgur.com/rdB1s.png This makes saying things like "Fetch all artists where ..., for each artist fetch their first 3 albums where ..., and for each album fetch the top 10…

Check out this PR. I believe we may have tackled this one but you'd need to try it out on Trino: https://github.com/trinodb/trino/pull/1415

Hooray! Yet another data point for Trino > Presto as far as I'm concerned ;^)

Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation

#15

Post by dmead »

I do the support for my department's trino cluster. We move ~1tb (and growing) in ETL jobs and support interactive queries for the data scientists/analysts.

It would be super good if you guys added big query write support. Its really annoying to have to run a hive cluster in google to act as a proxy for this.

Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation

#16

Post by atwebb »

dmead wrote: post #15

I do the support for my department's trino cluster. We move ~1tb (and growing) in ETL jobs and support interactive queries for the data scientists/analysts. It would be super good if you guys added big query write support. Its really annoying to have to run a hive cluster in google to act as a proxy for this.

Any chance you have an overview of the architecture and operations support required? How many data sources are you pinging?

Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation

#17

Post by jjoonathan »

gavinray wrote: post #8

I recently had to write SQL query generation for AWS Athena, which is based off Presto 0.217 It turns out that the dialect doesn't support LATERAL joins with a LIMIT in them. The below query only works if you remove the LIMIT clause. https://i.stack.imgur.com/rdB1s.png This makes saying things like "Fetch all artists where ..., for each artist fetch their first 3 albums where ..., and for each album fetch the top 10…

AWS Athena: selling a buggy, old, stale copy of someone else's work (Presto / Trino) for high prices and getting away with it because you control the platform.

If that's not peak Amazon, I don't know what is.

Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation

#18

Post by cpard »

ck_one wrote: post #9

Can Trino be used as a Snowflake replacement? How is the query speed compared to Snowflake?

Hey ck_one that's a hard question to answer and not get into "benchmarketing" territory.

My suggestion is to try both under your own workloads and see the difference. Trino is also used by products like Athena (AWS) and Galaxy (Starburst) so if you want to play around and see how Trino performs without spending too much time on setting up clusters on your own, you can try these great products.

Having said that, I'd like to add that building a performant distributed query engine is just hard. Trino has been in development for ten years and used by major companies in very demanding environments, these environments is where the technology has been defined and makes it what it is today and it is a proof of its performance and stability.

(edited to add an important disclaimer that I work at Starburst)

Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation

#19

Post by skadamat »

Big shout out to Brian Olsen from the Trino community (and Starburst) for helping the Trino community be successful

- https://github.com/bitsondatadev

- https://www.linkedin.com/in/bitsondatadev/

I recommend the Trino Slack for people not already in it: https://trino.io/slack.html

Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation

#20

Post by simpligility »

ck_one wrote: post #9

Can Trino be used as a Snowflake replacement? How is the query speed compared to Snowflake?

Yes ... Starburst Enterprise, which is a commercial distribution of Trino, can in fact also query Snowflake, but also Delta Lake and many many other systems at the same time.
Post reply on HN