Use Case

The SQL output plugin automatically creates the required table in the target database. For this it uses a default create table template that currently has the value CREATE TABLE {TABLE}({COLUMNS}) where {TABLE} is the metric name and {COLUMNS} is a list of column name/type pairs. The plugin has a setting table_template to override this template.

Expected behavior

I would expect this to work with every supported database, including ClickHouse.

Actual behavior

ClickHouse requires that in a CREATE TABLE statement a primary key or a sort key is given, thus the default template above does not work with the ClickHouse database.

There is a section about ClickHouse-specific configuration in the README, but it doesn't mention a mandatory table_template.

The integration test solves this issue by hardcoding an override for the template that reads: CREATE TABLE {TABLE}({COLUMNS}) ENGINE MergeTree() ORDER by timestamp In other words, it specifies the timestamp as the sort key.

Specifying only the timestamp as sort key is a valid approach, but it is not recommended in the ClickHouse world because the timestamp has a high cardinality.

Additional info

What I would like to see (and provide in the PR I created) is this:

• The default template works out of the box with ClickHouse.
• When setting your own table_template you have access to a list of tag columns that you can use as a low-cardinality key instead or in addition to the timestamp.