[V2] Support USE_BINARY_STRING in ClickHouse V2 client

[krvikash]

Describe your feedback

I’m currently working on upgrading the ClickHouse client in Trino from version 0.7.1-patch1 to 0.9.2 #26735.

In the Trino ClickHouse connector, we rely on ClickHouseClientOption.USE_BINARY_STRING. However, in the V2 client, the USE_BINARY_STRING option is no longer supported.

Could you please confirm if there are any plans to support USE_BINARY_STRING (or an equivalent option) in the V2 client?

Use Case:
In ClickHouse

CREATE TABLE test (col_1 String) Engine = Log;
Insert into test values ('Hello ClickHouse');

We are using getBytes (because the value can contain an arbitrary set of bytes, including null bytes as per https://clickhouse.com/docs/sql-reference/data-types/string) to get String, FixedString type data from the result set. By default, we map String, FixedString as Varbinary in Trino.

https://github.com/trinodb/trino/blob/081877b53f9d79c5d8f6b714b836f71a5361efe0/plugin/trino-clickhouse/src/main/java/io/trino/plugin/clickhouse/ClickHouseClient.java#L677

Code example

public class ClickHouseConnect
{
    public static void main(String[] args)
            throws SQLException
    {
        ClickHouseDriver driver = new ClickHouseDriver();
        Properties properties = new Properties();
        properties.setProperty(USE_BINARY_STRING.getKey(), "true");
        properties.setProperty("user", "test");
        properties.setProperty("password", "test");
        Connection connection = driver.connect("jdbc:clickhouse://localhost:33280/default", properties);
        ResultSet resultSet = connection.createStatement().executeQuery("Select * from test");
        while (resultSet.next()) {
            resultSet.getBytes("col_1");
        }
        connection.close();
    }
}

So when we are trying to upgrade from 0.7.1-patch1 to 0.9.2, we are getting below error:

Caused by: java.sql.SQLException: Method: getBytes("col_1") encountered an exception.
	at com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:69)
	at com.clickhouse.jdbc.ResultSetImpl.getBytes(ResultSetImpl.java:399)
	at com.clickhouse.jdbc.ResultSetImpl.getBytes(ResultSetImpl.java:210)
	at io.opentelemetry.instrumentation.jdbc.internal.OpenTelemetryResultSet.getBytes(OpenTelemetryResultSet.java:112)
	at io.trino.plugin.jdbc.StandardColumnMappings.lambda$varbinaryReadFunction$0(StandardColumnMappings.java:350)
	at io.trino.plugin.jdbc.JdbcPageSource.getNextSourcePage(JdbcPageSource.java:206)
	... 20 more
Caused by: com.clickhouse.client.api.ClientException: Column is not of array type
	at com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.getPrimitiveArray(AbstractBinaryFormatReader.java:572)
	at com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.getByteArray(AbstractBinaryFormatReader.java:581)
	at com.clickhouse.jdbc.ResultSetImpl.getBytes(ResultSetImpl.java:393)
	... 24 more

[chernser]

Good day, @krvikash !
Yes, we will support it. From my perspective we need to make it default behavior and convert to String on demand.
There is an issue #2263 and we will do work under this issue.

Would you please share your experience and use case of how you use this feature? It will help us to improve solution.

Thanks!

[krvikash]

Thanks @chernser, for your response. I have updated the description with the use case.

[chernser]

Good day, @krvikash !
I appreciate it!

Thank you!