Experimental Feature - "New" JSON/Dynamic/Variant DataTypes
Usage Notes
- JSON data can be inserted as either a Python dictionary or a JSON string containing a JSON object `{}`. Other
forms of JSON data are not supported
- Valid formats for the JSON type are 'native', which returns a Python dictionary, or 'string', which returns a JSON string
- Any value can be inserted into a Variant column, and ClickHouse will try to correctly determine the correct Variant
Type for the value, based on its String representation.
- More complete documentation for the new types will be provided in the future.
Known limitations:
- Each of these types must be enabled in the ClickHouse settings before using. The "new" JSON type is available started
with the 24.8 release
- Returned JSON objects will only return the `max_dynamic_paths` number of elements (which defaults to 1024). This
will be fixed in a future release.
- Inserts into `Dynamic` columns will always be the String representation of the Python value. This will be fixed
in a future release.
- The implementation for the new types has not been optimized in C code, so performance may be somewhat slower than for
simpler, established data types.
This is the first time that a new `clickhouse_connect` features has been labeled "experimental", but these new
datatypes are complex and still experimental in ClickHouse server. Current test coverage for these types is also
quite limited. Please don't hesitate to report issues with the new types.
Bug Fixes
- When operating ClickHouse Server in `strict` TLS mode, HTTPS connections [require](https://github.com/ClickHouse/poco/blob/master/NetSSL_OpenSSL/include/Poco/Net/Context.h#L84-L89) a client certificate even if that
certificate is not used for authentication. A new client parameter `tls_mode='strict'` can be used in this situation where
username/password authentication is being used with client certificates. Other valid values for the new `tls_mode` setting
are `'proxy'` when TLS termination occurs at a proxy, and `'mutual'` to specify mutual TLS authentication is used by
the ClickHouse server. If `tls_mode` is not set, and a client certificate and key are provided, `mutual` is assumed.
- The server timezone was not being used for parameter binding if parameters were sent as a list instead of a dictionary.
This should fully fix the reopened https://github.com/ClickHouse/clickhouse-connect/issues/377.
- String port numbers (such as from environmental variables) are now correctly interpreted to determine the correct interface/protocol.
Fixes https://github.com/ClickHouse/clickhouse-connect/issues/395
- Insert commands with a `SELECT FROM ... LIMIT 0` will no longer raise an exception. Closes https://github.com/ClickHouse/clickhouse-connect/issues/389.
Improvements
- Some low level errors for problems with Native format inserts and queries now include the relevant column name in the
error message. Thanks to [Angus Holder](https://github.com/angusholder) for the PR!
- There is a new intermediate buffer for HTTP streaming/chunked queries. The buffer will store raw data from the HTTP request
until it is actually requested in a stream. This allows some lag between reading the data from ClickHouse and processing
the same data. Previously, if processing the data stream fell 30 seconds behind the ClickHouse HTTP writes to the stream,
the ClickHouse server would close the connection, aborting the query and stream processing. This will now be mitigated by
storing the data stream in the new intermediate buffer. By default, this buffer is set to 10 megabytes, but for slow
processing of large queries where memory is not an issue, the buffer size can be increasing using the new `common` setting
`http_buffer_size`. This is a fix in some cases of https://github.com/ClickHouse/clickhouse-connect/issues/399, but note that
slow processing of large queries will still cause connection and processing failures if the data cannot be buffered.
- It is now possible to correctly bind `DateTime64` type parameters when calling Client `query` methods through one of two approaches:
- Wrap the Python `datetime.datetime` value in the new DT64Param class, e.g.
python
query = 'SELECT {p1:DateTime64(3)}' Server side binding with dictionary
parameters={'p1': DT64Param(dt_value)}
query = 'SELECT %s as string, toDateTime64(%s,6) as dateTime' Client side binding with list
parameters=['a string', DT64Param(datetime.now())]
- If using a dictionary of parameter values, append the string `_64` to the parameter name
python
query = 'SELECT {p1:DateTime64(3)}, {a1:Array(DateTime(3))}' Server side binding with dictionary
parameters={'p1_64': dt_value, 'a1_64': [dt_value1, dt_value2]}
This closes https://github.com/ClickHouse/clickhouse-connect/issues/396, see also the similar issue https://github.com/ClickHouse/clickhouse-connect/issues/212