alias_dictGet

alias_dictGet.txt

create table fake_table_dict_source(key Int32, value String) Engine=MergeTree order by tuple();
insert into fake_table_dict_source values(1, 'NY'), (2, 'Moscow'), (3, 'Berlin');


CREATE DICTIONARY mydict ( key Int32, value String injective) 
PRIMARY KEY key SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 
TABLE fake_table_dict_source DB 'default' USER 'default'))   
LIFETIME(600) LAYOUT(COMPLEX_KEY_HASHED());

create table facts(id Int64, city_id Int32, city_name String alias  dictGetString('default.mydict', 'value', tuple(city_id)))
Engine=MergeTree order by id;

insert into facts values(1,1), (2,33), (3,3);
insert into facts values(5,1), (6,33), (7,3);


select id, city_id, city_name from facts;
┌─id─┬─city_id─┬─city_name─┐
│  1 │       1 │ NY        │
│  2 │      33 │           │
│  3 │       3 │ Berlin    │
└────┴─────────┴───────────┘
┌─id─┬─city_id─┬─city_name─┐
│  5 │       1 │ NY        │
│  6 │      33 │           │
│  7 │       3 │ Berlin    │
└────┴─────────┴───────────┘

select count(), city_name from facts
group by city_name
┌─count()─┬─city_name─┐
│       2 │           │
│       2 │ Berlin    │
│       2 │ NY        │
└─────────┴───────────┘