MySQL dictionary source

Configure MySQL as a dictionary source in ClickHouse.

Example of settings:

DDL

SOURCE(MYSQL(
    port 3306
    user 'clickhouse'
    password 'qwerty'
    replica(host 'example01-1' priority 1)
    replica(host 'example01-2' priority 1)
    db 'db_name'
    table 'table_name'
    where 'id=10'
    invalidate_query 'SQL_QUERY'
    fail_on_connection_loss 'true'
    query 'SELECT id, value_1, value_2 FROM db_name.table_name'
    enable_compression 1
))

Configuration file

<source>
  <mysql>
      <port>3306</port>
      <user>clickhouse</user>
      <password>qwerty</password>
      <replica>
          <host>example01-1</host>
          <priority>1</priority>
      </replica>
      <replica>
          <host>example01-2</host>
          <priority>1</priority>
      </replica>
      <db>db_name</db>
      <table>table_name</table>
      <where>id=10</where>
      <invalidate_query>SQL_QUERY</invalidate_query>
      <fail_on_connection_loss>true</fail_on_connection_loss>
      <query>SELECT id, value_1, value_2 FROM db_name.table_name</query>
      <enable_compression>1</enable_compression>
  </mysql>
</source>

Setting fields:

Setting	Description
port	The port on the MySQL server. You can specify it for all replicas, or for each one individually (inside <replica>).
user	Name of the MySQL user. You can specify it for all replicas, or for each one individually (inside <replica>).
password	Password of the MySQL user. You can specify it for all replicas, or for each one individually (inside <replica>).
replica	Section of replica configurations. There can be multiple sections.
replica/host	The MySQL host.
replica/priority	The replica priority. When attempting to connect, ClickHouse traverses the replicas in order of priority. The lower the number, the higher the priority.
db	Name of the database.
table	Name of the table.
where	The selection criteria. The syntax for conditions is the same as for WHERE clause in MySQL, for example, id > 10 AND id < 20. Optional.
invalidate_query	Query for checking the dictionary status. Optional. Read more in the section Refreshing dictionary data using LIFETIME.
fail_on_connection_loss	Controls behavior of the server on connection loss. If true, an exception is thrown immediately if the connection between client and server was lost. If false, the server retries to fetch data at least three times before reporting an error. Note that retrying leads to increased response times. Default value: false.
query	The custom query. Optional.
enable_compression	Enables zlib compression for the MySQL protocol connection. When set to 1, ClickHouse requests protocol-level compression from the MySQL server. Can also be set per-replica inside <replica>. Default value: 0.
ssl_ca_pem	Contents of the CA certificate that the MySQL server certificate is verified against. Optional.
ssl_cert_pem	Contents of the client certificate, for certificate-based authentication. Optional.
ssl_key_pem	Contents of the private key belonging to ssl_cert_pem. Optional.
ssl_ca, ssl_cert, ssl_key	The same credentials as paths to files on the server. Only allowed for a dictionary defined in a server configuration file, see below. Optional.

The table or where fields cannot be used together with the query field. And either one of the table or query fields must be declared.

ssl_ca, ssl_cert and ssl_key name files that the server opens with its own privileges, so they are only accepted for a dictionary defined in a server configuration file. A dictionary created with a CREATE DICTIONARY query must pass the contents instead, in ssl_ca_pem, ssl_cert_pem and ssl_key_pem. Those values are masked in logs and in SHOW queries, the same way passwords are.

There is no explicit parameter secure. When establishing an SSL-connection security is mandatory.

MySQL can be connected to on a local host via sockets. To do this, set host and socket.

Example of settings:

DDL

SOURCE(MYSQL(
    host 'localhost'
    socket '/path/to/socket/file.sock'
    user 'clickhouse'
    password 'qwerty'
    db 'db_name'
    table 'table_name'
    where 'id=10'
    invalidate_query 'SQL_QUERY'
    fail_on_connection_loss 'true'
    query 'SELECT id, value_1, value_2 FROM db_name.table_name'
))

Configuration file

<source>
  <mysql>
      <host>localhost</host>
      <socket>/path/to/socket/file.sock</socket>
      <user>clickhouse</user>
      <password>qwerty</password>
      <db>db_name</db>
      <table>table_name</table>
      <where>id=10</where>
      <invalidate_query>SQL_QUERY</invalidate_query>
      <fail_on_connection_loss>true</fail_on_connection_loss>
      <query>SELECT id, value_1, value_2 FROM db_name.table_name</query>
  </mysql>
</source>