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, orEPHEMERAL - 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 configuringORDER 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 BYPRIMARY KEYPARTITION BYSAMPLE 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 BYorPRIMARY KEYbefore generating the DDL. If no sorting key is required, usetuple()forORDER 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.
References
- CH-Ops Installation Guide
- CH-Ops Official Repository
- CH-Ops Demo Video
Next in the CH-Ops Series
CH-Ops Schema Tools: Visualizer, Indexes & Projections
