7 ms·

Lesser known features of ClickHouse

158 points by pradeepchhetri 4y ago

zX41ZdbW 4y ago
In clickhouse-local example, you can omit the --structure argument and it will continue to work with automatic schema inference.
zX41ZdbW 4y ago
I recommend to write a follow-up with the topics: 1. clickhouse-git-import tool, what is it and how to use it. 2. ClickHouse as a monitoring agent with the system.asynchronous_log table. 3. Hosting static MergeTree tables on any web server.
- pradeepchhetri 4y ago
  Thank you for your response and going through the blog. I am curious about how to implement 2. ClickHouse as a monitoring agent with the system.asynchronous_log table. Can you share some links around it.
  - zX41ZdbW 4y ago
    About the system.asynchronous_metric_log table. It's very simple. Every ClickHouse instance, even empty, is collecting system-wide monitoring information. You can query this table directly from Grafana dashboard (for example).
    - pradeepchhetri 4y ago
      Good idea, makes sense. Thank you!
- gauravphoenix 4y ago
  >3. Hosting static MergeTree tables on any web server. Can you share some docs on that? AFAIK CH can use web server hosted data sources but I don't recall CH able to host merge tree tables on HTTP servers.
- js2 4y ago
  (1) is answered here https://github.com/apache/incubator-devlake/issues/1771 https://github.com/apache/incubator-devlake/issues/1771
ballenf 4y ago
I don't know what click house is, but that personal site is really awesome.
datalopers 4y ago
The best feature of Clickhouse is replacing entire data engineering and analytics teams and their “modern data platform” with one person and one application.
- deleted 4y ago
  [deleted]
- polskibus 4y ago
  How’s that? How does clickhouse replace ETLs? How does it alleviate data quality problems? It is just a data warehouse , very fast but with limited data modelling and permissioning compared to MS OLAP or tabular models.
  - deleted 4y ago
    [deleted]
  - benjaminwootton 4y ago
    Im not really disagreeing with him, but you can use DBT to materialise 10 daisy chained views, or hit Clickhouse with a query and allow it to crunch data. I am a proponent of modern data stack but it’s always at the back of my mind that an OLAP database on a big box negates the need for a lot of it.
    - polskibus 4y ago
      Sure it does, but you have to feed it somehow and maintain it so that the data there is of good quality and makes business sense. Afaik, clickhouse is not great for multitable joins required by good data warehousing practices like star and snowflake schema.
      - gauravphoenix 4y ago
        CH is actually very good if you can model the dimensional data as dictionary. Also CH can use external store like PSQL as dictionary. But yes, if your fact table data size is as big as dimensional data, CH or any other such system will be quite slow.
deleted 4y ago
[deleted]
slotrans 4y ago
Here's one not mentioned in the article (currently an experimental feature gated by a toggle): ClickHouse can consume a Postgres logical replication stream. Instead of paying for Stitch/Fivetran/etc, you can get OLTP->OLAP replication for free, with no additional software. It's not as flexible as the commercial stuff, and has several sharp edges, but still it's very useful and just an amazing thing to be built-in.
gynther 4y ago
A very good article. Can anyone recommend more like this to learn more about clickhouse?
- nemo44x 4y ago
  https://clickhouse.com/learn/ https://clickhouse.com/learn/
- hodgesrm 4y ago
  https://altinity.com/blog https://altinity.com/blog (Disclaimer: I work at Altinity.)
CSDude 4y ago
We have a use case for wide columnar data, used for mostly performance analytics. There are many types of events that share same columns, but mostly total unique columns are 20x of a typical events columns. My use case is filtering by some boolean logic and aggregating them. I use Elasticsearch with a mapping that does not tokenize text fields, (all strings are keyword type) and it works very well. I can add nodes as I want and adjust shards with ease with spot instances and make it cost effective. I have not found a way/tool to replace this. Many of the tools fail at dynamic data with cardinality. Wanted to use Clickhouse like this, adding columns as they are discovered but it did not go well, but it has been a while. Also adding replicas is not as easy as Elasticsearch. Does anyone have a similar use case implemented with Clickhouse when data is not known before hand and unfolds as time goes by?
- LunaSea 4y ago
  I think you should give PostgreSQL's intarray extension a try, especially using the "@@" QUERY_INT boolean logic queries over GIN indexed columns.
- datalopers 4y ago
  CH approach: Load the dynamic data into a JSON field and use materialized columns when needed for performance beyond calling JSONExtract().
  - gauravphoenix 4y ago
    the "Object" data type works very well and is quite performant. But do note that if you have lots of "schemas" you will need to create multiple such columns that represent such columns e.g. one column each for GitHub,GitLab etc.
    - qoega 4y ago
      Is it really the case? Assume you have column with schema name and one with json object. And your materialised view/JsonExtract can be dispatched by schema name for a row. I see the only suboptimal part if different schemas have different types for the same field and it has to convert it to String.
collegeburner 4y ago
Kind of related question: we really like clkckhouse but want a grafana alternative or at least an alternative to the vertamedia adapter. The adapter burns way more resources then clickhouse itself.
- pradeepchhetri 4y ago
  Grafana Labs recently wrote a new clickhouse adapter. https://github.com/grafana/clickhouse-datasource https://github.com/grafana/clickhouse-datasource https://grafana.com/blog/2022/05/05/introducing-the-official-clickhouse-plugin-for-grafana/ https://grafana.com/blog/2022/05/05/introducing-the-official...
- pradeepchhetri 4y ago
  The vertamedia clickhouse plugin works perfectly for me. Have you tried opening an issue upstream.
- hodgesrm 4y ago
  We maintain the Vertamedia adapter at Altinity and have hundreds of dashboards ourselves plus OSS users have countless thousands more. Can you log an issue so we can look at it? I've never heard of this problem but would love to learn more about it. https://github.com/Altinity/clickhouse-grafana/issues https://github.com/Altinity/clickhouse-grafana/issues

In clickhouse-local example, you can omit the --structure argument and it will continue to work with automatic schema inference.

zX41ZdbW 4y ago

I recommend to write a follow-up with the topics: 1. clickhouse-git-import tool, what is it and how to use it. 2. ClickHouse as a monitoring agent with the system.asynchronous_log table. 3. Hosting static MergeTree tables on any web server.

pradeepchhetri 4y ago
Thank you for your response and going through the blog. I am curious about how to implement 2. ClickHouse as a monitoring agent with the system.asynchronous_log table. Can you share some links around it.
- zX41ZdbW 4y ago
  About the system.asynchronous_metric_log table. It's very simple. Every ClickHouse instance, even empty, is collecting system-wide monitoring information. You can query this table directly from Grafana dashboard (for example).
  - pradeepchhetri 4y ago
    Good idea, makes sense. Thank you!
gauravphoenix 4y ago
>3. Hosting static MergeTree tables on any web server. Can you share some docs on that? AFAIK CH can use web server hosted data sources but I don't recall CH able to host merge tree tables on HTTP servers.
js2 4y ago
(1) is answered here https://github.com/apache/incubator-devlake/issues/1771 https://github.com/apache/incubator-devlake/issues/1771

ballenf 4y ago

I don't know what click house is, but that personal site is really awesome.

datalopers 4y ago

The best feature of Clickhouse is replacing entire data engineering and analytics teams and their “modern data platform” with one person and one application.

deleted 4y ago
[deleted]
polskibus 4y ago
How’s that? How does clickhouse replace ETLs? How does it alleviate data quality problems? It is just a data warehouse , very fast but with limited data modelling and permissioning compared to MS OLAP or tabular models.
- deleted 4y ago
  [deleted]
- benjaminwootton 4y ago
  Im not really disagreeing with him, but you can use DBT to materialise 10 daisy chained views, or hit Clickhouse with a query and allow it to crunch data. I am a proponent of modern data stack but it’s always at the back of my mind that an OLAP database on a big box negates the need for a lot of it.
  - polskibus 4y ago
    Sure it does, but you have to feed it somehow and maintain it so that the data there is of good quality and makes business sense. Afaik, clickhouse is not great for multitable joins required by good data warehousing practices like star and snowflake schema.
    - gauravphoenix 4y ago
      CH is actually very good if you can model the dimensional data as dictionary. Also CH can use external store like PSQL as dictionary. But yes, if your fact table data size is as big as dimensional data, CH or any other such system will be quite slow.

deleted 4y ago

[deleted]

slotrans 4y ago

Here's one not mentioned in the article (currently an experimental feature gated by a toggle): ClickHouse can consume a Postgres logical replication stream. Instead of paying for Stitch/Fivetran/etc, you can get OLTP->OLAP replication for free, with no additional software. It's not as flexible as the commercial stuff, and has several sharp edges, but still it's very useful and just an amazing thing to be built-in.

gynther 4y ago

A very good article. Can anyone recommend more like this to learn more about clickhouse?

nemo44x 4y ago
https://clickhouse.com/learn/ https://clickhouse.com/learn/
hodgesrm 4y ago
https://altinity.com/blog https://altinity.com/blog (Disclaimer: I work at Altinity.)

CSDude 4y ago

We have a use case for wide columnar data, used for mostly performance analytics. There are many types of events that share same columns, but mostly total unique columns are 20x of a typical events columns. My use case is filtering by some boolean logic and aggregating them. I use Elasticsearch with a mapping that does not tokenize text fields, (all strings are keyword type) and it works very well. I can add nodes as I want and adjust shards with ease with spot instances and make it cost effective. I have not found a way/tool to replace this. Many of the tools fail at dynamic data with cardinality. Wanted to use Clickhouse like this, adding columns as they are discovered but it did not go well, but it has been a while. Also adding replicas is not as easy as Elasticsearch. Does anyone have a similar use case implemented with Clickhouse when data is not known before hand and unfolds as time goes by?

LunaSea 4y ago
I think you should give PostgreSQL's intarray extension a try, especially using the "@@" QUERY_INT boolean logic queries over GIN indexed columns.
datalopers 4y ago
CH approach: Load the dynamic data into a JSON field and use materialized columns when needed for performance beyond calling JSONExtract().
- gauravphoenix 4y ago
  the "Object" data type works very well and is quite performant. But do note that if you have lots of "schemas" you will need to create multiple such columns that represent such columns e.g. one column each for GitHub,GitLab etc.
  - qoega 4y ago
    Is it really the case? Assume you have column with schema name and one with json object. And your materialised view/JsonExtract can be dispatched by schema name for a row. I see the only suboptimal part if different schemas have different types for the same field and it has to convert it to String.

collegeburner 4y ago

Kind of related question: we really like clkckhouse but want a grafana alternative or at least an alternative to the vertamedia adapter. The adapter burns way more resources then clickhouse itself.

pradeepchhetri 4y ago
Grafana Labs recently wrote a new clickhouse adapter. https://github.com/grafana/clickhouse-datasource https://github.com/grafana/clickhouse-datasource https://grafana.com/blog/2022/05/05/introducing-the-official-clickhouse-plugin-for-grafana/ https://grafana.com/blog/2022/05/05/introducing-the-official...
pradeepchhetri 4y ago
The vertamedia clickhouse plugin works perfectly for me. Have you tried opening an issue upstream.
hodgesrm 4y ago
We maintain the Vertamedia adapter at Altinity and have hundreds of dashboards ourselves plus OSS users have countless thousands more. Can you log an issue so we can look at it? I've never heard of this problem but would love to learn more about it. https://github.com/Altinity/clickhouse-grafana/issues https://github.com/Altinity/clickhouse-grafana/issues