Materialize

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.

All of the command options and parameters are listed in our reference page for the Materialize command. In our examples to follow we will only touch on what is possible using Materialize.

Let’s start by loading some sample data:

  1. $ mysql < ../common/insert_commerce_data.sql

We can look at what we just inserted:

  1. $ mysql --table < ../common/select_commerce_data.sql
  3. Using commerce
  4. Customer
  5. +-------------+--------------------+
  6. | customer_id | email              |
  7. +-------------+--------------------+
  8. |           1 | alice@domain.com   |
  9. |           2 | bob@domain.com     |
  10. |           3 | charlie@domain.com |
  11. |           4 | dan@domain.com     |
  12. |           5 | eve@domain.com     |
  13. +-------------+--------------------+
  14. Product
  15. +----------+-------------+-------+
  16. | sku      | description | price |
  17. +----------+-------------+-------+
  18. | SKU-1001 | Monitor     |   100 |
  19. | SKU-1002 | Keyboard    |    30 |
  20. +----------+-------------+-------+
  21. COrder
  22. +----------+-------------+----------+-------+
  23. | order_id | customer_id | sku      | price |
  24. +----------+-------------+----------+-------+
  25. |        1 |           1 | SKU-1001 |   100 |
  26. |        2 |           2 | SKU-1002 |    30 |
  27. |        3 |           3 | SKU-1002 |    30 |
  28. |        4 |           4 | SKU-1002 |    30 |
  29. |        5 |           5 | SKU-1002 |    30 |
  30. +----------+-------------+----------+-------+

Note that we are using commerce keyspace.

Planning to Use Materialize

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.

Create the Destination Tables

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 Materialize json_spec table_settings 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:

  1. $ cat <<EOF | mysql commerce
  2. CREATE TABLE corder_view (
  3.   order_id bigint NOT NULL,
  4.   customer_id bigint DEFAULT NULL,
  5.   sku varbinary(128) DEFAULT NULL,
  6.   price bigint DEFAULT NULL,
  7.   PRIMARY KEY (order_id)
  8. );
  9. CREATE TABLE corder_view_redacted (
  10.   order_id bigint NOT NULL,
  11.   customer_id bigint DEFAULT NULL,
  12.   sku varbinary(128) DEFAULT NULL,
  13.   PRIMARY KEY (order_id)
  14. );
  15. EOF

And now we can proceed to the Materialize step(s).

Start the Simple Copy Materialization

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.

  1. $ vtctlclient Materialize -- '{"workflow": "copy_corder_1", "source_keyspace": "commerce", "target_keyspace": "commerce", "table_settings": [{"target_table": "corder_view", "source_expression": "select * from corder"}]}'

Now, we should see the materialized view table corder_view:

  1. $ mysql --binary-as-hex=false commerce -e "select * from corder_view"
  2. +----------+-------------+----------+-------+
  3. | order_id | customer_id | sku      | price |
  4. +----------+-------------+----------+-------+
  5. |        1 |           1 | SKU-1001 |   100 |
  6. |        2 |           2 | SKU-1002 |    30 |
  7. |        3 |           3 | SKU-1002 |    30 |
  8. |        4 |           4 | SKU-1002 |    30 |
  9. |        5 |           5 | SKU-1002 |    30 |
  10. +----------+-------------+----------+-------+

And if we insert a row into the source table, it will be replicated to the materialized view:

  1. $ mysql commerce -e "insert into corder (order_id, customer_id, sku, price) values (6, 6, 'SKU-1002', 30)"
  3. $ mysql --binary-as-hex=false commerce -e "select * from corder_view"
  4. +----------+-------------+----------+-------+
  5. | order_id | customer_id | sku      | price |
  6. +----------+-------------+----------+-------+
  7. |        1 |           1 | SKU-1001 |   100 |
  8. |        2 |           2 | SKU-1002 |    30 |
  9. |        3 |           3 | SKU-1002 |    30 |
  10. |        4 |           4 | SKU-1002 |    30 |
  11. |        5 |           5 | SKU-1002 |    30 |
  12. |        6 |           6 | SKU-1002 |    30 |
  13. +----------+-------------+----------+-------+

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).

Viewing the Workflow While in Progress

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:

  1. $  vtctlclient Workflow -- commerce listall
  2. Following workflow(s) found in keyspace commerce: copy_corder_1
  4. $ vtctlclient Workflow -- commerce.copy_corder_1 show
  5. {
  6.     "Workflow": "copy_corder_1",
  7.     "SourceLocation": {
  8.         "Keyspace": "commerce",
  9.         "Shards": [
  10.             "0"
  11.         ]
  12.     },
  13.     "TargetLocation": {
  14.         "Keyspace": "commerce",
  15.         "Shards": [
  16.             "0"
  17.         ]
  18.     },
  19.     "MaxVReplicationLag": 1,
  20.     "MaxVReplicationTransactionLag": 1,
  21.     "Frozen": false,
  22.     "ShardStatuses": {
  23.         "0/zone1-0000000101": {
  24.             "PrimaryReplicationStatuses": [
  25.                 {
  26.                     "Shard": "0",
  27.                     "Tablet": "zone1-0000000101",
  28.                     "ID": 1,
  29.                     "Bls": {
  30.                         "keyspace": "commerce",
  31.                         "shard": "0",
  32.                         "filter": {
  33.                             "rules": [
  34.                                 {
  35.                                     "match": "corder_view",
  36.                                     "filter": "select * from corder"
  37.                                 }
  38.                             ]
  39.                         }
  40.                     },
  41.                     "Pos": "4c89eede-8c68-11ed-a40a-6f1a36c22987:1-1070",
  42.                     "StopPos": "",
  43.                     "State": "Running",
  44.                     "DBName": "vt_commerce",
  45.                     "TransactionTimestamp": 1672862991,
  46.                     "TimeUpdated": 1672862991,
  47.                     "TimeHeartbeat": 1672862991,
  48.                     "TimeThrottled": 0,
  49.                     "ComponentThrottled": "",
  50.                     "Message": "",
  51.                     "Tags": "",
  52.                     "WorkflowType": "Materialize",
  53.                     "WorkflowSubType": "None",
  54.                     "CopyState": null
  55.                 }
  56.             ],
  57.             "TabletControls": null,
  58.             "PrimaryIsServing": true
  59.         }
  60.     },
  61.     "SourceTimeZone": "",
  62.     "TargetTimeZone": ""
  63. }

We can now also use the Workflow stop/start actions to temporarily stop the materialization workflow. For example:

  1. $ vtctlclient Workflow -- commerce.copy_corder_1 stop
  2. +------------------+--------------+
  3. |      Tablet      | RowsAffected |
  4. +------------------+--------------+
  5. | zone1-0000000100 |            1 |
  6. +------------------+--------------+

And start to start the workflow again and continue with the materialization:

  1. $ vtctlclient Workflow -- commerce.copy_corder_1 start
  2. +------------------+--------------+
  3. |      Tablet      | RowsAffected |
  4. +------------------+--------------+
  5. | zone1-0000000100 |            1 |
  6. +------------------+--------------+

If at some point, when the initial copy is done and we have fully materialized all of the (initial) data, we do not want to continue replicating changes from the source, we can delete the workflow:

  1. $ vtctlclient Workflow -- commerce.copy_corder_1 delete
  2. +------------------+--------------+
  3. |      Tablet      | RowsAffected |
  4. +------------------+--------------+
  5. | zone1-0000000100 |            1 |
  6. +------------------+--------------+

Note that deleting the workflow will not DROP 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 (or stopped).

Start the Redacted Price Materialization

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:

  1. $ vtctlclient Materialize -- '{"workflow": "copy_corder_2", "source_keyspace": "commerce", "target_keyspace": "commerce", "table_settings": [{"target_table": "corder_view_redacted", "source_expression": "select order_id, customer_id, sku from corder"}]}'

Again, looking the target table will show all the source table rows, this time without the price column:

  1. $ mysql commerce --binary-as-hex=false -e "select * from corder_view_redacted"
  2. +----------+-------------+----------+
  3. | order_id | customer_id | sku      |
  4. +----------+-------------+----------+
  5. |        1 |           1 | SKU-1001 |
  6. |        2 |           2 | SKU-1002 |
  7. |        3 |           3 | SKU-1002 |
  8. |        4 |           4 | SKU-1002 |
  9. |        5 |           5 | SKU-1002 |
  10. |        6 |           6 | SKU-1002 |
  11. +----------+-------------+----------+

Again, we can add a row to the source table, and see it replicated into the target table:

  1. $ mysql commerce -e "insert into corder (order_id, customer_id, sku, price) values (7, 7, 'SKU-1002', 30)"
  3. $ mysql commerce --binary-as-hex=false -e "select * from corder_view_redacted"
  4. +----------+-------------+----------+
  5. | order_id | customer_id | sku      |
  6. +----------+-------------+----------+
  7. |        1 |           1 | SKU-1001 |
  8. |        2 |           2 | SKU-1002 |
  9. |        3 |           3 | SKU-1002 |
  10. |        4 |           4 | SKU-1002 |
  11. |        5 |           5 | SKU-1002 |
  12. |        6 |           6 | SKU-1002 |
  13. |        7 |           7 | SKU-1002 |
  14. +----------+-------------+----------+

What Happened Under the Covers

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:

  1. # We want to connect directly to the primary mysqld
  2. $ SOCKETPATH=${VTDATAROOT}/$(vtctlclient ListAllTablets -- --keyspace=commerce --tablet_type=primary | awk '$1 sub(/zone1-/, "vt_") {print $1}')
  4. $ mysql -u root -h localhost --socket=${SOCKETPATH}/mysql.sock --binary-as-hex=false -e "select * from _vt.vreplication\G"
  5. *************************** 1. row ***************************
  6.                    id: 2
  7.              workflow: copy_corder_2
  8.                source: keyspace:"commerce" shard:"0" filter:{rules:{match:"corder_view_redacted" filter:"select order_id, customer_id, sku from corder"}}
  9.                   pos: MySQL56/4c89eede-8c68-11ed-a40a-6f1a36c22987:1-4764
  10.              stop_pos: NULL
  11.               max_tps: 9223372036854775807
  12.   max_replication_lag: 9223372036854775807
  13.                  cell:
  14.          tablet_types:
  15.          time_updated: 1672865504
  16. transaction_timestamp: 1672865502
  17.                 state: Running
  18.               message:
  19.               db_name: vt_commerce
  20.           rows_copied: 6
  21.                  tags:
  22.        time_heartbeat: 1672865504
  23.         workflow_type: 0
  24.        time_throttled: 0
  25.   component_throttled:
  26.     workflow_sub_type: 0

Cleanup

As seen earlier, you can easily use the Workflow delete command to clean up a Materialize workflow when it’s no longer needed.

While this deletes the Materialize VReplication stream, the actual source and target tables are left unchanged and in the same state they were at the moment the VReplication stream was deleted.