clickhouse 外部字典 DDL

ck版本：20.8.9.6

-- 建表ddl (source:MySQL)
CREATE DICTIONARY crs.route_list_dict on cluster ck_cluster (
  route String DEFAULT '',
  name String DEFAULT ''
)
PRIMARY KEY route
SOURCE(MYSQL(
    port 61739
    user 'root'
    password 'rnH!FqKAw4wEynGF'
    replica(host 'sh-cdb-i6y7zce8.sql.tencentcdb.com' priority 1)
    db 'release_cdp'
    table 'route_list'
    invalidate_query 'SQL_QUERY'
    fail_on_connection_loss 'true'
    query 'SELECT route, name FROM release_cdp.route_list'
))
LIFETIME(MIN 1 MAX 10)
LAYOUT(COMPLEX_KEY_HASHED());

参数说明：
PRIMARY KEY: 主键--字典的key
SOURCE:来源
LIFETIME：字典定期更新间隔
LAYOUT：在内存中存储字典有多种方式（eg:COMPLEX_KEY_HASHED，支持字典key为String）

-- 删除dict
DROP DICTIONARY crs.route_list_dict on cluster ck_cluster

-- 测试查询
SELECT
    route,
    dictGet('crs.route_list_dict', 'name', tuple(route)) AS route_name,
    count(1)
FROM crs.crs_events_v3
WHERE toDate(dt) = '2021-12-06'
GROUP BY
    route,
    route_name
LIMIT 10


-- 查询结果
┌─route──────────────────────────────────────────────────┬─route_name───┬─count(1)─┐
│ activity-hot-new/activity-hot-new/activity-hot-new     │              │     2798 │
│ integral/my-integral-detail/my-integral-detail         │ 积分         │       78 │
│ categories/three-categories/three-categories           │              │     5539 │
│ goods/search-list/search-list                          │              │       61 │
│ activity-list/activity-group/activity-group            │              │       50 │
│ pages/password-coupon/password-coupon                  │ 口令券       │       57 │
│ address/address-list/address-list                      │ 地址管理     │     8943 │
│ my/my-footprint/my-footprint                           │ 我的足迹     │     3255 │
│ extranet/authentication-success/authentication-success │ 智慧导购认证 │       12 │
│ pages/shop-cart/shop-cart                              │ 购物车       │   454153 │
└────────────────────────────────────────────────────────┴──────────────┴──────────┘