时序数据库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
![](https://img2022.cnblogs.com/blog/1409126/202211/1409126-20221110204156602-1268130353.png)
查看数据表分区信息
![](https://img2022.cnblogs.com/blog/1409126/202211/1409126-20221110204525256-2831867.png)
查看数据表字段的信息
![](https://img2022.cnblogs.com/blog/1409126/202211/1409126-20221110204514657-1311175207.png)
参考文档:
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