Attention

dict_name parameter must be fully qualified for dictionaries created with DDL queries. Eg. <database>.<dict_name>.

Functions for Working with External Dictionaries

For information on connecting and configuring external dictionaries, see External dictionaries.

dictGet

Retrieves a value from an external dictionary.

  1. dictGet('dict_name', 'attr_name', id_expr)
  2. dictGetOrDefault('dict_name', 'attr_name', id_expr, default_value_expr)

Arguments

  • dict_name — Name of the dictionary. String literal.
  • attr_name — Name of the column of the dictionary. String literal.
  • id_expr — Key value. Expression returning a UInt64 or Tuple-type value depending on the dictionary configuration.
  • default_value_expr — Value returned if the dictionary doesn’t contain a row with the id_expr key. Expression returning the value in the data type configured for the attr_name attribute.

Returned value

  • If ClickHouse parses the attribute successfully in the attribute’s data type, functions return the value of the dictionary attribute that corresponds to id_expr.

  • If there is no the key, corresponding to id_expr, in the dictionary, then:

    1. - `dictGet` returns the content of the `<null_value>` element specified for the attribute in the dictionary configuration.
    2. - `dictGetOrDefault` returns the value passed as the `default_value_expr` parameter.

ClickHouse throws an exception if it cannot parse the value of the attribute or the value doesn’t match the attribute data type.

Example

Create a text file ext-dict-text.csv containing the following:

  1. 1,1
  2. 2,2

The first column is id, the second column is c1.

Configure the external dictionary:

  1. <yandex>
  2. <dictionary>
  3. <name>ext-dict-test</name>
  4. <source>
  5. <file>
  6. <path>/path-to/ext-dict-test.csv</path>
  7. <format>CSV</format>
  8. </file>
  9. </source>
  10. <layout>
  11. <flat />
  12. </layout>
  13. <structure>
  14. <id>
  15. <name>id</name>
  16. </id>
  17. <attribute>
  18. <name>c1</name>
  19. <type>UInt32</type>
  20. <null_value></null_value>
  21. </attribute>
  22. </structure>
  23. <lifetime>0</lifetime>
  24. </dictionary>
  25. </yandex>

Perform the query:

  1. SELECT
  2. dictGetOrDefault('ext-dict-test', 'c1', number + 1, toUInt32(number * 10)) AS val,
  3. toTypeName(val) AS type
  4. FROM system.numbers
  5. LIMIT 3
  1. ┌─val─┬─type───┐
  2. 1 UInt32
  3. 2 UInt32
  4. 20 UInt32
  5. └─────┴────────┘

See Also

dictHas

Checks whether a key is present in a dictionary.

  1. dictHas('dict_name', id_expr)

Arguments

Returned value

  • 0, if there is no key.
  • 1, if there is a key.

Type: UInt8.

dictGetHierarchy

Creates an array, containing all the parents of a key in the hierarchical dictionary.

Syntax

  1. dictGetHierarchy('dict_name', key)

Arguments

Returned value

  • Parents for the key.

Type: Array(UInt64).

dictIsIn

Checks the ancestor of a key through the whole hierarchical chain in the dictionary.

  1. dictIsIn('dict_name', child_id_expr, ancestor_id_expr)

Arguments

  • dict_name — Name of the dictionary. String literal.
  • child_id_expr — Key to be checked. Expression returning a UInt64-type value.
  • ancestor_id_expr — Alleged ancestor of the child_id_expr key. Expression returning a UInt64-type value.

Returned value

  • 0, if child_id_expr is not a child of ancestor_id_expr.
  • 1, if child_id_expr is a child of ancestor_id_expr or if child_id_expr is an ancestor_id_expr.

Type: UInt8.

Other Functions

ClickHouse supports specialized functions that convert dictionary attribute values to a specific data type regardless of the dictionary configuration.

Functions:

  • dictGetInt8, dictGetInt16, dictGetInt32, dictGetInt64
  • dictGetUInt8, dictGetUInt16, dictGetUInt32, dictGetUInt64
  • dictGetFloat32, dictGetFloat64
  • dictGetDate
  • dictGetDateTime
  • dictGetUUID
  • dictGetString

All these functions have the OrDefault modification. For example, dictGetDateOrDefault.

Syntax:

  1. dictGet[Type]('dict_name', 'attr_name', id_expr)
  2. dictGet[Type]OrDefault('dict_name', 'attr_name', id_expr, default_value_expr)

Arguments

  • dict_name — Name of the dictionary. String literal.
  • attr_name — Name of the column of the dictionary. String literal.
  • id_expr — Key value. Expression returning a UInt64 or Tuple-type value depending on the dictionary configuration.
  • default_value_expr — Value returned if the dictionary doesn’t contain a row with the id_expr key. Expression returning the value in the data type configured for the attr_name attribute.

Returned value

  • If ClickHouse parses the attribute successfully in the attribute’s data type, functions return the value of the dictionary attribute that corresponds to id_expr.

  • If there is no requested id_expr in the dictionary then:

    1. - `dictGet[Type]` returns the content of the `<null_value>` element specified for the attribute in the dictionary configuration.
    2. - `dictGet[Type]OrDefault` returns the value passed as the `default_value_expr` parameter.

ClickHouse throws an exception if it cannot parse the value of the attribute or the value doesn’t match the attribute data type.

Original article