Добавил в файл /etc/clickhouse-server/config.xml
<dictionaries_config>/etc/clickhouse-server/mysql-dictionary.xml</dictionaries_config>
<dictionaries_lazy_load>false</dictionaries_lazy_load>
Создал файл с настройками словаря /etc/clickhouse-server/mysql-dictionary.xml
<dictionary>
<name>mysql_user_actions</name>
<source>
<mysql>
<port>3306</port>
<user>admin</user>
<password>passwd</password>
<replica>
<host>localhost></host>
<priority>1</priority>
</replica>
<db>log</db>
<table>user_action</table>
<where></where>
<invalidate_query>SQL_QUERY</invalidate_query>
</mysql>
</source>
<layout>
<flat />
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>action_name</name>
<type>string</type>
<null_value>Неизвестно</null_value>
</attribute>
</structure>
<lifetime>846500</lifetime>
</dictionary>
При попытке использовать словарь в запросе выбрасывается исключение:
:) select datetime, session_id, dictGetString('mysql_user_actions', 'action_name', action_id) as action from user_action group by datetime, session_id, action;
SELECT
datetime,
session_id,
dictGetString('mysql_user_actions', 'action_name', action_id) AS action
FROM user_action
GROUP BY
datetime,
session_id,
action
Received exception from server (version 18.12.17):
Code: 36. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: No such external dictionary: mysql_user_actions
Я так понимаю, что он ищет словарь по порту 9000. Т.е. конфиг у меня не правильный. Я брал пример конфига с официальной документации.
В логах сервера есть варнинги по подключению данного словаря как я понимаю.
2018.10.11 10:10:16.266257 [ 31 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/mysql-dictionary.xml: unknown node in file: 'name', expected 'dictionary'
2018.10.11 10:10:16.266305 [ 31 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/mysql-dictionary.xml: unknown node in file: 'source', expected 'dictionary'
2018.10.11 10:10:16.266323 [ 31 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/mysql-dictionary.xml: unknown node in file: 'layout', expected 'dictionary'
2018.10.11 10:10:16.266340 [ 31 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/mysql-dictionary.xml: unknown node in file: 'structure', expected 'dictionary'
2018.10.11 10:10:16.266357 [ 31 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/mysql-dictionary.xml: unknown node in file: 'lifetime', expected 'dictionary'
2018.10.11 10:20:16.340788 [ 31 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/mysql-dictionary.xml: unknown node in file: 'source', expected 'dictionary'
2018.10.11 10:20:16.340831 [ 31 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/mysql-dictionary.xml: unknown node in file: 'layout', expected 'dictionary'
2018.10.11 10:20:16.340849 [ 31 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/mysql-dictionary.xml: unknown node in file: 'structure', expected 'dictionary'
2018.10.11 10:20:16.340866 [ 31 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/mysql-dictionary.xml: unknown node in file: 'lifetime', expected 'dictionary'
Как правильно подружить clickhouse и mysql?