clickhouse分布式WITH临时表查询问题

最新推荐文章于 2024-02-25 13:42:51 发布

_三石_

最新推荐文章于 2024-02-25 13:42:51 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

文章标签： clickhouse

本文链接：https://blog.youkuaiyun.com/weixin_43871079/article/details/132342265

文章讲述了作者在ClickHouse分布式环境中遇到的SQL错误，涉及WITH语句创建的临时表t1找不到。解决方法是将WITH语句移动到SQL的第一条语句，因为WITH定义的临时表必须是第一条。通过调整语句顺序解决了问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一个简单的sql引起的问题

WITH t1 AS (SELECT id FROM b.ods_a WHERE 1 = 1)
SELECT * FROM (
	SELECT 
		report_time,
        ceil(total * 2.361 / 10000, 2)    total,
        round(neighbor(total, -12), 2) AS prevYear,
        round(neighbor(total, -1), 2)  AS prevMonth
    FROM b.ods_b t2 INNER JOIN  t1 ON t1.id = t2.id
	    GROUP BY report_time
	    ORDER BY report_time
    )
);

SQL 错误 [1002]: ClickHouse exception, code: 1002, host: 10.122.49.209, port: 28124; Code: 60. DB::Exception: Received from ck01:29000. DB::Exception: Table default.t1 doesn't exist. (UNKNOWN_TABLE) (version 22.5.1.2079 (official build))
sql在单机clickhouse运行正常，搭建了一个分布式环境后出现的问题，从报错信息来看，是临时表t1未找到，但是查询的是默认default库
解决方法，就是把with表语句写到sql里去

SELECT * FROM (
	SELECT 
		report_time,
        ceil(total * 2.361 / 10000, 2)    total,
        round(neighbor(total, -12), 2) AS prevYear,
        round(neighbor(total, -1), 2)  AS prevMonth
    FROM b.ods_b t2 INNER JOIN  (SELECT id FROM b.ods_a WHERE 1 = 1) t1 ON t1.id = t2.id
	    GROUP BY report_time
	    ORDER BY report_time
    )
);

但是这样就需要把所有的sql都改一遍，而且为什么用with就是因为业务上有些语句比较复杂，这么写容易看，所以不太希望这么改动，只能再找下原因，本以为官网会有说明，但是没有找到具体的解释，无意中找了一下with的使用限制，里面提到了一句

使用WITH 定义的“临时表（视图）”，必须是定义WITH 后的第一条sql语句

因此将sql调整了一下顺序，发现就可以正常使用了

WITH t1 AS (SELECT id FROM b.ods_a WHERE 1 = 1)
SELECT * FROM (
	SELECT 
		report_time,
        ceil(total * 2.361 / 10000, 2)    total,
        round(neighbor(total, -12), 2) AS prevYear,
        round(neighbor(total, -1), 2)  AS prevMonth
    FROM t1 INNER JOIN  b.ods_b t2 ON t1.id = t2.id
	    GROUP BY report_time
	    ORDER BY report_time
    )
);