/ 
DBIO-ClickHouse-0.900004
/ DBIO::ClickHouse::Introspect

NAME

DBIO::ClickHouse::Introspect - Introspect a ClickHouse database via the system.* catalog

VERSION

version 0.900004

DESCRIPTION

DBIO::ClickHouse::Introspect reads the live state of a ClickHouse database over the MySQL wire protocol and returns a model hashref. It is the reverse of DBIO::ClickHouse::DDL: what the DDL layer renders from a DBIO column_info, this returns from the server state.

my $intro = DBIO::ClickHouse::Introspect->new(dbh => $schema->storage->dbh);
my $model = $intro->model;

To introspect a database other than the connection's own:

my $intro = DBIO::ClickHouse::Introspect->new(dbh => $dbh, database => 'analytics');

Each model section is fetched by a per-section reader under the Introspect/ subdirectory (DBIO::ClickHouse::Introspect::Tables, DBIO::ClickHouse::Introspect::Columns, DBIO::ClickHouse::Introspect::Indexes), matching the family layout mandated by core ADR 0018. The shared type parser lives in DBIO::ClickHouse::Introspect::Util.

Why system.* and not information_schema

ClickHouse ships information_schema views for MySQL-client compatibility, and this driver talks the MySQL wire protocol -- so reaching for them would be the obvious move. It is the wrong one. information_schema has no column for the table engine, the sorting key or the partition key, and it flattens ClickHouse's type strings into MySQL-ish approximations. Those are precisely the things a ClickHouse table cannot be recreated or compared without. The authoritative catalog is the system database, and that is what this reads.

Model shape

{
    tables                => { $name  => { ... } },
    columns               => { $table => [ { ... }, ... ] },
    indexes               => { },                 # always empty, see below
    data_skipping_indexes => { $table => { $name => { ... } } },
    foreign_keys          => { },                 # always empty, see below
}

indexes and foreign_keys are present but always empty. They are kept so that anything walking the canonical model of DBIO::Introspect::Base finds the sections it expects instead of autovivifying them; they are empty because ClickHouse has neither B-tree indexes nor unique constraints nor foreign keys (docs/adr/0004-no-foreign-keys.md). ClickHouse's data-skipping indexes are a different thing entirely and live in their own section -- DBIO::ClickHouse::Introspect::Indexes explains why conflating them would be actively harmful.

Round-trip fidelity

Type normalization is the exact inverse of DBIO::ClickHouse::DDL's type map, so a table this driver deployed introspects back to the metadata it was deployed from. The cases where that cannot hold -- String being the target of the whole string family, and explicitly numbered enums -- are enumerated in "Round-trip fidelity" in DBIO::ClickHouse::Introspect::Util. The diff layer must know about them or it will report phantom changes.

Table comments and column comments are read here but are not emitted by DBIO::ClickHouse::DDL, so they are a one-way street: introspection sees them, a redeploy drops them. Core's changed_column_fields does not compare comments, so this does not produce a phantom diff today.

ATTRIBUTES

database

Database name to introspect. When undef (the default) all queries are scoped to currentDatabase(), i.e. the database= component of the DSN.

METHODS

table_columns_info

Hashref { col_name = { data_type, size, is_nullable, default_value, is_auto_increment, extra } }>, with the native ClickHouse type of each column parsed back into portable DBIO metadata by "normalize_type" in DBIO::ClickHouse::Introspect::Util.

extra carries the flags that have no portable column-info home and that the DDL layer reads back: low_cardinality, unsigned, fixed_string, list (enum members), timezone, plus default_kind / default_expression for non-DEFAULT defaults.

is_nullable comes from the type string and nothing else. DBIO::Introspect::Base additionally folds is_pk into it, for models whose engine reports the NOT NULL attribute separately from the key constraint; this override does not, because ClickHouse spells nullability in the type and the fold could only ever contradict it. See the comment on the assignment.

table_uniq_info

Always an empty arrayref.

ClickHouse has no unique constraints and no unique indexes. The MergeTree sorting key is not unique either -- it is a sort order, and inserting the same key twice stores it twice. Reporting anything here would make a generated schema claim a uniqueness the database does not enforce, which DBIO would then act on in find and update_or_create.

table_fk_info

Always an empty arrayref. ClickHouse has no foreign keys at all -- not even a parsed-and-ignored syntax. See docs/adr/0004-no-foreign-keys.md.

table_comment

The COMMENT of the table, or undef.

column_comment

The COMMENT of one column, or undef.

view_definition

SQL text of the view's SELECT, or undef.

ClickHouse has no information_schema.views.view_definition worth reading (it is empty on most versions), so the definition is recovered from system.tables.create_table_query by stripping the leading CREATE [MATERIALIZED] VIEW ... AS. Fetched lazily, and only for views.

result_class_extra_statements

Emits one [ clickhouse_table => \%options ] statement per table, so a schema regenerated by DBIO::Generate carries the engine and the MergeTree keys it needs to be redeployable. The shape is DBIO::ClickHouse::Result's clickhouse_table; key entries that are bare column names come back as strings and expressions as SCALAR refs, which is what that method accepts.

Views get nothing -- they have no engine options to restate.

primary_key is emitted only when it actually differs from order_by: ClickHouse fills the primary key in from the sorting key when it was not declared separately, and restating it would make every table look like it declared a redundant prefix. settings is not emitted either, because engine_full always reports the server's own defaults (index_granularity = 8192 and friends) and pinning those into a generated schema would freeze a server default into user code.

NORMALIZED CONTRACT

These methods implement the generation contract defined in DBIO::Introspect::Base. table_keys, table_columns, table_pk_info and table_is_view are inherited unchanged -- the model follows the canonical shape, and the per-section readers already resolved ClickHouse's peculiarities (the MergeTree key standing in for a primary key, the engine name standing in for a table type). The methods below are overridden because ClickHouse genuinely differs.

SEE ALSO

AUTHOR

DBIO Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.