clickhouse 踩过的坑

【1】clickhouse left join 时 where条件判空无效

DROP TABLE if exists tablename1;
create table tablename1 engine = MergeTree order by column1
as
select t.* from tablename2 t
left join tablename3 t1 on t.column1=t1.column1
where t1.column is null;

默认管理时右表字段为'' 而不是null，因此条件where t1.column is null将剔除所有记录
正确写法：

DROP TABLE if exists tablename1;
create table tablename1 engine = MergeTree order by column1
as
select t.* from tablename2 t
left join tablename3 t1 on t.column1=t1.column1
where t1.column='';

 【2】clickhouse客户端dbeaver连接url需要设置连接时长，否则运行时间长的sql会报Read timed out错误，这是DBeaver默认设置的缘故 在query查询的时候DBeaver默认超过30秒自动断联，需要更改socket连接查询时间

dbc:clickhouse://xxxx:pppp/database?socket_timeout=300000

 

翻译

搜索

复制