MaterializeMySQL Engine

Refer to the official documentation 

Syntax:

Configure the database engine type as MaterializeMySQL and configure related parameters when creating a database.

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

where SETTINGS are:
[ { include_tables | exclude_tables } ]
[ skip_error_count ]
[ skip_unsupported_tables ]
[ query_with_final] 
[ order_by_only_primary_key ]
[ enable_binlog_reserved ]
[ shard_model ]
[ rate_limiter_row_count_per_second ]

Engine Parameters:

Engine Configuration Options:

Example:

CREATE DATABASE IF NOT EXISTS db_name ON CLUSTER cluster
ENGINE = MaterializeMySQL('<MySQL Address>:3306', '<Database>', '<User>', '<Password>') 
SETTINGS 
        allows_query_when_mysql_lost=true,
        max_wait_time_when_mysql_unavailable=10000;

MySQL Server-Side Configuration

To ensure MaterializeMySQL works correctly, there are some mandatory MySQL side configuration settings:

• default_authentication_plugin = mysql_native_password, because MaterializeMySQL can only authorize using this method.
• gtid_mode = on, because GTID-based logging is mandatory to provide proper MaterializeMySQL replication. Note that when turning this mode On, you should also specify enforce_gtid_consistency = on.

Usage Tips

Virtual Fields

When creating a new ReplacingMergeTree engine table with the MaterializeMySQL database engine on a cloud data warehouse UClickHouse cluster, two virtual fields will be added to the table by default.

DDL Statement Conversion

MaterializeMySQL does not support direct insert, delete, and update queries. MySQL DDL statements will be converted to corresponding ClickHouse DDL statements:

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

Note:

• If ClickHouse cannot parse certain DDL statements, the statements will be ignored.
• The MaterializeMySQL engine does not support cascading UPDATE/DELETE queries.

SELECT Queries

• If _version is not specified in the SELECT query, the FINAL modifier is used, and the data corresponding to the maximum _version is returned, which is the latest version of the data.
• If _sign is not specified in the SELECT query, it defaults to WHERE _sign=1, which means it returns data that is not deleted (_sign=1).

Index Conversion

• ClickHouse database tables automatically convert MySQL primary key and index clauses into ORDER BY tuples.
• ClickHouse has only one physical order determined by the ORDER BY clause. To create a new physical order, use materialized views.

MySQL and Cloud Data Warehouse UClickHouse Field Type Correspondence

Other types are not supported. If the MySQL table contains columns of such types, ClickHouse throws an exception “Unhandled data type” and stops replication.

Nullable  is already supported.

Synchronize with UDB MySQL Cloud Database

Create Database and Table

• Create a database and table in UDB MySQL database

  CREATE DATABASE mysqltest;
  CREATE TABLE mysqltest.testtable (id INT PRIMARY KEY, name VARCHAR(10));

• Create a synchronized database in the cloud data warehouse UClickhouse.

  set allow_experimental_database_materialize_mysql=1;
  CREATE DATABASE mysqltest ENGINE = MaterializeMySQL('MySQL database address', 'Database name', 'Username', 'Password');

• Query tables in the synchronized library in the cloud data warehouse UClickHouse.

  SHOW TABLES FROM mysqltest

  Query result as follows:

Insert Data and Query

• Insert data in UDB MySQL database

  INSERT INTO mysqltest.testtable VALUES (1,'Zhang San'),(2,'Li Si'),(3,'Jin Gang');

• Query in the cloud data warehouse UClickhouse

  SELECT * FROM mysqltest.testtable;

  Result as follows:

Delete Data, Add Column and Query

• Delete data, add a column, and update data for querying in UDB MySQL database.

  DELETE FROM mysqltest.testtable WHERE id=1;
  ALTER TABLE mysqltest.testtable ADD COLUMN age INT;
  UPDATE mysqltest.testtable SET age=28 where id=3;
  SELECT * FROM testtable;

  Result as follows:

• Query in the cloud data warehouse UClickhouse

  SELECT * FROM mysqltest.testtable;

  Result as follows: