81–90 of 140 posts
I started off with Splunk and every time I try Elasticsearch I feel like I'm stuck in a cage. Probably why they can charge so much for it.
Earlier quoted context omitted.
There's an aggregation part that comes after the CTE, at which point the "friend_count" or "last_login" fields are not available anymore. "active_users" isn't a table, it's the result of the CTE in the query that returns distinct users that have a last_login in the last 30 days. Also, keep in mind that this is a pretty simple example, a more realistic one would probably have a half dozen to a dozen CTEs, some nested,…
> "active_users" isn't a table, it's the result of the CTE in the query Maybe we really are at cross purposes. In the duckdf example above "active_users" is a dataframe. The duckdf library applies SQL queries on dataframes. It's not (necessarily) querying an on-disk database. If you're querying data using dplyr then it's highly likely that data is already in a dataframe. By the same principle write the original SQL q…
You can assume that it would contain the same data that would be in the CTE in the SQL query above. There's no such table in the database, it's a subquery.
If your argument is that chaining multiple SQL queries on data frames is needed, then we're both saying the same thing. SQL doesn't compose well, hence the need for multiple queries. At that point it's not just SQL though, it's querying of dataframes using SQL and composing the dataframes together using non-SQL code.
Luckily, dplyr will actually compose properly, and you can get the actual SQL statement that is equivalent to the dplyr expression, which would be rather annoying to obtain programmatically.
Earlier quoted context omitted.
I agree that Tidyquery and duckdf are quite new, but sqldf has been around for a long time. The first package upload to CRAN was 2007. Also, duckdf uses duckdb[1] to register a virtual in-memory table, so there's very little overhead for data transport. [1] https://duckdb.org/docs/api/r
While sqldf has been around for a while, it's not been a performant choice, since it requires transforming and moving data into a database, albeit one that's in process. So with something like df 3") works, but takes 200 seconds and doubles the memory footprint of the process whereas nrow(df[df$a > 3, ]) sum(df$a > 3) take ~1.5 seconds and ~1s respectively on my machine. I appear to have been too pessimistic about du…
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.
Earlier quoted context omitted.
SQL doesn't compose all that well. For example, imagine that you have a complex query that handles a report. If someone says "hey we need the same report but with another filter on X," your options are to copy paste the SQL query with the change, create a view that can optionally have the filter (assuming the field that you'd want to filter on actually is still visible at the view level), or parse the SQL query into…
I agree that dbplyr is a nice way to query databases, if already familiar with dplyr (actually I think dtplyr is more interesting for operating on data.tables). However, I'm not sure I really understand your point about the "if" statement. If the data is already in a dataframe, why not still use the "if" statement, but one of the packages I mentioned earlier to further modify the data? E.g. if (x = 10) { duckdf("SELE…
Whereas with the SQL packages you mentioned, you either have to conditionally construct your query string (ew) or pull results back and express your conditional as operations on the resulting data frame.
For pure dplyr-on-in-memory-data-frame there isn't much difference between the two, to be sure. For dplyr on dbplyr, or sparklyr, or for SparkR, or PySpark, or Dask, or pick-your-favorite-framework, the difference between operating on (effectively) query AST vs. result sets is huge.
I think it's an unfair comparison, notably because: 1) Clickhouse is rigid-schema + append-only - you can't simply dump semi-structured data (csv/json/documents) into it and worry about schema (index definition) + querying later. The only clickhouse integration I've seen up close had a lot of "json" blobs in it as a workaround, which cannot be queried with the same ease as in ES. 2) Clickhouse scalability is not as s…
I think the author addresses your point one in the article: > SQL is a perfect language for analytics. I love SQL query language and SQL schema is a perfect example of boring tech that I recommend to use as a source of truth for all the data in 99% of projects: if the project code is not perfect, you can improve it relatively easily if your database state is strongly structured. If your database state is a huge JSON…
Logs tend to be rarely read but often written. They also age very quickly and old logs are very rarely read. So putting effort to unify the schemas on write seems very wasteful versus doing so on read. Most of the queries are also text search rather than structured requests so the chance of missing something on read due to bad unification is very low.
Code change frequency is not a measure of quality or development speed.
One organization can encourage bigger PRs while another encourage tiny, frequent changes.
One can care about quality and stability while another can care very little about bugs.
Earlier quoted context omitted.
While sqldf has been around for a while, it's not been a performant choice, since it requires transforming and moving data into a database, albeit one that's in process. So with something like df 3") works, but takes 200 seconds and doubles the memory footprint of the process whereas nrow(df[df$a > 3, ]) sum(df$a > 3) take ~1.5 seconds and ~1s respectively on my machine. I appear to have been too pessimistic about du…
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 m…
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 :).
Earlier quoted context omitted.
When Cloudflare was considering clickhouse, we did estimates on just the hardware cost and it was well over 10x what clickhouse was based on druids given numbers on events processed per compute unit.
Are you saying druid hardware costs were coming out to be 10x of clickhouse hardware costs? Caveat: English is not my first language so might have missed your point in translation. :)
> SQL is a perfect language for analytics. Slightly off topic, but I strongly agree with this statement and wonder why the languages used for a lot of data science work (R, Python) don't have such a strong focus on SQL. It might just be my brain, but SQL makes so much logical sense as a query language and, with small variances, is used to directly query so many databases. In R, why learn the data.tables (OK, speed) o…
SQL doesn't compose all that well. For example, imagine that you have a complex query that handles a report. If someone says "hey we need the same report but with another filter on X," your options are to copy paste the SQL query with the change, create a view that can optionally have the filter (assuming the field that you'd want to filter on actually is still visible at the view level), or parse the SQL query into…
On that topic, I really enjoy working in Elixir because Ecto [1] lets you write "SQL" with Elixir's composable functional syntax. It sits somewhere between "the language is compiled to SQL" and ORM. The Ruby-esque syntax took some getting used to, but once I was past that hurdle my productivity skyrocketed. It's not 100% feature complete compatibility with all the different SQL dialects, but most of what you'll need is there.
Earlier quoted context omitted.
I agree that dbplyr is a nice way to query databases, if already familiar with dplyr (actually I think dtplyr is more interesting for operating on data.tables). However, I'm not sure I really understand your point about the "if" statement. If the data is already in a dataframe, why not still use the "if" statement, but one of the packages I mentioned earlier to further modify the data? E.g. if (x = 10) { duckdf("SELE…
I think the point from GP (which aligns with my thinking) is that more programmatic interfaces for querying data allow for control flow in constructing the query, not just in processing it. So you can conditionally add a predicate or whatever, and then run the whole query at once. Whereas with the SQL packages you mentioned, you either have to conditionally construct your query string (ew) or pull results back and ex…