How to batch insert when insert bitmap with extend api

[MaxWk]

in other words，how to batch insert with extend api

[wntp]

如果批量提交的话，数据库后台就报 void DB::ParalleParsingBlockInputStream::onBackgroundException(): Code 33, e.displayText() = DB::Exception Cannot read all data.Bytes expected: 105. Stack trace....
完全参考 #532 的写法，只是excute 换成了batchexcute。

[zhicwu]

Sorry for the late response. Yes, batch processing for Bitmap is currently not supported because of the text-based format the drivers uses to communicate with server. On the other hand, extend API should be enhanced for batch processing - writing data into a local file, and then load it into database using write API could be a workaround but it's very inconvenient.

[wntp]

Thanks for your answer and look forward to ClickHouse-JDBC becoming more and more powerful。#661 (comment)

[findczk]

+1 . hope support batch writing bitmap quickly. Thanks!

[findczk]

Hi! When will it support batch insert bitmap?

[zhicwu]

Hi! When will it support batch insert bitmap?

Have you tried extended API? It should work. If you're somehow stuck with jdbc driver and don't mind to test an in-development version, I can upload one for testing purpose.

[findczk]

Does the extended API support batch insert bitmap? When I do (statement.execute) it succeeds, and when I do (statement.executeBatch) it fails.

Does the extended API support batch insert bitmap? When I do (statement.execute) it succeeds, and when I do (statement.executeBatch) it fails.

[findczk]

Hi! When will it support batch insert bitmap?

Have you tried extended API? It should work. If you're somehow stuck with jdbc driver and don't mind to test an in-development version, I can upload one for testing purpose.

I just need to batch insert bitmaps, either using the extension API or JDBC Driver.

[yanye666]

If my sql is
："Select *from table_a where bitmapContains (#{bitmap}) and 1=1", can this be achieved using the extended API? , I mean I want to pass the bitmap to CH for query results. I think the extended API demo is an insert type sql. How should I write this type of query?

[zhicwu]

Below are some examples and I'll update and add more when I have time.

• Batch insert using input() function - available since 0.3.2-test1

  Sample code using JDBC API...

  try (ClickHouseConnection conn = newConnection(new Properties());
          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();
      }
      stmt.executeBatch();
  
      try (ResultSet rs = s.executeQuery("select * from test_batch_input")) {
          Object[][] values = new Object[objs.length][];
          int index = 0;
          while (rs.next()) {
              values[index++] = new Object[] {
                      rs.getObject(1), rs.getObject(2), rs.getObject(3)
              };
          }
          Assert.assertEquals(index, objs.length);
          for (int i = 0; i < objs.length; i++) {
              Object[] actual = values[i];
              Object[] expected = objs[i];
              Assert.assertEquals(actual[0], expected[0]);
              Assert.assertEquals(actual[1], expected[1]);
              Assert.assertEquals(actual[2], expected[3]);
          }
      }
  }

• Query with bitmap as parameter using temporary table - available since 0.3.2-test2

  Sample code...

  try (ClickHouseConnection conn = newConnection(new Properties());
          PreparedStatement stmt = conn.prepareStatement(
                  "SELECT bitmapContains(my_bitmap, toUInt32(1)) as v1, bitmapContains(my_bitmap, toUInt32(2)) as v2 from {tt 'ext_table'}")) {
      stmt.setObject(1, ClickHouseExternalTable.builder().name("ext_table")
              .columns("my_bitmap AggregateFunction(groupBitmap,UInt32)")
              .format(ClickHouseFormat.RowBinary) // optional as it's same as default
              .content(new ByteArrayInputStream(ClickHouseBitmap.wrap(1, 3, 5).toBytes()))
              .asTempTable() // necessary because of the parsing error
              .build());
      ResultSet rs = stmt.executeQuery();
      Assert.assertTrue(rs.next());
      Assert.assertEquals(rs.getInt(1), 1);
      Assert.assertEquals(rs.getInt(2), 0);
      Assert.assertFalse(rs.next());
  }

  Note: external table does not work at this point due to server-side parsing issue, see AggregateFunction column in external table didn't work ClickHouse#32171

[yanye666]

Below are some examples and I'll update and add more when I have time.

• Batch insert using input() function

  Sample code using JDBC API...

  try (ClickHouseConnection conn = newConnection(new Properties());
          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();
      }
      stmt.executeBatch();
  
      try (ResultSet rs = s.executeQuery("select * from test_batch_input")) {
          Object[][] values = new Object[objs.length][];
          int index = 0;
          while (rs.next()) {
              values[index++] = new Object[] {
                      rs.getObject(1), rs.getObject(2), rs.getObject(3)
              };
          }
          Assert.assertEquals(index, objs.length);
          for (int i = 0; i < objs.length; i++) {
              Object[] actual = values[i];
              Object[] expected = objs[i];
              Assert.assertEquals(actual[0], expected[0]);
              Assert.assertEquals(actual[1], expected[1]);
              Assert.assertEquals(actual[2], expected[3]);
          }
      }
  }

• Query with bitmap as parameter using external table - below code does not work at this point due to server-side parsing issue, see AggregateFunction column in external table didn't work ClickHouse#32171

  Sample code...

  try (ClickHouseConnection conn = newConnection(new Properties());
          PreparedStatement stmt = conn.prepareStatement(
                  "SELECT bitmapContains(my_bitmap, toUInt32(1)) as v1, bitmapContains(my_bitmap, toUInt32(2)) as v2 from ext_table")) {
      // below will be replaced by JDBC escape syntax later
      ClickHouseRequest<?> request = stmt.unwrap(ClickHouseRequest.class);
      request.addExternal(ClickHouseExternalTable.builder().name("ext_table")
              // this will end up with a parsing issue on server - it will work once the issue get fixed
              .columns("my_bitmap AggregateFunction(groupBitmap,UInt32)").format(ClickHouseFormat.RowBinary)
              .content(new ByteArrayInputStream(ClickHouseBitmap.wrap(1, 3, 5).toBytes())).build());
      ResultSet rs = stmt.executeQuery();
      Assert.assertTrue(rs.next());
      Assert.assertEquals(rs.getInt(1), 1);
      Assert.assertEquals(rs.getInt(2), 0);
      Assert.assertFalse(rs.next());
  }

That is currently unavailable.

[zhicwu]

0.3.2-test1 is under public testing - see #768 . Will push 0.3.2-test2 over the weekend for more features and improved performance.

[zhicwu]

Also documented examples at here