Problems with outputs.sql.convert using clickhouse driver

[timetofaceit]

Relevant telegraf.conf

[[outputs.sql]]
  driver = "clickhouse"
  data_source_name = "tcp://MY_IP:9000?username=user&password=pass&database=database"
  table_template = "CREATE TABLE IF NOT EXISTS {TABLE}({COLUMNS}) ENGINE = MergeTree() PRIMARY KEY (timestamp,host) ORDER by (timestamp,host) Partition by toYYYYMMDD(timestamp)"
  table_exists_template = "SELECT 1 FROM {TABLE} LIMIT 1"
  [outputs.sql.convert]
    integer = "Int64"
    real = "Float64"
    text = "String"
    timestamp = "DateTime"
    defaultvalue = "String"
    unsigned = "UInt64"
    bool = "UInt8"

Logs from Telegraf

[agent] Error writing to outputs.sql: code: 62, message: Syntax error: failed at position 133 ('UInt64'): UInt64,"err_out" Int64 UInt64,"drop_in" Int64 UInt64,"drop_out" Int64 UInt64,"bytes_sent" Int64 UInt64,"bytes_recv" Int64 UInt64,"packets_sent" Int64 UInt64,"pa. Expected one of: COLLATE, NOT, NULL, DEFAULT, MATERIALIZED, ALIAS, EPHEMERAL, AUTO_INCREMENT, COMMENT, CODEC, TTL, token, Comma, ClosingRoundBracket

System info

Telegraf version - 1.26.3, Clickhouse version - 23.4.2.11

Steps to reproduce

1. Start collecting metrics with Telegraf;
2. Telegraf fails to send metrics with the error above.

Expected behavior

Telegraf creates Database Tables automatically and sends metrics.

Actual behavior

Telegraf fails with error above.

Additional info

Seems to be the same problem that was couple of years back - #10671, #10673

[powersj]

And based on those previous issues did you try setting conversion_style = "literal"? That was introduced for this same problem with clickhouse.

[timetofaceit]

Yes! This solves that problem! Thanks!

I tried to add conversion_style but I was adding "unsigned_suffix" value like it says in the documentation instead of "literal". Somehow I managed to miss "literal" value... 😢

Also, I have another error now:
[agent] Error writing to outputs.sql: prepare failed: code: 16, message: No such column in in table

Maybe you know what can be the cause of this? Because some of the columns are created and some are not. In this error for some reason in column can't be created in my database table.

[powersj]

No such column in in table

It is reporting that there is no column "in" in the table. That seems a little weird to have a column named "in", so I would check your template and metric data.

[timetofaceit]

Hmm, but this in column (same as out column which is also can't be created) is coming from [[inputs.swap]] 🤔

https://github.com/influxdata/telegraf/blob/master/plugins/inputs/swap/README.md

[powersj]

Then you may need to rename the columns, or ensure that the table_template actually created the columns in the first place. Based on the docs, it sounds like keywords like "from" and "in" are not reserved and can be used for column names, but need to be quoted. I would ensure that the table exists and exists with the columns that you expect, then try telegraf again.

[timetofaceit]

If I understand correctly, my current table_template should do the trick or is there anything else I should add to it? Can't find proper documentation for that 😞

[timetofaceit]

OK, I have managed to sort everything out, but I think I found a bug.

I have a Redis cluster and I want to send his metrics to ClickHouse. The problem is that telegraf can't add missing columns to existing table... In my case redis table is created from master node, because it is starting quicker then slave node and then slave can't add his missing columns...

@powersj maybe you have experience with this and is this i a bug or I'm missing something in my telegraf config again?

[powersj]

Before any write, the plugin will check if the table exists. Only if the table does not exist does the plugin create a table based on the table template + metric that was passed to it.

In your case, you have an input plugin that produces multiple metrics. We generally do not want telegraf trying to create or update a table on every single metric that comes by. If required, this is left to the user to create a custom table exists template and/or custom template where you can create the missing columns.

[timetofaceit]

Understood, thanks @powersj for your help! 🙏

[SeanGaluzzi]

@timetofaceit
i got the same error: [agent] Error writing to outputs.sql: prepare failed: code: 16, message: No such column IpForwarding in table database.nstat (fcd32348-5634-4d6f-af5a-ad4fa0113796)
how did you manage to fix it?

this is my configuration so far:

[[inputs.system]]

[[inputs.cpu]]
  percpu = true
  totalcpu = true
  fieldexclude = ["time_*"]

[[inputs.mem]]

[[inputs.disk]]
  mount_points = ["/"]

[[inputs.net]]
  interfaces = ["eth0"]

[[inputs.nstat]]

[[outputs.sql]]
  driver = "clickhouse"
  data_source_name = "tcp://x.x.x.x:9000?username=default&password=xxx&database=database"
  table_template = "CREATE TABLE IF NOT EXISTS {TABLE}({COLUMNS}) ENGINE = MergeTree() PRIMARY KEY (timestamp,host) ORDER by (timestamp,host) Partition by toYYYYMMDD(timestamp)"
  table_exists_template = "SELECT 1 FROM {TABLE} LIMIT 1"

  [outputs.sql.convert]
    integer = "Int64"
    real = "Float64"
    text = "String"
    timestamp = "DateTime"
    defaultvalue = "String"
    unsigned = "UInt64"
    bool = "UInt8"

    conversion_style = "literal"