Skip to main content

Managing ClickHouse Settings with Atlas

Introduction​

ClickHouse provides a powerful settings system that allows you to control query execution behavior, performance characteristics, and resource management. These settings can be applied at different levels: globally, per user/profile, per session, or per query.

When managing your ClickHouse schemas with Atlas, you may need to configure specific settings to enable certain features or optimize schema operations.

Method 1: Settings Profiles​

ClickHouse allows you to create settings profiles that define a collection of settings for specific users or roles.

This is the recommended approach for persistent configuration that applies to all Atlas operations.

You can create a dedicated profile for Atlas and assign it to the user with which Atlas connects to your database. Settings profiles can be created using either SQL or XML configuration files.

Using SQL:

-- Create a settings profile for Atlas
CREATE SETTINGS PROFILE atlas_profile SETTINGS 
    allow_suspicious_low_cardinality_types = 1,
    max_memory_usage = 10000000000;

-- Assign the profile to the Atlas user
ALTER USER atlas_user SETTINGS PROFILE atlas_profile;

Using XML configuration:

Alternatively, you can define profiles in your ClickHouse configuration file. See the ClickHouse documentation for details on XML configuration.

Method 2: Connection URL Parameters​

You can pass settings directly in the Atlas connection URL as query parameters. These settings apply to all queries executed by Atlas during that connection session.

Syntax​

clickhouse://user:password@host:port/database?setting_name=value&another_setting=value

Example​

atlas schema apply \
  --url "clickhouse://user:password@localhost:9000/default?allow_suspicious_low_cardinality_types=1&max_memory_usage=10000000000"

This approach is useful for:

  • Temporary or one-time operations
  • Testing different setting values
  • Environment-specific configurations without modifying the database

Method 3: Versioned Migration Files​

When using Atlas with the versioned migration workflow, you can specify settings directly in your SQL migration files.

Option A: Table-level Settings​

Apply settings to a specific table using the SETTINGS clause in the DDL statement:

CREATE TABLE users (
    id LowCardinality(Int32)
)
ENGINE = MergeTree
PRIMARY KEY (id)
ORDER BY (id)
SETTINGS index_granularity = 8192, allow_suspicious_low_cardinality_types = 1;

Option B: Session-level Settings​

Apply settings at the session level for all subsequent statements in the migration file:

SET allow_suspicious_low_cardinality_types = 1;

CREATE TABLE users (
    id LowCardinality(Int32)
)
ENGINE = MergeTree
PRIMARY KEY (id)
ORDER BY (id);
info

Session-level settings using SET apply to all statements that follow in the same migration file.

Additional Resources​