MySQL dictionary source | ClickHouse Docs

Example of settings:

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`.
`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, or through a named collection defined there, see below. Optional.

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

Example of settings:

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>