sql_insert decimal type with clickhouse

[artemklevtsov]

Hi,

Is there a way to insert decimal type to clickhouse?

My test config:

input:
  generate:
    count: 1
    mapping: |
      root.id = 1
      root.x = 3.14

output:
  sql_insert:
    driver: clickhouse
    dsn: clickhouse://127.0.0.1:9000/default
    table: test
    columns:
      - id
      - x
    args_mapping: |
      root = [
        this.id,
        this.x,
      ]
    init_statement: |
      create table if not exists test(id UInt64, x Decimal64(2)) order by id;

Log output:

INFO Running main config from specified file       @service=redpanda-connect benthos_version=4.39.0 path=tmp/config.yaml
INFO Listening for HTTP requests at: http://0.0.0.0:4195  @service=redpanda-connect
INFO Input type generate is now active             @service=redpanda-connect label="" path=root.input
INFO Launching a Redpanda Connect instance, use CTRL+C to close  @service=redpanda-connect
INFO Output type sql_insert is now active          @service=redpanda-connect label="" path=root.output
ERRO Failed to send message to sql_insert: clickhouse [AppendRow]: x clickhouse [AppendRow]: converting float64 to Decimal(18, 2) is unsupported  @service=redpanda-connect label="" path=root.output
ERRO Failed to send message to sql_insert: clickhouse [AppendRow]: x clickhouse [AppendRow]: converting float64 to Decimal(18, 2) is unsupported  @service=redpanda-connect label="" path=root.output
ERRO Failed to send message to sql_insert: clickhouse [AppendRow]: x clickhouse [AppendRow]: converting float64 to Decimal(18, 2) is unsupported  @service=redpanda-connect label="" path=root.output

I tried x.string() without success.

Test environment:

docker run --rm -ti -p 9000:9000 clickhouse/clickhouse-server:24.8-alpine

[mihaitodor]

Hey @artemklevtsov 👋 Could you please try the following?

args_mapping: |
  root = [
    this.id.uint64(),
    this.x.float64(),
  ]

[artemklevtsov]

Thank you for the suggestion, but it don't works:

Log output:

ERRO Failed to send message to sql_insert: clickhouse [AppendRow]: x clickhouse [AppendRow]: converting float64 to Decimal(18, 2) is unsupported  @service=redpanda-connect label="" path=root.output

[artemklevtsov]

I found workaround using sql_raw:

input:
  generate:
    count: 1
    mapping: |
      root.id = 1
      root.x = 3.14

output:
  sql_raw:
    driver: clickhouse
    dsn: clickhouse://127.0.0.1:9000/default
    query: INSERT INTO test (id, x) VALUES ($1, $2);
    args_mapping: |
      root = [
        this.id,
        this.x,
      ]
    init_statement: |
      create table if not exists test(id UInt64, x Decimal64(2)) order by id;