利用 DataWorks 数据推送定期推播 ClickHouse Query 诊断信息

samuelhsin
94 阅读4分钟

前言

DataWorks 近期上线了数据推送功能，能够将数据库查询的数据组织后推送到各渠道 (如钉钉、飞书、企业微信及 Teams)，除了能将业务数据组织后推送，也能将数据库自身提供的监控数据组织后推送，这边我们就以 ClickHouse 为例，定期推播 ClickHouse 的慢 Query、数据量变化等信息，帮助用户掌握 ClickHouse 状态。

效果图 以下为一段时间内指定表及分区的存储使用情况，并推送到钉群、飞书、企业微信或 Teams。

image.png

实践：取得指定表及分区存储使用情况

以下为 SQL 内容

SELECT
    table,
    partition,
    count() AS parts,
    formatReadableSize(sum(bytes_on_disk)) AS bytes_on_disk,
    formatReadableQuantity(sum(rows)) AS rows,
    sum(marks) AS marks
FROM system.parts
WHERE active
GROUP BY
    table,
    partition;

以下为推送内容设定

image.png

调度及推送设置

image.png

推送结果

image.png

实践：取得指定表的日平均数据使用量统计

取得指定表的日平均数据使用量，方便了解数据量水位。 以下为 SQL 内容

SELECT
    table,
    formatReadableSize(size) AS size,
    rows,
    days,
    formatReadableSize(avgDaySize) AS avgDaySize
FROM
(
    SELECT
        table,
        sum(bytes) AS size,
        sum(rows) AS rows,
        min(min_date) AS min_date,
        max(max_date) AS max_date,
        max_date - min_date AS days,
        size / (max_date - min_date) AS avgDaySize
    FROM system.parts
    WHERE active
    AND (table = 'asynchronous_metric_log' OR table = 'query_thread_log' OR table = 'trace_log')
    GROUP BY table
    ORDER BY rows DESC
);

以下为推送内容设定

image.png

推送结果

image.png

实践：取得指定表最近列的数据压缩程度

可以按列查看数据的压缩程度。此查询还会返回内存中主索引的大小，了解这一点很有用，因为主索引必须小于内存。 以下为 SQL 内容

SELECT
    parts.table as table,
    parts.rows,
    parts.latest_modification,
    parts.disk_size,
    parts.primary_keys_size,
    parts.bytes_size,
    columns.compressed_size,
    columns.uncompressed_size,
    columns.compression_ratio,
    columns.compression_percentage
FROM
(
    SELECT
        table,
        formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
        formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
        round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes), 3) AS compression_ratio,
        round(100 - ((sum(data_compressed_bytes) * 100) / sum(data_uncompressed_bytes)), 3) AS compression_percentage
    FROM system.columns
    GROUP BY table
) AS columns
RIGHT JOIN
(
    SELECT
        table,
        sum(rows) AS rows,
        max(modification_time) AS latest_modification,
        formatReadableSize(sum(bytes)) AS disk_size,
        formatReadableSize(sum(primary_key_bytes_in_memory)) AS primary_keys_size,
        sum(bytes) AS bytes_size
    FROM system.parts
    WHERE active
    AND (table = 'asynchronous_metric_log' OR table = 'query_thread_log' OR table = 'trace_log')
    GROUP BY
        table
) AS parts ON columns.table = parts.table
ORDER BY parts.bytes_size DESC;

以下为推送内容设定

image.png

推送结果

image.png

实践：用户端在过去一段时间内发送的查询数

查询用户端在过去一段时间内发送的查询数，这边以 1 小时为例。 以下为 SQL 内容

SELECT
    client_name,
    count() as counts,
    query_kind,
    toStartOfHour(event_time) as event_time_h
FROM system.query_log
WHERE (type = 'QueryStart') AND (event_time > (now() - toIntervalMinute(60)))
GROUP BY
    event_time_h,
    client_name,
    query_kind
ORDER BY
    event_time_h DESC,
    count() ASC;

以下为推送内容设定

image.png

推送结果

image.png

实践：取得指定表的分区数

以下为 SQL 内容

SELECT
    table,
    partition_id,
    count() as counts
FROM system.parts
WHERE active
AND (table = 'asynchronous_metric_log' OR table = 'query_thread_log' OR table = 'trace_log')
GROUP BY
    table,
    partition_id;

以下为推送内容设定

image.png

推送结果

image.png

实践：取得运行最长的查询

以下为 SQL 内容

SELECT
    elapsed,
    initial_user,
    client_name,
    hostname() as hostname,
    query_id,
    query
FROM clusterAllReplicas(default, system.processes)
ORDER BY elapsed DESC;

以下为推送内容设定

image.png

推送结果

image.png

实践：查看最近失败的信息

查询最近失败的前20笔信息。 以下为 SQL 内容

SELECT name, code, value, last_error_time
FROM system.errors
ORDER BY last_error_time DESC limit 20;

以下为推送内容设定

image.png

推送结果

image.png

实践：取得前十个使用最多资源的查询

以下为 SQL 内容

SELECT
    type,
    event_time,
    initial_query_id,
    formatReadableSize(memory_usage) AS memory,
    `ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'UserTimeMicroseconds')] AS userCPU,
    `ProfileEvents.Values`[indexOf(`ProfileEvents.Names`, 'SystemTimeMicroseconds')] AS systemCPU,
    normalizedQueryHash(query) AS normalized_query_hash
FROM system.query_log
ORDER BY memory_usage DESC
LIMIT 10

以下为推送内容设定

image.png

推送结果

image.png

实践：取得projection存储使用情况

以下为 SQL 内容

SELECT
    name,
    parent_name,
    formatReadableSize(bytes_on_disk) AS bytes,
    formatReadableSize(parent_bytes_on_disk) AS parent_bytes,
    bytes_on_disk / parent_bytes_on_disk AS ratio
FROM system.projection_parts;

以下为推送内容设定

image.png

实践：取得最近创建的详细信息，包括创建时间、大小、行数等

以下为 SQL 内容

SELECT
    modification_time,
    rows,
    formatReadableSize(bytes_on_disk) as size,
    table
FROM clusterAllReplicas(default, system.parts)
WHERE active AND (level = 0)
ORDER BY modification_time DESC
LIMIT 100;

以下为推送内容设定

image.png

推送结果

image.png

实践：取得指定表的最近数据量统计

取得指定表的最近数据量统计，方便了解数据量水位。 以下为 SQL 内容

SELECT table,
    formatReadableSize(sum(bytes)) as size
    FROM system.parts
    WHERE active
    AND table = 'test_table'
GROUP BY table;

以下为推送内容设定

image.png

推送结果

image.png

实践：查看最近变动的配置

查询最近变动的配置值 (与 default 不同的部份)。 以下为 SQL 内容

SELECT
    name,
    value
FROM system.settings
WHERE changed;

以下为推送内容设定

image.png

推送结果

image.png

小结

ClickHouse 提供许多能查运行状态的表，能利用此数据加工后，透过 DataWorks 数据推送推至渠道方便监控，提升引擎运行态的质量。

相关文章

数据推送功能详细介绍 help.aliyun.com/zh/datawork… 数据开发工作流 + 数据推送介绍 help.aliyun.com/zh/datawork… 使用 DataWorks 建立每日天气预报推送 juejin.cn/post/739106… 语雀+通义千问+DataWorks，让AI定期推送每周总结 juejin.cn/post/739081… 利用 DataWorks 数据推送定期推播 Hologres Query 诊断信息 juejin.cn/post/738992… ClickHouse Query 诊断: clickhouse.com/docs/knowle…

avatar
文章
阅读
粉丝