Use clickhouse-jdbc save bitmap to CK error .

[standup-jb]

Is there have right demo to use clickhouse-jdbc save bitmap to ck server . I faced this problem maybe isn't a bug . Is there anybody can help me to solve this problems.

My Env

<dependency> <!-- will stop using ru.yandex.clickhouse starting from 0.4.0 --> <groupId>com.clickhouse</groupId> <artifactId>clickhouse-jdbc</artifactId> <version>0.3.2-patch6</version> <!-- below is only needed when all you want is a shaded jar --> </dependency>

CK server version is 21.6.5.37

Create table SQL

CREATE TABLE abTest.xabtest_dispatch_bitmap_v2 (xabtest_idString,dtString,create_atString,bitmap AggregateFunction(groupBitmap, UInt64) ) ENGINE = MergeTree PARTITION BY dt ORDER BY xabtest_id SETTINGS index_granularity = 64

Java Code

@test
public void test12() throws SQLException {
String url = "jdbc:ch://ip:port/abTest";
Properties properties = new Properties();
properties.setProperty("user", "username");
properties.setProperty("password","password");
ClickHouseDataSource dataSource = new ClickHouseDataSource(url, properties);
Roaring64NavigableMap bitmap = Roaring64NavigableMap.bitmapOf(1L);
Connection conn = dataSource.getConnection();
PreparedStatement statement = conn
.prepareStatement("insert into abTest.xabtest_dispatch_bitmap_v2 values (?,?,now(),?)");
statement.setString(1,"a");
statement.setString(2,"2022-01-01");
statement.setObject(3, ClickHouseBitmap.wrap(bitmap,ClickHouseDataType.UInt64));
System.out.println(statement.execute());
}

RUN the java code . The server repsonse the error message as below.

java.sql.BatchUpdateException: Code: 62, e.displayText() = DB::Exception: Cannot parse expression of type AggregateFunction(groupBitmap, UInt64) here: ru.yandex.clickhouse.util.ClickHouseBitmap$ClickHouseRoaring64NavigableMap@b00da3fb) (version 21.6.5.37 (official build))
, server ClickHouseNode(addr=http:sh-nh-b24-302-n15-clickhouse-89-249:8123, db=abTest)@-881958936

at com.clickhouse.jdbc.SqlExceptionUtils.batchUpdateError(SqlExceptionUtils.java:90)

[zhicwu]

Hi @standup-jb, please use new driver com.clickhouse.jdbc.ClickHouseDriver instead. ru.yandex.clickhouse package and legacy will be removed soon. Also you may want to check below example:

https://github.com/ClickHouse/clickhouse-jdbc/blob/3b0a353881349a4c3f7411c1ddfa62c4e7638dd7/clickhouse-jdbc/src/test/java/com/clickhouse/jdbc/ClickHousePreparedStatementTest.java#L573-L594

Update:
FYI, there's discrepancy between Java and C/C++ bitmap64 implementation, as I noticed a few different bytes generated in ClickHouse and Java lib(RoaringBitmap). I think you can use bitmap32 instead, and you may search issues in ClickHouse repo to learn more.

[standup-jb]

Hi @standup-jb, please use new driver com.clickhouse.jdbc.ClickHouseDriver instead. ru.yandex.clickhouse package and legacy will be removed soon. Also you may want to check below example:

https://github.com/ClickHouse/clickhouse-jdbc/blob/3b0a353881349a4c3f7411c1ddfa62c4e7638dd7/clickhouse-jdbc/src/test/java/com/clickhouse/jdbc/ClickHousePreparedStatementTest.java#L573-L594

Update: FYI, there's discrepancy between Java and C/C++ bitmap64 implementation, as I noticed a few different bytes generated in ClickHouse and Java lib(RoaringBitmap). I think you can use bitmap32 instead, and you may search issues in ClickHouse repo to learn more.

I run the provided unit test code , still has error .

@Test
public void testBatchInput() throws SQLException {
        Properties properties = new Properties();
        properties.setProperty("user", "default");
        properties.setProperty("password","password");
        properties.setProperty("continueBatchOnError", "true");
        ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource("jdbc:ch://ip:8123/abTest",properties);

        ClickHouseConnection conn = clickHouseDataSource.getConnection();
             Statement s = conn.createStatement();
             PreparedStatement stmt = conn.prepareStatement(
                     "insert into test_batch_input select id, name, value from input('id Int32, name Nullable" +
                             "(String), desc Nullable(String), value AggregateFunction(groupBitmap, UInt32)')");
    {
        s.execute("drop table if exists test_batch_input;"
                + "create table test_batch_input(id Int32, name Nullable(String), value AggregateFunction(groupBitmap, UInt32))engine=Memory");
        Object[][] objs = new Object[][]{
                new Object[]{1, "a", "aaaaa", ClickHouseBitmap.wrap(1, 2, 3, 4, 5)},
                new Object[]{2, "b", null, ClickHouseBitmap.wrap(6, 7, 8, 9, 10)},
                new Object[]{3, null, "33333", ClickHouseBitmap.wrap(11, 12, 13)}
        };
        for (Object[] v : objs) {
            stmt.setInt(1, (int) v[0]);
            stmt.setString(2, (String) v[1]);
            stmt.setString(3, (String) v[2]);
            stmt.setObject(4, v[3]);
            stmt.addBatch();
        }
        int[] results = stmt.executeBatch();

// Assert.assertEquals(results.length, objs.length);
for (int result : results) {
Assert.assertNotEquals(result, PreparedStatement.EXECUTE_FAILED);
}
}
}

Error Message

java.lang.NumberFormatException: For input string: "ru.yandex.clickhouse.util.ClickHouseBitmap$ClickHouseRoaringBitmap@776e4549"

at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at java.lang.Long.parseLong(Long.java:589)
at java.lang.Long.parseLong(Long.java:631)
at com.clickhouse.client.data.ClickHouseBitmapValue.update(ClickHouseBitmapValue.java:313)
at com.clickhouse.client.data.ClickHouseBitmapValue.update(ClickHouseBitmapValue.java:21)
at com.clickhouse.client.ClickHouseValue.updateUnknown(ClickHouseValue.java:1171)
at com.clickhouse.client.ClickHouseValue.update(ClickHouseValue.java:1259)
at com.clickhouse.client.data.ClickHouseObjectValue.update(ClickHouseObjectValue.java:144)
at com.clickhouse.client.data.ClickHouseBitmapValue.update(ClickHouseBitmapValue.java:347)
at com.clickhouse.client.data.ClickHouseBitmapValue.update(ClickHouseBitmapValue.java:21)
at com.clickhouse.jdbc.internal.InputBasedPreparedStatement.setObject(InputBasedPreparedStatement.java:270)
at com.ximalaya.xabtest.spark.wave.util.RoaringBitmapTest.testBatchInput(RoaringBitmapTest.java:339)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)

[zhicwu]

Judging from the exception you provided, it seems you're mixing new driver and legacy code. Can you use com.clickhouse.client.data.ClickHouseBitmap instead? If it's still not working, can you run the exact test I mentioned above without modification?

[standup-jb]

Judging from the exception you provided, it seems you're mixing new driver and legacy code. Can you use com.clickhouse.client.data.ClickHouseBitmap instead? If it's still not working, can you run the exact test I mentioned above without modification?

Yes, It is my mistakes. I can run the unit test code success .

[standup-jb]

@zhicwu Now, I can use clickhouse-jdbc write bitmap to ck . But i faced another issues .

I randomly added 100 long numbers to Roaring64NavigableMap in the unit test, and then executed and saved it to CK. I execute bitmapToArray in CK, the data displayed is completely different from the number I added to the bitmap

The UnitTest Code

`
@test
public void test12() throws SQLException {
Properties properties = new Properties();
properties.setProperty("continueBatchOnError", "true");
ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource("jdbc:ch://ip:8123/abTest",properties);
ClickHouseConnection conn = clickHouseDataSource.getConnection();
//Roaring64NavigableMap bitmap = Roaring64NavigableMap.bitmapOf(1L,2L,3L);
Roaring64NavigableMap bitmap = new Roaring64NavigableMap();
for (long i=0;i<100;i++){
long j = new Random().nextLong();
if (j < 0){
j = -j;
}
System.out.println(j);
bitmap.add(j);
}
System.out.println(ClickHouseBitmap.wrap(bitmap,ClickHouseDataType.UInt64).toBitmapBuildExpression());

        PreparedStatement statement = conn.prepareStatement("insert into abTest.xabtest_dispatch_bitmap_v2 " +
                "select xabtest_id,dt,create_at,bitmap from " +
                "input('\n" +
                "xabtest_id String, dt String, create_at DateTime , bitmap AggregateFunction" +
                "(groupBitmap, UInt64)')");

        statement.setString(1,"test");
        statement.setString(2,"2022-01-01");
        statement.setDate(3,new Date(System.currentTimeMillis()));
        statement.setObject(4, ClickHouseBitmap.wrap(bitmap, ClickHouseDataType.UInt64));
        statement.addBatch();
        int[] result = statement.executeBatch();
        System.out.println(Arrays.toString(result));

    }

`

The toBitmapBuildExpression

bitmapBuild([toUInt64(37852306073594854),toUInt64(50487767012236968),toUInt64(67121054165762014),toUInt64(158641504219275816),toUInt64(275529739814783380),toUInt64(477170895822268745),toUInt64(630597124499344791),toUInt64(664645423471903124),toUInt64(728307922834654008),toUInt64(742796080895439324),toUInt64(776967265927502958),toUInt64(780411853441085662),toUInt64(859012682104770587),toUInt64(889368784945466399),toUInt64(983058077526904235),toUInt64(995602108824063409),toUInt64(1133342365301333717),toUInt64(1224199168862023325),toUInt64(1261019664889066407),toUInt64(1277336945546069721),toUInt64(1373515431283840846),toUInt64(1396062733601184637),toUInt64(1444104568792809353),toUInt64(1451738265075233657),toUInt64(1592509560633644715),toUInt64(1601376145521041172),toUInt64(1618363407905406697),toUInt64(1786691935567497666),toUInt64(1854473816726758731),toUInt64(1865574589935929929),toUInt64(2000147089124292054),toUInt64(2092375434923448705),toUInt64(2184621697273986253),toUInt64(2286760828969954756),toUInt64(2368206991093390170),toUInt64(2380336374488442809),toUInt64(2428331976023502528),toUInt64(2461014247308247470),toUInt64(2489916983983358849),toUInt64(2498990892688499921),toUInt64(2632266542860053224),toUInt64(2725963412084085771),toUInt64(2940738974324192978),toUInt64(3036287673991779440),toUInt64(3113309032238281652),toUInt64(3142961738739067508),toUInt64(3145121281659704731),toUInt64(3349505495262532358),toUInt64(3376734772256793577),toUInt64(3488733080417168449),toUInt64(3636407674707571978),toUInt64(3764042832378206130),toUInt64(3884671516986834229),toUInt64(4031284603698239852),toUInt64(4075726569275723281),toUInt64(4287888052686414973),toUInt64(4317959405723256674),toUInt64(4360665396882053410),toUInt64(4585142006764820165),toUInt64(4893337290587204613),toUInt64(4930952552257452341),toUInt64(4978637704978013656),toUInt64(5069597665172359748),toUInt64(5095944763191648879),toUInt64(5301261776796983519),toUInt64(5343769722964309188),toUInt64(5504111170144647708),toUInt64(5528804685107320129),toUInt64(5597494409716886180),toUInt64(5673467996069569735),toUInt64(5958014638998097606),toUInt64(5970426836633858606),toUInt64(6106010472891390315),toUInt64(6143151737103665168),toUInt64(6155700352392732730),toUInt64(6212269300715679002),toUInt64(6683736753112378470),toUInt64(6742550058163033966),toUInt64(6917485248705498186),toUInt64(6917710482973767658),toUInt64(6920940900020171258),toUInt64(7100618775224984486),toUInt64(7304515515890699637),toUInt64(7418677705133140802),toUInt64(7465699073140350004),toUInt64(7695337155274557251),toUInt64(7767156180355779319),toUInt64(7842148296402797957),toUInt64(7867933219009366490),toUInt64(7943944635154957711),toUInt64(8173802053559042851),toUInt64(8592945744844733861),toUInt64(8619745023837955488),toUInt64(8761680327009964904),toUInt64(8803419100580104678),toUInt64(8898041684467501451),toUInt64(8925302387779521826),toUInt64(9027873309913320154),toUInt64(9121703870807240426),toUInt64(9190734381259993274)])

the buildToArray in CK

123021143114307406,196778591117858475,246068876234255642,257052849505179570,506422387931564518,622904537331906538,867961263340104308,1165221887141899968,1256773015590546242,1275139007521747892,1383313429283871010,1449891213391737338,1528334692934624455,1869361127848358364,2047655797818844491,2055510659718827435,2178397614394892052,2645764911830472091,2843893846553732006,2963624971589647669,3015826688983338357,3055623474106887270,3236043403636611435,3375728708162194506,3383260067494688214,3387755927724721881,3636686430805787448,3651389109710015519,3709255509295040354,3808606133810121289,4022939625768061508,4054205330343360821,4110815039722139834,4254737796562636066,4501046562419376094,4645579772815699009,4675343662040118468,4799752073596285817,5157136307184194571,5689890356507329409,5955737970841692089,6046316062130439557,6242244462217489413,6326878521411333866,6583896519089610408,6679578492217087685,6797281398014166851,6889391551326034305,7102981654474613737,7240603390413507319,7368063841963616585,7730190313427026350,7961413574873408928,8256457156943687902,8393876362601561993,8620876203076871293,8681398569647877094,8768657065080105704,8922733722154373147,9028372592026829204,9213680158263156959,9283461646042764038,9388789495050594980,9447838652029924618,9555177331027179218,10164381549243225200,10185332964315420526,10272497919601411471,10467133321561658689,10608188593652707886,10629392792843749850,11212611783260873256,11265692940845390958,11361481546655899096,11548989177096617381,12122279594100879005,12164045657391602557,12190522298469368611,12973322488111870887,13154976166261021530,13322649595441647121,13714797907489230231,13771637478543789766,13870413296311360564,14082395696645595759,14137217575822412171,15473690440778453864,15483606432090638740,15498805024931614129,15680068358169840066,15795456637395283396,15804627660553878733,15812258504344707130,16172630837163974357,17398049114165152474,17407667201031356137,17476044959684180508,17715824293065213968,18028663719497721041,18302612629315606892

[zhicwu]

Yes, like I mentioned earlier:

FYI, there's discrepancy between Java and C/C++ bitmap64 implementation, as I noticed a few different bytes generated in ClickHouse and Java lib(RoaringBitmap). I think you can use bitmap32 instead, and you may search issues in ClickHouse repo to learn more.

[standup-jb]

Yes, like I mentioned earlier:

FYI, there's discrepancy between Java and C/C++ bitmap64 implementation, as I noticed a few different bytes generated in ClickHouse and Java lib(RoaringBitmap). I think you can use bitmap32 instead, and you may search issues in ClickHouse repo to learn more.

I must use the long type (64bit) . So the bitmap32 is not useable for me . Is there have any solutions？

[standup-jb]

Yes, like I mentioned earlier:

FYI, there's discrepancy between Java and C/C++ bitmap64 implementation, as I noticed a few different bytes generated in ClickHouse and Java lib(RoaringBitmap). I think you can use bitmap32 instead, and you may search issues in ClickHouse repo to learn more.

I must use the long type (64bit) . So the bitmap32 is not useable for me . Is there have any solutions？

[zhicwu]

Sorry @standup-jb, I have very limited knowledge regarding bitmap so it's going to take a while for me to figure this out, not to mention that my focus is primarily on v0.3.3.

Do you want to take it over by submitting pull request for full bitmap64 support?

[ywill3]

Yes, like I mentioned earlier:

FYI, there's discrepancy between Java and C/C++ bitmap64 implementation, as I noticed a few different bytes generated in ClickHouse and Java lib(RoaringBitmap). I think you can use bitmap32 instead, and you may search issues in ClickHouse repo to learn more.

I must use the long type (64bit) . So the bitmap32 is not useable for me . Is there have any solutions？

Hello，Do you find another solutions? I used insert into xxx(a,b,c) values(?,?,bitmapBuild(?)) for prepareStatement. But it doesn't work ,The bitmap column is Null.

[ywill3]

Yes, like I mentioned earlier:

FYI, there's discrepancy between Java and C/C++ bitmap64 implementation, as I noticed a few different bytes generated in ClickHouse and Java lib(RoaringBitmap). I think you can use bitmap32 instead, and you may search issues in ClickHouse repo to learn more.

I must use the long type (64bit) . So the bitmap32 is not useable for me . Is there have any solutions？

Hello，Do you find another solutions? I used insert into xxx(a,b,c) values(?,?,bitmapBuild(?)) for prepareStatement. But it doesn't work ,The bitmap column is Null.

@standup-jb @zhicwu

[zhicwu]

Do you find another solutions?

Hi @ywill3, I didn't spend time for this one so no progress from my side.

I used insert into xxx(a,b,c) values(?,?,bitmapBuild(?)) for prepareStatement. But it doesn't work ,The bitmap column is Null.

ClickHouse does not support prepared statement. Your query will be converted to a large SQL statement which will be sent to server in string format. In order to leverage binary streaming, you should use input function or external table as mentioned in batch insert example:

-- JDBC driver will figure out parameters by parsing input function
insert into xxx select a, b, bitmapBuild(c) from input('a UUID, b DateTime64(3), c Array(UInt64)')

[ywill3]

Do you find another solutions?

Hi @ywill3, I didn't spend time for this one so no progress from my side.

I used insert into xxx(a,b,c) values(?,?,bitmapBuild(?)) for prepareStatement. But it doesn't work ,The bitmap column is Null.

ClickHouse does not support prepared statement. Your query will be converted to a large SQL statement which will be sent to server in string format. In order to leverage binary streaming, you should use input function or external table as mentioned in batch insert example:

-- JDBC driver will figure out parameters by parsing input function
insert into xxx select a, b, bitmapBuild(c) from input('a UUID, b DateTime64(3), c Array(UInt64)')

OK，Thanks