Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

MySQL dictionary source

View as MarkdownEdit this pageReport an issueSet up docs MCP

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