跳至主要内容

字典函数

注意

对于使用 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)

参数

  • dict_name — 字典的名称。 字符串文字.
  • id_expr — 键值。 表达式 返回字典键类型值或 元组 类型值,具体取决于字典配置。

返回值

  • 如果不存在键,则为 0。 UInt8.
  • 如果存在键,则为 1。 UInt8.

dictGetHierarchy

创建一个数组,其中包含 层次字典 中键的所有父级。

语法

dictGetHierarchy('dict_name', key)

参数

返回值

dictIsIn

检查字典中整个层次链中键的祖先。

dictIsIn('dict_name', child_id_expr, ancestor_id_expr)

参数

返回值

  • 如果 child_id_expr 不是 ancestor_id_expr 的子级,则为 0。 UInt8.
  • 如果 child_id_exprancestor_id_expr 的子级或 child_id_exprancestor_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 支持专门的函数,这些函数将字典属性值转换为特定数据类型,而不管字典配置如何。

函数

  • dictGetInt8dictGetInt16dictGetInt32dictGetInt64
  • dictGetUInt8dictGetUInt16dictGetUInt32dictGetUInt64
  • dictGetFloat32dictGetFloat64
  • dictGetDate
  • dictGetDateTime
  • dictGetUUID
  • dictGetString
  • dictGetIPv4dictGetIPv6

所有这些函数都有 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 会抛出异常。