时序数据库clickhouse_数据库操作

一：新增列

ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [codec] [AFTER name_after]

ALTER TABLE visits ON CLUSTER cluster_name ADD COLUMN column_name1, ADD COLUMN column_name2;

ALTER TABLE 表名称 ON 集群名称 ADD COLUMN 列名称 类型 DEFAULT 注解

eg：ALTER TABLE 表名称 ON 集群名称 ADD COLUMN lvl Int32 DEFAULT 'lvl'

 

二：删除列

eg：alter table snapshot_all（表名） drop column  static_pe_rate（列名）;

 

三：修改列数据类型

alter table order2（表名） modify column entr_px_decemail64（列名） Decimal(18,5);

 

四：CK中修改注释

alter table 表名称 ON 集群名称 COMMENT COLUMN 列名称 注解;
eg：alter table adm_sch_d05_content_live_service_di_local ON 集群名称 COMMENT COLUMN `pv_sid` 'lvl';

 

注意问题：
不可以修改列名称，CK中不支持修改列名称。
hive表中的日期为String类型到CK中不可以转为Date,会报错。必须为Sting.

 

五：清表

truncate trade1 （表名）;

六：查看表大小

查看数据库容量、行数、压缩率

SELECT 
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts

查看数据表容量、行数、压缩率

SELECT 
    table AS `表名`,
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts
WHERE table IN ('temp_1')
GROUP BY table

 

 

查看数据表分区信息

--查看测试表在19年12月的分区信息

SELECT partition AS `分区`, sum(rows) AS `总行数`, formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`, formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`, round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率` FROM system.parts WHERE (database IN ('default')) AND (table IN ('temp_1')) AND (partition LIKE '2019-12-%') GROUP BY partition ORDER BY partition ASC

 

 

查看数据表字段的信息

SELECT column AS `字段名`, any(type) AS `类型`, formatReadableSize(sum(column_data_uncompressed_bytes)) AS `原始大小`, formatReadableSize(sum(column_data_compressed_bytes)) AS `压缩大小`, sum(rows) AS `行数` FROM system.parts_columns WHERE (database = 'default') AND (table = 'temp_1') GROUP BY column ORDER BY column ASC

 

 

参考文档：

1、Clickhouse修改字段类型参考文档：

1）https://blog.51cto.com/u_7544687/4714956

2）https://blog.csdn.net/ganghaodream/article/details/111085006?spm=1001.2100.3001.7377&utm_medium=distribute.pc_feed_blog_category.none-task-blog-classify_tag-10.nonecase&depth_1-utm_source=distribute.pc_feed_blog_category.none-task-blog-classify_tag-10.nonecase

2、clickhouse官方文档：https://clickhouse.com/docs/zh/sql-reference/statements/alter/column

3、ck数据表大小：https://juejin.cn/post/6897042422761521159