Handle upstream schema changes
View as MarkdownMaterialize 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 the
PRIMARY KEYorUNIQUEconstraint that the capture instance uses.
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 SQL Server database
For this guide, setup a SQL Server 2016+ database. In your SQL Server, create a
table t1 and populate:
CREATE TABLE t1 (
a INT
);
INSERT INTO t1 (a) VALUES
(10);
GO -- The GO terminator may be unsupported or unnecessary for your client.
Configure your SQL Server Database
Configure your SQL Server database using the configuration instructions for self hosted SQL Server.
Connect your source database to Materialize
Create a connection to your SQL Server database using the CREATE CONNECTION syntax.
Create a source using the new syntax
In Materialize, create a source using the CREATE SOURCE
syntax.
CREATE SOURCE my_source
FROM SQL SERVER CONNECTION sqlserver_connection;
Create a table from the source
To start ingesting specific tables from your source database, you can create a table in Materialize. We’ll add it into the v1 schema in Materialize.
CREATE SCHEMA v1;
CREATE TABLE v1.t1
FROM SOURCE my_source(REFERENCE dbo.t1);
Once you’ve created a table from source, the initial
snapshot of table v1.t1 will begin.
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 SQL Server database
In your upstream SQL Server database, add a new column b to the table t1:
ALTER TABLE t1
ADD b BIT NULL;
INSERT INTO t1 (a,b) VALUES
(20, 1);
GO -- The GO terminator may be unsupported or unnecessary for your client.
This operation does not impact the SQL Server CDC output; the SQL Server
continues to publish CDC changes only for column a. As such, the addition of a
new column has no immediate effect in Materialize. In Materialize:
- The table
v1.t1will continue to ingest only columna. - The materialized view
v1.matviewwill continue to have access to columnaonly.
B. Enable CDC for your table under a new capture instance in your upstream SQL Server database
In order for Materialize to begin receiving data for this new column, you must
create a new capture instance for your table, explicitly specifing a new
@capture_instance
name.
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 't1',
@role_name = 'materialize_role',
@capture_instance = 'dbo_t1_v2', -- MUST BE SPECIFIED
@supports_net_changes = 0;
GO -- The GO terminator may be unsupported or unnecessary for your client.
The newly created capture instance will include CDC data for column b. Now,
when you create a new table t1 from your source in Materialize (see next
step), Materialize will select the most recently created capture
instance (i.e., the
capture instance with the newly added column b).
@index_name, SQL Server no longer prevents
dropping the constraint that the old capture instance uses. Do not drop it
until you have re-created the table in Materialize from the new capture
instance (see next step). Dropping it earlier leaves the existing Materialize
table with a unique key that SQL Server
no longer enforces, and queries that rely on the key can return wrong results.
See also:
C. Create a new table from the source in Materialize
To incorporate the new column into Materialize, create a new v2 schema and
recreate the table in the new schema. When creating the table, Materialize uses
the most recently created capture
instance (i.e., the
capture instance with the newly added column b):
CREATE SCHEMA v2;
CREATE TABLE v2.t1
FROM SOURCE my_source(REFERENCE dbo.t1);
The snapshotting of table v2.t1 will begin.
v2.t1 will include columns a and b.
When the new v2.t1 table has finished snapshotting, create a new materialized
view matview in the new schema. Since the new v2.matview is referencing the
new v2.t1, it can reference column b:
CREATE MATERIALIZED VIEW v2.matview AS
SELECT SUM(a)
FROM v2.t1
WHERE b = true;
Handle upstream column drop
A. Exclude the column in Materialize
To drop a column safely, in Materialize, first, create a new v3 schema, and
recreate table t1 in the new schema but exclude the column to drop. In this
example, we’ll drop the column b.
CREATE SCHEMA v3;
CREATE TABLE v3.t1
FROM SOURCE my_source(REFERENCE dbo.t1) WITH (EXCLUDE COLUMNS (b));
B. Drop a column in your upstream SQL Server database
In your upstream SQL Server database, drop the column b from the table t1:
ALTER TABLE t1 DROP COLUMN b;
GO -- The GO terminator may be unsupported or unnecessary for your client.
Dropping the column b in SQL Server will not affect v3.t1 (or on v1.t1) in
Materialize. However, the drop affects v2.t1 and v2.matview from our earlier
examples. When the user attempts to read from either, Materialize will report an
error that the source table schema has been altered.
Handle upstream constraint changes
Materialize ignores foreign key and CHECK constraints, and adding a PRIMARY KEY or UNIQUE constraint does not affect ingestion. Materialize records as a
key only the PRIMARY KEY or UNIQUE constraint that the capture instance
uses to identify rows (see Unique keys).
For tables created in v26.49 or later, dropping any other PRIMARY KEY or
UNIQUE constraint does not affect ingestion either.
PRIMARY KEY and
UNIQUE constraint that existed when the table was created. Dropping one of
those upstream is not detected and leaves the table with a key that SQL Server
no longer enforces, and queries that rely on the key can return wrong results.
Before dropping such a constraint, re-create the table in Materialize.
EXCLUDE CONSTRAINTS, EXCLUDE ALL NOT NULL CONSTRAINTS, or EXCLUDE ALL CONSTRAINTS options. Adding or removing a
NOT NULL constraint requires an upstream ALTER COLUMN, which puts the
table into an error state. Re-create the table in Materialize afterwards.
To drop the constraint that the capture instance uses, move the capture
instance to a different unique index first. This example uses a table t2
whose capture instance dbo_t2 uses the primary key t2_pkey, and which also
has a UNIQUE constraint t2_email_key:
CREATE TABLE t2 (
id INT NOT NULL CONSTRAINT t2_pkey PRIMARY KEY,
email NVARCHAR(255) NOT NULL CONSTRAINT t2_email_key UNIQUE
);
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 't2',
@role_name = 'materialize_role',
@capture_instance = 'dbo_t2',
@supports_net_changes = 0;
GO -- The GO terminator may be unsupported or unnecessary for your client.
CREATE TABLE v1.t2
FROM SOURCE my_source(REFERENCE dbo.t2);
A. Create a capture instance on a different index
In your upstream SQL Server database, create a new capture instance that uses
t2_email_key through
@index_name:
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 't2',
@role_name = 'materialize_role',
@capture_instance = 'dbo_t2_v2', -- MUST BE SPECIFIED
@index_name = 't2_email_key',
@supports_net_changes = 0;
GO -- The GO terminator may be unsupported or unnecessary for your client.
SQL Server allows at most two capture instances per table. If the table already has two, disable the one that is not in use first.
If the table has no other PRIMARY KEY or UNIQUE constraint, add one first,
on columns that are NOT NULL. SQL Server does not accept a UNIQUE
constraint over nullable columns as a capture instance index. If @index_name names an index created with CREATE UNIQUE INDEX, the new
Materialize table has no key.
t2_pkey. Dropping
it before you complete step C leaves v1.t2 with a key that SQL Server no
longer enforces, and queries that rely on the key can return wrong results.
B. Create a new table from the source in Materialize
Re-create the table in a new schema. Materialize uses the most recently created
capture instance, so
v4.t2 records t2_email_key as its key:
CREATE SCHEMA v4;
CREATE TABLE v4.t2
FROM SOURCE my_source(REFERENCE dbo.t2);
Queries over v4.t2 can no longer rely on t2_pkey as a key, so their plans
can differ from those over v1.t2. Before moving your views and queries to
v4.t2, compare their plans with EXPLAIN PLAN.
C. Drop the old table in Materialize
Once v4.t2 has finished snapshotting and you have moved your views and queries
to it, drop v1.t2 and any objects that depend on it:
DROP TABLE v1.t2 CASCADE;
D. Drop the constraint in your upstream SQL Server database
In your upstream SQL Server database, disable the old capture
instance dbo_t2, then drop the
constraint. Ensure that no other source tables or applications still use
dbo_t2; otherwise, they will break.
EXEC sys.sp_cdc_disable_table
@source_schema = 'dbo',
@source_name = 't2',
@capture_instance = 'dbo_t2';
ALTER TABLE t2 DROP CONSTRAINT t2_pkey;
GO -- The GO terminator may be unsupported or unnecessary for your client.
Dropping t2_pkey has no effect on v4.t2.
Optional
Disable unused capture instance
SQL Server only allows a maximum of 2 capture instances per table.
To find the capture instance(s) for a table:
SELECT capture_instance
FROM cdc.change_tables
WHERE source_schema = '<schema>'
AND source_table = '<table>';
After you have fully cut over to the new source version for the table, and you previously created a new capture instance for your upstream table, you may wish to disable the old capture instance if it is no longer in use.
To disable a capture instance for a table:
EXEC sys.sp_cdc_disable_table
@source_schema = '<schema>',
@source_name = '<source_table_name>',
@capture_instance = '<old_capture_instance_name>';