Dictionary Key and Fields

The <structure> clause describes the dictionary key and fields available for queries.

Overall structure:

<dictionary>
    <structure>
        <id>
            <name>Id</name>
        </id>

        <attribute>
            <!-- Attribute parameters -->
        </attribute>

        ...

    </structure>
</dictionary>

CREATE DICTIONARY (
    Id UInt64,
    -- attributes
)
PRIMARY KEY Id
...

In xml-file attributes are described in the structure section:

<id> — Key column.
<attribute> — Data column. There can be a large number of attributes.

In DDL-query attributes are described the body of CREATE query: - PRIMARY KEY — Key column - AttrName AttrType — Data column

Key

ClickHouse supports the following types of keys:

Numeric key. UInt64. Defined in the <id> tag or using PRIMARY KEY keyword.
Composite key. Set of values of different types. Defined in the tag <key> or PRIMARY KEY keyword.

A xml-structure can contain either <id> or <key>. DDL-query must contain single PRIMARY KEY.

Numeric Key

Type: UInt64.

Configuration example:

<id>
    <name>Id</name>
</id>

Configuration fields:

name – The name of the column with keys.

For DDL-query:

CREATE DICTIONARY (
    Id UInt64,
    ...
)
PRIMARY KEY Id
...

PRIMARY KEY – The name of the column with keys.

Composite Key

The key can be a tuple from any types of fields. The layout in this case must be complex_key_hashed or complex_key_cache.

!!! tip A composite key can consist of a single element. This makes it possible to use a string as the key, for instance.

The key structure is set in the element <key>. Key fields are specified in the same format as the dictionary attributes. Example:

<structure>
    <key>
        <attribute>
            <name>field1</name>
            <type>String</type>
        </attribute>
        <attribute>
            <name>field2</name>
            <type>UInt32</type>
        </attribute>
        ...
    </key>
...

CREATE DICTIONARY (
    field1 String,
    field2 String
    ...
)
PRIMARY KEY field1, field2
...

For a query to the dictGet* function, a tuple is passed as the key. Example: dictGetString('dict_name', 'attr_name', tuple('string for field1', num_for_field2)).

Attributes

Configuration example:

<structure>
    ...
    <attribute>
        <name>Name</name>
        <type>ClickHouseDataType</type>
        <null_value></null_value>
        <expression>rand64()</expression>
        <hierarchical>true</hierarchical>
        <injective>true</injective>
        <is_object_id>true</is_object_id>
    </attribute>
</structure>

CREATE DICTIONARY somename (
    Name ClickHouseDataType DEFAULT '' EXPRESSION rand64() HIERARCHICAL INJECTIVE IS_OBJECT_ID
)

Configuration fields:

Tag	Description	Required
`name`	Column name.	Yes
`type`	ClickHouse data type. ClickHouse tries to cast value from dictionary to the specified data type. For example, for MySQL, the field might be `TEXT`, `VARCHAR`, or `BLOB` in the MySQL source table, but it can be uploaded as `String` in ClickHouse. Nullable is not supported.	Yes
`null_value`	Default value for a non-existing element. In the example, it is an empty string. You cannot use `NULL` in this field.	Yes
`expression`	Expression that ClickHouse executes on the value. The expression can be a column name in the remote SQL database. Thus, you can use it to create an alias for the remote column. Default value: no expression.	No
`hierarchical`	Hierarchical support. Mirrored to the parent identifier. Default value: `false`.	No
`injective`	Flag that shows whether the `id -> attribute` image is injective. If `true`, ClickHouse can automatically place after the `GROUP BY` clause the requests to dictionaries with injection. Usually it significantly reduces the amount of such requests. Default value: `false`.	No
`is_object_id`	Flag that shows whether the query is executed for a MongoDB document by `ObjectID`. Default value: `false`.	No