MySQL dictionary source - ClickHouse Documentation

Example of settings:

DDL
Configuration file

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
))

<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`.

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.

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
Configuration file

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'
))

<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>