"ClickHouse response without column types" when using CSV format

[zhicwu]

Carlos N reported below issue on Telegram:

Carlos N, [28.01.21 05:44]
Hello, I am seeing the following exception when I use the CSV format for the response of my queries:

Caused by: java.lang.IllegalArgumentException: ClickHouse response without column types
 at ru.yandex.clickhouse.response.ClickHouseResultSet.<init>(ClickHouseResultSet.java:111)
 at ru.yandex.clickhouse.ClickHouseStatementImpl.createResultSet(ClickHouseStatementImpl.java:899)
 at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:122)

Carlos N, [28.01.21 05:44]
Has anybody found the issue before? It only happens with some of the most complex queries. I run.

Carlos N, [28.01.21 05:45]
I am using the default jdbc driver

Carlos N, [28.01.21 05:44]
Hello, I am seeing the following exception when I use the CSV format for the response of my queries:

Caused by: java.lang.IllegalArgumentException: ClickHouse response without column types
 at ru.yandex.clickhouse.response.ClickHouseResultSet.<init>(ClickHouseResultSet.java:111)
 at ru.yandex.clickhouse.ClickHouseStatementImpl.createResultSet(ClickHouseStatementImpl.java:899)
 at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:122)

Carlos N, [28.01.21 05:44]
Has anybody found the issue before? It only happens with some of the most complex queries. I run.

Carlos N, [28.01.21 05:45]
I am using the default jdbc driver

Carlos N, [28.01.21 08:58]
[In reply to Zhichun Wu]
Yes, we are using FORMAT because we wanted CSVWithNames, the issue doesn't occur without the FORMAT parameter. Is there a way for it to work with that parameter? This is the full query. 
select
    ...
from (
    ...
) as act
join ...
order by ...
 limit ? offset ? 
FORMAT CSVWithNames

Denny Crane [Junior support specialist at Altinity (NB,Canada)], [28.01.21 08:59]
The fact that following is working in JDBC

select 1 a, 'aaa' b
from numbers(2) 
FORMAT CSVWithNames

Is a bug and must be fixed. To avoid confusion.

[enqueue]

What‘s the purpose of specifying a custom format when working with the JDBC driver? The driver exposes the response via ResultSet, no matter what format was given. I agree that current behavior is broken, though.

[sneakythr0ws]

@zhicwu Hi! Is it possible to download csv file of "select ... FORMAT CSV" via clickhouse-jdbc?

[zhicwu]

@zhicwu Hi! Is it possible to download csv file of "select ... FORMAT CSV" via clickhouse-jdbc?

I think it's doable using extended API. I'll give it a shot tonight.

Update: it's not as I only saw writer API :/ I'll commit a tailored version of the grpc client over the weekend. It should work as you can get InputStream from response and do whatever you want like writing results into a CSV file. For example:

ClickHouseNode server = ClickHouseNode.builder().withPort(ClickHouseProtocol.GRPC).build(); // localhost:9100
ClickHouseClient client = ClickHouseClient.builder().build(); // only gRPC is supported at this point
try (ClickHouseResponse response = client.connect(server).sql("select * from FORMAT CSV").execute().get()) {
    try (InputStream in = response.getInputStream()) {
        ...
    }
}

[sneakythr0ws]

Hi @zhicwu! Do you have any progress on this?

[zhicwu]

Hi @zhicwu! Do you have any progress on this?

Thanks for your patience and sorry for no update for months. Fortunately, progress being made, stability and performance are improved, along with a few enhancements to the API, so now this can be simplified as following:

ClickHouseClient.dump(
  ClickHouseNode.builder()
    .withHost("my.ch.server")
    .withPort(ClickHouseProtocol.GRPC, 9100)
    .withDatabase("my_db")
    .build(),
  "select * from my_table",
  ClickHouseFormat.CSV,
  ClickHouseCompression.NONE,
  "/tmp/mytable.csv");

Note: compression is currently not supported very well, see ClickHouse/ClickHouse#28671 for details.

Now I'm still adding features and testing the client. If your work is somehow blocked by this, I can upload a version this weekend for you to play with. However, please be aware that the client is experimental, and you may have to upgrade ClickHouse server to a version containing changes from ClickHouse/ClickHouse#27064.