Can anyone clarify the differences between Trino and SparkSQL? Our company has used SparkSQL to aggressively replace use-cases that were based on PrestoSQL in the past.
The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation
41–50 of 70 posts
Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation
#42Can 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.
hey do you work for the company. Prbly add a disclaimer.
Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation
#43Trino vs ClickHouse, can anyone tell from experience how those two compare?
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.
Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation
#44Can anyone clarify the differences between Trino and SparkSQL? Our company has used SparkSQL to aggressively replace use-cases that were based on PrestoSQL in the past.
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.
Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation
#45Earlier quoted context omitted.
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.
I think many users just see they can execute a query on huge data cheaply and incredibly quickly and are delighted. That's certainly my experience. It's one of the backends available in Splink, our FOSS record linkage software and it's revolutionary how it allows users to execute large scale probabilistic record linkage ridiculously cheaply. It wasn't long ago you needed very expensive proprietary software plus a big…
The biggest value for corporate users is that they get everything already included as part of their existing cloud agreement.
Adding a new vendor to the mix needs to involve procurement, the legal team, vendor negotiations, while using a new AWS feature is just a matter of using it, even if it's not as good as the original ISV's version and doesn't support the long term viability of the project.
Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation
#46I 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…
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]Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation
#47Can you use Trino as a database proxy?
Essentially yes ... it can be the query engine for many databases at the same time.
Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation
#48The thing I wonder about with Presto and to a lesser extent Spark is, how many of their users adopted this tool because it was an easy migration path from Hive, and how many of those users will eventually re-platform to something else?
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 i…
Yeah I think that is the key question: will data lakes become the dominant paradigm? There is certainly a lot of talk around them, though I see a ton of companies are still just going all in on a conventional data warehouse, but they tend not to talk about it because it’s not a new or interesting thing to do.
Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation
#49Can anyone clarify the differences between Trino and SparkSQL? Our company has used SparkSQL to aggressively replace use-cases that were based on PrestoSQL in the past.
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.
I must disclaim that I contribute to Trino.
I agree but it depends a bit on what purpose you are using them for. If you mainly use the tool to JOIN some data in bulk and then write output somewhere else (i.e. ETL) - either will serve you fine.
If you write complex queries with multiple filters and want to JOIN across multiple datasets - sure Spark can do that as well but it's not as efficient in pushing down computation to the source.
e.g. A query like SELECT c.custkey, sum(totalprice) FROM orders o INNER JOIN customer c ON o.custkey = c.custkey WHERE o.orderstatus = 'O' GROUP BY c.custkey; when ran on Spark will pull both tables into memory and then perform the join + filter for orderstatus = 'O' and then compute the sum.
While in case of Trino it'll push down the entire query into the remote database (in this case, in other queries it'll push down some parts of the query) so the source database will not need to return gigabytes of data over the network every time the query runs (and hence finish faster as well).
Trino tries to push-down some operations to the remote system which can be done more efficiently there. e.g. filtering on a column that has an index in the remote RDBMS will be faster than pulling all data and then filtering in Trino. Spark doesn't have strong pushdown and has to pull most of the raw data and then apply processing on top of it.
That's one of the main differences. Spark is a distributed job execution framework first while Trino is a distributed federated query engine first and it shows in their strengths and weaknesses.
If you want to run arbitrary user defined transformations on data then Spark definitely has much more to offer than Trino.
Re: The SQL query engine Trino (formerly PrestoSQL) recaps a decade of innovation
#50Earlier quoted context omitted.
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.
hey do you work for the company. Prbly add a disclaimer.
Yes... where would I put the disclaimer?