Write Roaring Bitmap data from Spark（ck-jdbc） to CH

[zhou-binbin]

The more information of the question as follows:

the CK table :

CREATE TABLE app_userid_bit_ch
(
appname String,
uidbit AggregateFunction(groupBitmap, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY appname
SETTINGS index_granularity = 128

the Spark main program statements：

` val prop=new Properties()
val ckDriver="ru.yandex.clickhouse.ClickHouseDriver"
prop.put("driver",ckDriver)
prop.put("user",user)
prop.put("password",password)
import spark.implicits._
val ckdata = sc.textFile(args(0)).map(x=>{
val y = x.split(",")
val appname = y(0)
val uidbitmap = new RoaringBitmap()
y(1).split(":").foreach(k=>uidbitmap.add(k.toInt))
(appname,uidbitmap)
}).toDF()

ckdata.write.mode(saveMode="append")
.option("batchsize", "200")
.option("isolationLevel", "NONE")
.option("numPartitions", "1")
.jdbc(url, table,prop)`

the CH version is 20.8.3.18

I want to write Roaring Bitmap data into CK by spark,use java org.roaringbitmap.RoaringBitmap but failed. have CK support it ,if not ,have future plan?At present, we can only import the original ID data into CK,and then build RBM structure by creating materialized view. But the amount of original ID data is often very large.

[filimonov]

Please check ClickHouse/ClickHouse#16798

[filimonov]

Duplicate of #422

[lzzuo2]

How read Roaring Bitmap data with Spark?

[zhou-binbin]

How read Roaring Bitmap data with Spark?

Is it write bitmap data to ck by spark?

[lzzuo2]

How read Roaring Bitmap data with Spark?

Is it write bitmap data to ck by spark?

是的 我想通过spark直接读Roaring Bitmap 但是我现在只能通过jdbc的方式去读取, 例如 arrayJoin(bitmapToArray(userid_bm)) 这种方式去展开 如果bitmap里面的数据量很大 感觉读取比较慢 有没有通过spark直接读取的方式?

[zhou-binbin]

How read Roaring Bitmap data with Spark?

Is it write bitmap data to ck by spark?

是的 我想通过spark直接读Roaring Bitmap 但是我现在只能通过jdbc的方式去读取, 例如 arrayJoin(bitmapToArray(userid_bm)) 这种方式去展开 如果bitmap里面的数据量很大 感觉读取比较慢 有没有通过spark直接读取的方式?

这个我还没有尝试过，我只是想怎么写入bitmap数据到ck通过spark

[lzzuo2]

How read Roaring Bitmap data with Spark?

Is it write bitmap data to ck by spark?

是的 我想通过spark直接读Roaring Bitmap 但是我现在只能通过jdbc的方式去读取, 例如 arrayJoin(bitmapToArray(userid_bm)) 这种方式去展开 如果bitmap里面的数据量很大 感觉读取比较慢 有没有通过spark直接读取的方式?

这个我还没有尝试过，我只是想怎么写入bitmap数据到ck通过spark

我们这边先把明显数据从hive同步到ck，然后再通过jdbc的方式转成bitmap 再写到ck的bitmap表

[zhou-binbin]

How read Roaring Bitmap data with Spark?

Is it write bitmap data to ck by spark?

是的 我想通过spark直接读Roaring Bitmap 但是我现在只能通过jdbc的方式去读取, 例如 arrayJoin(bitmapToArray(userid_bm)) 这种方式去展开 如果bitmap里面的数据量很大 感觉读取比较慢 有没有通过spark直接读取的方式?

这个我还没有尝试过，我只是想怎么写入bitmap数据到ck通过spark

我们这边先把明显数据从hive同步到ck，然后再通过jdbc的方式转成bitmap 再写到ck的bitmap表

这个我了解，只不过要写入大量原始数据，我想直接通过spark聚合成bitmap数据直接写进去，避免写入大量原始数据

[zhicwu]

@zhou-binbin & @lzzuo2, 目前JDBC驱动使用TabSeparatedWithNamesAndTypes格式。由于目前实现依赖Java字符串编码导致写入二进制数据存在问题。写入序列化的Bitmap建议在0.3.0版本中使用Extended API（示例如下）。0.3.1版本开始会改用RowBinaryWithNamesAndTypes解决此类问题同时会减少内存占用提高性能。

// use JDBC interface - NOT recommended before 0.3.1
try (PreparedStatement statement = connection.prepareStatement("insert into my_bitmap_table values(..., ?, ...)")) {
    ...
    // RoaringBitmap bitmap = RoaringBitmap.bitmapOf(1,2,3,...);
    s.setObject(index++, ClickHouseBitmap.wrap(bitmap, ClickHouseDataType.UInt32));
    ...
    // the actual SQL in 0.3.0 will be something like, which is also why batch insertion does not work...
    // insert into my_bitmap_table values(..., bitmapBuild([toUInt32(1),toUInt32(3),toUInt32(3),...]) ...)
    s.execute();
}

// use extended API - recommended in 0.3.0
try (ClickHouseStatement statement = connection.createStatement()) {
    statement.sendRowBinaryStream("insert into my_bitmap_table", new ClickHouseStreamCallback() {
        public void writeTo(ClickHouseRowBinaryStream stream) throws IOException {
            ...
            // RoaringBitmap bitmap = RoaringBitmap.bitmapOf(1,2,3,...);
            // In addition to RoaringBitmap, you can pass:
            // ImmutableRoaringBitmap, MutableRoaringBitmap and even Roaring64NavigableMap
            stream.writeBitmap(ClickHouseBitmap.wrap(bitmap, ClickHouseDataType.UInt32));
            ...
        }
    });
}

[Translation]
JDBC driver uses TabSeparatedWithNamesAndTypes. It has issue when writing binary data, since current implementation relies on Java String encoding. To write serialized bitmap into ClickHouse, you'd better use extended API in 0.3.0 as shown above. Starting from 0.3.1 release, RowBinaryWithNamesAndTypes will be used instead to address the limitation along with improvement on both memory usage and performance.