byted-bytehouse-data-quality-inspector

ByteHouse data quality inspection tool

Description

This Skill is used to perform rapid data quality analysis on the key key columns (partition key, sorting key, primary key) of a ByteHouse table, identifying common quality issues such as null values, zero values, abnormal distribution, and duplicate keys.

Use this Skill in the following situations:

1. The user needs to check the data quality of a table in ByteHouse.
2. The user needs to analyze the null values and zero values of the table's sorting key, primary key, or partition key.
3. The user wants to understand the data distribution of key columns (Top N values and their proportions).
4. The user needs to check whether there are duplicate records in the sorting key / primary key combination.
5. The user mentions keywords such as "data quality", "null value check", "zero value check", "primary key duplication", "sorting key duplication", "partition key anomaly", etc.

Prerequisites

• Python 3.8+
• clickhouse-connect library (when not installed, execute pip install clickhouse-connect）
• jq（export_config.sh dependency, used to parse JSON configuration. Installation method:brew install jq or sudo apt install jq）
• Valid ByteHouse connection information (saved in ~/.bytehouse_config.json）

📁 File Description

• SKILL.md — this file, the main skill document.
• scripts/inspector.py — the main data quality inspection program.
• scripts/export_config.sh — from ~/.bytehouse_config.json load the configuration into environment variables (depends on jq）。

Configuration Description

The configuration is saved in ~/.bytehouse_config.json. If the file exists and is non-empty, use the configuration in the file directly; if it does not exist, ask the user for the ByteHouse connection information (you can send ByteHouse Connection Information Acquisition Guide to the user together), and once obtained, write it into the JSON file to avoid repeated inquiries. When the user switches ByteHouse clusters, update this file as well.

{
   "BYTEHOUSE_HOST": "<ByteHouse-host>",
   "BYTEHOUSE_PASSWORD": "<ByteHouse-password>"
}

• BYTEHOUSE_HOST(host address) and BYTEHOUSE_PASSWORD(password)must be provided by the user。

Load configuration:

source scripts/export_config.sh

🧭 Agent Usage Guide

1. Confirm configuration：source scripts/export_config.sh, ensure that BYTEHOUSE_HOST and BYTEHOUSE_PASSWORD are ready.
2. Confirm target table: confirm with the user the database name (--database) and table name (--table) to be checked. Multiple tables can be processed by calling the script in a loop.
3. Execute check：python3 scripts/inspector.py --database <库名> --table <表名>。
4. Interpret results: the script outputs two main parts: "null values/zero values/distribution" and "primary key/sorting key duplication". If the user does not have a clear focus, focus on anomalies such as null value proportion > 1%, zero value proportion > 5%, Top1 proportion > 20%, duplicate key row proportion > 0.5%, etc.
5. Unauthorized operations are prohibited: this Skill only performsread-onlystatistical queries,do nottrigger DML/DDL operations such as DELETE / OPTIMIZE / DROP. Data repair should be performed separately by the user after evaluation.

🎯 Features

1. Automatically identify key columns

   • from system.columns automatically obtain the table's partition key, sorting key, and primary key columns.

2. Null and Zero Value Check

   • Count the number and proportion of Null values in the key column.
   • For numeric types, count the number and proportion of zero values; for string types, count the number and proportion of empty strings.

3. Abnormal Distribution Analysis

   • Count the Top 5 most frequent values and their proportions in the key column.

4. Duplicate Check

   • For the primary key combination and the sort key combination respectively, count the number of duplicate unique key groups and the total number of rows involved.

🚀 Quick Start

# 加载配置
source scripts/export_config.sh

# 检查单张表
python3 scripts/inspector.py --database default --table my_table

💡 Common Practices

• Batch Check Multiple Tables：

  for tbl in orders order_items users; do
    python3 scripts/inspector.py --database default --table "$tbl"
  done
  

• Combine with Asset Analysis: First use the catalog output by byted-bytehouse-data-asset-analyzer to pick out the key tables, then run this Skill table by table.

Sample Output Summary

=== 表 default.my_table 数据质量分析报告 ===
总行数: 1000000
分区键: date
排序键: user_id, event_time
主键: user_id
=============================================

1. 关键列空值、零值及分布情况分析:
---------------------------------------------
▶ 列 [user_id] (类型: String):
  - 空值 (Null) 数量: 0 (占比: 0.00%)
  - 空字符串 ('') 数量: 15 (占比: 0.00%)
  - 数据分布 (Top 5):
    * user_123: 500 行 (占比: 0.05%)
    ...

2. 键重复情况分析:
---------------------------------------------
▶ 主键 [user_id] 重复情况:
  - 存在重复的唯一键组合数: 850
  - 涉及的重复行数: 15000 (占比: 1.50%)

Notes

• Running statistical aggregations on very large tables will consume a certain amount of cluster resources; please execute during off-peak business hours or on a dedicated compute group.
• This tool onlydetectsquality issues and does not perform any automatic repair actions.
• If the target table has a particularly large data volume and the user can accept sampling, you may first confirm with the user and then have the script add SAMPLE / LIMIT(the current script defaults to full-table statistics).

Failure Fallback Recommendations

Symptom	Handling Method
BYTEHOUSE_HOST not set	Guide the user to provide connection information, write it to ~/.bytehouse_config.json then source scripts/export_config.sh。
clickhouse-connect not installed	pip install clickhouse-connect or python3 -m pip install --user clickhouse-connect。
找不到表 xxx	Ask the user to confirm the database name and table name, or first use the byted-bytehouse-ai-query of list_tables.py list to verify.
Check takes too long / triggers resource alerts	It is recommended to execute on a dedicated compute group or outside peak business hours, and if necessary, first check only the key columns suspected to have issues.