clickhouse 十三（操作数据表）

布满荆棘的人生 2021-06-24 13:58 621阅读 0赞

> 注意：alter操作仅支持 \*MergeTree ，Merge以及Distributed等引擎表。本示例采用支持数据副本的MergeTree做测试表，alter语句操作数据的会在压缩的情况下被复制

测试表：

CREATE TABLE test.tmp_uid_info
    (
        `uid` Int32,
        `alias` Int32,
        `sex` String,
        `totalDate` Date,
        `source` String,
        `name` String
    )
    ENGINE = ReplicatedMergeTree('/clickhouse/test/tables/{shard}/tmp_uid_info', '{replica}')
    PARTITION BY totalDate
    ORDER BY uid
    SETTINGS index_granularity = 8192

**添加新字段**

#语法格式
    alter table tb_name add column [IF NOT EXISTS] name [type] [default_expr]
    [alter name_after]
    
    # 给测试表的末尾增加新字段.，对于数据表中已经存在旧数据⽽⾔，新追加的字段会使⽤默认值补全
    alter table tmp_uid_info on cluster ck_cluster add column age String default '0'

**删除已有字段**

# 语法格式
    alter table tb_name drop column [IF EXISTS] name
    
    # 删除测试表的age字段，注意字段被删除后，它的数据也会被连带删除
    alter table tmp_uid_info on cluster ck_cluster drop column age;

**修改字段类型**

# 语法格式
    alter table tmp_uid_info MODIFY COLUMN [IF EXISTS] name [type] [default_expr]
    # 修改uid的字段类型，由int32改成Int64，注意，使用String转成Int会报错
    alter table tmp_uid_info on cluster ck_cluster modify column uid Int64;

**修改字段名**

# 语法格式
    ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name
    # 修改uid的字段类型，由int32改成Int64，注意，使用String转成Int会报错
    alter table tmp_uid_info on cluster ck_cluster rename column name to username;

**给字段添加注释**

# 语法格式
    alter table tb_name comment column [IF EXISTS] name 'comment'
    
    # 给测试表的name字段添加注释
     alter table tmp_uid_info on cluster ck_cluster comment column name '姓名'
    
    # 即可查看到name的注释
    DESCRIBE TABLE tmp_uid_info
    
    ┌─name──────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
    │ uid       │ Int32  │              │                    │         │                  │                │
    │ alias     │ Int32  │              │                    │         │                  │                │
    │ sex       │ String │              │                    │         │                  │                │
    │ totalDate │ Date   │              │                    │         │                  │                │
    │ source    │ String │              │                    │         │                  │                │
    │ name      │ String │              │                    │ 姓名    │                  │                │
    │ age       │ String │ DEFAULT      │ '0'                │         │                  │                │
    └───────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

**清空数据表，只是表内的数据全部清空，⽽不是直接删除这张表**

# 语法格式
    truncate table [IF EXISTS] [db_name.]tb_name
    # 清空测试表数据
    truncate table test.tmp_uid_info on cluster ck_cluster

**通过system系统库的Parts表来查询分区信息**

SELECT 
        partition_id,
        name,
        table,
        database
    FROM system.parts
    WHERE (table = 'tmp_uid_info') AND (database = 'test')
    
    ┌─partition_id─┬─name─────────────┬─table────────┬─database─┐
    │ 20101027     │ 20101027_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20121027     │ 20121027_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20131127     │ 20131127_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20141127     │ 20141127_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20161027     │ 20161027_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20161127     │ 20161127_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20171127     │ 20171127_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20180127     │ 20180127_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20181027     │ 20181027_2_2_0_4 │ tmp_uid_info │ test     │
    │ 20181227     │ 20181227_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20190127     │ 20190127_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20190227     │ 20190227_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20191027     │ 20191027_2_2_0_4 │ tmp_uid_info │ test     │
    │ 20191127     │ 20191127_0_0_0_2 │ tmp_uid_info │ test     │
    │ 20201027     │ 20201027_0_0_0_2 │ tmp_uid_info │ test     │
    └──────────────┴──────────────────┴──────────────┴──────────┘

**根据条件删除测试表的数据：**

alter table test.tmp_uid_info on cluster ck_cluster delete
    where totalDate='2020-10-28'

**根据分区删除测试表的数据：**

# 根据分区键删除，测试表按日期分区
    alter table tmp_uid_info on cluster ck_cluster drop partition '2020-10-27'

**手动合并数据表的分区**

optimize table tmp_uid_info partition '2020-10-27' final