Pull Plugin
This document covers practical usage of pull. In TypeScript it is an optional plugin (@chkit/plugin-pull); in Python, chkit pull is a built-in CLI command — same introspection, no registration needed.
What it does
Section titled “What it does”- Connects to a live ClickHouse instance and introspects table metadata (columns, engines, indexes, projections, constraints, partitioning, TTL, settings).
- Introspects views and materialized views (including
TOclause parsing). - Introspects dictionaries (attributes — including
HIERARCHICAL/BIDIRECTIONAL/INJECTIVE/IS_OBJECT_IDmodifiers — primary key,SOURCE/LAYOUT/LIFETIME/RANGE/SETTINGS), preserving ClickHouse’s[HIDDEN]password redaction — see Credential handling. - Generates a deterministic schema file using the chkit builders — TypeScript (
@chkit/core) or Python (chkit). - Supports filtering by database and dry-run previews.
How it fits your workflow
Section titled “How it fits your workflow”The plugin is designed for bootstrapping a chkit project from an existing ClickHouse deployment.
chkit pull(alias forchkit plugin pull schema):- Connects to ClickHouse, introspects all schema objects, and writes a schema file in your project’s language.
- Generated file works directly with
chkit generateandchkit check.
- Dry-run mode previews the output without writing to disk.
Plugin setup
Section titled “Plugin setup”In clickhouse.config.ts, register pull(...) from @chkit/plugin-pull.
import { defineConfig } from '@chkit/core'import { pull } from '@chkit/plugin-pull'
export default defineConfig({ schema: './src/db/schema/**/*.ts', plugins: [ pull({ outFile: './src/db/schema/pulled.ts', databases: ['analytics'], overwrite: false, }), ],})No setup — chkit pull is built into the CLI. Configure behavior with flags:
chkit pull --out-file src/db/schema/pulled.py --database analyticsOptions
Section titled “Options”outFile(default:./src/db/schema/pulled.ts) — Output file path for the generated schema.databases(default:[], meaning all) — Filter to specific databases.overwrite(default:false) — Allow overwriting an existing output file.introspect(default: built-in) — Custom introspection function (advanced).
Invalid option values fail fast at startup via plugin config validation.
Commands
Section titled “Commands”chkit plugin pull schema(also available aschkit pull)- Introspects live ClickHouse and writes a chkit schema file.
Useful flags:
--out-file <path>— Override output file path.--database <name>— Filter to databases (repeat the flag for several).--dryrun— Preview output without writing.--force/--overwrite— Overwrite existing output file.
Exit codes: 0 (success), 1 (runtime error), 2 (config error).
Generated output format
Section titled “Generated output format”The output is a schema module that imports the chkit builders and exports the pulled definitions.
Column defaults come back as the SQL that ClickHouse stores, written as an expression ({ expression: "now64(3)" }), so a string default keeps its SQL quotes: { expression: "'web'" }. Pull keeps the defaultKind of MATERIALIZED, ALIAS, and EPHEMERAL columns next to it: { defaultKind: "MATERIALIZED", default: { expression: "toDate(ts)" } }. A pulled default works like a hand-written one; see default. Upgrade chkit together with @chkit/plugin-pull: chkit 0.2.0-beta.8 and older cannot read the { expression } form.
Comments of tables, columns, views, materialized views and dictionaries come from system.tables.comment and system.columns.comment, which ClickHouse has already decoded, so the pulled comment is exactly the stored text. A comment that contains AS leaves the pulled as query unchanged.
import { schema, table, view, materializedView } from '@chkit/core'
// Pulled from live ClickHouse metadata via chkit plugin pull schema
const app_events = table({database: "app",name: "events",engine: "MergeTree()",columns: [{ name: "id", type: "UInt64" },{ name: "received_at", type: "DateTime64(3)", default: { expression: "now64(3)" } },],primaryKey: ["id"],orderBy: ["id"],partitionBy: "toYYYYMM(received_at)",})
const app_events_view = view({database: "app",name: "events_view",as: "SELECT id FROM app.events",})
const app_events_mv = materializedView({database: "app",name: "events_mv",to: { database: "app", name: "events_rollup" },as: "SELECT id, count() AS c FROM app.events GROUP BY id",})
export default schema(app_events, app_events_view, app_events_mv)# Schema pulled from live ClickHouse metadata via `chkit pull`.from chkit import ColumnDefinition, SQLExpression, TableRef, materialized_view, schema, table, view
app_events = table( database="app", name="events", engine="MergeTree()", columns=[ ColumnDefinition(name="id", type="UInt64"), ColumnDefinition(name="received_at", type="DateTime64(3)", default=SQLExpression(expression="now64(3)")), ], primary_key=["id"], order_by=["id"], partition_by="toYYYYMM(received_at)",)
app_events_view = view( database="app", name="events_view", as_="SELECT id FROM app.events",)
app_events_mv = materialized_view( database="app", name="events_mv", to=TableRef(database="app", name="events_rollup"), as_="SELECT id, count() AS c FROM app.events GROUP BY id",)
definitions = schema(app_events, app_events_view, app_events_mv)Tables may also include uniqueKey, ttl, settings, indexes, projections, and constraints when present in the source metadata. Every setting the server stores is pulled, including the ones it fills in itself, such as index_granularity = 8192 on every MergeTree table. A setting that cannot be altered on an existing table gets a comment saying so: the read-only settings (a change recreates the table) and, on an ObsessionDB Shared table, storage_policy. The hidden auto_minmax_index_* skip indexes ClickHouse creates for the add_minmax_index_for_* settings are not pulled. An index type with no structured DSL form (or arguments the structured form cannot express exactly) is written as a raw index that carries ClickHouse’s type verbatim, so the pulled schema recreates the same index. Constraint expressions are written as ClickHouse stores them, e.g. (s IN ('a', 'b')) AND (id > 0).
chkit’s migration journal (_chkit_migrations, or the table CHKIT_JOURNAL_TABLE names) is never pulled. On a database chkit migrated, chkit generate on the pulled schema plans no operations and chkit drift reports none:
- A literal default and the expression pull reads back compare by value:
7and{ expression: "7" },2.0and2.,"it's"and'it\'s'. - Settings compare by value (
4096and"4096"), and a MergeTree table withoutindex_granularityhas the8192ClickHouse stores. - With the ObsessionDB plugin, a table without
storage_policyhas the's3'every ObsessionDBSharedtable has. - A dictionary
lifetimecompares in the form ClickHouse stores (300andMIN 0 MAX 300).
Credential handling ([HIDDEN] passwords)
Section titled “Credential handling ([HIDDEN] passwords)”By default, ClickHouse redacts inline SOURCE(...) passwords to [HIDDEN] on introspection (system.tables.create_table_query, SHOW CREATE DICTIONARY), and chkit does not attempt to work around that. When a pulled dictionary’s source contains [HIDDEN], chkit pull prints a console warning (and includes it in a warnings array in --json output), and the generated file emits the source verbatim with a leading comment:
// NOTE: password redacted by ClickHouse — replace '[HIDDEN]' with your credential (e.g. process.env.X).const default_users_dict = dictionary({database: "default",name: "users_dict",attributes: [{ name: "id", type: "UInt64" },{ name: "name", type: "String" },],primaryKey: ["id"],source: "MYSQL(host 'db' port 3306 user 'reader' password '[HIDDEN]' db 'app' table 'users')",layout: "HASHED()",lifetime: "300",})# NOTE: password redacted by ClickHouse — replace '[HIDDEN]' with your credential.default_users_dict = dictionary( database="default", name="users_dict", attributes=[ DictionaryAttribute(name="id", type="UInt64"), DictionaryAttribute(name="name", type="String"), ], primary_key=["id"], source="MYSQL(host 'db' port 3306 user 'reader' password '[HIDDEN]' db 'app' table 'users')", layout="HASHED()", lifetime="300",)Replace [HIDDEN] with a real credential — typically an environment-variable interpolation, matching how you’d author the dictionary by hand (see Credentials in source):
source: `MYSQL(host 'db' port 3306 user 'reader' password '${process.env.MYSQL_PASSWORD}' db 'app' table 'users')`,source=f"MYSQL(host 'db' port 3306 user 'reader' password '{os.environ['MYSQL_PASSWORD']}' db 'app' table 'users')",For round-trip fidelity without a manual edit, use named collections on the ClickHouse side instead of an inline password — chkit does not require this, but it’s the ClickHouse-native way to avoid the redaction entirely.
Because a source still carrying [HIDDEN] is excluded from the diff entirely (see Credentials in source), chkit generate won’t produce a migration for that dictionary’s source until you replace the placeholder with a real value.
Recovering the real password instead
Section titled “Recovering the real password instead”ClickHouse can be configured to skip the redaction and hand back the real password on introspection: enable the server-side display_secrets_in_show_and_select setting and grant the connecting user displaySecretsInShowAndSelect. The [HIDDEN] warning mentions this escape hatch.
If you do this, be aware of the consequence: chkit pull has no way to detect that this is happening, or to re-redact the value on your behalf — it just copies through whatever ClickHouse returns. The real password lands in the generated schema file in plain text, same as any dictionary you’d author by hand with an inline credential. chkit pull detects this case too and warns that a plain-text password was written to the file, so you don’t discover it by accident later.
Current limits
Section titled “Current limits”- Materialized views without a
TOclause (with an inner table) are skipped with a warning, and so is their.inner_id.*table. - Dictionaries whose
create_table_querycan’t be parsed (attributes, primary key, orSOURCE/LAYOUT/LIFETIMEmissing) are skipped. - XML-config dictionaries (not created via DDL) are not introspected.
- Requires a live ClickHouse connection.
- Fails when the server’s MergeTree
index_granularitydefault is notclickhouse.indexGranularity(8192 by default): the pulled schema would plan changes against the snapshot.