Error "expected ... arguments, got ..." in SQL output plugin with ClickHouse

[AndreKR]

Relevant telegraf.conf

[[outputs.sql]]
  driver = "clickhouse"
  data_source_name = "[redacted]"

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

[[inputs.ping]]
  urls = ["8.8.8.8", "2001:4860:4860::8888"]

Logs from Telegraf

2025-01-30T23:22:33Z E! [agent] Error writing to outputs.sql: execution failed: clickhouse [Append]:  clickhouse: expected 11 arguments, got 7

System info

Telegraf 1.31.2

Docker

No response

Steps to reproduce

1. Configure the SQL output plugin for a ClickHouse database server
2. Configure an input plugin that has a variable number of fields. In my example the ping plugin will include columns like minimum_response_ms when it received a reply but omit those columns if it didn't receive a reply.
3. Ensure that the input plugin tries to send fewer columns than it could. In the case of the ping plugin make sure it doesn't get a ping back.

Expected behavior

The columns that are missing from the input plugin are simply not be included in the INSERT, causing the database to use default values (like NULL or 0).

Actual behavior

You will see an error similar to this in the log:

Error writing to outputs.sql: execution failed: clickhouse [Append]:  clickhouse: expected 11 arguments, got 7

Additional info

Together with the team of the clickhouse-go database driver I did some troubleshooting and we found out that the clickhouse-go database driver doesn't like the syntax INSERT INTO my_table(col_a, col_b) ..., it expects a space after the table name like this: INSERT INTO my_table (col_a, col_b) .... (ClickHouse/clickhouse-go#1485)

The bug is not entirely trivial to fix because there is some regex parsing involved, so as a quick fix/workaround I propose to add the "missing" space there. All other DBMSs can accept that syntax just as well.