Does Doris have a feature similar to DictGet in Clickhouse?

[smokeriu]

DictGet is a function in Clickhouse to quickly get the value mapped in the Dict table by key. With function DictGet, I can complete the operation of labelling the data in the query SQL.

I checked the official Doris documentation and there doesn't seem to be a similar feature.

I currently have some Clickhouse SQL which uses DictGet. i currently want to migrate to Doris, best practice is to change to Join?

[ixzc]

In Doris, if the dictGet functionality in Clickhouse needs to be replaced, the Join operation is usually used to implement the association of dimensional data. For the scenario you mentioned, because the IP data in the latitude table is relatively dispersed, the IP data in the fact table may not be equally dispersed, which puts some restrictions on using Colocation Join.

Colocation Join has the advantage of avoiding data shuffling, which makes the Join more efficient, but it relies on the data being bucketed in a specific way so that the Join can be performed directly on the corresponding node. If the IP data distribution of the latitude and fact tables does not fit the Colocation Join requirements, you may want to consider the following solutions:

Use Broadcast Join: If the latitude table is not large, a Broadcast Join can be used to broadcast the latitude table to all nodes, so that each node can perform a Join operation locally. However, be aware that if the latitude table is large, this approach may incur higher network overhead.
Use Shuffle Join or Bucket Shuffle Join: If Broadcast Join is not available or you want to avoid its network overhead, you can choose Shuffle Join or Bucket Shuffle Join. These two methods Shuffle the data based on the Join condition, so that data with the same key is distributed to the same nodes.
See Doris Join optimization for details
If the IP data in the fact table is not particularly scattered, but you still want to be able to take advantage of the features of deferred materialization to optimize performance, you can consider using the Runtime Filter provided by Doris. This allows data to be filtered before joining, reducing the amount of data that actually needs to be joined.
Broadcast Join may be a reasonable choice if AD hoc queries require high performance and the amount of latitude table data is not large.
If Colocation Join is not an option, consider data preprocessing, such as indexing or materializing views, to speed up queries.