6 ms·

Casually querying an S3 bucket with either of those is easily a half day's work for any meaningful size of workload. S3 Select and Athena make this very straigh

by dmw_ng 4y ago

Casually querying an S3 bucket with either of those is easily a half day's work for any meaningful size of workload. S3 Select and Athena make this very straightforward, and in the case of Athena+parquet, obscenely cheap given the capability on offer. I wanted to dislike parquet as overengineered masturbation, but that's really hard when getting billed fractions of a cent for chewing through terabytes of data in a few seconds with no setup costs.

ryadh 4y ago
Interesting, why it would take so long ? I'm genuinely curious so that we can make it better (Disclaimer:I work at ClickHouse) Here's a example of a query against a Parquet file you can run on your laptop: ``` ./clickhouse local -q "SELECT town, avg(price) AS avg_price FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet https://datasets-documentation.s3.eu-west-3.amazonaws.com/ho...') GROUP BY town ORDER BY avg_price DESC LIMIT 10" ``` From: https://clickhouse.com/docs/knowledgebase/parquet-to-csv-json#accessing-the-data-using-a-table-function https://clickhouse.com/docs/knowledgebase/parquet-to-csv-jso...
- dmw_ng 4y ago
  Big fan of ClickHouse, but in the case of S3 there doesn't seem to be much point fighting city hall. I was the author of the original clickhouse-local-in-Lambda ticket FWIW. Even if that approach worked well, it'd still only be <50% cheaper than Athena's fractions-of-a-cent for queries on efficiently partitioned data, and still there is setup work involved. I think CH-local-in-Lambda might still easily compete on cost with Athena for raw CSV queries though
  - ryadh 4y ago
    Interesting, thanks for sharing this feedback! I didn't realise intially that it was about running clickhouse-local in Lambdas. Fyi, we recently improved the Parquet support in 23.2 https://github.com/ClickHouse/ClickHouse/pull/45878 https://github.com/ClickHouse/ClickHouse/pull/45878 Also, we still have optimizations for reading Parquet from S3 coming so that might improve
berkle4455 4y ago
> Casually querying an S3 bucket with either of those is easily a half day's work for any meaningful size of workload. Clickhouse and DuckDB you just type: select * from (s3 path and access credentials) Let's see what it takes on AWS Athena: 1) "Before you run your first query, you need to set up a query result location in Amazon S3." 2) What the fuck is a AwsDataCatalog? Why can't I just point it to a file/path? 3) What the fuck is AWS Glue Crawler? Oh shit another service I have to work with. 4) Oops. Glue can't access my bucket, now I have to create a new IAM role for it. 5) Okay, great I can finally run a query! 6) It wrote my results to a text file on S3. Awesome. I feel so goddamn efficient. I'm so fucking glad you suggested this rube goldberg disaster. And if I don't have elevated access to our AWS account, it's not even that simple, instead there's slack messages and emails and jira tickets to get DevOps to provision me and/or set it up for me (incorrect the first time of course). A week later I finally got a text file on a fucking S3 bucket. Awesome.
- chrisjc 4y ago
  Why bother doing any of that. Just set your bucket up with open access, and dump your files anywhere on S3, choose whatever format, compression and schema you want. Don't bother using prefixes or naming convention since that's a whole thing. Searching for what you're looking for is now really easy. Make sure you do a recursive list at the root of the bucket so you know what you're working with. It might take a while to respond, if so just recursively list again and again. Pick the files you want, download to your laptop and voila! Once you've finished munging your results drop it back in the bucket and head home. You're done! Okay sorry for the sarcasm, but what does everyone want? Everyone makes fun of the DataSwamp (DataLake, Lakehouse, etc), but that's what you're gonna get if you're not going to put any effort into learning your data storage strategy or discipline into maintaining and securing it. If someone wanted you to have access to this data, then have them take a few minutes to set you up with an external table. It's probably as easy as `CREATE EXTERNAL TABLE ...`. After all, Data Engineering is just a fancy name for Software Engineering. ;)
  - deleted 4y ago
    [deleted]
tootie 4y ago
S3 Select, Athena, Redshift Spectrum all doing nearly identical things. Classic AWS.

by dmw_ng 4y ago

ryadh 4y ago

Interesting, why it would take so long ? I'm genuinely curious so that we can make it better (Disclaimer:I work at ClickHouse) Here's a example of a query against a Parquet file you can run on your laptop: ``` ./clickhouse local -q "SELECT town, avg(price) AS avg_price FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet https://datasets-documentation.s3.eu-west-3.amazonaws.com/ho...') GROUP BY town ORDER BY avg_price DESC LIMIT 10" ``` From: https://clickhouse.com/docs/knowledgebase/parquet-to-csv-json#accessing-the-data-using-a-table-function https://clickhouse.com/docs/knowledgebase/parquet-to-csv-jso...

dmw_ng 4y ago
Big fan of ClickHouse, but in the case of S3 there doesn't seem to be much point fighting city hall. I was the author of the original clickhouse-local-in-Lambda ticket FWIW. Even if that approach worked well, it'd still only be <50% cheaper than Athena's fractions-of-a-cent for queries on efficiently partitioned data, and still there is setup work involved. I think CH-local-in-Lambda might still easily compete on cost with Athena for raw CSV queries though
- ryadh 4y ago
  Interesting, thanks for sharing this feedback! I didn't realise intially that it was about running clickhouse-local in Lambdas. Fyi, we recently improved the Parquet support in 23.2 https://github.com/ClickHouse/ClickHouse/pull/45878 https://github.com/ClickHouse/ClickHouse/pull/45878 Also, we still have optimizations for reading Parquet from S3 coming so that might improve

berkle4455 4y ago

> Casually querying an S3 bucket with either of those is easily a half day's work for any meaningful size of workload. Clickhouse and DuckDB you just type: select * from (s3 path and access credentials) Let's see what it takes on AWS Athena: 1) "Before you run your first query, you need to set up a query result location in Amazon S3." 2) What the fuck is a AwsDataCatalog? Why can't I just point it to a file/path? 3) What the fuck is AWS Glue Crawler? Oh shit another service I have to work with. 4) Oops. Glue can't access my bucket, now I have to create a new IAM role for it. 5) Okay, great I can finally run a query! 6) It wrote my results to a text file on S3. Awesome. I feel so goddamn efficient. I'm so fucking glad you suggested this rube goldberg disaster. And if I don't have elevated access to our AWS account, it's not even that simple, instead there's slack messages and emails and jira tickets to get DevOps to provision me and/or set it up for me (incorrect the first time of course). A week later I finally got a text file on a fucking S3 bucket. Awesome.

chrisjc 4y ago
Why bother doing any of that. Just set your bucket up with open access, and dump your files anywhere on S3, choose whatever format, compression and schema you want. Don't bother using prefixes or naming convention since that's a whole thing. Searching for what you're looking for is now really easy. Make sure you do a recursive list at the root of the bucket so you know what you're working with. It might take a while to respond, if so just recursively list again and again. Pick the files you want, download to your laptop and voila! Once you've finished munging your results drop it back in the bucket and head home. You're done! Okay sorry for the sarcasm, but what does everyone want? Everyone makes fun of the DataSwamp (DataLake, Lakehouse, etc), but that's what you're gonna get if you're not going to put any effort into learning your data storage strategy or discipline into maintaining and securing it. If someone wanted you to have access to this data, then have them take a few minutes to set you up with an external table. It's probably as easy as `CREATE EXTERNAL TABLE ...`. After all, Data Engineering is just a fancy name for Software Engineering. ;)
- deleted 4y ago
  [deleted]

tootie 4y ago

S3 Select, Athena, Redshift Spectrum all doing nearly identical things. Classic AWS.