连接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
创建表
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
删除数据，后台异步执行
ALTER table db.table delete where id >=1;
删除的任务会保存到mutations表中，id_done=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
