Does client-v2 query API support bulk insert for prepared statement?

[abcfy2]

Describe your feedback

For example:

client.query("INSERT INTO mytable(c1, c2, c3) VALUES ({v1:String}, {v2:String}, {v3:String})");

I want to insert multiple rows.

Does this API support bulk insert ? How to?

I don't want to manually splice prepared statements, that's too much trouble. (Like VALUES ({v1:String}, {v2:String}, {v3:String}), ({v1_2:String}, {v2_2:String}, {v3_2:String}),...,())

I know JDBC driver often has a executeBatch() function to do so.

But how to do this in client-v2?

Thanks.

[abcfy2]

I find another way to do so:

INSERT INTO mytable (c1, c2, c3)
SELECT *
FROM arrayJoin({myList:Array(Map(String, String))})

Is there any better way ?

Because this syntax (insert into select) will cause async_insert=1 no effect.

[chernser]

Good day, @abcfy2!

JDBC supports such inserts because it doesn't have a good way to send big data.
Java Client has ability to send data from stream or collection of data objects. This is more efficient way because allows to binary encode data and then effectively compress it.
If you have compiles list of values as strings list you may look into Values format and send it as string https://clickhouse.com/docs/en/interfaces/formats#data-format-values
We are about to release another way of inserting data - thru writers, it will give even more flexibility.

As for JDBC - if there is no special requirement, I would recommend using java client for better performance and feature support.

[abcfy2]

Thanks @chernser . Can't wait for this feature. How everything goes well.

But currently is there any way for me to insert bulk insert ? I try to build a CSV data to use Client#insert(String tableName, List<?> data, InsertSettings settings) API, but no use.

List<String> csvRows = new ArrayList<>();
csvRows.add("1,2,3");
csvRows.add("4,5,6");
InsertSettings settings = new InsertSettings();
settings.setOption(ClickHouseClientOption.FORMAT.getKey(), ClickHouseFormat.CSV.name());

client.insert("mytable", csvRows, settings);

java.lang.NullPointerException: Cannot invoke "java.lang.Boolean.booleanValue()" because the return value of "java.util.Map.get(java.lang.Object)" is null
	at com.clickhouse.client.api.Client.insert(Client.java:1227)

Maybe I have to build a POJO ? I find this API only support POJO.

But I can't use Client#insert(String tableName, InputStream data, ClickHouseFormat format) API because the data is not from file or http response or others InputStream data. Maybe support Stream<T> is better.

Thanks.

[chernser]

Good day, @abcfy2 !
Sorry for the delayed answer!

Here is new writer API in 0.8.0 where you can do something like

        try (InsertResponse response = client.insert(tableName, out -> {
            for (String row : csvData) {
                out.write(row.getBytes));
                out.write('\n');
            }
        }, ClickHouseFormat.CSV, insertSettings).get()) {
            System.out.println("Rows written: " + response.getWrittenRows());
        }

Also look into com.clickhouse.client.insert.InsertTests#testAppCompression - it is most close to your case.

[abcfy2]

Thanks.