“in”支持连接

v6ylcynt  于 2021-07-15  发布在  ClickHouse
关注(0)|答案(1)|浏览(385)

我们正在尝试将pentaho bi连接到clickhouse,有时pentaho会生成如下查询:

select
...
from
date_dimension_table,
fact_table,
other_dimension_table
where
fact_table.fact_date = date_dimension_table.date
and date_dimension_table.calendar_year = 2019
and date_dimension_table.month_name in ('April', 'June', ...)
and fact_table.other_dimension_id = other_dimension_table.id
and other_dimension_table.code in ('code1', 'code2', ...)
group by
date_dimension_table.calendar_year,
date_dimension_table.month_name,
other_dimension_table.code;

它产生clickhouse错误:代码:403,e.displaytext()=db::exception:join on的表达式无效。应为equals表达式,在('code1','code2',…)中获得(代码为c2)。支持的语法:join on expr([table.]column,…)=expr([table.]column,…)[和expr([table.]column,…)=expr([table.]column,…)](版本19.15.3.6(正式版本))
用于表的引擎:事实表-合并树,两个维度-tinylog。
因此,问题是:
这个问题可以通过改变表引擎来解决吗?不幸的是,我们无法更改查询,它是自动生成的。
如果没有,在最近的将来是否有计划支持clickhouse中in子句的连接?
桑克斯。

nhhxz33t

nhhxz33t1#

从clickhouse版本v20.3.2.12020-03-12(见7314版)开始,此问题已得到修复,因此您需要升级ch。
! 不要忘记检查所有向后不兼容的更改(请参阅changelog)。
让我们在ch 19.15.3版本54426上重现这个问题,以得到您描述的错误:

Received exception from server (version 19.15.3):
Code: 403. DB::Exception: Received from localhost:9000. DB::Exception: Invalid expression for JOIN ON. Expected equals expression, got code IN ('code1', 'code2'). Supported syntax: JOIN ON Expr([table.]column, ...) = Expr([table.]column, ...) [AND Expr([table.]column, ...) = Expr([table.]column, ...) ...].

现在对最新版本的ch(20.3.7修订版54433)执行此查询,以确保其正常工作:

docker pull yandex/clickhouse-server:latest

docker run -d --name ch_test_latest yandex/clickhouse-server:latest

docker exec -it ch_test_latest clickhouse-client

# create tables as described below

..

# execute test query

..

试验准备:

create table date_dimension_table (
    date DateTime,
    calendar_year Int32,
    month_name String
) Engine = Memory;

create table fact_table (
    fact_date DateTime,
    other_dimension_id Int32
) Engine = Memory;

create table other_dimension_table (
    id Int32,
    code String
) Engine = Memory;

测试查询:

SELECT 
    date_dimension_table.calendar_year, 
    date_dimension_table.month_name, 
    other_dimension_table.code
FROM date_dimension_table
    ,fact_table
    ,other_dimension_table
WHERE (fact_table.fact_date = date_dimension_table.date) 
    AND (date_dimension_table.calendar_year = 2019) 
    AND (date_dimension_table.month_name IN ('April', 'June')) 
    AND (fact_table.other_dimension_id = other_dimension_table.id) 
    AND (other_dimension_table.code IN ('code1', 'code2'))
GROUP BY 
    date_dimension_table.calendar_year, 
    date_dimension_table.month_name, 
    other_dimension_table.code

相关问题