Does clickhouse-jdbc surpport update data in clickhouse?

[qinf]

Dose clickhouse-jdbc surpport update data like this?

"alter table table_name update filed1='far' where key='boo';"

[zhicwu]

Yes, as long as the query works on your ClickHouse server. However, please keep in mind that update/delete in ClickHouse happens in an asynchronous way. You may have to check system.mutations(is_done=1?) periodically if you need to understand if the mutation is a success or not.

[zhicwu]

It's interesting that I didn't find any unit test for update and delete, which should be added.

[qinf]

I found this code in ru.yandex.clickhouse.ClickHousePreparedStatementImpl.ava, it will throw new SQLSyntaxErrorException if the sql not like 'INSERT INTO ...', so i com here to ask if che client can supprot update or delete.

@Override
    public int[] executeBatch(Map<ClickHouseQueryParam, String> additionalDBParams) throws SQLException {
        Matcher matcher = VALUES.matcher(sql);
        if (!matcher.find()) {
            throw new SQLSyntaxErrorException(
                    "Query must be like 'INSERT INTO [db.]table [(c1, c2, c3)] VALUES (?, ?, ?)'. " +
                            "Got: " + sql
            );
        }
        int valuePosition = matcher.start();
        String insertSql = sql.substring(0, valuePosition);
        BatchHttpEntity entity = new BatchHttpEntity(batchRows);
        sendStream(entity, insertSql, additionalDBParams);
        int[] result = new int[batchRows.size()];
        Arrays.fill(result, 1);
        batchRows = new ArrayList<byte[]>();
        return result;
    }

[qinf]

It's interesting that I didn't find any unit test for update and delete, which should be added.

Yes, I didn't find yan unit test for update or delete, so i don't know if it has the function.

[enqueue]

@qinf please use the regular statements for this. The "Batch" operations are optimized for loading data sets into ClickHouse, that's why the driver currently imposes such a limitation.

For example:

connection.createStatement().execute(
    "ALTER TABLE table_name UPDATE filed1='far' WHERE key='boo');

or

PreparedStatement stmt = connection.prepareStatement(
    "ALTER TABLE table_name UPDATE field1 = 'far' WHERE key = ?");
stmt.setString(1, "boo");
stmt.executeUpdate();

[qinf]

@qinf please use the regular statements for this. The "Batch" operations are optimized for loading data sets into ClickHouse, that's why the driver currently imposes such a limitation.

For example:

connection.createStatement().execute(
    "ALTER TABLE table_name UPDATE filed1='far' WHERE key='boo');

or

PreparedStatement stmt = connection.prepareStatement(
    "ALTER TABLE table_name UPDATE field1 = 'far' WHERE key = ?");
stmt.setString(1, "boo");
stmt.executeUpdate();

@enqueue Thank you for your replay, I will try it.

[zhicwu]

@den-crane / @filimonov, any comment for https://github.com/ClickHouse/clickhouse-jdbc/blob/release_0.2.4/src/main/java/ru/yandex/clickhouse/ClickHousePreparedStatementImpl.java#L326? Any concern to remove the restriction to support other statements(query/mutation etc.) in addition to insertion?

[den-crane]

We should not encourage users to use mutations. On the contrary we should warn users against using them.
Mutations should not be used on a daily basis.

I run mutations only from Clickhouse-client.

[enqueue]

I took a look at this issue today. and I do not see a straightforward way of supporting ClickHouse mutations in JDBC batch mode.

There is currently no officially recommended way to pass multiple statements in one HTTP call.

Option 1: We could try to analyze the query and do some tricks for trivial statements E.g. if the whole statement looks like this ALTER TABLE t UPDATE bar = 'constant' where foo = ? -> rewrite to use IN (?, ?, ..) Error-prone, hard to maintain magic for only some limited cases. 🙅

Option 2: We could collect the statements and, once the client calls executeBatch, kick off one HTTP call per statement, perhaps re-using the same session ID. This is doable, but would mean quite some work for a special use case. I was thinking about re-using such a mechanism for the more general autoCommit setting, but I don't see a very high merit in this either.

What's your opinion, @zhicwu ?

[zhicwu]

Thank you @enqueue as always for the detailed inputs! I agree that both options are not ideal but I'm leaning towards to the second. Some more thoughts on option 2:

• Regarding session, I think we only need it when there's set statement and/or temporary table was involved.
• Synchronicity - we can reuse connection setting async and append setting mutations_sync accordingly to alter statement(example) as needed
• We probably should also show a warning when one of the statements is for update/deletion, because it's heavy operation and is not encouraged to use
• Optimizations
  • consolidate set statements (needs to test if there's any performance improvement but it helps to avoid session and reduce network calls)
  • in a clustered environment, I'm not sure if we should always reuse the same http client for all statements - maybe some of them can be executed in parallel, and this applies to insertion too

You're right that this cannot be tackled shortly. I guess we can enhance SQL parser for the first two in 0.3.1 and consider optimizations in 0.4.0 with new client interface.