Large INSERT INTO SELECT read timeout

[sneakythr0ws]

Hi! How to run large (6B rows) INSERT INTO SELECT query via clickhouse-jdbc? It runs well but never ends and throws read timeout error. Clickhouse process finishes correctly with finish result in query_log. But after that, the clickhouse-jdbc waits for the timeout to end. And throws read timeout

But if I put LIMIT 100 executeUpdate ends correctly

I tried 0.3.2-patch9 and 0.3.1-patch versions

final ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(chUrl);;
clickHouseDataSource.getProperties().setUser(chUser);
clickHouseDataSource.getProperties().setPassword(chPassword);
clickHouseDataSource.getProperties().setConnectionTimeout(10000000);
clickHouseDataSource.getProperties().setSocketTimeout(10000000);
clickHouseDataSource.getProperties().setTimeToLiveMillis(10000000);
clickHouseDataSource.getProperties().setSessionTimeout(10000000L);
clickHouseDataSource.getProperties().setDataTransferTimeout(10000000);
clickHouseDataSource.getProperties().setMaxExecutionTime(10000000);

try (ClickHouseStatement statement = clickHouseDataSource.getConnection().createStatement()) {
    log.info("START update query");
    statement.executeUpdate("INSERT INTO SELECT"...);
    log.info("FINISH update query");
} catch (Exception e) {
   ...
}

[zhicwu]

Hi @sneakythr0ws, can you reproduce the issue using new driver? I tried below on DBeaver using new driver and it worked fine.

create table null_table(number UInt64)engine=Null;

-- query_duration_ms: 1,081,404
-- read_rows: 1,000,000,512,135
-- written_rows: 1,000,000,000,000
-- connect_timeout: 5000
-- socket_timeout: 3,600,000
-- session_timeout: <same as server default>
set max_execution_time=60000;
insert into null_table select number from numbers(1000000000000)

[sneakythr0ws]

can you reproduce the issue using new driver?

what do you mean new driver? java client or patch10?

I tried below on DBeaver using new driver and it worked fine.

in my case every row contains ~10 strings

[zhicwu]

what do you mean new driver? java client or patch10?

I meant com.clickhouse.jdbc.ClickHouseDriver.

in my case every row contains ~10 strings

How long it took for the query? I'll add some tests to see if I can reproduce the issue.

[sneakythr0ws]

How long it took for the query? I'll add some tests to see if I can reproduce the issue.

~1.5 hour

[sneakythr0ws]

I meant com.clickhouse.jdbc.ClickHouseDriver.

the same

[zhicwu]

That's a bit too long but let me give it a shot. @sneakythr0ws, are you using a proxy between the client and server by any chance?

[sneakythr0ws]

That's a bit too long but let me give it a shot. @sneakythr0ws, are you using a proxy between the client and server by any chance?

no. I use it without proxy