Clickhouse: Subquery for lookup

[kitex]

I am trying to use subquery to lookup value but it is not working.

drop table a;
create table a (
    eci Int64 ,
    edu Int64,
    pri Int64,
    result Int64
)ENGINE = MergeTree() ORDER BY (eci);

insert into a values(12,-1,-1,100);
insert into a values(-1,1,2,110);
select * from a ;

drop table b;
create table b(
    eci_lu Int64 ,
    edu_lu Int64,
    pri_lu Int64,
    location String 
)ENGINE = MergeTree() ORDER BY (eci_lu);

truncate table b;
insert into b values(12,-1,-1,'Apple');
insert into b values(-1,1,2,'Microsoft');
select * from b;


select eci,edu,pri, (select location from b where b.eci_lu=a.eci AND (b.edu_lu=a.edu and b.pri_lu=a.pri) ) from a ;

Error:

ClickHouse exception, code: 47, host: 10.74.16.13, port: 8123; Code: 47, e.displayText() = DB::Exception: Missing columns: 'a.edu' 'a.pri' 'a.eci' while processing

[den-crane]

CH does not support correlated sub-queries.

you can use dictionaries

CREATE DICTIONARY item_dict ( eci_lu Int64, edu_lu Int64, pri_lu Int64, location String  ) 
PRIMARY KEY eci_lu,edu_lu,pri_lu SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 
TABLE b DB 'default' USER 'default')) 
LIFETIME(MIN 0 MAX 0) LAYOUT(COMPLEX_KEY_HASHED());

select eci,edu,pri, dictGet('default.item_dict', 'location', tuple(a.eci, a.edu, a.pri) ) loc from a ;

┌─eci─┬─edu─┬─pri─┬─loc───────┐
│  -1 │   1 │   2 │ Microsoft │
│  12 │  -1 │  -1 │ Apple     │
└─────┴─────┴─────┴───────────┘

[kitex]

CREATE DICTIONARY item_dict ( eci_lu Int64, edu_lu Int64, pri_lu Int64, location String )
PRIMARY KEY eci_lu,edu_lu,pri_lu SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000
TABLE b DB 'default' USER 'default'))
LIFETIME(MIN 0 MAX 0) LAYOUT(COMPLEX_KEY_HASHED());

I could not run the query, is it dictionary from local table? Any documents as I am not able to find one.

[den-crane]

https://clickhouse.tech/docs/en/sql-reference/statements/create/#create-dictionary-query

ClickHouse Release V19.17.4.11, 2019-11-22
New Feature
Add the ability to create dictionaries with DDL queries

And you can create a dictionary using the old way -- xml file
https://clickhouse.tech/docs/en/sql-reference/dictionaries/external-dictionaries/external-dicts/

[kitex]

https://clickhouse.tech/docs/en/sql-reference/statements/create/#create-dictionary-query

ClickHouse Release V19.17.4.11, 2019-11-22
New Feature
Add the ability to create dictionaries with DDL queries

And you can create a dictionary using the old way -- xml file
https://clickhouse.tech/docs/en/sql-reference/dictionaries/external-dictionaries/external-dicts/

It works well. Just updated clickhouse to 20. Thanks.