Clickhouse TTL misleading

[safaci2000]

Component(s)

exporter/clickhouse

Is your feature request related to a problem? Please describe.

Please correct me if any of this is incorrect.

Currently the TTL only applies if you use create_schema: true, otherwise it's essentially ignored. Also, if the value is changed, then it won't be reflected on the DB.

Describe the solution you'd like

I would like to suggest either we:

1. Update the code to run an ALTER TABLE ... ON CLUSTER .... when the collector comes up against the cluster to ensure the TTL is respected.
2. Update the config to reflect the fact that the ttl is a bootstrap setting.

If a user looks at the config they would assume that all those settings would carry over rather than being silently ignored.

Describe alternatives you've considered

No response

Additional context

A few more notes, according to the docs The query is executed locally on the host and then the query is written by the host to ZK. I believe this should be fairly safe but you might run into the same SQL being executed multiple times. Alternatively you could run something like:

Disclaimer, AI assisted and needs further validation.

-- Step 1: Suppress full rewrite during the ALTER
ALTER TABLE my_table ON CLUSTER my_cluster
  MODIFY SETTING materialize_ttl_recalculate_only = 1;

-- Step 2: Apply the TTL change (lightweight, only updates ttl.txt)
ALTER TABLE my_table ON CLUSTER my_cluster
  MODIFY TTL event_time + INTERVAL 90 DAY;

-- Step 3: Reset the setting back to default
ALTER TABLE my_table ON CLUSTER my_cluster
  RESET SETTING materialize_ttl_recalculate_only;
--OR
-- Step 3:
ALTER TABLE my_table ON CLUSTER my_cluster
  MODIFY SETTING materialize_ttl_recalculate_only = 0;

-- Step 4: NOW actually apply TTL to existing data
ALTER TABLE my_table ON CLUSTER my_cluster
  MATERIALIZE TTL;

Where existing data is not updated (heavy operation) and only the meta data is updated which is a very easy and light operation.

Tip

_{React with 👍 to help prioritize this issue. Please use comments to provide useful context, avoiding +1 or me too, to help us triage it. Learn more here.}

[github-actions]

Pinging code owners:

• exporter/clickhouse: @hanjm @Frapschen @SpencerTorres

See Adding Labels via Comments if you do not have permissions to add labels yourself.

[axw]

Perhaps also the config shouldn't be ignored if create_schema==false, i.e. make it a config validation error? Let's see what the code owners say.

[SpencerTorres]

We recommend disabling create_schema in production. If you have a larger deployment with multiple collectors running, they shouldn't all be racing to run CREATE TABLE and ALTER TABLE statements. We're actually moving away from adding these schema-specific config features in favor of users creating their own schema (or using an external schema management system).

If we were to change this, I would probably add a schema block to the config that contained such settings to make it clear that we only create the schema, never modify it. Schema management is already painful to coordinate in large deployments when done correctly, I cannot imagine relying on multiple collectors to do it right.

I agree we should update the docs to prevent any confusion, thank you for the suggestion 👌

[safaci2000]

Expecting the user to create their own table schema is perfectly reasonable but there should be a way to communicate that. I'm not sure how you would sync up the user's schema preferences with Otel behavior.

How do you see this working? The user provides as SQL with the schema to use? How do you map a trace/log/profile etc to a given otel table? Do you point to a DB with table name and let the collector, discover all the fields? How do you rename a field that does not match the otel format 1 to 1.

Providing some guidance to someone fairly new to ClickHouse user on how to create a simple deployment would be good. I know when you ask for tools you get 50 different opinions but some advice on how to get started would be great.

I'm not sure what the correct behavior but for now, if we're doing a NoOp when the user asked to create/update the schema, calling it out in the logs would be good. I would even argue it should cause an error if the config has a TTL mismatch and it fully ignores it.