Materialize is a lower level vreplication command that allows for generalized materialization of tables. The target tables
can be copies, aggregations, or views. The target tables are kept in sync in near-realtime.
You can specify multiple tables to materialize using the create sub-command's --table-settings flag.
Be careful to avoid using the INSTANT ADD COLUMN feature in MySQL 8.0+ with materialization source tables as this can cause the vreplication based materialization workflow to break.
Each action or sub-command has additional options/parameters that can be used to modify its behavior. Please see the command's reference docs for the full list of command options or flags. Below we will add additional information for a subset of key options.
This is a JSON array where each value must contain two key/value pairs. The first required key is 'target_table' and it is the name of the table in the target-keyspace to store the results in. The second required key is 'source_expression' and its value is the select query to run against the source table. An optional key/value pair can also be specified for 'create_ddl' which provides the DDL to create the target table if it does not exist – you can alternatively specify a value of 'copy' if the target table schema should be copied as-is from the source keyspace. Here's an example value for table-settings:
"source_expression": "select email from customer where customer_id = 1" },
"source_expression": "select * from states",
"create_ddl": "copy" },
"source_expression": "select sku, count(*) as orders, sum(price) as revenue from corder group by sku",
"create_ddl": "create table sales_by_sku (sku varbinary(128) not null primary key, orders bigint, revenue bigint)" }