一.字典创建和查询
1.创建表和数据:
drop table t_region;
create table t_region(region_id UInt64, parent_region UInt64, region_name String) ENGINE=TinyLog;
insert into t_region values
(1, 0, 'jiangsu'),(2, 1, 'suzhou'),(3, 2, 'huqiu'),(4, 0, 'anhui'),(5, 4, 'hefei');
创建字典, 指定HIERARCHICAL字段:
DROP DICTIONARY t_dict_region;
CREATE DICTIONARY t_dict_region (
region_id UInt64,
parent_region UInt64 HIERARCHICAL,
region_name String
)
PRIMARY KEY region_id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9001
user 'default'
db 'default'
password ''
table 't_region'
))
LAYOUT(HASHED())
LIFETIME(30);
2.字典的查询
SELECT dictGetString('default.t_dict_region', 'region_name', toUInt64(2)) AS regionName;
┌─regionName─┐
│ suzhou │
└────────────┘
SELECT dictGetHierarchy('default.t_dict_region', toUInt64(3));
┌─dictGetHierarchy('default.t_dict_region', toUInt64(3))─┐
│ [3,2,1] │
└────────────────────────────────────────────────────────┘
3.字典数据源之mysql表
- 在mysql数据库创建表并插入数据:
drop table test.test_dc; create table test.test_dc( id bigint, name varchar(100), age int, PRIMARY KEY (id) ); insert into test.test_dc values(1, 'flink', 4); insert into test.test_dc values(2, 'spark', 6); insert into test.test_dc values(3, 'clickhouse', 5); 查看MySQL数据: mysql> select * from test.test_dc; +----+------------+------+ | id | name | age | +----+------------+------+ | 1 | flink | 4 | | 2 | spark | 6 | | 3 | clickhouse | 5 | +----+------------+------+
4.字典的数据源之文件数据源
TabSeparated格式
文件示例:
准备测试数据
文件命名为person.tsv,存放在目录:/var/lib/clickhouse/user_files,字段之间使用制表符分隔,即格式为TabSeparated。数据如下:
1 'id001' 'xiaohe' 23
2 'id002' 'xiaoxue' 25
3 'id003' 'xiaoyu' 26
4 'id004' 'xiaoxi' 27
创建字典:
DROP DICTIONARY t_dict_person_ddl;
CREATE DICTIONARY t_dict_person_ddl
(
id UInt64,
code String,
name String,
age UInt8
)
PRIMARY KEY id
SOURCE(FILE(path '/var/lib/clickhouse/user_files/person.tsv' format 'TabSeparated'))
LAYOUT(FLAT())
LIFETIME(30);
SELECT dictGetString('default.t_dict_person_ddl', 'name', toUInt64(2)) AS regionName;
当然,字典类型的数据也可以通过配置实现
<yandex>
<dictionary>
<name>t_dict_executable</name>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>code</name>
<type>String</type>
<null_value/>
</attribute>
<attribute>
<name>name</name>
<type>String</type>
<null_value/>
</attribute>
<attribute>
<name>age</name>
<type>UInt8</type>
<null_value/>
</attribute>
</structure>
<source>
<executable>
<command>cat /var/lib/clickhouse/user_files/person.tsv</command>
<format>TabSeparated</format>
</executable>
</source>
<layout>
<hashed/>
</layout>
<lifetime>10</lifetime>
</dictionary>
</yandex>
二.字典的存储方式
以下测试均在default数据库。
1. flat/hash/sparse_hash/cache
DROP DICTIONARY t_dict_person_ddl;
CREATE DICTIONARY t_dict_person_ddl
(
id UInt64,
code String,
name String,
age UInt8
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9001
user 'default'
db 'default'
password ''
table 't_dic_ch'
where 'id>0'
))
LAYOUT(CACHE(SIZE_IN_CELLS 10000))
LIFETIME(30);
SELECT dictGetString('default.t_dict_person_ddl', 'name', toUInt64(2)) AS regionName;
FLAT()、HASHED()、SPARSE_HASHED()、CACHE(SIZE_IN_CELLS 10000)
2. complex_key_hashed/complex_key_cache
DROP DICTIONARY t_dict_person_ddl;
CREATE DICTIONARY t_dict_person_ddl
(
id UInt64,
code String,
name String,
age UInt8
)
PRIMARY KEY id,code
SOURCE(CLICKHOUSE(
host 'localhost'
port 9001
user 'default'
db 'default'
password ''
table 't_dic_ch'
where 'id>0'
))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(30);
SELECT dictGet('default.t_dict_person_ddl', 'name', tuple(toUInt64(2), 'id002')) AS name;
COMPLEX_KEY_HASHED()、COMPLEX_KEY_CACHE(SIZE_IN_CELLS 10000)
3. range_hashed
drop table t_hash_range;
create table t_hash_range(id UInt64, start Date, end Date, amount Float32) ENGINE=TinyLog;
insert into t_hash_range values
(123, '2020-03-20', '2020-03-22', 0.15)
(123, '2020-03-23', '2020-03-27', 0.25)
(456, '2020-04-20', '2020-04-30', 0.35)
;
查看数据:
SELECT * FROM t_hash_range;
┌──id─┬──────start─┬────────end─┬─amount─┐
│ 123 │ 2020-03-20 │ 2020-03-22 │ 0.15 │
│ 123 │ 2020-03-23 │ 2020-03-27 │ 0.25 │
│ 456 │ 2020-04-20 │ 2020-04-30 │ 0.35 │
└─────┴────────────┴────────────┴────────┘
创建字典:
DROP DICTIONARY t_dict_hash_range;
CREATE DICTIONARY t_dict_hash_range (
id UInt64,
start Date,
end Date,
amount Float32
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
host 'localhost'
port 9001
user 'default'
db 'default'
password ''
table 't_hash_range'
))
LAYOUT(RANGE_HASHED())
RANGE(MIN start MAX end)
LIFETIME(30);
查看id为123的记录,在日期2020-03-21日的amount:
select dictGetFloat32('default.t_dict_hash_range', 'amount', toUInt64(123), toDate('2020-03-21')) as amount;
查看id为123的记录,在日期2020-03-25日的amount:
select dictGetFloat32('default.t_dict_hash_range', 'amount', toUInt64(123), toDate('2020-03-25')) as amount;
日期之外的记录:
SELECT dictGetFloat32('default.t_dict_hash_range', 'amount', toUInt64(123), toDate('2020-03-29')) AS amount;
4. ip_tire
创建表和测试数据:
drop table t_ip_tire;
create table t_ip_tire(prefix String, asn UInt32, ccode String) ENGINE=TinyLog;
insert into t_ip_tire values
('202.79.32.0/20', 17501, 'NP')
('2620:0:870::/48', 3856, 'US')
('2a02:6b8:1::/48', 13238, 'RU')
('2001:db8::/32', 65536, 'ZZ')
;
查看数据:
SELECT * FROM t_ip_tire;
┌─prefix──────────┬───asn─┬─ccode─┐
│ 202.79.32.0/20 │ 17501 │ NP │
│ 2620:0:870::/48 │ 3856 │ US │
│ 2a02:6b8:1::/48 │ 13238 │ RU │
│ 2001:db8::/32 │ 65536 │ ZZ │
└─────────────────┴───────┴───────┘
创建字典:
DROP DICTIONARY t_dict_ip_tire;
CREATE DICTIONARY t_dict_ip_tire (
prefix String,
asn UInt32,
ccode String
)
PRIMARY KEY prefix
SOURCE(CLICKHOUSE(
host 'localhost'
port 9001
user 'default'
db 'default'
password ''
table 't_ip_tire'
))
LAYOUT(IP_TRIE())
LIFETIME(30);
检索数据:
select
dictGetUInt32('default.t_dict_ip_tire', 'asn', tuple(IPv4StringToNum('202.79.32.22'))) as asn,
dictGetString('default.t_dict_ip_tire', 'ccode', tuple(IPv4StringToNum('202.79.32.22'))) as ccode