ClickHouse 命令汇总

连接clickhouse

mysql -h 127.0.0.1 -P 9004 -u user_name -ppassword
clickhouse-client -uuser_name --password password --port 9000 -h 127.0.0.1


安装clickhouse-client

  1. 访问:https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/
  2. 下载clickhouse-common-static、clickhouse-client 文件进行安装,可选择20.5.4.40版本
  3. 上传文件到服务器
  4. 执行命令


rpm -ivh clickhouse-common-static-20.5.4.40-2.x86_64.rpm
rpm -ivh clickhouse-client-20.5.4.40-2.noarch.rpm

创建表

CREATE TABLE part_v1 (ID String,URL String,EventTime Date)    
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(EventTime)
ORDER BY (ID, intHash32(ID))
TTL EventTime + toIntervalDay(19)
SAMPLE BY intHash32(ID);
复制代码

SAMPLE BY 采样,采样键必须在主键中也要定义

插入数据

INSERT INTO db.table VALUES (1, 1, '2020-08-11', 1)

修改数据,不能改主键、分区键

ALTER table db.table update age = 12 where ID in (select ID from test.part_v2 where EventTime='2019-01-01')

只有mergetree、merge、distributed 等支持alter

增加字段

ALTER table db.table add column if not exists age UInt8 default 0 AFTER URL

查看分区信息

select table,partition,path from system.parts where table='part_v1'

删除分区

ALTER table db.table DROP PARTITION field_date_1;

复制分区数据

ALTER TABLE db.table REPLACE PARTITION 1 FROM 2

删除数据,后台异步执行

删除的任务会保存到mutations表中,id_done=1表示执行完毕

ALTER table db.table delete where id >=1;
select database, table, mutation_id,block_numbers.number as num,is_done from system.mutations

修改字段类型

ALTER TABLE db.table MODIFY COLUMN age String

修改备注

ALTER TABLE db.table COMMENT COLUMN ID '主键ID'

删除字段

ALTER TABLE db.table DROP COLUMN age

查看zk存储的数据

select * from system.zookeeper where path='/clickhouse/tables/2-2/test_replication/replicas/test_replication_1';

查看表结构

DESC db.table
SHOW CREATE TABLE db.table

重命名表

RENAME TABLE db.table to db.newtable

删除表

TRUNCATE TABLE db.table

查看字典


select name,type,key,attribute.names,attribute.types from system.dictionaries

仅支持MergeTree引擎。会进行ttl清理、分区合并等

optimize table test.ttl_table_v1;

可以修改ttl,新增ttl,但不能取消ttl

alter table test.ttl_table_v1 modify column code String TTL create_time + INTERVAL 1 DAY;

ttl启停止

SYSTEM START TTL MERGES;​​​ ​​SYSTEM STOP TTL MERGES;

Buffer表,满足指定条件会将buffer表中数据刷到指定表

如果写入Mergetree并发很高,可能会导致Mergetree表的合并速度慢于写入速度,引入Buffer表来缓解这类问题

create table test.buffer_to_memory_1 as test.memory_1 engine = Buffer(test, memory_1, 16, 10 ,100, 1000, 1000000, 10000000, 100000000);

query_log

select * from system.query_log order by event_time desc limit 10;

trace_log

select * from system.trace_log where query_id = '29786b1e-9380-4373-90b8-9fc68ef89441';

查询正在进行执行的sql操作

SHOW PROCESSLIST

从mysql中导入数据

insert into test.test_order select * from mysql('127.0.0.1:3307','test','test_order','root','123456');

argMax 取最大值,day最大的那行数据的id

select argMax(day, id) from test.daily3;

json格式输出字段

select batch_id,concat('[',toString(groupUniqArray(id)),']')json from coupon_day_local group by batch_id;

格式化返回结果

SELECT * FROM test FORMAT JSON SELECT * FROM test FORMAT CSV SELECT * FROM test FORMAT TSKV SELECT * FROM test FORMAT XML