clickhouse 数据库 表 列（DDL）

注意事项

1）只有MergeTree引擎才支持对列的修改操作
2）只有MergeTree引擎才支持数据的更新和删除
2）只有MergeTree引擎才支持分区表

表操作

创建表

create table new_table as old_table;

显示视图

Show Views;
SHOW VIEWS 'test_*'; -- show all views that start with "test_"
SHOW VIEWS FROM test1; -- show views from database test1

显示物化视图

SHOW MATERIALIZED VIEWS [FROM database_name];

查看表的类型

SHOW CREATE TABLE XXX;

查看表的字段

DESC XXXX;

表重命名、移动表位置（库与库之间）

-- 修改表名
rename table test_dml to test_tbl_dml;

-- 将表从a库移动到b库，并修改表名

rename table default.test_tbl_dml to learn_db.new_tbl_dml;

列操作

添加列

-- 原有列的末尾添加新列
alter table test_dml add column age UInt8;

-- 指定列的后添加新列
alter table test_dml add column gender String after name;

删除列

alter table test_dml drop column  age;

修改列的数据类型

-- 修改列的数据类型，并给默认值0
alter table test_dml modify column gender UInt8 default 0;

修改或添加字段注释

alter table test_dml comment  column name  'name的备注';

给列增加TTL

ALTER TABLE tbl_order_activity
    MODIFY COLUMN
    activty_notice String TTL dt + INTERVAL 10 SECOND;

创建表时，给某列指定TTL

drop table t_order_mt3;
create table t_order_mt3
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2) TTL create_time + interval 30 SECOND,
    create_time  Datetime
) engine = MergeTree
      partition by toYYYYMMDD(create_time)
      primary key (id)
      order by (id, sku_id);


insert into t_order_mt3 values (106, 'sku_001', 1000.00, '2020-06-12 22:52:30'),
                               (106, 'sku_001', 123.00, now());

select *
from t_order_mt3;

单机测试，想要看到效果，则需要做以下步骤1、2

1）重启服务

sudo clickhouse restart

2）手动合并分区

OPTIMIZE TABLE t_order_mt3 FINAL;

3）查询数据

select *
from t_order_mt3

数据更新和删除

只有MergeTree擎才支持数据的更新和删除

create table tb_update
(
    id   UInt32,
    name String,
    age  String
) engine = MergeTree
      order by id
;


insert into tb_update
values (1, 'zs', 'M'),
       (2, 'ls', 'F');
-- 更新
alter table t1 update name='李四' where id=2;
-- 删除
alter table t1 delete where id = 2;

虽然ck支持数据更新和删除，但是比较重，每次修改或者删除，都会导致放弃原有分区，重新建分区

优化：可以设计一些版本字段，定期对老数据进行删除

create table A
(
  axxx,
  bxxx,
  cxxx,
  _signUInt8,
  _versionUInt32
);

==> 更新：插入一条新的数据，_version + 1
	查询的时候加上一个过滤条件，where version 最大
==> 删除：_sign，0 表示未删除，1 表示已删除，同时version + 1
	查询的时候加上一个过滤条件，where_sign = 0 and version 最大
==> 时间久了，数据膨胀了==》 类似合并机制，怎么把过期数据清除掉