Jupyter中能否在ClickHouse查询内直接Join本地DataFrame完成关联聚合？

这个方案完全可行，核心思路就是把小体量的品类维度表提前放到ClickHouse侧，所有过滤、关联、聚合逻辑都在ClickHouse端完成，最后只返回聚合后的小数据集到本地，完全避免拉取全量半年销售数据到本地做计算，性能提升非常明显。

下面是两种最常用的实现方式：

方式1：临时表方案（最通用） #

品类表一般数据量很小，你可以把从MySQL导出的品类DataFrame直接写入ClickHouse的临时表，临时表是会话级的，连接断开后会自动删除，不会持久化占用ClickHouse存储，写入速度极快。

以Python环境为例，实现代码参考：

import pandas as pd
from clickhouse_driver import Client

# 1. 从MySQL查询品类表生成本地DataFrame
category_df = pd.read_sql(
    "SELECT product_id, category FROM your_mysql_category_table",
    con=mysql_connection # 你的MySQL连接对象
)

# 2. 连接ClickHouse，创建临时表并写入品类数据
ck_client = Client(
    host="你的ClickHouse地址",
    user="用户名",
    password="密码",
    database="销售表所在库名"
)
# 临时表字段类型要和销售表的product_id类型完全一致，避免关联时隐式转换影响性能
ck_client.execute("""
CREATE TEMPORARY TABLE temp_category (
    product_id String,
    category String
)
""")
# 写入品类数据
ck_client.execute(
    "INSERT INTO temp_category VALUES",
    category_df.to_dict("records")
)

# 3. 执行关联聚合查询，所有计算逻辑都在ClickHouse侧完成
agg_result = ck_client.execute("""
SELECT 
    category,
    toYearWeek(date) AS week_number,
    sum(sales) AS sales
FROM sales_data
INNER JOIN temp_category 
ON sales_data.product_id = temp_category.product_id
WHERE date BETWEEN %(start_date)s AND %(end_date)s
GROUP BY category, week_number
""", params={
    "start_date": "2024-01-01",
    "end_date": "2024-06-30"
})

最后拿到的agg_result就是聚合后的结果，数据量很小，可以直接本地处理。

方式2：MySQL外表方案（适合高频使用场景） #

如果你经常需要做这类品类和销售数据的关联，完全可以省略导出DataFrame的步骤，直接在ClickHouse中创建MySQL引擎的外表，直连你的MySQL品类表，查询时直接关联即可，ClickHouse会自动拉取品类表数据做关联，无需手动同步。

创建外表的SQL参考：

CREATE TABLE mysql_category (
    product_id String,
    category String
) ENGINE = MySQL(
    '你的MySQL地址:端口',
    'MySQL库名',
    'MySQL品类表名',
    'MySQL用户名',
    'MySQL密码'
)

后续查询直接关联这个外表即可：

SELECT 
    category,
    toYearWeek(date) AS week_number,
    sum(sales) AS sales
FROM sales_data
INNER JOIN mysql_category 
ON sales_data.product_id = mysql_category.product_id
WHERE date BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY category, week_number

适用场景对比 #

• 临时表方案：适合偶尔做一次这类分析的场景，不需要提前做任何元数据配置，灵活度高。
• 外表方案：适合高频做品类销售分析的场景，一劳永逸，不用每次导出品类表。

内容的提问来源于stack exchange，提问作者Katsiaryna Shkirych