ClickHouse as an alternative to Elasticsearch for log storage and analysis • Hacker News Board

Re: ClickHouse as an alternative to Elasticsearch for log storage and analysis

#71

Post by jfim » Tue, Mar 02, 2021, 7:11 PM UTC

phillc73 wrote: post #66

jfim wrote: post #57

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…

It's for more complex queries, eg. active users per country:

  WITH active_users AS
    (SELECT DISTINCT user_id, user_country FROM ...
     WHERE last_login >= NOW() - 1 month)
  SELECT user_country, COUNT(user_id) AS user_count
    FROM active_users GROUP BY user_country
    ORDER BY user_count DESC

Now imagine someone says "what about users that have at least 5 friends?" If you're using dplyr and want to reuse most of your logic, it's just a matter of doing something like

  active_users_with_friends = active_users %>% filter(friend_count >= 5)

The SQL version is much hairier, since it's just code that's within a string.

Re: ClickHouse as an alternative to Elasticsearch for log storage and analysis

#72

Post by phillc73 » Tue, Mar 02, 2021, 7:19 PM UTC

jfim wrote: post #71

phillc73 wrote: post #66

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…

It's for more complex queries, eg. active users per country: WITH active_users AS (SELECT DISTINCT user_id, user_country FROM ... WHERE last_login >= NOW() - 1 month) SELECT user_country, COUNT(user_id) AS user_count FROM active_users GROUP BY user_country ORDER BY user_count DESC Now imagine someone says "what about users that have at least 5 friends?" If you're using dplyr and want to reuse most of your logic, it's…

I think we're talking at cross purposes, and I don't want to belabour the point too much, but there's no need to modify the original SQL query (I'm imagining this pulls data from a database). If the data is already in a dataframe, use one of the libraries which can apply SQL to in-memory dataframes.

e.g.

active_users_with_friends = 5")

Re: ClickHouse as an alternative to Elasticsearch for log storage and analysis

#73

Post by KaoruAoiShiho » Tue, Mar 02, 2021, 7:25 PM UTC

moralestapia wrote: post #21

Sorry to hijack the thread but can anyone suggest alternatives to the 'search' side of Elasticsearch? I haven't been following the topic and there's probably new and interesting developments like ClickHouse is for logging.

I just use elasticsearch, nothing wrong with it so far.

Re: ClickHouse as an alternative to Elasticsearch for log storage and analysis

#74

Post by jfim » Tue, Mar 02, 2021, 7:33 PM UTC

phillc73 wrote: post #72

jfim wrote: post #71

Earlier quoted context omitted.

It's for more complex queries, eg. active users per country: WITH active_users AS (SELECT DISTINCT user_id, user_country FROM ... WHERE last_login >= NOW() - 1 month) SELECT user_country, COUNT(user_id) AS user_count FROM active_users GROUP BY user_country ORDER BY user_count DESC Now imagine someone says "what about users that have at least 5 friends?" If you're using dplyr and want to reuse most of your logic, it's…

I think we're talking at cross purposes, and I don't want to belabour the point too much, but there's no need to modify the original SQL query (I'm imagining this pulls data from a database). If the data is already in a dataframe, use one of the libraries which can apply SQL to in-memory dataframes. e.g. active_users_with_friends = 5")

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, at which point correctly mutating the SQL statement is not trivial.

Re: ClickHouse as an alternative to Elasticsearch for log storage and analysis

#75

Post by ekimekim » Tue, Mar 02, 2021, 7:34 PM UTC

wikibob wrote: post #29

wakatime wrote: post #5

A related database using ideas from Clickhouse: https://github.com/VictoriaMetrics/VictoriaMetrics

Are you familiar with VictoriaMetrics? Can you elaborate on how it is similar and dissimilar to Clickhouse? What specific techniques are the same?

The core storage engine borrows heavily from it - I'll attempt to summarize and apologies for any errors, it's been a while since I worked with VictoriaMetrics or ClickHouse.

Basically data is stored in sorted "runs". Appending is cheap because you just create a new run. You have a background "merge" operation that coalesces runs into larger runs periodically, amortizing write costs. Reads are very efficient as long as you're doing range queries (very likely on a time-series database) as you need only linearly scan the portion of each run that contains your time range.

Re: ClickHouse as an alternative to Elasticsearch for log storage and analysis

#76

Post by BoorishBears » Tue, Mar 02, 2021, 7:38 PM UTC

anaphor wrote: post #42

tgtweak wrote: post #32

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…

You might be able to just put whatever you want into an Elasticsearch index, but I wouldn't recommend doing that. It could severely limit how you can query your data later, see: https://www.elastic.co/guide/en/elasticsearch/reference/curr... Also it can cause performance problems if you have really heterogeneous data with lots of different fields https://www.elastic.co/guide/en/elasticsearch/reference/curr...

Yup, reading that comment all I thought was exactly what I said in another comment here, it'll work great until it doesn't, and by then you'll suffer a lot to work around it

Same with scaling, scaling ES is super easy until you realize your index sizes aren't playing nicely with sharding or something and have to start working around that.

Clickhole feels like it's targeting what most people end up using ES for. Comparing it to ES and talking about what's missing is kind of missing the point imo.

Re: ClickHouse as an alternative to Elasticsearch for log storage and analysis

#77

Post by daniel_levine » Tue, Mar 02, 2021, 7:38 PM UTC

didip wrote: post #70

Does ClickHouse have integration with Superset and Grafana?

Yes to both, Altinity maintains the ClickHouse Grafana plugin https://altinity.com/blog/2019/12/28/creating-beautiful-graf...

And Superset has a recommendation of a ClickHouse connector https://superset.apache.org/docs/databases/clickhouse

Re: ClickHouse as an alternative to Elasticsearch for log storage and analysis

#78

Post by LASR » Tue, Mar 02, 2021, 7:40 PM UTC

anaphor wrote: post #42

tgtweak wrote: post #32

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…

You might be able to just put whatever you want into an Elasticsearch index, but I wouldn't recommend doing that. It could severely limit how you can query your data later, see: https://www.elastic.co/guide/en/elasticsearch/reference/curr... Also it can cause performance problems if you have really heterogeneous data with lots of different fields https://www.elastic.co/guide/en/elasticsearch/reference/curr...

Yeah we learned this the hard way. We had a field, status: 200. After a while, we introduced some new logging that emitted status: success.

Since the auto-index used a number type instead of string, we hit some issues trying to reindex the already ingested data.

Re: ClickHouse as an alternative to Elasticsearch for log storage and analysis

#79

Post by phillc73 » Tue, Mar 02, 2021, 7:49 PM UTC

jfim wrote: post #74

phillc73 wrote: post #72

Earlier quoted context omitted.

I think we're talking at cross purposes, and I don't want to belabour the point too much, but there's no need to modify the original SQL query (I'm imagining this pulls data from a database). If the data is already in a dataframe, use one of the libraries which can apply SQL to in-memory dataframes. e.g. active_users_with_friends = 5")

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 query to use SELECT with wildcard, to return everything. Then use a relevant library to apply SQL on dataframes and it's pretty much the same result as dplyr.

That's not to say complex SQL queries shouldn't be used to only return the minimally required information from the database. Clearly, sometimes there's too much data to fit in memory. My point is that if you're using dplyr on dataframes (or tibbles), then you could just as easily use an SQL based query on the same dataframe.

Re: ClickHouse as an alternative to Elasticsearch for log storage and analysis

#80

Post by kristjansson » Tue, Mar 02, 2021, 7:52 PM UTC

phillc73 wrote: post #60

kristjansson wrote: post #58

Earlier quoted context omitted.

I think the answer to your question is that, until relatively recently it was not possible to run arbitrary SQL against data frames in either language. sqldf and duckdf pass data off to another program (albeit in-process), run the query there, and pull the result set back to R, paying data movement and transportation costs along the way. Tidyquery is neat and avoids that issue, but is also only 18 months old or so

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 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:

    con = dbConnect(duckdb::duckdb(), ":memory:")
    duckdb::duckdb_register(con, "iris_view", iris)
    duckdb::duckdb_register(con, "df_view", df)
    dbGetQuery(con, "select count(*) from df_view where a > 0")

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