ClickHouse lets you define arbitrary settings as long as the name starts with custom_. They behave like regular settings: you can SET them in a session, attach them to user profiles, and read them with getSetting(). They are useful for passing application-level context (tenant ID, feature flag, schema version) through to query logic without polluting query parameters. The catch is that they need explicit typing when declared in XML, and the error message when you get the typing wrong is not obvious.
Defining a custom setting at the session level
The simplest case. Any setting prefixed with custom_ is accepted by SET without prior declaration:
SET custom_tenant_id = 42;
SET custom_feature_flag = 'beta';
SET custom_schema_version = toUInt64(7);
Read them back with getSetting:
SELECT getSetting('custom_tenant_id');
The value's runtime type is whatever was assigned. SQL type inference handles literals fine at the session level. The XML path is where typing becomes explicit.
Defining a custom setting in a user profile
You usually want custom settings to apply automatically per user or per role, not as a SET in every query. That means putting them in users.xml or users.d/*.xml under a profile. The naive attempt fails:
<?xml version="1.0"?>
<clickhouse>
<profiles>
<default>
<custom_data_version>1</custom_data_version>
</default>
</profiles>
</clickhouse>
Reloading the config produces:
Couldn't restore Field from dump: 1: while parsing value '1' for setting 'custom_data_version'. (CANNOT_RESTORE_FROM_FIELD_DUMP)
The reason: standard settings have a known type baked into the server, so the XML parser knows how to interpret the literal. Custom settings have no declared type, so the parser refuses to guess. You need to encode the type with the value.
Two ways to declare the type
Type prefix
Prepend the type name and an underscore to the value. The accepted prefixes mirror the variants in the internal Field type defined in src/Core/Field.cpp: UInt64, Int64, Float64, String, Bool, plus a few aggregate forms.
<?xml version="1.0"?>
<clickhouse>
<profiles>
<default>
<custom_data_version>UInt64_1</custom_data_version>
<custom_tenant_id>UInt64_42</custom_tenant_id>
<custom_factor>Float64_1.5</custom_factor>
</default>
</profiles>
</clickhouse>
Single-quoted strings
Wrap the value in single quotes inside the XML element to declare it as a string:
<?xml version="1.0"?>
<clickhouse>
<profiles>
<default>
<custom_data_version>'1'</custom_data_version>
<custom_feature_flag>'beta'</custom_feature_flag>
</default>
</profiles>
</clickhouse>
Pick the right form for the consumer. If a downstream query does toUInt64(getSetting('custom_data_version')), either form works because of implicit casting. If the query passes the value directly into arithmetic, the numeric form avoids unnecessary parsing.
Reading custom settings in queries
The getSetting function returns the value with its declared type:
-- In a view definition, gated by tenant
CREATE VIEW tenant_events AS
SELECT *
FROM events
WHERE tenant_id = getSetting('custom_tenant_id');
-- In a row policy or materialized column
ALTER TABLE events
ADD COLUMN schema_version UInt64
DEFAULT toUInt64(getSetting('custom_schema_version'));
Combined with profiles attached to specific users, this gives you a clean way to scope what a connection can see without rewriting queries on the client side.
When to use custom settings
Good fits:
- Multi-tenant isolation where the tenant ID needs to flow into views, row policies, and stored functions.
- Application versioning where the same table is read with different semantics depending on caller (schema migrations in flight).
- Feature flags that change query behavior without redeploying client code.
Bad fits:
- Anything that should be a query parameter. Custom settings are session-scoped and leak across queries on the same connection unless explicitly reset.
- Large blobs of data. Settings are not designed for kilobytes of state; use a table.
- Authorization. Custom settings can be overridden by the client unless the profile is marked
readonlyorconstraintsare added.
Locking down custom settings
To prevent a user from overriding a profile-set custom value, mark it constant in the profile:
<clickhouse>
<profiles>
<default>
<custom_tenant_id>UInt64_42</custom_tenant_id>
<constraints>
<custom_tenant_id>
<readonly/>
</custom_tenant_id>
</constraints>
</default>
</profiles>
</clickhouse>
Now any SET custom_tenant_id = ... from the client is rejected.
Common Pitfalls
- Forgetting the
custom_prefix. Names without it are treated as standard settings and rejected if unknown. - Using XML literals without a type prefix or quotes, then being surprised by
CANNOT_RESTORE_FROM_FIELD_DUMP. - Relying on a custom setting set by a previous query on the same connection. Use the profile or set it explicitly per query.
- Storing application secrets in custom settings. They are visible to anyone who can run
SELECT * FROM system.settings. - Mixing types between the XML default and runtime
SET. If the profile declaresUInt64_1and the client sets it to a string, queries that expect numeric behavior break.
Frequently Asked Questions
Q: What prefix do I need on the setting name?
A: custom_. Anything else and the server treats the name as a standard setting and complains it does not exist.
Q: Why do I get CANNOT_RESTORE_FROM_FIELD_DUMP?
A: The XML config does not know what type the value should be. Add a UInt64_, String_, Float64_, etc. prefix, or wrap string values in single quotes.
Q: Can I see all current custom settings?
A: Yes: SELECT * FROM system.settings WHERE name LIKE 'custom_%'. The changed column shows whether the value differs from the profile default.
Q: Do custom settings persist across reconnections?
A: Only if set in a profile. Settings applied via SET last for the lifetime of the session.
Q: Can I use custom settings inside a ROW POLICY expression?
A: Yes. getSetting('custom_tenant_id') is a regular function, valid anywhere an expression is. This is one of the most useful patterns for tenant scoping.
