clickhouse查看数据库及表空间大小语句
1、查看数据库容量、行数、压缩率
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;

注意在clickhouse-client客户端执行不能有换行

2、查看数据表容量、行数、压缩率
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 ('t_order')
GROUP BY
	TABLE
3、查看数据表分区信息
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 ('dd_order'))
	AND (TABLE IN ('t_order'))
	AND (PARTITION LIKE '2019-12-%')
GROUP BY
	PARTITION
ORDER BY
	PARTITION ASC
4、查看数据表字段的信息
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 = 'dd_order')
	AND (TABLE = 't_order')
GROUP BY
	COLUMN
ORDER BY
	COLUMN ASC
赞赏(Donation)
微信(Wechat Pay)

donation-wechatpay

相关博客