Kanishga Subramani

Posted on Sep 23

Design a ClickHouse® Table Without Hand-Writing DDL: Schema Studio

#clickhouse #devops #analytics #database

Designing a ClickHouse® table involves more than defining columns. Data types, sorting keys, partitioning, MergeTree configuration, indexes, and other settings can influence how the table performs as data grows.

Writing the complete DDL manually gives you control, but it also means translating each design decision into the correct ClickHouse® syntax.

Schema Studio simplifies this process by providing a guided workflow for designing and creating ClickHouse® tables while keeping the generated SQL visible and editable.

What is CH-Ops?

CH-Ops is a browser-based operations platform for ClickHouse®. It brings common database operations into a single web interface, including SQL querying, cluster monitoring, user management, backups, alerts, dashboards, and other operational workflows.

Alongside these operational capabilities, CH-Ops includes Schema Studio, a SQL tool designed to simplify the process of building ClickHouse® table definitions from source data.

Introducing Schema Studio in CH-Ops

Schema Studio is a guided table-design feature within CH-Ops.

Starting with your source data, it helps:

Infer and refine the schema
Configure ClickHouse®-specific table settings
Generate and validate the DDL
Optionally review the design with AI
Create the table

Importantly, Schema Studio creates the table structure only. The source data is used for schema inference and is not loaded into the newly created table.

You can access it under:

SQL Tools → Schema Studio

Building a ClickHouse® Table with Schema Studio

Schema Studio organizes the table-design process into four core stages.

Each stage focuses on a specific part of the design, starting with understanding the source data and ending with a validated table definition ready to be created in ClickHouse®.

1. Start with Your Source Data

Schema Studio first connects to the ClickHouse® instance selected in CH-Ops.

Enter the ClickHouse® username and password to establish the session.

Once connected, choose the data that will be used for schema inference.

You can upload a local file in formats such as:

CSV
TSV
JSON
NDJSON/JSONL
Parquet
ORC

You can also configure an object-storage source such as:

S3
Azure

2. Review and Shape the Schema

After analyzing the source, Schema Studio displays the inferred columns and corresponding ClickHouse® data types.

It also shows:

Approximate distinct values
Null percentages

This helps you review the structure before moving to table configuration.

The inferred schema remains editable.

You can:

Modify column names
Change data types
Add derived columns using DEFAULT, MATERIALIZED, ALIAS, or EPHEMERAL
Configure additional column options such as codecs and comments

Note: Schema inference may produce nullable types such as Nullable(Int64). Review the inferred types before configuring ORDER BY, and adjust the type if a nullable column needs to be used as the sorting key.

3. Define the ClickHouse® Table Design

The Engine step is where you configure how the table will be structured in ClickHouse®.

You can select the database and table name, choose the required MergeTree behavior, and define key table clauses such as:

ORDER BY
PRIMARY KEY
PARTITION BY
SAMPLE BY
TTL

For more advanced designs, you can also configure:

Data-skipping indexes
Projections
Replication
Distributed tables
Frequently filtered columns
Additional MergeTree settings

Note: For MergeTree tables, configure an ORDER BY or PRIMARY KEY before generating the DDL. If no sorting key is required, use tuple() for ORDER BY. Schema Studio flags a missing key in the Generate step.

Engine Configuration

The Engine Configuration step allows you to configure the MergeTree engine and key table clauses.

Advanced Configuration

Advanced Configuration allows you to add options such as:

Indexes
Projections
Replication
Distributed tables

4. Generate and Validate the DDL

After the table design is configured, Schema Studio generates the corresponding ClickHouse® CREATE TABLE statement in an editable SQL editor.

You can review or modify the generated SQL.

Use Validate to check the DDL before creating the table.

If you want to discard editor changes, Rebuild from Form regenerates the DDL using the configuration from the previous steps.

This keeps the generated SQL visible and gives you the ability to make manual adjustments before creating the table.

5. Review the Design with AI

Schema Studio also provides Evaluate with AI for an optional review using the supported AI provider configured in CH-Ops.

The evaluation can identify potential improvements related to:

Data types
Nullability
LowCardinality
Sorting keys
Partitioning
Codecs
Other table-design choices

Recommendations are presented for review and are not applied automatically.

When a recommendation is useful, Apply to Editor places the suggested DDL into the editor for further review and validation.

This provides an additional review step while keeping the final decision with the user.

6. Create the Table

After the DDL has been reviewed and validated, select Create Table.

Schema Studio displays a confirmation before executing the CREATE TABLE statement on the connected ClickHouse® instance.

Only the table structure is created.

The source data used for schema inference is not loaded into the table.

After creation, open the table in the SQL Editor to verify that it was created successfully and review its schema and configuration.

Why Schema Studio Matters

The value of Schema Studio is not simply that it generates a CREATE TABLE statement.

It brings the decisions involved in ClickHouse® table design into one workflow while keeping those decisions visible to the user.

Schema inference provides a starting point.

The Engine step exposes ClickHouse®-specific configuration.

Validation checks the generated DDL.

AI-assisted review provides an additional opportunity to refine the design before creation.

This reduces the repetitive work of translating a table design into SQL while keeping the final DDL available for inspection and modification.

Conclusion

Designing a ClickHouse® table requires decisions that can affect query performance, storage, and how the table behaves as data grows.

Schema Studio brings those decisions together in a guided workflow, from understanding the source data to creating the final table.

By combining:

Schema inference
ClickHouse®-specific configuration
DDL generation
Validation
Optional AI-assisted review

Schema Studio provides a more structured path to table creation without hiding the SQL or taking control away from the user.

DEV Community