byted-bytehouse-data-asset-analyzer

ByteHouse Data Asset and Lineage Analysis Skill

Description

Directly through clickhouse-connect Connect to ByteHouse (HTTPS 8123), query system.tables / system.columns Scan all tables and fields of the target database, and generate three outputs:

Schema Report — Complete table structure of the database (engines, fields, original DDL, etc.).
Data Asset Catalog — Total number of tables, total number of columns, engine distribution, automatic tags.
Lineage Analysis Report — Distributed ↔ Local relationships, similarity of same-named columns across tables.

Use this Skill when the following situations occur:

Need to obtain table structure and field information of a ByteHouse database.
Need to generate a data asset catalog and take stock of data assets.
Need to analyze lineage relationships between tables (for example, Distributed → Local).
The user mentions keywords such as "data asset", "lineage analysis", "table structure", "field analysis", etc.

Prerequisites

Python 3.8+
clickhouse-connect(Installation method:pip install clickhouse-connect）
jq（export_config.sh dependency, 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 documentation.
scripts/data_asset_analyzer.py — Main program for data asset and lineage analysis.
scripts/export_config.sh — From ~/.bytehouse_config.json Load configuration into environment variables (depends on jq）。

Configuration Description

Configuration is saved in ~/.bytehouse_config.json. If the file exists and is non-empty, use it directly; if it does not exist, ask the user for connection information (you can send the ByteHouse Connection Information Retrieval Guide to the user), and once obtained, write it to the JSON file to avoid repeated asking.

{
  "BYTEHOUSE_HOST": "<ByteHouse-host>",
  "BYTEHOUSE_PORT": "8123",
  "BYTEHOUSE_USER": "bytehouse",
  "BYTEHOUSE_PASSWORD": "<ByteHouse-password>"
}

Configuration Item Description:

Configuration Item	Required	Default Value	Description
`BYTEHOUSE_HOST`	Yes	—	ByteHouse gateway domain
`BYTEHOUSE_PORT`	No	`8123`	HTTP(S) port
`BYTEHOUSE_USER`	No	`bytehouse`	Username
`BYTEHOUSE_PASSWORD`	Yes	—	Password / API Key

Load configuration:

source scripts/export_config.sh

🧭 Agent Usage Guide

Confirm configuration：source scripts/export_config.sh, confirm that environment variables such as BYTEHOUSE_HOST are ready.
Confirm the database to analyze: If the user has not specified it, proactively ask (for example, default、tpcds）。
Execute analysis：python3 scripts/data_asset_analyzer.py <database>, the script will query through clickhouse-connect query system.tables / system.columns pull metadata.
Display results: Based on the 3 JSON results in the scripts/output/ directory, provide key points such as total number of tables, total number of columns, engine distribution, Top lineage relationships, etc.
Do not fabricate: All asset/lineage information must come from script outputs; do not guess based on context.

🎯 Features

1. Complete Schema Retrieval

Retrieve all tables of the specified database.
Retrieve all fields of each table.
Extract metadata such as table engine, comments, etc.
Keep the original CREATE TABLE statement text.

2. Data asset catalog generation

Table statistics (total table count, total column count).
Engine distribution statistics.
Automatic tag generation.
Table asset details (including a preview of the first 10 fields).

3. Lineage analysis

Table relationship identification (Distributed ↔ Local, based on naming patterns).
Column similarity analysis (same-named columns appearing across tables).

🚀 Quick Start

# 1. 加载配置（复用 ~/.bytehouse_config.json）
source scripts/export_config.sh

# 2. 执行分析（指定库名）
python3 scripts/data_asset_analyzer.py default
python3 scripts/data_asset_analyzer.py tpcds

The analysis content includes:

The complete database schema (all tables and fields).
Data asset catalog (table statistics, engine distribution, automatic tags).
Lineage analysis (table relationships, column similarity).

Output files (saved in the same directory as the script under scripts/output/ directory):

schema_{database}_{timestamp}.json — The complete database schema.
catalog_{database}_{timestamp}.json — Data asset catalog.
lineage_{database}_{timestamp}.json — Lineage analysis report.

💻 Programmatic invocation

scripts/data_asset_analyzer.py What is exposed is asynchronous function analyze_database(database)（not async, nornotclass). It returns the analysis result dictionary in memory,and does not write to disk itself; if you need to generate a JSON file, please run the script directly main()(that is, the command-line method).

To call it in other Python code:

import os
import sys

# 把 scripts/ 目录加入 sys.path，以便直接 import 模块
SKILL_SCRIPTS_DIR = os.path.join(
    os.path.dirname(os.path.abspath(__file__)),
    "scripts",
)
sys.path.insert(0, SKILL_SCRIPTS_DIR)

from data_asset_analyzer import analyze_database

# 前提：已经通过 `source scripts/export_config.sh` 或手工 export 设置了
# BYTEHOUSE_HOST / BYTEHOUSE_PORT / BYTEHOUSE_USER / BYTEHOUSE_PASSWORD 等环境变量
result = analyze_database("default")
# result 是一个字典，包含:
# - schema:  完整的数据库 schema
# - catalog: 数据资产目录
# - lineage: 血缘分析

📊 Output File Description

1. Schema file (`schema_*.json`)

Contains the complete database structure:

{
  "database": "default",
  "analyzed_at": "2026-03-12T19:50:00",
  "tables": [
    {
      "name": "conversation_feedback",
      "comment": "",
      "engine": "Distributed",
      "create_table_query": "CREATE TABLE ...",
      "columns": [
        {
          "name": "session_id",
          "type": "String",
          "comment": ""
        }
      ]
    }
  ]
}

2. Data asset catalog (`catalog_*.json`)

Contains data asset statistics (note: the statistics fields and tables_catalog are located at the top level, with no additional summary nesting):

{
  "database": "default",
  "generated_at": "2026-03-12T19:50:00",
  "total_tables": 8,
  "total_columns": 45,
  "engine_distribution": {
    "Distributed": 4,
    "MergeTree": 3,
    "Log": 1
  },
  "tables_catalog": [
    {
      "name": "conversation_feedback",
      "engine": "Distributed",
      "column_count": 10,
      "tags": ["分布式表", "用户/客户"],
      "columns": [
        {"name": "session_id", "type": "String"}
      ],
      "has_more_columns": false
    }
  ]
}

columns Only the first 10 fields are included for preview,has_more_columns indicates whether there are more fields.

3. Lineage analysis (`lineage_*.json`)

Contains table relationships and column similarity:

{
  "database": "default",
  "analyzed_at": "2026-03-12T19:50:00",
  "table_relationships": [
    {
      "table": "conversation_feedback",
      "related_tables": [
        {
          "name": "conversation_feedback_local",
          "relationship": "Local -> Distributed"
        }
      ]
    }
  ],
  "column_similarities": [
    {
      "column_name": "session_id",
      "occurrences": [
        {"table": "conversation_feedback", "type": "String"},
        {"table": "conversation_feedback_local", "type": "String"}
      ]
    }
  ]
}

🏷️ Automatic Tag Generation

The analyzer will, based ontable engine、table name keywords、column countautomatically generate tags (the engine-type tags are mutually exclusive; once one is matched, stop; the remaining rules can be stacked; if none are matched, mark as 未分类）：

Tag	Trigger condition
`分布式表`	Engine is `Distributed`
`MergeTree系列`	Engine contains `MergeTree`(and is not Distributed)
`日志引擎`	Engine contains `Log`(and is not either of the above two types)
`日志/事件`	Table name contains `log` / `event` / `trace`
`交易/订单`	Table name contains `order` / `trans` / `pay`
`用户/客户`	Table name contains `user` / `customer` / `account`
`商品/物料`	Table name contains `product` / `item` / `goods`
`时序聚合`	Table name contains `daily` / `hourly` / `monthly`
`维度表`	Table name contains `dim` / `dimension`
`事实表`	Table name contains `fact`
`宽表`	Column count > 50
`窄表`	Column count < 5
`未分类`	No rule above is matched

Failure fallback suggestions

Symptom	Handling method
`clickhouse-connect not installed`	Install dependencies:`pip install clickhouse-connect`。
`BYTEHOUSE_HOST is required` / not set	Guide the user to provide connection information, write to `~/.bytehouse_config.json` then re- `source scripts/export_config.sh`。
Connection failed / certificate error	The script always uses HTTPS and skips certificate verification (`secure=True`、`verify=False`); please confirm `BYTEHOUSE_HOST`The port is accessible from the public network.
The analysis result is empty	Ask the user to confirm whether the database name is correct and whether the account has permission to query the `system` table.