Row with map column not inserted when async_insert used

[ivenhov]

Hi

I have a table as follows

CREATE TABLE t_map
(
    `audit_seq` Int64 CODEC(Delta(8), LZ4),
    `timestamp` Int64 CODEC(Delta(8), LZ4),
    `event_type` LowCardinality(String),
    `event_subtype` LowCardinality(String),
    `actor_type` LowCardinality(String),
    `actor_id` String,
    `actor_tenant_id` LowCardinality(String),
    `actor_tenant_name` String,
    `actor_firstname` String,
    `actor_lastname` String,
    `resource_type` LowCardinality(String),
    `resource_id` String,
    `resource_container` LowCardinality(String),
    `resource_path` String,
    `origin_ip` String,
    `origin_app_name` LowCardinality(String),
    `origin_app_instance` String,
    `description` String,
    `attributes` Map(String, String)
)
ENGINE = MergeTree
ORDER BY (resource_container, event_type, event_subtype, date)
SETTINGS index_granularity = 8192

I'm using Spring JDBC to insert data using ? parameters.

Example insert will have syntax

INSERT INTO default.t_map VALUES (8481365034795008,1673349039830,'operation-9','a','service', 'bc3e47b8-2b34-4c1a-9004-123656fa0000','b', 'c', 'service-56','d', 'object','e', 'my-value-62', 'mypath', 'some.hostname.address.com', 'app-9', 'instance-6','x', {'key1':'value1'})

All works OK until I add to above INSERT

SETTINGS async_insert=1,wait_for_async_insert=0

At this point row is not inserted and silently discarded.
This still works via client CLI though for some reason.

Also when map is specified as null it works as expected regardless if SETTINGS is used or not

Any help appreciated
Regards
Daniel

[zhicwu]

Hi @ivenhov, thanks for reporting the issue. Just so be clear, you're using latest driver(0.3.2-patch11 and com.clickhouse.jdbc.ClickHouseDriver), right? I tried on ClickHouse 22.8 and it works well.

Also the table scheme is incomplete as date column was missing from the DDL.

[ivenhov]

Hi
Yes, I'm using 0.3.2-patch11
Sorry for the bad statement, my mistake when simplifying it, so date can be removed.

I've been debugging it for a while now and cannot get it to work.
I've noticed method setObject() in SqlBasedPreparedStatement sets the template for attributes column to String rather to Map<String,String>
https://github.com/ClickHouse/clickhouse-jdbc/blob/64816243e119ba6de331e1a2c8642643657d85ec/clickhouse-jdbc/src/main/java/com/clickhouse/jdbc/internal/SqlBasedPreparedStatement.java#L599

Debug log shows as below, note quoted value for the last column.

16:35:17.678 [main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: INSERT INTO default.tenant_1001_data SETTINGS async_insert=1,wait_for_async_insert=0 VALUES (8486470985973760, 1673368517504, 'operation-9', '', 'service', '2c91da13-e787-410a-a74d-cf28e9143641', '', '', 'service-82', '', 'my-object', '', 'my-bucket-34', 'my-path', '', '', '', '', '{key1=value1}')

when I remove SETTING I see different log

16:41:20.410 [main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: INSERT INTO default.tenant_1001_data 
 FORMAT RowBinary

I guess I'm doing something wrong and conversion from JDBC types to Clickhouse type. At the moment I'm using new SqlParameter(Types.OTHER) for that column.

From what I can gather template is build in JdbcParameterizedQuery parse() and it always set values to null
https://github.com/ClickHouse/clickhouse-jdbc/blob/64816243e119ba6de331e1a2c8642643657d85ec/clickhouse-jdbc/src/main/java/com/clickhouse/jdbc/JdbcParameterizedQuery.java#L48

Possibly I'm doing something wrong how I set up Spring's JDBC template.
Is there an example with Spring jdbcTemplate and map?

Thanks

[zhicwu]

Thanks for providing more details and sorry for the inconvenience at your end.

When SqlBasedPreparedStatement is used, it usually means JDBC driver failed to infer parameter types, so it has to fall back to String substitution. This is probably related to #1152 we talked about earlier. Have you tried nightly build?

I guess I'm doing something wrong and conversion from JDBC types to Clickhouse type. At the moment I'm using new SqlParameter(Types.OTHER) for that column.

Map is mapped to Types.STRUCT but it should work as long as Spring passes a Map object to PreparedStatement.setObject(), for example: stmt.setObject(1, Collections.singletonMap("key1", "value1"));

Is there an example with Spring jdbcTemplate and map?

We don't have the example now. Do you have a simplified repo for reproducing the issue? I can help to debug and see if it's a bug in JDBC driver.

[ivenhov]

Thanks for your suggestions
I did try with clickhouse-jdbc:0.3.3-SNAPSHOT and it seems to fix the problem.
Also works with com.clickhouse:clickhouse-jdbc:0.4.0-SNAPSHOT

Logging changes with new version

From
[main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: INSERT INTO default.example SETTINGS async_insert=1,wait_for_async_insert=0 VALUES (1673439717843, '{key1=value1}')

to
[main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: INSERT INTO default.example SETTINGS async_insert=1,wait_for_async_insert=0 
 FORMAT RowBinary

However it looks like that version also upgrades dependency of slf4j to 2.x which has a ripple effect on Spring 2.7 projects,.
Spring Boot 2.7 relies on slf4j-api 1.7 so upgrade to never Clickhouse jdbc causes logging to be disabled for the entire application.
The problem is described here spring-projects/spring-boot#12649

I'm not sure why 0.3.3 upgrades slf4j, it seems like a breaking change?
This is a reason I'm still on 0.3.2-patch11
I don't think there is something in-between, with fixes but using old slf4j ?

I've attached example project for you using Docker with testcontainers.
It's a bit convoluted as it's a stripped down version of my project.
Let me know if you need any help with the setup.

clickhouse-map-search.zip

Class to execute is called SearchIntegrationTest. Breakpoint on waitPostCreation() and inspection of example table shows row is not added but rather silently dropped.
The main logic is in TableDAO
There is a method there (currently not used) doInsertUsingDatasource() which workarounds the problem by using PreparedStatement rather than jdbcTemplate.

Thanks for your help with this
Daniel

[ivenhov]

FYI migration to spring boot 3.0 resolves the problem with slf4j conflict so logging in the application works ok now.
This is a bit drastic step however so I would like to know if there is any other solution?
Have you had any chance to look into the example code?
Also how 'official' are 0.3.3-SNAPSHOT and 0.4.0-SNAPSHOT? Is is OK to use those instead of 0.3.2-patch11 ?

Thanks
Daniel

[zhicwu]

Good to know slf4j is no longer an issue. But it sounds odd to me because it's supposed to be optional. Anyway, I made a few more changes in these days which may solve the problem. Will generate a new build tonight for you to test.

As to your question, nightly build should be same as release, because we use same set of tests for build verification. Having said that, I'll release 0.4.0 tomorrow and hopefully without too many patches we can move to 0.5 soon.

[ivenhov]

I've migrated my project to Spring Boot 3 so slf4j is no longer a problem for me. I'm not entirely sure where the problem was.
I'm now using clickhouse-jdbc 0.4.0 and that also works as expected.
Thank you for the build and your assistance.

Daniel

[zhicwu]

Thanks for the update. slf4j was a transitive dependency introduced by apache httpclient, which is optional. It's been removed in nightly build and will be available in 0.4.1.