7 ms·

While people indeed love reinventing the wheel you can literally do that today with clickhouse or duckdb. select * from a parquet file wherever it resides.

hackan 4y ago
we are pretty much literally doing that :D
crop_rotation 4y ago
It doesn't seem to be the same thing though. Like duckdb doesn't seem to use s3 select. The difference is that duckdb seems to be doing the filtering here. If you use s3 select s3 will give you result of the sql query. Would be good to know if any existing solution does it. > select * from a parquet file wherever it resides. I am sure any tool can do this. The point was to push the query to the source if you are issuing tons of them.
- deleted 4y ago
  [deleted]
- simonw 4y ago
  DuckDB pulls a clever trick with SQL and Parquet where it uses the HTTP range header to fetch back only the data it needs to answer the query. The design of Parquet supports this. If you run `select sum(cost) from sales` and the cost column is all in the same byte range, DuckDB will need to fetch just the header that tells it the offset of that cost column and then just the bytes that form the cost column - saving a huge amount of bandwidth. Running `select * from sales` will still have to scan over a much larger section of the file though.
  - crop_rotation 4y ago
    Unless the file is very small, the column would most likely not be in the same byte range. Duckdb also can't filter on multiple columns without fetching both. Being able to fully preselect data at the source is much more powerful than the parquet tricks duckdub uses. Don't get me wrong, the duckdb impl is good and generic and will support any parquet store, but it is not as powerful as S3 select.
  - tomnipotent 4y ago
    > the cost column is all in the same byte range Parquet is hybrid, so you have random chunks of contiguous bytes scattered throughout the file.
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

hackan 4y ago

we are pretty much literally doing that :D

crop_rotation 4y ago

It doesn't seem to be the same thing though. Like duckdb doesn't seem to use s3 select. The difference is that duckdb seems to be doing the filtering here. If you use s3 select s3 will give you result of the sql query. Would be good to know if any existing solution does it. > select * from a parquet file wherever it resides. I am sure any tool can do this. The point was to push the query to the source if you are issuing tons of them.

deleted 4y ago
[deleted]
simonw 4y ago
DuckDB pulls a clever trick with SQL and Parquet where it uses the HTTP range header to fetch back only the data it needs to answer the query. The design of Parquet supports this. If you run `select sum(cost) from sales` and the cost column is all in the same byte range, DuckDB will need to fetch just the header that tells it the offset of that cost column and then just the bytes that form the cost column - saving a huge amount of bandwidth. Running `select * from sales` will still have to scan over a much larger section of the file though.
- crop_rotation 4y ago
  Unless the file is very small, the column would most likely not be in the same byte range. Duckdb also can't filter on multiple columns without fetching both. Being able to fully preselect data at the source is much more powerful than the parquet tricks duckdub uses. Don't get me wrong, the duckdb impl is good and generic and will support any parquet store, but it is not as powerful as S3 select.
- tomnipotent 4y ago
  > the cost column is all in the same byte range Parquet is hybrid, so you have random chunks of contiguous bytes scattered throughout the file.

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