/ 
DBIO-ClickHouse-0.900004
/ DBIO::ClickHouse

NAME

DBIO::ClickHouse - ClickHouse-specific schema management for DBIO

VERSION

version 0.900004

SYNOPSIS

package MyApp::Schema;
use base 'DBIO::Schema';
__PACKAGE__->load_components('ClickHouse');

my $schema = MyApp::Schema->connect(
  'dbi:mysql:database=default;host=127.0.0.1;port=9004', $user, $pass,
  { mysql_enable_utf8mb4 => 1 },
);

# or, using the `-ch` shortcut:
package MyApp::Schema;
use DBIO Schema => -ch;

DESCRIPTION

DBIO::ClickHouse is the ClickHouse driver component for DBIO. When loaded into a schema class, connection() sets "storage_type" in DBIO::Schema to +DBIO::ClickHouse::Storage, which enables ClickHouse-specific storage behavior.

Transport: the MySQL wire protocol

ClickHouse is a column-oriented OLAP database that speaks the MySQL wire protocol on server port 9004. There is no native DBD::ClickHouse on CPAN, so this driver rides DBD::mysql or DBD::MariaDB over that wire protocol. DBD::mysql is the standard: it sends no SQL at all on connect, so it works against a stock ClickHouse server -- and therefore ClickHouse Cloud -- with no server-side accommodation. The live suite runs against 25.3.14.14 over a dbi:mysql: DSN pointed at an unmodified server. Connect with mysql_enable_utf8mb4 => 1 or character data comes back as bytes; the live harness sets it for you.

DBD::MariaDB is supported for servers whose config you control. It issues SET character_set_server = 'utf8mb4' on every connect, which a stock ClickHouse refuses with UNKNOWN_SETTING unless the server registers

<custom_settings_prefixes>SQL_,character_set_,collation_,sql_</custom_settings_prefixes>

read at server start only. ClickHouse Cloud forbids custom prefixes, so DBD::MariaDB is unusable there. It decodes character data unconditionally, so no utf8 attribute is needed.

Either way the DSN names the DBD, never the database: dbi:mysql:...;port=9004 or dbi:MariaDB:...;port=9004, both reaching DBIO::ClickHouse::Storage. See docs/adr/0001-mysql-wire-protocol-transport.md.

The storage must be pinned explicitly

A ClickHouse MySQL-wire DSN is dbi:MariaDB:... (or dbi:mysql:...), which is indistinguishable from a real MySQL DSN and would otherwise resolve to the MySQL storage. There is therefore no DSN-based autodetection for ClickHouse. You must pin +DBIO::ClickHouse::Storage either by loading this component or by the -ch shortcut -- both do so via connection() / "apply_driver" in DBIO. The pinned storage takes both DBDs as its own access path, so neither is reported as another driver's ("ACCESS PATH: DBD::MYSQL AND DBD::MARIADB" in DBIO::ClickHouse::Storage).

METHODS

connection

Overrides "connection" in DBIO to force +DBIO::ClickHouse::Storage as storage_type.

clickhouse_install_ddl

my $sql = $schema->clickhouse_install_ddl;

Generates the ClickHouse-native install DDL for this schema through "install_ddl" in DBIO::ClickHouse::DDL: one CREATE TABLE per source, each with an explicit ENGINE and sorting key, statements separated by a blank line.

This is the method "deployment_statements" in DBIO::ClickHouse::Storage looks for, so loading this component is also what makes deployment_statements work.

CLICKHOUSE SEMANTICS

ClickHouse shares only the wire protocol with MySQL, not the semantics. Notably: no transactions, no savepoints, no SELECT ... FOR UPDATE, no INSERT ... RETURNING, and no auto-increment / last_insert_id (the primary key is the MergeTree ORDER BY key; surrogate identity uses generateUUIDv4()). UPDATE / DELETE are asynchronous mutations (ALTER TABLE ... UPDATE/DELETE). See DBIO::ClickHouse::Storage and docs/adr/0002-standalone-dist-not-mysql-subclass.md.

Transactions

Since there are none, the transaction API is split in two rather than faked wholesale. Everything that only promises atomicity is a no-op and warns once per interpreter: txn_begin and txn_commit send nothing to the server, and txn_do runs its coderef without a BEGIN/COMMIT bracket (and never retries it). If that coderef dies, its exception propagates unchanged, preceded by a loud warning -- one per failure -- that everything the block completed is permanent.

Everything that claims work was undone throws: txn_rollback always throws a DBIO::Exception. Swallowing a rollback would leave your code believing the changes are gone while they are still in the table, which is the one case where a silent no-op corrupts your understanding of the data rather than merely failing to help.

So ordinary schema code (including core's own populate, multi-create, delete_all and cascade delete, which all use txn_scope_guard) keeps working -- minus the atomicity ClickHouse never had. See "TRANSACTIONS" in DBIO::ClickHouse::Storage and docs/adr/0003-no-transactions-policy.md.

Native DDL generation is implemented: "clickhouse_install_ddl" renders the install DDL through DBIO::ClickHouse::DDL, with the per-table ENGINE, ORDER BY, PARTITION BY and SETTINGS options declared through DBIO::ClickHouse::Result's clickhouse_table. Introspection (DBIO::ClickHouse::Introspect), diff (DBIO::ClickHouse::Diff) and desired-state deploy (DBIO::ClickHouse::Deploy) are implemented as well.

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.