字典函数
对于使用 DDL 查询 创建的字典,dict_name
参数必须完全指定,例如 <database>.<dict_name>
。否则,将使用当前数据库。
有关连接和配置字典的信息,请参阅 字典.
dictGet, dictGetOrDefault, dictGetOrNull
从字典中检索值。
dictGet('dict_name', attr_names, id_expr)
dictGetOrDefault('dict_name', attr_names, id_expr, default_value_expr)
dictGetOrNull('dict_name', attr_name, id_expr)
参数
dict_name
— 字典的名称。 字符串文字.attr_names
— 字典列的名称,字符串文字,或列名称元组,元组(字符串文字).id_expr
— 键值。 表达式 返回字典键类型值或 元组 类型值,具体取决于字典配置。default_value_expr
— 如果字典不包含带有id_expr
键的行,则返回的值。 表达式 或 元组(表达式),返回attr_names
属性中配置的值(或值)的数据类型。
返回值
如果 ClickHouse 在 属性的数据类型 中成功解析属性,则函数将返回与
id_expr
相对应的字典属性值。如果字典中没有与
id_expr
相对应的键,则- `dictGet` returns the content of the `<null_value>` element specified for the attribute in the dictionary configuration.
- `dictGetOrDefault` returns the value passed as the `default_value_expr` parameter.
- `dictGetOrNull` returns `NULL` in case key was not found in dictionary.
如果 ClickHouse 无法解析属性的值或值与属性数据类型不匹配,则 ClickHouse 会抛出异常。
简单键字典示例
创建一个包含以下内容的文本文件 ext-dict-test.csv
1,1
2,2
第一列是 id
,第二列是 c1
。
配置字典
<clickhouse>
<dictionary>
<name>ext-dict-test</name>
<source>
<file>
<path>/path-to/ext-dict-test.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<flat />
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>c1</name>
<type>UInt32</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>0</lifetime>
</dictionary>
</clickhouse>
执行查询
SELECT
dictGetOrDefault('ext-dict-test', 'c1', number + 1, toUInt32(number * 10)) AS val,
toTypeName(val) AS type
FROM system.numbers
LIMIT 3;
┌─val─┬─type───┐
│ 1 │ UInt32 │
│ 2 │ UInt32 │
│ 20 │ UInt32 │
└─────┴────────┘
复杂键字典示例
创建一个包含以下内容的文本文件 ext-dict-mult.csv
1,1,'1'
2,2,'2'
3,3,'3'
第一列是 id
,第二列是 c1
,第三列是 c2
。
配置字典
<clickhouse>
<dictionary>
<name>ext-dict-mult</name>
<source>
<file>
<path>/path-to/ext-dict-mult.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<flat />
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>c1</name>
<type>UInt32</type>
<null_value></null_value>
</attribute>
<attribute>
<name>c2</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>0</lifetime>
</dictionary>
</clickhouse>
执行查询
SELECT
dictGet('ext-dict-mult', ('c1','c2'), number + 1) AS val,
toTypeName(val) AS type
FROM system.numbers
LIMIT 3;
┌─val─────┬─type──────────────────┐
│ (1,'1') │ Tuple(UInt8, String) │
│ (2,'2') │ Tuple(UInt8, String) │
│ (3,'3') │ Tuple(UInt8, String) │
└─────────┴───────────────────────┘
范围键字典示例
输入表
CREATE TABLE range_key_dictionary_source_table
(
key UInt64,
start_date Date,
end_date Date,
value String,
value_nullable Nullable(String)
)
ENGINE = TinyLog();
INSERT INTO range_key_dictionary_source_table VALUES(1, toDate('2019-05-20'), toDate('2019-05-20'), 'First', 'First');
INSERT INTO range_key_dictionary_source_table VALUES(2, toDate('2019-05-20'), toDate('2019-05-20'), 'Second', NULL);
INSERT INTO range_key_dictionary_source_table VALUES(3, toDate('2019-05-20'), toDate('2019-05-20'), 'Third', 'Third');
创建字典
CREATE DICTIONARY range_key_dictionary
(
key UInt64,
start_date Date,
end_date Date,
value String,
value_nullable Nullable(String)
)
PRIMARY KEY key
SOURCE(CLICKHOUSE(HOST 'localhost' PORT tcpPort() TABLE 'range_key_dictionary_source_table'))
LIFETIME(MIN 1 MAX 1000)
LAYOUT(RANGE_HASHED())
RANGE(MIN start_date MAX end_date);
执行查询
SELECT
(number, toDate('2019-05-20')),
dictHas('range_key_dictionary', number, toDate('2019-05-20')),
dictGetOrNull('range_key_dictionary', 'value', number, toDate('2019-05-20')),
dictGetOrNull('range_key_dictionary', 'value_nullable', number, toDate('2019-05-20')),
dictGetOrNull('range_key_dictionary', ('value', 'value_nullable'), number, toDate('2019-05-20'))
FROM system.numbers LIMIT 5 FORMAT TabSeparated;
结果
(0,'2019-05-20') 0 \N \N (NULL,NULL)
(1,'2019-05-20') 1 First First ('First','First')
(2,'2019-05-20') 1 Second \N ('Second',NULL)
(3,'2019-05-20') 1 Third Third ('Third','Third')
(4,'2019-05-20') 0 \N \N (NULL,NULL)
另请参阅
dictHas
检查字典中是否存在键。
dictHas('dict_name', id_expr)
参数
返回值
dictGetHierarchy
创建一个数组,其中包含 层次字典 中键的所有父级。
语法
dictGetHierarchy('dict_name', key)
参数
返回值
- 键的父级。 Array(UInt64).
dictIsIn
检查字典中整个层次链中键的祖先。
dictIsIn('dict_name', child_id_expr, ancestor_id_expr)
参数
dict_name
— 字典的名称。 字符串文字.child_id_expr
— 要检查的键。 表达式 返回 UInt64 类型值。ancestor_id_expr
—child_id_expr
键的假定祖先。 表达式 返回 UInt64 类型值。
返回值
- 如果
child_id_expr
不是ancestor_id_expr
的子级,则为 0。 UInt8. - 如果
child_id_expr
是ancestor_id_expr
的子级或child_id_expr
是ancestor_id_expr
,则为 1。 UInt8.
dictGetChildren
将第一级子级作为索引数组返回。它是 dictGetHierarchy 的逆变换。
语法
dictGetChildren(dict_name, key)
参数
返回值
示例
考虑层次字典
┌─id─┬─parent_id─┐
│ 1 │ 0 │
│ 2 │ 1 │
│ 3 │ 1 │
│ 4 │ 2 │
└────┴───────────┘
第一级子级
SELECT dictGetChildren('hierarchy_flat_dictionary', number) FROM system.numbers LIMIT 4;
┌─dictGetChildren('hierarchy_flat_dictionary', number)─┐
│ [1] │
│ [2,3] │
│ [4] │
│ [] │
└──────────────────────────────────────────────────────┘
dictGetDescendant
返回所有后代,就像 dictGetChildren 函数递归应用了 level
次一样。
语法
dictGetDescendants(dict_name, key, level)
参数
dict_name
— 字典的名称。 字符串文字.key
— 键值。 表达式 返回 UInt64 类型值。level
— 层次级别。如果level = 0
,则返回所有一直到最后的后代。 UInt8.
返回值
示例
考虑层次字典
┌─id─┬─parent_id─┐
│ 1 │ 0 │
│ 2 │ 1 │
│ 3 │ 1 │
│ 4 │ 2 │
└────┴───────────┘
所有后代
SELECT dictGetDescendants('hierarchy_flat_dictionary', number) FROM system.numbers LIMIT 4;
┌─dictGetDescendants('hierarchy_flat_dictionary', number)─┐
│ [1,2,3,4] │
│ [2,3,4] │
│ [4] │
│ [] │
└─────────────────────────────────────────────────────────┘
第一级后代
SELECT dictGetDescendants('hierarchy_flat_dictionary', number, 1) FROM system.numbers LIMIT 4;
┌─dictGetDescendants('hierarchy_flat_dictionary', number, 1)─┐
│ [1] │
│ [2,3] │
│ [4] │
│ [] │
└────────────────────────────────────────────────────────────┘
dictGetAll
检索与 正则表达式树字典 中每个键匹配的所有节点的属性值。
除了返回类型为 Array(T)
而不是 T
的值之外,此函数的行为类似于 dictGet
.
语法
dictGetAll('dict_name', attr_names, id_expr[, limit])
参数
dict_name
— 字典的名称。 字符串文字.attr_names
— 字典列的名称,字符串文字,或列名称元组,元组(字符串文字).id_expr
— 键值。 表达式 返回字典键类型值的数组或 元组 类型值,具体取决于字典配置。limit
- 返回的每个值数组的最大长度。当截断时,子节点优先于父节点,否则将遵守正则表达式树字典的定义列表顺序。如果未指定,则数组长度不受限制。
返回值
如果 ClickHouse 在字典中定义的属性数据类型中成功解析属性,则返回一个字典属性值数组,这些值对应于
id_expr
,对应于attr_names
指定的每个属性。如果字典中没有与
id_expr
相对应的键,则返回一个空数组。
如果 ClickHouse 无法解析属性的值或值与属性数据类型不匹配,则 ClickHouse 会抛出异常。
示例
考虑以下正则表达式树字典
CREATE DICTIONARY regexp_dict
(
regexp String,
tag String
)
PRIMARY KEY(regexp)
SOURCE(YAMLRegExpTree(PATH '/var/lib/clickhouse/user_files/regexp_tree.yaml'))
LAYOUT(regexp_tree)
...
# /var/lib/clickhouse/user_files/regexp_tree.yaml
- regexp: 'foo'
tag: 'foo_attr'
- regexp: 'bar'
tag: 'bar_attr'
- regexp: 'baz'
tag: 'baz_attr'
获取所有匹配值
SELECT dictGetAll('regexp_dict', 'tag', 'foobarbaz');
┌─dictGetAll('regexp_dict', 'tag', 'foobarbaz')─┐
│ ['foo_attr','bar_attr','baz_attr'] │
└───────────────────────────────────────────────┘
获取最多 2 个匹配值
SELECT dictGetAll('regexp_dict', 'tag', 'foobarbaz', 2);
┌─dictGetAll('regexp_dict', 'tag', 'foobarbaz', 2)─┐
│ ['foo_attr','bar_attr'] │
└──────────────────────────────────────────────────┘
其他函数
ClickHouse 支持专门的函数,这些函数将字典属性值转换为特定数据类型,而不管字典配置如何。
函数
dictGetInt8
、dictGetInt16
、dictGetInt32
、dictGetInt64
dictGetUInt8
、dictGetUInt16
、dictGetUInt32
、dictGetUInt64
dictGetFloat32
、dictGetFloat64
dictGetDate
dictGetDateTime
dictGetUUID
dictGetString
dictGetIPv4
、dictGetIPv6
所有这些函数都有 OrDefault
修改。例如,dictGetDateOrDefault
。
语法
dictGet[Type]('dict_name', 'attr_name', id_expr)
dictGet[Type]OrDefault('dict_name', 'attr_name', id_expr, default_value_expr)
参数
dict_name
— 字典的名称。 字符串文字.attr_name
— 字典列的名称。 字符串文字.id_expr
— 键值。 表达式 返回 UInt64 或 元组 类型值,具体取决于字典配置。default_value_expr
— 如果字典不包含带有id_expr
键的行,则返回的值。 表达式 返回attr_name
属性中配置的数据类型的值。
返回值
如果 ClickHouse 在 属性的数据类型 中成功解析属性,则函数将返回与
id_expr
相对应的字典属性值。如果字典中没有请求的
id_expr
,则- `dictGet[Type]` returns the content of the `<null_value>` element specified for the attribute in the dictionary configuration.
- `dictGet[Type]OrDefault` returns the value passed as the `default_value_expr` parameter.
如果 ClickHouse 无法解析属性的值或值与属性数据类型不匹配,则 ClickHouse 会抛出异常。