/ 
DBIO-ClickHouse-0.900004
/ DBIO::ClickHouse::SQLMaker

NAME

DBIO::ClickHouse::SQLMaker - ClickHouse-specific SQL generation for DBIO

VERSION

version 0.900004

DESCRIPTION

ClickHouse-specific subclass of DBIO::SQLMaker. ClickHouse is reached over the MySQL wire protocol and uses backtick identifier quoting; the quote character is applied by DBIO::ClickHouse::Storage (via sql_quote_char and its sql_maker post-processing), not set here.

This class turns off the bits ClickHouse does not do:

  • SELECT ... FOR UPDATE is not supported -- disabled via _lock_select.

re-renders UPDATE and DELETE as the asynchronous mutations ClickHouse actually has -- see "MUTATIONS" -- adds the ClickHouse SELECT extensions that have no ANSI equivalent and are therefore not reachable through any core attribute -- see "CLICKHOUSE RESULTSET ATTRIBUTES" -- and renders the multi-row INSERT that "_insert_bulk" in DBIO::ClickHouse::Storage needs, see "insert_multi".

All other SQL generation is inherited unchanged from DBIO::SQLMaker.

This class is set as the sql_maker_class by DBIO::ClickHouse::Storage and is not normally instantiated directly.

METHODS

new

Defaults and validates the mutations_sync option (see "mutations_sync"), and registers the renderer that drops the SET keyword from an UPDATE.

update

my ($sql, @bind) = $sqlmaker->update('hit', { hits => 1 }, { id => $id });
# ALTER TABLE `hit` UPDATE `hits` = ? WHERE `id` = ? SETTINGS mutations_sync = 1

Renders the ALTER TABLE ... UPDATE mutation. See "MUTATIONS".

delete

my ($sql, @bind) = $sqlmaker->delete('hit', { id => $id });
# ALTER TABLE `hit` DELETE WHERE `id` = ? SETTINGS mutations_sync = 1

Renders the ALTER TABLE ... DELETE mutation. See "MUTATIONS".

_render_update_clause_target

Renders ALTER TABLE `t` UPDATE where core renders UPDATE `t`.

_render_update_clause_set

Renders the assignment list without the SET keyword -- ClickHouse writes ALTER TABLE t UPDATE a = 1, b = 2, not ... UPDATE SET a = 1. Registered as the update.set clause renderer in "new"; the assignment list itself is rendered by the inherited machinery, so literal SQL and per-column bind attributes behave exactly as on any other driver.

_render_delete_clause_target

Renders ALTER TABLE `t` DELETE where core renders DELETE FROM `t`.

insert_multi

my ($sql, @bind) = $sqlmaker->insert_multi(
  'hit', [qw( id url )], [ [ 1, '/a' ], [ 2, '/b' ] ],
);
# INSERT INTO `hit` (`id`, `url`) VALUES (?, ?), (?, ?)
# binds: [ id => 1 ], [ url => '/a' ], [ id => 2 ], [ url => '/b' ]

Renders one INSERT statement carrying every row as its own VALUES tuple. This is the shape ClickHouse wants -- an INSERT is a part write, and one statement per row produces one tiny part per row, which is the classic way to melt a MergeTree table. "_insert_bulk" in DBIO::ClickHouse::Storage routes the whole populate dataset through here as a single statement.

$cols is a positional column list; $rows is an arrayref of arrayrefs, each of the same length. A cell is a plain bind value, \'literal SQL', or \[ 'literal SQL with ?', @binds ] -- the same value vocabulary as core's single-row insert.

Binds come back in statement order (row by row, left to right) as [ $column_name => $value ] pairs, the bindtype => 'columns' form core's _resolve_bindattrs expects, so per-column type attributes are applied to every row.

Unlike core's _insert_bulk -- which prepares one statement and executes it once per row, and therefore requires the literal SQL of a given column to be identical in every row -- each row is rendered separately here. Rows may mix literals and plain values freely, e.g. \'generateUUIDv4()' in one row and an explicit id in the next.

What every row must share is the column list: one statement has one (col, col, ...) header, so a row of the wrong length is a hard error rather than a silently padded tuple.

The rendered statement grows with the dataset (one placeholder per cell). A populate large enough to exceed the server's max_allowed_packet has to be chunked by the caller; nothing here splits it.

_render_bind

Renders every bound value, and changes exactly one kind: a string that contains the byte 0x1A (SUB, Ctrl-Z) is bound as replaceAll(?, 'ch1a<n>x', char(26)), with the value's 0x1A bytes replaced by that marker.

The reason is the transport. DBD::mysql substitutes the placeholders itself and escapes 0x1A as \Z; ClickHouse's parser does not know that escape and stores the backslash and the Z, so a text or blob value grew by one character per 0x1A and read back as something else (bind-binary, bind-lob-empty-and-large). Server-side preparation, which would send the bytes untouched, is not available: the server reports no parameters for a prepared statement (mysql_server_prepare, measured). unhex(?) over the hex of the value is exact, but doubles the statement, and a 100000-byte blob then exceeds the server's max_query_size (262144, measured). The marker is the smaller change: the value goes as it was with nothing but the 0x1A bytes replaced, one placeholder, no re-encoding. <n> is the first number for which the marker does not occur in the value, so the replacement cannot touch anything else.

Measured with DBD::mysql only. DBD::MariaDB does not connect to a stock server (it sends SET character_set_server, rejected with UNKNOWN_SETTING; see DBIO::ClickHouse::Storage), so how it escapes 0x1A and whether the marker arrives intact there has not been measured and is not claimed.

Nothing else is rewritten: a value without 0x1A, undef, a reference (literal SQL) and a number are bound as they are. The bind keeps its column, so its attributes are resolved as for any other.

select

Stashes the ClickHouse dialect attributes for the duration of the statement so "_table" and "_gen_from_blocks" -- which are handed only the from spec by SQL::Abstract -- can reach them, then defers to the inherited implementation.

_assemble_binds

The inherited bind order with two ClickHouse slots spliced in: PREWHERE binds between the FROM and the WHERE binds, LIMIT n BY binds between the ORDER BY and the LIMIT binds -- i.e. each in the position its clause occupies in the emitted statement.

_table

Appends the PREWHERE clause (which follows the whole FROM and JOIN list) and, for a bare table name, the FINAL modifier.

_gen_from_blocks

Attaches FINAL to the primary (leftmost) table block of the outermost FROM list, after its alias.

_parse_rs_attrs

Appends LIMIT n BY columns after the inherited GROUP BY / HAVING / ORDER BY rendering, which is exactly where ClickHouse expects it: after ORDER BY and before the ordinary LIMIT that apply_limit adds.

_limit_by

my ($sql, @bind) = $sqlmaker->_limit_by({ rows => 3, by => 'user_id' });

Renders the limit_by attribute. See "limit_by".

CLICKHOUSE RESULTSET ATTRIBUTES

final, prewhere and limit_by are ordinary resultset attributes: DBIO::ResultSet::_resolved_attrs copies the attribute hash wholesale and DBIO::Storage::DBI::_select_args hands that whole hash to "select" as its fourth argument, so unknown keys reach the SQL maker untouched. No core change, no registration and no whitelist is involved -- the same route DBIO::Oracle::SQLMaker uses for connect_by / start_with.

final

$schema->resultset('Hit')->search(undef, { final => 1 });
# SELECT `me`.`id` FROM `hit` `me` FINAL

Emits the FINAL modifier, which makes a MergeTree-family table return fully merged rows -- the only correct way to read a ReplacingMergeTree or CollapsingMergeTree without doing the deduplication by hand. There is no core attribute that produces it, hence real API surface rather than a documented escape hatch.

FINAL is a modifier on a single table reference and must follow that table's alias (FROM t AS me FINAL; putting it before the alias is a syntax error). With joins it is therefore attached to the primary (leftmost) table only and join targets are left alone; ClickHouse does not propagate FINAL across a join either. Use literal SQL if a joined table needs it too.

prewhere

$schema->resultset('Hit')->search({ url => $url }, {
  prewhere => { event_date => $day },
});
# ... FROM `hit` `me` PREWHERE `event_date` = ? WHERE ( `url` = ? )

Emits ClickHouse's PREWHERE filter, which is read and evaluated before the remaining columns of the granule are fetched. The value is an ordinary SQL::Abstract condition with exactly the same bind semantics as where -- it is rendered through _recurse_where, not passed through as literal SQL -- and its bind values are assembled into their own slot so they land between the FROM binds and the WHERE binds.

Because PREWHERE is applied while reading the primary table, the condition should only reference columns of that table.

Note that the condition is rendered without the extra parentheses core wraps around WHERE; this follows the HAVING precedent in DBIO::SQLMaker::ClassicExtensions::_parse_rs_attrs.

limit_by

$schema->resultset('Hit')->search(undef, {
  order_by => { -desc => 'ts' },
  limit_by => { rows => 3, by => 'user_id' },
  rows     => 100,
});
# ... ORDER BY `ts` DESC LIMIT 3 BY `user_id` LIMIT ?

Emits LIMIT n BY columns -- "the first n rows per distinct value of columns", ClickHouse's top-N-per-group without a window function. It has no ANSI equivalent and slots between ORDER BY and the ordinary LIMIT/OFFSET, which no combination of core attributes can express.

The signature is a hashref reusing two names that already mean this elsewhere in a DBIO attribute hash: rows is the row count (as in the core rows attribute that becomes LIMIT) and by takes the same column spec as group_by (a name, an arrayref of names, or a \'literal' scalar ref), fed through the same _recurse_fields. A flat arrayref such as [ 3, 'user_id' ] was rejected because it hides which element is the count.

rows is validated as a positive integer and rendered inline, not as a placeholder. ClickHouse wants a constant expression in the LIMIT family, and placeholder behaviour there over the MySQL wire protocol is not yet verified against a live server; an integer that has passed /\D/ carries no injection surface. Revisit once the live suite has confirmed how the wire handles bound LIMIT arguments.

The LIMIT offset, n BY columns form is not exposed -- use literal SQL if you need the offset variant.

NOT IMPLEMENTED: SAMPLE

SAMPLE k is deliberately not given an attribute. It only works on tables declared with a SAMPLE BY expression in their engine definition (which the DDL layer does not emit yet), and on any other table it is an error rather than a no-op -- too little payoff for sugar that would look universally available.

Reach it with literal SQL instead, either through a result source whose name is a literal-SQL scalar ref:

__PACKAGE__->name(\'(SELECT * FROM hit SAMPLE 0.1)');

or by running the statement directly:

$schema->storage->dbh_do(sub {
  $_[1]->selectall_arrayref('SELECT ... FROM hit SAMPLE 0.1 WHERE ...', undef, @bind);
});

MUTATIONS

ClickHouse has no UPDATE and no DELETE statement. Row modification is an asynchronous mutation, spelled as an ALTER:

UPDATE `hit` SET `hits` = ? WHERE `id` = ?    # every other driver
ALTER TABLE `hit` UPDATE `hits` = ? WHERE `id` = ? SETTINGS mutations_sync = 1

DELETE FROM `hit` WHERE `id` = ?
ALTER TABLE `hit` DELETE WHERE `id` = ? SETTINGS mutations_sync = 1

"update" and "delete" render that form, so $rs->update, $rs->delete, $row->update and $row->delete work through the ordinary DBIO API. Note the two differences from ANSI beyond the leading ALTER TABLE: there is no SET keyword (the assignment list follows UPDATE directly), and the WHERE clause is mandatory -- an unrestricted resultset renders WHERE 1, ClickHouse's spelling of "every row", rather than omitting the clause and producing a syntax error.

Everything about the WHERE clause itself is inherited: the same conditions, the same operators, the same bind order. Only the surrounding statement changes.

What does not change here is the semantics -- a mutation is not transactional, is not atomic across statements, and is applied by rewriting whole parts in the background. See "MUTATIONS" in DBIO::ClickHouse::Storage for the cost, the return value, and docs/adr/0005-mutations-sync-by-default.md for the mutations_sync decision.

mutations_sync

Every rendered mutation carries a trailing SETTINGS mutations_sync = N, which is what makes the statement wait for the mutation instead of merely queueing it. N defaults to 1 ("wait until the mutation is done on the server we are talking to") and is a connect-time option:

MyApp::Schema->connect($dsn, $user, $pass, { mutations_sync => 2 });

  • 1 (the default) -- wait for completion on the current server. A following SELECT over the same connection sees the change.

  • 2 -- wait for completion on all replicas. Needed only when reads may land on a different replica than the write; slower, and it blocks on every replica being reachable.

  • 0 -- do not wait. The statement returns as soon as the mutation is queued; a later error is never reported to the caller and a read straight afterwards may still see the old rows.

  • undef -- emit no SETTINGS clause at all, so the server's own setting applies. This is the escape hatch for setting the value per session (on_connect_do => ['SET mutations_sync = 2']) or globally in the server profile: with an explicit clause on every statement, a session-level SET would be silently overridden.

Any other value throws at construction time rather than being sent to the server.

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.