Handle upstream schema changes

View as Markdown

NOTE: Changing column types is currently unsupported.

Materialize allows you to handle certain types of upstream table schema changes seamlessly, specifically:

Adding a column in the upstream database.
Dropping a column in the upstream database.
Dropping a PRIMARY KEY, UNIQUE, or NOT NULL constraint in the upstream database.

This guide walks you through how to handle these changes without any downtime in Materialize.

Prerequisites

Some familiarity with Materialize. If you’ve never used Materialize before, start with our guide to getting started.

Set up a MySQL database

For this guide, set up a MySQL 8.0.1+ database. In your MySQL database, create a table t1 in mydb and populate it:

CREATE DATABASE mydb;
USE mydb;

CREATE TABLE t1 (
    a INT PRIMARY KEY
);

INSERT INTO t1 (a) VALUES (10);

Configure your MySQL database

Configure your MySQL database for GTID-based binlog replication. You must set binlog_row_metadata=FULL to use the new CREATE SOURCE syntax.

Connect your source database to Materialize

Create a connection to your MySQL database using the CREATE CONNECTION syntax.

Create a source

In Materialize, create a source using the CREATE SOURCE syntax.

CREATE SOURCE my_source
  FROM MYSQL CONNECTION mysql_connection;

Create a table from the source

To start ingesting specific tables from your source database, create a table in Materialize. We’ll add it into the v1 schema.

CREATE SCHEMA v1;

CREATE TABLE v1.t1
    FROM SOURCE my_source (REFERENCE mydb.t1);

Once you’ve created a table from source, the initial snapshot of table v1.t1 will begin.

NOTE: During the snapshotting, the data ingestion for the existing tables for the same source is temporarily blocked. As such, if possible, you can resize the cluster to speed up the snapshotting process and once the process finishes, resize the cluster for steady-state. You can monitor the snapshot progress on the overview page for the source in the Materialize console.

Create a view on top of the table

For this guide, add a materialized view matview (also in schema v1) that sums column a from table t1.

CREATE MATERIALIZED VIEW v1.matview AS
    SELECT SUM(a) FROM v1.t1;

Handle upstream column addition

A. Add a column in your upstream MySQL database

In your upstream MySQL database, add a new column b to the table t1:

ALTER TABLE t1
    ADD COLUMN b BOOLEAN DEFAULT false;

INSERT INTO t1 (a, b) VALUES (20, true);

NOTE: In MySQL, BOOL/BOOLEAN is an alias for MySQL type TINYINT(1). As such, the column b will be ingested as smallint in Materialize. See MySQL’s data type documentation.

This operation has no immediate effect in Materialize. In Materialize:

The table v1.t1 will continue to ingest only column a.
The materialized view v1.matview will continue to have access to column a only.

B. Incorporate the new column in Materialize

MySQL uses binlog-based replication, which automatically includes all columns. To incorporate the new column into Materialize, create a new v2 schema and recreate the table in the new schema:

CREATE SCHEMA v2;

CREATE TABLE v2.t1
    FROM SOURCE my_source (REFERENCE mydb.t1);

The snapshotting of table v2.t1 will begin. v2.t1 will include columns a and b.

When v2.t1 has finished snapshotting, create a new materialized view in the new schema. Since v2.matview references v2.t1, it can reference column b:

CREATE MATERIALIZED VIEW v2.matview AS
    SELECT SUM(a)
    FROM v2.t1
    WHERE b = 1;

Handle upstream column drop

A. Exclude the column in Materialize

To drop a column safely, first create a new schema in Materialize and recreate the table excluding the column you intend to drop. In this example, we’ll drop column b.

CREATE SCHEMA v3;

CREATE TABLE v3.t1
    FROM SOURCE my_source (REFERENCE mydb.t1) WITH (EXCLUDE COLUMNS (b));

B. Drop the column in your upstream MySQL database

In your upstream MySQL database, drop column b from table t1:

ALTER TABLE t1 DROP COLUMN b;

Dropping column b will have no effect on v3.t1 in Materialize, provided you completed step A before dropping the column. However, the drop affects v2.t1 and v2.matview from our earlier examples. When you attempt to read from either, Materialize will report an error that the source table schema has been altered.

Once you have finished migrating any views and queries from v2 to v3, you can clean up the old objects:

DROP TABLE v2.t1;
DROP MATERIALIZED VIEW v2.matview;
DROP SCHEMA v2;

Handle upstream constraint drop

PREVIEW Excluding constraints with EXCLUDE CONSTRAINTS, EXCLUDE ALL NOT NULL CONSTRAINTS, or EXCLUDE ALL CONSTRAINTS is in public preview. It is under active development and may have stability or performance issues. It isn't subject to our backwards compatibility guarantees.

Materialize ignores foreign key and CHECK constraints. As such, you can add or drop them without affecting ingestion. To handle changes in PRIMARY KEY, UNIQUE, and NOT NULL constraints, follow the steps below.

A. Exclude the constraint in Materialize

To drop a PRIMARY KEY, UNIQUE, or NOT NULL constraint, first create a new v4 schema in Materialize and recreate table t1 in the new schema, excluding the constraint you intend to drop. In this example, we’ll drop the primary key.

MySQL identifies PRIMARY KEY and UNIQUE constraints by their index name. The primary key’s index is always named PRIMARY, and a UNIQUE constraint uses the name of its index. The constraint name is a string literal and must match the upstream index name exactly, including case. To list the index names of a table, run SHOW INDEX FROM t1; in your MySQL database.

CREATE SCHEMA v4;

CREATE TABLE v4.t1
    FROM SOURCE my_source (REFERENCE mydb.t1) WITH (EXCLUDE CONSTRAINTS ('PRIMARY'));

Materialize does not record the excluded constraint as a key of v4.t1.

Excluding nullability constraints

EXCLUDE CONSTRAINTS only accepts PRIMARY KEY and UNIQUE constraint names. Individual NOT NULL constraints cannot be excluded by name. To drop NOT NULL constraints safely, use EXCLUDE ALL NOT NULL CONSTRAINTS:

CREATE SCHEMA v4;

CREATE TABLE v4.t1
    FROM SOURCE my_source (REFERENCE mydb.t1) WITH (EXCLUDE ALL NOT NULL CONSTRAINTS);

Note that when excluding NOT NULL constraints:

Primary key columns remain NOT NULL, unless the primary key is also excluded with EXCLUDE CONSTRAINTS.
Columns in a UNIQUE constraint become nullable. This means Materialize no longer uses that constraint as a key when optimizing queries, which might reduce query performance.

PRIMARY KEY and UNIQUE constraints are still recorded. Dropping one upstream still puts the table into an error state unless it is also excluded:

CREATE TABLE v4.t1
    FROM SOURCE my_source (REFERENCE mydb.t1)
    WITH (EXCLUDE ALL NOT NULL CONSTRAINTS, EXCLUDE CONSTRAINTS ('PRIMARY'));

Excluding all constraints

To record no constraints at all, use EXCLUDE ALL CONSTRAINTS: the table has no keys and every column is nullable, so any later constraint drop is safe. Queries that relied on those keys for optimization may run slower.

CREATE TABLE v4.t1
    FROM SOURCE my_source (REFERENCE mydb.t1) WITH (EXCLUDE ALL CONSTRAINTS);

B. Drop the constraint in your upstream MySQL database

In your upstream MySQL database, drop the primary key from table t1:

ALTER TABLE t1 DROP PRIMARY KEY;

Dropping the primary key will have no effect on v4.t1 in Materialize, provided you completed step A before dropping it. However, the drop affects v3.t1 from our earlier example, which still records the primary key as a key. When you attempt to read from it, Materialize will report an error that the constraint was dropped upstream.

Once you have finished migrating any views and queries from v3 to v4, you can clean up the old objects:

DROP TABLE v3.t1;
DROP SCHEMA v3;