一、分区操作
- 查看所有分区
SELECT database, table, partition, name, active FROM system.parts WHERE table = 'table_name'
- 删除分区命令
ALTER TABLE tb_name DROP PARTITION partition_expr
- 分区查询
SELECT * FROM partition_name WHERE record_time = '2020-06-17';
- 查询分区信息
SELECT partition_id,name,table,database FROM system.parts where table = 'partition_name';
- 卸载分区
ALTER TABLE tb_name DETACH PARTITION partition_expr;
alter table tb_nama detach partition '202006';
- 装载分区
ALTER TABLE partition_v2 ATTACH PARTITION '202006';
二、表操作
- 建表
略，根据引擎不同语法不同。
- 删除表
DROP TABLE [IF EXISTS] [db_name.]tb_name;
- 复制表结构
CREATE TABLE [IF NOT EXISTS] [db_name.]new_tb AS [db_name2.]old_tb [ENGINE = engine]
- 复制表结构和数据
CREATE TABLE IF NOT EXISTS [db_name.]new_tb ENGINE = engine AS SELECT .....
- 移动表、重命名
RENAME TABLE [db_name11.]tb_name11 TO [db_name12.]tb_name12, [db_name21.]tb_name21 TO [db_name22.]tb_name
- 清空
TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name
- 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
三、库操作
- 建库
CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]
- 删库
DROP DATABASE [IF EXISTS] db_name;
四、字段操作
- 删除字段
ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name
N、库表指标
- 查看库表容量，压缩率
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
- 查看各库表指标(字节显示)：大小，行数，日期，落盘数据大小，压缩前，压缩后大小
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
- 查看各库表指标(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
)
- 查看表中数据大小
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
