Felix021

CREATE DICTIONARY xxx.ip_region_dict
ON CLUSTER default_cluster
(
    `prefix` UInt32,
    `province` Nullable(String),
    `province_code` UInt32,
    `city` Nullable(String),
    `city_code` UInt32,
    `area` Nullable(String),
    `area_code` UInt32
)
PRIMARY KEY prefix
SOURCE(CLICKHOUSE(HOST '127.0.01' USER 'user' PASSWORD 'xxxx' DB 'test' TABLE 'ip_region' ))
LIFETIME(MIN 0 MAX 0) -- 需要手动刷新
LAYOUT(HASHED())

SOURCE(MYSQL(HOST '127.0.0.1' PORT 3306 USER 'root' PASSWORD 'xxxxx' DB 'test' TABLE 'xxx'))
LIFETIME(MIN 300 MAX 600) -- 5~10分钟自动刷新
LAYOUT(HASHED())

SYSTEM RELOAD DICTIONARY
ON CLUSTER default_cluster
ip_region_dict;

dictGet('xxx.ip_region_dict', 'city', intDiv(IPv4StringToNumOrDefault(ip), 256))

CREATE FUNCTION getIPCity
ON CLUSTER default_cluster
AS (ip) ->
        dictGet('xxx.ip_region_dict', 'city', intDiv(IPv4StringToNumOrDefault(ip), 256));