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.

Works just fine in Trino.

  trino> USE memory.default;
  USE
  trino:default> create table artist (artistid int);
  CREATE TABLE
  trino:default> create table album (albumid int, artistid int);
  CREATE TABLE
  trino:default> insert into artist values 1, 2;
  INSERT: 2 rows
  
  Query 20220804_182827_00005_n4rat, FINISHED, 1 node
  Splits: 19 total, 19 done (100.00%)
  0.52 [0 rows, 0B] [0 rows/s, 0B/s]
  
  trino:default> insert into album values (11, 1), (12, 1), (21, 2);
  INSERT: 3 rows
  
  Query 20220804_182857_00006_n4rat, FINISHED, 1 node
  Splits: 19 total, 19 done (100.00%)
  0.18 [0 rows, 0B] [0 rows/s, 0B/s]
  
  trino:default> select * from (select * from artist limit 2) a cross join lateral (select * from album where album.artistid = a.artistid limit 2);
   artistid | albumid | artistid
  ----------+---------+----------
          1 |      12 |        1
          1 |      11 |        1
          2 |      21 |        2
  (3 rows)
  
  Query 20220804_182930_00007_n4rat, FINISHED, 1 node
  Splits: 41 total, 41 done (100.00%)
  0.35 [8 rows, 232B] [22 rows/s, 661B/s]

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

Steven here, I'm Ahana CEO (Managed Service for Presto) and member of Linux Foundation's Presto Foundation Since you asked about an alternative to AWS Athena, we offer a free-forever in-VPC Managed Service for Presto on AWS, which keeps up with the latest Presto releases.

Note: the original Presto continues to run in production at Meta (fmr. Facebook), Uber, and recently ByteDance TikTok data platform talked about running 1M queries a day with tens of thousands of cores. Some reasons to stay with Presto: - Reliability and scalability per above - Cutting edge innovations only in later versions of Presto: multi-level caching (project RaptorX) to boost query performance by 10X+ and table scan improvements (project Aria) to name a few - Only PrestoDB is hosted by Linux Foundation, giving confidence to community users that future releases will remain open.

Is it possible to achieve this with a window function?

<3

Thanks for the shoutout! :)

If you want to get started with Trino, here's a repo I created to do so: https://github.com/bitsondatadev/trino-getting-started

For managing difrerent workloads, check out this blogs and this videos from Shopify, Salesforce, Goldman Sachs, and Electronic Arts, respectively:

- https://engineering.salesforce.com/how-to-etl-at-petabyte-sc... - https://shopify.engineering/faster-trino-query-execution-inf... - https://trino.io/episodes/33.html - https://www.youtube.com/watch?v=-5mlZGjt6H4

All use the Lyft "Presto but really Trino"-Gateway project to run different clusters to handle various workloads. They go into various details for how this is achieved.

https://github.com/lyft/presto-gateway

Regarding the Trino/Presto split. I recommend looking at this blog to better understand why these two communities aren't mergeing. TL;DR Presto is a Facebook-driven project that mainly considers running on the Facebook infrastructure. Trino is community-driven that works on running well with all clouds and common infasturcture in the Trino community which is why you see a higher velocity there.

https://trino.io/blog/2022/08/02/leaving-facebook-meta-best-...https://trino.io/blog/2020/12/27/announcing-trino.html

Soon we anticipate that Trino will become the common name in the community space but we'll always love the origins of the Trino project being Presto.

BigQuery very recently announced their Storage Write API which is one of the ways we were looking to implement this but there are some issues with the latency and consistency guarantees that it offers.

But, yes, we do plan to add that eventually after ironing out all the kinks. See https://github.com/trinodb/trino/pull/13094

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

I mean, the hive migration path is one thing. Now that Iceberg is taking over the old Hive model, data lakes are all the rage again.

The other thing I would say is that Trino and Presto are not one-trick ponies or just hive replacements. There's also the ability to query across multiple systems that is, to me, the feature that future proofs a lot of architectures. It inherently frees you up to fiddle with your data in different systems but keep the access to that system in one location.

I can chime in in an okayish useful manner.

Apart from implementation details, probably not much different. It is similar to mysql vs postgresql. You are probably okay with either.

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)

Also with Iceberg or Delta Lake and Trino you can basically run a full lakehouse architecture on open source and places like Bloomberg, Goldman Sachs and many others do just that.

https://trino.io/users.html

Not sure if those are comparable 1:1. Do you mean you have a datalake that you are using snowflake to query using 'external tables' that you want to replace with trino ?

i can't speak to trino, but with my experience with aws athena and snowflake, they're roughly on par with each other across the board.

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.

Clickhouse is a realtime system where Trino is a batch-oriented system. There are tradeoffs for doing realtime vs batch.

Realtime is generally more expensive to run as you process every individual row as it comes, batch is when you can deal with minute latency and want to handle a lot of data in chunks.

Trino is also a query engine rather than a database and it connects to many different systems: https://trino.io/docs/current/connector.html

It also happens to connect to Clickhouse and it's very common that people will use Trino to query clickhouse realtime data and join it with data in big query, an object store data lake, or Snowflake: https://trino.io/docs/current/connector/clickhouse.html

You can consider that ClickHouse allows both to query a lot of supported external data sources(s3/hdfs/mysql/postgre/...) and to store data in pretty efficient columnar way with compression, indexes and all the bells and whistles. Native storage allows to use all the information about keys/indices to build query plan faster. With trino you can't store data inside trino. You can't even insert data using trino which allows you to solve scenarios like 'readonly analytics'. Trino allows you to use single query language for all the supported systems. So if you have a zoo of DBMS and object storages that you can just query it can help you to hide this complexity.

Essentially yes ... it can be the query engine for many databases at the same time.