data dictionary
Data dictionary is a very simple and practical storage medium provided by ClickHouse. It defines data in the form of key value and attribute mapping. The data in the dictionary will be loaded into memory actively or passively (whether the data is loaded actively at ClickHouse startup or lazily at the first query depends on the parameter settings), and dynamic updates are supported. Due to the memory resident nature of dictionary data, it is very suitable for saving constants or frequently used dimension table data to avoid unnecessary JOIN queries. Data dictionaries are divided into built-in and extended dictionaries. As the name suggests, built-in dictionaries are built-in dictionaries by ClickHouse by default, while external extended dictionaries are dictionaries implemented by users through custom configuration. Under normal circumstances, the data in the dictionary can only be accessed through the dictionary function (ClickHouse has specially set up a kind of dictionary function, which is specially used for retrieving dictionary data). But there is one exception, that is, using a special dictionary engine. With the help of the dictionary engine, the data dictionary can be attached to a proxy data table to realize the JOIN query between the data table and the dictionary data. More details and usage of the dictionary engine will be introduced in the following chapters.
Built in dictionary
ClickHouse currently has only one built-in dictionary - Yandex METRICA dictionary. From the name, we can see that this is a dictionary used in ClickHouse's own products, and its design intention is to quickly access geo geographic data. Unfortunately, Yandex did not open geo geographic data due to copyright. This means that ClickHouse's current built-in dictionary only provides the definition mechanism and retrieval function of the dictionary, without any built-in ready-made data. Therefore, the current situation of the built-in dictionary is awkward, and it is necessary to import data according to its dictionary specification.
External extended dictionary
The external extension dictionary is registered in ClickHouse in the form of a plug-in. The user defines the data schema and data source. Currently, the extended dictionary supports 7 types of memory layouts and 4 types of data sources. Compared with the built-in dictionary with very limited content, the extended dictionary is a more commonly used function.
Preparing dictionary data
In the following pages, the usage methods of each extended dictionary will be introduced one by one, including their configuration form, data structure and creation method. However, some preparations need to be made before that. To facilitate the demonstration, three copies of test data are prepared here in advance, all of which are in CSV format. Among them, the first is enterprise organization data, which will be used for flat, hashed, cache and complex_key_hashed and complex_ Key_ Demonstration scenario of cache dictionary. This data has three fields: id, code and name. The data format is as follows:
The second is sales data, which will be used for range_ Demonstration scenario of hashed dictionary. This data has four fields: id, start, end and price. The data format is as follows:
The last one is asn data, which will be used to demonstrate ip_trie dictionary scenario. This data has three fields: ip, asn and country. The data format is as follows:
Element composition of the extended dictionary configuration file
The configuration file of the extension dictionary is defined by config Dictionaries in XML file_ Config configuration item specifies:
By default, ClickHouse will automatically recognize and load all the_ Dictionary Configuration file at the end of XML. At the same time, ClickHouse can also dynamically perceive various changes of configuration files in this directory, and supports online update of configuration files without downtime. Multiple dictionaries can be defined within a single dictionary configuration file, where each dictionary is defined by a set of dictionary elements. Under the dictionary element, there are five sub elements, all of which are required. Their complete configuration structure is as follows:
In the above structure, the meanings of main configurations are as follows.
Name: the name of the dictionary. It is used to determine the unique identifier of the dictionary. It must be globally unique. Multiple dictionaries cannot be duplicated.
Structure: the data structure of the dictionary.
layout: the type of dictionary, which determines how data is organized and stored in memory. Currently, there are 7 types of extended dictionaries.
Source: the data source of the dictionary, which determines where the data in the dictionary is loaded. At present, the extended dictionary has three types of data sources: files, databases and others.
lifetime: the update time of the dictionary. The extended dictionary supports online data update.
Data structure of extended dictionary
The data structure of the extended dictionary is defined by the structure element and consists of two parts: key and attribute, which respectively describe the data ID and field attribute of the dictionary. The complete form of structure is as follows (these fields will be used to access the data in the dictionary in the later query process):
Next, the meanings of key and attribute are introduced in detail.
key
Key is used to define the key values of dictionaries. Each dictionary must contain one key value key field, which is used to locate data, similar to the table primary key of a database. The key value can be divided into two types: numerical type and compound type. (1) Numeric: numeric keys are defined by UInt64 integer types, and support flat, hashed, and range_ The dictionary of hashed and cache types (the extended dictionary type will be described later). Its definition method is as follows.
(2) Composite: composite keys are defined by Tuple tuples and can be composed of 1 to more fields, similar to composite primary keys in databases. It only supports complex_key_hashed, complex_key_cache and IP_ Dictionary of type trie. The definition method is as follows.
attribute
Attribute is used to define the attribute fields of the dictionary. The dictionary can have 1 to more attribute fields. Its complete definition method is as follows:
There are 7 configuration items under the attribute element, including name, type and null_value is required. The detailed descriptions of these configuration items are shown in the table.
Type of extended dictionary
The types of the extended dictionary are defined by the layout element. Currently, there are 7 types. The type of a dictionary not only determines the storage structure of its data in memory, but also determines the type of key supported by the dictionary. According to different key types, they can be divided into two categories: one is based on flat, hashed and range_ A single numeric key type composed of hashed and cache, because both of them use a single numeric id; The other is composed of complex_key_hashed, complex_key_cache and IP_ A composite key type composed of tries. complex_key_hashed and complex_ Key_ The cache dictionary is no different from hashed and cache in terms of function, but simply replaces the numeric key with the composite key.
Next, we will introduce the complete configuration methods of the seven dictionaries one by one in combination with the prepared test data. Through this process, you can appreciate the characteristics of different types of dictionaries and their usage.
flat
Flat dictionary is the most neutral dictionary type of all types. It can only use UInt64 numeric key. As the name suggests, the data of the flat dictionary is saved in memory using an array structure. The initial size of the array is 1024 and the upper limit is 500000, which means that it can only save 500000 rows of data at most. If the amount of data exceeds its upper limit when creating a dictionary, the dictionary creation fails. The code shows the flat dictionary configuration file created manually.
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_flat_dict</name>
<source>
<file>
<path>/chbase/data/dictionaries/organization.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<flat/>
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>code</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary></dictionaries>
In the above configuration, the source data source is a CSV file, and the structure data structure corresponds to it. After copying the configuration file to the /etc/clickhouse-server directory of the ClickHouse service node, the creation of the dictionary is completed. Check system After the dictionaries system table, you can see that the flat dictionary has been created successfully.
SELECT name,type,key,attribute.names,attribute.types FROM system.dictionaries;
hashed
Similarly, the hashed dictionary can only use UInt64 numeric key s. However, unlike the flat dictionary, the data of the hashed dictionary is saved in memory through a hash structure, and there is no restriction on the upper storage limit.
The configuration file is as follows:
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_hashed_dict</name>
<source>
<file>
<path>/chbase/data/dictionaries/organization.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<hashed/>
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>code</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary></dictionaries>
Similarly, after copying the configuration file to the /etc/clickhouse-server directory of the ClickHouse service node, the creation process of the dictionary is completed.
range_hashed
range_hashed dictionary can be regarded as a variant of hashed dictionary. It adds the feature of specified time interval to the original function. The data will be stored in hash structure and sorted according to time. Time interval through range_min and range_ The max element specifies that the specified field must be of type Date or DateTime. Now follow the configuration of the hashed dictionary and create a test_ range_hashed_ Dictionary XML configuration file, change layout to range_hashed and add range_min and range_max element.
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_range_hashed_dict</name>
<source>
<file>
<path>/chbase/data/dictionaries/sales.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<range_hashed/>
</layout>
<structure>
<id>
<name>id</name>
</id>
<range_min>
<name>start</name>
</range_min>
<range_max>
<name>end</name>
</range_max>
<attribute>
<name>price</name>
<type>Float32</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary></dictionaries>
In the above configuration, a copy of sales data is used. The start and end fields in the data are related to range respectively_ Min and range_max corresponds. After copying the configuration file to the /etc/clickhouse-server directory of the ClickHouse service node, the creation of the dictionary is completed. Check system After the dictionaries system table, you can see the range_hashed dictionary has been created successfully:
cache
The cache dictionary can only use UInt64 numeric key, and its dictionary data will be saved in memory through a fixed length vector array. Fixed length vector array is also called cells, and its array length is determined by size_in_cells. And size_ In_ The value of cells must be an integer multiple of 2. If it is not, it will be automatically taken as an integer multiple of 2.
The retrieval logic of cache dictionary is different from that of other dictionaries. It does not load all data into memory at one time. When getting data from the cache dictionary, it will first check whether the data has been cached in the cells array. If the data is not cached, it will load the data from the source and cache it in the cells. Therefore, cache dictionary is the most unstable dictionary because its performance depends entirely on the cache hit rate (cache hit rate = hit times / query times). If 99% or higher cache hit rate cannot be achieved, it is best not to use this type. The code shows the cache dictionary configuration file created after hashed.
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_cache_dict</name>
<source>
<executable>
<command>cat /chbase/data/dictionaries/organization.csv</command>
<format>CSV</format>
</executable>
</source>
<layout>
<cache>
<size_in_cells>10000</size_in_cells>
</cache>
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>code</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary></dictionaries>
In the above configuration, layout is declared as cache and the cache size is_ In_ Cells is set to 10000. The value of cells can be considered according to the actual situation. It is also feasible to set it to 1000000000 when the memory is abundant. It should also be noted that if the cache dictionary uses a local file as the data source, it must be set in the form of executable.
complex_key_hashed
Complex_ Key_ The hashed dictionary is exactly the same as the hashed dictionary in terms of function, except that a single numeric key is replaced by a compound key. The code listing shows that after the configuration is modeled on the hashed dictionary, change the layout to complex_ Key_ An example of hashed and replacing the key type.
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_complex_key_hashed_dict</name>
<source>
<file>
<path>/chbase/data/dictionaries/organization.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<complex_key_hashed/>
</layout>
<structure>
<key>
<attribute>
<name>id</name>
<type>UInt64</type>
</attribute>
<attribute>
<name>code</name>
<type>String</type>
</attribute>
</key>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary></dictionaries>
After copying the configuration file to the /etc/clickhouse-server directory of the ClickHouse service node, the creation of the dictionary is completed.
complex_key_cache
Complex_ Key_ The cache dictionary also has the same characteristics as the cache dictionary, except that a single numeric key is replaced by a composite key. Now follow the cache dictionary and change the layout to complex_key_cache and replace key type
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_complex_key_cache_dict</name>
<source>
<executable>
<command>cat /chbase/data/dictionaries/organization.csv</command>
<format>CSV</format>
</executable>
</source>
<layout>
<complex_key_cache>
<size_in_cells>10000</size_in_cells>
</complex_key_cache>
</layout>
<structure>
<key>
<attribute>
<name>id</name>
<type>UInt64</type>
</attribute>
<attribute>
<name>code</name>
<type>String</type>
</attribute>
</key>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary></dictionaries>
After copying the configuration file to the /etc/clickhouse-server directory of the ClickHouse service node, the creation of the dictionary is completed.
ip_trie
Although both are dictionaries of composite key s, IP_ The trie dictionary is special because it can only specify a single String type field to refer to the IP prefix. IP_ The trie dictionary data is stored in memory using the trie tree structure and is specially used for IP prefix query scenarios, such as querying the corresponding ASN information through the IP prefix.
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_ip_trie_dict</name>
<source>
<file>
<path>/chbase/data/dictionaries/asn.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<ip_trie/>
</layout>
<structure>
<key>
<attribute>
<name>prefix</name>
<type>String</type>
</attribute>
</key>
<attribute>
<name>asn</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>country</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary></dictionaries>
Through the above introduction, the reader has learned how to create seven types of dictionaries. In these dictionaries, flat, hashed, and range_hashed has the highest performance in turn, while cache has the most unstable performance. Finally, summarize the characteristics of these dictionaries
Data source for extended dictionary
The data source is defined using the source element, which specifies where the dictionary data comes from. At this stage, the extended dictionary supports 9 data sources in 3 categories. Next, we will introduce them one by one in a more systematic way.
file type
Files can be divided into local files, executable files and remote files. They are the most easy-to-use and direct data sources, and are very suitable for static data.
Local file
Local files are defined using the file element. Where, path represents the absolute path of the data file, and format represents the data format, such as CSV or TabSeparated. Its complete configuration is shown below.
Executable file
The executable data source is a variant of the local file. It needs to access the data file through the cat command. For cache and complex_ Key_ The dictionary of cache type must use this type of file data source. Executable files are defined using the executable element. Where, command represents the absolute path of the data file, and format represents the data format, such as CSV or TabSeparated. Its complete configuration is shown below.
Remote file
The remote file is similar to the executable file, except that it replaces the cat command with the post request and supports the HTTP and HTTPS protocols. Remote files are defined using HTTP elements. Where, url represents the access address of remote data, and format represents the data format, such as CSV or TabSeparated. Its complete configuration is shown below.
Database type
Compared with file type, database type data source is more suitable for use in formal production environment. Currently, the extended dictionary supports mysql, ClickHouse itself and MongoDB. Next, we will introduce their creation methods. For the installation of MySQL and MongoDB database environment, it will not be repeated here due to space reasons, and the relevant SQL scripts can be downloaded from the source code site attached to this book.
MySQL
MySQL data source supports extracting data from the specified database as the data source of its dictionary. First, you need to prepare the source data and execute the following statement to create a test table in MySQL:
CREATE TABLE t_organization (
id int(11) NOT NULL AUTO_INCREMENT,
code varchar(40) DEFAULT NULL,
name varchar(60) DEFAULT NULL,
updatetime datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET =utf8;Next, write the test data:
INSERT INTO t_organization (code,name,updatetime)
values('a0001','R & D department',now()),('a0002','Product department',NOW());After the above preparation, you can configure the dictionary of MySQL data source. Now follow the configuration of the flat dictionary and create a test_ Mysql_ Dictionary XML configuration file, and replace the source with the MySQL data source:
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_mysql_dict</name>
<source>
<mysql>
<port>3306</port>
<user>root</user>
<password>chen2895161</password>
<replica>
<host>192.168.59.133</host>
<priority>1</priority>
</replica>
<db>gmall</db>
<table>t_organization</table>
<!--
<where>id=1</where>
-->
<invalidate_query>select updatetime from t_organization where id = 8</invalidate_query>
</mysql>
</source>
<layout>
<flat/>
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>code</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>updatetime</name>
<type>DateTime</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary></dictionaries>
The meanings of each configuration item are as follows.
❑ port: database port.
❑ user: database user name.
❑ password: database password.
❑ replica: the host address of the database. It supports MySQL clusters.
❑ db:database database.
❑ table: the data table corresponding to the dictionary.
❑ where: filter criteria when querying table; not required.
❑ invalidate_query: specify an SQL statement to judge whether the data needs to be updated when it is updated. It is not required.
ClickHouse
The extended dictionary supports the ClickHouse data table as the data source, which is an interesting design. Before configuration, you also need to prepare the test data of the data source. Execute the following statement to create a test table in ClickHouse and write the test data:
CREATE TABLE t_organization (
ID UInt64,
Code String,
Name String,
UpdateTime DateTime
) ENGINE = TinyLog;INSERT INTO t_organization values (1,'a0001','R & D department',now()), (2,'a0002','Product department',now()), (3,'a0003','Data Department',now()), (4,'a0004','Testing department',now()), (5,'a0005','Operation and maintenance department',now()), (6,'a0006','Planning Department',now()), (7,'a0007','Marketing Department',now());
The configuration of ClickHouse data source is very similar to that of MySQL data source, so we can follow the dictionary configuration of MySQL data source and create a data source named test_ Ch_ Dictionary XML, replace the source with the ClickHouse data source:
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_ch_dict</name>
<source>
<clickhouse>
<host>192.168.59.136</host>
<port>9000</port>
<user>default</user>
<password></password>
<db>default</db>
<table>t_organization</table>
<!--
<where>id=10</where>
-->
<invalidate_query>SELECT UpdateTime FROM t_organization WHERE ID = 1</invalidate_query>
</clickhouse>
</source>
<layout>
<flat/>
</layout>
<!--Case sensitive,Need to correspond to data table fields-->
<structure>
<id>
<name>ID</name>
</id>
<attribute>
<name>Code</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>Name</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>UpdateTime</name>
<type>DateTime</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary></dictionaries>
The meanings of each configuration item are as follows.
❑ host: the host address of the database.
❑ port: database port.
❑ user: database user name.
❑ password: database password.
❑ db:database database.
❑ table: the data table corresponding to the dictionary.
❑ where: filter criteria when querying table; not required.
❑ invalidate_query: specify an SQL statement to judge whether the data needs to be updated when it is updated. It is not required.
Data update strategy of extended dictionary
The extended dictionary supports the online update of data, and there is no need to restart the service after the update. The update frequency of dictionary data is specified by the lifetime element in the configuration file. The unit is seconds:
Where min and max specify the upper and lower limits of the update interval respectively. ClickHouse will randomly trigger the update action within this time interval, which can effectively stagger the update time and avoid explosive updates of all dictionaries at the same time. When min and max are both 0, dictionary update will be disabled. For a cache dictionary, lifetime also represents its cache expiration time.
The dictionary has the concept of version internally. In the process of data update, the old version of the dictionary will continue to provide services. Only after the update is completely successful, the new version of the dictionary will replace the old version. Therefore, the update operation or the exception occurred during the update will not have any impact on the use of the dictionary.
Different types of dictionary data sources have slightly different update mechanisms. In general, the extended dictionary currently does not support incremental updates. However, some data sources can be judged according to the identification, and the update action can be implemented only after the source data has changed substantially. This identifier, which determines whether the source data has been modified, is called previous in the dictionary. It stores a value for comparison. ClickHouse's background process will start the judgment of data refresh every 5 seconds, and compare whether the previous values in each data dictionary are the same. If they are the same, they do not need to update the data; If it is different and meets the update frequency, it means that the data needs to be updated. Different data sources have different implementation logic for the previous value acquisition method, which is described in detail below.
File data source
For a file type data source, its previous value comes from the modification time of the system file, which is similar to the stat query command in the Linux system:
The data update is triggered only when the values of the last two previous are different.
MySQL(InnoDB), ClickHouse and ODBC
For MySQL (InnoDB engine), ClickHouse and ODBC data sources, their previous values are derived from invalidate_ SQL statements defined in query. For example, in the following example, if the two updatetime values are different, it will be determined that the source data has changed and the dictionary needs to be updated.
This has certain requirements for the source table. It must have a field that supports judging whether the data is updated.
MySQL(MyISAM)
If the data source is MySQL's MyISAM table engine, its previous value is much simpler. In mysql, the data table using the MyISAM table engine supports querying the modification time through the SHOW TABLE STATUS command. For example, execute the following statement in MySQL to query the update of the data table_ Time value:
SHOW TABLE STATUS WHERE NAME = 't_organization';
So, if you update twice_ If the value of time is different, it will be determined that the source data has changed and the dictionary needs to be updated.
Other data sources
Except for the data sources described above, other data sources cannot judge whether to skip the update according to the identification. Therefore, no matter whether the data changes substantially or not, as long as the time requirements of the current lifetime are met, they will perform the update action. Compared with the previous update methods, other types of updates are less efficient. In addition to the passive update according to the time frequency defined by lifetime, the data dictionary can also actively trigger the update. The following statement will trigger the update of all data dictionaries:
SYSTEM RELOAD DICTIONARIES
It also supports specifying the update of a specific dictionary:
SYSTEM RELOAD DICTIONARIE [DICT_NAME]
Basic operations of extended dictionary
So far, we have created 10 different types of extended dictionaries in ClickHouse. Next, we will focus on the basic operations of the dictionary, including querying the metadata and data of the dictionary, and accessing the data with the help of the dictionary table engine.
Metadata query
Through system Dictionaries system table, which can query metadata information of extended dictionaries. For example, by executing the following statement, you can see the names, types, fields and other information of all the extension dictionaries that have been created so far:
SELECT name,type,key,attribute.names,attribute.types FROM system.dictionaries;
In system In the dictionaries system table, the meanings of the main fields are as follows.
❑ Name: the name of the dictionary. When using dictionary functions, you need to access data through the dictionary name.
❑ type: the type of dictionary.
❑ key: the key value of the dictionary. The data is located by the key value.
❑ attribute Names: the name of the attribute, which is saved as an array.
❑ attribute Types: attribute types, which are saved in the form of an array in the same order as attribute Names are the same.
❑ bytes_allocated: the number of bytes in memory occupied by loaded data.
❑ query_count: the number of times the dictionary has been queried.
❑ hit_rate: the hit rate of dictionary data query.
❑ element_count: the number of rows of loaded data.
❑ load_factor: loading rate of data.
❑ source: data source information.
❑ last_exception: exception information, which requires special attention. If the dictionary generates an exception during loading, the exception information will be written to this field. last_exception is the main way to obtain dictionary debugging information.
Data query
Under normal circumstances, dictionary data can only be obtained through dictionary functions. For example, the following statement uses the dictget ('dict\u name','attr\u name', key) function:
SELECT dictGet('test_flat_dict','name',toUInt64(2));
If the dictionary uses a compound key, you need to use tuples as parameters:
SELECT dictGet('test_ip_trie_dict','asn',tuple(IPv4StringToNum('82.118.230.0')));
In addition to the dictGet function, ClickHouse also provides a series of dictionary functions prefixed with dictGet, as shown below.
❑ functions to obtain integer data: dictGetUInt8, dictGetUInt16, dictGetUInt32, dictGetUInt64, dictGetInt8, dictGetInt16, dictGetInt32, dictGetUInt64.
❑ functions to obtain floating-point data: dictGetFloat32, dictGetFloat64.
❑ functions to obtain date data: dictGetDate, dictGetDateTime.
❑ functions to get string data: dictGetString, dictGetUUID. The usage of these functions is similar to dictGet and will not be repeated here.
Dictionary table
In addition to reading data through Dictionary functions, ClickHouse also provides another way to read data in the form of Dictionary tables. A Dictionary table is a data table that uses the Dictionary table engine, such as the following example:
CREATE TABLE tb_test_flat_dict (
id UInt64,
code String,
name String
) ENGINE = Dictionary(test_flat_dict);Through this table, you can query the data in the dictionary.
Creating dictionaries using DDL queries
From version 19.17.4.11, ClickHouse supports creating dictionaries using DDL queries, for example:
It can be seen that the configuration parameters are no different from those before, but they are converted to DDL.