Skip to main content

ClickHouse

Time-travel support​

Time-travel is not supported by ClickHouse. This means that updating applications based on this Database are subject to data desynchronization in case of changes in the Database.
Read this page to learn more about the visible effects of this desynchronization.

Vectors support​

ClickHouse handles vectors natively, via the Array type in the model and thanks to special functions at query time. Thus, in DirectQuery, this is the only supported mode for dealing with this data type.

Table engines support​

Among the MergeTree family, Atoti supports only the simple MergeTree table engine (as well as it's Replicated or Distributed variant).
The reason behind is that more complex table engines (such as AggregatingMergeTree) require more complex SQL queries to return accurate results. See more information on table engines.

Gotchas​

Nullable fields​

ClickHouse can define fields with nullable types. DirectQuery is capable of detecting this and defines its internal model accordingly.
While this is not a problem in itself, it can conflict with the rule in Atoti+ cubes that all levels must be based on non-nullable values. This does not create an issue of any sort as the local model is updated behind the scene, assigning a default value based on the type.
It has a side effect on the query performance, as DirectQuery must convert on-the-fly null values to their assigned default values, as well as adding extra conditions during joins to handle null values on both sides.

Flatten the data model​

Atoti+ Datastore technology has highly optimized joins. This is not the case in ClickHouse, that has general purpose joins.
As ClickHouse is very fast at handling wide tables (since it's stored by columns), it is advised to use as few tables as possible and flatten the data model.
Dictionaries are an alternative to joined tables for small cardinalities. They are supported by DirectQuery and presented as standard tables.

One notable drawback of using a flat model is that building one hierarchy requires a full scan of a large table. As a consequence, the more non-virtual hierarchies a project has, the longer it takes to have a project ready to use.

Foreign keys​

Joins between tables perform faster when the keys are of an integer type (such as UInt64). It's advised to avoid other types, such as String.