ClickHouse JDBC executeQuery with

[autumneve0108]

There are some mistakes with SELECT SYNTAX "FORMAT JSON" in the tail through jdbc driver. clickhouse-client works normally. such as: SELECT '1' as col_1, toDate('2018-05-01') as col_2, 3.3 as col_3, 4.5 as col_4 FORMAT JSON

issue code snippet: String sourceAppAddr = "127.0.0.1:8123"; String jdbcConfig = "jdbc:clickhouse://"+sourceAppAddr; ClickHouseProperties properties = new ClickHouseProperties(); properties.setMaxExecutionTime(1); properties.setUser("default"); properties.setPassword(""); ClickHouseDataSource dataSource = new ClickHouseDataSource(jdbcConfig+"/default", properties); Connection conn = dataSource.getConnection();

Statement stmt = conn.createStatement(); String sql = "SELECT '1' as col_1, toDate('2018-05-01') as col_2, 3.3 as col_3, 4.5 as col_4 FORMAT JSON ";// ResultSet rs = stmt.executeQuery(sql); while(rs.next()) { System.out.println("row index:"+(rs.getRow()-1)+"col_1:"+rs.getString("col_1")+ "col_2:"+rs.getDate("col_2")+ "col_3:"+rs.getDouble("col_3")+ "col_4:"+rs.getDouble("col_4")); }

error log: SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder". SLF4J: Defaulting to no-operation (NOP) logger implementation SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details. Exception in thread "main" ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 62, host: 192.168.1.61, port: 8123; Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 87: JSON FORMAT TabSeparatedWithNamesAndTypes;. Expected identifier, e.what() = DB::Exception

at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:58)
at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:28)
at ru.yandex.clickhouse.ClickHouseStatementImpl.checkForErrorAndThrow(ClickHouseStatementImpl.java:680)
at ru.yandex.clickhouse.ClickHouseStatementImpl.getInputStream(ClickHouseStatementImpl.java:512)
at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:105)
at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:89)
at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:85)
at TestFormatJSON.main(TestFormatJSON.java:24)

Caused by: java.lang.Throwable: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 87: JSON FORMAT TabSeparatedWithNamesAndTypes;. Expected identifier, e.what() = DB::Exception

at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:53)
... 7 more

[serebrserg]

Please refer to explanation there https://github.com/yandex/clickhouse-jdbc/issues/188 In short, you shouldn't specify format yourself, because the driver queries server with format the driver supports, which is currently TabSeparatedWithNamesAndTypes.

[autumneve0108]

Hi Serebrserg,

It is due to the rows_before_limit_at_least value. Could you please help to specify how to extract the value from Class ClickhouseResultSet or ResultSetMetaData.

{"meta": [ { "name": "col_1", "type": "String" }, { "name": "col_2", "type": "Date" }, { "name": "col_3", "type": "Float64" }, { "name": "col_4", "type": "Float64" } ],

"data":
[
        {
                "col_1": "1",
                "col_2": "2018-05-01",
                "col_3": 3.3,
                "col_4": 4.5
        }
],

"rows": 1,

"rows_before_limit_at_least": 1,

"statistics":
{
        "elapsed": 0.000030582,
        "rows_read": 1,
        "bytes_read": 1
}

}

Thanks!!

[serebrserg]

To have rows_before_limit_at_least you can call ru.yandex.clickhouse.ClickHouseStatementImpl#executeQueryClickhouseResponse(java.lang.String) without specifying format. The driver will add FORMAT JSONCompact, query clickhouse and parse response to ru.yandex.clickhouse.response.ClickHouseResponse. The downside is that the whole result must fit in memory and that data is not typed - it is a List<List<String>> Currently there is no other way to get rows_before_limit_at_least value.

[cameronbraid]

This feature would be very useful to have.

Is there any way the JDBC driver can use JSON format internally and allow this field (and other metadata fields like statistics) to be accessed somehow - possibly via a custom subclass of ResultSetMetaData ?

[alex-krash]

@cameronbraid , we are going to move to RowBinary protocol internally. It is not clear for me in this issue, what functionality is missing?

[cameronbraid]

When running a select with limit clause, it would be useful to be able to get the number of records that were processed before the limit was applied. This is what the rows_before_limit_at_least gives (or at least an approximate).

In mysql you can do it with SELECT SQL_CALC_FOUND_ROWS ... LIMIT X then a second query with select FOUND_ROWS() to get the count before limit

in postgresql you can do SELECT COUNT(*) OVER() AS __full_count, (rest of select list) where ... limit X and pull out the __full_count value for the count before limit value

Clickhouse supports this (or something close) with rows_before_limit_at_least, however its not available via JDBC

[cameronbraid]

I tried doing it using two queries like

query = 'select * from pageViews'
limitQuery =  '$(query) limit 100 offset 20'
count = 'select count() from ($query)'

however the count query is typically very slow