Re:ClickHouse as an alternative to Elasticsearch for log sto...
by phillc73 on Tuesday March 02, 2021 @03:02PM
(#26319821)
Yeah, I agree sqldf is quite slow. Fair point.
As you've seen, duckdb registers an "R data frame as a virtual table." I'm not sure what they mean by "yet" either.
Of course it is possible to write an R dataframe to an on-disk duckdb table, if that's what you want to do.
There are some simple benchmarks on the bottom of the duckdf README[1]. Essentially I found for basic SQL SELECT queries, dplyr is quicker, but for much more complex queries, the duckdf/duckdb combination performs better.
If you really want speed of course, just use data.table.
Re:ClickHouse as an alternative to Elasticsearch for log sto...
by kristjansson on Tuesday March 02, 2021 @03:25PM
(#26320150)
Didn't realize duckdf was your package. Cool! Definitely something I'll consider for future work, though I spend more time on the Spark-and-Python side of the fence these days.
If you wanted to add corresponding memory benchmarks the value-prop of duckdf might be clearer to those of us that have been scarred by sqldf :).
I appear to have been too pessimistic about duckdf/duckdb though. It's docs[1] claim that registering an R data.frame as a table
> does not actually transfer data into DuckDB yet.
Which implied to me that it just deferred transfer until the user runs a query. However, in a basic test just now:
it appears to execute the query directly against the data frame. At least, it runs in ~2.2s, and doesn't allocate more memory. Cool! As you've noted though, it's very new - looks like they released 0.1.0 last May?I think the point stands: until very recently, SQL-on-dataframes was not a viable choice for anyone working at even moderate scales in R or Python, so preference has been for APIs exposed by libraries (pandas, data.table, dplyr, ...) that offered users reasonable performance, even if SQL would have been be a more ergonomic interface.
[1] https://duckdb.org/docs/api/r