4 ms·

People who've used Clickhouse or other OLAP databases in production & at scale, how do you "interconnect" it with relational data? I'm currently experimenting

People who've used Clickhouse or other OLAP databases in production & at scale, how do you "interconnect" it with relational data? I'm currently experimenting with Clickhouse, because my dataflow is increasing in size (40M rows right now, doubling every month or so) and my current setup (MongoDB) is at its limits. I would like to migrate the 40M rows to CH, but I also need the metadata for the rows to be in something more robust like Postgres or Mongo. Would you have a microservice that does the queries between the OLAP and relational DBs and does the join manually, exposing that as some low-level API? Or is using the various FDW options (remote tables in Clickhouse, Postgres clickhouse_fdw, etc.) realistic in production? Sorry for the ramble, it's late here. But I can clarify if necessary.

deleted 5y ago
[deleted]
unamedrus 5y ago
> I also need the metadata for the rows to be in something more robust like Postgres or Mongo. How big is that metadata? If it's less than tens of millions rows, you can look at External Dictionaries support in clickhouse. https://clickhouse.tech/docs/en/sql-reference/dictionaries/external-dictionaries/external-dicts/#dicts-external-dicts https://clickhouse.tech/docs/en/sql-reference/dictionaries/e... https://altinity.com/blog/2020/5/19/clickhouse-dictionaries-reloaded https://altinity.com/blog/2020/5/19/clickhouse-dictionaries-...
pella 5y ago
there is a new Clickhouse Database Engine: "PostgreSQL" "Allows to connect to databases on a remote PostgreSQL server. Supports read and write operations (SELECT and INSERT queries) to exchange data between ClickHouse and PostgreSQL." https://clickhouse.tech/docs/en/engines/database-engines/postgresql/ https://clickhouse.tech/docs/en/engines/database-engines/pos...
wiredfool 5y ago
I ran across this recently: https://eng.uber.com/logging/ https://eng.uber.com/logging/ Uber's use of clickhouse to handle semi-structured data from logging.
anhldbk 5y ago
How about using CDC with Apache Kafka?
hashhar 5y ago
Take a look at query engines like Trino (formerly PrestoSQL) [https://trino.io/ https://trino.io/]. (Disclaimer: I'm a contributor to Trino). I used it at a previous job to combine data from MongoDB, Kafka, S3 and Postgres to great effect. It tries to push-down as many operations as possible to the source too to improve performance. Full ANSI SQL support over multiple number of backends (Kafka, Cassandra, Postgres, ClickHouse, S3 and many more). The best part is it has a plugin ecosystem so you can very easily implement your own connectors and all the heavy lifting gets done by the core-engine while your plugin only has to abstract your backend to concepts that the engine can understand.
- ckdarby 5y ago
  Came here to say the same great things about Trino
- flakiness 5y ago
  Offtopic, but I didn't realize there is prestosql(now trino)[1] and prestodb. Was only aware of prestodb side. I love the paper [3], which was apparently written before they left FB to form the new company. [1] https://trino.io/blog/2020/12/27/announcing-trino.html [2] https://prestodb.io/ [3] https://trino.io/Presto_SQL_on_Everything.pdf
- LaserToy 5y ago
  The problem with Trino is that it is not that easy to scale to possible RPS of Clickhouse, it introduces tons a of latency and push downs are faaaar from perfect. Uber has a smart solution for Pinot, when they run it as a single node proxies
  - hashhar 5y ago
    I wouldn't use Trino if you are looking to ONLY query a single database like Clickhouse or Postgres etc (unless you want an ANSI SQL abstraction over your choice of database). Obviously ClickHouse and Postgres will have lower latency when hit directly because you can bypass the analysis, planning, optimization and scheudling that Trino does. It does federation better than ClickHouse and that's where it shines. Joins across disparate systems - even between relational and non-relational systems. And obviously for the MPP queries on distributed filesystems.
fnord77 5y ago
Don't know about CH, but with apache druid, you can "join" with a rdbms query via a LOOKUP sql function.
hodgesrm 5y ago
ClickHouse can select and insert directly from/to remote MySQL [1] and PostgreSQL [2] tables. See MySQL and PostgreSQL database engines. It's a common way to access mutable dimension data as well as to pull data into ClickHouse for analysis. I have not used PostgreSQL myself but the MySQL database engine works great. In some cases queries from ClickHouse to MySQL run faster than they do on MySQL itself. There are other engines as well, e.g., MongoDB. [1] https://clickhouse.tech/docs/en/engines/table-engines/integrations/mysql/ https://clickhouse.tech/docs/en/engines/table-engines/integr... [2] https://clickhouse.tech/docs/en/engines/table-engines/integrations/postgresql/ https://clickhouse.tech/docs/en/engines/table-engines/integr...
mason55 5y ago
Really depends what you're trying to do with your OLAP database. If you're using it purely for reporting purposes then you really don't want to interconnect it with your OLTP database to support real-time queries. Reason number one is that you don't want some unexpected analytics workload to suddenly impact your production Postgres. Reason number two is that your analytics data model and your OLTP data model are frequently different. Usually your OLAP model needs to know what the value of a given dimension was at the time the event occurred, but if you're linking directly to Postgres then you can only see what the values are right now. You can also go the other direction if you need the current values of OLTP data joined with your Clickhouse data: schedule an ETL out of Clickhouse and back into your OLTP database and aggregate the data to a reasonable level such that Posgres can handle it without a problem. What's your actual use case? I'd normally consider a requirement to join OLAP & OLTP data in real time to be a "design smell". I don't mean that there's no value in things like fdw or easier ways to move data around, but you should consider using it to help with the ETL process and not as a real-time interconnect. Keep OLTP & OLAP workloads separate and both of your DBs will be happier.
- momothereal 5y ago
  Alright, here is an example. My data is a stream of events, each row has the values (event_id, person_id, date). This is the table with 40M rows, with inserts between 2/s and 10/s. person_id is a foreign ID for a "person table" that fits well in the relational model. A "person" has various attributes (name, DOB, email) as well as a one-to-many relation to "groups". Groups are a collection of users with additional attributes (e.g. group name). Now, what if I want to answer questions like: - how many total events for all persons in group X - who are the top 10 users in number of events in group X - which are the top 10 groups in number of events In this case, the person/group tables are part of the core business logic, they aren't specific to the events table. It doesn't make sense to store it in Clickhouse. Also, this person/group data gets updated sparsely, but "freshness" should be kept at a minimum (< 30secs). The simple approach to the first question would be - Get all the user IDs in group X - Filter events by those user IDs But what if there are tens of thousands of users in group X? And hundreds of groups? Are megabyte-long queries supported in Clickhouse?

deleted 5y ago

[deleted]

unamedrus 5y ago

> I also need the metadata for the rows to be in something more robust like Postgres or Mongo. How big is that metadata? If it's less than tens of millions rows, you can look at External Dictionaries support in clickhouse. https://clickhouse.tech/docs/en/sql-reference/dictionaries/external-dictionaries/external-dicts/#dicts-external-dicts https://clickhouse.tech/docs/en/sql-reference/dictionaries/e... https://altinity.com/blog/2020/5/19/clickhouse-dictionaries-reloaded https://altinity.com/blog/2020/5/19/clickhouse-dictionaries-...

pella 5y ago

there is a new Clickhouse Database Engine: "PostgreSQL" "Allows to connect to databases on a remote PostgreSQL server. Supports read and write operations (SELECT and INSERT queries) to exchange data between ClickHouse and PostgreSQL." https://clickhouse.tech/docs/en/engines/database-engines/postgresql/ https://clickhouse.tech/docs/en/engines/database-engines/pos...

wiredfool 5y ago

I ran across this recently: https://eng.uber.com/logging/ https://eng.uber.com/logging/ Uber's use of clickhouse to handle semi-structured data from logging.

anhldbk 5y ago

How about using CDC with Apache Kafka?

hashhar 5y ago

Take a look at query engines like Trino (formerly PrestoSQL) [https://trino.io/ https://trino.io/]. (Disclaimer: I'm a contributor to Trino). I used it at a previous job to combine data from MongoDB, Kafka, S3 and Postgres to great effect. It tries to push-down as many operations as possible to the source too to improve performance. Full ANSI SQL support over multiple number of backends (Kafka, Cassandra, Postgres, ClickHouse, S3 and many more). The best part is it has a plugin ecosystem so you can very easily implement your own connectors and all the heavy lifting gets done by the core-engine while your plugin only has to abstract your backend to concepts that the engine can understand.

ckdarby 5y ago
Came here to say the same great things about Trino
flakiness 5y ago
Offtopic, but I didn't realize there is prestosql(now trino)[1] and prestodb. Was only aware of prestodb side. I love the paper [3], which was apparently written before they left FB to form the new company. [1] https://trino.io/blog/2020/12/27/announcing-trino.html [2] https://prestodb.io/ [3] https://trino.io/Presto_SQL_on_Everything.pdf
LaserToy 5y ago
The problem with Trino is that it is not that easy to scale to possible RPS of Clickhouse, it introduces tons a of latency and push downs are faaaar from perfect. Uber has a smart solution for Pinot, when they run it as a single node proxies
- hashhar 5y ago
  I wouldn't use Trino if you are looking to ONLY query a single database like Clickhouse or Postgres etc (unless you want an ANSI SQL abstraction over your choice of database). Obviously ClickHouse and Postgres will have lower latency when hit directly because you can bypass the analysis, planning, optimization and scheudling that Trino does. It does federation better than ClickHouse and that's where it shines. Joins across disparate systems - even between relational and non-relational systems. And obviously for the MPP queries on distributed filesystems.

fnord77 5y ago

Don't know about CH, but with apache druid, you can "join" with a rdbms query via a LOOKUP sql function.

hodgesrm 5y ago

ClickHouse can select and insert directly from/to remote MySQL [1] and PostgreSQL [2] tables. See MySQL and PostgreSQL database engines. It's a common way to access mutable dimension data as well as to pull data into ClickHouse for analysis. I have not used PostgreSQL myself but the MySQL database engine works great. In some cases queries from ClickHouse to MySQL run faster than they do on MySQL itself. There are other engines as well, e.g., MongoDB. [1] https://clickhouse.tech/docs/en/engines/table-engines/integrations/mysql/ https://clickhouse.tech/docs/en/engines/table-engines/integr... [2] https://clickhouse.tech/docs/en/engines/table-engines/integrations/postgresql/ https://clickhouse.tech/docs/en/engines/table-engines/integr...

mason55 5y ago

Really depends what you're trying to do with your OLAP database. If you're using it purely for reporting purposes then you really don't want to interconnect it with your OLTP database to support real-time queries. Reason number one is that you don't want some unexpected analytics workload to suddenly impact your production Postgres. Reason number two is that your analytics data model and your OLTP data model are frequently different. Usually your OLAP model needs to know what the value of a given dimension was at the time the event occurred, but if you're linking directly to Postgres then you can only see what the values are right now. You can also go the other direction if you need the current values of OLTP data joined with your Clickhouse data: schedule an ETL out of Clickhouse and back into your OLTP database and aggregate the data to a reasonable level such that Posgres can handle it without a problem. What's your actual use case? I'd normally consider a requirement to join OLAP & OLTP data in real time to be a "design smell". I don't mean that there's no value in things like fdw or easier ways to move data around, but you should consider using it to help with the ETL process and not as a real-time interconnect. Keep OLTP & OLAP workloads separate and both of your DBs will be happier.

momothereal 5y ago
Alright, here is an example. My data is a stream of events, each row has the values (event_id, person_id, date). This is the table with 40M rows, with inserts between 2/s and 10/s. person_id is a foreign ID for a "person table" that fits well in the relational model. A "person" has various attributes (name, DOB, email) as well as a one-to-many relation to "groups". Groups are a collection of users with additional attributes (e.g. group name). Now, what if I want to answer questions like: - how many total events for all persons in group X - who are the top 10 users in number of events in group X - which are the top 10 groups in number of events In this case, the person/group tables are part of the core business logic, they aren't specific to the events table. It doesn't make sense to store it in Clickhouse. Also, this person/group data gets updated sparsely, but "freshness" should be kept at a minimum (< 30secs). The simple approach to the first question would be - Get all the user IDs in group X - Filter events by those user IDs But what if there are tens of thousands of users in group X? And hundreds of groups? Are megabyte-long queries supported in Clickhouse?