-- 创建表
create table temp (`EventDate` DateTime, `UserId` UInt32) engine = MergeTree() partition by toYYYYMM(EventDate) order by EventDate;

-- 创建分布式复制表
create table temp (`EventDate` DateTime, `UserId` UInt32) engine = ReplicatedMergeTree('/clickhouse/tables/{shard}/temp', '{replica}') partition by toYYYYMM(EventDate) order by EventDate;

-- 创建集群分布式表
-- 更多的引擎可以看文档
create table temp_all (`EventDate` DateTime, `UserId` UInt32) engine = Distributed('your_cluster', 'default', 'temp', UserId);

-- 物化视图
-- 数据会根据原始表更新
create materialized view temp_v (`EventDate` DateTime, `UserId` UInt32) engine = MergeTree() partition by toYYYYMM(EventDate) order by UserId populate as select * from temp;

-- 从 MySQL 中导入数据并建表
-- 注意要指定 order by
create table from_mysql ENGINE = MergeTree order by id as select * from mysql('127.0.0.1', 'db', 'table', 'user', 'password');

-- 可以先建好表，再从 MySQL 中导入
create table temp_test (`id` UInt32, `creaetd_at` UInt32) ENGINE = MergeTree order by id partition by toYYYYMM(toDateTime(creaetd_at));
insert into temp_test select id, created_at from mysql('127.0.0.1', 'db', 'table', 'user', 'password');

-- 从 ClickHouse 其他表导入数据
insert into temp_test select * from remote('127.0.0.1:9000', db, table);
insert into temp_test select * from remote('127.0.0.1:9000', db, table, 'user', 'password');

-- 删除/更新数据
-- 该命令是异步的，并不会马上执行
alter table tb_name delete where id > 0;
alter table tb_name update col1=val1, col2=val2 where id > 0;

-- 查看任务进度
select * from system.mutations;
kill mutation where mutation_id = 'trx_id';

-- 获取表的分区数据
select partition, name, active from system.parts where table = 'temp';

-- 分区操作
-- 详细的可以看文档
alter table test detach partition 202004 -- 卸载分区
alter table test attach partition 202004 -- 根据'分区键'挂载分区
alter table test_new attach partition 202004 from test -- 从其他表中挂载分区
alter table test attach part '202004_0_0_1' -- 根据'目录名'挂载分区

-- 查看表占用大小
select database, table, formatReadableSize(sum(bytes)) as size from system.parts group by database, table order by database, table;

-- 查看进程等
show processlist;
select query_id, user, address, elapsed, query from system.processes order by query_id;
kill query where query_id='query_id';

-- 查看连接数量
select * from system.metrics where metric like '%Connection%';

-- 查看集群信息
select * from system.clusters;

-- 优化表分区
optimize table test [PARTITION partition] [FINAL]

-- 系统配置
select * from system.settings;
set send_logs_level = 'debug'; -- 修改日志级别，如 trace|debug 等等
set insert_deduplicate = 0; -- 关闭重复数据自动删除，测试数据时关闭会比较好用

-- 创建测试数据
create table test_data(`key` Text, `context` Text, `uid` UInt32, `dt` DateTime) ENGINE = MergeTree order by dt;
insert into test_data select * from generateRandom('key UUID, context Text, uid UInt32, dt Datetime', null, 100) limit 100000000;

-- 语句查询禁止使用缓存
select count() from test_data where context like '%123%' settings min_bytes_to_use_direct_io=1;

-- 一些分词常用函数
select tokens('hello, world!');
select ngrams('hello, world!', 4);

insert into dst_table select * from src_table;
insert into dst_table select * from remote('127.0.0.1:9000', default, src_table);

alter table dst_table attach partition 202004 from src_table

cd /var/lib/clickhouse/data/default/temp
cp -R 201803_0_0_0 /dst/new_table/detached/

# 注意文件的权限
chown -r clickhouse:clickhouse /dst/new_table/detached/201803_0_0_0

# 挂载分区
# alter table new_table attach partition 201803;

set max_memory_usage = '40G';
set max_bytes_before_external_group_by = '20G';

set join_algorithm = 'auto';
set partial_merge_join_optimizations = 0; # 避免可能出现的错误