6 ms·

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

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 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 tracks where ..." really difficult Does Trino support this out of curiosity?

karma_fountain 4y ago
Is it possible to achieve this with a window function?
- gavinray 4y ago
  I found out it is! Kudos to this kind internet stranger for telling me: https://stackoverflow.com/a/73129836/13485494 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 <= 2 │ └── • window │ estimated row count: 2 │ └── • hash join │ estimated row count: 2 │ equality: (x) = (a) │ ├── • scan │ estimated row count: 6 (100% of the table; stats collected 2 minutes ago) │ table: xyz@xyz_pkey │ spans: FULL SCAN │ └── • scan estimated row count: 1 (100% of the table; stats collected 3 minutes ago) table: abc@abc_pkey spans: FULL SCAN
bitsondatadev 4y ago
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 https://github.com/trinodb/trino/pull/1415
- gavinray 4y ago
  Hooray! Yet another data point for Trino > Presto as far as I'm concerned ;^)
  - bitsondatadev 4y ago
    If you want to try an SaaS Athena alternative that's backed by Trino you can check out Starburst Galaxy: https://www.starburst.io/platform/starburst-galaxy/ https://www.starburst.io/platform/starburst-galaxy/ Full disclosure I work at Starburst.
    - gavinray 4y ago
      Oh nice, I have high opinions of you folks! Guy who goes by the name of "Randgalt" online builds some great Java libraries and works there too I believe.
      - simpligility 4y ago
        Yep .. Jordan works on Trino and Starburst Galaxy. We got lots of other great engineers helping as well btw. https://github.com/randgalt https://github.com/randgalt
    - ofrzeta 4y ago
      On the homepage there's two occurrences of "ELT" (instead of "ETL" I would guess). Is that correct?
jjoonathan 4y ago
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.
- lopatin 4y ago
  Can’t you say the same thing for EC2 but with Linux instead of Presto? Personally I like Athena. The fact that it’s in the Amazon platform and managed is a plus for me.
  - jjoonathan 4y ago
    EC2 involved substantial VM management and networking innovations that I respect. Ditto lambda and S3. I would not categorize any of these as OSS flips in nearly the way that I would categorize Athena as an OSS flip.
- RobinL 4y ago
  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 on prem cluster, costing in the hundreds of thousands, to achieve this. A lot of the magic for me is on the infrastructure side: how they can read/write large datasets from s3 so quickly, so the value isn't just in the SQL engine.
  - jjoonathan 4y ago
    Yes, it's good to be platform king. We know. Low friction for you, high friction for everyone else.
  - jfim 4y ago
    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.
hashhar 4y ago
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]
stevenmih 4y ago
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.
- bitsondatadev 4y ago
  - DataGeekette 4y ago
    Or possibly he is just passionate about a project that he believes in, just like others in the larger Presto / Trino community. Personally, I think both Ahana and Starburst have interesting takes on how to monetize the original Presto platform. And like many, many other data projects, there is room in the market for different platforms. Long gone are the days where everyone was just stuck with a single RDMBS to do everything!
  - DataGeekette 4y ago
    But IMO, personal attacks seem pretty off topic for a thread about cool tech.
    - deleted 4y ago
      [deleted]

karma_fountain 4y ago

Is it possible to achieve this with a window function?

gavinray 4y ago
I found out it is! Kudos to this kind internet stranger for telling me: https://stackoverflow.com/a/73129836/13485494 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 <= 2 │ └── • window │ estimated row count: 2 │ └── • hash join │ estimated row count: 2 │ equality: (x) = (a) │ ├── • scan │ estimated row count: 6 (100% of the table; stats collected 2 minutes ago) │ table: xyz@xyz_pkey │ spans: FULL SCAN │ └── • scan estimated row count: 1 (100% of the table; stats collected 3 minutes ago) table: abc@abc_pkey spans: FULL SCAN

bitsondatadev 4y ago

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 https://github.com/trinodb/trino/pull/1415

gavinray 4y ago
Hooray! Yet another data point for Trino > Presto as far as I'm concerned ;^)
- bitsondatadev 4y ago
  If you want to try an SaaS Athena alternative that's backed by Trino you can check out Starburst Galaxy: https://www.starburst.io/platform/starburst-galaxy/ https://www.starburst.io/platform/starburst-galaxy/ Full disclosure I work at Starburst.
  - gavinray 4y ago
    Oh nice, I have high opinions of you folks! Guy who goes by the name of "Randgalt" online builds some great Java libraries and works there too I believe.
    - simpligility 4y ago
      Yep .. Jordan works on Trino and Starburst Galaxy. We got lots of other great engineers helping as well btw. https://github.com/randgalt https://github.com/randgalt
  - ofrzeta 4y ago
    On the homepage there's two occurrences of "ELT" (instead of "ETL" I would guess). Is that correct?

jjoonathan 4y ago

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.

lopatin 4y ago
Can’t you say the same thing for EC2 but with Linux instead of Presto? Personally I like Athena. The fact that it’s in the Amazon platform and managed is a plus for me.
- jjoonathan 4y ago
  EC2 involved substantial VM management and networking innovations that I respect. Ditto lambda and S3. I would not categorize any of these as OSS flips in nearly the way that I would categorize Athena as an OSS flip.
RobinL 4y ago
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 on prem cluster, costing in the hundreds of thousands, to achieve this. A lot of the magic for me is on the infrastructure side: how they can read/write large datasets from s3 so quickly, so the value isn't just in the SQL engine.
- jjoonathan 4y ago
  Yes, it's good to be platform king. We know. Low friction for you, high friction for everyone else.
- jfim 4y ago
  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.

hashhar 4y ago

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]

stevenmih 4y ago

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.

bitsondatadev 4y ago
- DataGeekette 4y ago
  Or possibly he is just passionate about a project that he believes in, just like others in the larger Presto / Trino community. Personally, I think both Ahana and Starburst have interesting takes on how to monetize the original Presto platform. And like many, many other data projects, there is room in the market for different platforms. Long gone are the days where everyone was just stuck with a single RDMBS to do everything!
- DataGeekette 4y ago
  But IMO, personal attacks seem pretty off topic for a thread about cool tech.
  - deleted 4y ago
    [deleted]