[exporter/clickhouse] Injection of projection into table schema via config

[fredthomsen]

Component(s)

exporter/clickhouse

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

I am using the clickhouseexporter to get data into clickhouse and multiple different services/applications read that data, but the current table schema may allow a query to be done in a way that is scalable or performs well.

Describe the solution you'd like

Injection of projection in the schema DDL via configuration as follows:

clickhouse:
  endpoint: tcp://clickhouse:9000
  projections:
    gauge:
      app1_query_projection: |
        SELECT * 
        ORDER BY toUnixTimestamp64Nano(TimeUnix), Attributes['context.id'], ResourceAttributes['resource.name']

Describe alternatives you've considered

More customizable DDL statements for table schema creation in general.

Additional context

No response

[github-actions]

Pinging code owners:

• exporter/clickhouse: @hanjm @dmitryax @Frapschen

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

[crobert-1]

Hello @fredthomsen, can you format your suggested configuration to make the indentation of options clear? I'm having a hard time following when it's one line.

[fredthomsen]

Hello @fredthomsen, can you format your suggested configuration to make the indentation of options clear? I'm having a hard time following when it's one line.

Ok @crobert-1 yeah that did look bad. Update made

[crobert-1]

I'll have to defer to the code owners on design decisions and configuration options, but from documentation this sounds like a good idea to me. Thanks for the suggestion!

[hanjm]

The projection optimization is heavily associated with the specific query scenarios after writing. The collector merely establishes the tables required for writing at startup and will not modify the schema once it's created.
Hence, it is suggested to operate the Clickhouse tables directly as needed, rather than placing it at the collector.

Add some documentation on how to use projection and materialized views to optimize telemetry data querying would be worthwhile.

[fredthomsen]

Yeah, I concede that knowing all the specific querying scenarios at deployment time is unlikely, although we had a few cases where we thought we could specify them up front.

[github-actions]

This issue has been inactive for 60 days. It will be closed in 60 days if there is no activity. To ping code owners by adding a component label, see Adding Labels via Comments, or if you are unsure of which component this issue relates to, please ping @open-telemetry/collector-contrib-triagers. If this issue is still relevant, please ping the code owners or leave a comment explaining why it is still relevant. Otherwise, please close it.

Pinging code owners:

• exporter/clickhouse: @hanjm @dmitryax @Frapschen

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

[RoryCrispin]

My preferred implementation of this is to add a flag to the exporter which disables table creation. Then, you can manage the tables yourself with whatever schema you like, using your preferred DB migrator setup.
Then just emplace the tables you need and set the table names in the OTel configuration.

We run this in prod and insert into a table with Null engine and build materialized views based off from that, where we can have different TTL, indexes and materialized columns (from LogAttributes and ResourceAttributes) for each application type

I'll be happy to raise a PR for this

[hanjm]

@RoryCrispin Looks good 👍

[RoryCrispin]

Cc @SpencerTorres

[github-actions]

This issue has been inactive for 60 days. It will be closed in 60 days if there is no activity. To ping code owners by adding a component label, see Adding Labels via Comments, or if you are unsure of which component this issue relates to, please ping @open-telemetry/collector-contrib-triagers. If this issue is still relevant, please ping the code owners or leave a comment explaining why it is still relevant. Otherwise, please close it.

Pinging code owners:

• exporter/clickhouse: @hanjm @dmitryax @Frapschen @SpencerTorres

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

[github-actions]

This issue has been inactive for 60 days. It will be closed in 60 days if there is no activity. To ping code owners by adding a component label, see Adding Labels via Comments, or if you are unsure of which component this issue relates to, please ping @open-telemetry/collector-contrib-triagers. If this issue is still relevant, please ping the code owners or leave a comment explaining why it is still relevant. Otherwise, please close it.

Pinging code owners:

• exporter/clickhouse: @hanjm @dmitryax @Frapschen @SpencerTorres

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

[SpencerTorres]

@crobert-1 This can be closed, we solved this by adding a flag to toggle auto table creation (#32282). In the latest version, the user can create their own tables however they need.