This guide follows on from the Get Started guides. Please make sure that you have a
Kubernetes Operator or local installation ready.
Make sure you have only run the "101" step of the examples, for example 101_initial_cluster.sh in the
local example. The commands in this guide also assume you have setup the shell
aliases from the example, e.g. env.sh in the local example.
Materialize is a VReplication command/workflow. It can be used as a more
general way to achieve something similar to MoveTables or as a way to generate
materialized views of a table (or set of tables) in the same or
different keyspace from the source table (or set of tables). In general, it can be used to create and maintain
continually updated materialized views in Vitess, without having
to resort to manual or trigger-based population of the view content.
Since Materialize uses VReplication, the view can be kept up-to-date
in very close to real-time which enables use-cases like creating copies of the same table that are sharded in
different ways for the purposes of avoiding expensive cross-shard queries. Materialize is also flexible enough to
allow for you to pre-create the schema and VSchema for the copied table, allowing you
to for example maintain a copy of a table without some of the source table's MySQL indexes.
In this scenario, we are going to make two copies of the corder table in the same commerce keyspace using
the table names of corder_view and corder_view_redacted. The first copy will be identical to the source table, but
for the corder_view_redacted copy we will use the opportunity to drop or redact the price column.
In the case where we are using Materialize to copy tables between or across keyspaces we can use the
"create_ddl": "copy" option in the
to create the target table for us (similar to what MoveTables does). However, in our case where we are using
Materialize within a single keyspace (commerce) so we need to manually create the target tables. Let's go ahead
and do that:
We will run two Materialize operations, one for each copy/view of the corder table we will be creating. We could
combine these two operations into a single Materialize operation, but we will keep them separate for clarity.
Note that the target table is just a normal table, there is nothing that prevents you from writing to it directly.
While you might not want to do that in this in the "materialized view" use-case, in certain other use-cases it might
be completely acceptable to write to the table. Doing so is completedly fine as long as you don't end up altering
or removing rows in a fashion that would break the "replication" part of the VReplication workflow
(e.g. removing a row in the target table directly that is later updated in the source table).
While we can also see and manipulate the underlying VReplication streams created by Materialize there are
Workflow commands to show, stop, start and delete the
Materialize workflow. For example, once we have started the Materialize command above, we can observe the
status of the VReplication workflow using the Workflow command:
Note that deleting the workflow will notDROP the target table of the Materialize workflow or DELETE any of the
data already copied. The data in the target table will remain as it was at the moment the workflow was deleted
Now we can perform the materialization of the corder_view_redacted table we created earlier. Remember that we created
this table without a price column so we will not be copying that column in our query either:
As with MoveTables, a VReplication stream was formed for each of the Materialize workflows we created. We can see these by inspecting the internal _vt.vreplication table on the
target keyspace's primary tablet, e.g. in this case: