Description #

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. There are limitations, however, to the queries which can be used with Materialize:

• The query must be a SELECT statement
• Only the following operators are supported: =, <, <=, >, >=, <>, != (e.g. no IN, OR, or LIKE)
• The query must be against a single table (so no JOINs)
• The query cannot use DISTINCT
• The query cannot use a derived table
• Expressions in the query must have an alias, e.g. select hour(c1) as c1_hour from t1
• The GROUP BY expression cannot reference an aggregate expression such as MAX or COUNT

The Basic Materialize Workflow Lifecycle #

1. Initiate the migration using Materialize
2. Monitor the workflow using show or status
Materialize --target-keyspace <target-keyspace> show --workflow <workflow>
Materialize --target-keyspace <target-keyspace> status --workflow <workflow>
   
3. Start accessing your views once the workflow has started Replicating

Command #

Please see the Materialize command reference for a full list of sub-commands and their flags.

Example #

vtctldclient --server localhost:15999 Materialize --workflow product_sales --target-keyspace commerce create --source-keyspace commerce --table-settings '[{"target_table": "sales_by_sku", "create_ddl": "create table sales_by_sku (sku varbinary(128) not null primary key, orders bigint, revenue bigint)", "source_expression": "select sku, count(*) as orders, sum(price) as revenue from corder group by sku"}]' --cells zone1 --cells zone2 --tablet-types replica

Parameters #

Action #

Materialize is an "umbrella" command. The action or sub-command defines the operation on the workflow.

Options #

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.

--cells #

optional
default local cell

Uses #

• Improve performance by using picking a tablet in cells in network proximity with the target
• To reduce bandwidth costs by skipping cells that are in different availability zones
• Select cells where replica lags are lower

--tablet-types #

optional
default "in_order:REPLICA,PRIMARY"
string

Uses #

• To reduce the load on PRIMARY tablets by using REPLICAs or RDONLYs
• Reducing lag by pointing to PRIMARY

--table-settings #

required
JSON

[
  {
    "target_table": "customer_one_email",
    "source_expression": "select email from customer where customer_id = 1"
  },
  {
    "target_table": "states",
    "source_expression": "select * from states",
    "create_ddl": "copy"
  },
  {
    "target_table": "sales_by_sku",
    "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)"
  }
]

Notes #

There are special commands to perform common materialization tasks and you should prefer them to using Materialize directly.

• If you just want to copy tables to a different keyspace use MoveTables
• If you want to change sharding strategies use Reshard instead