ClickHouse 常用语句
一、常用操作
1、建数据库
连接数据库：clickhouse-client -h 10.0.0.0 --port 9000 -u test_user --password test_password -m
可以通过jdbc方式访问，DBeaver软件选择ck引擎，8123端口。也可直接客户端方式访问ck
create database bdg_inf on cluster default_cluster;
2、建表分为本地数据表，和查询表。
方式1：推荐，根据主键合并
（1）本地local表-数据表
CREATE TABLE if not exists bdg_inf.dws_compass_v1_local ON CLUSTER production
(
dt String comment '日期分区',
event_id String comment '事件id',
biz_type Int64 comment '业务线',
client_type Int64 comment '客户端类型',
md5_log String comment '埋点唯一键',
event_type Int64 comment '事件类型 1:浏览 2:点击 3:播放 4:展示 5:长按 6:页面响应 7:时间消耗',
sdk_version String comment 'sdk版本',
distinct_id String comment '唯一id',
client_name String comment '客户端名称',
event_time Int64 comment '事件发生时间 通过event_timestamp进行格式转换得到'
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/bdg_inf.dws_compass_v1_local', '{replica}')
PARTITION BY (dt)
ORDER BY (event_id, md5_log)
TTL parseDateTimeBestEffort(dt) + toIntervalMonth(2)
SETTINGS index_granularity = 8192;（2）分布式表
CREATE TABLE if not exists bdg_inf.dws_compass_v1 ON CLUSTER production
(
dt String comment '日期分区',
event_id String comment '事件id',
biz_type Int64 comment '业务线',
client_type Int64 comment '客户端类型',
md5_log String comment '埋点唯一键',
event_type Int64 comment '事件类型 1:浏览 2:点击 3:播放 4:展示 5:长按 6:页面响应 7:时间消耗',
sdk_version String comment 'sdk版本',
distinct_id String comment '唯一id',
client_name String comment '客户端名称',
event_time Int64 comment '事件发生时间 通过event_timestamp进行格式转换得到'
)ENGINE = Distributed('production', 'bdg_inf', 'dws_compass_v1_local', javaHash(concat(event_id, dt)));（3）手动触发merge
OPTIMIZE TABLE bdg_inf.dws_compass_v1_local ON CLUSTER production PARTITION('20220616') FINAL;
方式2：可能重复
（1）、本地表-数据表，on cluster ch_cluster语法标识分布式DDL，即执行一次就可以在集群所有实例上创建同样的本地表。
！！！spark方式灌数据，stage失败时会多数据
CREATE TABLE if not exists bdg_inf.dws_compass_test_local ON CLUSTER default_cluster
(
`event_id` String COMMENT '事件id',
`biz_type` Int16 COMMENT '业务线',
`client_type` Int16 COMMENT '客户端类型',
`event_type` Int16 COMMENT '事件类型',
`sdk_version` String COMMENT 'sdk版本',
`distinct_id` String COMMENT '唯一id',
`client_name` String COMMENT '客户端名称',
`create_time` Int64 COMMENT '日志清洗时间',
`dt` String COMMENT '分区字段'
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/bdg_inf.dws_compass_test_local', '{replica}')
PARTITION BY (dt)
ORDER BY (event_id, sdk_version)
TTL parseDateTimeBestEffort(dt) + toIntervalMonth(3)
SETTINGS index_granularity = 8192（2） 查询表--分布式表，本身不存储数据
CREATE TABLE bdg_inf.dws_compass_test ON CLUSTER default_cluster
(
`event_id` String COMMENT '事件id',
`biz_type` Int16 COMMENT '业务线',
`client_type` Int16 COMMENT '客户端类型',
`event_type` Int16 COMMENT '事件类型',
`sdk_version` String COMMENT 'sdk版本',
`distinct_id` String COMMENT '唯一id',
`client_name` String COMMENT '客户端名称',
`create_time` Int64 COMMENT '日志清洗时间',
`dt` String COMMENT '分区字段'
)
ENGINE = Distributed('default_cluster', 'bdg_inf', 'dws_compass_test_local', rand())3、添加字段（只有MergeTree系列、Merge、Distributed表引擎支持alter操作）+++
alter table bdg_inf.dws_compass_test_local ON CLUSTER default_cluster add column if not exists score Float32 default 8.8 after create_time;
alter table bdg_inf.dws_compass_test ON CLUSTER default_cluster add column if not exists score Float32 default 8.8 after create_time;
4、删除字段+++
alter table bdg_inf.dws_compass_test_local ON CLUSTER default_cluster drop column if exists score;
alter table bdg_inf.dws_compass_test ON CLUSTER default_cluster drop column if exists score;
5、分布式分区表新增数据+++
insert into bdg_inf.dws_compass_test (event_id, biz_type , client_type , event_type,sdk_version,distinct_id,client_name,create_time,dt) values ('123123112233', 8, 3, 4,'2.4.1','bjhl297001001421b9e9','ios',1653480770280,20220525);
6、分布式表更新数据+++
alter table bdg_inf.dws_compass_test_local ON CLUSTER default_cluster update client_name = 'ios1' where event_id = '123123112233';
7、分布式表删除数据
ALTER TABLE bdg_inf.dws_compass_local on cluster production DELETE WHERE dt='20220612';
8、查看分区信息
select database, table, partition, partition_id, name, path from system.parts where database = 'bdg_inf' and table = 'dws_compass_test_local';
9、删除partition
alter table bdg_inf.dws_compass_local on cluster production drop partition ('20220616')
10、复制分区数据，做测试数据用
insert into bdg_inf.dws_compass_test (event_id, biz_type , client_type , event_type,sdk_version,distinct_id,client_name,create_time,dt)
select event_id, biz_type , client_type , event_type,sdk_version,distinct_id,client_name,create_time,toFixedString('20220523', 8) AS sss from bdg_inf.dws_compass_test where dt='20220526'
11、修改字段类型（修改前后的字段数据类型需要兼容）
alter table alter_table_test ON CLUSTER default_cluster modify column if exists score Float64 default 0.0;
12、添加或修改字段备注
alter table alter_table_test ON CLUSTER default_cluster comment column if exists score '分数';
13、重命名或移动表？？？
rename table default.alter_table_test to default.alter_table_rename_test ON CLUSTER default_cluster;
14、清空数据
truncate table if exists default.alter_table_rename_test ON CLUSTER default_cluster;
18、把某一列变为默认值
alter table partition_table_test clear column name in partition 'Shanghai';
19、卸载和装载partition
alter table partition_table_test detach partition 'Shanghai';
alter table partition_table_test attach partition 'Shanghai';
20、csv方式导入数据
clickhouse-client -h 1.1.1.12 -format_csv_delimiter=',' -q 'insert into test_db.aa_all FORMAT CSV ' < /root/aa_all_data.csv
21、txt方式导入数据
22、删除表
DROP TABLE IF EXISTS bdg_inf.dws_compass_analysis_dynamic ON CLUSTER production;
DROP TABLE IF EXISTS bdg_inf.dws_compass_analysis_dynamic_local ON CLUSTER production;
23、连接ck，并导出数据
clickhouse-client --password test_password -u test_user -m -h 10.224.0.0 --port 9000 --query "desc table1" > ./clickhouse_table_schema.tmp
24、测试建表语句
CREATE TABLE test.etl_city_manage_no_ka_profit (
`area_business_id` Int64,
`area_category_id` Int64,
`area_code` Int64,
`income_total` Float64,
`v` Int64
) ENGINE = Distributed(
stock,
test,
etl_city_manage_no_ka_profit_local,
area_code % 31
)
---------------------------------------------------
CREATE TABLE test.etl_city_manage_no_ka_profit_local (
`area_business_id` Int64,
`area_category_id` Int64,
`area_code` Int64,
`income_total` Float64,
`v` Int64
) ENGINE = ReplacingMergeTree(v)
ORDER BY
(area_business_id, area_category_id, area_code) SETTINGS index_granularity = 819225、修改表合并大小，4GB以上走wide，以下走Compact。默认是1GB。可以有效提高写入速度，降低merge数量。
默认情况下每列都是一个文件，如使用阿里存算分离ck的话会频繁调用cos接口获取数据文件，在此阶段非常费时间，调整该参数可以减少调cos接口数量，提高写入速度，降低merge数量
修改成4G才走wide方式
Alter table xxxx modify setting min_bytes_for_wide_part=4294967296;26、合并分区文件。有些旧分区不会自动合并，加上俩参数后可以两小时检查一次旧分区part进行合并。合并文件受max_bytes_to_merge_at_max_space_in_pool控制
Alter table bdg_inf.test_table modify setting min_age_to_force_merge_seconds=120;
Alter table bdg_inf.test_table modify setting min_age_to_force_merge_on_partition_only=true;
二、运维
1、查看集群情况
select * from system.clusters;
2、资源查看--查看每列存储大小
SELECT column,
any(type),
formatReadableSize(sum(column_data_compressed_bytes)) AS compressed,
formatReadableSize(sum(column_data_uncompressed_bytes)) AS uncompressed,
sum(rows)
FROM clusterAllReplicas('production', system.parts_columns)
WHERE database = 'bdg_inf'
and table = 'dws_compass_local'
AND active
GROUP BY column
ORDER BY column ASC
3、查看库表大小，行数，日期，压缩前，压缩后大小
--------------各分区大小
SELECT
database,
table,
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 clusterAllReplicas('production', system.parts)
WHERE database='bdg_inf' AND table='dws_compass_analysis_dynamic_local'
--AND partition='20220721'
GROUP BY database,table,partition
ORDER BY database,table,partition ASC
--------------表总大小
SELECT
database,
table,
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 clusterAllReplicas('production', system.parts)
WHERE database='bdg_inf' AND table='dws_compass_analysis_dynamic_local'
GROUP BY database,table
4、查看各库表指标(字节显示)：大小，行数，日期，落盘数据大小，压缩前，压缩后大小
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 = 'bdg_inf'
and table = 'dws_compass_test_local'
group by database, table
5、查看当前正在ddl操作的库表
SELECT
FQDN() AS node,
database,
table,
mutation_id,
create_time,
command,
is_done,
parts_to_do,
latest_fail_reason
FROM clusterAllReplicas('production', system.mutations)
WHERE is_done = 0;
清除正在ddl操作的库表
KILL MUTATION WHERE database = 'default' AND table = 'table_name'
5.1 查询zk中执行日志
SELECT * FROM system.zookeeper where path = '/clickhouse/task_queue/ddl' order by name desc limit 1000
5.2 查询历史执行sql
select
databases,
query_kind,
query,
formatDateTime(event_time,'%Y%m%d%H%M')t1,
query_duration_ms,*
from clusterAllReplicas('offline_production', `system`.query_log) where event_date ='2023-06-19'
--and event_time>='2022-10-10 12:28:00' and event_time<='2022-10-10 14:38:00'
and databases[1]='dw'
and query like '%tab1234%'
and query like '%DISTINCT user_number%'
and query_duration_ms!=0
limit 100
6、system.parts表schema
partition`（String）-- 分区名称。
`name`（String）-- 数据部分的名称。
`part_type`（String）-- 数据部分的存储格式。
`active`（UInt8）-- 指示数据部分是否处于活动状态的标志。如果数据部分处于活动状态，则会在表中使用它。否则，将其删除。合并后，不活动的数据部分仍然保留。
`marks`（UInt64）-- 标记数。要获得数据部分中的大约行数，请乘以marks索引粒度（通常为8192）（此提示不适用于自适应粒度）。
`rows`（UInt64）-- 行数。
`bytes_on_disk`（UInt64）-- 所有数据片段的总大小（以字节为单位）。
`data_compressed_bytes`（UInt64）-- 数据片段中压缩数据的总大小。不包括所有辅助文件（例如，带标记的文件）。
`data_uncompressed_bytes`（UInt64）-- 数据片段中未压缩数据的总大小。不包括所有辅助文件（例如，带标记的文件）。
`marks_bytes`（UInt64）-- 带标记的文件的大小。
`modification_time`（DateTime） --包含数据片段的目录被修改的时间。这通常对应于数据零件创建的时间。
`remove_time`（DateTime）-- 数据片段变为非活动状态的时间。
`refcount`（UInt32）-- 使用数据片段的位置数。大于2的值表示在查询或合并中使用了数据部分。
`min_date`（Date）-- 数据片段中日期键的最小值。
`max_date`（Date） -- 数据片段中日期键的最大值。
`min_time`（DateTime）-- 数据片段中日期和时间键的最小值。
`max_time`（DateTime）-- 数据片段中日期和时间键的最大值。
`partition_id`（String）-- 分区的ID。
`min_block_number`（UInt64）-- 合并后组成当前部分的数据片段的最小数量。
`max_block_number`（UInt64）-- 合并后组成当前部分的最大数据片段数。
`level`（UInt32）-- 合并树的深度。零表示当前零件是通过插入而不是通过合并其他零件来创建的。
`data_version`（UInt64）-- 用于确定应将哪些突变应用于数据部分（版本高于的突变data_version）的编号。
`primary_key_bytes_in_memory`（UInt64）-- 主键值使用的内存量（以字节为单位）。
`primary_key_bytes_in_memory_allocated`（UInt64）-- 为主键值保留的内存量（以字节为单位）。
`is_frozen`（UInt8）-- 显示分区数据备份存在的标志。1，备份存在。0，备份不存在。有关更多详细信息，请参见“冻结分区”。
`database`（String）-- 数据库的名称。
`table`（String）-- 表的名称。
`engine`（String）-- 不带参数的表引擎的名称。
`path`（字符串）-- 包含数据零件文件的文件夹的绝对路径。
`disk`（字符串）-- 存储数据部分的磁盘的名称。
`hash_of_all_files`（字符串）-- 压缩文件的sipHash128。
`hash_of_uncompressed_files`（String）-- 未压缩文件（带有标记的文件，索引文件等）的sipHash128。
`uncompressed_hash_of_compressed_files`（String）-- 压缩文件中的数据的sipHash128，就好像它们是未压缩的一样。
`delete_ttl_info_min`（DateTime）-- TTL DELETE规则的日期和时间键的最小值。
`delete_ttl_info_max`（DateTime）-- TTL DELETE规则的日期和时间键的最大值。
`move_ttl_info.expression`（Array（String））-- 表达式数组。每个表达式定义一个TTL MOVE规则。
`move_ttl_info.min`（Array（DateTime））-- 日期和时间值的数组。每个元素都描述了TTL MOVE规则的最小键值。
`move_ttl_info.max`（Array（DateTime））-- 日期和时间值的数组。每个元素都描述了TTL MOVE规则的最大键值。
`bytes`（UInt64）-- bytes_on_disk的别名。
`marks_size`（UInt64）-- marks_bytes的别名。7、查询列信息
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 = 'test') AND (table = 'label')
GROUP BY column
ORDER BY column ASC8、system.parts_columns表 schema
`partition`（String）-- 分区名称。
`name`（String）-- 数据片段的名称。
`part_type`（String）-- 数据片段的存储格式。
`active`（UInt8）-- 指示数据部分是否处于活动状态的标志。如果数据部分处于活动状态，则会在表中使用它。否则，将其删除。合并后，不活动的数据部分仍然保留。
`marks`（UInt64）-- 标记数。要获得数据部分中的大约行数，请乘以marks索引粒度（通常为8192）（此提示不适用于自适应粒度）。
`rows`（UInt64）-- 行数。
`bytes_on_disk`（UInt64）-- 所有数据部分文件的总大小（以字节为单位）。
`data_compressed_bytes`（UInt64）-- 数据部分中压缩数据的总大小。不包括所有辅助文件（例如，带标记的文件）。
`data_uncompressed_bytes` （UInt64） --数据部分中未压缩数据的总大小。不包括所有辅助文件 （例如，带标记的文件）。
`marks_bytes` （UInt64） --带有标记的文件的大小。
`modification_time` （DateTime） --包含数据部分的目录被修改的时间。这通常对应于数据零件创建的时间。
`remove_time` （DateTime） --数据部分变为非活动状态的时间。
`refcount` （UInt32） --使用数据部分的位置数。大于2的值表示在查询或合并中使用了数据部分。
`min_date` （Date） --数据部分中日期键的最小值。
`max_date` （Date） --数据部分中日期密钥的最大值。
`partition_id` （String） --分区的ID。
`min_block_number` （UInt64） --合并后组成当前部分的数据部分的最小数量。
`max_block_number` （UInt64） --合并后组成当前部分的最大数据部分数。
`level` （UInt32） --合并树的深度。零表示当前零件是通过插入而不是通过合并其他零件来创建的。
`data_version` （UInt64） --用于确定应将哪些突变应用于数据部分 （版本高于的突变data_version）的编号。
`primary_key_bytes_in_memory` （UInt64） --主键值使用的内存量 （以字节为单位）。
`primary_key_bytes_in_memory_allocated` （UInt64） --为主键值保留的内存量 （以字节为单位）。
`database` （String） --数据库名称。
`table` （String） --表的名称。
`engine` （String） --不带参数的表引擎的名称。
`disk_name` （String） --存储数据部分的磁盘的名称。
`path` （String） --包含数据零件文件的文件夹的绝对路径。
`column` （String） --列的名称。
`type` （String） --列类型。
`column_position` （UInt64） --以1开头的表中列的顺序位置。
`default_kind` （字符串） --表达类型 （DEFAULT，MATERIALIZED，ALIAS作为默认值），或者如果没有定义它为空字符串。
`default_expression` （String） --默认值的表达式，如果未定义，则为空字符串。
`column_bytes_on_disk` （UInt64） --列的总大小，以字节为单位。
`column_data_compressed_bytes` （UInt64） --列中压缩数据的总大小，以字节为单位。
`column_data_uncompressed_bytes` （UInt64） --列中解压缩数据的总大小，以字节为单位。
`column_marks_bytes` （UInt64） --带标记的列的大小，以字节为单位。
`bytes` （UInt64） --的别名bytes_on_disk。
`marks_size` （UInt64） --的别名marks_bytes。9、查询内存超限的sql
select query, query_start_time, exception from clusterAllReplicas('production02', system.query_log) WHERE(event_time >= '2024-03-18 00:00:00') AND (event_time <= '2024-03-18 23:00:00') and (query_kind = 'Select') and (exception != '') and exception like '%Memory limit%' limit 40;
