sql_insert: Cannot insert Bloblang objects as ClickHouse maps

[benwebber]

It is not possible to insert Bloblang objects as ClickHouse Map(K, V) values using sql_insert.

The problem seems to be that Connect does not pass the object to the ClickHouse driver as a map[K]V, but rather a map[K]interface {}.

ERRO[2025-12-02T13:07:49-05:00] Failed to send message to sql_insert: clickhouse [AppendRow]: data clickhouse [AppendRow]: converting map[string]interface {} to Map(String, Int64) is unsupported. try using map[string]int64  @service=redpanda-connect label="" path=root.output

Here is a simple example that tries to insert a String: Int64 object:

input:
  generate:
    mapping: |
      root = {uuid_v4(): random_int(timestamp_unix_nano())}

output:
  sql_insert:
    driver: clickhouse
    dsn: "${CLICKHOUSE_DSN:clickhouse://default:default@localhost:9000/default}"
    init_statement: |
      CREATE TABLE IF NOT EXISTS example (
        data Map(String, Int64)
      )
      ENGINE = Memory
    table: example
    columns:
      - data
    args_mapping: |
      root = [this]

I've sometimes found it necessary to call type conversion methods using the ClickHouse driver. That has no effect here:

root = {uuid_v4().string(): random_int(timestamp_unix_nano()).int64()}

I found a workaround using sql_raw and JSONExtract. If we pass the value as a JSON string, ClickHouse can cast it to a Map.

input:
  generate:
    mapping: |
      root = {uuid_v4(): random_int(timestamp_unix_nano())}

output:
  sql_raw:
    driver: clickhouse
    dsn: "${CLICKHOUSE_DSN:clickhouse://default:default@localhost:9000/default}"
    init_statement: |
      CREATE TABLE IF NOT EXISTS example (
        data Map(String, Int64)
      )
      ENGINE = Memory
    query: |
      INSERT INTO example (data) VALUES (JSONExtract($1, 'Map(String, Int64)'))
    args_mapping: |
      root = [
        this.format_json().string(),
      ]