Help Center/ MapReduce Service/ Component Operation Guide (LTS) (Ankara Region)/ Using ClickHouse/ Common ClickHouse SQL Syntax/ ALTER TABLE: Modifying a Table Schema
Updated on 2024-11-29 GMT+08:00
View PDF
Share

ALTER TABLE: Modifying a Table Schema

This section describes the basic syntax and usage of the SQL statement for modifying a table schema in ClickHouse.

Basic Syntax

ALTER TABLE [database_name].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...

ALTER supports only *MergeTree, Merge, and Distributed engine tables.

The ALTER operation is executed asynchronously between replicas. To modify the result return policy, change the value of profiles.default.replication_alter_partitions_sync to:

  • 0: asynchronous execution
  • 1: waiting until the execution on the current server is complete
  • 2: waiting until all replicas (if any) are executed

When this parameter is set to 2, the timeout interval can be specified by modifying profiles.default.replication_wait_for_inactive_replica_timeout.

To modify the parameter, do the following:

Log in to FusionInsight Manager and choose Cluster > Services > ClickHouse. Click Configurations then All Configurations, search for replication_alter_partitions_sync in the search box in the upper right corner, change the value of profiles.default.replication_wait_for_inactive_replica_timeout, and save the configuration.

Example

  • Add the test01 column to the t1 table. 
    ALTER TABLE t1 ADD COLUMN test01 String DEFAULT 'defaultvalue';
  • Query the modified table t1. 
    desc t1
┌─name────┬─type─┬─default_type─┬─default_expression ┬─comment─┬─codec_expression─┬─ttl_expression─┐
│  id          │ UInt8  │                │                     │           │                    │                  │  
│  name        │ String │                │                     │           │                    │                  │ 
│  address     │ String │                │                     │           │                    │                  │
│  test01      │ String │  DEFAULT       │  'defaultvalue'     │           │                    │                  │
└───────┴────┴────────┴────────── ┴───── ┴──────────┴─────────┘
  • Change the type of the name column in the t1 table to UInt8. 
    ALTER TABLE t1 MODIFY COLUMN name UInt8;
  • Query the modified table t1. 
    desc t1
┌─name────┬─type─┬─default_type─┬─default_expression ┬─comment─┬─codec_expression─┬─ttl_expression─┐
│  id          │ UInt8  │                │                     │           │                    │                  │  
│  name        │ UInt8  │                │                     │           │                    │                  │ 
│  address     │ String │                │                     │           │                    │                  │
│  test01      │ String │  DEFAULT       │  'defaultvalue'     │           │                    │                  │
└───────┴────┴────────┴────────── ┴───── ┴──────────┴─────────┘
  • Delete the test01 column from the t1 table. 
    ALTER TABLE t1 DROP COLUMN test01;
  • Query the modified table t1. 
    desc t1
┌─name────┬─type─┬─default_type─┬─default_expression ┬─comment─┬─codec_expression─┬─ttl_expression─┐
│  id          │ UInt8  │                │                     │           │                    │                  │  
│  name        │ UInt8  │                │                     │           │                    │                  │ 
│  address     │ String │                │                     │           │                    │                  │
└───────┴────┴────────┴────────── ┴───── ┴──────────┴─────────┘