Documentation Index
Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
Use this file to discover all available pages before exploring further.
Time-travel support
Time-travel is not supported natively by ClickHouse. This means that updating applications based on this Database are subject to data de-synchronization in case of changes in the Database.Read this page to learn more about the visible effects of this de-synchronization. However, it’s possible to set-up a mechanism to emulate time-travel to avoid such issues.
Vectors support
ClickHouse handles vectors natively, via theArray 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 simpleMergeTree table engine (as well as its Replicated or Shared variant). More complex table engines (such as
AggregatingMergeTree) require more complex SQL queries to return accurate results.
See more information on table engines.
Supported types
The following Clickhouse data types are supported by Atoti, i.e. they are automatically read and converted to their Java equivalent:- UInt8, UInt16, UInt32, UInt64, UInt128, UInt256
- Int8, Int16, Int32, Int64, Int128, Int256
- Float32, Float64
- Decimal, Decimal32, Decimal64, Decimal128, Decimal256
- Bool
- String, FixedString
- Date, Date32
- DateTime, DateTime32, DateTime64
- Array (only arrays of scalar are supported, not arrays of arrays)
- Enum8, Enum16, UUID, IPv4, IPv6 (they are converted to String)
Gotchas
Null values as a join result (join_use_nulls=1)
Our Clickhouse connector uses the SQL standard regarding the handling of null values as a result of a join. For example on a left join some missing keys in the right table will result in null value in returned rows. However, the default behavior in ClickHouse is to fill empty cells with default values (based on the type) instead of null. If querying outside our java connector, in a standard sql client for instance, you can set this property addingsettings join_use_nulls=1 after your select query.
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 asUInt64). It’s advised to avoid other types, such as String.
Optimize join order
Starting from ClickHouse version 25.9, the settingquery_plan_optimize_join_order_limit allows optimization of join order. This setting defines the number of tables for which join order optimization is attempted.
While this feature has limited use cases currently, it may improve performance in specific scenarios.