Hierarchical Dictionaries 

ClickHouse supports hierarchical dictionaries with a numeric key.

Look at the following hierarchical structure:

0 (Common parent)
│
├── 1 (Russia)
│   │
│   └── 2 (Moscow)
│       │
│       └── 3 (Center)
│
└── 4 (Great Britain)
    │
    └── 5 (London)

This hierarchy can be expressed as the following dictionary table.

region_idparent_regionregion_name
10Russia
21Moscow
32Center
40Great Britain
54London

This table contains a column parent_region that contains the key of the nearest parent for the element.

ClickHouse supports the hierarchical property for external dictionary attributes. This property allows you to configure the hierarchical dictionary similar to described above.

The dictGetHierarchy function allows you to get the parent chain of an element.

For our example, the structure of dictionary can be the following:

<dictionary>
    <structure>
        <id>
            <name>region_id</name>
        </id>

        <attribute>
            <name>parent_region</name>
            <type>UInt64</type>
            <null_value>0</null_value>
            <hierarchical>true</hierarchical>
        </attribute>

        <attribute>
            <name>region_name</name>
            <type>String</type>
            <null_value></null_value>
        </attribute>

    </structure>
</dictionary>

Rating: 3.7 - 3 votes

Was this content helpful?
★★★★☆