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

cat ./24680.csv | clickhouse-client --query="INSERT INTO test.etl_membi_oi_hm_with_all_dim_local FORMAT CSV";

21、txt方式导入数据

cat ./11111111.txt |clickhouse-client --port 9002 --query="INSERT INTO test.etl_dim_custom_class_local FORMAT TabSeparated"

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.2.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;