Designing ClickHouse table structure

NineData provides a visual table structure design solution. User can click the mouse in the SQL console to complete the table structure design without manually entering SQL commands.

Prerequisite

• Database is ClickHouse.

• In Organizational mode, user has the SQL console function module authority.

• In Organizational mode, user has read and write permissions to the target data source.

  ::tip

  If user do not have the above permissions, please contact organization's system administrator to Configure permissions or Bind roles.

  :::

• In the commercial versions (DevOps Pro, DevOps Enterprise), please ensure that your monthly/yearly subscription is not expired, as it may result in the inability to use the Database DevOpsservice. You can quickly check the remaining quota and expiration date at the top right corner of the NineData console page.

  

Steps

User can design the table structure when creating or editing a table through the SQL console. This article takes creating a table as an example to demonstrate.

1. Open the SQL console and go to the target data source.

2. In the database list on the left side of the SQL console, right-click the target database and click Create Table.

   

   tip

   The data table will be created in the target database.

3. On the Create Table tab, user can start to visually design the table structure, and the system will automatically generate SQL Script at the bottom of the page according to the configuration. For details of the variable structure design page, please select the corresponding tab below.

   

Click to expand the full visual parameter description of the table structure.

Common

• **Name**: The name of the custom table.
• **Database**: Select the database under which the current table needs to be created. The default is the database right-clicked in step 2.
• **Comment**: Comments for the table, that is, the COMMENT attribute.
• **Table Engine**: Select the storage engine for the table.
• **Local Table** (Displayed only when the table engine is Distributed): Select a local table to create a distributed table for.
• **Distributed Parameters** (Displayed only when the table engine is Distributed): The parameters of the distributed table.
• **Engine Parameters**: Table engine corresponding to the engine parameter.
• On Cluster: Select the cluster to which the current ClickHouse database belongs, the current form and all later action on the table will take effect in all databases of the cluster. And **Create Distributed Table** will be selected by default. This option will specify the create table option: Engine = Distributed table.

Columns

Set columns.

• **Columns**: In the leftmost text box, enter the column name.The first line of the current interface is default to the main column, the column name is default to id.
• **Data Type**: The second text box on the left. Click the text box to select the type of data column.
• NOT NULL: Determines whether this column allows empty values.The first main column in the current interface. This field is mandatory.
• **Default**: Set the default value of the current column, i.e. DEFAULT properties.This default value is used when no other value is inserted in this column.
• **Comment**: Comments for the column, i.e. the COMMENT attribute. Set column settings with annotations.Maximum length is 1024 characters.
• PRIMARY KEY: Sets whether the current column is the main column, either by selecting PRIMARY KEY left or by clicking on the magnification icon on the left of the listing, Multiple columns can serve as the main key column and form the composite primary key.
• **Compression and Encoding**: Select compression method for column data.

Indexes

Set indexes.

• **Name**: Enter index name.
• **Index Type**: Select the type of index to add.
• **Column or Expression**: Select column names that need to be added to the index.
• Granularity: Indexed particles, i.e. an index per number of rows, default is 8192.

Key Config

Set keys.

• Primary Key: Select one or more columns as primary Keys and all columns set to primary Key will be automatically set to Sorting Key.
• Order By: Select one or more columns as Sorting Key.
• Sample By: sets the target column to the sample field and only supports the selection of columns set to primary key.

Projection

Create a projection.

• **Name**: Enter the name of the projection.
• **Expression**: Enter an expression for the projection.e.g.: SELECT * ORDER BY user_name

:

currently only supports creating projections when creating tables.

Constraint

Set constraints.

• **Name**: Input constraint name.
• **Expression**: input constraint expression. e.g.: age >= 20.

:

Currently only supports setting constraints when creating tables.

Partitioning

If the size of database is too large, it can be partitioned by column to improve performance.

• PARTITION BY: Select a column or customize an expression to partition.

Table Options

Set table level options.

• TTL: The Time-to-live value of the table.
• **Other Options**: Add other table options based on demand.

Distributed table configuration

Set the distributed table option, only displayed when Create Distributed Table is selected.

• **Distributed table name**: Customize the name of the distributed table.
• **Distributed Parameters**: Enter the parameters of the distributed table.

4. Click Save and click OK in the popup Submit window.

   tip

   SQL script can be used to perform in other database clients.