clickhouse-connect v1.13.0
This release adds declarative skip indexes and projections to the SQLAlchemy dialect and Alembic autogenerate. It also adds Alembic view and SQL UDF operations and compares table engines and settings during autogenerate. It fixes a broad set of insert validation, query classification, binding and pandas/NumPy decoding bugs.
Installation
pip install clickhouse-connect
Upgrade notes
Some fixes change behavior that existing code may depend on. Check these before you upgrade:
- Alembic autogenerate compares more by default. Explicit indexes, projections and table options are now compared with the server. Indexes or projections created only by earlier imperative migrations can generate drops when they are not declared in the metadata. Declare them with
ClickHouseIndexorClickHouseProjection, exclude them with Alembic filters, or passclickhousedb_compare_indexes=False,clickhousedb_compare_projections=Falseorclickhousedb_compare_table_options=Falsetocontext.configure(). A change to the table engine,ORDER BY,PARTITION BY, TTL or a read-only setting now raisesUnsupportedTableChangeErrorinstead of writing an empty revision. op.alter_column(..., server_default=None)now removes the default. The server rejectsREMOVE DEFAULTwhen the column has no default, so a hand-writtenserver_default=Nonethat was a silent no-op now fails.Inspector.get_columns()returns plain strings forserver_default,clickhouse_materialized,clickhouse_aliasandclickhouse_ttl. Wrap them insqlalchemy.text()if you buildColumnobjects from them directly.- Stricter Python codec inserts. Unknown Enum labels,
Nonein non-nullable Enum and integer columns, and NaN or infinity in integer columns now raiseDataErrorinstead of writing 0.insert_dfstill writes 0 or NULL for missing values. - Placeholder-like text in strings and comments no longer selects server-side binding. These queries use client-side percent formatting, so a bare
%must be escaped as%%when you pass parameters. query_dfwithNullable(Int128/UInt128/Int256/UInt256)now returnsobjectdtype with Pythonintvalues. Before, values above2**53could be rounded throughfloat64.
Highlights
Features
- Declarative data skipping indexes with
ClickHouseIndexandInspector.get_clickhouse_indexes(), with Alembic autogenerate support. - Declarative projections with
ClickHouseProjectionandInspector.get_clickhouse_projections(), with Alembic autogenerate support. - Alembic autogenerate compares the table engine, keys, TTL and settings. Changed engine
settingsrender asop.modify_clickhouse_table_settings(...)with a reversible downgrade. - Alembic create, drop and
CREATE OR REPLACEhelpers for ordinary views and SQL UDFs.Inspector.get_clickhouse_function_names()andget_clickhouse_function_definition()reflect SQL UDFs. - All 21 ClickHouse Alembic operation classes are now public, with typed fields for policy hooks and Rewriters.
Inspector.get_view_definition()returns the storedSELECTof views and materialized views.- SQLAlchemy
Tupleaccepts anelementsmapping of field names to types. Closes #1144.
Performance
- String and bytes parameter escaping uses lookup tables. Large
INlists andArray(String)parameters bind about 3-4x faster, binaryStringparameters about 6x. - Python codec
IPv6inserts of address objects are about 10-35x faster. - The Rust codec reads scalar nullable date and datetime columns from typed buffers, which speeds up NumPy and pandas results.
- SQL comment detection uses a compiled scanner on CPython with the C extensions.
Bug fixes
- Query classification follows the ClickHouse lexer for comments, backtick identifiers, heredocs and escapes, so
query_limitand command detection work as expected. Closes #925. - Enum type names without element numbers, such as
Enum8('a', 'b'), now parse. Closes #1189. - Unknown Enum labels raise
DataErrorinstead of inserting 0. Closes #1156. - NaN and infinity in direct integer inserts raise
DataError. Closes #1172. query_dfkeeps largeNullable(Int128)andNullable(Int256)family values exact. Fixes #1181.query_dfandquery_npdecode JSON values stored in shared data, and no longer drop shared0andfalsevalues.client.insertconverts NumPydatetime64arrays to the unit of the target date or time column.- Python codec
Decimalinserts truncate extra digits like the server and the Rust codec. - Unencodable
FixedStringvalues raiseUnicodeEncodeErrorinstead of writing zero bytes. - Top-level
enum.Enummembers in server-side parameters bind like their value. - Naive
datetimeinserts near and before the epoch no longer raiseOSErroron Windows. Addresses the Python codec part of #1162. - Placeholder-like text in strings, identifiers, comments and heredocs no longer selects server-side binding. Closes #1146.
- Dictionaries bound to named
Tupleparameters use the declared field order. Closes #1148. Inspector.get_columns()returns JSON-serializable strings for column expressions. Closes #1141.- With the Rust codec, nullable date and datetime columns in NumPy and pandas results match the Python codec.
- Alembic version-table inserts bypass insert deduplication, so a downgrade then upgrade no longer replays applied migrations.
- Alembic autogenerate no longer proposes
drop_tablefor views missing from the metadata. op.alter_column(..., server_default=None)emitsREMOVE DEFAULT, andNonefor codec, TTL, alias or materialized removes that property.- Reflection handles engine settings that start with a clause keyword, such as
ttl_only_drop_parts, and quoted engine names such asNull. - Typed SQLAlchemy
Mapparameters keepDateTime64fractional seconds withdict_parameter_format="map". - Integer insert errors say whether a value had the wrong type or was out of range.
See the CHANGELOG for full details.