Skip to content

Latest commit

 

History

History
196 lines (139 loc) · 8.97 KB

materialized-mysql.md

File metadata and controls

196 lines (139 loc) · 8.97 KB
Raw
toc_priority toc_title
29
MaterializedMySQL

MaterializedMySQL {#materialized-mysql}

This is experimental feature that should not be used in production.

Creates ClickHouse database with all the tables existing in MySQL, and all the data in those tables.

ClickHouse server works as MySQL replica. It reads binlog and performs DDL and DML queries.

This feature is experimental.

Creating a Database {#creating-a-database}

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]

Engine Parameters

  • host:port — MySQL server endpoint.
  • database — MySQL database name.
  • user — MySQL user.
  • password — User password.

Engine Settings

  • max_rows_in_buffer — Max rows that data is allowed to cache in memory(for single table and the cache data unable to query). when rows is exceeded, the data will be materialized. Default: 65505.
  • max_bytes_in_buffer — Max bytes that data is allowed to cache in memory(for single table and the cache data unable to query). when rows is exceeded, the data will be materialized. Default: 1048576.
  • max_rows_in_buffers — Max rows that data is allowed to cache in memory(for database and the cache data unable to query). when rows is exceeded, the data will be materialized. Default: 65505.
  • max_bytes_in_buffers — Max bytes that data is allowed to cache in memory(for database and the cache data unable to query). when rows is exceeded, the data will be materialized. Default: 1048576.
  • max_flush_data_time — Max milliseconds that data is allowed to cache in memory(for database and the cache data unable to query). when this time is exceeded, the data will be materialized. Default: 1000.
  • max_wait_time_when_mysql_unavailable — Retry interval when MySQL is not available (milliseconds). Negative value disable retry. Default: 1000.
  • allows_query_when_mysql_lost — Allow query materialized table when mysql is lost. Default: 0 (false).
CREATE DATABASE mysql ENGINE = MaterializedMySQL('localhost:3306', 'db', 'user', '***')
     SETTINGS
        allows_query_when_mysql_lost=true,
        max_wait_time_when_mysql_unavailable=10000;

Settings on MySQL-server side

For the correct work of MaterializeMySQL, there are few mandatory MySQL-side configuration settings that should be set:

  • default_authentication_plugin = mysql_native_password since MaterializeMySQL can only authorize with this method.
  • gtid_mode = on since GTID based logging is a mandatory for providing correct MaterializeMySQL replication. Pay attention that while turning this mode On you should also specify enforce_gtid_consistency = on.

Virtual columns {#virtual-columns}

When working with the MaterializedMySQL database engine, ReplacingMergeTree tables are used with virtual _sign and _version columns.

  • _version — Transaction counter. Type UInt64.
  • _sign — Deletion mark. Type Int8. Possible values:
    • 1 — Row is not deleted,
    • -1 — Row is deleted.

Data Types Support {#data_types-support}

MySQL ClickHouse
TINY Int8
SHORT Int16
INT24 Int32
LONG UInt32
LONGLONG UInt64
FLOAT Float32
DOUBLE Float64
DECIMAL, NEWDECIMAL Decimal
DATE, NEWDATE Date
DATETIME, TIMESTAMP DateTime
DATETIME2, TIMESTAMP2 DateTime64
ENUM Enum
STRING String
VARCHAR, VAR_STRING String
BLOB String
BINARY FixedString

Other types are not supported. If MySQL table contains a column of such type, ClickHouse throws exception "Unhandled data type" and stops replication.

Nullable is supported.

Specifics and Recommendations {#specifics-and-recommendations}

Compatibility restrictions

Apart of the data types limitations there are few restrictions comparing to MySQL databases, that should be resolved before replication will be possible:

  • Each table in MySQL should contain PRIMARY KEY.

  • Replication for tables, those are containing rows with ENUM field values out of range (specified in ENUM signature) will not work.

DDL Queries {#ddl-queries}

MySQL DDL queries are converted into the corresponding ClickHouse DDL queries (ALTER, CREATE, DROP, RENAME). If ClickHouse cannot parse some DDL query, the query is ignored.

Data Replication {#data-replication}

MaterializedMySQL does not support direct INSERT, DELETE and UPDATE queries. However, they are supported in terms of data replication:

  • MySQL INSERT query is converted into INSERT with _sign=1.

  • MySQL DELETE query is converted into INSERT with _sign=-1.

  • MySQL UPDATE query is converted into INSERT with _sign=-1 and INSERT with _sign=1.

Selecting from MaterializedMySQL Tables {#select}

SELECT query from MaterializedMySQL tables has some specifics:

  • If _version is not specified in the SELECT query, FINAL modifier is used. So only rows with MAX(_version) are selected.

  • If _sign is not specified in the SELECT query, WHERE _sign=1 is used by default. So the deleted rows are not included into the result set.

  • The result includes columns comments in case they exist in MySQL database tables.

Index Conversion {#index-conversion}

MySQL PRIMARY KEY and INDEX clauses are converted into ORDER BY tuples in ClickHouse tables.

ClickHouse has only one physical order, which is determined by ORDER BY clause. To create a new physical order, use materialized views.

Notes

  • Rows with _sign=-1 are not deleted physically from the tables.
  • Cascade UPDATE/DELETE queries are not supported by the MaterializedMySQL engine.
  • Replication can be easily broken.
  • Manual operations on database and tables are forbidden.
  • MaterializedMySQL is influenced by optimize_on_insert setting. The data is merged in the corresponding table in the MaterializedMySQL database when a table in the MySQL server changes.

Examples of Use {#examples-of-use}

Queries in MySQL:

mysql> CREATE DATABASE db;
mysql> CREATE TABLE db.test (a INT PRIMARY KEY, b INT);
mysql> INSERT INTO db.test VALUES (1, 11), (2, 22);
mysql> DELETE FROM db.test WHERE a=1;
mysql> ALTER TABLE db.test ADD COLUMN c VARCHAR(16);
mysql> UPDATE db.test SET c='Wow!', b=222;
mysql> SELECT * FROM test;
+---+------+------+
| a |    b |    c |
+---+------+------+
| 2 |  222 | Wow! |
+---+------+------+

Database in ClickHouse, exchanging data with the MySQL server:

The database and the table created:

CREATE DATABASE mysql ENGINE = MaterializedMySQL('localhost:3306', 'db', 'user', '***');
SHOW TABLES FROM mysql;
┌─name─┐
│ test │
└──────┘

After inserting data:

SELECT * FROM mysql.test;
┌─a─┬──b─┐
│ 1 │ 11 │
│ 2 │ 22 │
└───┴────┘

After deleting data, adding the column and updating:

SELECT * FROM mysql.test;
┌─a─┬───b─┬─c────┐
│ 2 │ 222 │ Wow! │
└───┴─────┴──────┘

Original article