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 = 8192
25、修改表合并大小,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 ASC
8、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;