Clickhouse

描述

[]:非必选

{table}:表名

cluster:分布式

on cluster cluster_1shards_1replicas

删除表 sync

DROP TABLE {table} [{cluster}] sync;

添加列

#如果指定AFTER name_after（表中另一个列的名称），则新的列会加在指定列的后面。否则，新的列将被添加到表的末尾
ALTER TABLE {table} [{cluster}] ADD COLUMN user_name String DEFAULT '' COMMENT '用户名' [AFTER 字段];

修改列

ALTER TABLE {table} [{cluster}] MODIFY COLUMN user_name [String] [DEFAULT NULL] [COMMENT ''];

删除列

ALTER TABLE {table} [{cluster}] DROP COLUMN user_name;

导入\导出CSV

# 导入不带表头
clickhouse-client --host=192.168.1.1 --port=9000 -udefault --password="123456" --query="insert into {table} FORMAT CSV"  test.csv
# 导出带表头
clickhouse-client -h 127.0.0.1 -u dbadba --password --database lodi_super_ck --query  "select * from {table}  format CSVWithNames" > test.csv

# 导入指定制表符
clickhouse-client --host=192.168.1.1 --port=9000 -udefault --password="123456" --query="insert into {table} FORMAT CSVWithNames" --format_csv_delimiter=','   test.csv

注意：如果执行语句后面不加FORMAT CSV或FORMAT CSVWithNames，默认是\t作为分隔符。只有指定FORMAT CSV或FORMAT CSVWithNames后，指定--format_csv_delimiter才生效

建表/分布式

CREATE TABLE {table} [{cluster}]
(
    `id` Int64,
    `tid` UInt8 DEFAULT '1'  COMMENT 'tid',
    `OCode` String COMMENT '',
    `Username` Nullable(String) DEFAULT NULL  COMMENT '账户',
    `Date` DateTime('Asia/Shanghai') COMMENT '时间',
    `startTime` Nullable(DateTime('Asia/Shanghai')) COMMENT '开始时间'
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/表名', '{replica}')
PARTITION BY toYYYYMMDD(Date)
ORDER BY (tid, Date, OCode)
SETTINGS index_granularity = 8192, min_compress_block_size = 24, min_bytes_for_wide_part = 0;

CREATE TABLE {table} [{cluster}]
(
    `id` Int64,
    `tid` UInt8 DEFAULT '1'  COMMENT 'tid',
    `OCode` String COMMENT '',
    `Username` Nullable(String) DEFAULT NULL  COMMENT '账户',
    `Date` DateTime('Asia/Shanghai') COMMENT '时间',
    `startTime` Nullable(DateTime('Asia/Shanghai')) COMMENT '开始时间'
)
ENGINE = Distributed('cluster_1shards_1replicas', '库名', '表名', rand());

刷新配置文件

system reload config

合并分区

optimize table {表名} final;