search

ClickHouse / clickhouse-java

ClickHouse Java Clients & JDBC Driver
https://clickhouse.com
Apache License 2.0
1.45k stars 534 forks source link

Error on Initial Data Insertion into Table with JSON Column #1427

Open BentsiLeviav opened 1 year ago

BentsiLeviav commented 1 year ago

Describe the bug

When attempting to insert data for the first time into an empty table that includes a JSON column, the process triggers an exception as follows:

Exception in thread "main" java.lang.NumberFormatException: For input string: "{}" at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:67) at java.base/java.lang.Integer.parseInt(Integer.java:668) at com.clickhouse.data.value.UnsignedByte.valueOf(UnsignedByte.java:78) at com.clickhouse.data.value.UnsignedByte.valueOf(UnsignedByte.java:61) at com.clickhouse.data.value.ClickHouseByteValue$UnsignedByteValue.update(ClickHouseByteValue.java:93) at com.clickhouse.data.value.ClickHouseByteValue$UnsignedByteValue.update(ClickHouseByteValue.java:16) at com.clickhouse.data.ClickHouseValue.update(ClickHouseValue.java:1236) at com.clickhouse.data.value.ClickHouseByteValue.update(ClickHouseByteValue.java:408) at com.clickhouse.data.value.ClickHouseByteValue$UnsignedByteValue.update(ClickHouseByteValue.java:16) at com.clickhouse.data.format.ClickHouseRowBinaryProcessor$TupleSerializer.serialize(ClickHouseRowBinaryProcessor.java:243) at com.clickhouse.data.ClickHouseDataProcessor.write(ClickHouseDataProcessor.java:593) at com.clickhouse.jdbc.internal.InputBasedPreparedStatement.addBatch(InputBasedPreparedStatement.java:345) at com.clickhouse.jdbc.internal.InputBasedPreparedStatement.executeAny(InputBasedPreparedStatement.java:113) at com.clickhouse.jdbc.internal.InputBasedPreparedStatement.executeLargeUpdate(InputBasedPreparedStatement.java:197) at com.clickhouse.jdbc.internal.AbstractPreparedStatement.executeUpdate(AbstractPreparedStatement.java:135) at org.example.Main.main(Main.java:37)

Expected behavior

Expected functionality entails the successful insertion of JSON into an empty table.

Cause

This issue arises from the underlying behavior of ClickHouse's JSON representation, which is based on Tuples. If no JSON data has been previously inserted, the default type inferred is a Tuple of Int8. Consequently, the JDBC driver encounters difficulty in serializing the JSON string, leading to the reported error.

Current Process

The driver currently deduces the schema by executing the query: SELECT column FROM table WHERE 0. However, the ClickHouse team has suggested an alternative schema inference mechanism utilizing the query: desc table tableName format TSVWithNamesAndTypes. This revised approach returns JSON as a data type, rather than Tuple.

Steps to reproduce

  1. Create the following table 
    CREATE TABLE public.tst
(
`id` String,
`o` Object('json')
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
PRIMARY KEY id
ORDER BY id
SETTINGS index_granularity = 8192
  2. Run the code example provided below

Code example


public static void main(String[] args) throws SQLException {
        String url = "jdbc:ch:https://******.clickhouse.cloud:8443/public"; // use http protocol and port 8123 by default
        Properties properties = new Properties();
        properties.setProperty("username", "default");
        properties.setProperty("password", "password");
        ClickHouseDataSource dataSource = new ClickHouseDataSource(url, properties);

        try (Connection con = DriverManager
                .getConnection(url, properties)) {

            try (PreparedStatement stmt = con.prepareStatement(
                "INSERT INTO tst (id, o) VALUES (?, ?)")) {
                stmt.setString(1, "test");
                stmt.setString(2, "{}");
                stmt.executeUpdate();

            }
        }
    }

Error log

Exception in thread "main" java.lang.NumberFormatException: For input string: "{}" at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:67) at java.base/java.lang.Integer.parseInt(Integer.java:668) at com.clickhouse.data.value.UnsignedByte.valueOf(UnsignedByte.java:78) at com.clickhouse.data.value.UnsignedByte.valueOf(UnsignedByte.java:61) at com.clickhouse.data.value.ClickHouseByteValue$UnsignedByteValue.update(ClickHouseByteValue.java:93) at com.clickhouse.data.value.ClickHouseByteValue$UnsignedByteValue.update(ClickHouseByteValue.java:16) at com.clickhouse.data.ClickHouseValue.update(ClickHouseValue.java:1236) at com.clickhouse.data.value.ClickHouseByteValue.update(ClickHouseByteValue.java:408) at com.clickhouse.data.value.ClickHouseByteValue$UnsignedByteValue.update(ClickHouseByteValue.java:16) at com.clickhouse.data.format.ClickHouseRowBinaryProcessor$TupleSerializer.serialize(ClickHouseRowBinaryProcessor.java:243) at com.clickhouse.data.ClickHouseDataProcessor.write(ClickHouseDataProcessor.java:593) at com.clickhouse.jdbc.internal.InputBasedPreparedStatement.addBatch(InputBasedPreparedStatement.java:345) at com.clickhouse.jdbc.internal.InputBasedPreparedStatement.executeAny(InputBasedPreparedStatement.java:113) at com.clickhouse.jdbc.internal.InputBasedPreparedStatement.executeLargeUpdate(InputBasedPreparedStatement.java:197) at com.clickhouse.jdbc.internal.AbstractPreparedStatement.executeUpdate(AbstractPreparedStatement.java:135) at org.example.Main.main(Main.java:37)

Configuration

Environment

ClickHouse server

zhicwu commented 1 year ago

Hi @BentsiLeviav, sorry for the inconvenience. It's a known limitation as reported in #1023. The workaround at this point is to use input function for insertion.

If majority of the users prefer to use String for insertion, I think we can enhance the driver to overcome this by adding a new option.

qusijun commented 4 months ago

Hi @BentsiLeviav, sorry for the inconvenience. It's a known limitation as reported in #1023. The workaround at this point is to use input function for insertion.

If majority of the users prefer to use String for insertion, I think we can enhance the driver to overcome this by adding a new option.

have any update about this issue ?

BentsiLeviav commented 4 months ago

Hi @qusijun

Unfortunately, this feature (object support) is considered experimental therefore we don't consider it as "production-ready". Anyway, could you try the solution provided by @zhicwu ?