第10章 ClickHouse常用命令

一、分区操作

1. 查看所有分区

SELECT database, table, partition, name, active FROM system.parts WHERE table = 'table_name'

2. 删除分区命令

ALTER TABLE tb_name DROP PARTITION partition_expr

3. 分区查询

SELECT * FROM partition_name WHERE record_time = '2020-06-17';

4. 查询分区信息

SELECT partition_id,name,table,database FROM system.parts where table = 'partition_name';

5. 卸载分区

ALTER TABLE tb_name DETACH PARTITION partition_expr;
alter table tb_nama detach partition '202006'; 

6. 装载分区

ALTER TABLE partition_v2 ATTACH PARTITION '202006';

二、表操作

1. 建表

略，根据引擎不同语法不同。

2. 删除表

DROP TABLE [IF EXISTS] [db_name.]tb_name;

3. 复制表结构

CREATE TABLE [IF NOT EXISTS] [db_name.]new_tb AS [db_name2.]old_tb [ENGINE = engine]

4. 复制表结构和数据

CREATE TABLE IF NOT EXISTS [db_name.]new_tb ENGINE = engine AS SELECT .....

5. 移动表、重命名

RENAME TABLE [db_name11.]tb_name11 TO [db_name12.]tb_name12, [db_name21.]tb_name21 TO [db_name22.]tb_name

6. 清空

TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name

7. insert

INSERT INTO [db.]table [(c1,c2,c3...)] values (v11,v12,v13...),(v21,v22,v23...),.....
insert into partition_v2 values ('a0014',toString(1+2),now());

INSERT INTO [db.]table [(c1,c2,c3...)] FORMAT format_name data_set;
INSERT INTO partition_v2 FORMAT CSV \
'A0017','url1','2020-06-01'\
'A0018','url2','2020-06-03'\

INSERT INTO [db.]table [(c1,c2,c3...)] SELECT * FROM partition_v1 

三、库操作

1. 建库

CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]

2. 删库

DROP DATABASE [IF EXISTS] db_name;

四、字段操作

1. 删除字段

ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name

N、库表指标

1. 查看库表容量，压缩率

select database,
　　table,
　　sum(bytes) as size,
　　sum(rows) as rows,
　　min(min_date) as min_date,
　　max(max_date) as max_date,
　　sum(bytes_on_disk) as bytes_on_disk,
　　sum(data_uncompressed_bytes) as data_uncompressed_bytes,
　　sum(data_compressed_bytes) as data_compressed_bytes,
　　(data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
　　max_date - min_date as days,
　　size / (max_date - min_date) as avgDaySize
from system.parts
where active
　　and database = 'db_name'
　　and table = 'table_name'
　　group by database, table

2. 查看各库表指标(字节显示)：大小，行数，日期，落盘数据大小，压缩前，压缩后大小

select database,
　　table,
　　sum(bytes) as size,
　　sum(rows) as rows,
　　min(min_date) as min_date,
　　max(max_date) as max_date,
　　sum(bytes_on_disk) as bytes_on_disk,
　　sum(data_uncompressed_bytes) as data_uncompressed_bytes,
　　sum(data_compressed_bytes) as data_compressed_bytes,
　　(data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
　　max_date - min_date as days,
　　size / (max_date - min_date) as avgDaySize
from system.parts
where active
　　and database = 'db_name'
　　and table = 'table_name'
　　group by database, table

3. 查看各库表指标(GB显示)：大小，行数，日期，落盘数据大小，压缩前，压缩后大小

select
　　database,
　　table,
　　formatReadableSize(size) as size,
　　formatReadableSize(bytes_on_disk) as bytes_on_disk,
　　formatReadableSize(data_uncompressed_bytes) as data_uncompressed_bytes,
　　formatReadableSize(data_compressed_bytes) as data_compressed_bytes,
　　compress_rate,
　　rows,
　　days,
　　formatReadableSize(avgDaySize) as avgDaySize
from
　(
　　　select
　　　　　　database,
　　　　　　table,
　　　　　　sum(bytes) as size,
　　　　　　sum(rows) as rows,
　　　　　　min(min_date) as min_date,
　　　　　　max(max_date) as max_date,
　　　　　　sum(bytes_on_disk) as bytes_on_disk,
　　　　　　sum(data_uncompressed_bytes) as data_uncompressed_bytes,
　　　　　　sum(data_compressed_bytes) as data_compressed_bytes,
　　　　　　(data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
　　　　　　max_date - min_date as days,
　　　　　　size / (max_date - min_date) as avgDaySize
　　　　from system.parts
　　　　where active 
　　　　　　and database = 'db_name'
　　　　　　and table = 'tb_name'
　　　　group by
　　　　　　database,
　　　　　　table
)

4. 查看表中数据大小

SELECT column,
　　any(type),
　　sum(column_data_compressed_bytes) AS compressed,
　　sum(column_data_uncompressed_bytes) AS uncompressed,
　　sum(rows)
FROM system.parts_columns
WHERE database = 'db_name'
　　and table = 'table_name'
　　AND active
GROUP BY column
ORDER BY column ASC