ClickHouse
[client-v2] JSON Support
Description
Client should support JSON Data type (https://clickhouse.com/docs/en/integrations/data-formats/json/overview).
Considerations:
- Client should provide convenient API to get access to row data
- Client should have examples of using a few commonly used JSON parsing libraries
- Client should not implement own JSON parser in any way
- Client documentation should list all details of work with JSON format
See also:
- https://github.com/ClickHouse/ClickHouse/pull/70288
- https://github.com/Avogar/ClickHouse/blob/master/src/DataTypes/Serializations/SerializationObject.cpp
Hi @dzmitry-biatenia-smop! We plan to release 0.7.2 by end of November. However this functionality is not in the 0.7.2: it is a complex and experimental feature - so it will take time to add it to the client. However 0.7.2 might have a minimal support when JSON columns can be read/written as a string.
How would you use JSON? Do you have a DTO object or do you work directly with some generic classes generated by GSON or Jackson ?
Thanks!
@chernser the basic case - I was able to create a column with JSON type in Clickhouse table with flag allow_experimental_json_type enabled, and even was able to insert some JSON data there with com.clickhouse.client.api.Client#execute(), but when can running any com.clickhouse.client.api.Client.query("select * from table_with_json_column") I'm getting Unsupported format: "JSON" coming from here because response has JSON type here. I tried to set output_format_binary_write_json_as_string , but seems it's not supported by my version of server 24.8.4
@chernser are you going to release that fix separately? any ETA for that fix?
@dzmitry-biatenia-smop patch should be release by end of this week. release is by end of the month another option is to take nightly build.
@chernser can you please notify me when that patch is ready or point me to the correct nightly build?
Good day, @dzmitry-biatenia-smop ! I've just merged the JSON support (see example). Nightly build should be ready in a several minutes. Here is how to use it https://gist.github.com/chernser/b4eacfde70093847449e3aef8e51ae8e
@chernser I tried nightly build as per example you've provided with dependency on new nightly build,
<repository>
<id>ossrh</id>
<name>Sonatype OSSRH</name>
<url>https://s01.oss.sonatype.org/content/repositories/snapshots/</url>
</repository>
with version
<dependency>
<groupId>com.clickhouse</groupId>
<artifactId>client-v2</artifactId>
<version>0.7.1-SNAPSHOT</version>
</dependency>
and event could not build a client with those options:
.serverSetting(ClientSettings.INPUT_FORMAT_BINARY_READ_JSON_AS_STRING, "1")
.serverSetting(ClientSettings.OUTPUT_FORMAT_BINARY_WRITE_JSON_AS_STRING, "1")
getting
Setting input_format_binary_read_json_as_string is neither a builtin setting nor started with the prefix 'SQL_' registered for user-defined settings: Maybe you meant ['input_format_json_read_bools_as_strings','input_format_json_read_objects_as_strings']. (UNKNOWN_SETTING) (version 24.8.4.13 (official build))
or
Setting output_format_binary_write_json_as_string is neither a builtin setting nor started with the prefix 'SQL_' registered for user-defined settings: Maybe you meant ['output_format_arrow_string_as_string','output_format_parquet_string_as_string']. (UNKNOWN_SETTING) (version 24.8.4.13 (official build))
errors.
When I'm trying to read JSON in java client without those options, I'm getting another runtime error:
Reading JSON from binary is not implemented yet
Please, advise
Hi @chernser! Are there any updates or plans to resolve this issue? We’re very interested in leveraging the potential of JSON in ClickHouse for working with data in Metabase, but as far as I understand, this is currently a blocker. Is there any way we could help accelerate resolving this, if so, feel free to reach out.
Hi @chernser, I am also highly interested in the new JSON functionality, however my use case would be DBeaver + Grafana, but as far as I understand it the error is everywhere the same...
Hi @chernser , is there a way to pass the output_format_binary_write_json_as_string option through the JDBC driver? I think the serverSettings are not really supported.
Good day, @redox !
JDBC accepts all settings and filters out only own settings.
You can use com.clickhouse.client.api.ClientConfigProperties#serverSetting to wrap any server setting and pass it to client.
This method will add com.clickhouse.client.api.ClientConfigProperties#SERVER_SETTING_PREFIX to a setting name (for example,
clickhouse_setting_output_format_binary_write_json_as_string. Value will be converted into a string with valueOf and will be sent to a server.
JDBC accepts all settings and filters out only own settings. You can use
com.clickhouse.client.api.ClientConfigProperties#serverSettingto wrap any server setting and pass it to client. This method will addcom.clickhouse.client.api.ClientConfigProperties#SERVER_SETTING_PREFIXto a setting name (for example,clickhouse_setting_output_format_binary_write_json_as_string. Value will be converted into a string withvalueOfand will be sent to a server.
Oh wow I didn't get this!! Thank you so much, will try now!
@chernser I ended up hitting a "Failed to read row" error and opened https://github.com/ClickHouse/clickhouse-java/issues/2359 - would love to hear from you what I'm missing
What is the current status of the JSON support? I could not find useful information in the readme. JSON data type is ready for prime time in clickhouse, then client v2 should also have support for JSON.
@MacNale the client does have support for JSON data type. You can find our supported types for client v2 in our documentation.
Was there something specific you were looking for?
